Read committed, snapshots and what happens when a reader meets a writer
I've spent more debugging hours on this one difference than on everything else in this article put together, so it gets more room. It also generates more confused bug reports than anything else in either direction. Relative speed has nothing to do with it.
PostgreSQL is multiversion from the ground up. The default isolation level is Read Committed, and a SELECT sees a snapshot taken at the instant the statement began. Readers don't block writers. Writers don't block readers. Two transactions updating the same row still wait on each other, and the documentation is blunt about the trade: an updating command can see an inconsistent snapshot, because it sees concurrent effects on the rows it's updating but not on other rows.
SQL Server defaults to Read Committed implemented with shared locks. A long-running report holds locks that block writers, a writer holds locks that block the report, and the folklore answer is to sprinkle WITH (NOLOCK) everywhere and accept dirty reads. That folklore is the reason so many SQL Server codebases read rows that were never committed.
First thing I run on any SQL Server instance I inherit:
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases;
If is_read_committed_snapshot_on comes back 0, Read Committed is doing it the locking way. Turning on read committed snapshot isolation gets you behaviour close to PostgreSQL's default. SQL Server parks the row versions in tempdb, where PostgreSQL keeps them in the table itself:
ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
The switch needs exclusive access to the database, which is what the termination clause is buying you, and it will roll back other sessions. Do it in a window.
The snapshot_isolation_state_desc column in that query reports a different setting, ALLOW_SNAPSHOT_ISOLATION, which is not the same switch.
Once RCSI is on, tempdb carries the version store. Give it a fast volume, split it into multiple data files if the instance is busy and set an alert on file growth. PostgreSQL charges for the same guarantee in a different currency: dead row versions accumulate in the heap, autovacuum cleans them up, and a table with a hot update pattern and a badly tuned autovacuum will bloat until somebody notices the disk. Check n_dead_tup in pg_stat_user_tables against your busiest tables and tune autovacuum there before the disk makes the decision for you.
Tempdb is one of those names that stops making sense once you know what's in it. Version store, hash spills, table variables, the internal worktables the optimiser creates for a big sort, and on a busy instance it's the file you'll see waits against first. The name comes from a time when it held temporary tables and not much else. Anyway.
T-SQL and PL/pgSQL: the dialect differences that cost time
Both speak SQL, and most of an ordinary CRUD application ports without anyone thinking about it. The rest is where your fortnight goes.
Identity columns and sequences
T-SQL uses the IDENTITY(1,1) column property and you read the new key back with SCOPE_IDENTITY() or an OUTPUT clause. PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or the older serial types) backed by a real sequence object, and you read the key back with RETURNING:
-- SQL Server
INSERT INTO dbo.Orders (CustomerId) VALUES (42);
SELECT SCOPE_IDENTITY();
-- PostgreSQL
INSERT INTO orders (customer_id) VALUES (42) RETURNING id;
Sequences in PostgreSQL are addressable objects you can grant on, reset and share between tables. SQL Server has CREATE SEQUENCE too and hardly anybody uses it.
TOP, LIMIT and the portable form
SQL Server writes SELECT TOP (10) * FROM dbo.Orders, PostgreSQL writes SELECT * FROM orders LIMIT 10, and both support the ANSI form. If the SQL has to survive a migration, write the ANSI form:
SELECT * FROM orders
ORDER BY created_at DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
SQL Server requires an ORDER BY for OFFSET and FETCH. Paginating without one is how you get duplicate rows on page three.
String concatenation and NULL
SQL Server concatenates with + (and, from 2025, || as well), PostgreSQL with ||. The overloaded plus sign is worse than it looks: because int outranks every string type in T-SQL's data type precedence list, 'abc' + 1 tries to convert the string to an integer and fails at runtime. No concatenation happens. In PostgreSQL, 'abc' || 1 gives you abc1.
NULL behaves the same way in both once you're using the operator, in that any NULL operand poisons the result. Both also have a CONCAT() function that treats NULL as an empty string. A port that swaps + for || mechanically keeps working. A port that swaps + for CONCAT() silently changes the results of every row with a NULL in it.
Date functions
The boundary-counting behaviour gets proper treatment under the T-SQL date and time functions, which is the page I'd read before writing any conversion rules. Everything else here is tedious without being hard. GETDATE() and SYSDATETIME() become now() or current_timestamp. DATEADD(day, 7, x) becomes x + interval '7 days'. DATETRUNC(month, x) becomes date_trunc('month', x), with the datepart quoted on the PostgreSQL side.
DATEDIFF is the one that produces wrong numbers quietly; nothing errors. T-SQL's version counts datepart boundaries crossed, so DATEDIFF(year, '2005-12-31 23:59:59', '2006-01-01 00:00:00') returns 1 for a gap of one second. PostgreSQL has no direct equivalent and you subtract timestamps to get an interval, which means somebody has to decide what the original code was really asking for.
Case sensitivity and collation defaults
This is the one that breaks applications weeks after the migration, in a code path nobody tested.
A default SQL Server install on an English locale uses SQL_Latin1_General_CP1_CI_AS. CI means case-insensitive, so WHERE Email = '[email protected]' matches a stored value of [email protected]. AS means accent-sensitive, so it will not match bó[email protected].
PostgreSQL's standard collations are deterministic, which makes them case-sensitive and accent-sensitive. The same query returns zero rows. The application reads that as a bad password, so the login form stops working for everyone who capitalised their email address, and nothing in the log explains why. Three options: lower() on both sides with a matching expression index, the citext type or a nondeterministic ICU collation.
CREATE COLLATION ci_ai (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
The nondeterministic route costs you performance and B-tree indexes can't use deduplication on those columns. I use citext for email and username columns and leave everything else deterministic, which has never caused me a problem and does mean the collation decision lives in the schema where somebody will see it.
Identifiers fold differently too. PostgreSQL lowercases unquoted identifiers, so a table created as "MyTable" with quotes needs quotes forever afterwards. Several of the tools that generate a PostgreSQL schema from a SQL Server one emit quoted identifiers by default, and you end up with a database where every query needs double quotes.
Schemas, dbo and public
SQL Server names objects database.schema.object, with dbo as the default schema, and a user's default schema is set server-side per database with ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;. PostgreSQL uses public as the default schema and resolves unqualified names through search_path, which is a session setting.
Cross-database queries are where this turns into work. In SQL Server, SELECT * FROM Reporting.dbo.Invoices from inside another database on the same instance just works. PostgreSQL keeps separate databases in a cluster properly isolated: you need postgres_fdw or dblink to reach across, and most teams restructure those databases as schemas instead. If your application relies on cross-database joins, budget for that rewrite by name and not as a line item called migration.
PostgreSQL vs MSSQL for five common situations
Five cases where the PostgreSQL vs MSSQL answer is clear enough to state in one paragraph. The answer comes first in each.
A .NET shop
The reflex is SQL Server and the reflex is half wrong. Npgsql is a first-class EF Core provider and modern .NET on PostgreSQL is completely ordinary. If you already own SQL Server licences with cores to spare, use them. A small .NET team standing up a new service on a rented Linux box saves four core licences on day one with PostgreSQL and pays a fortnight of unfamiliarity for it.
A SaaS with a database per tenant
SQL Server wins this one outright. Thousands of databases on one instance is a pattern it handles well, cross-database queries work without extra machinery and backup and restore per tenant is a solved operation. PostgreSQL can hold many databases per cluster, and the connection accounting gets awkward quickly enough that most teams end up on pgbouncer with a schema-per-tenant design. That works, and it is a different architecture you're now maintaining.
An analytics side project
PostgreSQL, with a caveat. SQL Server's clustered columnstore indexes are excellent and one of the few places the paid engine wins on technical grounds alone. For a side project you are not buying Standard to get them.
An existing SQL Server estate
Stay. The migration cost is real, your team knows the failure modes and the licence is already paid for this year. Move specific new services to PostgreSQL if you want to and leave the estate alone. Rewriting a working system to save a licence fee is a project that overruns.
A greenfield API
PostgreSQL. Every hosting option supports it, the driver situation is good in every language, the extensions cover the things you'll want in year two and nothing about it is per-core. The exception is a small internal tool that has to feed a Microsoft reporting layer, where SQL Server Express may cover the whole job for free.
Vector search: SQL Server 2025 against pgvector
SQL Server 2025 added a native vector type, declared as VECTOR(dimensions) with an optional base type, supporting 1 to 1,998 dimensions with float32 as the default. VECTOR_DISTANCE does exact k-nearest-neighbour search. That much is generally available and it works.
What makes vector search practical at scale is not. Microsoft's documentation on vectors states that approximate vector indexes, which are DiskANN-based, and VECTOR_SEARCH are in preview. Exact kNN over a million rows means scanning a million rows. The 1,998-dimension ceiling constrains you as well, since embedding models emitting 3,072 dimensions don't fit at full width, and the half-precision float16 option that goes to 3,996 is itself a preview feature on SQL Server 2025.
pgvector has been in production use for years, is at 0.8.7 and supports PostgreSQL 13 and newer, with HNSW and IVFFlat indexes and six distance operators including cosine, L2 and inner product. Vectors store up to 16,000 dimensions, and an index takes up to 2,000 of them, 4,000 as halfvec and 64,000 as binary vectors. The pgvector repository documents the index tuning parameters that trade recall against speed, and there is a body of public experience behind those numbers that the SQL Server side does not have yet.
If retrieval-augmented search is a requirement this quarter and not a plan for next year, this section decides the whole comparison on its own. PostgreSQL wins it and it isn't close.
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
