Menu
I want to delete rows on one my tables that are more than 7 days old. What is the best way to do this? to make a cron job that runs every night or does PostgreSQL have built in features for doing something like this?
225
PostgreSQL does not currently have a built-in cron-like functionality, so I'd use the system's cron to run a suitable delete statement. If you already have a btree index on the timestamp column, you might as well run the delete much more frequently than nightly, taking out small chunks at a time.
First, specify the table from which you want to delete data in the DELETE FROM clause. Second, specify which rows to delete by using the condition in the WHERE clause. The WHERE clause is optional. However, if you omit it, the DELETE statement will delete all rows in the table.
In PostgreSQL, a cascade means that a delete or update of records in a parent table will automatically delete or update matching records in a child table where a foreign key relationship is in place.
Dapper is useful for querying different types of RDBMS, not only PostgreSQL.
delete from the_table where the_timestamp < now() - interval '7 days' PostgreSQL does not currently have a built-in cron-like functionality, so I'd use the system's cron to run a suitable delete statement. If you already have a btree index on the timestamp column, you might as well run the delete much more frequently than nightly, taking out small chunks at a time. If you don't have the index, then running it nightly during off-peak times would be better.
If these don't have the performance you need, you could try partitioning. But I'd do that as a last resort, not a first resort.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
