Re: [ADMIN] Fast Deletion For Large Tables

Raymond,

Partitioned tables would solve your issue since you
could just truncate a partiotion in order to delete the
unneeded data. Unfortunately they are not available in
PostgreSQL. But maybe in a future release.

Unfortunately you also cannot use a UNION ALL in a view
to fake partitions.

So only several changes to your applicatin would allow
you to partion your data. But depending on your
situation it may be worthwhile. E.g. if you always
want to keep one weeks worth of data and purge a day at
a time you could have 7 tables instead of one.
table1_mon, table1_tue, table1_wed, etc. And you could
select from all of them with a UNION ALL.

There is no rowid in PostgreSQL. So while adding an
additional column with a sequence would help as long as
you have an index. Just indexing your date column
maybe easier. But then you pay with slower inserts or
the time and effort to build the index.

Regards,
Nikolaus Dilger

On Wed, 02 October 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?
> Thank Q!