12 Things SQL Server DBAs Need to Know Before Supporting PostgreSQL

---

SQL Server DBAs are being asked to support PostgreSQL environments more frequently than ever. Cloud migrations, application modernization projects, and organizations running mixed database estates all create situations where a DBA fluent in SQL Server needs to operate on a PostgreSQL server with minimal ramp-up time. The two engines share SQL syntax at a surface level, and that similarity is what makes the transition deceptively difficult. Just enough looks familiar to create confidence. Then something breaks in a way that makes no sense if SQL Server is the mental model.

This article covers twelve architectural and operational differences between PostgreSQL and SQL Server that have direct production consequences. Every fact is verified from official PostgreSQL documentation at postgresql.org and Microsoft Learn. The goal is not a complete PostgreSQL learning guide. The goal is to identify the twelve things most likely to surprise a SQL Server DBA supporting a PostgreSQL environment, with the specific migration and operational implications called out for each.

1 MVCC Is Always On. RCSI Is Not.

This is the single most important architectural difference for a SQL Server DBA to internalize because it explains why transaction patterns that cause severe blocking in SQL Server may never have surfaced as a problem in the PostgreSQL source environment.

According to the official PostgreSQL documentation: “The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.” This is not a configuration option in PostgreSQL. It is how the engine works by design, for every database, from the first connection.

In SQL Server, the default isolation level is READ COMMITTED with lock-based concurrency. Readers take shared locks on the rows and pages they read. If a writer holds an exclusive lock on the same data, the reader waits. This reader-writer blocking is the default SQL Server behavior. Read Committed Snapshot Isolation (RCSI) gives SQL Server similar behavior to PostgreSQL’s MVCC by providing readers with row versions from TempDB instead of requiring shared locks, but RCSI must be explicitly enabled per-database. It is off by default on every SQL Server database.

-- Check RCSI status on all user databases
-- Databases migrated from PostgreSQL should have RCSI enabled
SELECT name, is_read_committed_snapshot_on AS RCSI_Enabled
FROM sys.databases WHERE database_id > 4 ORDER BY name;

-- Enable RCSI (requires brief ~5 second exclusive DB lock)
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

2 Every PostgreSQL Table Is a Heap

In SQL Server, a table without a clustered index is called a heap and is considered a storage anti-pattern for most workloads. Microsoft Learn states: “Most tables should have a carefully chosen clustered index unless a good reason exists for leaving the table as a heap.” Heaps cause forwarded fetches, RID-level locking, and require full table scans for primary key lookups.

In PostgreSQL, every table is a heap. There is no clustered index concept in PostgreSQL’s storage engine. Rows are stored in the order they were inserted, or in whatever order the storage manager places them after updates and vacuuming. PostgreSQL relies on non-clustered indexes (called simply “indexes” in PostgreSQL) for fast row lookups. These indexes store a physical pointer called a ctid (column tuple identifier) to the row’s current location in the heap.

3 CLUSTER Is a One-Time Operation, Not a Maintained Index

PostgreSQL does have a CLUSTER command. A SQL Server DBA encountering it might assume it is equivalent to a clustered index. It is not. According to the official PostgreSQL documentation: “After a cluster operation, the table is physically reordered based on the index information. But subsequent INSERT, UPDATE, and DELETE operations are not guaranteed to maintain the cluster order.”

PostgreSQL’s CLUSTER is a one-time physical reorder of the heap at the moment the command executes. Rows written after the CLUSTER completes are stored wherever the storage manager places them, not in index order. To re-establish the physical ordering, CLUSTER must be run again manually. No automatic process maintains it. In SQL Server, the clustered index continuously maintains the physical ordering of rows as data changes. An INSERT into a SQL Server clustered table places the new row in the correct ordered position. PostgreSQL’s CLUSTER provides no such guarantee after the initial operation.

4 Dead Tuples Do Not Clean Themselves: VACUUM

PostgreSQL’s MVCC implementation stores multiple versions of updated and deleted rows directly in the heap. When a row is updated, the old version is not immediately removed. It remains in the table as a dead tuple, visible only to transactions that started before the update. According to official PostgreSQL documentation: “In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it is necessary to do VACUUM periodically, especially on frequently-updated tables.”

VACUUM reclaims the space occupied by dead tuples and makes it available for reuse. Without regular VACUUM, dead tuples accumulate, tables bloat in physical size, index scans become less efficient, and eventually transaction ID wraparound becomes a risk. PostgreSQL’s autovacuum daemon runs VACUUM automatically in the background, but autovacuum must be configured correctly and monitored. On write-heavy tables, the default autovacuum settings may not keep up with dead tuple accumulation.

In SQL Server, deleted and updated rows generate ghost records that are cleaned automatically by the ghost cleanup background task. The DBA does not need to schedule or monitor this process. It runs continuously without configuration.

-- Check dead tuple accumulation on PostgreSQL
-- High n_dead_tup relative to n_live_tup = autovacuum not keeping up
SELECT
    relname                             AS TableName,
    n_live_tup                          AS LiveTuples,
    n_dead_tup                          AS DeadTuples,
    CASE WHEN n_live_tup > 0
        THEN ROUND(100.0 * n_dead_tup / n_live_tup, 1)
        ELSE 0
    END                                 AS DeadTuplePct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

5 Physical Files: One Per Table vs Shared Data Files

In SQL Server, all database objects, including every table, index, and internal structure, share the same set of physical data files: the MDF primary data file and any NDF secondary data files. The file system sees only a small number of large files per database. SQL Server manages internal allocation within those files.

In PostgreSQL, each table and each index has its own physical file on the operating system. A database with 500 tables and 1,200 indexes has approximately 1,700 relation files on disk, plus additional files for TOAST storage (PostgreSQL’s mechanism for handling large column values). Each file is named by the table’s object identifier (OID) in the PostgreSQL catalog. The files live in the PostgreSQL data directory under the database-specific subdirectory. This means the file system reflects the table structure in a way that SQL Server’s file system does not.

6 shared_buffers Defaults to 128 MB and Must Be Tuned

SQL Server allocates memory aggressively by default. The default max server memory setting is effectively unlimited, and SQL Server will claim as much RAM as the OS makes available. The DBA’s job is to set max server memory to leave enough RAM for the OS and other processes.

PostgreSQL takes the opposite approach. According to official PostgreSQL documentation: “The default is typically 128 megabytes (128MB)… If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system.” A production PostgreSQL server installed with default settings is running with a buffer cache of 128 MB regardless of available RAM. This is intentional: PostgreSQL is designed to run on almost any hardware, from embedded systems to enterprise servers. But a production server left at the default is severely under-utilizing available memory.

Unlike SQL Server which manages its buffer pool dynamically, PostgreSQL requires a server restart to change shared_buffers because it is allocated as a shared memory segment at server startup.

-- Check current PostgreSQL memory settings
-- Connect via psql and run:
SHOW shared_buffers;         -- should be ~25% of total RAM for production
SHOW work_mem;               -- per sort/hash operation
SHOW effective_cache_size;   -- planner hint, not allocated memory
SHOW maintenance_work_mem;   -- used by VACUUM, CREATE INDEX, etc.

-- Check total RAM available (PostgreSQL 9.4+)
SELECT pg_size_pretty(total_bytes) AS TotalRAM
FROM pg_control_checkpoint();
-- Or check via OS: SELECT * FROM pg_config WHERE name = 'PKGLIBDIR';

7 effective_cache_size Is Not Memory PostgreSQL Allocates

This setting confuses almost every SQL Server DBA encountering PostgreSQL configuration for the first time. effective_cache_size sounds like a memory allocation setting. It is not. According to official PostgreSQL documentation, effective_cache_size is an estimate of the effective size of the disk cache that is available to a single query. It has no effect on the size of PostgreSQL’s shared memory and does not affect the actual memory available to the database server. It is used only as a hint to the query planner about how much data is likely to be cached in the OS page cache when estimating the cost of index versus sequential scans.

