Message-ID: <5000E8FF.3080309@ringerc.id.au>
Date: Sat, 14 Jul 2012 11:35:27 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
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.
References: 
 <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
	<4FF70D17.3070309@pinpointresearch.com>
	<CAFXpGYa5oAfZW21u1eQqn1FR8TQOVFUW-HjK1nAECdgD8Vvhng@mail.gmail.com>
In-Reply-To: 
 <CAFXpGYa5oAfZW21u1eQqn1FR8TQOVFUW-HjK1nAECdgD8Vvhng@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------070903080604090201060702"

This is a multi-part message in MIME format.
--------------070903080604090201060702
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------070903080604090201060702
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 07/13/2012 03:50 PM, Stanislaw
      Pankevich wrote:<br>
    </div>
    <blockquote
cite="mid:CAFXpGYa5oAfZW21u1eQqn1FR8TQOVFUW-HjK1nAECdgD8Vvhng@mail.gmail.com"
      type="cite">
      <pre wrap="">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. </pre>
    </blockquote>
    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.<br>
    <br>
    PostgreSQL will <i>never</i> 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.<br>
    <br>
    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.<br>
    <br>
    The BASE jumper will always get there faster, but the abseiler will
    always get there alive.<br>
    <br>
    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.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------070903080604090201060702--