PostgreSQL

Open-source object-relational database; the developer default.

Reviewed by 7wData

On this page

Publisher review

PostgreSQL is the open-source object-relational database that has become the default choice for most teams building production applications in 2026. With over 35 years of active development and a permissive BSD-style license, it offers strong ACID guarantees, a 300+ extension ecosystem, and native support for JSON/JSONB, full-text search, vector search (pgvector), geospatial data (PostGIS), and time-series workloads (TimescaleDB). According to the 2025 Stack Overflow Developer Survey, 55.6% of professional developers use PostgreSQL, and 47% actively want to use it next year.

The community treats PostgreSQL as a multi-purpose tool. The "just use Postgres" philosophy—start with PostgreSQL and migrate only when concrete limits emerge—resonates because it works for the majority of applications: OLTP systems, real-time analytics, document storage, and hybrid workloads. However, this dominance masks real trade-offs. A February 2026 debate on Hacker News revealed deep disagreement about when to move away. Engineers from database companies, Redis maintainers, and backend leads pushed back on universalist claims, noting that maintaining PostgreSQL at scale demands operational expertise and costs money.

The single-primary, single-node architecture does not scale writes horizontally; sharding and distributed transactions add complexity, latency, and infrastructure cost. Major version upgrades require dump/restore or logical replication—no in-place upgrade path. Bloat from frequent updates, XID wraparound, and vacuuming complexity plague teams managing large, write-heavy datasets. For small-to-medium applications, standard OLTP, or hybrid workloads with occasional analytics, PostgreSQL is nearly always the right choice. Teams handling pure analytics at scale, extreme write throughput, or multi-tenant sharding should evaluate purpose-built alternatives earlier. The operational burden of PostgreSQL tuning—vacuuming, autovacuum configuration, index maintenance, major version upgrades—often surprises teams that assume it "just works." PostgreSQL is mature, reliable, and well-documented, but the question in 2026 is not whether it is good, but whether your specific workload justifies the operational cost of one that was not designed for it.

Get the AI & data signal, daily.

335k+ subscribers read this every morning. One email, both newsletters. Unsubscribe anytime.

How it works

  1. ACID Transactions

    Full ACID compliance with write-ahead logging and Multi-Version Concurrency Control (MVCC) for reliable data integrity and consistent reads under concurrent load.

  2. JSON/JSONB Support

    Native JSON and JSONB data types with SQL/JSON operators, indexing, and path expressions; store flexible documents alongside relational data.

  3. Extension Ecosystem (300+)

    pgvector for vector search, PostGIS for geospatial, TimescaleDB for time-series, and many others; extensions define new data types and index methods at the engine level.

  4. Streaming Replication & Point-in-Time Recovery

    Logical replication for zero-downtime major version upgrades; continuous archiving enables recovery to any point in time.

  5. Full-Text Search

    Integrated full-text search with phrase queries, stemming, ranking, and tsvector type; no separate search engine required for basic use cases.

  6. Advanced Indexing

    Multiple index types (B-tree, Hash, GiST, GIN, BRIN); partial indexes, expression indexes, and covering indexes optimize query performance for diverse workloads.

  7. Table Partitioning

    Range, list, and hash partitioning split large tables across storage and improve query efficiency and maintenance speed for multi-terabyte datasets.

Strengths and trade-offs

Strengths

  • Developer-default in 2025 (55.6% professional adoption); handles OLTP, vector search, geospatial, and time-series in one system without specialized databases.
  • Mature open-source ecosystem with BSD-style license; no licensing fees, only infrastructure and operational costs. pgvector, PostGIS, TimescaleDB keep ACID guarantees.
  • Comprehensive ACID compliance and reliability; handles transactional workloads, hybrid analytics, and complex queries without data loss or corruption risk.

Trade-offs

  • Single-primary architecture with no native horizontal write scaling. Sharding requires complex application changes, distributed transaction overhead, and network latency across nodes.
  • Operational complexity demands DBA expertise: vacuuming, autovacuum tuning, index maintenance, and major version upgrades (no in-place upgrades—requires dump/restore or logical replication).
  • Not designed for large-scale analytics (OLAP). Teams handling terabytes of analytical queries, extreme column scans, or pure reporting often move to Snowflake, ClickHouse, or DuckDB.

