Postgres routine (and not so routine) maintenance

Postgres is our DB of choice. We run all our vTrack and vWork systems on it. We have lately been developing data archiving and culling strategies to manage data growth and disk consumption.

I’ve found a few hints that are best shared.

Culling Old Data to Recover Disk Space

The initial thought is to perform a quick delete of the old data, a-la:

DELETE FROM table1 where date < [some date in the past]

Nothing wrong with that. However, it doesn’t recover any disk space. The natural instinct is to follow it with a vacuum:

VACUUM VERBOSE ANALYZE table1

I like to throw verbose and analyze in there too. But doh, this doesn’t recover any disk space either, it just marks the space used by the deleted as free, making it available for new data later.

Which leads to what the Postgres manual says about the subject:

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

Ok, let’s do a VACUUM FULL then:

VACUUM FULL VERBOSE ANALYZE table1

Good; Yay; finally some improvement. But that took bloody ages on the massive table that I just removed a bazillion rows from.

So, we devised a cunning plan, loosely based on what another page in the Postgres manual says about recovering disk space. In the manual it says you should perform a TRUNCATE on the table if you want to clean it up quickly, which got me thinking. Instead maybe we should just copy all of the keep-able data into a new table and blow away the old one (along with all the cull-able data).

In it’s basic form:

CREATE TABLE table1new
SELECT * INTO table1new FROM table1 WHERE date >= [some date in the past]
DROP TABLE table1
ALTER TABLE table1new RENAME TO table1

Because SELECT INTO doesn’t bring across any of the table meta-data, we then put the correct restrictions on the columns manually:

ALTER TABLE table1 ALTER COLUMN column2 SET NOT NULL
ALTER TABLE table1 ALTER COLUMN column3 SET NOT NULL
....

… recreate primary keys and indexes:

ALTER TABLE table1 ADD CONSTRAINT table1_pkey PRIMARY KEY(column1)")
CREATE UNIQUE INDEX index_table1_on_column2_and_column3 ON table1 USING btree (column2, column3)
....

… find out the max integer for sequence re-creation. A bit of psuedo code here, depends on your scripting language (we wrap all this stuff around Ruby/Rails and ActiveRecord):

max_id = SELECT max(column1) FROM table1

Then re-create the sequence:

CREATE SEQUENCE table_column1_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START #{max_id+1} CACHE 1 OWNED BY table1.column1")
ALTER TABLE table1 ALTER COLUMN column1 SET DEFAULT nextval('table1_column1_seq'::regclass)

All of this work just to save some disk space? Not to mention the fact that you are dropping a table, which has some risk to it - especially if everything breaks half way through through the process.

It sounds scary and seems like quite a lot of work, but the elapsed run time difference is pretty huge.

When doing a DELETE FROM followed by a VACUUM FULL, our test system took 130 seconds. In comparison, doing a CREATE TABLE, SELECT INTO, DROP TABLE and RENAME takes around 45 seconds. This is on a very small table (2M rows) by our standards.

Another difference between the two methods is that the first method keeps the table available for reads. Writes will be locked out because a VACUUM FULL takes out an exclusive lock on the table. The second method drops the table entirely, so an outage would be required for any services or daemons which are accessing that table.

If you think about it some more though, the fact that you are having to cull data out of the table means that it is probably a write-heavy table. This indicates that an outage would have been necessary anyway, to perform a VACUUM FULL. Given this, you might as well perform the faster (albeit more complicated) form of the cull.

A final note about VACUUM FULL and indexes

I also stumbled upon another little gem while seeing just how much disk we could save. If you do a VACUUM FULL it doesn’t also hoover the indexes.

Quote, from here:

The FULL option does not shrink indexes; a periodic REINDEX is still recommended. In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.

Pretty much says it all really. And it brings me back to the previous section about copying the data into a new table, and re-creating the indexes from scratch.

If you want to save a few MB without being scary however, a REINDEX might be a good command to keep in mind.