Replicating PostgreSQL to Redshift with AWS DMS: Production Lessons
Moving a busy transactional PostgreSQL database into Redshift for analytics is less about starting a replication task and more about keeping it trustworthy under load. At more than 800 changes per second and over 70 million rows per day in the captured example, small stalls, skipped rows and type mismatches quickly become visible to analysts. This guide explains how DMS moves data, where it tends to struggle, and the operational checks that make it viable in production.
What DMS is doing under the hood
A DMS source is the system data is read from, such as PostgreSQL, MySQL, DocumentDB or an S3 bucket. A DMS target is where replicated data lands, which can also be a relational database, document database or S3 bucket. The replication instance itself runs on EC2. Setup can be done through the AWS console wizard or as JSON for version control, and configuration varies with whether systems are on-premises, on IaaS or on PaaS, so the source- and target-specific AWS pages matter more than a generic checklist.
Capture, batch, intermediate table, apply
For PostgreSQL to Redshift, capture begins at the PostgreSQL replication slot. Changes are accumulated until batch thresholds based on BatchApplyTimeoutMin, BatchApplyTimeoutMax and BatchApplyMemoryLimit are reached. DMS then writes a change file and creates an intermediate table named with the awsdms_changes prefix, for example awsdms_changes31fdb6d65ekz99b4. That table holds inserts, deletes and bulk updates. DMS sweeps it in sequence ranges, applying each range to its destination table, then returns to capture. Understanding that intermediate table is key, because most failure modes show up as a mismatch between its row count and the rows actually changed in Redshift.
Strengths and limits to plan for
Advantages that matter in practice: data can stay inside the VPC without a third party processing it, setup is comparatively quick, you pay for the EC2 replication instance rather than a separate product licence, documentation and community examples are extensive, and DDL changes propagate automatically so target schemas do not need manual migration for every source change. CloudWatch metrics are also available, and target latency alarms are essential rather than optional. Drawbacks are equally concrete: DMS is a broad general-purpose tool with many source and target combinations, occasional single-row skips on high-volume tables, possible loss after an instance reboot, a memory leak that eventually fails tasks, task status that may not reflect a silent failure, and boolean columns arriving as varchar(14).
Production checklist and failure triage
Use this as a pre-launch and weekly operations checklist: alarm on target latency and on task state changes; verify task state independently because a failed task may not be marked failed; track DMS update counts in Redshift, since DMS comments its update statements with its version; watch replication instance memory and restart tasks before exhaustion; pause tasks around Redshift maintenance windows; reconcile row identifiers daily; and test boolean handling in queries before analysts depend on it. A useful decision rule: if latency spikes while update counts look abnormal, suspect one-by-one mode first; if rows are missing after a reboot or maintenance window, suspect an interrupted update; if tasks fail roughly twice a week, suspect memory growth.
Fixing the one-by-one switch
DMS estimates affected rows from the intermediate table sequence range. If a bulk update touches fewer rows than that range, it switches to one-by-one mode, applies updates individually, then returns to bulk mode. On a table receiving many changes per second, that detour can make latency climb sharply. One mitigation pattern is a checker running every three minutes that counts DMS-related updates. When the count exceeds a fixed threshold chosen to avoid false positives, a fixer finds the latest rolled-back update in Redshift internal views, parses the intermediate table name, target table and sequence range, left-joins the intermediate table to the target to locate missing identifiers, fetches the full row from PostgreSQL, translates types such as arrays and JSON into Redshift-compatible forms, inserts the row, then stops and resumes the task and waits fifteen minutes before checking again. The three parts are the checker, the fixer and the type translator.
Maintenance windows and memory pressure
Redshift maintenance can reboot the cluster mid-update, leaving a DMS task silently stopped rather than marked failed. A scheduling tool that stops replication tasks five minutes before a weekly window and resumes them thirty-five minutes after the window starts gives buffer for the reboot, which typically begins two to six minutes after the cluster is marked for maintenance and takes one to three minutes. In the captured example, an 11:00 UTC window led to stopping at 10:55 and resuming at 11:30. Separately, DMS instances were reported to run out of memory about twice a week. Checking available memory and stopping and resuming tasks when it falls below 1.5 GB restored memory close to the instance maximum. Rebooting the instance instead was avoided because it caused data loss.
Reconciling gaps and choosing compression encoding
Missing rows were linked to reboots during updates, task failures including memory exhaustion, and occasional unexplained skips. When replicated tables have monotonically increasing primary keys, a daily job can find gaps reliably: get the minimum and maximum primary key in Redshift, export source identifiers for the day to a file, load them into Redshift, left-join to find absent identifiers, fetch complete rows from PostgreSQL, generate inserts with PostgreSQL-to-Redshift type conversion, and insert them. Running this during low-traffic hours keeps analytics tables complete without waiting for DMS to self-heal. For table design, do not let DMS choose compression encoding by default. Instead, run a full-load task against a disposable development Redshift cluster, let large tables fill for an hour or more, run ANALYZE COMPRESSION there, generate the creation script from the result, and apply it to production before starting replication. ANALYZE COMPRESSION takes an exclusive lock and should only run when the table is idle, never as a routine production operation.
Bottom line
After a year in production, DMS was judged promising but not fully mature. It succeeded where earlier attempts had not, but only with substantial internal tooling for monitoring, repair and reconciliation. That trade-off is the central decision: choose DMS when VPC containment, automatic DDL propagation and low product cost outweigh the engineering cost of alarms, checkers and gap-filling jobs. If that operational work is not staffed, latency spikes and silent gaps are likely to reach analysts first.
| Symptom | Likely cause | First check |
|---|---|---|
| Target latency spikes | One-by-one mode after a short bulk update | DMS update counts and intermediate table ranges |
| Rows missing after reboot or maintenance | Interrupted update or silent task failure | Task state, maintenance timing and daily identifier reconciliation |
| Task fails about twice a week | Replication instance memory growth | Available memory; stop and resume tasks below 1.5 GB in the captured setup |
| Booleans behave as strings | Intermediate varchar mapping to varchar(14) | Column types in Redshift and downstream casts |
Triage guide derived from the captured production experience; thresholds and schedules are examples, not universal defaults.
Frequently asked questions
How does a PostgreSQL to Redshift DMS task apply changes?
DMS reads changes from the PostgreSQL replication slot, groups them into batches using settings such as BatchApplyTimeoutMin, BatchApplyTimeoutMax and BatchApplyMemoryLimit, writes them to an intermediate awsdms_changes table, then applies inserts, deletes and bulk updates to the target tables before repeating the cycle.
What is the one-by-one mode switch?
When a bulk update affects fewer rows than the intermediate table indicates, DMS falls back to applying updates individually. That slowdown can raise target latency sharply on high-volume tables, so latency alarms and a checker for unusual update counts are recommended.
Why do PostgreSQL booleans become strings in Redshift?
Booleans arrive in Redshift as varchar(14) because the intermediate table stores columns as varchar. Forcing a boolean target was reported to fail and trigger one-by-one mode, so downstream queries should treat those values as strings unless a tested conversion is in place.
How can missing rows be detected and repaired?
Missing rows were reported after cluster reboots during updates, task failures such as memory exhaustion, and occasional unexplained skips. A daily reconciliation using monotonically increasing primary keys, comparing source identifiers against the target with a left join, and inserting any gaps can close those holes.