programming-postgres

name	programming-postgres
user-invocable	false
description	Internal skill invoked by /programming chain. Use when writing PostgreSQL queries, designing schemas, choosing index types, implementing pagination, or configuring Row Level Security. Trigger keywords: PostgreSQL, postgres, pg, index, B-tree, GIN, BRIN, RLS, UPSERT, cursor pagination, SKIP LOCKED, timestamptz, jsonb.

PostgreSQL Patterns

Choose the right index and the right data type; most PostgreSQL performance problems are one or the other.

Quick reference for schema design, indexing, and query patterns.

When to Use

• Creating or modifying PostgreSQL schemas
• Choosing index types for query patterns
• Implementing pagination (especially cursor-based)
• Working with Row Level Security (RLS)
• Building queue-like processing with advisory locks
• Reviewing queries for common anti-patterns

When NOT to Use

• Non-PostgreSQL databases (MySQL, SQLite, etc.)
• Application-level ORM patterns (see language-specific skills)

Index Selection

Query Pattern	Index Type	Example
Equality (=)	B-tree	CREATE INDEX ON users (email)
Range (<, >, BETWEEN)	B-tree	CREATE INDEX ON events (created_at)
Array contains (@>)	GIN	CREATE INDEX ON posts USING gin (tags)
JSONB key lookup	GIN	CREATE INDEX ON docs USING gin (metadata)
Full-text search	GIN	CREATE INDEX ON posts USING gin (tsv)
Large table, range scans	BRIN	CREATE INDEX ON logs USING brin (created_at)
Pattern matching (LIKE)	B-tree	CREATE INDEX ON users (name text_pattern_ops)

Composite index ordering

Put equality columns first, then range columns:

-- Query: WHERE tenant_id = $1 AND created_at > $2
CREATE INDEX ON events (tenant_id, created_at);
-- NOT: (created_at, tenant_id)

Covering indexes

Include extra columns to enable index-only scans:

CREATE INDEX ON orders (customer_id) INCLUDE (total, status);
-- Query: SELECT total, status FROM orders WHERE customer_id = $1
-- Satisfied entirely from the index

Partial indexes

Index only the rows that matter:

CREATE INDEX ON orders (created_at) WHERE status = 'pending';
-- Only indexes pending orders — smaller, faster

Data Types

Use Case	Type	Notes
Primary keys	bigint	serial or generated always
UUIDs	uuid	gen_random_uuid()
Strings	text	No varchar unless hard limit needed
Timestamps	timestamptz	Always with timezone
Money	numeric	Never float/double
Booleans	boolean	Not int 0/1
JSON	jsonb	Not json (binary is faster)
Arrays	text[] etc.	Native arrays for simple lists
IP addresses	inet	Not text
Intervals	interval	For durations

Cursor Pagination

Offset pagination is O(n) — cursor pagination is O(1).

-- First page
SELECT id, title, created_at
FROM posts
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page (cursor = last row's created_at + id)
SELECT id, title, created_at
FROM posts
WHERE tenant_id = $1
  AND (created_at, id) < ($cursor_ts, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Requires a unique, ordered compound key (e.g., (created_at, id)).

UPSERT

INSERT INTO metrics (key, value, updated_at)
VALUES ($1, $2, now())
ON CONFLICT (key) DO UPDATE
SET value = EXCLUDED.value,
    updated_at = EXCLUDED.updated_at;

Queue Processing with SKIP LOCKED

Process rows as a queue without blocking other workers:

-- Worker picks one unprocessed row
WITH next AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'processing', started_at = now()
FROM next
WHERE jobs.id = next.id
RETURNING jobs.*;

Row Level Security (RLS)

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Users see only their own documents
CREATE POLICY "users_own_docs" ON documents
  FOR ALL
  USING (user_id = current_setting('app.current_user_id')::bigint);

-- Admins see everything
CREATE POLICY "admin_all_docs" ON documents
  FOR ALL
  USING (current_setting('app.current_role') = 'admin');

For Supabase, use auth.uid() instead of current_setting.

Anti-Pattern Detection

Run these queries to find common problems:

-- Tables without primary keys
SELECT t.schemaname, t.tablename
FROM pg_tables t
LEFT JOIN pg_constraint c
  ON c.conrelid = (t.schemaname || '.' || t.tablename)::regclass
  AND c.contype = 'p'
WHERE t.schemaname = 'public'
  AND c.oid IS NULL;

-- Unused indexes (check pg_stat_user_indexes)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Sequential scans on large tables
SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

Common Mistakes

Mistake	Fix
OFFSET for deep pagination	Use cursor pagination with (created_at, id) < ...
json instead of jsonb	Always use jsonb — binary storage, indexable
varchar(255) everywhere	Use text — PostgreSQL treats them identically
timestamp without tz	Always timestamptz
Missing index on FK columns	Add B-tree index on every foreign key
SELECT * in production	Select only needed columns
Float for money	Use numeric — exact arithmetic