Contact

The PostgreSQL database: a complete overview, pros, cons and limits

What PostgreSQL is used for, where it is the best choice and where another database wins: pros and cons, a comparison with MySQL, SQLite, MongoDB and ClickHouse, extensions, SQL examples, limits and tips.

Stack and technologies Updated

In short

PostgreSQL is a free open-source relational database with a strict approach to data: transactions, constraints and one of the most complete SQL implementations. On top of classic tables it stores JSON with indexes, geodata (PostGIS), vectors for AI search (pgvector) and time series (TimescaleDB), so one database often covers what used to take three. It is the default choice for web service and SaaS backends, online shops and finance. Its weak spots: every connection is a separate process (you need a connection pooler), updates leave old row versions that VACUUM has to clean up, and analytics over billions of rows and horizontal write scaling are better served by other systems.

PostgreSQL at a glance

The main facts in one table — where the database came from, how it keeps data consistent and how it evolves.

Type
Object-relational database with open source code
History
The POSTGRES project at UC Berkeley under Michael Stonebraker from 1986; SQL from 1995; the name PostgreSQL since 1996
Developed by
PostgreSQL Global Development Group — a community; no company owns the project
License
PostgreSQL License — free, including commercial use
Transactions
ACID and MVCC: reads do not block writes
SQL
One of the most complete implementations: CTEs, window functions, MERGE, SQL/JSON
JSON
jsonb since 2014: binary JSON with indexes
Indexes
B-tree, Hash, GIN, GiST, SP-GiST, BRIN; partial and expression indexes
Extensions
PostGIS, pgvector, TimescaleDB, Citus and hundreds more
Replication
Streaming since 2010, logical since 2017
Releases
A major version once a year, supported for five years; fixes at least quarterly
Popularity
The most used database among developers in the Stack Overflow survey since 2023

What PostgreSQL is used for: 8 areas

The core is classic transactional data, but extensions have turned PostgreSQL into a platform. Under each area — the tools and extensions it runs on.

  1. 01

    Web service and SaaS backends

    Users, subscriptions, orders and permissions: data with strict links between tables. Every popular framework supports it.

    DjangoRailsLaravelPrisma

  2. 02

    Online shops and payments

    Transactions guarantee that money and stock never go out of sync — either the whole operation is saved, or none of it.

    ACIDSERIALIZABLECHECK

  3. 03

    Geodata and maps

    “Nearest shops”, delivery zones, routes: PostGIS is the standard for spatial data.

    PostGISGiST

  4. 04

    Site search

    Full-text search with word forms and ranking, plus typo-tolerant matching — without a separate search engine.

    tsvectorpg_trgm

  5. 05

    Vector search and AI

    Embeddings next to the data they describe: semantic search and RAG in the same database as the products and users.

    pgvectorHNSW

  6. 06

    Time series and metrics

    Sensor readings, events, prices: partitioning by time and compression for years of history.

    TimescaleDBBRIN

  7. 07

    Documents and JSON

    Flexible attributes, settings and third-party responses in jsonb — with indexes, right next to strict columns.

    jsonbGIN

  8. 08

    Job queues

    Background jobs, emails and webhooks without a separate broker: workers pick up tasks without getting in each other’s way.

    SKIP LOCKEDRiverObanpgmq

Pros and cons of PostgreSQL

PostgreSQL puts data correctness first. Most of its strengths come from that — and most of the tuning it needs, too.

Pros · 8

  • Reliable transactions

    ACID, a write-ahead log and point-in-time recovery: after a crash the database comes back consistent.

  • Rich SQL

    CTEs, window functions, LATERAL, upsert and MERGE: reports and complex logic are written in the database instead of loops in code.

  • JSON with indexes

    jsonb gives document flexibility without giving up transactions, joins and constraints.

  • Extensions

    PostGIS, pgvector, TimescaleDB, pg_cron: new capabilities plug into the same database and the same SQL.

  • An index for every case

    Partial and expression indexes, GIN for JSON and text, BRIN for huge time-ordered tables.

  • Strict data

    Types, foreign keys, CHECK and UNIQUE constraints stop bad data at the door — not in a report a month later.

  • No vendor lock-in

    A free license and a community instead of an owner company: no licence fees and no risk that the product is closed.

  • Managed everywhere

    AWS, Google Cloud, Azure, Supabase, Neon and many local providers offer it as a service with backups and replicas out of the box.

Cons · 8

  • A process per connection

    Each connection costs memory, and the default limit is 100. Hundreds of web processes need a pooler such as PgBouncer.

  • VACUUM and bloat

    An update writes a new version of the row; the old one is cleaned up by autovacuum. If it falls behind, tables and indexes swell.

  • Write scaling

    Reads scale with replicas, but all writes go to one primary server. Sharding means Citus or your own logic.

  • Modest defaults

    Out of the box it is tuned for a small machine: shared_buffers, work_mem and autovacuum must be set for real load.

  • Major upgrades take planning

    Minor updates are simple, but a major version needs pg_upgrade or logical replication, and every extension must support it.

  • Row storage for analytics

    Scanning billions of rows for a few columns is slow — columnar databases like ClickHouse are built for that.

  • Expensive frequent updates

    Because of row versions, a table where the same rows change thousands of times a second creates a lot of writes and cleanup work.

  • Needs an administrator’s eye

    Backups, monitoring, slow queries and disk growth need regular attention — or a managed service that does it for you.

