From: Craig Ringer <ringerc@ringerc.id.au>
To: Stanislaw Pankevich <s.pankevich@gmail.com>
Cc: pgsql-performance@postgresql.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: Sat, 14 Jul 2012 11:35:27 +0800
Message-ID: <5000E8FF.3080309@ringerc.id.au> (raw)
In-Reply-To: <CAFXpGYa5oAfZW21u1eQqn1FR8TQOVFUW-HjK1nAECdgD8Vvhng@mail.gmail.com>
References: <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
<4FF70D17.3070309@pinpointresearch.com>
<CAFXpGYa5oAfZW21u1eQqn1FR8TQOVFUW-HjK1nAECdgD8Vvhng@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ringerc@ringerc.id.au, s.pankevich@gmail.com
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.
In-Reply-To: <5000E8FF.3080309@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox