Skip to content
KB

PostgreSQL or SQL Server - how do they differ, and when is migrating from one to the other worth it?

howto · sql · databases · postgresql · sql-server · migration · data-types · performance · licensing

Neither is faster in general. Move to PostgreSQL to drop per-core licences, run on any host and gain its types and extensions. Move to SQL Server for the Microsoft data stack, columnstore analytics, or a vendor that certifies only it. Migrate only when that gain beats the cost, which is in the code (procedures, dialect, collation, locking), not the data.

PostgreSQL and Microsoft SQL Server are both mature relational databases with ACID transactions, a cost-based optimiser, parallel query, row-level security, JSON, full-text search, partitioning and streaming replication. For an ordinary transactional application either one carries the load, and a well-indexed schema matters far more than the engine. What really separates them is money and licensing, the ecosystem around the engine, a handful of engine design choices (concurrency, storage, plan caching, connections), the dialect, and the data types. This entry compares them on those axes as of PostgreSQL 18 and SQL Server 2025, then gives the reasons people move in each direction, what a migration really costs, and a decision procedure.

Aspect PostgreSQL SQL Server
Licence PostgreSQL License (permissive, BSD-like): free for any use, fork or embed Commercial, per core (or server + CAL); free Express and Developer editions
Who ships it A community project; support from many vendors and every cloud Microsoft; one vendor, one support line
Platforms Linux, Windows, macOS, BSD; any container Windows and Linux (and containers on Linux)
Managed offerings Amazon RDS and Aurora, Azure Database for PostgreSQL, Google Cloud SQL and AlloyDB, many others Azure SQL Database and Managed Instance, Amazon RDS, Cloud SQL
Default concurrency MVCC: readers never block writers Locking READ COMMITTED: readers wait for writers unless snapshot isolation is turned on
Table storage Heap; indexes point to row versions Clustered index (the table is a B-tree on its key) or heap
Connection model One OS process per connection; a pooler is needed past a few hundred Threads; thousands of connections are normal
Plan reuse Per session (prepared statements) Global plan cache shared by all sessions
Procedural code PL/pgSQL plus PL/Python, PL/Perl and others; functions and procedures T-SQL; batches, procedures, functions, triggers, CLR
Extensibility Extensions: PostGIS, pgvector, TimescaleDB, Citus, pg_cron and hundreds more; custom types, operators and index methods Fixed engine; CLR assemblies, external languages
Analytics Row store; columnar only through extensions or a separate engine Columnstore indexes with batch-mode execution in every edition
Bundled tooling psql, pg_dump, pg_basebackup; the rest from the ecosystem (pgAdmin, DBeaver, pgBackRest, Patroni) SSMS, SQL Server Agent, SSIS, Analysis Services, Power BI Report Server, Query Store

This is the most common reason anyone migrates, so get the numbers before anything else.

  • PostgreSQL costs nothing to license at any size. You pay for hardware or a managed service, and for support if you buy it. No feature sits behind a paid edition.
  • SQL Server charges per core in the core-based model, sold in two-core packs with a minimum of four cores per server or virtual machine; Enterprise costs several times Standard. In the cloud the licence is folded into the hourly price, which is why the same instance size usually costs far more as SQL Server than as PostgreSQL on Amazon RDS or Azure.
  • What the edition caps (SQL Server 2025). Express is free but is limited to the lesser of one socket or four cores, a 1,410 MB buffer pool and 50 GB per database, with no SQL Server Agent. Standard is limited to the lesser of four sockets or 32 cores and a 256 GB buffer pool (24 cores and 128 GB in 2022 and earlier). Standard has only basic availability groups (two replicas, one database, no readable secondary), no online index rebuild, and batch mode capped at two threads. Full Always On availability groups with readable secondaries, online index operations, and most of the intelligent query processing family are Enterprise only.
  • Developer editions are free but not for production. Since 2025 there are two, Standard Developer and Enterprise Developer, so a team can test against the edition it will really run.

The trap here is a cost model that compares only licences. A migration costs engineering time, retraining, new monitoring and backup tooling, and a period of running both systems. A Standard licence for a small server is often cheaper than one month of a team porting stored procedures. The licence argument is strong when Enterprise is needed for availability groups or for more than the Standard caps, when many instances or cores are involved, or when the product is shipped to customers who would each have to buy SQL Server.

Most types map one to one; the ones that do not are where data is lost silently or queries change meaning. Map every column by hand and test the edge values (maximum length, minimum date, fractional seconds, case).

SQL Server PostgreSQL What changes
bit boolean WHERE flag = 1 fails in PostgreSQL: write WHERE flag or = true. Drivers return bool, not 0/1.
tinyint (0 to 255) smallint PostgreSQL has no 1-byte or unsigned integer; add CHECK (x BETWEEN 0 AND 255) to keep the range.
smallint, int, bigint smallint, integer, bigint Same ranges.
int IDENTITY(1,1) integer GENERATED ALWAYS AS IDENTITY Both are backed by a sequence-like counter; after a bulk load, reset it in PostgreSQL with setval or the next insert collides. SCOPE_IDENTITY() becomes INSERT ... RETURNING id.
decimal(p,s), numeric(p,s) numeric(p,s) Same; PostgreSQL also accepts numeric with no limit (up to 131,072 digits before the point).
money, smallmoney numeric(19,4), numeric(10,4) PostgreSQL’s own money depends on the lc_monetary setting and is best avoided; neither engine’s money type carries a currency.
float, real double precision, real Same IEEE types. float(n) maps by precision in both.
char(n), varchar(n) char(n), varchar(n) or text SQL Server’s n counts bytes (with a UTF-8 collation a character can take up to four); PostgreSQL’s counts characters. PostgreSQL text and varchar without a limit cost the same.
nchar(n), nvarchar(n), nvarchar(max) varchar(n) or text A PostgreSQL database has one encoding, normally UTF-8, so there is no separate Unicode type and no N'...' prefix.
varchar(max), text, ntext text text/ntext are deprecated in SQL Server anyway.
binary(n), varbinary(n), varbinary(max), image bytea Large values are stored out of line (TOAST) automatically; there is no FILESTREAM, so very large files belong in object storage.
uniqueidentifier uuid Same 16 bytes, different sort order: SQL Server orders a GUID by its last six bytes first, PostgreSQL byte by byte. ORDER BY id and range scans give other results. NEWSEQUENTIALID() order does not carry over; use uuidv7() (PostgreSQL 18) for new time-ordered keys.
date date Same, though SQL Server’s range starts at year 1 and PostgreSQL’s earlier.
time(n) time(n) SQL Server stores to 100 ns (7 digits), PostgreSQL to 1 µs (6): the seventh digit is rounded away.
datetime timestamp(3) datetime is rounded to 1/300 s (values end in .000, .003, .007), so migrated values look slightly odd but fit.
datetime2(n) timestamp(n) 7-digit precision loses the last digit, as with time.
smalldatetime timestamp(0) Minute precision in SQL Server.
datetimeoffset timestamptz timestamptz converts to UTC and does not keep the offset; it is shown in the session’s TimeZone. If the original offset matters, store it in a second column.
rowversion (timestamp) an explicit version column, or the system column xmin Not a date. PostgreSQL has no auto-incrementing row version; optimistic concurrency uses a version column bumped by the application or a trigger, or reads xmin, which changes on every update.
xml xml Both exist; PostgreSQL has XPath functions but no XML indexes (index an expression instead).
nvarchar holding JSON, or json (2025) jsonb jsonb is binary, indexable with GIN and has rich operators; SQL Server 2025 adds a native binary json type and JSON indexes, and earlier versions keep JSON as text.
geography, geometry PostGIS geography, geometry PostGIS is an extension, and the most capable spatial engine of the two.
hierarchyid ltree extension Different functions; queries are rewritten.
sql_variant none (jsonb or separate columns) No equivalent.
table-valued parameter, table type arrays (integer[]), composite types, unnest PostgreSQL passes sets as arrays; SQL Server has no array type.
vector (2025) pgvector vector Both store embeddings and search by distance; pgvector’s HNSW index is mature, SQL Server’s vector index was still in preview at 2025’s release.
none array, range types (tstzrange), enum, domains, inet, cidr, interval, composite types PostgreSQL-only types. Moving to SQL Server turns each into a child table, two columns or a CHECK constraint.

This is the trap that breaks the most queries after a migration, because nothing fails: results just change.

  • SQL Server’s usual default collations are case-insensitive and accent-sensitive (SQL_Latin1_General_CP1_CI_AS on an English-locale server), so 'abc' = 'ABC' is true and a unique index treats them as duplicates.
  • PostgreSQL compares strings case-sensitively by default. Use lower(col) with an expression index, ILIKE, the citext extension, or a nondeterministic ICU collation (CREATE COLLATION ... (provider = icu, locale = 'und-u-ks-level2', deterministic = false)); LIKE works on nondeterministic collations only from PostgreSQL 18.
  • Identifiers fold differently. PostgreSQL folds unquoted names to lower case, so CREATE TABLE Orders makes orders, and a name created quoted as "Orders" must be quoted forever after. Tools that copy a schema with its mixed-case names quoted leave every query needing quotes; convert names to snake_case during the move. SQL Server resolves identifiers by the database collation, normally case-insensitively.
  • Trailing spaces. SQL Server ignores trailing spaces when comparing varchar values ('a' = 'a ' is true); PostgreSQL does not for varchar and text.
  • Concatenation and NULL. 'a' + NULL is NULL in SQL Server; PostgreSQL uses ||, which is also NULL with a NULL operand, while concat() skips NULLs in both. SQL Server 2025 accepts || too.

Benchmarks that crown one engine say more about their configuration than about the engines. What decides how a given workload behaves is these design differences.

PostgreSQL keeps old row versions in the table itself, so a reader sees a snapshot and never waits for a writer, and a writer never waits for a reader. The price is VACUUM: dead row versions stay in the table and its indexes until autovacuum removes them. A long-running transaction, an abandoned replication slot or a forgotten idle in transaction session stops cleanup, and tables bloat. An update writes a whole new row version and, unless it qualifies as a HOT update (no indexed column changed, room on the page), new entries in every index, so update-heavy tables with many indexes write more than in SQL Server. The 32-bit transaction counter must be frozen by vacuum before it wraps, which autovacuum does unless it is starved.

SQL Server under its default READ COMMITTED takes shared locks: a reader waits for an uncommitted write to the rows it reads, and many applications answer that with NOLOCK, which reads uncommitted and even duplicated or missing rows. Turning on READ_COMMITTED_SNAPSHOT (RCSI) gives PostgreSQL-like behaviour by keeping versions in tempdb (or in the database with accelerated database recovery); Azure SQL Database has it on by default. Optimized locking (2025, Standard and Enterprise) cuts lock memory and escalation further.

So an application moving from SQL Server to PostgreSQL loses its blocking, and one that relied on blocking to serialise work (read a row, then update it, assuming nobody else could) now needs SELECT ... FOR UPDATE or a serializable transaction. The other way, an application from PostgreSQL meets blocking and deadlocks it never had, unless RCSI is turned on before it goes live.

A SQL Server table with a clustered index is stored in key order, so a range scan on that key reads adjacent pages and a lookup by it is one seek. PostgreSQL tables are always heaps; every index is secondary, and CLUSTER reorders a table once without keeping it ordered. Covering indexes (INCLUDE) and index-only scans close most of the gap. The consequence for a port: a random key such as a v4 UUID as the clustered key fragments a SQL Server table badly, while in PostgreSQL it only fragments the index.

Need PostgreSQL SQL Server
Ordinary lookups and ranges B-tree (skip scan on multicolumn indexes from 18) B-tree, clustered or nonclustered
Index on part of a table Partial index, WHERE any expression Filtered index, simple predicates only
Index on an expression Expression index, lower(email) Index on a persisted computed column
JSON, arrays, full text GIN JSON index (2025), full-text catalog (separate service)
Geometry, ranges, nearest neighbour, exclusion constraints GiST, SP-GiST Spatial indexes
Huge append-only tables ordered by time BRIN, one small summary per range of pages Clustered columnstore
Analytics over many rows none built in (extensions) Columnstore with batch mode

SQL Server compiles a plan once and shares it across all sessions; the plan is built for the first parameter values it sees, which is fast and the cause of the classic “parameter sniffing” slowdown when later values need another plan. Query Store, plan forcing and the parameter-sensitive plan optimisation (2022) are the tools for it. PostgreSQL plans each query unless it is a prepared statement, and a prepared statement switches to a generic plan after five executions if that one is not worse; plan_cache_mode controls it. PostgreSQL has no query hints in core (the pg_hint_plan extension adds them); the remedy is statistics (CREATE STATISTICS for correlated columns) and configuration.

Each PostgreSQL connection is an operating system process with its own memory, so a few hundred connections is a sensible ceiling, and an application with many app servers or serverless functions needs a pooler (PgBouncer in transaction mode, or the one built into the managed service). Session state (temporary tables, SET, advisory locks, prepared statements on older PgBouncer) does not survive transaction pooling. SQL Server’s thread model takes thousands of connections, and client drivers pool on the client.

SQL Server’s columnstore indexes with batch-mode execution compress a fact table many times over and scan it an order of magnitude faster than a row store, inside the transactional database; the intelligent query processing features (memory grant and degree-of-parallelism feedback, adaptive joins) are mostly Enterprise. PostgreSQL has parallel scans, joins and aggregates and JIT compilation, but no columnar storage in core; heavy analytics there goes to an extension (Citus columnar, TimescaleDB compression), a replica feeding a warehouse, or a column engine. A reporting workload over hundreds of millions of rows is the clearest performance reason to prefer SQL Server.

Both do parallel queries, partitioning (declarative in PostgreSQL, by partition function in SQL Server), table compression (TOAST compression only in PostgreSQL), materialised views (PostgreSQL refreshes on demand; SQL Server indexed views stay current but restrict the query and are used automatically only in Enterprise), row-level security, encrypted connections, temporal or time-series tables (system-versioned temporal tables are built into SQL Server; PostgreSQL uses an extension or triggers), and logical change capture (logical decoding versus CDC and change tracking).

SQL Server PostgreSQL
SELECT TOP (10) ... ... LIMIT 10 (both accept OFFSET ... FETCH NEXT ... ROWS ONLY)
ISNULL(a, b) COALESCE(a, b) (works in both)
GETDATE(), SYSDATETIME(), SYSUTCDATETIME() now() is the start of the transaction, the same for every row and statement in it; clock_timestamp() is the moment of the call
DATEADD(day, 1, d), DATEDIFF(day, a, b) d + interval '1 day', b::date - a::date or extract(epoch from b - a)
LEN(s) (ignores trailing spaces) length(s) (counts them)
CHARINDEX, STUFF, STRING_AGG strpos, overlay, string_agg
@variable, DECLARE in any batch variables only inside PL/pgSQL (DO block, function, procedure)
IF EXISTS (...) ... ELSE in a plain batch inside PL/pgSQL; or CREATE TABLE IF NOT EXISTS, DROP ... IF EXISTS
#temp tables, @table variables CREATE TEMP TABLE (adds catalog churn when created per call), CTEs, arrays
MERGE (needs HOLDLOCK to be safe from concurrent inserts) INSERT ... ON CONFLICT DO UPDATE for upserts; MERGE exists from 15 and gains RETURNING in 17
OUTPUT inserted.* RETURNING * (OLD and NEW in RETURNING from 18)
CROSS APPLY, OUTER APPLY CROSS JOIN LATERAL, LEFT JOIN LATERAL ... ON true
A procedure returning result sets with SELECT A function RETURNS TABLE or SETOF; a procedure returns sets only through refcursors
TRY ... CATCH, THROW, @@ERROR BEGIN ... EXCEPTION WHEN ... END (each such block is a subtransaction, so it costs), RAISE
BEGIN TRAN inside a procedure, nested transactions counted by @@TRANCOUNT COMMIT/ROLLBACK allowed in procedures (11+), not in functions; no nesting, savepoints instead
Cross-database queries db.schema.table, linked servers One database per connection; schemas inside one database, or postgres_fdw
SQL Server Agent jobs pg_cron, or the scheduler outside the database
sp_executesql EXECUTE format(...) USING ... in PL/pgSQL, or parameters from the client

DDL is transactional in both engines for almost all statements, so a failed migration script rolls back either way; PostgreSQL’s CREATE INDEX CONCURRENTLY and SQL Server’s ONLINE = ON (Enterprise) are the exceptions that cannot run in a transaction or need an edition.

Area PostgreSQL SQL Server
High availability Streaming replication built in (synchronous or not); automatic failover from Patroni, repmgr or the managed service Always On availability groups (Enterprise; basic AG in Standard), failover cluster instances, log shipping
Readable replicas Hot standby, any edition Enterprise availability groups
Backup pg_basebackup plus WAL archiving for point-in-time recovery; pgBackRest or Barman; incremental base backups from 17; pg_dump for logical copies BACKUP DATABASE full, differential and log backups, point-in-time restore, compression and encryption built in
Upgrades Major version by pg_upgrade (keeps statistics from 18) or logical replication; a major release every year, each supported five years In-place or side-by-side; a release every two to three years, compatibility level keeps old behaviour
Monitoring pg_stat_statements, pg_stat_activity, EXPLAIN (ANALYZE, BUFFERS), auto_explain Query Store, DMVs, Extended Events, actual execution plans in SSMS
Authentication Passwords (SCRAM), certificates, Kerberos/GSSAPI, SSPI on Windows, LDAP, OAuth from 18 Windows integrated (Active Directory), SQL logins, Microsoft Entra ID
Resource limits per workload Per role settings (work_mem, statement_timeout); no resource governor Resource Governor (Standard from 2025)

Why teams move from SQL Server to PostgreSQL

Section titled “Why teams move from SQL Server to PostgreSQL”
  1. Licence cost, above all when Enterprise is needed only for availability groups, readable replicas or capacity beyond Standard, or when the product is deployed per customer.
  2. Portability and no lock-in. The same engine runs on every cloud, on premises and in a container in CI, with several vendors competing to host it.
  3. Linux and container operations as the default rather than a port: images, Kubernetes operators, infrastructure as code.
  4. Types and extensions: arrays, ranges with exclusion constraints, jsonb with GIN, PostGIS, pgvector, TimescaleDB, pg_cron. A feature that would be a separate product elsewhere is often an extension.
  5. Readers never block writers without configuring snapshot isolation, and every feature in every deployment, with no edition matrix to check.
  6. The ecosystem’s default. New frameworks, ORMs and hosted platforms treat PostgreSQL as the first-class target.

Why teams move from PostgreSQL to SQL Server

Section titled “Why teams move from PostgreSQL to SQL Server”
  1. The Microsoft data stack: SSIS, Analysis Services, Power BI and Fabric mirroring, Active Directory and Entra ID sign-in, and an organisation whose DBAs know SQL Server.
  2. Analytics inside the operational database: columnstore with batch mode, and an optimiser with adaptive feedback, for reporting over large tables without a separate warehouse.
  3. A single vendor accountable for the engine, the tools and the support contract, and long fixed support lifecycles.
  4. A third-party application certified only on SQL Server, or a customer contract that requires it.
  5. Built-in features PostgreSQL leaves to extensions or tools: system-versioned temporal tables, ledger tables, Always Encrypted, Resource Governor, SQL Server Agent, global plan caching for very many short identical queries, very high connection counts without a pooler.
  6. Operations pain at scale with PostgreSQL (vacuum and bloat on update-heavy tables, connection storms) that a team would rather trade for a different set of limits.

The data is the cheap part: a tool moves tables and rows, and the type mapping above is mechanical once decided. The expensive parts are:

  • Server-side code. Every procedure, function, trigger and view in T-SQL or PL/pgSQL is rewritten and retested; automatic converters handle syntax, not semantics (transactions, error handling, result sets). Count the objects and lines first; this number drives the estimate. Logic kept in the database is what ties an application to its engine.
  • Behaviour that does not fail loudly: case-insensitive comparisons, trailing spaces, now() at transaction start, GUID ordering, lost offsets in timestamptz, blocking that the code relied on, implicit conversions SQL Server allows and PostgreSQL refuses.
  • The application’s SQL: raw queries in code, reports, ORM mappings (an ORM hides most of the dialect; EF Core, Hibernate and SQLAlchemy all support both, but not quoted identifiers or engine-specific functions the code calls directly).
  • Operations: backup, monitoring, high availability, user and permission scripts, scheduled jobs, the people who run them.
  • Cutover: a one-time load with downtime, or change data capture (AWS DMS, Debezium, logical replication) to keep the target in step until a short switch.

Tools a search turns up first: AWS Schema Conversion Tool and Database Migration Service (SCT converts schema and code from SQL Server to PostgreSQL only, with a report of what it could not convert; DMS moves the data in either direction, but nothing converts PostgreSQL schema and code to SQL Server for you, and Microsoft’s SQL Server Migration Assistant takes no PostgreSQL source, so that direction is ported by hand), pgloader (SQL Server to PostgreSQL in one command, data and schema, no procedure conversion), Babelfish for Aurora PostgreSQL (PostgreSQL that speaks the SQL Server wire protocol and much of T-SQL, so an application connects largely unchanged; it is offered as a managed service only on Aurora, does not cover all of T-SQL, and keeps the application written for SQL Server, which defers the port rather than doing it), and commercial converters. Each speeds up the mechanical part; none removes the testing.

A migration’s safety net is the same as a multi-engine application’s: one migration history and the full test suite run against both engines in CI.

flowchart TD
  A[Why change engines?] --> B{Vendor or contract<br/>requires one?}
  B -- yes --> V[Use the required one]
  B -- no --> C{Licence cost or<br/>edition caps hurt?}
  C -- yes --> P1[PostgreSQL is a candidate]
  C -- no --> D{Microsoft BI stack or<br/>in-database columnstore?}
  D -- yes --> S1[SQL Server is a candidate]
  D -- no --> E{Need PostgreSQL types<br/>or extensions?}
  E -- yes --> P1
  E -- no --> K[Stay where you are]
  P1 --> F{Gain over years beats<br/>cost of porting code?}
  S1 --> F
  F -- no --> K
  F -- yes --> G[Port and test both<br/>in parallel, then cut over]

Run it with numbers, in this order:

  1. Constraints first. A vendor certification, a contract, a host that offers only one engine, or a regulator decides it before any comparison.
  2. Write the reason down as a measurable gain: licence per year, hours of on-call, a feature the business needs. “PostgreSQL is modern” or “SQL Server is enterprise” is not a reason.
  3. Inventory the cost: number of procedures, functions, triggers and lines of database code; raw SQL in the application; columns with the risky types above (uniqueidentifier keys, datetimeoffset, rowversion, money, sql_variant, hierarchyid, arrays, ranges); case-insensitive behaviour the application relies on; jobs, reports and integrations that connect directly.
  4. Prove the hot path: port the ten heaviest queries and the busiest write path with a copy of production data, and compare plans and timings with the engine tuned (PostgreSQL’s shared_buffers, work_mem, autovacuum; SQL Server’s RCSI, max memory, MAXDOP).
  5. Decide on the totals: gain over three to five years against port, test, parallel run and retraining. If the gain is only licence and the code base is mostly T-SQL or PL/pgSQL, it rarely pays; if the logic lives in application code behind an ORM, it usually does.
  6. For a new system with no constraints, the default is PostgreSQL: no licence, no edition caps, every host. Choose SQL Server when the organisation already runs it well or needs its analytics and Microsoft integration.
  • Row counts and checksums per table match (a hash over a canonical text form of each row, computed on both sides).
  • The edge values survive: longest strings, earliest and latest dates, seven-digit fractions, non-ASCII text, GUIDs read back in the same order the application expects.
  • The full test suite passes on the target, including tests that run two sessions at once (the locking difference shows up only there).
  • The heaviest queries have acceptable plans under production volume (EXPLAIN (ANALYZE, BUFFERS) against the SQL Server actual plan), and autovacuum or index maintenance keeps up under the real write rate.
  • A backup has been restored on the target and the failover tested before the cutover, not after.

For an application that uses an ORM, little database code and moderate data, either engine works and the one the team already operates well is the right one; a migration there changes cost, not capability. Azure SQL Database and Managed Instance, Amazon Aurora PostgreSQL and other cloud variants differ from the boxed products in features and limits, so check the variant’s own feature list. A workload that is mostly analytics over billions of rows, full-text search or graph traversal belongs in an engine built for it, and neither comparison here decides that; whether the data is relational at all is a separate question.

Source: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2025

History: 2 changes
  1. Made public; body changed
  2. Created