Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

up vote 7 down vote accepted

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

CREATE OR REPLACE FUNCTION trf_keep_row_number_steady()
RETURNS TRIGGER AS
$body$
BEGIN
    -- delete only where are too many rows
    IF (SELECT count(id) FROM log_table) > rownum_limit
    THEN 
        -- 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;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_keep_row_number_steady 
AFTER INSERT ON log_table
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
2  
+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

Your Answer

 
discard

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.