PostgreSQL

Overview

PostgreSQL is an open-source relational database with features that often replace separate tools: JSONB for semi-structured data, built-in full-text search, window functions, recursive CTEs, row-level security (RLS) for tenant isolation, and streaming replication. It offers partitioning and several index types (B-tree, GIN, GiST, BRIN). The current major version is 18 (18.6 as of August 2026); 14 reaches end of life on 12 November 2026, so new projects should start on 17 or 18.

Instructions

• Schemas: use uuid primary keys with DEFAULT gen_random_uuid() (built in since 13, no extension needed), or uuidv7() on 18+ when you want time-ordered keys that keep B-tree inserts local. Use timestamptz for all timestamps, text rather than varchar(n) unless a limit is a real rule, GENERATED ALWAYS AS IDENTITY for integer keys, and CHECK, UNIQUE and foreign keys with explicit ON DELETE. Partition large time-series tables with PARTITION BY RANGE.
• JSON: use jsonb for genuinely dynamic data and a GIN index for containment (@>) queries; keep known fields in real columns.
• Indexing: read EXPLAIN (ANALYZE, BUFFERS) output before adding an index. Use partial indexes for filtered queries, expression indexes for computed values, and INCLUDE columns for index-only scans. Build on live tables with CREATE INDEX CONCURRENTLY.
• Full-text search: add a tsvector column declared GENERATED ALWAYS AS (...) STORED with a GIN index. Always pass the language config explicitly (to_tsvector('english', ...)), wrap nullable columns in coalesce, query with websearch_to_tsquery (accepts quotes and -term, never raises syntax errors) and rank with ts_rank.
• Multi-tenancy: enable RLS and write policies with USING and WITH CHECK. Table owners, superusers and roles with BYPASSRLS skip policies, so connect the application as a separate non-owner role (or add FORCE ROW LEVEL SECURITY). Pass the tenant with set_config('app.tenant_id', $1, true) inside the transaction.
• Operations: pool connections (PgBouncer, or the pooler your platform provides) because each connection is a server process; enable pg_stat_statements through shared_preload_libraries followed by CREATE EXTENSION pg_stat_statements; rely on autovacuum and run ANALYZE by hand after bulk loads; use Patroni or a managed service for failover.

Examples

Example 1: Multi-tenant table with row-level security