Postgresql 滚动删除旧行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18187490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql delete old rows on a rolling basis?
提问by Arya
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?
我想删除一个表中超过 7 天的行。做这个的最好方式是什么？做一个每天晚上运行的 cron 工作，或者 PostgreSQL 是否有内置的功能来做这样的事情？
回答by Clodoaldo Neto
delete from the_table
where the_timestamp < now() - interval '7 days'
回答by jjanes
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.
PostgreSQL 目前没有内置的类似 cron 的功能，所以我会使用系统的 cron 来运行合适的 delete 语句。如果你已经在时间戳列上有一个 btree 索引，你最好比每晚更频繁地运行删除，一次取出小块。如果您没有索引，那么在非高峰时间每晚运行它会更好。
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.
如果这些没有你需要的性能，你可以尝试分区。但我会把它作为最后的手段，而不是第一手段。
回答by Igor Romanchenko
The easiest way (for me) to schedule a DB job is to create a cron job, that executes a SQL script using psql.
安排数据库作业的最简单方法（对我而言）是创建一个 cron 作业，它使用psql.
Hereyou can read about psql. Use -for -cto pass the SQL commands to psql.
Here你可以阅读关于psql. 使用-f或-c将 SQL 命令传递给psql.
Also it might be easier to write a PL/pgSQL function, that does your job and call it from psqlwith SELECT my_function();
此外，它可能是更容易编写一个PL / pgSQL函数，那你的工作和调用它psql与SELECT my_function();
