PostgreSQL for SQL Server DBAs

PostgreSQL vs SQL Server: A Transition Guide for SQL Server DBAs (And PostgreSQL DBAs)

Migrating from Microsoft SQL Server to PostgreSQL requires understanding key differences in how these systems operate. This guide compares PostgreSQL and SQL Server across architecture, administration, SQL syntax, and tooling with practical insights for SQL Server DBAs making the transition. Both are powerful relational databases, but PostgreSQL’s open-source heritage and extensibility contrast with SQL Server’s Microsoft ecosystem and enterprise tooling
IMP NOTE: PostgreSQL latest versions have improvements, like Vacuum, etc. For more recent knowledge, please read the latest documentation for SQL Server and PostgreSQL. Let me know if missed something, and I will update the document.
We’ll explore their Architecture & Performance, Administration & Management, SQL & Procedural Differences, Migration Challenges, and Tooling & Ecosystem. A summary table at the end highlights major differences and similarities for quick reference.

Architecture & Performance

Concurrency and Transaction Handling
PostgreSQL and SQL Server use different models for managing concurrent access to data:
PostgreSQL – MVCC Concurrency: PostgreSQL uses Multi-Version Concurrency Control (MVCC) to allow readers and writers to proceed without blocking each other. Each transaction sees a snapshot of data, so reads don’t lock writes and vice versa. This design minimizes lock contention and reduces deadlocks. Only conflicts like two transactions modifying the same row will block at commit, which keeps throughput high for many simultaneous users.
SQL Server – Locking (with Optional Snapshot): SQL Server by default uses a locking mechanism for concurrency, obtaining locks on rows or pages to maintain ACID consistency. Readers might block writers (and vice versa) under the default READ COMMITTED isolation. SQL Server does have an Optimistic or Snapshot Isolation mode (Read Committed Snapshot Isolation) that, when enabled, uses row versioning in tempdb to achieve MVCC-like behavior. However, this is not on by default; without it, contention can lead to more frequent deadlocks and blocking compared to PostgreSQL’s approach. In summary, PostgreSQL’s MVCC provides better concurrency with fewer explicit locks, whereas SQL Server relies on locks unless you explicitly configure snapshot isolation.

Indexing Strategies

Both databases use indexes for performance, but supported index types differ:

• PostgreSQL Indexes: PostgreSQL offers a variety of index types: B-tree (default), Hash, GiST, SP-GiST, GIN, and BRIN indexes. These specialized indexes can accelerate specific workloads – e.g. GIN for full-text search and JSONB, GiST for spatial/geometric data, BRIN for very large tables with naturally ordered data (like time-series). PostgreSQL also supports expression indexes (index on an expression or function) and partial indexes (indexing a filtered subset of a table) for extra flexibility. There is no concept of a clustered index that dictates the table’s storage order; instead, PostgreSQL tables are heap-organized, and you can optionally CLUSTER a table once on an index (this physically reorders data, but it’s a one-time operation and not maintained automatically).
• SQL Server Indexes: SQL Server supports clustered and nonclustered B-tree indexes. A clustered index determines the physical order of rows in the table (each table can have at
  most one), while nonclustered indexes are separate structures that point to the clustered index or heap. By default, SQL Server will create a clustered index on a table’s primary key (unless specified otherwise) and nonclustered indexes for unique constraints. In addition, SQL Server provides Columnstore indexes (columnar storage for analytics) and full-text indexes for text search. PostgreSQL currently lacks a built-in columnstore index (though extensions like Citus or TimescaleDB provide columnar storage), whereas in SQL Server, columnstore indexes are integrated and commonly used in data warehousing scenarios.

Index Tuning: Both systems use cost-based optimizers that decide when to use indexes. PostgreSQL’s planner can utilize multiple indexes via bitmap index scans and supports index-only scans (servicing a query entirely from an index if all needed columns are indexed). SQL Server’s optimizer is also very sophisticated and considers index statistics to decide query plans. A notable difference is that PostgreSQL requires manual index maintenance for certain scenarios (e.g. you might occasionally REINDEX or VACUUM indexes to manage bloat), while SQL Server indexes can become fragmented and may need REBUILD or REORGANIZE for optimal performance. Regular monitoring of index usage and maintenance is important in both.

Query Execution and Optimization

SQL Server and PostgreSQL both offer tools for understanding and tuning query execution, but their approaches differ:

• SQL Server Query Optimizer: SQL Server’s optimizer uses statistics on data distributions and can do advanced transformations. SQL Server provides the Query Store (which tracks historical query performance and can suggest or force plan changes) and tuning tools like the Database Engine Tuning Advisor (DTA). DBAs often use Dynamic Management Views (DMVs) (e.g. sys.dm_exec_query_stats, sys.dm_db_index_usage_stats) to find slow queries and index usage patterns. SQL Server also has execution plan caching – repeated execution of the same query (or stored procedure) will reuse a cached plan, which can be good for performance but sometimes leads to issues like parameter sniffing. The platform’s newer Intelligent Query Processing features can automatically adjust row estimates and optimize recurring workloads.
• PostgreSQL Query Planner: PostgreSQL also has a cost-based optimizer. DBAs use the EXPLAIN and EXPLAIN ANALYZE commands to inspect query execution plans and performance. PostgreSQL does not have an equivalent of Query Store built-in, but you can enable the pg_stat_statements extension to track long-running queries and their resource usage. Instead of a tuning advisor, performance optimization in PostgreSQL is a more manual process: you examine slow queries, create appropriate indexes, and adjust configuration. PostgreSQL relies on ANALYZE (auto-vacuum runs this periodically) to gather statistics; these include histograms of data distribution but are sometimes less granular than SQL Server’s stats. One difference is that PostgreSQL will plan each query each time (unless you use prepared statements or stored functions which cache plans). This avoids SQL Server’s parameter sniffing problem, but it means PostgreSQL doesn’t automatically cache plans across sessions unless you explicitly prepare the statement.

Query Tuning: In SQL Server, you might use hints or plan guides sparingly to force join orders or index usage. PostgreSQL also supports query hints indirectly via the enable_* settings (for example, turning off nested loops for a session) or the PG_HINT_PLAN extension, but these are less commonly used. Instead, you typically tune PostgreSQL by adjusting planner-related settings (like random_page_cost, work_mem for sort operations) or rewriting queries. Both databases benefit from proper indexing and query writing (for example, avoiding functions on columns in WHERE clauses can help indexes in both systems).

