PostgreSQL block sizes: Getting started ...

06.2013 / Category: / Tags: | |

I have been wondering a while about the optimal block size in PostgreSQL. Usually the default value of 8kb has proven to be beneficial for most applications. However, after listening to some talk about MonetDB, I got interested in testing various workloads for different block sizes – especially with blocksizes larger than PostgreSQL’s current maximum of 32k.
We came up with a prototype patch to support larger blocksizes for PostgreSQL, which can be downloaded from here: PATCH

Very large PostgreSQL blocks revisited

It has turned out to be pretty obvious that for a default workload as created by pgbench, larger blocks are not too beneficial. We did a test with typical block sizes of 2k, 4k, 8k, 18k and 1 MB. In all tests the results have been pretty much the same. The bottom line is: Supersize blocks are obviously never beneficial for the typical case.

Our first results are like that:

Samsung SSD 840, 500 GB            TPS

blocksize=2k                                     147.9
blocksize=4k                                     141.7
blocksize=8k                                     133.9
blocksize=16k                                   127.2
blocksize=1MB                                   42.5

All results were produced on a 4 core Intel i5 CPU with 2.67 GHz and 16 GB Ram. One Samsung SSD was serving as storage device.

Settings used for pgbench:
• Scale factor: 10
• Concurrent connections: 4
• Transactions: 10.000 / connection
• Fillfactor: 70%
• 128 MB shared buffers
• Default PostgreSQL config

The main interesting thing here is that using supersize blocks will basically destroy performance for a handful of reasons.
NOTE: This is a standard OLTP test.

Comments are closed.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram