Migrating from SQL Server to PostgreSQL: incompatibilities and a migration plan

What makes the migration hard

Organizations migrate from SQL Server to PostgreSQL for various reasons: licensing and total cost of ownership, import-substitution requirements, desire to avoid vendor lock-in, or changes in infrastructure (cloud, containers, changes in support team).

But migration is almost never a 1:1 copy. Even if "tables look the same", data types differ, functions and string comparisons behave differently, transaction semantics, locking rules and execution plans vary. So such a move usually becomes a project to adapt the application and processes, not just a schema transfer.

Usually what breaks is what had been taken for granted for years: T-SQL queries, stored procedures and triggers, dynamic SQL, and indexes that helped in one RDBMS but produce unexpected plans in another. Separate sources of surprises are dates and times, GUIDs, money/decimal, NVARCHAR and collations, and NULL behavior.

Before starting, it’s useful to quickly assess risk with a simple checklist:

• How much logic lives in the database (procedures, triggers, SQL Agent jobs).
• How dependent the application is on T-SQL and vendor-specific features.
• Data volume and allowable downtime window.
• Critical reports and the “heavy” queries that are time-sensitive.
• Requirements for fault tolerance, backups and recovery.

In organizations with strict support and delivery transparency requirements (government, finance), the risk is often not in "moving tables" but in ensuring that after the cutover all routines, reports and nightly loads work predictably.

Inventory: what to migrate and who depends on the DB

Before planning migration, understand exactly what you are moving. Often a "single database" actually includes multiple databases, sets of schemas, years of history and dozens of integrations that get remembered at the last minute.

Start with a simple passport of the current system: list of databases and schemas, key tables and their size, growth rates, critical reports and marts. Mark objects that are hard to recreate: complex views, functions, procedures, triggers.

Next, record dependencies. Besides the main application this usually includes ETL loads, BI reports, exchange services (queues, APIs), background jobs, file exports, accounting integrations and monitoring. Assign an owner to each dependency so changes are coordinated quickly.

To avoid surprises in performance, pick a "load profile" in advance: which operations do most of the reads and which do the writes. In practice 10–20 queries and 3–5 write scenarios give the main picture.

Minimum documentation to collect in one place:

• objects and data volumes (with monthly growth)
• critical reports and SLAs for response time
• list of dependent systems and contacts
• top queries and write scenarios by load
• acceptable downtime and maintenance windows

Example: a manufacturing company must close shifts and downtime is only allowed at night. This immediately sets requirements for the migration plan, rollback and cache warm-up after cutover.

Data type incompatibilities: common pitfalls

The most unpleasant bugs are usually not "wrong type" but a silent change of behavior: rounding, date comparisons, string sorting, auto-increment. Schemas can look similar, but in production reports suddenly don’t reconcile or unique keys break.

Numbers are simple until you hit a monetary field. INT and BIGINT usually map directly, but MONEY is better not migrated as-is. PostgreSQL doesn’t have a perfect analogue; it’s safer to move to NUMERIC(precision, scale) with explicit precision, otherwise you may see unexpected rounding in sums and taxes.

Identifiers cause surprises too. UNIQUEIDENTIFIER almost always becomes UUID. Check where UUIDs are generated: in SQL Server this might have been done by the application or by a default; in PostgreSQL it’s better to define generation explicitly and standardize the format.

Most date/time bugs occur here. DATETIME2 is usually mapped to timestamp, but you need to decide: without time zone or timestamptz. If part of the system treats times as local and another as UTC, comparisons and grouping by date will change after migration, especially around day boundaries.

For strings the main trap is ordering and case. NVARCHAR isn’t required separately in PostgreSQL: Unicode is supported by default. But collation and comparison rules differ, so a query like "find everything starting with A" may return a different set than on SQL Server.

For binary data, VARBINARY typically becomes bytea. If FILESTREAM was used, it’s often easier to move files to object storage or a file server and keep metadata and links in the DB.

Another topic is IDENTITY versus sequences. In PostgreSQL this is GENERATED ... AS IDENTITY or SEQUENCE, and you must check behavior on manual inserts: you can easily "jump" the counter and get key conflicts.

Quick mini-check before migrating a table:

• Are there MONEY columns and have they been mapped to appropriate NUMERIC precision?
• Are GUIDs present and where are they generated?
• Which time fields must be UTC and which must be local?
• Is there a dependence on collation/case in searches?
• How is auto-numbering implemented and are manual inserts possible?

Simple example: in a ticketing DB the created_at field was stored as local time in SQL Server and reports were built "per day." After moving to timestamptz without explicit conversion, some tickets shift to a neighboring date and daily totals stop matching.

SQL behavior and constraints: nuances that surface in production

Surprises often come not from data types but from how SQL behaves in real queries and transactions. These differences usually appear after release when data becomes "dirtier" and load increases.

A common source of hidden bugs is NULL and empty strings. In SQL Server an app may have lived for years assuming "empty" equals "". In PostgreSQL NULL and "" are always distinct, and conditions like = '', <> '', LIKE '%' or string concatenation can yield different results. Agree in advance what counts as "no value" and make it explicit in code (for example using COALESCE) and constraints.

Second risk area is quoting and name case. In SQL Server many are used to object names working without quotes and often case-insensitively (depending on collation). In PostgreSQL unquoted names are lower-cased and names in double quotes become case-sensitive. If your schema contains "CamelCase" tables or columns, this quickly leads to a chain of "not found" errors.

Check constraints carefully for real scenarios. CHECK and FOREIGN KEY in PostgreSQL can be DEFERRABLE and checked at transaction end, which helps with complex loads. At the same time, cascades (ON DELETE CASCADE, ON UPDATE) and RESTRICT/NO ACTION rules should be reviewed separately, otherwise you may get unexpected deletions or blocked business operations.

Also review transactions and locking. PostgreSQL uses MVCC and blocking expectations may differ from SQL Server. What previously passed without delay can hit long transactions and wait queues.

Before production run a short checklist:

• find places comparing to '' and places that allow NULL in logic
• list objects that use quoted identifiers or mixed case
• verify cascade rules and FK behavior on delete/update
• test typical conflicting operations (two threads updating the same row)
• evaluate bulk UPDATE/DELETE on large tables (time, locks, table "bloat")

Most issues show up not on small tests but on live data and concurrent queries.

T-SQL in queries: what to rewrite first

Differences appear fastest in application queries. Start with those run most often: lists, search, reports, write operations.

The most common change is row limiting. In SQL Server this is TOP (N) or OFFSET ... FETCH. PostgreSQL uses LIMIT for limiting and OFFSET for skipping. And you almost always need ORDER BY: without it the result can "jump".

Typical replacements that give the biggest immediate effect:

• SELECT TOP (10) ... ORDER BY ... -> SELECT ... ORDER BY ... LIMIT 10
• OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY -> OFFSET 20 LIMIT 10
• String concatenation: a + b -> a || b (and handle NULLs)
• ISNULL(x, y) -> COALESCE(x, y) (result type can differ)
• IIF(cond, a, b) -> CASE WHEN cond THEN a ELSE b END

Upserts are trickier. SQL Server often used MERGE. In PostgreSQL you usually use INSERT ... ON CONFLICT (...) DO UPDATE. Ensure the conflict target is a unique index or constraint.

Date and string functions almost always need manual verification. Examples: GETDATE() -> now(), DATEADD(day, 7, dt) -> dt + interval '7 days', formatting via to_char(...). Even when an analogue exists, formatting and rounding may differ.

Another risk zone is TRY_CONVERT and soft conversions. In PostgreSQL a failed CAST usually raises an error, so it’s safer to write guarded logic (e.g. validate with a regex) or normalize data before migration.

Window functions are generally similar: ROW_NUMBER() OVER (...), SUM(...) OVER (...) work as expected. But if you filtered on a window function in the same query in SQL Server, in PostgreSQL you often need a subquery: compute the window first, then filter.

T-SQL in database code: procedures, triggers, dynamic SQL

Hardest to migrate is not the tables but in-database code: procedures, triggers and functions. SQL Server often uses an imperative T-SQL style with many side effects. PostgreSQL solves similar tasks with PL/pgSQL functions and procedures, but syntax and idioms differ.

When rewriting, start with logic that affects money and critical data: calculations, deductions, period closings, validation rules. Practical tactic: first port the "pure" queries (SELECT/INSERT/UPDATE), then the wrapping logic (error handling, transactions, branching).

Triggers and temporary structures

PostgreSQL triggers can be BEFORE/AFTER and FOR EACH ROW/STATEMENT, which affects load. If SQL Server used a single catch-all trigger, it may be easier in PostgreSQL to split logic across multiple triggers and document the design. Check trigger firing order in advance.

Temporary tables and table variables need reconsideration. Instead of table variables, use temporary tables (TEMP) or CTEs. Sometimes persistent tables with explicit cleanup inside a transaction are suitable if data must survive multiple steps.

Dynamic SQL, security and privileges

Dynamic SQL is migrated via EXECUTE, but the bigger concern is parameters and security. String concatenation of user input is as dangerous in PostgreSQL, so use parameterization and safe identifier building.

Before go-live check:

• where EXEC/sp_executesql is used and what parameters flow in
• which procedures require owner rights and which must run with caller rights
• role mapping: logins, schemas, GRANTs, access to functions and sequences
• places where code depends on context (CURRENT_USER, object lookups by schema)
• execution plans for the most frequent dynamic queries (they may destabilize)

If strict access controls are required, define the target role model and test it in a staging environment before data transfer.

Indexes and performance: differences that affect speed

SQL Server often relies on clustered indexes: data on disk is physically ordered by key, influencing reads. PostgreSQL’s default index is btree, but there is no persistent clustering by default: rows are stored in insertion order and indexes hold references. CLUSTER exists but is a one-off operation and order drifts over time.

Covering indexes differ too. SQL Server commonly uses INCLUDE. PostgreSQL also supports INCLUDE, but often the important part is to avoid pulling extra columns into SELECTs and avoid turning a filter into a computed expression.

PostgreSQL’s strengths are specialized indexes. GIN is great for arrays, JSONB and full-text search. GiST suits geodata and similarity searches. BRIN helps on very large, roughly ordered tables: the index is tiny and can have a noticeable effect.

Why does a query become slower after migration? Often it’s the plan: different statistics, different optimizer, and it picks the "wrong" path. For example, a report might switch from index lookups to sequential scans if statistics are stale or selectivity is misestimated.

What to monitor after migration:

• ANALYZE after bulk loads and significant data changes.
• VACUUM (and autovacuum tuning) to avoid bloat.
• Indexes tailored to real filters and JOINs, not created "just in case."
• Execution plans: verify that expected indexes are used.
• Bulk updates: watch table and index growth.

Schema migration plan: step-by-step

DDL is easier to migrate if rules are agreed in advance. Common issues are naming chaos (dbo, different owners, case) and hidden dependencies: foreign keys, defaults, functions.

A reliable order of operations

First define the target structure: which schemas will be used in PostgreSQL (e.g. instead of dbo — app), table names, owners and required privileges. This saves hours of fixes after the first load.

Then proceed in steps:

• Migrate table DDL and map data types (e.g. NVARCHAR -> TEXT/VARCHAR, DATETIME -> TIMESTAMP, BIT -> BOOLEAN). At this stage it’s better not to enable foreign keys.
• Add primary keys and unique constraints. Then add foreign keys, starting from parent tables so you don’t hit creation order issues.
• Configure key generation: IDENTITY in SQL Server usually becomes identity columns or sequences. Set start values immediately to avoid collisions for new rows.
• Migrate CHECK constraints and DEFAULTs, but rework expressions: what worked in T-SQL may need rewriting.
• Create indexes after the base schema is up and set basic settings for initial tests.

Small practical example

If many tables lived in dbo and reference each other, don’t try to create all foreign keys at once. First create tables and PKs, then run FK scripts separately. It’s easier to identify incompatibilities and avoid drowning in hundreds of errors at once.

Data migration plan: load, verification and rollback

Decide in advance which mode suits you: full cutover with a downtime window, incremental migration (dual writes or replication), or hybrid where most data is loaded beforehand and final delta is applied in a short window. In practice a short final downtime often wins: it’s easier to manage and rollback.

Load order is more important than raw speed. If you start with facts, you’ll quickly hit foreign keys and dirty data. A practical sequence:

• reference data (organizations, users, statuses)
• core entities (orders, invoices, patient records)
• join tables (many-to-many)
• historical and archive tables
• service queues and temporary data (if needed)

Verify at each step. Minimum checks: compare row counts, sums on key numeric fields and checksums (e.g. row-hash) for large tables. Add spot checks: 50–100 live documents users open frequently.

For large tables plan batched and parallel loads with progress logging and idempotency. Example: load a transactions table of 200M rows by date ranges, recording which ranges finished successfully.

Rollback must be real, not just theoretical. Keep SQL Server read/write until the acceptance point, store migration logs, and define rollback criteria in advance (integrity errors, critical performance drop, inability to close day).

Example scenario: migrating a typical application DB

Imagine a simple ticketing system: employees create tickets, managers view statuses, accounting and IT export weekly reports. The DB is small but tied to daily reports and lists.

Key tables can be reduced to two: tickets with id, created date, status, department and assignee; and ticket_events storing changes: who changed status and when. This is enough to surface typical problems.

Usually the most critical pieces are not complex queries but the most frequent ones:

• list tickets by status sorted by date (for operators)
• search by number or part of the subject (call center)
• filters by date and department (managers)
• aggregates for reports: how many closed per day/week, average handling time

Typical incompatibilities surface quickly. Dates: SQL Server often used GETDATE() and implicit conversions; in PostgreSQL use CURRENT_TIMESTAMP and explicit timestamp/date types. GUIDs: uniqueidentifier maps to uuid, but check input format and generation. Pagination: TOP and OFFSET/FETCH become LIMIT/OFFSET. UPSERT: use INSERT ... ON CONFLICT instead of MERGE.

-- Пагинация
SELECT ... ORDER BY created_at DESC LIMIT 50 OFFSET 100;

-- UPSERT
INSERT INTO tickets(id, status, updated_at)
VALUES ($1, $2, CURRENT_TIMESTAMP)
ON CONFLICT (id) DO UPDATE
SET status = EXCLUDED.status, updated_at = EXCLUDED.updated_at;

To avoid doing migration "by eye", record a baseline before transfer: 5–10 most frequent queries, their average time and 95th percentile, and expected row counts. After migration compare correctness (do sums, counts, period queries match) and speed (response times for the same filters and data volume). If any metric drifts, it’s easier to find the cause when you have concrete numbers and example queries.

Minimal load tests before launch

Before go-live don’t try to test everything; confirm the critical parts: the system can handle real load without errors and without surprise latency. A minimal test set can be prepared in 1–2 days using real queries and parameters.

Three tests that give the most signal

Usually three runs catch most problems:

• Concurrent reads: take the 10 most frequent queries from production logs (or APM), use typical parameters and run them in parallel (e.g. 10–50 concurrent users). Watch not only average time but the slow tail.
• Writes and bulk changes: run inserts, updates and upsert patterns used by the app. Include concurrency and measure locks: a frequent post-migration issue is wait times due to transactions and isolation levels.
• Mixed load plus reports: run background reports and aggregates for a typical date range (e.g. last 30 days) while simulating normal users. This surfaces poor indexes and bad plans fastest.

For honest tests run them on a data copy close in size to production and warm caches with a short run before measurements.

Acceptance criteria

Agree in advance what “pass” means:

• Response time: p95 and p99 for key queries are within agreed thresholds.
• Errors: zero application and driver errors (timeouts, deadlocks, serialization failures) at target concurrency.
• Locks: lock wait times don’t increase over the test duration.
• Disk and WAL growth: predictable growth without spikes or overflow.
• Stability: consistent results across 2–3 repeated runs.

Example: if a "Monthly Sales" report opened in SQL Server in 3–5 seconds, allow 4–6 seconds in PostgreSQL but p99 must not jump to minutes under concurrent writes. Such a skew usually indicates a missing index, rewritten query or a transactional approach that needs rework.

Common mistakes and migration traps

The worst part is small differences that silently change system behavior. Everything may seem to work until reports diverge, latency spikes or permissions break.

One common trap is sorting and string comparison. SQL Server relies on column or database collation, while PostgreSQL uses locale/ICU and per-field collation. A name report may sort "Ё" differently, or string search may become case-sensitive when it wasn’t.

Another trap is migrating indexes "as they were." PostgreSQL’s optimizer is different and extra indexes cost writes and vacuum cycles. After migration inserts and updates often slow and database size grows because the familiar set of indexes was unnecessary.

Also check trigger logic: a SQL Server trigger doing "a bit of magic" on insert can become blocking and long transactions in PostgreSQL, especially during batch loads.

Privileges and roles break often. Services may use a single login that suddenly lacks rights to a schema, sequences or functions. The error looks like "permission denied" but usually stems from an incomplete role map.

Quick checks before first production run:

• compare sorting on 10–20 key reports and reference lists
• rebuild indexes for real queries rather than by habit
• measure typical write times (INSERT/UPDATE) on test data
• verify service accounts’ rights on schemas, sequences and functions
• enable basic monitoring: slow queries, locks, table growth

Checklist and next steps

To make migration predictable, document what must be ready before the first test load and before production cutover.

For schema and data verify:

• Inventory: which DBs, schemas, tables, jobs, procedures, triggers, reports and apps depend on the DB, who owns them and what the critical path looks like.
• DDL and types: type mappings, NULL/DEFAULT rules, identity/sequence, collations and case sensitivity.
• T-SQL replacements: list of most frequent queries and pain points (TOP/OFFSET, MERGE, date handling, TRY...CATCH) and a rewrite plan.
• Indexes and constraints: which indexes are actually used, where composite/partial indexes are needed, which UNIQUE/FKs are essential for data quality.
• Data checks: reconciliations, checksums, spot checks, cleaning rules and a plan for handling dirty data.

For deployment and operations:

• Correctness: key reports and operations return the same results on control data.
• Load: 3–5 typical scenarios (search, write, batch processing) run with realistic volumes and measured times.
• Recovery: test backups and restores on a separate bench.
• Rollback: stop criteria, decision owner and procedure to return to the old DB.
• Launch: pilot with limited traffic, then broaden and cut over fully.

Also assign an infrastructure owner: servers, storage, backups, monitoring and 24/7 support. If you need help with target architecture and resource sizing, it’s convenient to discuss with the GSE.kz integration team while selecting servers (for example from the S200 Series).