17.6 实战：从单机证据到选型 ADR

freeze generator and monthly golden
  -> bootstrap two retained shard database shells
  -> build two exact remote schemas
  -> build a local single-node baseline
  -> build LIST-partitioned foreign parents
  -> compare four byte-identical result paths
  -> collect parallel/index/spill/summary plans
  -> collect pruning/pushdown/transfer plans
  -> preserve a non-pushed JOIN counterexample
  -> prove application privilege denial
  -> prove partial shard failure semantics
  -> checksum catalogs and business state
  -> reject unsafe reset attempts
  -> pre-verify three databases
  -> exact per-database reset
  -> rebuild and repeat the entire review

pigsty_l1=not-run

[pg36-admin]
host=/path/to/socket-or-host
port=5432
dbname=pg36_shop
user=postgres

chmod 600 /path/to/pg_service.conf
export PGSERVICEFILE=/path/to/pg_service.conf
export PGSERVICE=pg36-admin

database = pg36_shop
writable primary
PostgreSQL major = 18
session_user = postgres superuser
can SET ROLE pg36_owner
pg36_app = constrained LOGIN non-superuser
ch04-v1 physical model exists
postgres_fdw 1.2 available or exact managed state
pg36_shard_a/b database shell identity exact
fdw host/port exactly equal current instance

expected database name
database owner/comment
shard remainder
shard marker
UTC/ISO session
timeouts

static/labs/ch17/
├── fixture-manifest.json
├── frozen-monthly.csv
├── fixture.sql
├── fixture-remote.sql
├── bootstrap.sql
├── remote-context.sql
├── remote-setup.sql
├── context.sql
├── setup.sql
├── verify.sql
├── remote-verify.sql
├── final-state.sql
├── reset.sql
├── remote-reset.sql
├── review.py
├── task.sh
├── analytics-distributed-adr.md
├── baseline-v1.5-proposal.json
└── pigsty-declaration.example.yml

frozen-monthly.csv
  rows=32
  sha256=64b045809e10364fd84a587121d919e8562a15335c4c6c015e91a0ead3a44323

fixture.sql
  sha256=3110d0369b0c62fffeee643200f5320d1f6bd26ad5f9950b2ab2b58991080e10

fixture-remote.sql
  sha256=da02dbca8d2cfeb0294c369b15cad3619a9433d984f98866b5043eb4508e3e91

./static/labs/ch17/task.sh setup

connect maintenance database postgres
  -> create/reuse exact pg36_shard_a/b shells

connect pg36_shard_a
  -> exact remote schema rebuild
  -> load even tenant IDs
  -> analyze

connect pg36_shard_b
  -> exact remote schema rebuild
  -> load odd tenant IDs
  -> analyze

connect pg36_shop
  -> exact FDW/data schema rebuild in one transaction
  -> load full local baseline
  -> create indexes/materialized summary
  -> create LIST foreign partitions/views/grants
  -> analyze
  -> commit
  -> VACUUM ANALYZE local sales fact

CREATE INDEX sales_fact_tenant_day_idx
ON shop_ch17.sales_fact (
  tenant_id,
  occurred_on,
  account_id
)
INCLUDE (amount, units, channel);

Bitmap Heap Scan on sales_fact
  -> Bitmap Index Scan on sales_fact_tenant_day_idx

Index Only Scan
Heap Fetches: 0

VACUUM (ANALYZE) shop_ch17.sales_fact;

Index Only Scan using sales_fact_tenant_day_idx
  actual rows=7500
  Heap Fetches: 0

distributed_amount=2256000.00
distributed_sales=240000
distributed_units=1200000
first_day=2026-01-01
last_day=2026-04-30
local_amount=2256000.00
local_sales=240000
local_units=1200000
shard_rows=dist_0:120000,dist_1:120000
summary_rows=2880
summary_sales=240000

psql -X -w \
  --dbname="service=$PGSERVICE" \
  --set=fdw_host=/path/to/socket \
  --set=fdw_port=5432 \
  --file=static/labs/ch17/local-parallel-plan.sql

Finalize HashAggregate
  -> Gather
       Workers Planned: 2
       Workers Launched: 2
       -> Partial HashAggregate
            -> Parallel Seq Scan on sales_fact
                 actual rows=80000 loops=3

parallel path exists
two workers actually launched
aggregation decomposes into partial/final
240k rows become 32 groups

production speedup
safe system-wide worker count
behavior under concurrent reports

Sort actual rows=240000
Sort Method: external merge
Disk: about 5920kB
temp read/write present

Sort actual rows=240000
Sort Method: quicksort
Memory: about 13645kB
no temp read

Parallel Seq Scan on sales_fact
actual rows=80000 loops=3

Seq Scan on daily_tenant_summary
actual rows=2880 loops=1

“全局月报每次扫描 24 万行，所以必须分片”

