Basically part of our Postgresql table is used to keep server access logs, and as such sometimes during production this can get pretty large. is there any way of setting in postgresql to have a maximum number of records a table can have and to push off the oldest record?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You can define a trigger to maintain your desired row number:
This is probably not the best performing option, but once you reach the limit, it will never be exceeded. If there is space for fluctuation, then you can check the row number periodically and delete excess rows from the beginning.
If you have really large logs (say a million per month) than partitioning can be the easiest solution. You can then simply drop the unnecessary tables (say where
But be careful before discarding old logs. Are you sure you will never need those?
I created this proc and run it from PG Agent (or windows job or cron job depending). I can have more rows, this just keeps my log table not too big. Saves the overhead of a trigger.