Memory and Performance Tuning

Performance tuning also involves configuration of memory and background processes:

• SQL Server Memory Management: SQL Server dynamically manages memory with a buffer pool that caches data pages. A DBA typically only sets max and min memory limits; the server will self-tune within those bounds. SQL Server also has features like Resource Governor (to allocate CPU/IO/Memory to workloads) and can use an Buffer Pool Extension to SSD for caching. Out of the box, SQL Server is often tuned for OLTP, and many settings are auto-configuring (statistics updates, tempdb configuration, etc. have sensible defaults).
• PostgreSQL Memory and Autovacuum: PostgreSQL relies on the DBA to configure memory parameters. Key settings include shared_buffers (the main data cache inside PostgreSQL), work_mem (memory for sorts and hash operations per query), and effective_cache_size (an estimate to help the planner gauge how much OS cache is available). These need to be adjusted based on available RAM and workload. PostgreSQL does not have a feature exactly like Resource Governor; all sessions compete for resources equally (though you can limit connections or use OS cgroups for resource control).
• Vacuum and Maintenance: Because PostgreSQL’s MVCC creates new row versions on writes, it requires a background process to clean up old row versions (dead tuples). The autovacuum daemon routinely vacuums tables to reclaim space and to update statistics. Autovacuum settings (frequency, thresholds) may need tuning in heavy-update workloads to prevent table bloat. SQL Server also handles row version cleanup in tempdb for snapshot isolation, but generally, deleted or updated rows are immediately removed (except for ghost records which a background thread cleans up quickly). SQL Server maintenance is more about index fragmentation and statistics updates (which it can handle automatically or via scheduled jobs). On PostgreSQL, vacuuming is a new concept for SQL Server DBAs – it’s crucial to monitor that autovacuum is keeping up, or run manual VACUUM/VACUUM FULL as needed to maintain performance.

In summary, both systems can be tuned for high performance, but SQL Server automates more of this out-of-the-box, whereas PostgreSQL gives you more manual control (and responsibility) to configure memory and maintenance tasks.

Administration & Management

Backup and Recovery

Backup/restore procedures differ fundamentally between SQL Server and PostgreSQL:

• SQL Server Backups: In SQL Server, backups are typically physical, consistent snapshots of the entire database. A BACKUP DATABASE command produces a binary .bak file containing the exact data pages, and optionally you take log backups for point-in-time recovery. SQL Server’s backup mechanism is robust – it can backup online, reading the data files and logging changes occurring during the backup, so that the restored backup reflects a single point in time. Restoring is an all-or-nothing operation: you get the entire database back to that point. One limitation is the inability to restore individual tables or objects from a native backup – you must restore the whole database (possibly to a separate instance) and then copy out what you need. SQL Server offers full, differential, and transaction log backups, allowing fine-grained RPO/RTO if managed properly. It also supports features like backup compression, encryption, and can do backups to URL (cloud storage) in newer versions.
• PostgreSQL Backups: PostgreSQL offers multiple approaches to backup, which can be confusing at first:
  • Logical Backups: Using the pg_dump utility (or pg_dumpall for all databases) creates a logical backup – essentially a SQL script (or compressed archive) with CREATE TABLE and COPY/INSERT commands to reconstruct the database. This is similar to generating scripts in SSMS. Logical backups are flexible: you can dump and restore individual databases or tables easily, and even change versions (useful for upgrades). They also exclude indexes in the data dump (only the CREATE INDEX statements are saved), which keeps backup files smaller and rebuilds indexes on restore for efficiency. However, a plain pg_dump is not consistent for point-in-time if the database is active; you typically dump from a single transaction or use the –snapshot option with replication slots for consistency. Also, you can’t apply WAL (transaction logs) to a pg_dump output – it’s a static snapshot of the data at the time of dump, so you cannot roll forward increments using logs.
  • Physical Backups (base backups): PostgreSQL also supports physical backups akin to SQL Server’s. A common approach is to use pg_basebackup or file system snapshots to copy the data files, usually in combination with WAL (Write-Ahead Log) archiving for continuous recovery. By archiving WAL segments and taking periodic base backups, you can restore the entire cluster and apply WAL to achieve point-in-time recovery (PITR), similar to SQL Server’s full + log backup strategy. Tools like Barman or WAL-G can help manage this process. Physical backups operate at the cluster level (all databases) rather than a single database, since PostgreSQL’s basic unit of backup is the entire instance’s data directory.

Restore: To restore a pg_dump output, you typically create an empty database and run pg_restore (for custom format dumps) or psql (for plain SQL dumps) to execute the SQL commands. This can be slow for large data sets but allows object-level restores easily. For physical backups, restore involves shutting down Postgres, copying in the base backup files, configuring recovery to apply WAL, and starting in recovery mode to roll forward logs. This is more complex to set up than SQL Server’s straightforward RESTORE commands.

Bottom line: SQL Server’s backup/restore is monolithic but straightforward for full database recovery, whereas PostgreSQL offers flexible but multi-part backup options. A SQL Server DBA should plan for new backup routines in PostgreSQL, perhaps using a combination of physical backups for PITR and logical dumps for selective restores. Also note that cloud versions (like AWS RDS for PostgreSQL) can integrate snapshot backups, simplifying some of this.

Monitoring and Performance Management

Monitoring a PostgreSQL server is different from using SQL Server’s tools:

