Hello,
today I stumbled across a interesting question about the order rows are dumped out while exporting a database with
pg_dump.I know questions like this are around this list sometimes, but I think this is a bit more special.
First of all I know that dumping a database is a somewhat nondeterministic process. It's hard to determine in which
orderobjects are dumped. If my understanding is correct when it's about dumping the rows from a specific table the rows
willappear in the dump in disk-order. This order is changed everytime there are updates to rows in that table and how
oftena vacuum occurs to release old row versions.
From some testing I "verified" this by experimentation - just created a new table with a known order of rows and dumped
it.The dump was ordered in the same way the rows were inserted. Same again with updating some of the rows those rows
appeardat the end of the dump. At last I vacuumed the database and updated some rows they appeared in the "spaces" the
previousupdated rows left behind. Exactly what I expected :-)
Now for my question - we have a case where rows are inserted in order and are updated rarely (about 10% of the rows and
oftenin order) and we are seeking for a solution to make the dump (e.g. the backup of this database) more random at
databaselevel (by influencing the on disk order?). Obvious way would be to update all rows randomly by software before
dumpingbut that would be a manual process. So anyone out there with some other ideas (I found this interesting as I'm
gettinga more insight view of my favourite database)?
Thanks in advance,
Jan