这条重复聚合可先用 2,880 行日粒度处理；
生产还要评审刷新、新鲜度、迟到和恢复成本。

sales_fact_day_brin_idx
  access_method=brin
  operator_class=pg_catalog.date_minmax_ops
  size > 0
  size < covering B-tree in this fixture

monthly-local.csv
monthly-summary.csv
monthly-distributed.csv
monthly-two-stage.csv

cmp static/labs/ch17/frozen-monthly.csv \
    evidence/.../monthly-local.csv

SELECT count(*), sum(amount)
FROM shop_ch17.sales_fact_distributed
WHERE tenant_id = 3
  AND occurred_on >= DATE '2026-04-01';

Foreign Scan on shop_ch17.sales_fact_dist_1
  actual rows=7500
  Remote SQL:
    SELECT amount
    FROM shop_ch17_shard.sales_fact
    WHERE occurred_on >= '2026-04-01'
      AND tenant_id = 3

sales_fact_dist_1 present
sales_fact_dist_0 absent
tenant/date in Remote SQL

HashAggregate
  -> Append actual rows=240000
       -> Foreign Scan dist_1 actual rows=120000
            Remote SQL: SELECT tenant_id, occurred_on, units, amount ...
       -> Foreign Scan dist_0 actual rows=120000
            Remote SQL: SELECT tenant_id, occurred_on, units, amount ...

SELECT tenant_id, occurred_on,
       count(*), sum(units), sum(amount)
FROM shop_ch17.sales_fact_dist_0
GROUP BY tenant_id, occurred_on

UNION ALL

SELECT tenant_id, occurred_on,
       count(*), sum(units), sum(amount)
FROM shop_ch17.sales_fact_dist_1
GROUP BY tenant_id, occurred_on;

GroupAggregate actual rows=32
  -> Sort actual rows=960
       -> Append actual rows=960
            -> Foreign Scan Aggregate dist_0 actual rows=480
                 Remote SQL ... GROUP BY 1, 2
            -> Foreign Scan Aggregate dist_1 actual rows=480
                 Remote SQL ... GROUP BY 1, 2

SELECT
  account.segment,
  count(*) AS sale_count,
  sum(sale.amount) AS amount_total
FROM shop_ch17.sales_fact_distributed AS sale
JOIN shop_ch17.account_dim_distributed AS account
  ON account.tenant_id = sale.tenant_id
 AND account.account_id = sale.account_id
WHERE sale.tenant_id = 3
  AND sale.occurred_on >= DATE '2026-04-01'
GROUP BY account.segment;

Hash Join on coordinator actual rows=7500
  -> Foreign Scan sales_fact_dist_1 actual rows=7500
  -> Hash
       -> Foreign Scan account_dim_dist_1 actual rows=50

sale_count=7500
amount_total=69375.00

exit=3
SQLSTATE 42501

app_schema_usage=true
app_local_sales_select=true
app_local_sales_write=false
app_distributed_sales_select=true
app_distributed_sales_write=false
app_server_a_usage=true
app_server_b_usage=true

healthy_shard_tenant_2=30000

exit=3
SQLSTATE 08001

server-catalog-after-failure.csv

evidence_dir="$PWD/evidence/ch17/evaluate-$(date -u +%Y%m%dT%H%M%SZ)"

PG36_EVIDENCE_DIR="$evidence_dir" \
  ./static/labs/ch17/task.sh evaluate

PG36_EVIDENCE_DIR="$PWD/evidence/ch17/verify" \
  ./static/labs/ch17/task.sh verify

manifest version/target/checksum
source generator and frozen CSV hashes
four byte-identical monthly exports
local/remote cardinality and checksums
server/mapping/relation/index/security/size catalogs
parallel/index/spill/summary plans
tenant pruning and Remote SQL
naive/two-stage row shape
non-pushed JOIN counterexample
application read/write
shard failure and restored server catalog
final state
coordinator/remote verify outputs
baseline ADR contract

1. fix correctness, SQL, statistics, paths
2. prove parallel/index/BRIN/spill/summary on one node
3. assess offline replica for tolerable-staleness reads
4. enter distribution only after measured resource boundary
5. evaluate Citus when PostgreSQL-compatible sharding fits
6. compare specialized OLAP only when PostgreSQL paths miss SLO

target versions
default path
distribution key
routing warning
remote aggregation design
production Citus gate
fixture contracts
expected checksums
lab authentication warning
evidence inventory
rollback contract
limitations

3dcb7308cf6983122ee860ad3dc2a4b44651549e3d5631770839bb9a0be450c6

schema/query changes for distribution key
backfill and dual-write/read
coordinator/worker nodes
HA and service routing
network/TLS/auth
backup repository and restore
monitoring/alerting
rebalance capacity
rolling/major upgrade
on-call training
license/cloud cost
exit rehearsal

