pg_dump order of rows
От | jan |
---|---|
Тема | pg_dump order of rows |
Дата | |
Msg-id | e023dbd5b1442c270a30c8c849b55aec@dafuer.de обсуждение исходный текст |
Ответы |
Re: pg_dump order of rows
|
Список | pgsql-general |
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 dumop (e.g. the backup) more random at database level (by influencingthe on disk order)? Obvious way would be to update all rows randomly by software before dumpim
В списке pgsql-general по дате отправления: