Why SQL Server Applications Break on PostgreSQL and How Compatibility Layers Fix It

When an enterprise decides to migrate from SQL Server to PostgreSQL, the first conversation is almost always about data movement. How many terabytes need to transfer? Which replication tool should we use? What is the cutover window? These are necessary questions, but they obscure a harder truth: moving the data is the straightforward part. Keeping the application working is where migrations stall.

SQL Server applications were built on assumptions that PostgreSQL does not share. The T-SQL dialect handles NULL comparisons, temporary table scoping, date arithmetic, and cursor behavior differently than PL/pgSQL. The Tabular Data Stream (TDS) wire protocol has no native equivalent in the PostgreSQL ecosystem. Application drivers, connection strings and ORM configurations frequently contain SQL Server-specific parameters that either fail silently or produce different behavior when pointed at a PostgreSQL endpoint.

The result is a pattern that migration teams recognize immediately: the data arrives intact, the schema looks correct after automated conversion, and then the application breaks in production because a stored procedure that correctly handled NULL comparisons for years now filters rows incorrectly, or a temporary table with a specific session scope no longer exists when the next batch statement executes. These are not data movement failures. They are application compatibility failures, and they account for the majority of migration delays and rollbacks.

Where the Compatibility Gap Actually Lives

The most dangerous migration failures are not the ones that prevent cutover. They are the ones that appear successful through every validation gate and then fail at runtime under real application load. Stored procedure translation is the primary accumulation point for these failures because automated schema conversion tools are designed to handle structural differences, not semantic ones.

