Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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 tables ahead of time ...
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

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


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


TABLE history_20170916;


fooid
target
updated_at


foo
target9
2017-09-16 23:00:00


-- 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
2 rows affected

3 rows affected

TABLE history;


fooid
target
updated_at


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

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