Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

On 07/13/2012 03:50 PM, Stanislaw Pankevich wrote:
> MySQL: the fastest strategy for cleaning databases is truncation with
> following modifications:
> 1) We check is table is not empty and then truncate.
> 2) If table is empty, we check if AUTO_INCREMENT was changed. If it
> was, we do a truncate.
>
> For MySQL just truncation is much faster than just deletion.
You're talking about MySQL like it's only one database. Is this with
MyISAM tables? InnoDB? Something else? I don't see any mention of table
formats in a very quick skim of the discussion you linked to.

PostgreSQL will /never/ be able to compete with MyISAM on raw speed of
small, simple operations. There might things that can be made faster
than they are right now, but I really doubt it'll ever surpass MyISAM.

My mental analogy is asking an abseiler, who is busy clipping in and
testing their gear at the top of a bridge, why they aren't at the bottom
of the canyon with the BASE jumper yet.

The BASE jumper will always get there faster, but the abseiler will
always get there alive.

If you're talking about InnoDB or another durable, reliable table
structure then I'd be interested in the mechanics of what MySQL's
truncates are doing.

--
Craig Ringer