DEVELOPMENT TECHNOLOGIES

PostgreSQL: Architecture, Use Cases, and PostgreSQL vs MySQL

How PostgreSQL stores and protects data with MVCC and WAL, what teams use it for, from OLTP to pgvector, and how it compares with MySQL, MongoDB, and SQL Server.
PostgreSQL logo

What Is PostgreSQL?

PostgreSQL is an open-source object-relational database that started at UC Berkeley in 1986 and is maintained by the PostgreSQL Global Development Group under the permissive PostgreSQL License. Teams comparing PostgreSQL vs MySQL, or evaluating database management software in general, choose it for strict ACID compliance and deep extensibility.

The license lets anyone use, modify, and distribute PostgreSQL free of charge for commercial or private work, so there are no per-core fees or vendor lock-in. The engine is fully ACID compliant, using Multiversion Concurrency Control (MVCC) and a Write-Ahead Log (WAL) for transactional integrity and crash recovery.

A new major version ships every year, and the community supports each one for five years, so teams can plan upgrades well in advance. Recent releases have focused on vacuum performance, online maintenance, and replication.

Release notes and reference material are in the official PostgreSQL documentation.

How PostgreSQL Works

PostgreSQL uses a process-per-connection architecture, MVCC, and a Write-Ahead Log to guarantee ACID transactions while readers and writers work concurrently without blocking each other.

Each client connection gets its own operating system process. Rows are stored in an unclustered heap, and every index points directly to heap tuples. Each change is written to the WAL before it counts as committed, so the server can replay transactions after a crash.

Instead of locking rows for readers during updates, MVCC writes a new row version and keeps the old one for earlier transactions. Those old versions stay on disk until autovacuum or VACUUM reclaims them, refreshes planner statistics, maintains the visibility map for index-only scans, and prevents transaction ID wraparound. Standard VACUUM and REINDEX CONCURRENTLY keep tables online; VACUUM FULL and plain REINDEX lock them.

Large values are compressed or moved out of line by TOAST. Queries use six built-in index types: B-tree, Hash, GiST, SP-GiST, GIN, and BRIN, plus partial, expression, and covering indexes. Custom types, operators, functions, and procedural languages can be added without recompiling the core. Documented limits are listed in the table below.

ResourceDocumented limitNotes
Database sizeUnlimitedBounded only by storage.
Table size32 TBWith the default 8 KB block size.
Field size1 GBLarge values stored out of line by TOAST.
Columns per table1,600Columns per index: 32.
Identifier length63 bytesQuery parameters: 65,535.
Engineer's Take

We recommend PostgreSQL as the default relational store when an application needs strict integrity, complex joins, and workloads such as JSONB or vector search in one engine. Three pitfalls catch teams in production: one OS process per connection exhausts memory without a pooler, autovacuum that falls behind on busy tables causes bloat, and VACUUM FULL during business hours locks tables. We add pooling from day one, tune autovacuum, and test migrations on production-sized data.

Diatom Enterprises favicon
Diatom engineering team
PostgreSQL at a Glance
Type

Open-source object-relational database

License

PostgreSQL License (free, permissive)

Release cycle

Annual major versions, five years of support each

Strengths

ACID, transactional DDL, JSONB, PostGIS, pgvector

Compared with
Need a reliable tech partner?
20+

Years in business

80+

Senior engineers

200+

Projects delivered

What PostgreSQL Is Used For

PostgreSQL’s main uses come down to consolidating transactional, analytical, document, geospatial, and vector workloads that once needed several databases into one ACID-compliant engine.

Transactional Systems

Financial ledgers, order management, and booking systems rely on ACID transactions, foreign keys, and MVCC, where readers and writers do not block each other.

Analytics and Time Series

The planner handles complex joins and secondary index scans well, so teams run reporting directly on PostgreSQL, adding TimescaleDB for high-ingest telemetry and BRIN indexes for large ordered tables.

JSON Documents and Geospatial Data

Variable product catalogs and user-defined fields live in GIN-indexed JSONB columns next to relational data, and PostGIS adds spatial queries for logistics, mapping, and property platforms.

AI Vector Search with pgvector

The open-source pgvector extension stores embeddings in normal tables and runs exact or approximate nearest-neighbor search in SQL with HNSW or IVFFlat indexes, without a separate vector database.

PostgreSQL vs MySQL and MongoDB

Comparing PostgreSQL vs MySQL or MongoDB, PostgreSQL stands out for strict relational integrity, transactional DDL, and rich extensions under a free license. Compared with SQL Server, the main differences are per-core licensing and the T-SQL dialect.