A higher effective_cache_size makes the planner more willing to use index scans because it assumes more data is likely to be cached. A lower value makes sequential scans more attractive because the planner assumes less is cached. The typical recommendation is to set it to 50 to 75 percent of total RAM, representing the combined PostgreSQL shared_buffers and OS page cache. Changing this setting does not allocate any memory.

8 work_mem Is Per Sort Operation, Not Per Session

In SQL Server, a memory grant is allocated per query execution based on the query plan’s estimated memory requirements. The grant is a single allocation for the entire query’s execution.

In PostgreSQL, work_mem is the amount of memory available to each sort or hash operation within a query. A single complex query with multiple sorts and hash joins can use multiples of work_mem simultaneously. If a query performs five sort operations and each requires the full work_mem allocation, the total memory used by that one query is five times work_mem. Multiply this by concurrent sessions and a work_mem setting that looks conservative can exhaust available RAM on a server with many concurrent connections.

The default work_mem is 4 MB. On a server running 200 concurrent sessions each running a query with multiple sorts, the potential memory consumption from work_mem alone is in the gigabytes.

9 Roles Live at the Instance Level, Not the Database Level

In SQL Server, security has two distinct layers. Logins exist at the server (instance) level and control who can connect to the instance. Users exist inside each database and control what a connected login can do within that specific database. A login can be mapped to different users in different databases with different permissions in each.

In PostgreSQL, roles exist at the cluster (instance) level and serve as both users and groups. The same role can connect to and access multiple databases. Database-level permissions are granted within each database, but the role identity is global to the instance. PostgreSQL does not separate the concepts of login (server-level identity) and user (database-level identity) the way SQL Server does. A role is the single object that represents both. Roles can be members of other roles, which is how group-based permission management is implemented.

-- List all roles in a PostgreSQL instance
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles ORDER BY rolname;

-- List role memberships (group assignments)
SELECT r.rolname AS GroupRole, m.rolname AS MemberRole
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
ORDER BY r.rolname;

-- List permissions on a specific database
-- Connect to the target database first
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('PUBLIC', 'postgres')
ORDER BY grantee, table_schema, table_name;

10 There Is No Built-In Job Scheduler

SQL Server Agent is a core SQL Server component that ships with and is managed within SQL Server itself. It provides job scheduling, job step sequencing, alerts, operator notifications, and integration with Database Mail. SQL Server DBAs manage all scheduled maintenance, backup jobs, and monitoring through SQL Server Agent without installing additional software.

PostgreSQL does not include a built-in job scheduler. The core PostgreSQL engine has no equivalent of SQL Server Agent. Scheduling recurring PostgreSQL tasks requires one of the following approaches, all of which are external to the database engine itself:

• pg_cron: A PostgreSQL extension that runs jobs based on cron-style scheduling expressions. Installed as an extension and managed within the database. Available on many cloud PostgreSQL services.
• pgAgent: A separate job scheduling agent for PostgreSQL, managed through pgAdmin. Requires a separate installation and its own metadata tables in a target database.
• OS-level schedulers: Linux cron or Windows Task Scheduler invoking psql or scripts on a schedule, completely outside PostgreSQL.
• Cloud platform schedulers: Azure Database for PostgreSQL, Amazon RDS for PostgreSQL, and Google Cloud SQL each provide their own built-in scheduling options.

11 Backup Granularity Works Differently in Both Directions

SQL Server backup is database-level. BACKUP DATABASE backs up one database. To back up an entire SQL Server instance, each database must be backed up individually. There is no single command to back up all databases on an instance in one operation. The combination of full, differential, and transaction log backups provides point-in-time recovery to any moment within the log chain.

