Postgres 15

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?.
-- table for storing data processing status

CREATE TABLE status_table (
  base_name  text      NOT NULL
, version    smallint  NOT NULL
, ref_time   int       NOT NULL
, processed  bool      NOT NULL
, processing bool      NOT NULL
, updated    int       NOT NULL
, PRIMARY KEY (base_name, version)
);
 
INSERT INTO status_table VALUES 
-- --------- new ref_time batch 27793000 ----------
--           settled and complete
-- ------------------------------------------------
  ('abc', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 500)
, ('jkl', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 450)
, ('pqr', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 400)
-- --------- new ref_time batch 27793300 ----------
--           settled and complete
-- ------------------------------------------------
, ('def', 1, 27793300, 't', 'f', (extract(epoch from now()) / 60) - 350)
, ('hft', 1, 27793300, 't', 'f', (extract(epoch from now()) / 60) - 300)
-- --------- new ref_time batch 27793600 ----------
--           settled and NOT complete
-- ------------------------------------------------
, ('ghi', 1, 27793600, 'f', 't', (extract(epoch from now()) / 60) - 275)
, ('wqt', 1, 27793600, 't', 'f', (extract(epoch from now()) / 60) - 250)
-- --------- new ref_time batch 27793900 ----------
--           NOT settled and NOT complete
-- ------------------------------------------------
, ('rfe', 1, 27793900, 't', 'f', (extract(epoch from now()) / 60) - 225)
-- any 'updated' time above here (threshold is -200) counts as 'settled' (if all in ref_time batch are settled)
, ('mno', 1, 27793900, 'f', 't', (extract(epoch from now()) / 60) - 175)
, ('dpw', 1, 27793900, 't', 'f', (extract(epoch from now()) / 60) - 150)
-- --------- new ref_time batch 27794200 ----------
--           NOT settled and complete
-- ------------------------------------------------
, ('eqz', 1, 27794200, 't', 'f', (extract(epoch from now()) / 60) - 125)
, ('fwp', 1, 27794200, 't', 'f', (extract(epoch from now()) / 60) - 100)

CREATE TABLE



INSERT 0 12


-- the actual data table

CREATE TABLE data_table (
  location  text      NOT NULL
, param_id  text      NOT NULL
, ref_time  int       NOT NULL
, fcst_time smallint  NOT NULL
, timestamp int       NOT NULL
, value     text      NOT NULL
, PRIMARY KEY (location, param_id, ref_time, fcst_time)
);

-- note that: 'ref_time' + 'fcst_time' = 'timestamp'
-- I know it's not ideal to store 'timestamp' when it can be derived from 'ref_time' and 'fcst_time' (could just store those two)
-- but all three bits of info are in the raw data so I store all three rather than deriving 'timestamp' after extraction
-- maybe I could/should reduce table storage by storing only 'ref_time' and 'fcst_time' and deriving 'timestamp'?

INSERT INTO data_table VALUES
-- --------- new ref_time batch 27793000 ----------
  ('aaa', 'temp', 27793000,   0, 27793000, '21.0')
, ('aaa', 'temp', 27793000, 100, 27793100, '20.1')
, ('aaa', 'temp', 27793000, 200, 27793200, '19.6')
, ('aaa', 'temp', 27793000, 300, 27793300, '18.3')
, ('aaa', 'temp', 27793000, 400, 27793400, '17.1')
, ('aaa', 'temp', 27793000, 500, 27793500, '16.4')
, ('aaa', 'temp', 27793000, 600, 27793600, '16.4')
, ('aaa', 'temp', 27793000, 700, 27793700, '16.4')
, ('aaa', 'temp', 27793000, 800, 27793800, '16.4')
, ('aaa', 'temp', 27793000, 900, 27793900, '16.4')
-- new param in same ref_time batch
-- note that the timestamps overlap with previous batch
, ('aaa', 'humi', 27793000,   0, 27793000, '55.0')
, ('aaa', 'humi', 27793000, 100, 27793100, '57.1')
, ('aaa', 'humi', 27793000, 200, 27793200, '62.6')
, ('aaa', 'humi', 27793000, 300, 27793300, '76.3')
, ('aaa', 'humi', 27793000, 400, 27793400, '72.1')
, ('aaa', 'humi', 27793000, 500, 27793500, '65.4')
, ('aaa', 'humi', 27793000, 600, 27793600, '63.4')
, ('aaa', 'humi', 27793000, 700, 27793700, '62.2')
, ('aaa', 'humi', 27793000, 800, 27793800, '59.1')
, ('aaa', 'humi', 27793000, 900, 27793900, '57.8')
-- --------- new ref_time batch 27793300 ----------
-- note that the timestamps overlap with previous batch
, ('aaa', 'temp', 27793300,   0, 27793300, '18.0')
, ('aaa', 'temp', 27793300, 100, 27793400, '17.1')
, ('aaa', 'temp', 27793300, 200, 27793500, '16.6')
, ('aaa', 'temp', 27793300, 300, 27793600, '15.3')
, ('aaa', 'temp', 27793300, 400, 27793700, '13.1')
, ('aaa', 'temp', 27793300, 500, 27793800, '11.4')
-- new param in same ref_time batch
, ('aaa', 'humi', 27793300,   0, 27793300, '76.0')
, ('aaa', 'humi', 27793300, 100, 27793400, '72.1')
, ('aaa', 'humi', 27793300, 200, 27793500, '65.6')
, ('aaa', 'humi', 27793300, 300, 27793600, '63.3')
, ('aaa', 'humi', 27793300, 400, 27793700, '61.1')
, ('aaa', 'humi', 27793300, 500, 27793800, '59.4')
-- --------- new ref_time batch 27793600 ----------
-- note that the timestamps overlap with previous batch
, ('aaa', 'temp', 27793600,   0, 27793600, '18.0')
, ('aaa', 'temp', 27793600, 100, 27793700, '17.1')
, ('aaa', 'temp', 27793600, 200, 27793800, '16.6')
, ('aaa', 'temp', 27793600, 300, 27793900, '15.3')
, ('aaa', 'temp', 27793600, 400, 27794000, '13.1')
, ('aaa', 'temp', 27793600, 500, 27794100, '11.4')
-- --------- new ref_time batch 27793900 ----------
, ('aaa', 'temp', 27793900,   0, 27793900, '18.0')
, ('aaa', 'temp', 27793900, 100, 27794000, '17.1')
, ('aaa', 'temp', 27793900, 200, 27794100, '16.6')
, ('aaa', 'temp', 27793900, 300, 27794200, '15.3')
, ('aaa', 'temp', 27793900, 400, 27794300, '13.1')
, ('aaa', 'temp', 27793900, 500, 27794400, '11.4')