• SQL Server Monitoring: DBAs often use SQL Server’s built-in Activity Monitor and DMVs to check current activity (e.g. sys.dm_exec_requests, sys.dm_tran_locks), as well as Profiler/Extended Events or third-party tools for capturing queries. SQL Server’s error logs and performance counters (Perfmon) also provide insight. Many SQL Server shops use specialized monitoring software that taps into DMVs to report on CPU, IO, and query performance. The SQL Server Agent is available to schedule routine tasks and alerts (like jobs for reindexing or sending alerts on long-running queries).
• PostgreSQL Monitoring: PostgreSQL exposes runtime metrics via system catalog views (often called the Statistics Collector views). Key views include pg_stat_activity (current connections and queries), pg_stat_user_tables (per-table stats like sequential and index scans count), and pg_stat_statements (if enabled, it captures aggregated query performance stats). Without a built-in “Profiler”, one common approach is to increase PostgreSQL’s log_statement or log_min_duration_statement to capture slow queries to the logs and then use tools like pgBadger to analyze log files. For more real-time analysis, enabling the pg_stat_statements extension allows you to query a view of the most time-consuming SQL statements (similar to a lightweight Query Store). There are also many open-
  source and commercial monitoring tools for Postgres: e.g. pgAdmin has a dashboard for basic stats, and solutions like Prometheus/Grafana (with PostgreSQL exporters) can graph metrics. Nagios, Zabbix, and Cacti have plugins for PostgreSQL as well.

Alerting & Job Scheduling: PostgreSQL does not include an equivalent to SQL Server Agent in the core product. You can add the pgAgent job scheduler (from pgAdmin) or use cron scripts for scheduling backups and maintenance. In cloud deployments like RDS/Azure, the cloud platform might handle backups or provide an event scheduler.

SQL Server DBAs should be prepared to set up additional tooling or extensions to achieve the monitoring and automation they are accustomed to. The PostgreSQL ecosystem provides many options, but assembling them is a DIY effort compared to SQL Server’s all-in-one environment.

User Management and Security

Managing logins, users, and roles differs significantly between the platforms:

• SQL Server Security Model: SQL Server has a two-level security structure: logins at the server level (for authentication) and users at the database level (mapped to logins, for database permissions). It also has fixed server roles (like sysadmin, dbcreator) and database roles (fixed ones like db_owner, or custom roles). SQL Server supports Windows Authentication (AD integration) and SQL Server Authentication for logins. Permissions can be granted to users or roles on various securables (databases, objects, etc). Additionally, SQL Server provides features like Transparent Data Encryption (TDE) for encrypting data files and Always Encrypted for client-side encryption of sensitive columns, as well as row-level security policies and dynamic data masking for fine-grained access control.
• PostgreSQL Role Model: PostgreSQL uses a unified concept of roles for authentication and authorization – it does not distinguish between logins and users. A role can have the ability to login (that is, a role with the LOGIN attribute is roughly analogous to a SQL Server login+user combination). Roles are global across the entire PostgreSQL instance (cluster); you can grant a role privileges in any database. There are no per-database users that are distinct from roles. This means creating a user in PostgreSQL is done with CREATE ROLE name LOGIN… (or the shorthand CREATE USER which is the same command). Roles can be members of other roles (to implement group roles). PostgreSQL has the concept of a superuser (roles with the SUPERUSER attribute, akin to sa or sysadmin) and various privileges like CREATEDB or CREATEROLE that can be assigned to roles.
  • Authentication in PostgreSQL is controlled by the pg_hba.conf file (host-based authentication), which dictates allowed authentication methods (MD5/SCRAM passwords, GSSAPI Kerberos, peer auth, etc.) for different connection sources. PostgreSQL does not natively integrate with Windows AD for Windows Authentication in the same seamless way (though GSSAPI can be used for Kerberos/AD authentication). In other words, there’s no built-in “Windows Authentication” checkbox – in a Postgres on Windows, you’d still use a password or Kerberos setup. (Managed cloud Postgres or Postgres forks may offer integration with cloud identity services or AD integration as a separate feature.)
  • Granting Permissions: Similar to SQL Server, you GRANT privileges on database objects to roles. PostgreSQL’s permission system is ANSI-compliant, using GRANT/REVOKE on schemas, tables, etc. One notable difference: PostgreSQL by default has a concept of object ownership and default privileges – a newly created table is owned by its creator and no one else (aside from superuser) can access it unless privileges are granted or a schema usage is granted. In SQL Server, members of the db_owner role or a user granted CONTROL on the schema could access new objects. DBAs often create group roles in Postgres (like read_only, read_write) and
    grant those to user roles to manage permissions collectively, similar to how you might use roles in SQL Server.
• Advanced Security Features: Both systems support SSL/TLS for encrypting client-server traffic. SQL Server’s TDE encrypts data at rest in the database files; PostgreSQL does not have built-in TDE in the community edition (some Postgres forks and cloud versions offer it, or one can use filesystem encryption). For column encryption, SQL Server’s Always Encrypted allows certain columns to be transparently encrypted/decrypted on the client side. PostgreSQL doesn’t have an exact counterpart yet (there is work on PG12+ for transparent data encryption and some extensions for client-side encryption).
  • Row-Level Security (RLS): PostgreSQL has built-in support for row-level security policies (since version 9.5), which can restrict which rows a given role can see or modify in a table. SQL Server introduced a similar row-level security feature in recent versions (implemented via predicate functions and security policies). Both allow multi-tenant style security within a table, though the syntax differs.
  • Auditing: SQL Server provides an audit feature and login triggers, etc. PostgreSQL has an event_trigger feature for DDL events, and logging can be configured for auditing. There’s also a popular pgaudit extension to provide detailed audit logs. In the EDB Postgres Advanced Server (a proprietary fork), additional auditing features are built-in.

Key takeaway: Managing users/roles is conceptually simpler in PostgreSQL (just roles for everything), but you lose the separation of server vs database level principals – a role exists cluster-wide. Also, expect to rely more on external mechanisms for some security features (encryption, auditing) that are native in SQL Server. As a DBA, mapping your SQL Server login/user model to Postgres roles and revisiting any use of Windows Auth or encryption will be necessary during migration.

Maintenance and Utilities

• Maintenance Tasks: In SQL Server, many DBAs schedule index maintenance and update statistics jobs (though modern SQL Server can auto-update stats and even defragment indexes online). In PostgreSQL, autovacuum handles statistics updates and dead row cleanup, but you might schedule VACUUM FULL for extreme cases of bloat or use the reindexdb tool for index rebuilds if needed. There is no direct equivalent of DBCC CHECKDB in PostgreSQL; instead, consistency checks are done on the fly, and tools like pg_checksums or the amcheck extension can verify data integrity in a running cluster.
• High Availability & Replication: Both have replication and HA options but implemented differently. SQL Server offers AlwaysOn Availability Groups, failover clustering, log shipping, etc. PostgreSQL has streaming replication (WAL-based) for replicas, and tools like repmgr or Patroni for failover automation. There’s no one-click equivalent to an Availability Group with read/write routing in vanilla Postgres (though Postgres 14+ added basics like quorum commit for sync replication, etc.). For a SQL Server DBA, setting up HA on Postgres means working with the OS and external tools more (or using cloud-managed Postgres where HA is provided by the service).
• Upgrades: SQL Server upgrades in place between versions. PostgreSQL traditionally requires either dump-and-reload or using the pg_upgrade tool to perform binary upgrades between major versions. This is just something to plan for, as major PostgreSQL versions come out annually.

