告别Redis！如何在PostgreSQL 18实现无缓存架构的平滑落地？

-- Schema and plan helpers (run in a maintenance window)
CREATE TABLE catalog_item (
  item_id           BIGSERIAL PRIMARY KEY,
  category_id       BIGINT NOT NULL,
  tenant_id         BIGINT NOT NULL,
  price_cents       INTEGER NOT NULL,
  rating_avg        NUMERIC(3,2) NOT NULL DEFAULT 0.0,
  tags              TEXT[] NOT NULL DEFAULT '{}',
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted           BOOLEAN NOT NULL DEFAULT FALSE
);


-- Generated column to precompute a simple personalization bucket
ALTER TABLE catalog_item
  ADD COLUMN p_bucket SMALLINT GENERATED ALWAYS AS (
    (rating_avg * 10)::smallint
  ) STORED;


-- Covering index that matches the query (note the INCLUDE list)
CREATE INDEX CONCURRENTLY idx_catalog_lookup
  ON catalog_item (tenant_id, category_id, p_bucket, deleted)
  WHERE deleted = FALSE
  INCLUDE (item_id, price_cents, rating_avg, updated_at, tags);


-- A precomputed projection for “trending” that used to be cached
CREATE MATERIALIZED VIEW mv_trending AS
SELECT tenant_id, category_id,
       item_id, price_cents, rating_avg, tags,
       row_number() OVER (PARTITION BY tenant_id, category_id ORDER BY rating_avg DESC, updated_at DESC) AS rk
FROM catalog_item
WHERE deleted = FALSE;


-- Keep it fresh without blocking writers
CREATE UNIQUE INDEX CONCURRENTLY mv_trending_pk
  ON mv_trending (tenant_id, category_id, item_id);


-- In a job runner or cron:
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_trending;

-- Serve top N without touching Redis
PREPARE fetch_slice (bigint, bigint, smallint, int) AS
SELECT item_id, price_cents, rating_avg, tags
FROM   catalog_item
WHERE  tenant_id = $1
  AND  category_id = $2
  AND  p_bucket >= $3
  AND  deleted = FALSE
ORDER  BY rating_avg DESC, updated_at DESC
LIMIT  $4;


-- When trending is requested
PREPARE fetch_trending (bigint, bigint, int) AS
SELECT item_id, price_cents, rating_avg, tags
FROM   mv_trending
WHERE  tenant_id = $1 AND category_id = $2 AND rk <= $3;

# postgresql.conf (16 GB VM example)
shared_buffers = '4GB'
effective_cache_size = '11GB'
work_mem = '64MB'
maintenance_work_mem = '1GB'
track_io_timing = on
jit = on
max_worker_processes = 8
max_parallel_workers_per_gather = 2
random_page_cost = 1.1
default_statistics_target = 500

+--------------------------------------+-------+-------+-------+-------------------------------+
| Path                                 |  p50  |  p95  |  p99  | Notes                         |
+--------------------------------------+-------+-------+-------+-------------------------------+
| Redis (hit)                          |  6 ms |  18 ms|  28 ms| fast but extra hop            |
| Redis (miss → DB)                    | 24 ms |  80 ms| 120 ms| hop + serialization + origin  |
| Postgres 18 direct (covering index)  | 18 ms |  55 ms|  95 ms| fewer hops, stable tail       |
| Postgres 18 via mv_trending (warm)   | 12 ms |  38 ms|  70 ms| precomputed hot slice         |
+--------------------------------------+-------+-------+-------+-------------------------------+

BEFORE
┌────────┐    ┌──────────────┐    ┌───────────┐
│ Client │───▶│ Redis/Codec  │───▶│ Postgres  │
└────────┘    └──────────────┘    └───────────┘
    ▲              │                  ▲
    └──── misses ──┘                  └── invalidates ─┘
AFTER
┌────────┐    ┌──────────────────────────────────────────┐
│ Client │─────▶│              Postgres 18                │
└────────┘    │  • Covering index (tenant, category, p)  │
              │  • Materialized view for trending        │
              │  • Parallel plan where it helps          │
              └──────────────────────────────────────────┘