Postgresql's huge disadvantage

When building a database which has a big deal of ranged queries, it might be extremely helpful to have clustered index support.

Let say, you keep a table of messages. To query last 10 at inbox it takes about 1 disk seek in worst case with clustered on index by (receiver, timestamp). When there is no clustering, be ready to issue 10 disk seeks.

InnoDB and MS SQL Server both have clustered index support. Instead, Postgresql provides CLUSTER command, which must be explicitly issued to rebuild internal database structure to cluster rows according to specified index. In order to keep you DB more or less clustered, you have to cron the CLUSTER command.

But:

1) CLUSTER takes exclusive lock on table. It took 2 hours to cluster my 3 GB of data. Daily cron would render my application to have 10% downtime. Nice. You can try to aid this by using pg_reorg.

2) Clustering does not change any logical data, only physical storage layout. Nevertheless, it generates the amount of WAL equal to the size of the data. Again, daily CLUSTER would add 3 GB of backup traffic. Same with pg_reorg.

All this makes clustered indexing in Postgresql unusable.

Postgresql tuple/row

Q: What is the difference between tuple and row in Postgresql?
A: Tuple is a some version of a row.

Postgresql transaction counter (transactions per second)

There are transactions counters for each database in a cluster.

If you want to find out how many transactions has your system generated at the moment, you should connect to any database as a superuser (postgres) and

select sum(xact_commit) from pg_stat_database;

Easy, but took some time to find the recipe.

Write-heavy setup for Postgresql

My project has a database which is updated almost as frequently, as read. The main bottleneck for the database was disk speed. Here are some tips on how to optimize Postgresql configuration to avoid overusing disk IO. For my case it helped to reduce iowait from ~150 to less then 50 in average.

synchronous_commit. Since users score is not a critical parameter, it is safe to set sychronous_commit to off. The worst thing that can happen is that you loose several last transactions.

checkpoint_segments, checkpoint_timeout. Checkpoint causes all the modified data to be stored in actual table structures. Before the checkpoint happens, WAL guarantees data durability. If you have some frequently modified row, it is checkpointed each time. If checkpoints happen in your database too frequently it is a significant overhead. Increase the parameters to make checkpoint happen less frequently.

Background Writer. It writes dirty pages in background to lower amount of work for checkpoint. Again, some frequently modified value might be written each BW activity round. This is overkill for write-heavy database, because the value will be checkpointed anyways. I turned BW off at all, setting bgwriter_lru_maxpages = 0.

Hope it helps. Comments are extremely welcome.

Postgresql in Ubuntu distribution

Ubuntu has default limitation for shared memory about 32 MB. This is why (I guess) packaged Postgres has shared_buffers parameter set to modest 24 MB.

This is quite low value for a large DB and for modern hardware. There are numerous recommendations on how big this value should be. It makes sense to try setting this value and run your benchmarks.

To increase kernel shared memory limitation edit /etc/sysctl.conf and add or replace the following (about 110 MB in this example):

kernel.shmmax = 110000000

Then run

sudo sysctl -p

to make the settings be effective immediately.