Skip site navigation (1) Skip section navigation (2)

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.

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Stanislaw Pankevich <s(dot)pankevich(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: 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.
Date: 2012-07-14 03:35:27
Message-ID: 5000E8FF.3080309@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Craig RingerDate: 2012-07-14 05:05:54
Subject: Re: Any tool/script available which can be used to measure scalability of an application's database.
Previous:From: Craig RingerDate: 2012-07-14 03:28:26
Subject: Re: query overhead

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group