PostgreSQL
Open SourceThe World's Most Advanced Open Source Relational Database.
Published 29 May 2026Last updated 31 October 2026
Scores
Popularity5/5
Broadly known
The most popular relational database among developers per Stack Overflow 2024; rapidly growing.
Learning Curve3/5
Moderate
Standard SQL is broadly known, but CTEs, window functions, and JSONB take time to master.
Flexibility5/5
Unopinionated
Extensions like PostGIS and pgvector, custom types, and PL/pgSQL cover virtually any need.
Performance4/5
Fast
Battle-tested query planner; efficient with proper indexing and regular vacuum.
Portability5/5
No lock-in
Open SQL standard with no lock-in; data and skills transfer to any SQL-compatible system.
About PostgreSQL
PostgreSQL (often called Postgres) is a powerful open-source object-relational database with over 35 years of active development, originating at UC Berkeley in 1986. It is widely considered the world's most advanced open-source database — ACID-compliant, standards-conformant, and trusted for data integrity in applications ranging from small web services to enterprise financial systems.
Data types go well beyond standard SQL: primitive types (integer, numeric, text, boolean, date/time), structured types (array, range, composite, UUID), JSON/JSONB with rich indexing and querying, and specialised types for geometric shapes, network addresses, and XML. The native JSONB type with GIN indexing makes Postgres viable as a document store alongside its relational role.
Advanced query capabilities include CTEs (WITH clauses, including recursive), window functions, table partitioning (declarative range/list/hash), foreign data wrappers (FDW) for querying external data sources in SQL, logical replication for selective row-level replication, and parallel query execution across multiple CPU cores.
Extensibility is a core design principle: users can define custom functions, operators, data types, index methods, and procedural languages. PostGIS (geospatial) is the most prominent extension, turning Postgres into the world's leading open-source geospatial database. The ecosystem spans hundreds of packages including pg_trgm (fuzzy text search), timescaledb (time-series), and pgvector (vector embeddings for AI).
Key Features
- Full ACID compliance with reliable transaction support
- Extensible with custom functions, types, and extensions
- Advanced indexing including GIN, GiST, and partial indexes
- Native JSON/JSONB support with indexing and querying
- PostGIS extension for geospatial data and queries
- pgvector extension for vector similarity search next to relational data
- MVCC for high concurrency without read locks
- Mature replication with streaming and logical replication
Pros
- Most feature-rich open-source relational database
- Strong data integrity with full ACID compliance
- Highly extensible, with extensions for geospatial, time series, and vectors
- Advanced query planner handles complex queries well
- Decades of proven production use
- Free and open source, supported by every major cloud
Cons
- Needs configuration tuning for best performance
- VACUUM can affect performance on write-heavy workloads
- No built-in automatic sharding
- Each connection is a process, so large apps need a pooler
- Replication and high availability need extra tooling
PostgreSQL Pricing
Open SourceTech Stacks with PostgreSQL
Next.js + PostgreSQL
ProjectFull-stack Next.js app on PostgreSQL (self-managed or a serverless host like Neon), with Clerk, Auth0, or BetterAuth for auth, deployed to Vercel or a container host like Railway.
Programming
Databases
Hosting
Authentication
GitLab Self-Hosted
InfrastructureThe full GitLab platform, repository, CI/CD, and collaboration, as an Omnibus container on your own server, with bundled PostgreSQL, Redis, and NGINX inside.
Python Web (FastAPI + React)
ProjectA clean separation of concerns: React on the frontend, FastAPI serving a typed REST API, and PostgreSQL for persistent storage (MySQL, MariaDB, and serverless Postgres hosts are also available). Docker keeps environments consistent.
Learning Resources
All articlesTools Related to PostgreSQL
Works well with PostgreSQL(11)
Redis often sits in front of PostgreSQL as a cache for hot reads, sessions, and rate-limit counters. PostgreSQL already keeps frequently read data in shared_buffers, so a separate cache earns its place once read volume or latency needs outgrow the database.
Lovable Cloud, the default backend for new Lovable projects, is built on Supabase, so every Lovable app, whether on Lovable Cloud or a separately connected Supabase project, stores its data in PostgreSQL.
Bolt Cloud, Bolt.new's native backend suite, includes a managed PostgreSQL database alongside auth, storage, and server functions; Supabase, also Postgres-based, is the opt-in alternative on paid plans.
n8n has a native PostgreSQL node for running queries and INSERTs as workflow steps — PostgreSQL is used as a data destination or source in n8n automation pipelines.
PostgreSQL is the primary database Hasura introspects; all queries, mutations, and subscriptions map directly to Postgres tables, views, and functions.
PostgreSQL publishes change events to Kafka via Debezium logical replication — the source side of the most common CDC streaming pattern.
Integrates with PostgreSQL(14)
Django's ORM has first-class PostgreSQL support — JSONB, ArrayField, HStoreField, and full-text search are PostgreSQL-specific features built into django.contrib.postgres; the production Django stack defaults to PostgreSQL.
TypeORM has first-class, long-standing PostgreSQL support with a native driver, migrations, and both Active Record and Data Mapper access to Postgres-backed entities.
Drizzle has first-class PostgreSQL support with SQL-first, type-safe queries and Drizzle Kit migrations.
Prisma has first-class PostgreSQL support with a native connector, migrations, and a fully type-safe client.
dbt runs SQL transformations directly inside PostgreSQL — the dbt-postgres adapter executes models as CREATE TABLE / CREATE VIEW statements; PostgreSQL is one of dbt's primary targets alongside Snowflake and BigQuery.
Airbyte has an official PostgreSQL destination connector; supports full refresh and incremental sync with CDC via logical replication — one of Airbyte's most-used connectors.
Tools that require PostgreSQL(1)
PostGraphile works exclusively with PostgreSQL — it introspects the Postgres schema at startup to generate the entire GraphQL API. No Postgres, no PostGraphile.
Alternatives to PostgreSQL(14)
Both are full-featured open-source relational SQL databases with ACID compliance and broad language support; PostgreSQL offers richer SQL features and a powerful extension system while MySQL prioritises simplicity and read performance in traditional web stacks.
PostgreSQL is the raw relational database; Supabase wraps Postgres with auth, storage, and realtime — use raw PostgreSQL for full control.
Both are full-featured open-source relational SQL databases with ACID compliance; PostgreSQL has a richer extension ecosystem and more advanced SQL features, while MariaDB offers MySQL compatibility and strong multi-master replication via Galera Cluster.
Neon is serverless PostgreSQL — the same engine as standard Postgres, but with scale-to-zero and branching instead of a continuously running instance.
Self-managed PostgreSQL is free and runs anywhere with full control; Prisma Postgres is a hosted, operation-billed Postgres that removes server management and costs nothing when idle.
Self-managed PostgreSQL costs nothing in licence fees and runs anywhere; PlanetScale runs managed highly available Postgres clusters, including sharded Postgres through Neki, with branching and online schema changes on paid plans.
