Does Postgresql delete old lines by rolling?

Question

I want to delete rows in one of my tables, whose age exceeds 7 days. What is the best way to do this? to do a cron job that runs every night, or does PostgreSQL have built-in functions to do something like that?

Answer 1

delete from the_table where the_timestamp < now() - interval '7 days' 

Answer 2

PostgreSQL does not currently have built-in functions like cron, so I would use the cron system to run a suitable delete instruction. If you already have a btree index in the timestamp column, you can also start the deletion much more often than at night by taking out small pieces at a time. If you do not have an index, then working at night after hours would be better.

If you do not have the required performance, you can try to split. But I will do it as a last resort, and not the first resort.

Answer 3

The easiest way (for me) to schedule a database task is to create a cron task that executes an SQL script using psql .

Here you can read about psql . Use -f or -c to pass SQL commands to psql .

It may also be easier to write a PL / pgSQL function that does your work and calls it from psql with SELECT my_function();