1

I have a reports table which has literally millions, if not hundreds of millions of report records, which date back to 2011. I think it's slowing down my queries exponentially. I only want to keep reports from the past 6 months. I have a time column in reports table which gives the current time so I can use that to measure reports older than 6 months. So I am thinking about running some type of cron job on my ubuntu server in which the postgresql server is installed on. But I am looking for some guidance about going about this.

2

Recently I set up a similar archiving solution. The numbers and the target are a bit different (the latter being moving old data into an archive partition), but the idea is the same.

First, you will need an index on the timestamp, for quickly finding the rows to be deleted. To avoid excessive locking and blocking other processes as a consequence, this is preferably done with the CONCURRENTLY option.

Second, you have to keep your table tidy by vacuuming it regularly - if you have a relatively recent PostgreSQL version, there are chances that the database will take care of it with autovacuum.

The actual DELETE statement will be a rather simple one. In my case, I use a function for this, so that we can keep the database logic under version control. This may look like

CREATE OR REPLACE FUNCTION delete_function()
RETURNS void
LANGUAGE sql
AS $body$
DELETE FROM big_table
WHERE report_timestamp < now() - interval '6 months';
$body$;

You will need a script (say a bash script), which simply calls the statement, like

#!/bin/bash

psql {connection parameters} -c 'SELECT delete_function()'

The only thing that remains is setting up a cron job which calls this script at the desired intervals.

There is still one thing to consider, namely that the first delete would be a huge one. I would prevent this by deleting the big amount of old data in suitably sized chunks, again for avoiding blocking other processes that want to access the table. The size of these chunks can be very different based on your circumstances. Note that this is more or less a manual process, unless you have already a 'chunker' in use (as we have).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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