Consider temporary table semantics. SQL Server supports global temporary tables (prefixed with ##) that persist across sessions and local temporary tables (prefixed with #) that are visible within a session boundary. PostgreSQL's temporary tables follow different visibility and cleanup rules. Automated conversion tools frequently produce code that compiles without errors but returns incorrect results under concurrent access patterns because the scope assumptions do not carry over.

NULL comparison behavior presents a subtler class of problems. Both T-SQL and PL/pgSQL implement three-valued logic for NULL comparisons, but the interaction with session-level settings such as SET ANSI_NULLS, SET CONCAT_NULL_YIELDS_NULL and SET ARITHABORT creates edge cases that are extraordinarily difficult to catch in automated testing. A WHERE clause that filtered correctly across millions of rows for years in SQL Server may begin producing different result sets after conversion because the implicit NULL handling changed.

Proprietary date functions are another common source of semantic drift. T-SQL functions like DATEADD, DATEDIFF, EOMONTH and FORMAT have no direct equivalents in PostgreSQL. The translated expressions may produce the correct result for the common case and the wrong result for edge cases involving daylight saving time transitions, leap year boundaries or date arithmetic across month boundaries.

Cursors, Multiple Active Result Sets (MARS) and SQL Server-specific row-counting behavior add another layer of complexity. Applications that depend on @@ROWCOUNT, OUTPUT clauses with side effects, or specific cursor positioning behavior often require nontrivial restructuring that automated tools cannot produce.

In a typical enterprise SQL Server estate with several hundred stored procedures, automated conversion tools flag 10 to 15 percent of procedures as requiring manual review. Of those flagged procedures, a smaller subset contains the kind of semantic mismatch that passes schema validation and unit testing but produces incorrect behavior under production concurrency and data distribution.

The Standard Response Has a Cost Problem

The conventional approach to these compatibility gaps is a full application rewrite. Rewrite every stored procedure in PL/pgSQL. Update every ORM mapping. Change every connection string. Modify every inline query. Run a complete application regression suite. Schedule a freeze on new feature development during the migration window.

This approach works, but its cost and risk are frequently underestimated. Every line of rewritten code introduces the potential for new bugs. The testing burden grows linearly with the number of stored procedures and exponentially with the number of code paths that touch the database. Organizations that attempt this path often find themselves in a year-long migration cycle during which the application cannot ship new features, and the business begins to question whether the migration was worth undertaking at all.

The cost pressure is real. Application rewrite and testing can account for 60 to 70 percent of total migration expense in SQL Server to PostgreSQL projects, far exceeding the cost of data movement and infrastructure provisioning. For organizations running packaged or third-party applications, a full rewrite may not even be an option because the source code for the database access layer is not available for modification.

The Compatibility Layer Alternative

An alternative architecture has emerged over the past several years that addresses this problem at a different level of abstraction. Instead of converting the application to speak PostgreSQL, a compatibility layer sits between the application and the database, translating SQL Server's TDS protocol and T-SQL dialect into PostgreSQL equivalents at runtime.

The application continues to use its native SQL Server driver, with no change to the connection string. Stored procedures stay in T-SQL. From the application's perspective, nothing about the database has changed — the engine sitting on the other end of the connection being PostgreSQL is entirely invisible to it.

The most mature implementation of this pattern is Babelfish, originally developed at Amazon and later contributed to the open source community as part of the Babelfish for PostgreSQL project. Babelfish adds a TDS listener to a PostgreSQL engine, enabling SQL Server clients to connect using the native SQL Server wire protocol and execute T-SQL statements against a PostgreSQL database without any client-side changes.

This is not a pass-through proxy or a query rewriting layer. Babelfish implements a substantial portion of the T-SQL language surface directly within the PostgreSQL engine, including stored procedure execution, scalar functions, temporary tables, transactions, cursors, and error handling. It also implements enough of the TDS protocol to support the most common SQL Server driver behaviors, including connection pooling, transaction isolation level negotiation and prepared statement handling.

How the Migration Equation Changes

The compatibility-layer approach reframes the migration from an all-or-nothing rewrite to an incremental compatibility assessment. The question shifts from "how do we rewrite every stored procedure?" to "which T-SQL constructs fall outside the supported dialect, and how do we handle those specific gaps?"

This distinction has practical consequences. In most SQL Server estates, 80 to 90 percent of T-SQL constructs used by OLTP applications have direct or near-direct equivalents in the compatibility layer. The remaining 10 to 20 percent — typically involving unsupported features such as SQLCLR, Service Broker, specific system stored procedures, or edge-case T-SQL syntax — become candidates for targeted mitigation rather than wholesale refactoring.

The mitigation strategies for those gaps are manageably scoped:

• Rewrite the specific unsupported construct in PL/pgSQL while keeping the rest of the application on the compatibility layer
• Replace the feature with an equivalent PostgreSQL extension or middleware component
• Accept a minor functional difference and document the change for downstream consumers

None of these strategies require the full application regression test that a complete rewrite would demand. The testing scope narrows to the specific code paths that interact with the unsupported constructs, which is typically a fraction of the total application surface.

What makes this approach work is the gap assessment. Running the application's workload against the compatibility layer in a pre-production environment and measuring which constructs succeed, which fail and which produce different results is the only reliable way to size the remaining work. Tooling such as Babelfish Compass helps automate this assessment by analyzing the SQL Server schema and workload to identify compatibility gaps before the migration begins.

When Compatibility Layers Fit

Compatibility layers are not a universal answer, but they are well suited to specific migration scenarios.

High stored procedure density. Applications with hundreds or thousands of T-SQL stored procedures benefit most because the rewrite cost is concentrated in the database layer and the compatibility layer protects the largest investment. An 80 percent reduction in required application changes translates directly into a shorter migration timeline with less risk.

Packaged and third-party applications. When the application is a commercial off-the-shelf product or a legacy system with no active development team, rewriting the database access layer is often infeasible. The compatibility layer becomes the only viable migration path to an open-source database.

Incremental migration strategies. Organizations that need to continue shipping application features while the database migration proceeds benefit from the decoupling that a compatibility layer provides. The application team ships features. The migration team validates compatibility gaps. The two workstreams run in parallel without blocking each other.

Limited proprietary feature usage. Estates that rely heavily on SQL Server-specific features such as SQLCLR, Full-Text Search or Service Broker will still need to address those patterns directly. The compatibility layer handles the common T-SQL surface but does not eliminate all migration work. A thorough gap assessment reveals which features fall outside the supported scope, and teams can plan those remediations as discrete, bounded work items.

The Practical Path Forward

For teams evaluating a SQL Server to PostgreSQL migration, the central insight is that the problem is not primarily about data movement — it is about application compatibility. Once that distinction is recognized, the architecture choices become clearer.

Each component of the migration stack addresses a distinct failure mode: data movement handles the transfer, schema conversion handles structural translation and the compatibility layer closes the dialect gap between what the application speaks and what PostgreSQL understands. None of them require the others to be perfect before the next phase can begin.

A compatibility-layer architecture does not eliminate all migration work. Rather than requiring rewrites of every stored procedure, driver updates and full regression testing before cutover, it transforms the project into a manageable compatibility assessment with targeted mitigations for the gaps that fall outside the supported dialect. For most enterprise SQL Server estates, that is the difference between a migration that completes and one that stalls.