Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 1 · Lesson 1 · 5 min read

PostgreSQL vs SQL Server: Architecture Compared

On this page
  1. SQL Server vs PostgreSQL at a glance
  2. Licensing: one edition, no feature tiers
  3. Processes instead of threads: the connection model
  4. MVCC: readers don't wait for writers
  5. Terminology: instance, cluster, database and schema
  6. Check yourself

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:

TopicSQL ServerPostgreSQL
LicensingCommercial, several editionsFree and open source, one edition
ConnectionsWorker threads inside one processOne operating system process per connection
Readers and writersReaders wait for writers by defaultReaders never wait for writers (MVCC)
Old row versionstempdb version store, if snapshot isolation is onKept in the table, cleaned up by vacuum
Top-level unitInstanceCluster
Cross-database queriesThree-part names, linked serversNot 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.

The same request path in both engines. The difference is the third box: a dedicated process per connection in PostgreSQL, a shared worker thread in SQL Server.
The same request path in both engines. The difference is the third box: a dedicated process per connection in PostgreSQL, a shared worker thread in SQL Server.

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 ServerPostgreSQLWatch out for
InstanceCluster"Cluster" means one server and its data directory, not several machines
DatabaseDatabaseYou 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
LoginRole with LOGINPostgreSQL has one concept, the role (Day 22)
Database userRoleRoles act as both users and groups
Linked serverpostgres_fdw extensionRemote 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:

SQL
SELECT datname FROM pg_database WHERE NOT datistemplate;   -- databases in this cluster
SELECT schema_name FROM information_schema.schemata;        -- schemas in this database

Check 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.