Fast Deletion For Large Tables

Поиск
Список
Период
Сортировка
От Raymond Chui
Тема Fast Deletion For Large Tables
Дата
Msg-id 3D9B38F7.12A7AEA3@noaa.gov
обсуждение исходный текст
Ответы Re: Fast Deletion For Large Tables  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: [GENERAL] Fast Deletion For Large Tables  (Roland Roberts <roland@astrofoto.org>)
Re: [GENERAL] Fast Deletion For Large Tables  (Roland Roberts <roland@astrofoto.org>)
Re: Fast Deletion For Large Tables  (Hubert depesz Lubaczewski <depesz@depesz.pl>)
Список pgsql-admin
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!

Вложения

В списке pgsql-admin по дате отправления:

Предыдущее
От: Kostis
Дата:
Сообщение: idle in transaction with mod_perl
Следующее
От: Wojciech Bernacki
Дата:
Сообщение: Replication, High availability