PostgreSQL compared with MySQL, SQLite, MongoDB and ClickHouse

A qualitative comparison with the databases PostgreSQL is most often weighed against. Exact figures depend on the data and the queries, so the table shows relative positions rather than benchmarks.

CriterionPostgreSQLMySQLSQLiteMongoDBClickHouse
Data model tables plus JSON, geodata, vectors tables tables in one file documents columnar tables
Transactions full ACID ACID with InnoDB ACID, one writer at a time multi-document since 4.0 limited
Query language the richest SQL SQL, fewer features SQL its own query language SQL dialect for analytics
JSON jsonb with GIN indexes JSON type, indexes on expressions JSON functions native JSON type
Scaling read replicas; sharding via Citus replicas, mature tooling one machine built-in sharding clusters with shards
Analytics on billions of rows slow without extensions slow not for this medium best
Operations server, needs tuning and a pooler server, easy to start no server at all server or cluster server or cluster
License PostgreSQL License, free GPL, owned by Oracle public domain SSPL, not open source Apache 2.0
Best at web services, SaaS, money, mixed data classic websites and CMS apps, prototypes, small sites documents with a changing shape events, logs, analytics

When to choose PostgreSQL — and when not to

Thirteen typical tasks with a verdict. Where PostgreSQL is not the best choice, the alternative is named.

  • Web service or SaaS backend

    Best fit

    The default choice: strict data, transactions and every framework supports it.

  • Online shop, payments, accounting

    Best fit

    Transactions and constraints keep money and stock consistent.

  • Geodata and maps

    Best fit

    PostGIS is the industry standard for spatial queries.

  • Tables plus flexible JSON

    Best fit

    jsonb with indexes removes the need for a separate document database.

  • Vector search for RAG

    Best fit

    pgvector handles millions of vectors next to the data they describe.

  • Site search

    Works

    Built-in full-text search is enough for most sites; for complex relevance use Elasticsearch or Meilisearch.

  • Job queue

    Works

    SKIP LOCKED is fine up to thousands of jobs per second; beyond that — RabbitMQ, NATS or Kafka.

  • Time series

    Works

    With TimescaleDB — yes; at huge ingest rates look at ClickHouse.

  • A simple content site

    Works

    Works, but a CMS on MySQL or SQLite is simpler to run.

  • Analytics on billions of events

    Pick another

    ClickHouse: columnar storage is many times faster here.

  • Cache and sessions

    Pick another

    Redis or Valkey: data in memory, microsecond access.

  • A database inside an app or device

    Pick another

    SQLite: one file, no server.

  • Storing files and images

    Pick another

    Object storage like S3; keep only the link and metadata in the database.

The PostgreSQL ecosystem: tools for common tasks

Much is built in, the rest comes as extensions and separate tools. The middle column is what ships with PostgreSQL itself.

TaskBuilt inExtensions and tools
Connection pooling — PgBouncer, PgCat
Backups pg_dump, pg_basebackup pgBackRest, Barman, WAL-G
High availability streaming replication Patroni, CloudNativePG
Query statistics pg_stat_statements pgBadger, postgres_exporter
Query plans EXPLAIN ANALYZE, auto_explain explain.dalibo.com
Geodata — PostGIS
Vectors — pgvector
Time series partitioning TimescaleDB
Sharding partitioning, postgres_fdw Citus
Full-text search tsvector, pg_trgm ParadeDB
Scheduled jobs — pg_cron
Queues SKIP LOCKED, LISTEN/NOTIFY pgmq, River, Graphile Worker
Schema migrations — Flyway, Liquibase, Atlas, goose
Administration psql pgAdmin, DBeaver, DataGrip

The limits of PostgreSQL: where it hits the ceiling

  1. Thousands of direct connections

    If the web server can open more processes than the database accepts connections, a traffic peak takes down every site on it at once. A pooler in front of the database solves this.

  2. Hot rows updated constantly

    Counters, balances and statuses changed thousands of times a second generate dead row versions faster than autovacuum clears them. Batch the updates or move counters to Redis.

  3. Analytics over billions of rows

    Row storage reads whole rows for every scan. For event and log analytics a columnar database is an order of magnitude faster.

  4. One server for all writes

    When one primary can no longer absorb the writes, the next step is sharding with Citus or splitting data by service — a serious project.

  5. Files inside the database

    A field holds up to 1 GB, but images and documents in tables bloat backups and replicas. Keep files in object storage.

  6. Schema changes on large tables

    Some ALTER TABLE operations lock the table or rewrite it completely. On tables with hundreds of millions of rows migrations need a plan and a lock timeout.

