PostgreSQL Guide 2026 — Performance, JSON, Full-Text Search, and Scaling

-- B-tree: default, good for equality and range queries
CREATE INDEX idx_posts_user_created
  ON posts (user_id, created_at DESC);
 
-- Partial index: only indexes rows matching a condition
CREATE INDEX idx_posts_published
  ON posts (published_at DESC)
  WHERE published = true;
 
-- GIN index for full-text search and JSONB containment
CREATE INDEX idx_posts_search
  ON posts USING GIN (search_vector);
 
CREATE INDEX idx_users_metadata
  ON users USING GIN (metadata);
 
-- BRIN for very large, append-only tables (logs, events)
CREATE INDEX idx_events_created
  ON events USING BRIN (created_at);
 
-- Check existing indexes and their usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Store flexible metadata alongside structured columns
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);
 
-- Insert with nested JSONB
INSERT INTO products (name, price, metadata) VALUES (
  'Wireless Headphones',
  149.99,
  '{
    "brand": "AudioTech",
    "colors": ["black", "white", "red"],
    "specs": {"battery_hours": 30, "wireless": true},
    "tags": ["audio", "wireless", "premium"]
  }'
);
 
-- Query: products with battery > 20 hours, tagged "wireless"
SELECT name, price,
       metadata -> 'specs' ->> 'battery_hours' AS battery
FROM products
WHERE (metadata -> 'specs' ->> 'battery_hours')::int > 20
  AND metadata @> '{"tags": ["wireless"]}';
 
-- Update nested JSONB field
UPDATE products
SET metadata = jsonb_set(metadata, '{specs, warranty_years}', '2')
WHERE id = $1;
 
-- Aggregate JSONB arrays
SELECT jsonb_agg(DISTINCT tag)
FROM products, jsonb_array_elements_text(metadata -> 'tags') AS tag;

-- Add a tsvector column for search
ALTER TABLE posts ADD COLUMN search_vector TSVECTOR;
 
-- Populate it with weighted fields
UPDATE posts SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(excerpt, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'C');
 
-- Trigger to keep it updated automatically
CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.excerpt, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.content, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER posts_search_update
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION update_search_vector();
 
-- Search query with ranking
SELECT
  id, title, excerpt,
  ts_rank(search_vector, query) AS rank
FROM posts,
  plainto_tsquery('english', 'react server components') AS query
WHERE published = true
  AND search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- Running totals and rankings
SELECT
  order_id,
  user_id,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS spending_rank,
  LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount,
  amount - LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS delta
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days';
 
-- Percentile for pricing analytics
SELECT
  category,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY price) AS p95_price
FROM products
GROUP BY category;

-- Non-recursive CTE for readability
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE last_login > NOW() - INTERVAL '30 days'
    AND deleted_at IS NULL
),
user_post_counts AS (
  SELECT user_id, COUNT(*) AS post_count
  FROM posts
  WHERE published = true
  GROUP BY user_id
)
SELECT u.name, u.email, COALESCE(p.post_count, 0) AS posts
FROM active_users u
LEFT JOIN user_post_counts p ON p.user_id = u.id
ORDER BY posts DESC;
 
-- Recursive CTE for hierarchical data (org chart, categories)
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 AS depth, name::text AS path
  FROM categories WHERE parent_id IS NULL
 
  UNION ALL
 
  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1,
         ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY path;