Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 5 · Lesson 30 · 4 min read

After the Migration: PostgreSQL Next Steps

On this page
  1. SQL Server to PostgreSQL cheat sheet
  2. Post-migration checks for the first weeks
  3. What to learn next
  4. Check yourself

SQL Server to PostgreSQL cheat sheet

Keep this open for your first months of PostgreSQL work. Each row points to the day that covers it.

You know (SQL Server)Use in PostgreSQLDay
InstanceCluster1
Several related databasesSchemas in one database1, 6
TOP (n)LIMIT n, or FETCH FIRST n ROWS WITH TIES4
+ for stringsThe double-pipe operator, or concat()4
ISNULL(a, b), IIF(c, a, b)coalesce(a, b), CASE WHEN c THEN a ELSE b END4
GETDATE()now(), which is fixed per transaction, or clock_timestamp()4
IDENTITY(1,1), SCOPE_IDENTITY()GENERATED ALWAYS AS IDENTITY, RETURNING4
OUTPUT inserted.*RETURNING *4
CROSS APPLY, OUTER APPLYCROSS JOIN LATERAL, LEFT JOIN LATERAL ... ON true4
LEN, CHARINDEX, TRY_CASTlength, strpos, pg_input_is_valid4
Case-insensitive namesUnquoted names fold to lowercase. Use snake_case.4
BIT, MONEY, UNIQUEIDENTIFIERboolean, numeric(19,4), uuid5
DATETIME2, DATETIMEOFFSETtimestamp, or timestamptz for real moments5
JSON in NVARCHAR(MAX)jsonb with GIN indexes5
PERSISTED computed columnGENERATED ALWAYS AS (...) STORED. Virtual is the default in 18.6
WITH NOCHECK constraintNOT VALID, then VALIDATE CONSTRAINT6
#temp tableCREATE TEMP TABLE8
sp_executesqlEXECUTE format(...) USING ...8
TRY ... CATCH, THROWBEGIN ... EXCEPTION ... END, RAISE EXCEPTION8
Procedure that returns rowsFunction RETURNS TABLE9
Procedure that commits in batchesCREATE PROCEDURE, run with CALL9
Trigger using inserted / deletedStatement trigger with transition tables, or a row trigger with NEW / OLD10
Indexed viewMaterialized view with a scheduled REFRESH11
MERGE upsertINSERT ... ON CONFLICT. MERGE also exists since PostgreSQL 15.12
NOLOCK, READ_COMMITTED_SNAPSHOTNot needed. MVCC is always on.12
Error 1205 deadlock retriesRetry on SQLSTATE 40P01, and 40001 for serialization failures12
Actual execution planEXPLAIN ANALYZE, with buffers included in 1815
Clustered indexNone. Heap tables plus indexes, INCLUDE, partial indexes.16
Statistics auto-updateAutovacuum's ANALYZE, plus ANALYZE after loads17, 18
Ghost cleanup, version storeVACUUM and autovacuum18
Partition function, SWITCHPARTITION BY, DETACH and DROP19
Query Storepg_stat_statements20
Login, user, db_datareaderRoles, group roles, default privileges22
Full, differential and log backupsBase backups, incrementals, WAL archiving, PITR23
Always On AGStreaming replication plus Patroni24, 25
Transactional replicationLogical replication24
SQL Agent jobspg_cron, or an external scheduler26
Extended Events, DMVsServer log settings, pg_stat_* views27

Post-migration checks for the first weeks

The migration isn't finished at cutover. Most surprises surface in the first weeks, and the same handful of checks catches them:

  • Daily: look at the top of pg_stat_statements by total time. New entries are queries that behave differently on PostgreSQL. Fix them with Days 15 to 17.
  • Daily: check n_dead_tup and last_autovacuum on the busiest tables. Lower the scale factor on any table where autovacuum falls behind (Day 18).
  • Daily: confirm backups and WAL archiving succeeded, and that replicas are in sync (Days 23 and 24).
  • Weekly: scan the log for slow statements and lock waits (Day 27).
  • Weekly: look for idle-in-transaction sessions coming from application code that holds transactions open (Day 12).
  • After a month: review indexes with idx_scan = 0, and drop the ones carried over from SQL Server that nothing uses (Day 27).
  • Before the first incident: run a failover drill and a point-in-time restore drill, and time both (Days 23 and 25).

What to learn next

Pick one direction that fits your role. Doing one well beats skimming four.

Go deeper on PL/pgSQL. Read the PL/pgSQL chapter of the official documentation from start to end. Then take your most complex migrated procedure and add proper exception handling, dynamic SQL with EXECUTE ... USING (which avoids injection), and tests.

Learn your managed platform. If you run on Amazon RDS or Aurora, Azure Database for PostgreSQL, or Google Cloud SQL, learn its parameter groups, extension allow-lists, backup and point-in-time restore, failover behavior and monitoring tools. The concepts are the ones from Week 4. The controls are the platform's.

Read plans fluently. Take ten slow queries from pg_stat_statements and explain each plan to a colleague. Nothing builds PostgreSQL intuition faster than reading real plans.

Join the community. The official documentation is unusually good and should be your first stop. The pgsql-general mailing list, PostgreSQL user groups and PGConf events are welcoming to people coming from other databases. Contributing documentation fixes or clear bug reports is a good way in, and you'll learn a lot from the replies.

A major PostgreSQL version arrives every year. PostgreSQL 19 is planned for November 19, 2026, and its release notes include REPACK (CONCURRENTLY), a built-in way to rebuild a bloated table without blocking writes. Read each release's notes. Several features covered in this course, such as virtual generated columns, uuidv7() and default buffer output in EXPLAIN, arrived in the most recent release.

Check yourself

Day 15 to read the plan with EXPLAIN ANALYZE, Day 17 for common causes such as stale statistics, functions on indexed columns and case-insensitive comparisons, and Day 16 if an index is missing or the wrong type.

Backup and WAL archiving success, because without them you can't recover. Replication lag and inactive slots, because they cause failover data loss or full disks. Autovacuum progress and transaction ID age, because falling behind leads to bloat and, at worst, stopped writes.

Watching closely: error rates and slow queries from pg_stat_statements, connection usage, replication lag, autovacuum activity on the busiest tables, and backups completing. Keep the SQL Server source read-only and available for comparisons until you're confident.

Opening a connection per request without pooling, relying on case-insensitive comparisons, using NOT IN with nullable columns, ignoring vacuum, and expecting NOLOCK or hints to tune queries. Each one has a PostgreSQL alternative covered in this course.

Deepen what you'll use daily: query tuning with EXPLAIN, replication and Patroni if you run your own servers, or your managed service's documentation if you don't. The PostgreSQL 30-day course on this site covers the core database topics in more depth.