CREATE TABLE



INSERT 0 44


-- just a query to show some useful stats from status_table
-- not all of it is needed for the next stage... just useful to visualise
SELECT ref_time
  , max(updated) AS max_updated
  , count(*) AS total
  , count(*) FILTER (WHERE processed) AS processed
  , round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 1) AS percent
  , round(round(count(*) FILTER (WHERE processed) * 1.0 / count(*), 1) * 100) AS rounded
  -- a batch is 'complete' when all files in the batch have been processed:
  , count(*) FILTER (WHERE processed) = count(*) AS complete
  -- we assume no more data coming in ('settled') when a batch hasn't been updated in a while:
  , max(updated) < (round(extract(epoch from now()) / 60) - 200) AS settled
  -- ready is when the batch is 'complete' AND 'settled':
  , (count(*) FILTER (WHERE processed) = count(*)) AND (max(updated) < (round(extract(epoch from now()) / 60) - 200)) AS ready
FROM status_table
GROUP BY ref_time
ORDER BY ready DESC, rounded DESC, ref_time DESC


ref_time
max_updated
total
processed
percent
rounded
complete
settled
ready


27793300
27809987
2
2
100.0
100
t
t
t

27793000
27809887
3
3
100.0
100
t
t
t

27794200
27810187
2
2
100.0
100
t
f
f

27793900
27810137
3
2
66.7
70
f
f
f

27793600
27810037
2
1
50.0
50
f
t
f



SELECT 5


WITH
  stats AS (
    SELECT ref_time
      , count(*) FILTER (WHERE processed) = count(*) AS complete
      , round(round(count(*) FILTER (WHERE processed) * 1.0 / count(*), 1) * 100) AS rounded
      , max(updated) < (round(extract(epoch from now()) / 60) - 200) AS settled
      , (count(*) FILTER (WHERE processed) = count(*)) AND (max(updated) < (round(extract(epoch from now()) / 60) - 200)) AS ready
    FROM status_table
    GROUP BY ref_time
    ORDER BY ready DESC, rounded DESC, ref_time DESC
  ),
  first AS (
    SELECT ref_time FROM stats LIMIT 1
  ),
  ref1 AS (
    -- select all ready batches other than the first in order above
    -- but only delete from those other batches where timestamp is less than max timestamp of first batch (see maxts CTE below)
    SELECT ref_time
    FROM stats
    WHERE ready AND ref_time != (SELECT ref_time FROM first)
  ),
  ref2 AS (
    -- select all settled but incomplete batches
    -- we can delete all records in these
    SELECT ref_time
    FROM stats
    WHERE settled AND NOT complete
  ),
  maxts AS (
    SELECT ref_time, max(timestamp) AS max_timestamp
    FROM data_table
    WHERE ref_time = (SELECT ref_time FROM first)
    GROUP BY ref_time
  )
DELETE
FROM
     data_table AS d
USING
     ref1 AS r1,
     ref2 AS r2
WHERE
     (d.ref_time = r2.ref_time)
  OR (d.ref_time = r1.ref_time) AND d.timestamp <= (SELECT max_timestamp FROM maxts)

DELETE 24
ref_time	max_updated	total	processed	percent	rounded	complete	settled	ready
27793300	27809987	2	2	100.0	100	t	t	t
27793000	27809887	3	3	100.0	100	t	t	t
27794200	27810187	2	2	100.0	100	t	f	f
27793900	27810137	3	2	66.7	70	f	f	f
27793600	27810037	2	1	50.0	50	f	t	f