8 tips for working with PostgreSQL without the bruises

  1. 01

    A pooler from day one

    PgBouncer in transaction mode lets hundreds of web processes share a few dozen real connections.

  2. 02

    Turn on pg_stat_statements

    It shows which queries eat the most time in total — that is where to start optimising, not with guesses.

  3. 03

    EXPLAIN before an index

    EXPLAIN (ANALYZE, BUFFERS) shows the real plan and where the time goes. An index added blindly may never be used.

  4. 04

    Indexes for queries, not just in case

    Every index slows down writes and takes disk. Remove unused ones — the statistics show them.

  5. 05

    Tune autovacuum, never turn it off

    For large, busy tables lower the thresholds so cleanup runs more often and in smaller steps.

  6. 06

    Test backups by restoring

    pgBackRest with WAL archiving gives point-in-time recovery. A backup nobody has restored is only a hope.

  7. 07

    Migrations without long locks

    CREATE INDEX CONCURRENTLY, a short lock_timeout and big changes split into steps keep the site online.

  8. 08

    The right types

    timestamptz for time, numeric for money, text with a CHECK instead of varchar(n), identity columns instead of serial.

What PostgreSQL looks like: 3 SQL examples

Three examples behind PostgreSQL’s main strengths: strict tables with JSON, reports right in the database and a job queue without a separate broker. Checked on PostgreSQL 16.

Strict columns and JSON in one table

Constraints guard the fields whose rules are known, jsonb keeps the rest, and a GIN index finds orders by any key inside the JSON.

orders.sql 
-- an order: strict columns where the rules are known, JSON for the rest
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    total       numeric(12, 2) NOT NULL CHECK (total >= 0),
    status      text NOT NULL DEFAULT 'new',
    details     jsonb NOT NULL DEFAULT '{}',
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- an index over any field inside the JSON
CREATE INDEX orders_details_idx ON orders USING gin (details);

-- orders delivered by courier
SELECT id, total
FROM orders
WHERE details @> '{"delivery": "courier"}';

Reports and upsert in the database

A window function ranks customers within each month in one query, and ON CONFLICT inserts a row or updates the existing one atomically.

reports.sql 
-- revenue by month and each customer's place within the month
SELECT date_trunc('month', created_at) AS month,
       customer_id,
       sum(total) AS revenue,
       rank() OVER (PARTITION BY date_trunc('month', created_at)
                    ORDER BY sum(total) DESC) AS place
FROM orders
GROUP BY 1, 2
ORDER BY month, place;

-- stock: insert a new row or add to the existing one in a single command
INSERT INTO stock (sku, qty) VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

A job queue without a broker

Each worker takes one queued job; SKIP LOCKED makes the others skip it instead of waiting, so workers never grab the same job twice.

queue.sql 
-- a worker takes the next job; other workers skip it instead of waiting
WITH next AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs
SET status = 'running', started_at = now()
FROM next
WHERE jobs.id = next.id
RETURNING jobs.id, jobs.payload;

Questions about PostgreSQL

What is PostgreSQL in simple terms?

A free database that stores an application’s data in tables and guarantees it is not lost or corrupted. It is used by websites, online services, shops and banks — from small projects to very large ones.

How do you pronounce PostgreSQL?

“Post-gres-Q-L”. The short name Postgres is officially accepted too.

PostgreSQL or MySQL?

For a new web service or SaaS PostgreSQL is usually the better choice: richer SQL, JSON with indexes, extensions and stricter data. MySQL is simpler to start with and fits classic sites and CMS where it is already the standard.

Can PostgreSQL replace MongoDB?

In most projects — yes: jsonb with GIN indexes stores documents and searches inside them, while keeping transactions and joins. MongoDB remains stronger where built-in sharding of huge document collections is needed.

Is PostgreSQL free?

Yes, completely, including for commercial use. It is distributed under the PostgreSQL License, similar to MIT and BSD; you pay only for servers or a managed service.

How much data can PostgreSQL handle?

Terabytes on one server are normal. With the default block size a table can grow to 32 TB and a single field to 1 GB; beyond one machine, sharding with Citus is used.

Do I need Redis if I have PostgreSQL?

Often not at the start: PostgreSQL can handle queues, simple caching and sessions. Redis or Valkey become worthwhile for hot counters, rate limiting and caches with microsecond access.

Is PostgreSQL good for analytics?

For reports on millions and tens of millions of rows — yes, thanks to window functions and parallel queries. For billions of events a columnar database like ClickHouse is much faster.

Online form

PostgreSQL
databases

I work with PostgreSQL in my projects: I design the schema for the task, speed up slow queries and set up indexes, backups and connections. Tell me about the task — I answer within one working day.

Or write to [email protected]