37 先纵向扩容，再横向扩展

select left(regexp_replace(query, '\s+', ' ', 'g'), 29) as query,
       calls,
       round(total_exec_time) as total_ms,
       round(mean_exec_time::numeric, 2) as mean_ms,
       shared_blks_hit + shared_blks_read as blks,
       wal_bytes
from pg_stat_statements
where dbid = (select oid from pg_database where datname = current_database())
order by total_exec_time desc
limit 5;

             query             | calls  | total_ms | mean_ms |  blks   | wal_bytes
-------------------------------+--------+----------+---------+---------+-----------
 update account_totals set eve |  10612 |   149895 |   14.13 |  260308 |   1378255
 insert into events (account_i | 120225 |     6450 |    0.05 | 3059854 |  72265853
 ...

for i in $(seq 1 30); do
  psql -Atc "select coalesce(wait_event_type, 'CPU') || ' '
                    || coalesce(wait_event, 'running')
             from pg_stat_activity
             where datname = current_database()
               and application_name = 'pgbench' and state = 'active'"
  sleep 0.25
done | sort | uniq -c | sort -rn

explain (analyze, costs off, timing off)
select (created_at at time zone 'UTC')::date as day,
       count(*) as events,
       count(*) filter (where kind = 'deploy') as deploys,
       count(*) filter (where kind = 'deploy'
                        and payload->>'status' = 'failed') as failed_deploys
from events
where account_id = 42
  and created_at >= '2026-09-01' and created_at < '2026-10-01'
group by 1 order by 1;

 GroupAggregate (actual rows=30.00 loops=1)
   Buffers: shared hit=2124 read=33125
   ...
               ->  Parallel Seq Scan on events (actual rows=51.33 loops=3)
                     Rows Removed by Filter: 666615

 GroupAggregate (actual rows=30.00 loops=1)
   Buffers: shared hit=117 read=39
   ->  Sort (actual rows=154.00 loops=1)
         ->  Bitmap Heap Scan on events (actual rows=154.00 loops=1)
               Heap Blocks: exact=149
               ->  Bitmap Index Scan on events_account_created_idx ...
                     Buffers: shared hit=3 read=4

create table account_daily (
  account_id      bigint not null,
  day             date   not null,
  events          int    not null,
  deploys         int    not null,
  failed_deploys  int    not null,
  builds          int    not null,
  alerts          int    not null,
  primary key (account_id, day)
);

insert into account_daily
select account_id,
       (created_at at time zone 'UTC')::date,
       count(*),
       count(*) filter (where kind = 'deploy'),
       count(*) filter (where kind = 'deploy' and payload->>'status' = 'failed'),
       count(*) filter (where kind = 'build'),
       count(*) filter (where kind = 'alert')
from events
group by 1, 2;

 Limit (actual rows=10.00 loops=1)
   Buffers: shared hit=3565 read=11281 written=8
   ...
         ->  Parallel Bitmap Heap Scan on events (actual rows=10543.00 loops=3)
               Rows Removed by Filter: 157493

 Limit (actual rows=10.00 loops=1)
   Buffers: shared hit=1427 read=2791 written=11
   ...
               ->  Bitmap Heap Scan on account_daily (actual rows=91600.00 loops=1)
                     Heap Blocks: exact=1662

insert into account_daily as d
select account_id,
       (created_at at time zone 'UTC')::date,
       count(*),
       count(*) filter (where kind = 'deploy'),
       count(*) filter (where kind = 'deploy' and payload->>'status' = 'failed'),
       count(*) filter (where kind = 'build'),
       count(*) filter (where kind = 'alert')
from events
where created_at >= date_trunc('day', now() - interval '1 day', 'UTC')
group by 1, 2
on conflict (account_id, day) do update
set events         = excluded.events,
    deploys        = excluded.deploys,
    failed_deploys = excluded.failed_deploys,
    builds         = excluded.builds,
    alerts         = excluded.alerts
where (d.events, d.deploys, d.failed_deploys, d.builds, d.alerts)
      is distinct from
      (excluded.events, excluded.deploys, excluded.failed_deploys,
       excluded.builds, excluded.alerts);

 Insert on account_daily d (actual rows=0.00 loops=1)
   Conflict Resolution: UPDATE
   Rows Removed by Conflict Filter: 699
   Tuples Inserted: 1000
   Conflicting Tuples: 998
   ...
     ->  Index Scan using events_created_idx on events (actual rows=10480.00 ...)
 Execution Time: 15.769 ms

create materialized view project_activity_30d as
select project_id,
       count(*)                                as events,
       count(*) filter (where kind = 'deploy') as deploys,
       max(created_at)                         as last_event_at
from events
-- now() in a live system
where created_at >= timestamptz '2026-10-06' - interval '30 days'
group by project_id;

refresh materialized view concurrently project_activity_30d;

ERROR:  cannot refresh materialized view "public.project_activity_30d" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the
       materialized view.

                            query                            | mean_ms | wal_bytes
-------------------------------------------------------------+---------+-----------
 refresh materialized view project_activity_30d              |      41 |   2152086
 refresh materialized view concurrently project_activity_30d |     181 |     27601

create table import_staging_logged   (like events);
create unlogged table import_staging (like events);

insert into import_staging_logged
  select * from events where created_at < '2026-01-01';
insert into import_staging
  select * from events where created_at < '2026-01-01';

                       query                        |  rows  |   wal
----------------------------------------------------+--------+---------
 insert into import_staging_logged select * from ev | 476712 | 77 MB
 insert into import_staging        select * from ev | 476712 | 0 bytes

create table events_archive (like events including defaults);

with moved as (
  delete from events
  where id in (
    select id from events
    where created_at < '2025-11-01'
    order by id
    limit 10000
  )
  returning *
)
insert into events_archive
select * from moved;

frozen: 35029 pages from table (99.56% of total) had 1996020 tuples frozen
visibility map: 4962 pages set all-visible, 35008 pages set all-frozen
                (30046 were all-visible)
...
WAL usage: 70383 records, 35190 full page images, 291082046 bytes,
           2126 buffers full
system usage: CPU: user: 0.22 s, system: 0.24 s, elapsed: 6.01 s