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

User request: "Set up a PostgreSQL database with row-level security for multi-tenant isolation"

Actions:

1. Create tables with a tenant_id column and UUID primary keys
2. Enable RLS with ALTER TABLE ... ENABLE ROW LEVEL SECURITY
3. Create policies using current_setting('app.tenant_id') for per-request isolation
4. Set up connection pooling with PgBouncer and configure app.tenant_id per connection

Output: A multi-tenant database where tenant data is isolated at the database level, preventing cross-tenant data leaks.

Example 2: Add full-text search to a content platform

User request: "Implement search across articles with relevance ranking and highlighting"

Actions:

1. Add a search_vector generated column using to_tsvector('english', title || ' ' || body)
2. Create a GIN index on the search vector column
3. Build a search query using @@ with plainto_tsquery() and rank results with ts_rank()
4. Add ts_headline() for highlighting matched terms in results

Output: A fast full-text search with relevance ranking, highlighting, and GIN index-backed performance.

Guidelines

• Use UUID primary keys to avoid sequential ID enumeration and merge conflicts.
• Use TIMESTAMP WITH TIME ZONE for all timestamps; never use TIMESTAMP which loses timezone context.
• Add indexes based on EXPLAIN ANALYZE output, not guesswork; measure before optimizing.
• Use connection pooling (PgBouncer) for applications with more than 20 connections since PostgreSQL forks a process per connection.
• Use RLS for multi-tenant applications since database-level isolation is more reliable than application-level checks.
• Use JSONB for truly dynamic data, not as a replacement for proper columns.
• Run VACUUM ANALYZE after bulk operations since stale statistics lead to bad query plans.