How to Optimize PostgreSQL for Production in 2026

CREATE DATABASE perf_test;
\c perf_test;

-- Parent table partitioned by range (date)
CREATE TABLE sales (
  id SERIAL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2),
  customer_id INT,
  metadata JSONB,
  PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

-- Partitions for 2025-2026
CREATE TABLE sales_2025 PARTITION OF sales
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sales_2026 PARTITION OF sales
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Insert 100k test rows (simulated)
INSERT INTO sales (sale_date, amount, customer_id, metadata)
SELECT
  ('2025-01-01'::DATE + (random() * 365)::INT) AS sale_date,
  random() * 1000 AS amount,
  (random() * 1000)::INT AS customer_id,
  jsonb_build_object('category', 'tech', 'tags', array['laptop', 'sale']) AS metadata
FROM generate_series(1, 100000);

CREATE INDEX idx_sales_date_brin ON sales USING BRIN (sale_date);
CREATE INDEX idx_sales_amount_btree ON sales (amount) WHERE amount > 500;
CREATE INDEX idx_sales_metadata_gin ON sales USING GIN (metadata);
CREATE INDEX idx_sales_customer_trgm ON sales USING gin (customer_id gin_trgm_ops); -- Requires pg_trgm

-- ANALYZE for accurate stats
ANALYZE sales;

-- Vacuum to reclaim space
VACUUM ANALYZE sales_2025;

-- Add FTS columns
ALTER TABLE sales ADD COLUMN tsv tsvector;
CREATE INDEX idx_sales_tsv_gin ON sales USING GIN(tsv);

-- Trigger for auto-update (french config for French)
CREATE OR REPLACE FUNCTION sales_fts_trigger() RETURNS trigger AS $$
BEGIN
  NEW.tsv := to_tsvector('french', coalesce(metadata::text, ''));
  RETURN NEW;
END$$ LANGUAGE plpgsql;

CREATE TRIGGER sales_fts_update BEFORE INSERT OR UPDATE ON sales
  FOR EACH ROW EXECUTE FUNCTION sales_fts_trigger();

-- Refresh on existing data
UPDATE sales SET metadata = metadata WHERE id <= 100000;

-- FTS query with ranking
SELECT id, sale_date, ts_rank(tsv, query) AS rank
FROM sales, plainto_tsquery('french', 'laptop vente') query
WHERE tsv @@ query
ORDER BY rank DESC
LIMIT 10;

-- Materialized view: Monthly revenue
CREATE MATERIALIZED VIEW sales_monthly_mv AS
SELECT
  DATE_TRUNC('month', sale_date) AS month,
  SUM(amount) AS total,
  COUNT(*) AS count,
  AVG(amount) AS avg
FROM sales
GROUP BY month;

CREATE UNIQUE INDEX idx_mv_month ON sales_monthly_mv (month);

-- Fast query
SELECT * FROM sales_monthly_mv WHERE month >= '2025-06-01';

-- Periodic refresh (cron/job)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_monthly_mv;

WITH ranked_sales AS (
  SELECT
    sale_date,
    amount,
    customer_id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sale_date DESC) as rn,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) as prev_amount
  FROM sales
  WHERE sale_date >= '2025-01-01'
)
SELECT
  customer_id,
  SUM(amount) FILTER (WHERE rn = 1) as latest_monthly,
  AVG(amount - COALESCE(prev_amount, 0)) as growth
FROM ranked_sales
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY growth DESC
LIMIT 20;

-- EXPLAIN ANALYZE to validate
EXPLAIN (ANALYZE, BUFFERS) WITH ranked_sales AS ... [same query];