An idle thought

A few days ago there was a thread on one of our lists where someone
was suprised it took as much i/o to delete data as it took to insert
it in the first place. At first this does seem surprising but the fact
that Postgres stores its transaction information inline with the data
and does all i/o in blocks makes this inevitable.

http://archives.postgresql.org/pgsql-performance/2010-03/msg00141.php

However then I started thinking about this case and wondered if it
wouldn't be possible to optimize. One of the suggested optimizations
was to look at using TRUNCATE. But I wonder why it's necessary to use
a dedicated command. Shouldn't it be possible for the system to notice
this situation and do effectively the same thing itself?

I'm picturing storing a bit in the visibility map indicating that *no*
records are visible in a given page. If that bit is set then the page
is virtually empty even if it contains data. If anyone tries to load
the page they should just initialize a new page instead which will
overwrite it. That way a big batch delete just has to set a bunch of
flags in the visibility map to do the bulk delete.

There are a couple problems with the way I've described this idea
here. Firstly, if the deleting transaction hasn't committed yet or
later aborts then of course the records need to still be visible. So
either the visibility map would need an xmax for the page as a whole
or it would need to only be set after the page has actually been
vacuumed. Secondly there's the whole retail vacuum problem -- any
index entries referring to this page would be left dangling unless
there's some kind of retail vacuum or perhaps a page version number.

I'm not 100% sure this idea is workable but if it is it might make
batch deletes a lot less painful. Several orders of magnitude less i/o
whenever a single transaction deletes many rows.

--
greg