Pricing context

PostgreSQL is free open-source software released under a permissive BSD-style license with no per-CPU, per-core, or licensing fees. There are no hidden costs for features or scalability. However, total cost of ownership shifts entirely to infrastructure (compute, storage), operational tooling (monitoring, backup storage), and engineering expertise required for configuration tuning, streaming replication, and major version upgrades. Commercial support is optional and available through vendors like Crunchy Data, EDB, Percona, and Aiven.

Getting started with PostgreSQL

  1. Install PostgreSQL

    Download PostgreSQL from postgresql.org, run the installer for your operating system, or use Docker to containerize it. Follow the setup wizard, establish a superuser password, and verify the installation by running psql in your terminal.

  2. Connect and create a database

    Open a terminal and use psql to connect to your PostgreSQL server with your superuser credentials. Execute CREATE DATABASE trial_db to establish your first database, then connect to it and confirm the connection succeeds.

  3. Create tables and define schema

    Write CREATE TABLE statements to define your data structure. Specify column names, data types (INTEGER, VARCHAR, JSONB, TIMESTAMP), and constraints (PRIMARY KEY, NOT NULL). Build a schema that matches your application's data model.

  4. Load data and run queries

    Insert sample data using INSERT statements or the COPY command. Execute SELECT queries to retrieve and verify your data. Test filtering, joins, and aggregations to confirm PostgreSQL handles your workload correctly.

  5. Set up monitoring and backups

    Schedule automated backups using pg_dump or pg_basebackup. Configure log file storage and retention. Deploy a monitoring tool like pgAdmin, Grafana, or your cloud provider's dashboard to track performance and query execution.

Frequently Asked Questions

What is PostgreSQL and why is it so popular?

PostgreSQL is an open-source object-relational database used by 55.6% of professional developers. Its 35+ years of development, permissive BSD license, ACID guarantees, and 300+ extension ecosystem make it the default choice for most production applications. It handles OLTP, analytics, and hybrid workloads without specialized databases.

What are PostgreSQL's main features?

PostgreSQL offers ACID transactions, native JSON/JSONB support, full-text search, vector search via pgvector, and geospatial capabilities through PostGIS. Time-series workloads run on TimescaleDB. The 300+ extension ecosystem enables advanced indexing, table partitioning, streaming replication, and point-in-time recovery for data reliability and zero-downtime major version upgrades.

What are PostgreSQL's main limitations?

PostgreSQL's single-primary architecture doesn't scale writes horizontally; sharding adds complexity, latency, and infrastructure cost. Major version upgrades require dump/restore or logical replication with no in-place upgrade path. Operational complexity—vacuuming, autovacuum tuning, index maintenance—demands DBA expertise. It's not designed for large-scale analytics (OLAP).

Is PostgreSQL free to use?

PostgreSQL is free open-source software under a permissive BSD-style license with no per-CPU, per-core, or licensing fees. Total cost of ownership shifts to infrastructure (compute, storage), operational tooling (monitoring, backups), and engineering expertise for configuration tuning and major version upgrades. Commercial support is optional through vendors.

When should you use PostgreSQL vs. other databases?

Use PostgreSQL for OLTP, small-to-medium applications, hybrid workloads, and real-time analytics. Choose alternatives for pure large-scale analytics (Snowflake, ClickHouse, DuckDB), extreme write throughput, or multi-tenant sharding. The "just use Postgres" philosophy works until concrete scaling limits emerge. Evaluate operational costs and DBA expertise upfront.

What is the operational burden of running PostgreSQL?

PostgreSQL demands ongoing operational expertise: vacuuming, autovacuum configuration, index maintenance, and XID wraparound prevention. Streaming replication and major version upgrades require downtime or dump/restore. Teams often underestimate these costs. The key question is not whether PostgreSQL is reliable, but whether your workload justifies the operational complexity and engineering hours.

Alternatives in this category

