PostgreSQL

Overview

PostgreSQL is an advanced relational database with features that often eliminate the need for separate tools: JSONB for semi-structured data, built-in full-text search, window functions for analytics, recursive CTEs for hierarchical queries, row-level security for multi-tenant isolation, and streaming replication for high availability. It supports partitioning, multiple index types (B-tree, GIN, GiST, BRIN), and connection pooling via PgBouncer.

Instructions

• When designing schemas, use UUID primary keys with gen_random_uuid(), TIMESTAMP WITH TIME ZONE for all timestamps, appropriate constraints (CHECK, UNIQUE, foreign keys with ON DELETE), and partitioning for time-series data.
• When working with JSON, use JSONB for truly dynamic data with GIN indexes for containment queries, but prefer proper columns for known fields since they provide better validation and performance.
• When optimizing queries, add indexes based on EXPLAIN ANALYZE output rather than guesswork, use partial indexes for filtered queries, expression indexes for computed values, and covering indexes with INCLUDE for index-only scans.
• When building full-text search, create tsvector generated columns with GIN indexes, use ts_rank() for relevance scoring, and choose the appropriate language configuration for stemming.
• When implementing multi-tenancy, use row-level security (RLS) policies for database-level isolation rather than application-level checks, setting the user context via current_setting().
• When managing production databases, use PgBouncer for connection pooling, monitor with pg_stat_statements, run VACUUM ANALYZE after bulk operations, and set up streaming replication with Patroni for high availability.

Examples

Example 1: Design a multi-tenant SaaS database with RLS