By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799187 fiddles created (41761 in the last week).
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);
✓
hidden batch(es)
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 tables ahead of time ...
✓
✓
✓
hidden batch(es)
INSERT INTO history VALUES
('foo', 'target1', '2017-09-14 01:00')
, ('foo', 'target2', '2017-09-14 02:00')
, ('foo', 'target3', '2017-09-14 03:00')
, ('foo', 'target4', '2017-09-14 04:00')
, ('foo', 'target5', '2017-09-14 05:00') -- already 5 rows for 'foo' in the past
, ('foo', 'target6', '2017-09-15 11:00') -- two more "today"
, ('foo', 'target7', '2017-09-15 12:00')
, ('bar', 'target8', '2017-09-15 02:00')
, ('foo', 'target9', '2017-09-16 23:00'); -- tomorrow is always ready to go
9 rows affected
hidden batch(es)
TABLE history;
fooid
target
updated_at
foo
target1
2017-09-14 01:00:00
foo
target2
2017-09-14 02:00:00
foo
target3
2017-09-14 03:00:00
foo
target4
2017-09-14 04:00:00
foo
target5
2017-09-14 05:00:00
foo
target6
2017-09-15 11:00:00
foo
target7
2017-09-15 12:00:00
bar
target8
2017-09-15 02:00:00
foo
target9
2017-09-16 23:00:00
…
hidden batch(es)
TABLE history_20170915;
fooid
target
updated_at
foo
target6
2017-09-15 11:00:00
foo
target7
2017-09-15 12:00:00
bar
target8
2017-09-15 02:00:00
…
hidden batch(es)
TABLE history_20170916;
fooid
target
updated_at
foo
target9
2017-09-16 23:00:00
…
hidden batch(es)
-- 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 the *few* for which new rows are actually 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
-- CREATE TABLE history_20170917 PARTITION OF history FOR VALUES FROM ('2017-09-17 0:0') TO ('2017-09-18 0:0'); -- should rahter be there ahead of time
