PostgreSQL for a SQL Server professional

The commercial picture

Free is the smallest difference

The licence is the part you adjust to fastest. The release cadence is the part that changes how you plan your year.

	SQL Server	PostgreSQL
Licence	Per core, plus CALs. The edition you buy sets the feature list.	Free, all features. One edition: no Express 10 GB cap, no Enterprise-only index.
Support	About ten years per release, from a vendor you can escalate to.	Five years per major, from the community. A paid contract is optional: EDB, Crunchy Data, others.
Cadence	A new major every two or three years. You patch; you rarely re-platform.	A new major every autumn. Minors every quarter. You upgrade majors every two or three years regardless.
Upgrade	In place with setup, or an availability group to shorten the cutover.	Minor: swap binaries, restart. Major: `pg_upgrade` or dump and restore. The data directory format changes.

Put the upgrade on the calendar.

PostgreSQL 14 reaches end of life on 12 November 2026, five years after it shipped. Anything you stand up today carries a known expiry, and the clock starts on day one, not when you notice. That is the habit to build: a major upgrade is routine maintenance you schedule, not a project you fund.

The shape of a server

One cluster, many databases, no bridges

This is the difference that breaks the most migrations. In SQL Server a database is a namespace. In PostgreSQL it is a separate catalog with its own connections.

A cluster is the whole server. One initdb, one data directory, one port, one set of roles. That is the closest match to a SQL Server instance.
A database is a hard wall. No OtherDb.dbo.Table. A connection attaches to one database and never sees another. Crossing needs postgres_fdw, dblink, or the application.
Schemas are the crossing point. sales.orders and audit.log live in one database and join freely. Where SQL Server said "database", PostgreSQL usually wants "schema".
Roles are cluster-wide. There is no login-then-user split: create role ... login is what you called a login. Privileges are then granted per database, per schema.
Names resolve through search_path. A list of schemas, defaulting to "$user", public. A wrong path silently reads the wrong table.
Authentication lives in a file. pg_hba.conf decides which host, role and database may connect, and by which method. scram-sha-256 is the default; md5 is deprecated as of 18.

No sa, and no fixed server roles.

The bootstrap superuser is postgres and it is an ordinary account. There is no db_owner; you grant usage on the schema, select on the tables, and alter default privileges so tomorrow's tables behave the same. Built-in roles such as pg_read_all_data and pg_monitor cover the broad cases. And since 15, the public schema grants nobody create by default: the first "permission denied for schema public" is almost always that.

Storage

There is no clustered index

In SQL Server the primary key often is the table, and its order is the physical order. Here a table is an unordered heap and every index is a separate structure pointing into it.

No clustered keyword exists. A primary key is a unique index over the heap. It does not sort the rows, and it does not set their physical order.
cluster is a one-time rewrite. It reorders a table by one index and then stops maintaining that order. pg_repack does the same job without the exclusive lock.
Index-only scans need a clean table. A covering index with include can skip the heap, but only for pages vacuum has already marked all-visible. On a hot table the heap fetch comes back.
Heap-only updates are the prize. An update that touches no indexed column, and fits on the same page, writes no index entries at all. A fillfactor below 100 is what leaves that room.
Every index taxes every write. An update writes a new row version, and each index over a changed column takes a new entry. Drop the ones no query uses; there is no write-benefit to keeping them.

Concurrency

Readers never block writers

SQL Server defaults to locking: a reader waits for an uncommitted writer. PostgreSQL defaults to snapshotting: a reader sees the last committed version and never waits. What you get for free, something has to clean up.

with (nolock) has no equivalent and no purpose. Dirty reads are impossible. read uncommitted is accepted, then silently behaves as read committed.
The default is read committed, per statement. Each statement takes a fresh snapshot, so rows can change mid-transaction. repeatable read pins one snapshot; serializable uses SSI and can abort transactions.
Long transactions hold back cleanup. Vacuum cannot remove a row version that an open transaction might still see. One idle-in-transaction connection grows the table for everyone.
A transaction ID is 32 bits. Around 2.1 billion, the server refuses writes. Autovacuum's freeze prevents it, and a failsafe added in 14 makes vacuum skip non-essential work near the limit.
DDL is transactional, which cuts both ways. A failed alter rolls back cleanly, but it takes access exclusive: queued behind a long query, it blocks everything after it. Set lock_timeout and use create index concurrently.

The dialect

Same SQL, different words

About a dozen constructs account for most of the pain. Everything else is close enough that you will not notice it until something returns the wrong type.

