PostgreSQL 多租户 SaaS 的六个关键决策：隔离、RLS、索引与可恢复性

CREATE TABLE tenant_catalog (
    tenant_id uuid PRIMARY KEY,
    placement text NOT NULL CHECK (placement IN ('shared', 'dedicated')),
    database_dsn text,
    status text NOT NULL DEFAULT 'active'
);

docker run --name tenant-pg \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -d postgres:16

until docker exec tenant-pg pg_isready -U postgres >/dev/null 2>&1; do
  sleep 1
done

docker exec -i tenant-pg psql -U postgres <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE ROLE app_user LOGIN PASSWORD 'app-password';

CREATE TABLE invoices (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    invoice_no text NOT NULL,
    amount_minor bigint NOT NULL CHECK (amount_minor >= 0),
    currency char(3) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, invoice_no)
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY invoices_by_tenant ON invoices
    FOR ALL
    TO app_user
    USING (
        tenant_id = current_setting('app.tenant_id', true)::uuid
    )
    WITH CHECK (
        tenant_id = current_setting('app.tenant_id', true)::uuid
    );

GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

INSERT INTO invoices (tenant_id, invoice_no, amount_minor, currency)
VALUES
    ('11111111-1111-1111-1111-111111111111', 'INV-001', 2599, 'USD'),
    ('22222222-2222-2222-2222-222222222222', 'INV-001', 4700, 'USD');

SET ROLE app_user;
BEGIN;
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT tenant_id, invoice_no, amount_minor, currency FROM invoices;
COMMIT;
RESET ROLE;
SQL

ALTER TABLE invoices
ADD CONSTRAINT invoices_tenant_invoice_no_key
UNIQUE (tenant_id, invoice_no);

CREATE INDEX CONCURRENTLY invoices_tenant_created_idx
ON invoices (tenant_id, created_at DESC)
INCLUDE (invoice_no, amount_minor, currency);

EXPLAIN (ANALYZE, BUFFERS)
SELECT invoice_no, amount_minor, currency
FROM invoices
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
ORDER BY created_at DESC
LIMIT 50;

export RECOVERY_DB='postgresql://postgres:secret@recovery-host/app'
export TENANT_ID='11111111-1111-1111-1111-111111111111'

psql "$RECOVERY_DB" -v ON_ERROR_STOP=1 \
  -c "\copy (
        SELECT id, tenant_id, invoice_no, amount_minor, currency, created_at
        FROM invoices
        WHERE tenant_id = '$TENANT_ID'
      ) TO 'tenant_invoices.csv' WITH (FORMAT csv, HEADER true)"

-- 第一步：新增可空列，旧版本应用仍可继续工作
ALTER TABLE invoices ADD COLUMN payment_status text;

-- 第二步：由作业按主键或时间窗口分批回填，而不是一次更新整张表
UPDATE invoices
SET payment_status = 'pending'
WHERE id IN (
    SELECT id
    FROM invoices
    WHERE payment_status IS NULL
    LIMIT 1000
);

-- 第三步：先添加未验证约束，缩短持锁阶段
ALTER TABLE invoices
ADD CONSTRAINT invoices_payment_status_check
CHECK (payment_status IN ('pending', 'paid', 'void')) NOT VALID;

-- 第四步：数据回填完成后再扫描验证
ALTER TABLE invoices
VALIDATE CONSTRAINT invoices_payment_status_check;

-- 如需新索引，在事务外并发创建
CREATE INDEX CONCURRENTLY invoices_tenant_status_idx
ON invoices (tenant_id, payment_status);