The important information is this:
There are several million entries in this table and a couple of thousand changes per day.
And:
SELECTs are not an issue, it gets rarely queried.
What matters is write performance. For daily maintenance, only a very small percentage of old rows has to be considered if we can keep new additions separate - while still allowing convenient access to the whole table. Add the right index to the table of old entries, and this is very fast. You wrote:
Would PostgreSQL table partitioning help here? I know partitioning is
used to easily throw away big chunks of data that are older than X
days, but partitioning by fooId would seem to create too many
partitions in my case.
I think you were on the right track with table partitioning. Especially since one of the major features of Postgres 10 (to be released soon) is a new partitioning system. The release notes:
Add table partitioning syntax that automatically creates partition constraints and handles routing of tuple insertions and updates (Amit Langote).
Details in the manual for Postgres 10:
So don't bother with Postgres 9.6 and start with Postgres 10 right away.
Setup
CREATE TABLE history ( -- partition parent
fooid text NOT NULL,
target text NOT NULL,
updated_at timestamp NOT NULL DEFAULT now()
)
PARTITION BY RANGE (updated_at);
CREATE TABLE history_past
PARTITION OF history FOR VALUES FROM (MINVALUE) TO ('2017-09-15 0:0'); -- partition for timestamps before 2017-09-15 ("past")
CREATE TABLE history_20170915
PARTITION OF history FOR VALUES FROM ('2017-09-15 0:0') TO ('2017-09-16 0:0'); -- partition 2017-09-15 ("today");
CREATE TABLE history_20170916
PARTITION OF history FOR VALUES FROM ('2017-09-16 0:0') TO ('2017-09-17 0:0'); -- partition 2017-09-16 ("tomorrow");
-- more daily tables ahead of time ...
Just keep inserting into history
. Rows end up in the appropriate partition automatically. The only index you need to make the daily consolidation fast:
CREATE INDEX history_past_fooid_updated_at_idx ON history_past (fooid, updated_at);
Index maintenance does not affect write performance at all since new rows end up in the partition of "today" without any index - as fast as can be.
Daily consolidation
Run once per day at hours of low traffic. Best with a cron job or pgagent:
-- trim excess from new day (rare case!)
DELETE FROM history_20170915 h
USING (
SELECT fooid, updated_at
, row_number() OVER (PARTITION BY fooid ORDER BY updated_at DESC) AS rn
FROM history_past
) d
WHERE d.fooid = h.fooid
AND d.updated_at = h.updated_at
AND d.rn > 5;
-- trim excess from past to make room for new rows
DELETE FROM history_past h
USING (
SELECT fooid, updated_at, new_rows
, row_number() OVER (PARTITION BY fooid ORDER BY updated_at DESC) AS rn
FROM (SELECT fooid, count(*) AS new_rows FROM history_20170915 GROUP BY 1) ct
JOIN history_past p USING (fooid) -- only consider few where rows were added
) d
WHERE d.fooid = h.fooid
AND d.updated_at = h.updated_at
AND d.rn + d.new_rows > 5;
ALTER TABLE history DETACH PARTITION history_past;
INSERT INTO history_past -- identical structure guaranteed
TABLE history_20170915; -- short syntax
DROP TABLE history_20170915;
ALTER TABLE history ATTACH PARTITION history_past
FOR VALUES FROM (MINVALUE) TO ('2017-09-16 0:0'); -- "yesterday" added to the past
dbfiddle here
Replace all history_20170915
with yesterday's table and adapt the date for history_past
accordingly.
Create partitions for the next month or so ahead of time to be sure. The daily cron job also checks and adds partitions to keep the buffer at one month.
A trigger solution like @a_horse provided is fast for each single entry, but the cost sums up for thousands of daily entries. You also need to factor in index maintenance.
And to see the complete history you can still just:
SELECT * FROM history WHERE fooid = 'foo';