While preparing a replication test setup with 9.0beta1 I noticed strange
page allocation patterns which Andrew Gierth found interesting enough to
report here.
I've written a simple tool to generate traffic on a database [1], which
did about 30 TX/inserts per second to a table. Upon inspecting the data
in the table, I noticed the expected grouping of tuples which came from
a single backend to matching pages [2]. The strange part was that the
pages weren't completely filled but the backends seemed to jump
arbitrarily from one page to the next [3]. For the table in question
this resulted in about 10% wasted space.
After issuing a VACUUM on the table the free space map got updated (or
initialized?) and the backends used the remaining space in the pages,
though the spurious page allocation continued.
best regards,
Michael
[1] https://workbench.amd.co.at/hg/pgworkshop/file/dc5ab49c99bb/pgexerciser
[2] E.g.:
(0,1) TX1
(0,2) TX5
(0,3) TX7
..
(1,1) TX2
(1,2) TX6
(1,3) TX9
etc.
[3] http://nopaste.narf.at/show/55/
Optimal usage seems to be 136 tuples per page for the table in question.