Practical Case Study: Migrate SQL Server to PostgreSQL

This article explores migration of 200GB SQL Server database used in e-commerce application to PostgreSQL for cost savings, open-source flexibility, and better performance on analytical queries.

Database Overview:

Schema Conversion

First step of migration is schema conversion (tables, constraints, indexes, and stored procedures) into PostgreSQL format. Key challenge of schema conversion:

• MS SQL identity columns are converted into PostgreSQL SERIAL or BIGSERIAL
• DATETIME is converted into TIMESTAMP, BIT is converted into BOOLEAN, UNIQUEIDENTIFIER is converted into UUID

Example of table conversion from SQL Server to PostgreSQL

In MS SQL:

CREATE TABLE Orders (
ID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATETIME DEFAULT GETDATE(),
IsPaid BIT DEFAULT 0
);

In PostgreSQL:

CREATE TABLE Orders (
ID SERIAL PRIMARY KEY,
OrderDate TIMESTAMP DEFAULT NOW(),
IsPaid BOOLEAN DEFAULT FALSE
);

Data Migration

The goal of this step is to migrate all records from SQL Server to PostgreSQL without data loss. MSSQL-to-PostgreSQL converter has been used for both schema and data migration and it gave the benchmark as follows (stats for main tables only):

Stored Procedures Migration

MS SQL T-SQL procedures and functions have to be converted to PL/pgSQL in PostgreSQL. Key bottlenecks of this stage:

• MERGE Not Supported in PostgreSQL, must be rewritten as INSERT ON CONFLICT
• Differences in Exception Handling – the code of stored procedures must be translated accordingly
• Procedures returning a rowset must be rewritten to return the appropriate refcursor
• MS SQL uses table variables, while PostgreSQL does not support this feature. All table variables must be replaced by temporary tables.
• All MS SQL specific built-in functions and operators must be replaced by PostgreSQL equivalents

Triggers Migration

Triggers are database-level mechanisms that execute automatically when specific events occur, such as INSERT, UPDATE, or DELETE operations. While both MS SQL Server and PostgreSQL support triggers, there are differences in syntax, behavior, and implementation that must be accounted for during migration.

Key Differences:

• In MS SQL triggers are executed once per statement. In PostgreSQL triggers are execute once per row (default) or per statement (configurable).
• MS SQL uses INSERTED and DELETED tables to access old/new values. PostgreSQL uses OLD and NEW variables for the same purpose.
• In SQL Server trigger’s code goes inside CREATE TRIGGER statement. PostgreSQL trigger’s code is composed as separate procedure that is called from CREATE TRIGGER statement.

Example of Migration

In MS SQL:

CREATE TRIGGER trg_AfterInsertOrders
ON Orders
AFTER INSERT
AS
BEGIN
UPDATE Customers
SET TotalOrders = TotalOrders + 1
FROM Customers C
INNER JOIN INSERTED I ON C.ID = I.CustomerID;
END;

PostgreSQL Equivalent:

CREATE OR REPLACE FUNCTION trg_AfterInsertOrders()
RETURNS TRIGGER AS $$
BEGIN
UPDATE Customers
SET TotalOrders = TotalOrders + 1
WHERE ID = NEW.CustomerID;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_AfterInsertOrders
AFTER INSERT ON Orders
FOR EACH ROW EXECUTE FUNCTION trg_AfterInsertOrders();

Performance Benchmarking

To ensure queries run efficiently in PostgreSQL, we compared query speeds before and after migration.

Bottlenecks Encountered:

• Missing Indexes Slowed Queries. PostgreSQL does not automatically optimize indexes like SQL Server. Workaround is to manually create indexes for common queries.
• Autovacuum is Slowing Bulk Inserts. PostgreSQL autovacuum process was running too frequently, impacting performance. Workaround is to tune autovacuum settings.

Conclusion

This case study of successful migration from MS SQL to PostgreSQL demonstrates that with the proper tools and performance tests Postgres can be a cost-effective and high-performance alternative to SQL Server.

Key Takeaways:

• PostgreSQL reduced query execution time by ~30-40%
• Required manual fixes for conversion of stored procedures and indexing
• PostgreSQL tuning (autovacuum, indexing) was critical for performance boost.