SQL Server	PostgreSQL	Why it bites
`select top 10`	`limit 10`	A parse error, not a warning.
`getdate()`	`now()`	Returns `timestamptz`, not a naive timestamp.
`isnull(a, b)`	`coalesce(a, b)`	No `isnull` function exists.
`a + b`	`a \|\| b`	`+` is arithmetic only; concatenation errors.
`[brackets]`	`"quotes"`	Brackets never parse. Single quotes are always strings.
`go`	`—`	Client-side batching. `psql` uses `;` and backslash commands.
`identity(1,1)`	`generated always as identity`	`serial` is the older way and leaves an orphan sequence.
`merge`	`insert ... on conflict`	`merge` exists; `on conflict` is the idiom.
`scope_identity()`	`returning id`	The whole inserted row comes back, not just a number.
`select * into #t`	`create temp table as`	Temp tables are session-scoped, not block-scoped.
`sp_who2`	`pg_stat_activity`	This is where the DMVs went. `\d table` is `sp_help`.

psql replaces SSMS.

\d describes an object, \l lists databases, \dn lists schemas, \x flips to expanded output for wide rows. There is no live plan diagram and no plan cache to click into: explain (analyze, buffers) is the plan, and reading it is the skill you will actually be paid for.

Data types

The types map, until they do not

Most of the mapping is obvious. The dangerous part is that two types collide on one word, and the shorter name is the one with the trap.

SQL Server	Use instead	Watch out
`nvarchar(n)`	`text`	No speed difference. `varchar(n)` only adds a length check.
`nvarchar(max)`	`text`	No separate LOB type; large values move out of line on their own.
`datetime`	`timestamp`	No time zone. This is the trap that keeps taking money.
`datetimeoffset`	`timestamptz`	Stored as UTC, shown in the session `TimeZone`.
`bit`	`boolean`	Real true and false. No `0`/`1` arithmetic.
`money`	`numeric(19,4)`	PostgreSQL's `money` is locale-dependent. Do not use it.
`uniqueidentifier`	`uuid`	16 bytes, and not SQL Server's sort order.
`int identity`	`integer generated always as identity`	Or `bigint`. Sequence gaps are normal.
`varbinary(max)`	`bytea`	Large objects exist, but `bytea` is the default answer.
`xml`	`jsonb`	Indexable with GIN, and usually what you actually wanted.
`sql_variant`	`jsonb`	Nothing replaces it; `jsonb` is the nearest thing.
`hierarchyid`	`ltree`	An extension, not core.

Two NULL rules that will differ.

Pick timestamptz by default and convert to a local timestamp only at the edge of the application. And a unique index here accepts any number of NULLs, where SQL Server allows exactly one; if you need that back, declare unique nulls not distinct on 15 or later.

Running it

Backups, failover and the missing Agent

Almost none of the SQL Server tooling around the engine exists. Each replacement is smaller, separate, and better behaved once it is wired up.

You are used to	In PostgreSQL
SQL Server Agent	`pg_cron`, systemd timers, or cron. No scheduler ships with the server.
Full, differential and log backups	`pg_dump` for one database, or a base backup plus continuous WAL for point-in-time restore. There is no differential.
Database Mail	Nothing built in. Send from the application or a script.
Linked Servers	`postgres_fdw`, `dblink`, or a foreign data wrapper.
Always On availability groups	Streaming replication plus Patroni or repmgr. The server does not fail over by itself.
Transactional replication	Logical replication: `create publication`, `create subscription`.
Query Store	`pg_stat_statements` for the aggregate, `auto_explain` for the slow ones. No plan forcing.
SSIS, SSRS, SSAS	Not in the box. PostgreSQL is an engine, not a platform.
sys.dm_os_wait_stats	`pg_stat_activity.wait_event`, sampled per session.

Your first week

Install a supported major, not the newest. 18 is current; 19 is a release candidate with general availability planned for 19 November 2026, so it is not a day-one choice.
Size it before you tune it. shared_buffers around a quarter of RAM, max_connections near 100, and PgBouncer in front if the application pools badly.
Turn on pg_stat_statements and auto_explain. Without them you are blind in a way Query Store never let you be.
Decide the database boundary once. One database with many schemas, or many databases bridged by postgres_fdw or the application. This is the decision you cannot cheaply undo.
Port the schema by hand. Transliterate the syntax, then re-decide every type, starting with every datetime that should have been timestamptz.
Prove the backup. Base backup plus WAL archiving to object storage, then a real restore onto a scratch server before you trust the schedule.
Rehearse the upgrade. Run pg_upgrade against a copy now, so the first attempt is not the one that matters.

What you pick up in the trade.

Extensions are the real prize: postgis, pgvector, pg_partman, pg_trgm, pg_cron, timescaledb. create extension installs a capability that in SQL Server would have been a separate product with its own licence. jsonb with GIN indexes, arrays, ranges and copy for bulk load come with the server.