PostgreSQL Conference 2012 on February 24 in Japan

Japan PostgreSQL Users Group will be having an annual 1day technical conference, PostgreSQL Conference 2012, on February 24 in Tokyo. There will be two keynote sessions and 13 sessions on PostgreSQL.
(The photo was taken by @koyhoge at PostgreSQL Conference 2011)

• PostgreSQL Conference 2012 - NPO法人 日本PostgreSQLユーザ http://www.postgresql.jp/events/pgcon2012/ (Google translated)

xlogdump 0.5.1 released

The latest xlogdump has been released.

This version allows users to lookup object names from object ids for built-in database objects by reading the lookup table file instead of the system catalog.

----------------------------------------------------------------------
2011-11-15  Satoshi Nagayasu <satoshi.nagayasu@gmail.com>
        * Version 0.5.1
        * Allows to lookup the object names for the build-in database objects
          by reading `oid2name.txt' file, instead of reading the system
          catalogs through a database connection,
        * Added '-f, --file' option to read an oid2name cache file,
        * Added '-g, --gen_oid2name' option to generate an oid2name file
          to cache oids and object names in the system catalogs.
----------------------------------------------------------------------

Performance impact of the pg_stat_statements module (Updated: Nov-04-2011)

When I tweeted that I thought pg_stat_statements should be integrated to the core a few weeks ago, Josh responded me that it would be up to submitting a patch and figuring out the performace overhead.

So, before starting a patch work, I've decided to figure out the performance impact of the pg_stat_statements module.

The right pic is the results from pgbench, averages from 5 times runs each, with and without pg_stat_statements module.

Loading data into UNLOGGED tables, and considering ALTER TABLE

UNLOGGED table has been introduced in 9.1, and it would improve INSERT/UPDATE/DELETE performance in several situations. At this time, I'm curious how it would work well on data loading.

To load large amount of rows into PostgreSQL, WAL logging would be a performance penalty, especially under the situation that have several tables on different spindles, because WAL logging forces data loading being serialized.

I have created a small function, called "enable_logging()", to switch table persistence mode for existing tables between (regular) logging and UNLOGGED.

bool enable_logging(Oid relid, bool mode);

You can see the source at github, and an example at the bottom of this entry.

Index-only scans and heap block reads

As you may know, the Index-only scans feature has been committed to the PostgreSQL repository and it's available to all the developers.

I'm very curious about such performance feature, and always want to understand what would change the things better and how it works. So, I just tried the index-only scans to determine what would be changed.

The right figure shows PostgreSQL index scans and determining tuple visibilities. It tells us why each index scan needs to access heap tuples, and how the Index-only scans solve the problem.

Because of the PostgreSQL storage design, tuple visibility information is only stored in the tuple header itself. So, to determine an index item visibility, the corresponding heap tuple header needs to be examined even it is an index scan.