Thread: truncate vs. delete

truncate vs. delete

From

Emi Lu

Date:

24 July 2008, 13:34:28

Good morning,

If I remember correctly, "delete" does not release space, while truncate 
will.

I have an option now

(1) Use object creator(with create/drop permission which I do not need 
in my cronjob script) to truncate table1(>100,000 recs) records

(2) Use user1(has r/w only) to delete from table1, then vacuum it

May I know how inefficient "delete from" comparing to truncate please?

Thanks a lot!

Re: truncate vs. delete

From

Emi Lu

Date:

24 July 2008, 13:48:00

I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html





Emi Lu wrote:
> Good morning,
> 
> If I remember correctly, "delete" does not release space, while truncate 
> will.
> 
> I have an option now
> 
> (1) Use object creator(with create/drop permission which I do not need 
> in my cronjob script) to truncate table1(>100,000 recs) records
> 
> (2) Use user1(has r/w only) to delete from table1, then vacuum it
> 
> May I know how inefficient "delete from" comparing to truncate please?
> 
> Thanks a lot!
> 
> 
>

Re: truncate vs. delete

From

"A. Kretschmer"

Date:

24 July 2008, 13:54:27

am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:
> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html

Not realy, for instance, pg can rollback a truncate, and a sequence are
not reset.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: truncate vs. delete

From

Lennin Caro

Date:

24 July 2008, 13:58:17

The truncate is more fast to delete, the truncate command not scan the table

http://www.postgresql.org/docs/8.3/static/sql-truncate.html


--- On Thu, 7/24/08, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

> From: A. Kretschmer <andreas.kretschmer@schollglas.com>
> Subject: Re: [SQL] truncate vs. delete
> To: pgsql-sql@postgresql.org
> Date: Thursday, July 24, 2008, 1:53 PM
> am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu
> folgendes:
> > I found a link for SQL Server, it applies to
> PostgreSQL 8.0.x as well?
> >
> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html
> 
> Not realy, for instance, pg can rollback a truncate, and a
> sequence are
> not reset.
> 
> 
> Andreas
> -- 
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr:
> -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA  
> http://wwwkeys.de.pgp.net
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Re: truncate vs. delete

From

Emi Lu

Date:

24 July 2008, 14:01:52

A. Kretschmer wrote:
> am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:
>> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
>> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html
> 
> Not realy, for instance, pg can rollback a truncate, and a sequence are
> not reset.
> 
> 
Thank you. I am quite sure that I will not use "delete" now.
Now I a question about how efficient between

(1) truncate a big table (with 200, 000)    vacuum it (optional?)    drop primary key    load new data    load primary
ke   vacuum it
 

(2) drop table (this table has no trigger, no foreign key)    re-create table (without primary key)    load new data
setupprimary key    vacuum it
 

suggestions PLEASE?

Thanks a lot!

Re: truncate vs. delete

From

"A. Kretschmer"

Date:

24 July 2008, 14:17:34

am  Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes:
> A. Kretschmer wrote:
> >am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:
> >>I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
> >>http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html
> >
> >Not realy, for instance, pg can rollback a truncate, and a sequence are
> >not reset.
> >
> >
> Thank you. I am quite sure that I will not use "delete" now.
> Now I a question about how efficient between
> 
> (1) truncate a big table (with 200, 000)
>     vacuum it (optional?)

not required


>     drop primary key
>     load new data
>     load primary ke
>     vacuum it

analyse it, instead vacuum.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: truncate vs. delete

From

"Scott Marlowe"

Date:

24 July 2008, 14:38:17

n Thu, Jul 24, 2008 at 7:53 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:
>> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
>> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html
>
> Not realy, for instance, pg can rollback a truncate, and a sequence are
> not reset.

Also you can truncate a table with foreign key references (using
cascade), something SQL Server apparently can't do either.

Re: truncate vs. delete

From

Emi Lu

Date:

24 July 2008, 14:42:27

A. Kretschmer wrote:
> am  Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes:
>> A. Kretschmer wrote:
>>> am  Thu, dem 24.07.2008, um  9:47:48 -0400 mailte Emi Lu folgendes:
>>>> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well?
>>>> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html
>>> Not realy, for instance, pg can rollback a truncate, and a sequence are
>>> not reset.
>>>
>>>
>> Thank you. I am quite sure that I will not use "delete" now.
>> Now I a question about how efficient between
>>
>> (1) truncate a big table (with 200, 000)
>>     vacuum it (optional?)
> 
> not required
> 
> 
>>     drop primary key
>>     load new data
>>     load primary ke
>>     vacuum it
> 
> analyse it, instead vacuum.

It gets more and more clear to me know!

I guess I need only do analyze(primary key column) after loading data.
The new picture will be:
. truncate table
. drop primary key
. load data
. set primary key
. analyze interesting columns

Re: truncate vs. delete

From

Shane Ambler

Date:

24 July 2008, 14:43:42

Emi Lu wrote:

> Thank you. I am quite sure that I will not use "delete" now.
> Now I a question about how efficient between
> 
> (1) truncate a big table (with 200, 000)
>     vacuum it (optional?)
>     drop primary key
>     load new data
>     load primary key
>     vacuum it
> 
> (2) drop table (this table has no trigger, no foreign key)
>     re-create table (without primary key)
>     load new data
>     setup primary key
>     vacuum it
> 
> suggestions PLEASE?
> 
> Thanks a lot!
> 

Shouldn't be a noticeable difference either way.

A quick test -

postgres=# \timing
Timing is on.
postgres=# create table test (id serial primary key,data integer);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for 
serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"test_pkey" for table "test"
CREATE TABLE
Time: 26.779 ms
postgres=# insert into test (data) values (generate_series(1,200000));
INSERT 0 200000
Time: 4604.307 ms
postgres=# truncate table test;
TRUNCATE TABLE
Time: 31.278 ms
postgres=# insert into test (data) values (generate_series(1,200000));
INSERT 0 200000
Time: 4545.386 ms
postgres=# drop table test;
DROP TABLE
Time: 45.261 ms
postgres=#

shows a 10ms difference between truncate and drop.

-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz