Re: DELETE vs TRUNCATE explanation

Hi,

I work with Daniel Farina and was the other engineer who "discovered" this, once again. That is, I got bit by it and have been running TRUNCATE on my test suites for years.

On Thursday, July 12, 2012 at 12:15 PM, Jeff Janes wrote:

> On Wed, Jul 11, 2012 at 3:51 PM, Daniel Farina <daniel(at)heroku(dot)com (mailto:daniel(at)heroku(dot)com)> wrote:
> >
> > Nope. I don't. But an exact crossover is a level of precision I don't
> > really need, because here are where things stand on a completely
> > unremarkable test suite on the closest project to me that meets the
> > "regular web-app" profile case:
> >
> > With en-masse DELETE:
> > rake 41.89s user 3.08s system 76% cpu 58.629 total
> >
> > With TRUNCATE:
> > rake 49.86s user 2.93s system 5% cpu 15:17.88 total
> >
> > 15x slower. This is a Macbook Air with full disk encryption and SSD
> > disk with fsync off, e.g. a very typical developer configuration.
> >
>
>
> What is shared_buffers?

1600kB

Not sure this will make much difference with such small data, but of course I could be dead wrong here.
>
> > This is a rather small schema -- probably a half a dozen tables, and
> > probably about a dozen indexes. This application is entirely
> > unremarkable in its test-database workload: it wants to load a few
> > records, do a few things, and then clear those handful of records.
> >
>
>
> How many rounds of truncation does one rake do? I.e. how many
> truncations are occurring over the course of that 1 minute or 15
> minutes?
>
>

All tables are cleared out after every test. On this particular project, I'm running 200+ tests in 1.5 minutes (or 30 seconds with DELETE instead of TRUNCATE). For another, bigger project it's running 1700+ tests in about a minute. You can do the math from there.

I'd say this is not atypical at all, so I too encourage teaching TRUNCATE about small tables and optimizing for that, as well as a section in the docs about postgres tweaks for test suites. I'm sure many people have done independent research in this area, and it'd be great to have it documented in one place.

-Harold
>
>
> Cheers,
>
> Jeff
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org (mailto:pgsql-performance(at)postgresql(dot)org))
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>