Re: Benchmark shows very slow bulk delete

On Wed, 27 Jan 2010, Thom Brown wrote:
> Had a quick look at a benchmark someone put together of MySQL vs PostgreSQL,
> and while PostgreSQL is generally faster, I noticed the bulk delete was very
> slow: http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html
>
> Is this normal?

On the contrary, TRUNCATE TABLE is really rather fast.

Seriously, the Postgres developers, when designing the system, decided on
a database layout that was optimised for the most common cases. Bulk
deletion of data is not really that common an operation, unless you are
deleting whole categories of data, where setting up partitioning and
deleting whole partitions would be sensible.

Other complications are that the server has to maintain concurrent
integrity - that is, another transaction must be able to see either none
of the changes or all of them. As a consequence of this, Postgres needs to
do a sequential scan through the table and mark the rows for deletion in
the transaction, before flipping the transaction committed status and
cleaning up afterwards.

I'd be interested in how mysql manages to delete a whole load of rows in
0.02 seconds. How many rows is that?

(Reading in the comments, I saw this: "The slow times for Postgresql Bulk
Modify/Bulk Delete can be explained by foreign key references to the
updates table." I'm not sure that fully explains it though, unless there
are basically zero rows being deleted - it's hardly bulk then, is it?)

Matthew

--
People who love sausages, respect the law, and work with IT standards
shouldn't watch any of them being made. -- Peter Gutmann