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.

I'm using PostgreSQL (8.4) to store data produced by an application making frequent inserts (in the table structure described below).

The database keeps growing with time and, since the newer data is more relevant than the older data (in this particular application), deleting the older rows is a reasonable solution (either based on lower id or older input_datetime, which is more or less the same).

To prevent issues related from this database (the only database running on this server) from affecting the rest of the system, I've put the PostgreSQL data directory on its own partition (ext3, on a Linux system). Nevertheless, when this partition becomes full, this causes a number of problems.

I'm thinking of deleting older data regularly (e.g. DELETE FROM data_group WHERE id <= ... via a cron job) to deal with this.

Firstly, my understanding of VACUUM (as performed by auto-vacuum, which is on) is that, while it doesn't necessarily give back the disk space to the OS (like VACUUM FULL would), it still allows some new data to be inserted within the disk space already used (that is, the DELETEs don't necessarily affect the file size, but they still free space in PostgreSQL's own data structures). Is this correct? (I've noticed VACUUM FULL caused a few problems with the application itself, probably because of the locks it uses.)

If so, it also appears that SELECT pg_database_size('my_database') reflects the size used on disk, which doesn't necessarily reflect what's available for further inserts. Is there another way to estimate how much space is available for new inserts?

In addition, when it's too late and the partition is filled at 100%, running this DELETE statement causes this error and crashes the PostgreSQL service:

PANIC: could not write to file "pg_xlog/xlogtemp.7810": No space left on device

The PostgreSQL daemon stopping is of course a major issue (and there is no other disk to move the cluster to on this machine).

Are there general strategies to prevent this sort of problem from occurring (knowing that disk space is constrained within a given partition, but that it can be acceptable to delete older data)? I would like to automate as much of this as possible, without root or postgres (or PostgreSQL admin) intervention.


CREATE TABLE data_group (
    id SERIAL PRIMARY KEY,
    name TEXT,
    input_datetime TIMESTAMPTZ
);

CREATE TABLE data_item (
    id SERIAL PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES data_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
    position INTEGER NOT NULL,
    data BYTEA
);
share|improve this question

1 Answer 1

up vote 3 down vote accepted

On one hand, you can have a look at one of my previous answers to see how you can keep a table size more or less steady. There you will find a solution with triggers - of course, this can be solved using a cron job as well. In the latter case I would first check if the row number exceeded a certain limit and the either delete the oldest rows or drop a partition.

On the other hand, as you already noticed, one has to take care of the disk space where pg_xlog is. When it gets full, it is not that easy to recover... But checking your database settings you can have a fair estimation how much space you need:

There will always be at least one WAL segment file, and will normally not be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files. Each segment file is normally 16 MB (though this size can be altered when building the server). You can use this to estimate space requirements for WAL. Ordinarily, when old log segment files are no longer needed, they are recycled (renamed to become the next segments in the numbered sequence). If, due to a short-term peak of log output rate, there are more than 3 * checkpoint_segments + 1 segment files, the unneeded segment files will be deleted instead of recycled until the system gets back under this limit.

If you don't have replication set up, the maximum is 3 * checkpoint_segments + 1 (times 16 MB). A typical replicationless setup will need something under 10 GB for pg_xlog, I think.

share|improve this answer
    
I've not familiarized myself with all of the changes between versions, but do you think there's any storage algorithm changes that would compress the data and log files better in the newer versions? –  swasheck Jul 23 '13 at 15:11
    
@swasheck Nothing that I'm aware of... I don't know though if compressing WAL files makes much sense as it needs some computation to (de)compress them and in turn this affects recovery times adversely - while disk space is relatively cheap. –  dezso Jul 23 '13 at 15:20
    
Thanks. I guess I'm not actually meaning "compression" but just storage algorithm tweaks that take less space on disk. Good point about WAL files, though. –  swasheck Jul 23 '13 at 15:22
    
This sounds like a good approach. Do you know if there would be a way to do this based on the space available on the partition? I don't know whether there is a way to know the actual space used (by the data that's not deleted) or what could potentially be available. (I could certainly put the pg_xlog on a different partition too.) –  Bruno Jul 25 '13 at 9:39
1  
@Bruno you may be interested in this and the pg_freespacemap module mentioned there. –  dezso Jul 25 '13 at 20:58

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.