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.

[Craig Ringer <ringerc@xxxxxxxxxxxxx>]

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