SQL

Using a PostgreSQL trigger to rotate logs in a table

Create the function (this example assumes the response_time column is responsible for the timestamp used to rotate the logs, and is in a unix timestamp format) used to rotate the logs:

CREATE OR REPLACE FUNCTION "func_example_log_rotate"()
RETURNS trigger AS
$$
BEGIN
	DELETE FROM "example_log"
	WHERE TO_TIMESTAMP("response_time") < NOW() - INTERVAL '1 hour';
	RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';

And create the trigger to fire on each insert statement:

CREATE TRIGGER "example_log_rotate"
	AFTER INSERT
	ON "example_log"
	FOR EACH STATEMENT
EXECUTE PROCEDURE "func_example_log_rotate"();

As an aside, you may wish to declare the log table as an unlogged table, if the contents are ephemeral and losing them after a crash would not matter:

ALTER TABLE "example_log" SET UNLOGGED;

This will improve performance at the cost of a truncated table if PostgreSQL does not shut down cleanly.