Integrations

Supabase Neon Crunchy Data Aiven

How PostgreSQL compares

Direct head-to-head against 3 competitors. Picked by 7wData.

This tool

PostgreSQL

Pricing
PostgreSQL is free open-source software released under a permissive BSD-style license with no per-CPU, per-core, or licensing fees. There are no hidden costs for features or scalability. However, total cost of ownership shifts entirely to infrastructure (compute, storage), operational tooling (monitoring, backup storage), and engineering expertise required for configuration tuning, streaming replication, and major version upgrades. Commercial support is optional and available through vendors like Crunchy Data, EDB, Percona, and Aiven.
Target
PostgreSQL is the open-source object-relational database that has become the default choice for most teams building production applications in 2026.
Deployment
self-hosted
Strength
Developer-default in 2025 (55.6% professional adoption); handles OLTP, vector search, geospatial, and time-series in one system without specialized databases.
Watch for
Single-primary architecture with no native horizontal write scaling. Sharding requires complex application changes, distributed transaction overhead, and network latency across nodes.

MySQL

Pricing
Community Edition free under GPL. Standard: $2,140/year (2-core). Enterprise: $5,350/year (2-core).
Target
Web application teams on LAMP stacks prioritizing broad hosting availability and simple read-heavy OLTP.
Deployment
Open-source (GPL), commercial license, or managed cloud via AWS RDS, GCP, Azure.
Strength
Default database on shared and budget hosting; dominant in PHP and WordPress deployments for read-heavy OLTP.
Watch for
Oracle-owned since 2010; GPL dual-license forces commercial distributors to open-source their code or buy the Enterprise edition.

MongoDB Atlas

Pricing
Free (M0, 512MB). Dedicated from $57/month. Serverless: $0.10/million reads, $1.00/million writes.
Target
Teams with flexible or rapidly evolving schemas who want horizontal sharding without managing distributed infrastructure.
Deployment
SaaS on AWS, GCP, or Azure; self-managed Community edition also available.
Strength
Native horizontal auto-sharding at the storage layer; no application-level sharding logic required, unlike PostgreSQL.
Watch for
Egress and cross-region data transfer fees escalate sharply; production deployments frequently exceed initial cost estimates.

CockroachDB

Pricing
Free: 50M RUs and 10 GiB/month. Serverless paid: approx. $1-2/million RUs. Dedicated and Enterprise: custom.
Target
Teams hitting PostgreSQL write-scaling limits who need multi-region, always-on distributed SQL with PostgreSQL wire compatibility.
Deployment
SaaS (CockroachDB Cloud) and self-hosted on-prem or customer-managed cloud.
Strength
PostgreSQL-wire-compatible distributed SQL with automatic multi-region replication; survives node or availability zone failures without manual failover.
Watch for
Cloud deployment costs run substantially higher than self-hosted PostgreSQL; distributed serializable transactions add measurable latency overhead.

User reviews

No user reviews yet. Be the first to write one.

Sources

Reporting on this tool draws on these publicly available sources.

  1. www.postgresql.org — PostgreSQL 18.4 release, BSD-style open-source license, 35+ years of development, ACID compliance, JSON/JSONB, extensions ecosystem
  2. onesignal.com — Real-world scaling challenges at 75TB across 40 servers: bloat from frequent updates, XID wraparound, major version upgrade complexity, solutions including pg_repack and logical replication
  3. news.ycombinator.com — February 2026 community debate on 'Just Use Postgres'; disagreement between advocates and database professionals on when PostgreSQL hits limits and requires specialized alternatives
  4. dev.to — PostgreSQL weaknesses: operational complexity of replication setup, higher memory usage than MySQL, not commonly available on budget hosting, performance trade-offs for simple read-heavy workloads
  5. www.instaclustr.com — Horizontal scaling limitations: data consistency challenges, single-primary write bottleneck, application compatibility issues, cost trade-offs between vertical and horizontal scaling
  6. www.postgresql.org — PostgreSQL License details: permissive BSD-like open-source license, free to download, use, modify, distribute; no per-CPU or licensing fees