Waiting for 9.6 – Allow per-tablespace effective_io_concurrency

On 8th of September, Alvaro Herrera committed patch:

Allow per-tablespace effective_io_concurrency
Per discussion, nowadays it is possible to have tablespaces that have
wildly different I/O characteristics from others.  Setting different
effective_io_concurrency parameters for those has been measured to
improve performance.
Author: Julien Rouhaud
Reviewed by: Andres Freund

There is this, not very well known, setting – effective_io_concurrency. This is used by PostgreSQL so that it can tell kernel to parallelize certain IO operations (bitmap heap scans currently only).

So far, it was one, global, setting, set to somewhat safe value of “1". But these days, it is really simple to have various disks with very different characteristics – for example, slow magnetic drive, and SSD drive.

Thanks to above commit we can now set effective_io_concurrency per tablespace, which will make better use of resources in such cases.

For example one can:

$ SHOW effective_io_concurrency ;
(1 ROW)
$ CREATE tablespace fast location '/mnt/ssd';
$ ALTER tablespace fast SET ( effective_io_concurrency = 10 );

This new setting will be, of course, visible in list of tablespaces:

$ \db+
                                            List OF tablespaces
    Name    | Owner  | Location  | Access privileges |            Options            |  SIZE   | Description
 fast       | depesz | /mnt/ssd  |                   | {effective_io_concurrency=10} | 0 bytes |
 pg_default | pgdba  |           |                   |                               | 41 MB   |
 pg_global  | pgdba  |           |                   |                               | 464 kB  |
(3 ROWS)

That's nice, and definitely useful, thanks guys.