> Scara Maccai <m_lists@yahoo.it> 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
