> Scara Maccai <m_lists@xxxxxxxx> wrote: > >> What am I doing wrong? I didn't exactly follow the full sequence but it sounded like what's happening is that Postgres is noticing all these empty pages from earlier deletes and reusing that space. That's what it's designed to do. As Kevin said, there's no guarantee that tuples will be read back in the order you inserted them. You might want to check your assumptions about the performance. If you're deleting large batches the new tuples might not be where you expect them to be in the table but they should still all end up in chunks mostly in order. They might be located together closely enough that they might still perform as if they're clustered. If you're really insistent that they be clustered you would have to make sure there are no free space map entries for them. This means never running vacuum on the table. That will cause massive problems down the line but if you periodically run CLUSTER you might be able to engineer things to avoid them since cluster effectively does a vacuum too. Keep in mind this will mean your table is massively bloated which will make sequential scans much slower. Also, keep in mind that Postgres is moving in the direction of maintaining the free space map more automatically. It will get harder and harder to ensure that space doesn't get reused. I'm not even sure some features in 8.3 (HOT) and 8.4 (new FSM) don't already make it nearly impossible. I've certainly never heard of anyone else trying to. A better option you might consider is to use a separate table for the re-ordered tuples. If you never insert into the re-ordered table except in the final order you want (and in the same connection), and never update or delete records, then it should work. You could even do this using partitions, so you have a single table with the dynamically added records in one partition and then you re-order the records into a new partition and swap it in to replace the old partition. Whatever you do you'll definitely still want an ORDER BY clause on your queries if you need them in a certain order. Running the queries you're doing is fine for seeing what order they're in on disk but there are several reasons why they might still come out out of order even if you never run vacuum and always insert them in order. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
