Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 8 down vote accepted

You can define a trigger to maintain your desired row number:

CREATE OR REPLACE FUNCTION trf_keep_row_number_steady()
    -- delete only where are too many rows
    IF (SELECT count(id) FROM log_table) > rownum_limit
        -- I assume here that id is an auto-incremented value in log_table
        DELETE FROM log_table
        WHERE id = (SELECT min(id) FROM log_table);
    END IF;
LANGUAGE plpgsql;

CREATE TRIGGER tr_keep_row_number_steady 
FOR EACH ROW EXECUTE PROCEDURE trf_keep_row_number_steady();

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.

EDIT: 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 max(timestamp) < CURRENT_DATE - 1 year). You can use your timestamp (or a derived date) as condition for range partitioning.

But be careful before discarding old logs. Are you sure you will never need those?

share|improve this answer
we can execute it periodically, and we're sure we won't need them once the table gets big enough to require this, I'm just trying to automate DB maintenance as much as possible :) – Jharwood Jun 29 '12 at 10:38
also i was hoping that postgres could tell which one was older itself, but if not as we don't have ID's it could use our date created timestamp field "2012-06-22 17:17:52.692514" – Jharwood Jun 29 '12 at 10:43
@Jharwood - edited my answer. Please tell me if you need further details. – dezso Jun 29 '12 at 10:57
@dexso should be perfect, thank you – Jharwood Jun 29 '12 at 11:17
+1 on the partitioning suggestion. If you want to go with a count without the extreme overhead of scanning the table each time, you could use pg_class.reltuples for an approximatioinor you could use triggers to maintain a count in a "control" table. – kgrittn Jun 30 '12 at 18:06

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.

CREATE or replace FUNCTION activitylogcleanup(_MaxRows int) RETURNS void
    LANGUAGE plpgsql
    AS $$
   minid    int;
    SELECT logid into minid FROM activitylogapplication 
     order by logid desc limit 1 OFFSET _MaxRows;

    if not found then 
    END IF; 

    Delete from activitylogapplication where logid < minid;

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.