PostgreSQL (often called "Postgres") is a free, open-source relational database that has grown from a 1980s research project into one of the most trusted databases in production today. It stores structured data in tables like any relational system, but it is known for strict standards compliance, genuine extensibility, and a feature set that increasingly overlaps with specialized NoSQL and search systems. This post walks through what Postgres is, how it is built internally, its core features, and when to reach for it.
1. What is PostgreSQL?
PostgreSQL is an object-relational database management system (ORDBMS). "Relational" means data lives in tables with rows and columns, connected through keys. "Object-relational" means it also supports custom data types, inheritance between tables, and user-defined functions, going beyond the strict relational model.
It is released under the permissive PostgreSQL License, is developed by a global community rather than a single company, and runs on Linux, Windows, macOS, and most Unix-like systems. It is used by startups and by some of the largest technology companies in the world for workloads ranging from simple web apps to geospatial analytics and financial systems.
Why the elephant? Postgres's mascot, "Slonik", is a nod to the project's name: "slon" means elephant in several Slavic languages, chosen as a playful pun because elephants are said to never forget — fitting for a database.
2. A short history
| Year | Milestone |
|---|---|
| 1986 | Michael Stonebraker starts the POSTGRES project at UC Berkeley as a successor to his earlier Ingres database, aiming to support richer data types and rules. |
| 1994 | Students add a SQL query language interpreter, and the project is renamed Postgres95. |
| 1996 | The project is renamed PostgreSQL to reflect its SQL support, and moves to open-source community development. |
| 2001 | Introduction of Write-Ahead Logging (WAL), improving crash recovery and durability. |
| 2005 | Savepoints and table partitioning (via inheritance) arrive. |
| 2010 | Streaming replication and hot standby, enabling built-in high availability. |
| 2012 | Native JSON support added, followed by the more efficient binary JSONB format in 2014. |
| 2017 | Native declarative table partitioning and logical replication arrive. |
| 2020s | Ongoing releases add better parallel query execution, improved partitioning, and performance work; Postgres is consistently rated among the most admired databases in developer surveys. |
3. Internal architecture
PostgreSQL uses a process-based, client-server architecture. Each client connection is handled by its own backend process on the server, coordinated through shared memory.
-
Client connects. An application opens a TCP connection (or local socket) to the postmaster, the main server process listening for connections.
Backend process is forked. The postmaster starts a dedicated backend process for that connection, which handles all queries from it until it disconnects.
Query is parsed and planned. The backend parses the SQL, rewrites it if views or rules apply, and the query planner chooses an execution plan using table statistics.
Executor runs the plan. Rows are read from or written to shared buffers (an in-memory cache of disk pages), with the buffer manager deciding what stays cached.
Changes go to the Write-Ahead Log (WAL) first. Every change is recorded in the WAL before the data file is updated, so the database can replay the log and recover after a crash.
Background processes keep things healthy. The background writer flushes dirty pages, the checkpointer periodically syncs data to disk, autovacuum reclaims space from updated or deleted rows, and the WAL writer and archiver handle logging and replication.
Why this matters. Because every change is WAL-logged first, Postgres can guarantee durability (the "D" in ACID) and also reuses the WAL stream for replication: standby servers simply apply the same log.
4. How MVCC keeps it concurrent
PostgreSQL uses Multi-Version Concurrency Control (MVCC) instead of locking rows for every read. When a row is updated, Postgres does not overwrite it in place; it writes a new version of the row and marks the old version as expired for future transactions, while readers already in progress keep seeing the version that was current when their transaction started.
- Readers never block writers, and writers never block readers. Each transaction works with a consistent snapshot of the data.
- Old row versions become "dead tuples" once no transaction needs them anymore.
- Autovacuum is the background process that removes dead tuples and updates statistics, so tables don't bloat indefinitely. Tuning or disabling autovacuum carelessly is one of the most common causes of Postgres performance problems.
5. Data types
Beyond the standard numeric, text, and date types, Postgres ships with data types that let you model data more precisely instead of forcing everything into strings and numbers.
| Type | Use case |
|---|---|
| JSON / JSONB | Semi-structured or document-style data; JSONB stores it in a parsed binary form that supports indexing. |
| ARRAY | A column that holds a list of values of the same type, e.g. a list of tags. |
| UUID | Universally unique identifiers, common for distributed systems and public-facing IDs. |
| RANGE / MULTIRANGE | A span of values, such as a date range for a booking or a numeric range for pricing tiers. |
| GEOMETRY (via PostGIS) | Points, lines, and polygons for geospatial data, with spatial indexing and functions. |
| ENUM | A fixed, ordered list of labels, useful for statuses like "pending", "shipped", "delivered". |
| hstore | A simple key-value pair type stored in a single column, an early alternative to JSONB. |
| Composite / custom types | Define your own structured types, or write a C extension type for specialized needs. |
6. Indexing options
An index is a separate data structure that lets Postgres find rows without scanning the whole table. Postgres supports several index types because different data and query patterns need different structures.
| Index type | Best for |
|---|---|
| B-tree (default) | Equality and range queries (=, <, >, BETWEEN) on sortable data. Used for most indexes. |
| Hash | Simple equality lookups only; rarely needed since B-tree covers most cases well. |
| GIN (Generalized Inverted Index) | Values containing multiple elements: full-text search, JSONB, arrays. |
| GiST (Generalized Search Tree) | Geometric data, full-text search, and "nearest neighbor" or overlap queries; the basis for PostGIS indexes. |
| SP-GiST | Space-partitioned data with uneven distributions, such as IP ranges or phone number prefixes. |
| BRIN (Block Range Index) | Very large tables where data is naturally ordered, like time-series logs, trading index size for speed. |
7. SQL features worth knowing
- Common Table Expressions (CTEs) using
WITH, including recursive CTEs for hierarchical data like org charts or category trees. - Window functions such as
ROW_NUMBER(),RANK(), andLAG(), for running totals, rankings, and comparisons across rows without collapsing them. - Upserts with
INSERT ... ON CONFLICT DO UPDATE, inserting a row or updating it if it already exists. - Full-text search built in, using
tsvectorandtsquerytypes with GIN indexes, no separate search engine required for many use cases. - Table partitioning, splitting a large table into smaller physical pieces by range, list, or hash, while querying it as one logical table.
- Row-level security, letting the database itself restrict which rows a given user can see or modify.
- Triggers and stored procedures in multiple languages (PL/pgSQL, PL/Python, PL/Perl, and more).
8. Extensions
One of Postgres's defining strengths is a clean extension system: new data types, functions, and index methods can be added without modifying the database's own source code. Some of the most widely used extensions:
| Extension | What it adds |
|---|---|
| PostGIS | Full geographic object support, turning Postgres into a spatial database used by mapping and GIS applications. |
| pgvector | Vector similarity search for embeddings, widely used in AI and recommendation applications. |
| TimescaleDB | Automatic time-based partitioning and functions optimized for time-series data. |
| pg_stat_statements | Tracks execution statistics for every query, essential for performance tuning. |
| Citus | Shards a Postgres database across multiple nodes for horizontal scaling. |
| pg_cron | Runs scheduled jobs directly inside the database. |
9. Common use cases
Where Postgres shines
- General-purpose application backends (web and mobile)
- Financial and transactional systems needing strict correctness
- Geospatial applications, via PostGIS
- Analytics on moderate-to-large datasets
- AI applications storing embeddings with pgvector
- Systems needing complex queries, joins, or custom functions
Where you may look elsewhere
- Extremely high write throughput key-value workloads (consider Redis, DynamoDB)
- Petabyte-scale distributed analytics (consider Snowflake, BigQuery)
- Workloads needing automatic, transparent horizontal sharding out of the box
- Simple embedded or mobile-local storage (consider SQLite)
10. PostgreSQL vs. MySQL vs. Oracle
| Aspect | PostgreSQL | MySQL | Oracle Database |
|---|---|---|---|
| License | Open source (PostgreSQL License) | Open source (GPL) with a commercial edition | Commercial, proprietary |
| Standards compliance | Very high | Historically looser, improved over time | Very high |
| Extensibility | Extensive: custom types, functions, extensions | Limited compared to Postgres | Extensive, but commercial add-ons |
| JSON support | Strong, with indexing via JSONB | Good, improved in recent versions | Supported |
| Typical strength | Complex queries, correctness, extensibility | Simplicity, read-heavy web workloads | Enterprise support, mature tooling |
| Cost | Free | Free / paid tiers | Licensing can be significant |
11. Strengths and trade-offs
Strengths. Strong standards compliance, genuine extensibility, strong data integrity guarantees, rich indexing and query features, free and open source, and a large, active community.
Trade-offs. Each client connection uses a full OS process, so very high connection counts usually need a connection pooler such as PgBouncer. Horizontal scaling (sharding across machines) is not built in by default and typically relies on extensions or external tools. Tuning autovacuum and query plans for very large tables requires real operational knowledge.
12. FAQ
Is PostgreSQL really free for commercial use?
Yes. The PostgreSQL License is a permissive open-source license, similar in spirit to MIT or BSD, allowing free use, modification, and distribution, including in commercial products.
Can PostgreSQL handle "big data"?
It handles large datasets well, especially with partitioning, good indexing, and extensions like Citus or TimescaleDB. For petabyte-scale distributed analytics across many machines, purpose-built systems like BigQuery or Snowflake are usually a better fit.
Is PostgreSQL a NoSQL database?
No, it is fundamentally a relational database. However, its JSONB type, arrays, and extensions let it cover many jobs that once required a separate NoSQL or search database.
What is the difference between JSON and JSONB?
JSON stores an exact text copy of the input, preserving formatting and key order, but Postgres re-parses it on every read. JSONB stores a parsed, binary representation that is slightly larger to write but much faster to query and can be indexed with GIN.
Do I need to tune autovacuum manually?
For small databases, the defaults are usually fine. For large or high-write tables, tuning autovacuum thresholds is one of the most common and important performance tasks in production Postgres.
13. Conclusion
PostgreSQL has earned its reputation by combining strict correctness with real flexibility: a relational core strong enough for financial systems, extended with JSON, geospatial, vector, and full-text capabilities that let it stand in for several specialized databases at once. Its MVCC design keeps readers and writers out of each other's way, its WAL-based architecture gives it reliable durability and replication, and its extension system lets the community keep adding capability without forking the core project.
Key takeaway. If you need one dependable, standards-compliant database that can grow from a side project into a demanding production system without a rewrite, PostgreSQL is one of the safest defaults available today.
Further reading
- The official PostgreSQL documentation, postgresql.org/docs
- "PostgreSQL: Introduction and Concepts" by Bruce Momjian
- PostGIS documentation, postgis.net
- pgvector project, github.com/pgvector/pgvector
- "The Internals of PostgreSQL" by Hironobu Suzuki, interdb.jp/pg
This article is for general education. Always check the current PostgreSQL documentation for version-specific details, since features and defaults change between releases.
0 Comments