We’ll now look at differences in SQL language and procedural code between the two systems.

SQL & Procedural Differences

Moving to PostgreSQL means adapting Transact-SQL (T-SQL) habits to PostgreSQL’s dialect and procedural language (PL/pgSQL). Here we highlight differences in SQL syntax, stored procedures, functions, and triggers that a SQL Server DBA should be aware of.

SQL Syntax and Query Differences

Both databases implement the SQL standard, but there are many dialect differences:

• Identifier Quoting and Case: SQL Server identifiers are not case-sensitive by default (case sensitivity depends on the collation). In PostgreSQL, unquoted identifiers are folded to lowercase; quoted identifiers are case-sensitive. For example, a table named Employees in SQL Server might be referenced case-insensitively, but in PostgreSQL you’d typically create it unquoted (becomes employees) or if you create using quotes preserving case, you must always quote it exactly. Best practice is to use lowercase unquoted identifiers in PostgreSQL to avoid headaches.
• TOP vs LIMIT: SQL Server uses SELECT TOP 10 * FROM table to limit rows, whereas PostgreSQL uses the standard LIMIT 10 (and optional OFFSET) clause. PostgreSQL does not support the TOP keyword. (Both support the SQL standard FETCH FIRST N ROWS as well.)
• DELETE and UPDATE syntax: In PostgreSQL, you must include the FROM keyword in a DELETE statement (DELETE FROM table …). SQL Server allows DELETE Table WHERE … (omitting FROM) – PostgreSQL does not support that shorthand. Also, PostgreSQL supports the RETURNING clause on INSERT/UPDATE/DELETE to return affected rows (SQL Server’s OUTPUT clause is similar in function).
• String Concatenation: In SQL Server, you use + to concatenate strings. PostgreSQL uses the || operator for concatenation. For example, FirstName + ‘ ‘ + LastName in T-SQL would be FirstName || ‘ ‘ || LastName in PostgreSQL. (PostgreSQL also has a CONCAT() function if needed.)
• Functions and Expressions: Many built-in functions have different names:
  • Date/time: SQL Server’s GETDATE() corresponds to PostgreSQL’s NOW() (or CURRENT_TIMESTAMP). SQL Server’s datepart functions (DATEADD, DATEDIFF, etc.) have equivalents in PostgreSQL (AGE, EXTRACT, etc., or use INTERVAL arithmetic).
  • String: SQL Server’s LEN() is LENGTH() in Postgres (note: LEN in T-SQL ignores trailing spaces, whereas LENGTH in PG counts them). ISNULL(x, alt) in T-SQL becomes COALESCE(x, alt) (PostgreSQL doesn’t have ISNULL, but COALESCE is standard SQL). PostgreSQL also supports regex matching via ~ operator and REGEXP_MATCHES which has no direct T-SQL equivalent.
  • PostgreSQL is strict with types. An example: in SQL Server, you can often compare varchar and int implicitly; PostgreSQL will throw an error if you try to compare text to number without casting. You may need more explicit casts (::type or CAST) in PostgreSQL.
• Temp Tables and Table Variables: PostgreSQL uses regular tables in a special schema (temp schema) for temporary tables (CREATE TEMP TABLE). They behave somewhat like SQL Server temp tables (scope to session, auto-dropped on disconnect), but there’s no exact equivalent of T-SQL table variables (@tableVar). You can use a temporary table or a common table expression instead. Also, each session’s temp tables are isolated in Postgres; you can’t directly share a temp table between sessions.
• MERGE/UPSERT: SQL Server’s MERGE statement can do insert/update/delete in one statement. PostgreSQL introduced MERGE in version 15, but in older versions, the typical approach was to use an INSERT … ON CONFLICT … DO UPDATE for upserts or do separate UPDATE then INSERT if no row updated (or use a stored function to encapsulate logic). So if you’re on PostgreSQL 14 or lower, you’ll need to rewrite MERGE logic using ON CONFLICT or alternative approaches.
• Other DDL differences: PostgreSQL uses SERIAL (or GENERATED AS IDENTITY in newer versions) to create auto-incrementing primary keys instead of IDENTITY property. For example, SERIAL is a pseudo-type that creates a sequence and a default nextval for the column. Also, PostgreSQL has no CREATE SYNONYM feature; you’d use views or search_path to achieve similar indirection if needed. Another difference: PostgreSQL allows CREATE OR REPLACE for many objects (functions, views, etc.) which is handy during deployments, whereas SQL Server often requires dropping and recreating objects in scripts.

Stored Procedures and Functions

SQL Server and PostgreSQL both support server-side programming but with different languages and invocation methods:

• T-SQL vs PL/pgSQL: SQL Server’s stored procedures and user-defined functions are written in T-SQL (Transact-SQL). PostgreSQL’s primary procedural language is PL/pgSQL (Procedural Language/PostgreSQL), which is syntactically different but plays a similar role. PostgreSQL also allows stored functions in other languages (PL/Python, PL/Perl, PL/Java, etc.) if enabled, and SQL Server allows creating CLR procedures/functions using .NET languages.
• Stored Procedures vs Functions: Historically, PostgreSQL only had functions (which could return a value or result set). Each function ran within a transaction and could not commit or rollback independently. Starting with PostgreSQL 11, there is a concept of a stored procedure (created with CREATE PROCEDURE) which does not return a value and can manage transactions (allowing commits or rollbacks inside). However, a lot of server-side code in PostgreSQL is still done with functions (especially if written before PG11). In practice:
  • A PostgreSQL function can return scalar values, table results (setof), or void. You call functions in SQL (e.g. SELECT funcname(args); or within queries if they return a result set).
  • A PostgreSQL procedure is invoked with a CALL procedure(args); and cannot be part of a SELECT. Procedures are useful for migration when you have a batch of actions with transaction control, but if you just need to return data, functions are idiomatic in PG.
  • In SQL Server, a stored procedure (created with CREATE PROCEDURE) is called with the EXEC or EXECUTE command and cannot be used in a SELECT. A user-defined function (UDF) in SQL Server can be scalar or table-valued; those can be invoked in queries. PostgreSQL’s functions cover both concepts depending on their return type.
