PostgreSQL

PostgreSQL is the production standard for relational data in modern web applications. After 35 years of development, it has grown far beyond a traditional RDBMS: native JSON/JSONB support for document-style storage, full-text search without an external engine, geospatial capabilities via PostGIS, and AI-ready vector search through pgvector. This combination means many applications that might reach for MongoDB, Elasticsearch, or a dedicated vector database can stay on a single Postgres instance without sacrificing capability.

Axevate uses PostgreSQL across AI pipelines (pgvector for RAG), eCommerce backends, and SaaS applications. Managed Postgres has also reached a level of maturity — via Supabase, Neon, AWS RDS, and Google Cloud SQL — where operational complexity is no longer a barrier. Our experience spans schema design for performance, migration strategies at scale, connection pooling under load, and pgvector index tuning for production RAG systems.

1Core Query Engine & Indexing

PostgreSQL's query planner is one of the most sophisticated in any database. It evaluates dozens of execution plans for each query, estimating costs based on table statistics, index availability, and join ordering. Understanding how the planner works — and how to guide it when it makes wrong choices — is essential for consistent production performance. Run EXPLAIN (ANALYZE, BUFFERS) on slow queries to see the actual execution plan, row estimates, and where time is spent.

Index selection is the highest-leverage performance decision. B-tree indexes (the default) cover equality, range, and sorting operations. GIN indexes are appropriate for JSONB, full-text search, and array containment — critical for JSON-heavy schemas. BRIN indexes are extremely compact and appropriate for monotonically-growing data like timestamps in append-only tables. Partial indexes (WHERE clause on CREATE INDEX) cover specific query patterns at a fraction of the storage and maintenance cost of full indexes.

Partial indexes and expression indexes are underused. A partial index on (status) WHERE status = 'pending' covers queue-style patterns with near-zero storage overhead. An expression index on LOWER(email) makes case-insensitive lookups instant. These pay large dividends in applications with predictable access patterns — which includes most production web applications.

2JSONB: Document Storage in Postgres

JSONB stores JSON as a binary decomposed representation that supports GIN indexing, containment queries (@>), key existence checks (?), and path-based access. This makes Postgres capable of many document-database workloads without a separate system. A GIN index on a JSONB column enables fast queries like WHERE metadata @> '{"status": "active"}' — equivalent to a MongoDB query against an indexed field.

The tradeoff is schema discipline. JSONB gives flexibility but loses the type safety, constraint enforcement, and query planner statistics of typed columns. Our pattern: use typed columns for fields you filter or sort on frequently (status, user_id, created_at), and JSONB for extensible metadata that varies by record and is rarely queried directly. This gives you predictable performance on hot paths and flexibility on variable-schema data.

JSONB functions are powerful but syntax-heavy. The ->> operator extracts a field as text; -> extracts as JSON; #>> and #> handle nested paths. jsonb_set() for updates and jsonb_agg() for aggregating results into JSON arrays cover most mutation patterns. For applications that heavily manipulate nested JSON structures, consider whether the data model should be normalized into relational tables instead.

3pgvector: AI & RAG Embeddings

pgvector is the PostgreSQL extension for storing and querying vector embeddings — enabling semantic search and retrieval-augmented generation (RAG) directly in your existing Postgres database. Install via CREATE EXTENSION vector; store embeddings as vector(1536) columns (for OpenAI text-embedding-3-small); query with <-> (L2 distance), <=> (cosine distance), or <#> (inner product). For most RAG applications, cosine distance on normalized embeddings gives the best semantic similarity results.

pgvector supports two index types: IVFFlat and HNSW. IVFFlat divides the vector space into lists and searches only a subset — lower memory, slower build, good for large datasets. HNSW (Hierarchical Navigable Small World) builds a graph structure — higher memory usage, faster queries, better recall. For production RAG systems where query latency matters, HNSW is generally the right choice. Set hnsw.ef_search higher (200+) at query time for better recall at modest throughput cost.

pgvector's major advantage is consolidation: your embeddings, metadata, and relational data live in one place. You can filter by metadata before or after vector search (hybrid queries), join to other tables, and apply your existing backup, replication, and access control infrastructure. The tradeoff vs. dedicated vector databases (Pinecone, Qdrant, Weaviate) is that pgvector's indexing performance under very high write throughput or at 100M+ vector scale is less proven. For most production RAG applications — documents, support articles, product catalogs — pgvector is the right call.

4Connection Pooling & PgBouncer

PostgreSQL creates a process per connection, which means each connection has memory overhead (~5-10MB) and OS process cost. A Next.js application deployed on Vercel with 100 concurrent serverless function instances will attempt 100 simultaneous database connections — well above the default 100-connection Postgres limit and causing connection pool exhaustion under load.

PgBouncer is the standard connection pooler for PostgreSQL. It sits between your application and Postgres, maintaining a small pool of long-lived Postgres connections and queuing application requests. In transaction pooling mode (the highest-throughput setting), a connection is held only for the duration of a transaction — allowing thousands of application connections to share dozens of Postgres connections. The caveat: transaction pooling is incompatible with prepared statements and advisory locks, so verify your ORM or query library is compatible.

Managed Postgres services handle pooling differently. Supabase includes a PgBouncer-based pooler on port 6543 (vs. direct connections on 5432). Neon uses a serverless proxy that handles connection pooling transparently. AWS RDS Proxy provides PgBouncer-equivalent pooling for RDS instances. For serverless deployments (Vercel, AWS Lambda), always route through a pooler — direct connections to Postgres from serverless functions will exhaust your connection limit under real load.

5Migrations & Schema Management

Schema migrations are the most operationally dangerous part of PostgreSQL at scale. Certain DDL operations acquire table-level locks that block reads and writes: adding a column with a DEFAULT, dropping a column, adding a non-partial non-concurrent index. In a high-traffic application, these can cause visible downtime.

Patterns for zero-downtime migrations: add columns as nullable first (no lock), backfill in batches, then add constraints; use CREATE INDEX CONCURRENTLY to build indexes without locking; use RENAME COLUMN only during a maintenance window or with a multi-step deploy (add new column → dual-write → backfill → switch reads → drop old). Tools like Flyway, Liquibase, and Sqitch provide migration versioning and audit trails. Prisma Migrate and Drizzle Kit generate and apply migrations from schema definitions.

Migration testing in production-scale copies is undervalued. Many teams test migrations against a small development database where they complete in milliseconds, then discover they run for 2 hours against 500M rows in production. Run migration time estimates against a production-size copy before scheduling a deploy. For very large tables, pg_repack can rebuild a table with new constraints without the long-running lock.