SQL Server to PostgreSQL Migration: Complete Guide 2025

1. T-SQL to PL/pgSQL Conversion

SQL Server's T-SQL has proprietary syntax that doesn't exist in PostgreSQL. Key differences:

-- Identity columns
CREATE TABLE users (
  id INT IDENTITY(1,1),
  name NVARCHAR(100)
);

-- Date functions
SELECT GETDATE(), DATEADD(day, 7, OrderDate)

-- String concat
SELECT 'Hello' + ' ' + 'World'

-- TOP keyword
SELECT TOP 10 * FROM orders

-- Variables
DECLARE @count INT
SET @count = (SELECT COUNT(*) FROM users)

-- Serial/Sequences
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

-- Date functions
SELECT NOW(), OrderDate + INTERVAL '7 days'

-- String concat
SELECT 'Hello' || ' ' || 'World'

-- LIMIT keyword
SELECT * FROM orders LIMIT 10

-- Variables
DO $$
DECLARE count_val INT;
BEGIN
  SELECT COUNT(*) INTO count_val FROM users;
END $$;

2. Data Type Mapping

3. SSIS, SSRS, and SQL Agent Alternatives

• SSIS →
  Apache Airflow, Pentaho, Talend

  Modern orchestration with better monitoring and scheduling

• SSRS →
  Grafana, Metabase, Apache Superset

  Modern BI tools with better visualization and dashboards

• SQL Agent →
  pg_cron extension, Airflow, cron + scripts

  pg_cron provides similar scheduling within PostgreSQL

How long does SQL Server to PostgreSQL migration take?

Migration timeline depends on database size and complexity. A small database (10-50GB, 100 tables) typically takes 2-4 weeks including testing. Medium databases (100-500GB, 500 tables) require 1-3 months. Large enterprise databases (1TB+, 1000+ tables with complex stored procedures) can take 3-6 months for careful phased migration.

What about SQL Server-specific features like SSIS, SSRS, and SQL Agent?

PostgreSQL alternatives exist: SSIS can be replaced with Apache Airflow, Luigi, or Pentaho Data Integration. SSRS can be replaced with Grafana, Metabase, or Apache Superset. SQL Agent can be replaced with pg_cron extension or external schedulers like Airflow. Migration requires redesigning ETL workflows and reports, but modern alternatives often provide better features.

How much cost savings can I expect from migrating to PostgreSQL?

Typical cost savings range from 50-80% depending on your SQL Server edition. SQL Server Enterprise ($14,000/core) vs PostgreSQL (free) provides maximum savings. Including support contracts and Azure/AWS managed PostgreSQL, organizations typically save 60-70% on database licensing alone. Total TCO reduction including reduced maintenance overhead averages 50-65%.

Can I keep both SQL Server and PostgreSQL running during migration?

Yes, this is the recommended approach for large migrations. Use dual-write pattern or CDC (Change Data Capture) to keep both databases synchronized during migration. This allows you to validate PostgreSQL performance, test application changes, and have an immediate rollback path if issues arise. Most enterprises run parallel databases for 1-3 months before final cutover.

What are the biggest challenges in T-SQL to PL/pgSQL conversion?

Main challenges include: (1) IDENTITY columns → SERIAL/SEQUENCES, (2) Date/time functions differences (GETDATE() → NOW(), DATEADD → interval arithmetic), (3) String concatenation (+ operator → ||), (4) TOP keyword → LIMIT, (5) ISNULL/COALESCE behavior differences, (6) Transaction isolation levels, (7) Cursor syntax differences. Most can be addressed with automated conversion tools plus manual refinement.

Is PostgreSQL performance comparable to SQL Server?

Yes, PostgreSQL often outperforms SQL Server for read-heavy workloads, JSON operations, and OLAP queries. SQL Server can be faster for write-heavy OLTP with proper indexing. Real-world benchmarks show PostgreSQL 15+ handles 15,000-20,000 TPS on commodity hardware. The key is proper tuning: indexes, VACUUM strategy, connection pooling (PgBouncer), and query optimization. Most organizations see similar or better performance after migration with significantly lower costs.