• Calling Conventions: SQL Server uses EXEC ProcName @param = value, … to run a procedure. PostgreSQL uses SQL-callable functions/procedures. For functions, you typically do SELECT function_name(args); (or SELECT * FROM function_that_returns_table(args);). For procedures (PG11+), you do CALL procedure_name(args);. One tricky point: If you migrate a SQL Server stored procedure that produces result sets, in PostgreSQL you might implement it as a function returning a table so that you can SELECT * FROM my_function(); to get the result. Also, PostgreSQL doesn’t support output parameters the same way – instead a function can simply return a composite type or use INOUT params. If you need multiple result sets from one call, PostgreSQL functions can return refcursors or use multiple return queries, but it’s not as straightforward as multiple SELECT statements in a T-SQL proc.
• Variable Declaration and Syntax: T-SQL uses DECLARE @var INT and SET @var = 1. PL/pgSQL uses a DECLARE block at the top of the function body and no @ on variables. For example:

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
DECLARE
sum_result INT;
BEGIN
sum_result := a + b;
RETURN sum_result;
END;
$$ LANGUAGE plpgsql;

This contrasts with a SQL Server procedure:

CREATE PROCEDURE AddNumbers @a INT, @b INT, @sum INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @sum = @a + @b;
END;

• In PL/pgSQL, := is used for assignment, and each block or control structure is ended explicitly with END (e.g. END IF, END LOOP). Also, PL/pgSQL uses PERFORM some_query; when you want to execute a query that doesn’t return anything (like an UPDATE statement inside a function).
• Transaction Scope: In SQL Server, you can begin/commit transactions within a stored procedure. In PostgreSQL, if you are writing a function (not a procedure), you cannot commit or rollback within that function – it runs entirely in the context of an outer transaction. If using the newer stored procedures (CREATE PROCEDURE), you can issue COMMIT/ROLLBACK inside, or call them with CALL in an explicit transaction block as needed. This means some migration of complex T-SQL that manages transactions might require redesign (you could break logic into multiple Postgres procedures or calls since each PL/pgSQL function is all-or-nothing).
• Error Handling: SQL Server uses TRY…CATCH blocks in T-SQL for error handling, and you can raise errors with RAISERROR or THROW. PostgreSQL’s PL/pgSQL uses BEGIN … EXCEPTION … END blocks to catch exceptions. The concept is similar but syntax differs. You would translate a TRY/CATCH to a BEGIN block with an EXCEPTION section in PL/pgSQL, and use RAISE to throw errors. One limitation is that PostgreSQL doesn’t have an exact equivalent to @@ERROR or @@ROWCOUNT global variables; you check the result of SQL directly or handle exceptions.
• CLR vs Extensions: If your SQL Server environment uses CLR stored procedures or functions (C# code running in the database), the PostgreSQL analogue is using an extension language (like PL/Python or PL/Java) or foreign data wrappers for external functionality. This requires enabling the appropriate extension and writing functions in that language. It’s doable, but might require re-writing .NET logic in another language or as an external service if migrating.

Triggers: Both PostgreSQL and SQL Server support triggers, but with notable differences in how and when they fire:

• PostgreSQL Triggers: PostgreSQL supports triggers that fire BEFORE or AFTER an INSERT, UPDATE, or DELETE (or TRUNCATE), as well as INSTEAD OF triggers on views. Triggers can be defined to execute per row or per statement. Commonly, you write row-level triggers for each row affected by a DML statement. The trigger function in PostgreSQL is a separate function (written in PL/pgSQL or another language) that returns NULL or OLD/NEW as required, and you reference NEW and OLD records in it. You can have multiple triggers on the same event for a table, and you can control their firing order with the FOLLOWS/PRECEDES options (PostgreSQL 11+).
• SQL Server Triggers: SQL Server triggers fire after the DML statement by default (FOR/AFTER triggers), and you can also create INSTEAD OF triggers (commonly on views to intercept an insert/update). SQL Server triggers are always statement-level – they fire once per statement, and within the trigger you handle all the affected rows using the pseudo-tables inserted and deleted. You cannot natively have a BEFORE trigger (all SQL Server triggers are AFTER, except INSTEAD OF which substitutes the action). Also, SQL Server supports DDL triggers (on events like CREATE_TABLE) and Logon triggers (on user login events) for auditing or other purposes.

Key differences:

• In PostgreSQL, if you want to ensure something happens before each row insert (e.g. assign a UUID or modify input), a BEFORE ROW trigger is ideal. In SQL Server, you would typically do that with a default or within an AFTER trigger by modifying the inserted data (though after triggers cannot change the data that’s being inserted, so you’d use defaults or INSTEAD OF if you need to override values).
• PostgreSQL’s separation of trigger function and trigger definition means you write a function like CREATE FUNCTION audit_log() RETURNS trigger AS $$ BEGIN … END; $$ LANGUAGE plpgsql; and then CREATE TRIGGER trig AFTER INSERT ON table FOR EACH ROW EXECUTE FUNCTION audit_log();. In SQL Server, the trigger’s body is defined as part of the CREATE TRIGGER statement itself.
• If migrating triggers, you will likely rewrite the logic into a PL/pgSQL function and adjust references: use NEW.column instead of inserted.column, etc. Also remember PostgreSQL triggers can fire per row, so if your SQL Server trigger assumed set-based operation (handling multiple rows in one execution), in PostgreSQL the row-level trigger will execute for each row, which could have performance implications. Alternatively, you could simulate a set-based trigger by using a statement-level trigger (FOR EACH STATEMENT) that iterates through the affected rows via a cursor, but that’s rarely done. It may be more straightforward to implement logic in a row trigger or handle multi-row operations within the trigger function manually using arrays or subqueries if needed.
• DDL triggers in SQL Server (and logon triggers) have no exact equivalent in vanilla PostgreSQL. PostgreSQL does have Event Triggers for certain DDL events (CREATE, ALTER, DROP commands) at the database instance level, which can be used for auditing or controlling DDL. They are less granular than SQL Server’s DDL triggers which can be database-scoped. If you rely on those (e.g. to prevent certain schema changes), you’d need to see if PostgreSQL event triggers can meet the requirement.

Migration Challenges (SQL Server to PostgreSQL)

Migrating a database from SQL Server to PostgreSQL is not just a straight backup-restore. Here are common challenges SQL Server DBAs face, and how to address them:

• Data Type Mismatches: SQL Server’s data types have PostgreSQL equivalents, but some need conversion. For example, VARCHAR(MAX) in SQL Server can be TEXT in PostgreSQL (both handle large text). NUMERIC(p,s) works similarly in both. SQL Server’s DATETIME/SMALLDATETIME should be mapped to PostgreSQL TIMESTAMP [WITHOUT TIME ZONE] (or TIMESTAMPTZ if it’s actually datetime with time zone in context). The MONEY type in SQL Server can be NUMERIC(19,4) in PostgreSQL (Postgres has a money type, but it behaves differently and is locale-specific). The UNIQUEIDENTIFIER (GUID) in SQL maps to UUID in Postgres (you may need to enable the uuid-ossp extension to generate UUIDs). Also note BIT in SQL Server is a numeric 0/1, while in PostgreSQL, BOOLEAN is a true boolean type (TRUE/FALSE). During migration, use tools or scripts to translate schema DDL to appropriate types.
• Schema vs Database Organization: In SQL Server, a single instance can host multiple databases, and you often cross-database query by specifying Database.Schema.Table. In PostgreSQL, an instance (called a cluster) also hosts multiple databases, but you cannot query across databases in the same query (each connection is tied to one database). Instead, PostgreSQL encourages use of schemas within a database for logical separation. If your application frequently joins across databases in SQL Server, you might need to merge those into schemas in one PostgreSQL database, or use foreign data wrappers (FDWs) to connect from one DB to another, which adds complexity. The dblink or postgres_fdw extension can function somewhat like Linked Servers in SQL Server, but performance will differ. Plan to redesign cross-database interactions either at the application level or using FDWs.
• T-SQL Code Conversion: Hand-written T-SQL in stored procedures, functions, and triggers must be converted to PL/pgSQL (or another supported language). This often requires:
  • Changing procedural constructs (WHILE loops, IF syntax, variable declarations, etc.) to PL/pgSQL format.
  • Replacing built-in function names (e.g., GETDATE() → NOW(), @@ROWCOUNT → use FOUND or GET DIAGNOSTICS in PL/pgSQL, etc.). ISNULL() → COALESCE(), DATEADD() → + INTERVAL, DATEDIFF() → AGE() or date_part().
  • Handling differences in error/exception handling (TRY/CATCH → EXCEPTION blocks).
  • Rewriting any usage of temporary tables or table variables, perhaps as transient tables or refactored logic.
  • If dynamic SQL is used (EXEC (‘SELECT …’) in T-SQL), in PL/pgSQL you would use the EXECUTE … USING … construct for dynamic queries, or build the query string and use EXECUTE within the PL/pgSQL function.
  • Ensure the logic of triggers accounts for per-row invocation in Postgres (or explicitly loop over NEW/OLD in a statement-level trigger if needed).

See at the END of this document for more details about Code Conversion Guide.

Automated tools can assist in converting T-SQL to PL/pgSQL. For instance, AWS Schema Conversion Tool (SCT) or third-party tools like Ispirer Migration Toolkit can automate much of the code translation and flag any incompatible constructs. Still, expect to allocate time for debugging and testing converted code.

• Query Tuning Differences: After migration, some queries might not perform as well until tuned for PostgreSQL. The execution plans can differ. For example, PostgreSQL might need a different index strategy (maybe an index on expressions, or different multi-column index order) to match SQL Server performance. Also, SQL Server’s optimizer has some different behaviors (e.g., SQL Server may automatically create missing statistics on the fly; PostgreSQL does not). Parameterized query performance may differ – e.g., what was a fast stored proc in SQL Server might need a hint in PG if the plan selection isn’t optimal due to generic plans. DBAs should be ready to analyze using EXPLAIN and add indexes or tweak queries accordingly. Remember that PostgreSQL can use hashes or merges for joins where SQL might use nested loops, etc., so verify the plans and adjust as needed (sometimes adjusting work_mem to allow hash joins or sorting in memory can help a lot).
• Case Sensitivity Issues: If your SQL Server database was case-insensitive, you might find queries that assume case-insensitive comparisons. PostgreSQL’s text comparisons are case-sensitive by default (unless you use the citext extension or ILIKE for case-insensitive LIKE). You may need to apply LOWER() on comparisons or define citext columns where appropriate to mimic case-insensitive behavior. Also, migrating object names, it’s simplest to fold everything to lower-case unquoted in PG, and update application queries to match if they were delimited or case-sensitive in SQL Server.
• Application Changes: Client applications using ADO.NET, ODBC, etc., will need new connection strings (to point to Postgres). Also, any SQL that the application sends might need adjustments. For example, parameter placeholders: SQL Server (ODBC/OLEDB) uses @param names, whereas PostgreSQL (libpq, ODBC) can use $1, $2 for positional parameters or parameter names if the framework supports it. If the app executed any T-SQL-specific commands (like SET TRANSACTION ISOLATION LEVEL SNAPSHOT which is SQL Server-specific, or USE [Database] statements to switch DB context), those will need removal or replacement.
• Lack of SQL Server-specific features: Some features in SQL Server simply don’t exist or have analogues in Postgres:
  • SQL Server Agent (job scheduler) – solution: use pgAgent or external schedulers for jobs.
  • Replication/CDC – Postgres has logical replication and third-party change data capture tools, but if you used SQL Server’s transactional replication or Change Tracking/CDC, you’ll need new approaches in Postgres (like AWS DMS, Debezium, or built-in logical decoding).
  • Always On/Failover – need to set up streaming replication or use a cloud managed service for HA.
  • Analytical features like OLAP cubes (SSAS) are outside Postgres’s scope (you’d use separate analytics tools or things like Citus extension for distributed queries if needed).
  • Integration Services (SSIS) – would be replaced by other ETL tools or Python scripts or cloud data pipelines when moving to an open-source stack.
• Testing and Validation: It’s a challenge to ensure the migrated database behaves the same. Data should be validated (row counts, basic aggregates to ensure nothing is lost or mis-converted). Also, test all application functionality against the new PostgreSQL backend. Some edge-case differences (like the handling of NULL in unique constraints mentioned earlier, or default values differences) could surface issues. For instance, remember that in PostgreSQL a unique constraint treats NULL as not equal to NULL (allowing multiple NULLs) whereas SQL Server unique indexes treat NULL as a single value (allow only one NULL). This might actually resolve some SQL Server quirks (like “only one NULL allowed in unique column”) but it’s a change to note.

Migration Tools & Solutions: Use migration tools to automate where possible. The AWS SCT (Schema Conversion Tool) or Azure DMS can convert schema and data. Ispirer and ESF Database Migration Toolkit are other options. Some tools can even translate stored procedure code to PL/pgSQL. However, human review is necessary for complex procedures. For the data transfer, using ETL or database migration services (AWS DMS, Azure DMS) can move data in bulk and even do ongoing replication until cutover, minimizing downtime.

In summary, expect to invest time in rewriting code, testing functionality, and tuning performance. But also leverage the PostgreSQL features and extensions that can substitute for SQL Server features (for example, use Foreign Data Wrappers instead of linked servers, use cron + psql scripts to replace SQL Agent jobs, etc.). The community has a lot of experience with these migrations, so many solutions are documented.

Tooling & Ecosystem

Working in PostgreSQL’s ecosystem will feel different from the Microsoft-centric tooling of SQL Server. Here’s how the tools and community support compare:

• Client and Administration Tools: SQL Server DBAs are accustomed to SQL Server Management Studio (SSMS) – a comprehensive GUI for querying, design, and admin on Windows – or the newer Azure Data Studio (cross-platform). In the PostgreSQL world, the equivalent primary GUI is pgAdmin 4 (a web/electron-based client). PgAdmin allows you to run queries, browse schemas, and manage the server. There are also many alternative GUIs: DBeaver, HeidiSQL, DataGrip, OmniDB, Toad Edge, etc., which support PostgreSQL. Even Oracle’s SQL Developer can connect to PostgreSQL. Many SQL Server folks also like Azure Data Studio, which can connect to PostgreSQL via an extension, providing a unified experience.

For monitoring, unlike SQL Server which has built-in Activity Monitor, you might use pgAdmin’s dashboards or specialized tools. Some third-party monitoring systems (SolarWinds, Datadog, etc.) support both SQL Server and Postgres, which can ease the transition.

• Command-Line Tools: SQL Server’s sqlcmd is a command-line SQL runner, and Postgres has the powerful psql command-line client. Psql allows scripting and can be used in automation (similar to using sqlcmd with scripts). Postgres also provides utilities like pg_dump/pg_restore (for backups as discussed), vacuumdb (to run vacuum), pg_ctl (to control server start/stop), etc. If you used PowerShell with SQL Server (SQLPS or dbatools), note that you can manage PostgreSQL via shell scripts or Python scripts (perhaps using libraries like psycopg2 for automation tasks).
• Extensions and Plugins: One of PostgreSQL’s strengths is its extensibility. There is a rich ecosystem of extensions that can be enabled in PostgreSQL to add functionality:
  • PostGIS for geospatial support (SQL Server has spatial types built-in; in Postgres you get even more powerful GIS features via PostGIS).
  • pg_stat_statements for query monitoring (discussed earlier).
  • postgres_fdw for connecting to other Postgres instances or even other databases (yes, you can create a foreign table in Postgres that pulls data from, say, Oracle or MySQL, somewhat analogous to Linked Servers).
  • PgCrypto for cryptographic functions (if you need to store hashed passwords, etc., similar to how SQL Server has HASHBYTES or encryption functions).
  • Full Text Search is built-in (you create a tsvector column and use GIN indexes), no separate service needed like SQL Server’s Full-Text Engine.
  • Custom Languages: You can add languages like PL/Python, PL/R to write stored procedures in Python or R for advanced logic.

SQL Server is less extensible (you generally rely on Microsoft to provide features or use CLR integration for custom code). In PostgreSQL, if you need something, there’s often an extension for it. Embracing this ecosystem can replace or enhance many SQL Server features. For example, if you missed SQL Server’s ability to send emails via Database Mail, you could use Postgres’s smtp extension or just call an external script via triggers, etc., or better yet, let the application layer handle it (as one AWS tip suggests, using external services like AWS Lambda for such tasks).

• Community and Support: PostgreSQL has a large, active open-source community. There are many online resources: the official PostgreSQL documentation is very thorough and will be your frequent reference. Community forums like Stack Overflow, the PostgreSQL mailing lists, and Reddit (/r/PostgreSQL) are filled with Q&A. There is no official vendor support unless you use a provider (e.g., EDB offers support subscriptions, or cloud providers support their services). In contrast, SQL Server DBAs might be used to Microsoft Premier support or MSDN/Docs for official guidance. PostgreSQL’s community and contributors often release new features annually, and the community often backports fixes to stable branches quickly.
• Both databases rank among the most popular systems, so expertise is widely available. Transitioning DBAs should leverage community-contributed tools, blog posts, and migration guides (many have done it before). Books and documentation targeted at “SQL Server to Postgres migration” can also provide recipes for common translations.
• Ecosystem Compatibility: Both PostgreSQL and SQL Server run on major cloud platforms. If you’re in AWS, you might use Amazon RDS for PostgreSQL or Aurora PostgreSQL; in Azure, there’s Azure Database for PostgreSQL. These managed services take care of backups and HA, which can ease some administrative burdens (though they might limit some features or require adjustment in how you manage roles, etc.). On the analytics side, many BI tools (Tableau, PowerBI, etc.) that worked with SQL Server will also work with Postgres via ODBC/JDBC. ORMs like Entity Framework or Hibernate have PostgreSQL dialects, so most applications can be repointed to a new database with minimal changes beyond connection strings and query adjustments.
• Versioning and Releases: PostgreSQL releases a new major version yearly (with features driven by the community). Upgrading might involve using pg_upgrade or dump/restore if you host it yourself. SQL Server releases major versions less frequently (every 2-3 years), with cumulative updates in between, and upgrades in-place. As a DBA, you’ll plan smaller but more regular version upgrades with Postgres. On the flip side, you get new features (like new index types, improved performance) much faster in the Postgres world.

In the end, both databases are powerful and mature. SQL Server may win in ease-of-use and integrated tooling for a Windows/MS shop, whereas PostgreSQL offers flexibility, lower cost (open source), and a broad ecosystem of extensions and deploy options. Many SQL Server DBAs find that once they adapt to the PostgreSQL way, they appreciate the transparency and control it offers.

Below is a summary table comparing PostgreSQL and SQL Server across key areas for a quick overview:

This comparison should give SQL Server professionals a clear picture of what to expect in PostgreSQL. While the two systems share the relational core and SQL basics, the differences in concurrency, tooling, backup, and procedural logic are significant. By understanding these differences – and leveraging PostgreSQL’s strengths like MVCC, extensibility, and its active community – SQL Server DBAs can confidently transition and run PostgreSQL effectively in production. The learning curve involves new tools and syntax, but the outcome is the ability to work across both open-source and commercial database environments with equal ease.

T-SQL to PL/pgSQL Code Conversion Guide

Converting T-SQL code to PL/pgSQL requires systematic changes across multiple areas. This comprehensive guide covers the key differences and conversion requirements.

1. Procedural Constructs

Control Flow Statements

– WHILE loops:

- T-SQL: `WHILE (@counter < 10)`
- PL/pgSQL: `WHILE counter < 10 LOOP ... END LOOP;`

– IF statements:

- T-SQL: `IF @value = 1 BEGIN ... END`
- PL/pgSQL: `IF value = 1 THEN ... END IF;`

– CASE statements:

- Both syntaxes are similar, but PL/pgSQL requires END CASE

Variable Declarations

- T-SQL: `DECLARE @var int = 1;`
- PL/pgSQL: `var integer := 1;`

2. Built-in Function Conversions

Common function mappings:

– Date/Time:

- GETDATE() → NOW()
- DATEADD() → + INTERVAL
- DATEDIFF() → AGE() or date_part()
- GETUTCDATE() → now() at time zone 'UTC'

– String:

- ISNULL() → COALESCE()
- CHARINDEX() → position()
- SUBSTRING() → substring() (similar syntax)
- LEN() → length()

– System:

- @@ROWCOUNT → FOUND or GET DIAGNOSTICS
- @@ERROR → SQLSTATE
- @@IDENTITY → lastval()
- SCOPE_IDENTITY() → currval()

3. Error Handling

Basic Structure

-T-SQL:

BEGIN TRY
-- code here
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH

PL/pgSQL:

BEGIN
-- code here
EXCEPTION
WHEN others THEN
-- error handling
END;

Key Differences
– Error identification: SQLSTATE codes in PostgreSQL vs. error numbers in SQL Server
– Error information access: GET STACKED DIAGNOSTICS in PostgreSQL
– Message output: RAISE NOTICE vs PRINT
– Exception scope: Contained within block in PostgreSQL

4. Temporary Tables and Table Variables

Temporary Tables

– T-SQL: CREATE TABLE #temp
– PL/pgSQL: CREATE TEMPORARY TABLE temp

Key Differences:
– No global temp tables (##) in PostgreSQL
– Different session handling
– ON COMMIT behaviors differ
– Consider using WITH TEMP for query-scope temp tables

Table Variables:
– Replace T-SQL table variables with temporary tables
– Consider using composite types for simple cases
– Use TEMPORARY tables with ON COMMIT DROP for scope control

5. Dynamic SQL
Basic Syntax
– T-SQL: `EXEC(‘SELECT * FROM ‘ + @tablename)`
– PL/pgSQL: `EXECUTE format(‘SELECT * FROM %I’, tablename)`

Safety Features
– Use quote_literal() for literal values
– Use quote_ident() for identifiers
– format() function for complex string building
– USING clause for parameter passing:

EXECUTE ‘SELECT * FROM users WHERE id = $1’ USING user_id;

6. Triggers
Timing and Scope
– BEFORE/AFTER/INSTEAD OF (limited INSTEAD OF support in PostgreSQL)
– Statement-level vs Row-level execution

Data Access
– NEW/OLD record handling differs
– No INSERTED/DELETED tables in PostgreSQL
– Row-level triggers fire per row by default
Example conversion:

T-SQL

CREATE TRIGGER tr_update ON table
AFTER UPDATE AS
BEGIN
SELECT * FROM inserted
SELECT * FROM deleted
END

PL/pgSQL

CREATE TRIGGER tr_update
AFTER UPDATE ON table
FOR EACH ROW
EXECUTE FUNCTION trigger_function();
CREATE FUNCTION trigger_function()
RETURNS trigger AS $$
BEGIN
— Access NEW and OLD records directly
— NEW contains updated values
— OLD contains previous values
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

7. Return Value Handling

Function Returns
– OUTPUT parameters → RETURNS/OUT parameters
– Table-valued functions require different syntax
– Use RETURN NEXT/RETURN QUERY for multiple rows

8. Transaction Control
– Different savepoint syntax
– Default isolation levels differ
– Autonomous transactions handled differently
– XACT_ABORT behavior differences

9. Security Context
– EXECUTE AS → SECURITY DEFINER
– Different permission inheritance models
– Owner chains not supported in PostgreSQL
– Role-based security differences

10. Performance Considerations

– Plan caching differs between platforms
– Parameter sniffing behavior varies
– Different recompilation triggers
– Statistics handling varies

Best Practices

1. Start with schema and data type mapping
2. Convert stored procedures and functions incrementally
3. Test thoroughly with representative data volumes
4. Monitor performance in both environments
5. Document PostgreSQL-specific optimizations
6. Consider using pgTAP for testing stored procedures

Common Pitfalls

1. Assuming identical transaction behavior
2. Not accounting for different NULL handling
3. Overlooking date/time precision differences
4. Expecting identical index behavior
5. Missing cursor behavior differences