Overview
- 1PostgreSQL 17 is the latest major release — supported until November 2029.
- 2Streaming I/O framework: sequential scans use OS read-ahead for up to 2x throughput improvement.
- 3JSON_TABLE() brings full SQL/JSON standard compliance for querying JSON as relational data.
- 4Logical replication now supports failover to standby, making HA configurations simpler.
- 5pgvector extension makes PostgreSQL a competitive vector database for AI applications.
Key Features in 17
Use Cases
- →Transactional applications requiring full ACID compliance
- →JSON/document storage alongside relational data (JSONB columns)
- →Vector search for AI applications via pgvector extension
- →Time-series data with TimescaleDB extension
What's New in PostgreSQL 17
- read_stream API: sequential scans prefetch ahead using OS buffers — massive throughput gains
- JSON_TABLE(): `SELECT * FROM data, JSON_TABLE(data.payload, '$[*]' COLUMNS(id INT PATH '$.id'))`
- MERGE RETURNING: return affected rows from MERGE operations like INSERT...RETURNING
- Logical replication: `pg_create_logical_replication_slot(failover => true)` for HA
- VACUUM: incremental vacuuming breaks work into resumable passes — less I/O disruption
- COPY FROM ... WITH (ON_ERROR IGNORE, LOG_VERBOSITY DEFAULT): skip bad rows, log errors
- btree vacuum: dead index entries cleaned more aggressively — smaller, faster indexes
Core SQL Features
- Window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), NTILE()
- CTEs (WITH clauses): recursive CTEs for tree/graph queries; MATERIALIZED hint
- JSONB: binary JSON storage with GIN indexing; operators @>, ?, #>>, jsonb_path_query()
- Full-text search: to_tsvector, to_tsquery, GIN/GiST indexes, ts_rank, phrase search
- Array types: ANY(), ALL(), array_agg(), unnest(), array operators @>, &&
- Lateral joins: `LATERAL` subquery accesses columns from preceding FROM items
Performance Tuning
- EXPLAIN ANALYZE BUFFERS: read I/O hits vs misses to find cache-unfriendly queries
- pg_stat_statements: track query execution time, calls, and I/O across sessions
- Index types: B-tree (default), GIN (arrays/JSONB/FTS), GiST (geometric/FTS), BRIN (time series), Hash
- Partial indexes: `CREATE INDEX ON orders(customer_id) WHERE status = 'pending'`
- autovacuum tuning: lower `autovacuum_vacuum_scale_factor` for large frequently-updated tables
- work_mem: increase per-sort/hash operation for complex queries (default 4MB is often too low)
Extensions & Ecosystem
- pgvector: store and query vector embeddings — cosine, L2, inner product distance operators
- PostGIS: geographic objects, spatial queries, GIS operations — the gold standard for geo data
- TimescaleDB: time-series optimised hypertables with automated partitioning
- pg_partman: partition management — range, list, and hash partitioning automation
- Supabase: Postgres-as-a-service with Auth, Storage, Edge Functions, and Realtime
- pg_cron: schedule PostgreSQL jobs as cron expressions inside the database
Frequently Asked Questions
What is pgvector and how does it turn PostgreSQL into a vector database?
pgvector is a PostgreSQL extension that adds a `vector` data type and index types (IVFFlat, HNSW) for similarity search. You store embeddings as vector columns and query them with `<->` (L2), `<=>` (cosine), or `<#>` (inner product) operators. This lets you perform semantic search directly in PostgreSQL without a separate vector database like Pinecone or Weaviate.
What is JSONB and when should I use it over a separate JSON document store?
JSONB stores JSON as decomposed binary — indexable with GIN indexes, faster to query than text JSON. Use JSONB when: documents have semi-structured data that varies per row, you need to query inside JSON fields, or you want to avoid managing a separate MongoDB/document store. For purely document-centric access patterns with no relational joins, a dedicated document store may still be preferable.
PostgreSQL vs MySQL vs SQLite — how to choose?
PostgreSQL is the best general-purpose relational database — full ACID, advanced SQL, JSON, arrays, full-text search, and a rich extension ecosystem. MySQL is widely hosted and fine for read-heavy web applications but lacks PostgreSQL's features. SQLite is ideal for local/embedded use cases, prototypes, and client-side storage. For most new server-side applications, choose PostgreSQL.