Seamlessly Migrating Stored Procedures from MSSQL to PostgreSQL

In today’s fast-evolving tech landscape, businesses are continually looking to improve scalability, reduce costs, and enhance system performance. A key approach to achieving this is migrating databases and procedures to more flexible and cost-effective platforms. Recently, we undertook a major project to migrate stored procedures from MSSQL to PostgreSQL, delivering a robust solution that balanced performance, scalability, and data integrity. In this article, we’ll walk through the strategic steps taken to make this migration a success.

Why Migrate from MSSQL to PostgreSQL?

Organizations often rely on Microsoft SQL Server (MSSQL) for robust database management, but PostgreSQL offers unique advantages:

• Cost Efficiency: PostgreSQL is open-source, reducing licensing costs significantly.

• Scalability: Known for handling high volumes of transactions and complex queries, PostgreSQL is a powerful choice as data grows.

• Flexibility: PostgreSQL supports custom functions, extensions, and is more adaptable for distributed architectures.

With these advantages in mind, we embarked on a meticulous migration journey, from initial analysis to performance optimization.

Step 1: Identifying Key Tables in MSSQL Stored Procedures

We began by analyzing existing stored procedures in MSSQL to identify essential tables and dependencies, allowing us to create a focused migration plan.

Example:

For the procedure GetDepartmentTasks, we determined that the table [dbo].[DepartmentTasks] was fundamental. Documenting these dependencies reduced complexity, allowing us to prioritize only the necessary tables for migration.

Tasks in this Step:

• Analyzed stored procedures to pinpoint crucial tables.

• Created a list of dependencies for focused migration.

Step 2: Migrating and Validating Tables in PostgreSQL

Next, we migrated these tables from MSSQL to PostgreSQL, focusing on data structure consistency and integrity.

• Data Structure Comparison: Adjusted data types and indexing to account for differences between MSSQL and PostgreSQL schemas.

• Data Validation: We verified that migrated tables retained data integrity through row counts, checksums, and sample comparisons.

Example:

For the table [dbo].[DepartmentTasks], we compared record counts in both databases, ensuring each step preserved data integrity.

Step 3: Translating MSSQL Stored Procedure Logic to PostgreSQL

MSSQL and PostgreSQL stored procedures differ in syntax and functionality, so we adapted MSSQL-specific constructs to PostgreSQL-friendly functions.

Example Conversion:

The procedure GetDepartmentTasks in MSSQL might use a table hint like WITH (NOLOCK), which PostgreSQL doesn’t support. We translated this to a PostgreSQL-compatible approach while maintaining functional parity.

Original MSSQL Procedure:

CREATE PROCEDURE [dbo].[GetDepartmentTasks]
    @departmentId int,
    @taskIds [dbo].[TaskIdList] READONLY
AS
BEGIN
    SELECT [TaskId], [DepartmentId], [TaskType], [StartDate], [EndDate]
    FROM [dbo].[DepartmentTasks] WITH (NOLOCK)
    WHERE [DepartmentId] = @departmentId
    AND [TaskType] IN (SELECT [Id] FROM @taskIds);
END;

Converted PostgreSQL Function:

CREATE OR REPLACE FUNCTION dbo.get_department_tasks(department_id integer, task_ids integer[])
    RETURNS TABLE("TaskId" bigint, "DepartmentId" integer, "TaskType" integer, "StartDate" timestamp, "EndDate" timestamp)
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN QUERY
    SELECT "TaskId", "DepartmentId", "TaskType", "StartDate", "EndDate"
    FROM dbo.department_tasks
    WHERE "DepartmentId" = department_id
    AND "TaskType" = ANY(task_ids);
END;
$BODY$;

Step 4: Optimizing PostgreSQL with Indexing

To maintain query performance, we added indexes in PostgreSQL on high-usage columns.

Index Example:

For the department_tasks table, we indexed DepartmentId and TaskType, critical columns for efficient querying. Using EXPLAIN ANALYZE, we verified that the indexes improved performance without excessive storage costs.

Index Creation Example:

CREATE INDEX idx_department_tasks_departmentid_tasktype ON dbo.department_tasks("DepartmentId", "TaskType");

Step 5: Enabling Compatibility with Both MSSQL and PostgreSQL

To allow thorough testing, we created parallel MSSQL and PostgreSQL stored procedures. This enabled dual compatibility and allowed side-by-side testing without affecting live data.

Example Implementation:

We developed a new service layer function, FetchTasksV2, to call the PostgreSQL version while retaining the original MSSQL logic. This enabled comprehensive validation before full migration.

Step 6: Conducting Unit Tests for Functional Parity

We created unit tests to compare results from both MSSQL and PostgreSQL procedures, verifying that they produced identical results.

Testing Approach:

• Ran stored procedures with identical parameters in both environments.

• Automated comparison of results to flag any discrepancies.

• Used mock objects to isolate tests from external dependencies.

Step 7: Benchmarking for Performance Insights

With functional parity confirmed, we benchmarked performance between MSSQL and PostgreSQL to identify any potential delays or bottlenecks. Execution times and resource utilization were logged for identical queries on both databases.

Benchmarking Example:

Executing GetDepartmentTasks, we compared average response times, finding that optimized PostgreSQL functions matched or exceeded MSSQL performance.

Step 8: Fine-Tuning PostgreSQL Stored Procedures

We used insights from benchmarking to further optimize PostgreSQL stored procedures. By analyzing query paths with tools like EXPLAIN ANALYZE, we identified and refined bottlenecks, enhancing efficiency.

Step 9: Deploying the New PostgreSQL Version in Production

After extensive testing and optimization, we deployed the PostgreSQL stored procedures in production, updating the service layer to exclusively call PostgreSQL. This provided a seamless transition without affecting end users.

Key Takeaways from the Migration

Migrating stored procedures from MSSQL to PostgreSQL requires careful planning and execution but offers significant long-term benefits:

• Cost Savings: PostgreSQL’s open-source model reduces licensing expenses.

• Enhanced Scalability: PostgreSQL supports complex functions and high transaction volumes, perfect for expanding needs.

• Performance Optimization: With targeted optimization, PostgreSQL functions can match or exceed MSSQL performance.

Through a methodical approach, we provided a high-performance, flexible PostgreSQL solution that supports the client’s growing needs.

Thinking About Database Migration?

Migrating from MSSQL to PostgreSQL could be a game-changer. If you’re ready to scale, reduce costs, and improve performance, let’s discuss how we can create a tailored migration strategy to meet your goals.