Criteriontech logo postgresqlPostgreSQLtech logo mysqlMySQLtech logo mongodbMongoDB
Data model & storageObject-relational; unclustered heap with indexes pointing to tuples.Relational; InnoDB clusters rows in the primary key index.Document store; BSON documents without an enforced schema by default.
LicenseFree PostgreSQL License.Open-source and commercial editions.Source-available server plus commercial cloud.
Concurrency modelProcess per connection; needs a pooler at scale.Thread per connection, lower memory per client.Document-level concurrency across nodes.
Schema migrationsTransactional DDL that can roll back.DDL commits implicitly.Schema-less by default.
Non-relational supportJSONB, custom types, PostGIS, TimescaleDB, pgvector.Native JSON type with multi-valued indexes.Native documents; $lookup for joins.
ScalingVertical and read replicas; sharding via extensions.Read replicas, Group Replication, and Vitess for sharding.Built-in horizontal sharding.
Swipe the table to compare →

When to Choose PostgreSQL

Choose PostgreSQL when your application depends on multi-table integrity, complex analytical or secondary-index queries, safe transactional migrations, free open-source licensing, or one engine that combines relational tables with JSONB, PostGIS, or pgvector. Compared with MySQL, it trades a little memory per connection for stronger complex-query performance and rollable schema changes.

When PostgreSQL Is Not the Right Fit

PostgreSQL is not the right fit when you need native horizontal write sharding out of the box, which MongoDB provides, when the workload is almost entirely simple primary-key lookups and you cannot run a connection pooler, which suits MySQL, or when your enterprise stack is tightly bound to T-SQL and built-in columnstore analytics in SQL Server.

How We Approach PostgreSQL Projects

We run PostgreSQL in production with schema discipline, connection pooling, tuned autovacuum, non-blocking migrations, and careful extension choices.

Pooling and Role Design

A connection pooler sits between applications and the database to control memory. Permissions use PostgreSQL’s cluster-wide role hierarchy and predefined roles, and logical replication systems restrict which output plugins can be loaded.

Non-Blocking Migrations and Vacuum

Migrations run inside transactions so failures roll back. On live tables we use standard VACUUM and REINDEX CONCURRENTLY instead of locking commands, and tune autovacuum for high-update tables to prevent bloat and wraparound.

Upgrades and Extension Governance

Minor and major upgrades are scheduled well ahead of each version’s end of life. Extensions like pgvector, PostGIS, and TimescaleDB are chosen per workload and tested with production-sized data.

Sound like the right partner?

Tell us about your project. We'll get back to you within 1 business day.

Frequently Asked Questions

Direct answers to common engineering and architectural questions about PostgreSQL.

What are the main architectural differences in PostgreSQL vs MySQL?

PostgreSQL uses a process per connection, unclustered heap storage, and transactional DDL. MySQL uses a thread per connection, clusters data in the InnoDB primary key index, and commits DDL implicitly. MySQL uses less memory per connection and is fast on simple primary-key lookups, while PostgreSQL needs a pooler at scale but handles complex queries, secondary index scans, and rollable migrations more safely. See also our MySQL page.

How does vector search work in PostgreSQL?

With the open-source pgvector extension, embeddings are stored in regular tables and searched with SQL, using exact search or HNSW and IVFFlat indexes and distance metrics such as L2, inner product, and cosine. A dedicated vector engine becomes the better choice at very large scale or when you need managed hybrid search.

What is the maximum database and table size in PostgreSQL?

According to the PostgreSQL 18 documentation, database size is unlimited, a single table can reach 32 TB with the default 8 KB block size, and a single field can hold up to 1 GB. A table can have up to 1,600 columns, and large values are compressed or stored out of line by TOAST.

Why does PostgreSQL require routine VACUUM maintenance?

MVCC keeps old row versions after updates and deletes so concurrent transactions see consistent snapshots. Vacuuming reclaims that space, updates planner statistics, maintains the visibility map for index-only scans, and prevents transaction ID wraparound. Use standard VACUUM during normal operation, because VACUUM FULL blocks reads and writes.

How does PostgreSQL compare with SQL Server on licensing?

PostgreSQL is free to use, modify, and distribute for commercial purposes under the PostgreSQL License. Microsoft SQL Server is proprietary and licensed per core, with Standard and Enterprise editions and a free Express edition with strict resource limits, so large deployments carry significant license costs.

The People Behind

A team of skilled engineers, architects, and delivery managers, dedicated to making every engagement a success.

Let's discuss your project