PostgreSQL backup works at two levels with different tools. Logical backup with pg_dump can target a single database, a specific schema, or specific tables, producing a portable SQL script or custom-format archive. Physical backup with pg_basebackup copies the entire PostgreSQL data directory, capturing all databases in the cluster at once. There is no native per-database physical backup in PostgreSQL equivalent to SQL Server’s BACKUP DATABASE. Point-in-time recovery in PostgreSQL is achieved through Write-Ahead Log (WAL) archiving combined with a base backup.

12 Case Sensitivity: Unquoted Identifiers Are Lowercased

SQL Server is case-insensitive by default with CI (Case Insensitive) collation. SELECT * FROM Customers and SELECT * FROM CUSTOMERS and SELECT * FROM customers all refer to the same table. Object names are stored and compared case-insensitively. This is the behavior SQL Server DBAs build habits around.

PostgreSQL behaves differently. According to official PostgreSQL documentation, unquoted identifiers are folded to lowercase. When an object is created with an unquoted name, PostgreSQL stores it in lowercase regardless of how it was typed. CREATE TABLE Customers creates a table named customers in PostgreSQL. SELECT * FROM customers and SELECT * FROM Customers and SELECT * FROM CUSTOMERS all work because they are all folded to lowercase.

The problem arises when quoted identifiers are used. If an object is created with a quoted mixed-case name like CREATE TABLE "CustomerOrders", PostgreSQL preserves the exact case including the capital letters. That table can only be referenced as "CustomerOrders" with the exact capitalization inside double quotes. SELECT * FROM CustomerOrders without quotes folds to customerorders and returns an error.

The Quick Reference: SQL Server vs PostgreSQL Side by Side

Concept	SQL Server	PostgreSQL
Reader-writer concurrency	Shared locks by default. RCSI opt-in per database.	MVCC always on. Readers never block writers.
Table storage default	Heap without a clustered index (should always add one).	Always a heap. No clustered index concept exists.
Physical row ordering	Clustered index maintains order continuously on all changes.	CLUSTER reorders once. Subsequent changes are unordered.
Dead row cleanup	Ghost cleanup task runs automatically. No DBA action needed.	Dead tuples accumulate until VACUUM runs. Must monitor.
Physical files	All objects share MDF/NDF files. Few large files per database.	Each table and index has its own file. Many small files.
Buffer cache default	Unlimited by default. Set max server memory to constrain.	128 MB default. Must set shared_buffers to ~25% of RAM.
effective_cache_size	No equivalent planner hint setting.	Planner hint only. Does not allocate memory.
Sort memory	Single memory grant per query execution.	work_mem per sort/hash operation. Multiplies with query complexity.
Security model	Logins (server level) mapped to Users (database level). Separated.	Roles at instance level. Serve as both users and groups.
Job scheduling	SQL Server Agent built in. No additional software needed.	No built-in scheduler. Requires pg_cron, pgAgent, or OS scheduler.
Per-database backup	BACKUP DATABASE backs up one database with full/diff/log chain.	pg_dump for logical. pg_basebackup for physical (whole cluster).
Case sensitivity	CI collation by default. Object names case-insensitive.	Unquoted identifiers folded to lowercase. Quoted identifiers exact-case.

References

• PostgreSQL Documentation: Introduction to MVCC (reading never blocks writing)
• PostgreSQL Documentation: CLUSTER (one-time operation, not maintained)
• PostgreSQL Documentation: VACUUM (dead tuples and reclamation)
• PostgreSQL Documentation: Resource Consumption (shared_buffers default 128 MB, work_mem, effective_cache_size)
• PostgreSQL Documentation: Database File Layout (per-table relation files)
• PostgreSQL Documentation: pg_dump
• PostgreSQL Documentation: pg_basebackup
• PostgreSQL Documentation: Identifiers and Key Words (unquoted identifiers folded to lowercase)
• Microsoft Learn: Heaps (Tables without Clustered Indexes)
• Microsoft Learn: SQL Server Transaction Locking and Row Versioning Guide
• SQLYARD: The PostgreSQL Migration Trap: Heaps, Forwarded Fetches, and the Fix
• SQLYARD: MigrateIQ Migration Tool