Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
create table store_database(id serial primary key);
insert into store_database select generate_series(0,169,1);

create table store_record(
  id serial primary key,
  updated_at timestamptz default now(),
  database_id bigint references store_database(id));
create index on store_record (database_id, updated_at desc nulls last);

create table store_record_statistics(
  database_id smallint unique references store_database(id) 
    on update cascade,
  latest_record_updated_at timestamptz,
  record_count integer default 0);

insert into store_record_statistics --initializes table with view definition
SELECT g.id, MAX(s.updated_at), COUNT(*)
FROM store_database g LEFT JOIN store_record s ON g.id = s.database_id
GROUP BY g.id;

create index store_record_statistics_idx 
  on store_record_statistics (database_id) 
  include (latest_record_updated_at,record_count);

cluster verbose store_record_statistics using store_record_statistics_idx;


version


PostgreSQL 12.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1



CREATE TABLE



INSERT 0 170



CREATE TABLE



CREATE INDEX



CREATE TABLE



INSERT 0 170



CREATE INDEX



CLUSTER


CREATE FUNCTION maintain_store_record_statistics_trigger() 
  RETURNS TRIGGER LANGUAGE plpgsql AS
$$ BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN --decrement and find second most recent updated_at
    UPDATE store_record_statistics srs
    SET (record_count,
         latest_record_updated_at) 
    =   (record_count - 1,
         (SELECT s.updated_at
          FROM   store_record s
          WHERE  s.database_id = srs.database_id
          ORDER  BY s.updated_at DESC NULLS LAST
          LIMIT  1))
    WHERE database_id = old.database_id;
  END IF;
  IF TG_OP in ('INSERT','UPDATE') THEN --increment and pick most recent updated_at
    UPDATE store_record_statistics 
    SET (record_count,
         latest_record_updated_at) 
    =   (record_count + 1,
         greatest(
            latest_record_updated_at,
            new.updated_at))
    WHERE database_id=new.database_id;
  END IF;
  RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER maintain_store_record_statistics
AFTER INSERT OR UPDATE OF database_id OR DELETE ON store_record 
INITIALLY DEFERRED FOR EACH ROW
EXECUTE PROCEDURE maintain_store_record_statistics_trigger();

CREATE FUNCTION maintain_store_record_statistics_truncate_trigger() 
RETURNS TRIGGER LANGUAGE plpgsql AS
$$ BEGIN
  update store_record_statistics 
  set (record_count, latest_record_updated_at)
    = (0           , null);
  RETURN NULL;
END $$;

CREATE TRIGGER maintain_store_record_statistics_truncate
AFTER TRUNCATE ON store_record
EXECUTE PROCEDURE maintain_store_record_statistics_truncate_trigger();

CREATE FUNCTION



CREATE TRIGGER



CREATE FUNCTION



CREATE TRIGGER


explain analyze verbose insert into store_record (database_id,updated_at) 
  select random()*1e2, now()+random()*'1 second'::interval
  from generate_series(0,1e4,1) a;


QUERY PLAN


Insert on public.store_record  (cost=0.00..42.50 rows=1000 width=20) (actual time=82.952..82.953 rows=0 loops=1)

  ->  Subquery Scan on "*SELECT*"  (cost=0.00..42.50 rows=1000 width=20) (actual time=1.813..18.630 rows=10001 loops=1)

        Output: nextval('store_record_id_seq'::regclass), "*SELECT*"."?column?_1", "*SELECT*"."?column?"

        ->  Function Scan on pg_catalog.generate_series a  (cost=0.00..25.00 rows=1000 width=16) (actual time=1.775..6.875 rows=10001 loops=1)

              Output: (random() * '100'::double precision), (now() + (random() * '00:00:01'::interval))

              Function Call: generate_series('0'::numeric, '10000'::numeric, '1'::numeric)

Planning Time: 0.068 ms

Trigger RI_ConstraintTrigger_c_1162088 for constraint store_record_database_id_fkey: time=122.849 calls=10001

Execution Time: 207.669 ms



EXPLAIN


select * from store_record_statistics order by record_count desc limit 10;


database_id
latest_record_updated_at
record_count


26
2022-11-11 18:25:31.994843+00
129

66
2022-11-11 18:25:32.007389+00
123

35
2022-11-11 18:25:32.004359+00
122

48
2022-11-11 18:25:32.007632+00
120

50
2022-11-11 18:25:32.000157+00
119

12
2022-11-11 18:25:32.004489+00
118

32
2022-11-11 18:25:31.984583+00
118

10
2022-11-11 18:25:32.005893+00
117

44
2022-11-11 18:25:32.007527+00
116

15
2022-11-11 18:25:31.994287+00
115



SELECT 10
database_id	latest_record_updated_at	record_count
26	2022-11-11 18:25:31.994843+00	129
66	2022-11-11 18:25:32.007389+00	123
35	2022-11-11 18:25:32.004359+00	122
48	2022-11-11 18:25:32.007632+00	120
50	2022-11-11 18:25:32.000157+00	119
12	2022-11-11 18:25:32.004489+00	118
32	2022-11-11 18:25:31.984583+00	118
10	2022-11-11 18:25:32.005893+00	117
44	2022-11-11 18:25:32.007527+00	116
15	2022-11-11 18:25:31.994287+00	115