Path A:
  pg-analytics primary + replica with pg_offline_query
  pg_conf: olap.yml

Path B:
  pg-citus0/1/2 groups
  pg_mode: citus
  pg_shard / pg_group

stop new lab sessions
verify coordinator + A + B exact state
drop coordinator views/matview/tables
drop mappings/servers/extension
drop remote tables/schemas
retain empty database shells
verify zero remaining managed objects

canonical source of truth remains PostgreSQL
dual-read compares checksums
route change has version/epoch
old and new writers cannot both own same key
backfill has watermark and resume point
last reversible point is named
service/DNS rollback is tested
new system data can be exported back

PG36_RESET_TOKEN=RESET_CH17_ANALYTICS_FDW_LAB
PG36_RESET_TARGET=pg36_shop/shop_ch17+shop_ch17_ext+fdw

SQLSTATE P3660
reset refused: invalid ch17 action token

SQLSTATE P3661
reset refused: invalid ch17 target token

SQLSTATE P3663
reset refused: ch17 workers are active

DROP VIEW ...
DROP MATERIALIZED VIEW ...
DROP TABLE exact parents/local tables ...
DROP SCHEMA shop_ch17;

DROP USER MAPPING ...  -- six exact mappings
DROP SERVER ...        -- two exact servers
DROP EXTENSION postgres_fdw;
DROP SCHEMA shop_ch17_ext;

status=coordinator-reset-ok
remaining_data_schema=0
remaining_extension_schema=0
remaining_ch17_servers=0
retained_shard_databases=pg36_shard_a,pg36_shard_b

full remote verify
BEGIN
drop sales_fact
drop account_dim
drop fixture_meta
drop schema
COMMIT

status=remote-reset-ok
remaining_schema=0
database_shell=retained

coordinator -> shard A -> shard B

export PG36_RESET_TOKEN=RESET_CH17_ANALYTICS_FDW_LAB
export PG36_RESET_TARGET=pg36_shop/shop_ch17+shop_ch17_ext+fdw

PG36_EVIDENCE_DIR="$PWD/evidence/ch17/reset" \
  ./static/labs/ch17/task.sh reset

evidence_dir="$PWD/evidence/ch17/$(date -u +%Y%m%dT%H%M%SZ)"

PG36_EVIDENCE_DIR="$evidence_dir" \
  ./static/labs/ch17/task.sh all

status=ok
fixture=frozen-byte-identical-four-paths
single_node=parallel+index+summary+spill
distributed=tenant-pruning+fdw+two-stage
counterexamples=hash-is-not-modulo+join-not-pushed
failure=healthy-shard-read+global-08001
guards=P3660+P3661+P3663
postgres_fdw=1.2
pigsty_l1=not-run
release_candidate_checksum=3dcb7308cf6983122ee860ad3dc2a4b44651549e3d5631770839bb9a0be450c6

evidence/ch17/<run>/
├── cycle-1/
├── reset-wrong-token.*
├── reset-wrong-target.*
├── reset-active-worker.*
├── reset-exact/
└── cycle-2/

business_checksum=42fb8ab5444469eba1f104a8e1e529dd
distributed_sales=240000
fixture=ch17-analytics-v1
local_sales=240000
monthly_checksum=644d45544ebbc2a80c42270c38ac6885
naive_transfer_rows=240000
postgres_fdw=1.2
release=1.5-proposal
shard_rows=dist_0:120000,dist_1:120000
summary_rows=2880
tenant3_april=7500:69375.00
two_stage_transfer_rows=960

status=review-ok
fixture=frozen-byte-identical-four-paths
single_node=parallel+index+summary+spill
distributed=tenant-pruning+fdw+two-stage
counterexamples=hash-is-not-modulo+join-not-pushed
failure=healthy-shard-read+global-08001
business_checksum=42fb8ab5444469eba1f104a8e1e529dd
monthly_checksum=644d45544ebbc2a80c42270c38ac6885
release_candidate_checksum=3dcb7308cf6983122ee860ad3dc2a4b44651549e3d5631770839bb9a0be450c6

[ ] representative production-scale data and skew
[ ] independent nodes and failure domains
[ ] target network/TLS/identity
[ ] Pigsty L1 inventory and runtime convergence
[ ] actual Citus or selected candidate, not FDW surrogate
[ ] OLTP+OLAP mixed concurrency
[ ] P50/P95/P99 and open-loop throughput
[ ] CPU/memory/I/O/temp/WAL/network cost
[ ] coordinator/worker HA
[ ] backup-to-empty restore checksum
[ ] rebalance interrupt/resume
[ ] schema change mixed-version test
[ ] rolling/major upgrade
[ ] RPO/RTO and partial-result semantics
[ ] migration dual-read and last rollback point
[ ] exit rehearsal