Keep only the last 5 rows per object in an audit table

Question

I have a web application backed by a Postgres (v11) database and a main table where each row in the table may be seen as an object and each column is a field of the object.

So we have:

| id | name | field1 | field2| .... | field 100|
-----------------------------------------------
| 1  | foo  | 12.2   | blue  | .... | 13.7     |
| 2  | bar  | 22.1   | green | .... | 78.0     |

The table was created using:

CREATE TABLE records(
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(50),
  field1 NUMERIC,
  field2 VARCHAR(355),
  field100 NUMERIC);

Now I have an audit table which stores updates to every field of every object. The audit table is defined as:

| timestamp | objid | fieldname | oldval | newval | 
-----------------------------------------------
| 1234      | 1     | field2    | white  | blue   |
| 1367      | 1     | field1    | "11.5" | "12.2" |
| 1372      | 2     | field1    | "11.9" | "22.1" |
| 1387      | 1     | name      | baz    | foo    |

The table was created using:

CREATE TABLE audit_log(
  timestamp TIMESTAMP,
  objid VARCHAR (50) REFERENCES records(id),
  fieldname VARCHAR (50) NOT NULL,
  oldval VARCHAR(355),
  newval  VARCHAR(355));

oldval/newval are kept as varchar since they are purely for auditing purpose so the actual datatype does not really matter.

For obvious reasons, this table has gotten huge over the last few years or so so I wanted to delete some old data. Someone suggested keeping only the last 5 updates for every object (i.e. the UI can then show the last 5 updates from the audit table).

I understand you can get this using a GROUP BY and a LIMIT but the problem is that I have a million+ objects, with some having been updated a thousand+ times while others have barely a few updates over multiple years. And the audit log is being very read/write heavy (as can be expected).

What would be the best way to delete all entries which are older than the 5th latest update for each object (of course, ideally I'll move that out to some secondary storage)?

Answer 1

There a few ingredients to the solution:

• The PostgreSQL row_number function. Unfortunately this is a "window function" and can't be used in the where clause.
• A common table expression (CTE): "with T as (...some SQL...) ...do something with T..."
• The PostgreSQL ctid field, which uniquely identifies a row in a table.

You use the CTE to create a logical table that includes both ctid and row_number. Then you reference it from a delete statement. Something like this:

with t as (
    select ctid, row_number() over (partition by objid)
    from the_audit_table
)
delete from the_audit_table
where ctid in (select ctid from t where row_number > 5)

If you're concerned about the effect of doing this all at once, then just run a lot of smaller transactions on some subset of the objid space. Or (if you're going to wind up deleting 99% of the rows) create a new table, change row_number > 5 to row_number <= 5 and make it into an insert into the new table, then replace the old table with the new one.

Test in QA first! :-)

Answer 2

If you are going to keep just 5 records in groups that might contain thousands, a more efficient approach would to use a temporary table.

First, create a new table on the fly by selecting the records that you want to retain, using the CREATE TABLE AS syntax. Analytical functions make it easy to select the records.

CREATE TABLE audit_log_backup AS
SELECT mycol1, mycol2, ... 
FROM (
    SELECT a.*, ROW_NUMBER() OVER(PARTITION BY objid ORDER BY timestamp DESC) rn
    FROM audit_log a
) x WHERE rn <= 5

Then, just TRUNCATE the original table and reinsert saved data :

TRUNCATE audit_log;
INSERT INTO audit_log SELECT * FROM audit_log_backup;
--- and eventually...
DROP TABLE audit_log_backup;

As explained in the documentation, truncating a big table is much more efficient than deleting from it :

TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

One thing to be careful about, as commented by Erwin Brandsetter, is that this technique creates a race condition where records added (or updated) after the copy was started will not be taken into account. One solution would be to perform all operations in a single transaction, while locking the table :

BEGIN WORK;
LOCK TABLE audit_log IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE audit_log_backup AS ...;
TRUNCATE audit_log;
INSERT INTO audit_log SELECT * FROM audit_log_backup;
COMMIT WORK;

The downside is that this will put on wait any session that would try to access the table while the transaction is in progress.

---

Disclaimer : whatever you do, make sure to properly backup the whole table before you starting purging it !

Answer 3

You could use a plain row_number(), similar to what @Willis suggested, improved with ORDER BY:

WITH cte AS (
    SELECT ctid
         , row_number() OVER (PARTITION BY objid ORDER BY timestamp DESC) AS rn
    FROM   audit_log
   )
DELETE FROM audit_log
USING  cte
WHERE  cte.ctid = tbl.ctid
AND    cte.row_number > 5;

That's going to take a long time for your big table. You can have that faster with a multicolumn index on audit_log(objid, timestamp DESC) and this query:

WITH del AS (
   SELECT x.ctid
   FROM   records r
   CROSS LATERAL (
      SELECT a.ctid
      FROM   audit_log a
      WHERE  a.objid = r.id
      ORDER  BY a.timestamp DESC
      OFFSET 5  -- excluding the first 5 per object
      ) x
   )
DELETE FROM audit_log
USING  del
WHERE  del.ctid = tbl.ctid;

Or:

DELETE FROM audit_log
WHERE  ctid NOT IN (
   SELECT x.ctid
   FROM   records r
   CROSS  JOIN LATERAL (
      SELECT a.ctid
      FROM   audit_log a
      WHERE  a.objid = r.id
      ORDER  BY a.timestamp DESC
      LIMIT  5  -- the inverse selection here
      ) x
   );

The latter may be faster with the supporting index.

Related:

• How do I (or can I) SELECT DISTINCT on multiple columns?
• How to use the physical location of rows (ROWID) in a DELETE statement

Writing a new table with just the top 5 for each object would be much faster. You can use the subquery from the last query for this. (And see GMB's answer.) It produces a pristine table without bloat. But I ruled that out due to the table being very read/write heavy. If you cannot afford the necessary exclusive lock for some time, that's a no-go.

Your timestamp column is not defined NOT NULL. You may need NULLS LAST. See:

• PostgreSQL sort by datetime asc, null first?