PostgreSQL vs SQL Server: Architecture Compared
On this page
SQL Server vs PostgreSQL at a glance
You already know how a relational database works. Tables, indexes, transactions and execution plans all carry over. What trips SQL Server people up isn't SQL. It's a handful of architectural choices that make PostgreSQL behave differently under load, and some words that mean something else on the other side.
This lesson covers those differences, so the rest of the course makes sense. Here's the summary first:
| Topic | SQL Server | PostgreSQL |
|---|---|---|
| Licensing | Commercial, several editions | Free and open source, one edition |
| Connections | Worker threads inside one process | One operating system process per connection |
| Readers and writers | Readers wait for writers by default | Readers never wait for writers (MVCC) |
| Old row versions | tempdb version store, if snapshot isolation is on | Kept in the table, cleaned up by vacuum |
| Top-level unit | Instance | Cluster |
| Cross-database queries | Three-part names, linked servers | Not in one query, use schemas or an extension |
Licensing: one edition, no feature tiers
SQL Server comes in editions such as Enterprise, Standard and Express, and features like Always On availability groups need the expensive ones. PostgreSQL has a single edition under a permissive open-source license. Partitioning, replication, JSON and full-text search are all in the free product.
The trade-off is support. Microsoft is one vendor with one support line. PostgreSQL is built by a community, and paid support comes from cloud providers (Amazon RDS and Aurora, Azure Database for PostgreSQL, Google Cloud SQL) or specialist companies. A new major version ships every year and each is supported for five years. PostgreSQL 18 is current. PostgreSQL 19 reached its first release candidate on October 15, 2026, and is planned for November 19, 2026.
Processes instead of threads: the connection model
SQL Server runs as one process. Its own scheduler, SQLOS, hands requests to a pool of worker threads, so a new connection is cheap.
PostgreSQL does it differently. A supervisor process called the postmaster listens on port 5432. For every new connection it starts a separate operating system process, a backend, which handles that one session until it disconnects.
Two consequences matter in practice:
- Connections are expensive. Each one costs a process start and some memory, even when idle. An app that opens 2,000 connections works on SQL Server, but it will hurt PostgreSQL. That's why PostgreSQL deployments almost always use a connection pooler such as PgBouncer (Day 20).
- Crashes are contained differently. One backend crashing doesn't corrupt the others, but because they share memory, the postmaster restarts all sessions to be safe.
MVCC: readers don't wait for writers
In SQL Server's default READ COMMITTED mode, a SELECT that wants rows an UPDATE has locked waits for the lock. Blocking chains, sp_who2 and sys.dm_tran_locks are everyday tools.
PostgreSQL uses MVCC (multi-version concurrency control) for everything, all the time. An UPDATE doesn't overwrite a row. It writes a new version and keeps the old one. A reader sees the versions that were committed when its statement started, so it never waits for a writer.
If you've turned on READ_COMMITTED_SNAPSHOT in SQL Server, you already know this behavior. The difference is where old versions live. SQL Server keeps them in tempdb. PostgreSQL keeps them in the table itself, and a background process called autovacuum removes them once no transaction needs them. That's new territory for most SQL Server DBAs, and Day 18 covers it properly.
Blocking still exists in PostgreSQL: two writers updating the same row still wait for each other. You'll investigate it with pg_stat_activity and pg_locks instead of the DMVs.
Terminology: instance, cluster, database and schema
This table prevents a lot of confusion:
| SQL Server | PostgreSQL | Watch out for |
|---|---|---|
| Instance | Cluster | "Cluster" means one server and its data directory, not several machines |
| Database | Database | You connect to one database, and can't join across databases in one query |
Schema (dbo) | Schema (public) | Used much more in PostgreSQL to organize objects |
| Login | Role with LOGIN | PostgreSQL has one concept, the role (Day 22) |
| Database user | Role | Roles act as both users and groups |
| Linked server | postgres_fdw extension | Remote access needs an extension |
The database row needs the most attention. In SQL Server, SELECT * FROM Sales.dbo.Orders JOIN Billing.dbo.Invoices ... works across databases on the same instance. In PostgreSQL, a connection belongs to one database. If your SQL Server databases are queried together, migrate them as schemas in one PostgreSQL database: sales.orders and billing.invoices. Keep separate databases only for data that's truly independent.
If you already have a PostgreSQL server available, you can see both levels:
SELECT datname FROM pg_database WHERE NOT datistemplate; -- databases in this cluster
SELECT schema_name FROM information_schema.schemata; -- schemas in this databaseCheck yourself
Each connection is a separate process with its own memory, so 1,500 of them will strain the server. Put a connection pooler such as PgBouncer in front, so a few dozen server connections serve all the clients.
Make them two schemas in one database, sales and billing. Queries then join sales.orders to billing.invoices directly, which separate PostgreSQL databases wouldn't allow.
The report reads a snapshot of committed row versions, so it neither waits for nor blocks the updates. The cost is that old row versions pile up until vacuum can clean them, which matters for very long transactions.
No. There's one edition, released under the permissive PostgreSQL License, with every feature included, from partitioning to parallel query. Costs come from hosting and support, not licenses. That changes capacity planning: you no longer limit cores or features to stay inside an edition.
A cluster is one running server instance with its data directory, like a SQL Server instance. It holds several databases, and queries can't join across databases directly. Each database holds schemas, which group tables much like SQL Server schemas do, and are the usual way to separate what used to be separate databases.