Skip site navigation (1) Skip section navigation (2)

Re: [ADMIN] Fast Deletion For Large Tables

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Raymond Chui <raymond(dot)chui(at)noaa(dot)gov>
Cc: <pgsql-admin(at)postgresql(dot)org>, <pgadmin-support(at)postgresql(dot)org>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: [ADMIN] Fast Deletion For Large Tables
Date: 2002-10-04 15:34:58
Message-ID: 20021004083355.M36970-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-adminpgsql-general
On Wed, 2 Oct 2002, Raymond Chui wrote:

> I have some tables with huge data.
> The tables have column timestamp and float.
> I am try to keep up to 6 day of data values.
> What I do is execute SQL below from crontab (UNIX to
> schedule commands).
>
> BEGIN;
> DELETE FROM table_1 WHERE column_time < ('now'::timestamp - '6
> days'::interval);
> .....
> DELETE FROM table_n WHERE column_time < ('now'::timestamp - '6
> days'::interval);
> COMMIT;
>
>
> Everything is running fine, except take long time to finish.
> Because some tables stored values from 50,000 to 100,000 rows
> Some deletion need to deleted up to 45,000 rows.
>
> So I am thinking just delete the rows by their row number or row ID,
> like
>
> DELETE FROM a_table WHERE row_id < 45000;
>
> I know there is row_id in Oracle.
> Is there row_id for a table in Postgres?

Not really of that sort IIRC Oracle's row_id definition, although you
could probably fake something with a sequence.



In response to

pgsql-admin by date

Next:From: Roland RobertsDate: 2002-10-04 16:24:09
Subject: Re: [GENERAL] Fast Deletion For Large Tables
Previous:From: mallahDate: 2002-10-04 14:43:11
Subject: Re: errors reloading with pg_dump

pgadmin-support by date

Next:From: Roland RobertsDate: 2002-10-04 16:24:09
Subject: Re: [GENERAL] Fast Deletion For Large Tables
Previous:From: Shridhar DaithankarDate: 2002-10-04 14:34:20
Subject: Re: Fast Deletion For Large Tables

pgsql-general by date

Next:From: Stephan SzaboDate: 2002-10-04 15:37:49
Subject: Re: Inheritance: delete parent deletes children
Previous:From: Justin CliftDate: 2002-10-04 15:21:11
Subject: Re: PostgreSQL Website : advocacy.postgresql.org

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group