After the Migration: PostgreSQL Next Steps
On this page
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 PostgreSQL | Day |
|---|---|---|
| Instance | Cluster | 1 |
| Several related databases | Schemas in one database | 1, 6 |
TOP (n) | LIMIT n, or FETCH FIRST n ROWS WITH TIES | 4 |
+ for strings | The double-pipe operator, or concat() | 4 |
ISNULL(a, b), IIF(c, a, b) | coalesce(a, b), CASE WHEN c THEN a ELSE b END | 4 |
GETDATE() | now(), which is fixed per transaction, or clock_timestamp() | 4 |
IDENTITY(1,1), SCOPE_IDENTITY() | GENERATED ALWAYS AS IDENTITY, RETURNING | 4 |
OUTPUT inserted.* | RETURNING * | 4 |
CROSS APPLY, OUTER APPLY | CROSS JOIN LATERAL, LEFT JOIN LATERAL ... ON true | 4 |
LEN, CHARINDEX, TRY_CAST | length, strpos, pg_input_is_valid | 4 |
| Case-insensitive names | Unquoted names fold to lowercase. Use snake_case. | 4 |
BIT, MONEY, UNIQUEIDENTIFIER | boolean, numeric(19,4), uuid | 5 |
DATETIME2, DATETIMEOFFSET | timestamp, or timestamptz for real moments | 5 |
JSON in NVARCHAR(MAX) | jsonb with GIN indexes | 5 |
PERSISTED computed column | GENERATED ALWAYS AS (...) STORED. Virtual is the default in 18. | 6 |
WITH NOCHECK constraint | NOT VALID, then VALIDATE CONSTRAINT | 6 |
#temp table | CREATE TEMP TABLE | 8 |
sp_executesql | EXECUTE format(...) USING ... | 8 |
TRY ... CATCH, THROW | BEGIN ... EXCEPTION ... END, RAISE EXCEPTION | 8 |
| Procedure that returns rows | Function RETURNS TABLE | 9 |
| Procedure that commits in batches | CREATE PROCEDURE, run with CALL | 9 |
Trigger using inserted / deleted | Statement trigger with transition tables, or a row trigger with NEW / OLD | 10 |
| Indexed view | Materialized view with a scheduled REFRESH | 11 |
MERGE upsert | INSERT ... ON CONFLICT. MERGE also exists since PostgreSQL 15. | 12 |
NOLOCK, READ_COMMITTED_SNAPSHOT | Not needed. MVCC is always on. | 12 |
| Error 1205 deadlock retries | Retry on SQLSTATE 40P01, and 40001 for serialization failures | 12 |
| Actual execution plan | EXPLAIN ANALYZE, with buffers included in 18 | 15 |
| Clustered index | None. Heap tables plus indexes, INCLUDE, partial indexes. | 16 |
| Statistics auto-update | Autovacuum's ANALYZE, plus ANALYZE after loads | 17, 18 |
| Ghost cleanup, version store | VACUUM and autovacuum | 18 |
| Partition function, SWITCH | PARTITION BY, DETACH and DROP | 19 |
| Query Store | pg_stat_statements | 20 |
| Login, user, db_datareader | Roles, group roles, default privileges | 22 |
| Full, differential and log backups | Base backups, incrementals, WAL archiving, PITR | 23 |
| Always On AG | Streaming replication plus Patroni | 24, 25 |
| Transactional replication | Logical replication | 24 |
| SQL Agent jobs | pg_cron, or an external scheduler | 26 |
| Extended Events, DMVs | Server log settings, pg_stat_* views | 27 |
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_statementsby total time. New entries are queries that behave differently on PostgreSQL. Fix them with Days 15 to 17. - Daily: check
n_dead_tupandlast_autovacuumon 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.