Paul SerbanSoftware Engineer

Using PostgreSQL to Replace DynamoDB: A Practical Migration GuideHow to model DynamoDB-style access patterns in PostgreSQL without losing the flexibility that made NoSQL appealing in the first place

Introduction

DynamoDB earns its popularity honestly. It scales without much operational thought, it charges you roughly for what you use, and AWS handles replication, partitioning, and failover behind the scenes. For teams building at genuine internet scale, with access patterns that were designed up front and rarely change, it is an excellent piece of infrastructure. The problem is that most teams are not building at that scale, and most access patterns are not fixed up front - they evolve as the product evolves, and DynamoDB's single-table design philosophy punishes exactly that kind of change.

This article is about a specific, increasingly common decision: replacing DynamoDB with PostgreSQL. Not because DynamoDB is bad, but because a relational database with a JSONB column can often deliver the flexibility teams actually need - ad-hoc queries, joins, secondary access patterns discovered after launch - while giving up very little of what made DynamoDB attractive in the first place. We will walk through the conceptual mapping between the two systems, look at real schema and query examples, and be honest about where PostgreSQL is a worse choice than DynamoDB, because pretending otherwise would not serve you well as an engineer making this call for your own system.

Context and Problem Overview

DynamoDB's data model forces a particular kind of discipline. Every table has a partition key, optionally a sort key, and a fixed set of access patterns that you are expected to enumerate before you write a line of application code. This is by design - DynamoDB achieves its predictable low-latency behavior precisely because it does not support arbitrary queries. If you did not plan for a query pattern when you designed your keys and indexes, you either denormalize more data into the item, add a Global Secondary Index, or you cannot run that query efficiently at all. Teams that get this right up front do very well. Teams that discover new requirements six months into production often find themselves duplicating data across items, writing scripts to backfill new GSIs, or reaching for DynamoDB Streams and Lambda just to keep derived views in sync.

Relational databases solve a different problem. PostgreSQL was never optimized for the same kind of horizontally-partitioned, latency-guaranteed access DynamoDB provides, but it was built from the ground up to support flexible, unplanned queries against normalized or semi-structured data. Modern PostgreSQL - specifically the JSONB type introduced in version 9.4 - closes a large part of the gap that used to make relational databases feel rigid compared to document stores. You get schema flexibility where you want it, and constraints, foreign keys, and indexes where you want structure.

The decision to move from DynamoDB to PostgreSQL, then, is really a decision about which set of constraints you would rather live with. DynamoDB constrains your queries but frees you from most operational scaling concerns. PostgreSQL frees your queries but requires you, or a managed service, to think about vacuuming, connection limits, index bloat, and - past a certain scale - sharding. Neither constraint disappears; it just moves to a different part of the system, and the right choice depends heavily on your team's actual growth trajectory rather than the trajectory you hope for.

Deep Technical Explanation: Mapping DynamoDB Concepts to PostgreSQL

The starting point for any migration is recognizing that DynamoDB's primary key structure maps almost directly onto a composite primary key in PostgreSQL. A DynamoDB table with a partition key of customer_id and a sort key of order_id becomes a table where (customer_id, order_id) is the primary key. The crucial difference is that PostgreSQL does not require you to know your query patterns in advance to make this efficient - a B-tree index on that composite key supports exact lookups, range scans, and prefix matching on order_id within a given customer_id, all without any extra configuration. Where DynamoDB requires you to choose the sort key up front because changing it means rebuilding the table, PostgreSQL lets you add indexes after the fact with CREATE INDEX CONCURRENTLY, without taking the table offline.

The second core mapping is around DynamoDB's schemaless attributes. Items in a DynamoDB table can carry arbitrary fields beyond the key schema, and this flexibility is a major reason teams choose it. PostgreSQL's JSONB column type, which stores JSON in a decomposed binary format rather than as text, gives you the same flexibility with better query support than most people expect. You can index into a JSONB column with a GIN index, query specific keys with the ->> and -> operators, and even build partial or expression indexes on specific paths inside the document. The practical pattern most teams settle on is hybrid: promote fields you filter, sort, or join on frequently into real typed columns, and leave genuinely variable or sparse attributes inside JSONB.

Global Secondary Indexes are the DynamoDB feature people worry about replacing, and the good news is that ordinary PostgreSQL indexes are both more powerful and less operationally expensive. A GSI in DynamoDB is effectively a second table that AWS maintains for you asynchronously, with its own provisioned throughput and its own eventual consistency characteristics. A B-tree index in PostgreSQL is maintained synchronously as part of the same transaction that writes the row, is always consistent with the table it indexes, and does not need separate capacity planning. You can also build indexes DynamoDB has no equivalent for - partial indexes that only cover rows matching a condition, expression indexes on computed values, or multi-column indexes that support sorting by more than one field at once.

Transactions are the area where the mapping actually favors PostgreSQL outright. DynamoDB Transactions exist, but they are limited to 100 items per transaction and carry additional cost and complexity. PostgreSQL gives you full ACID transactions across arbitrarily many rows and tables, with standard isolation levels, as a baseline feature of the database rather than a bolted-on capability. If your application logic depends on multi-row consistency - moving money between two accounts, decrementing inventory while creating an order - this is not a minor convenience; it removes an entire category of application-level compensation logic you would otherwise need to write by hand.

Implementation and Practical Examples

Consider a common e-commerce access pattern: fetching a customer's order history sorted by date, and separately looking up a single order by its ID regardless of which customer placed it. In DynamoDB this typically requires a base table keyed by customer_id and order_id#created_at, plus a GSI keyed by order_id alone. In PostgreSQL, the same requirement looks like this:

CREATE TABLE orders (
    order_id     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id  uuid NOT NULL REFERENCES customers(id),
    status       text NOT NULL DEFAULT 'pending',
    total_cents  integer NOT NULL,
    metadata     jsonb NOT NULL DEFAULT '{}',
    created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

CREATE INDEX idx_orders_metadata
    ON orders USING GIN (metadata);

The primary key already supports the "fetch a single order" pattern, and the composite index supports "fetch a customer's orders sorted by recency" without needing a second table or a separately provisioned index. No duplicated data, no eventual consistency between a base table and a GSI.

Application code that talks to this schema looks like ordinary parameterized SQL rather than a query-builder DSL. Here is a TypeScript example using the pg driver, structured the way you would write it in a production service with connection pooling already configured:

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});

interface Order {
  orderId: string;
  customerId: string;
  status: string;
  totalCents: number;
  metadata: Record<string, unknown>;
  createdAt: Date;
}

async function getRecentOrdersForCustomer(
  customerId: string,
  limit = 20
): Promise<Order[]> {
  const { rows } = await pool.query(
    `SELECT order_id, customer_id, status, total_cents, metadata, created_at
     FROM orders
     WHERE customer_id = $1
     ORDER BY created_at DESC
     LIMIT $2`,
    [customerId, limit]
  );

  return rows.map((r) => ({
    orderId: r.order_id,
    customerId: r.customer_id,
    status: r.status,
    totalCents: r.total_cents,
    metadata: r.metadata,
    createdAt: r.created_at,
  }));
}

Optimistic locking, which in DynamoDB requires a condition expression on a version attribute, translates directly into a WHERE clause backed by a version column and a normal UPDATE. The following Python example, using psycopg, shows the pattern for a service that needs to guard against concurrent writes to the same order:

import psycopg
from psycopg.rows import dict_row

def update_order_status(conn: psycopg.Connection, order_id: str, expected_version: int, new_status: str) -> bool:
    with conn.cursor(row_factory=dict_row) as cur:
        cur.execute(
            """
            UPDATE orders
            SET status = %s, version = version + 1
            WHERE order_id = %s AND version = %s
            RETURNING order_id
            """,
            (new_status, order_id, expected_version),
        )
        updated = cur.fetchone()
        conn.commit()
        return updated is not None

If the row's version does not match what the caller expected, the update affects zero rows, and the function returns False - the same semantics as a failed conditional write in DynamoDB, expressed as ordinary SQL rather than a proprietary expression language.

Trade-offs and Pitfalls

The honest limitation of this whole approach is horizontal write scaling. DynamoDB partitions your table automatically across as many nodes as your throughput requires, and it does this without you thinking about it at all. A single PostgreSQL primary, by contrast, scales vertically - you can get a surprisingly long way by adding CPU, memory, and faster storage, and pairing that with read replicas for read-heavy workloads, but you eventually hit a ceiling on write throughput that no amount of vertical scaling fixes. If your actual requirement is tens of thousands of sustained writes per second, plain PostgreSQL is not the right tool, and you should look at sharding extensions like Citus, or reconsider whether DynamoDB was the correct choice to move away from in the first place.

Connection handling is a subtler but very real pitfall. PostgreSQL's default connection model is comparatively expensive - each connection consumes a non-trivial amount of memory on the server, and most production deployments hit connection limits long before they hit CPU or query performance limits, especially in serverless or highly concurrent environments where every function invocation might open its own connection. This is exactly the kind of operational concern DynamoDB abstracts away entirely; there is no connection limit to think about with a managed HTTP-based API. Solving this in PostgreSQL means introducing PgBouncer or a managed pooler, which is a well-understood pattern but is still one more component you now own and must monitor.

A second, less obvious pitfall is vacuum and bloat management. PostgreSQL's MVCC model means updates and deletes leave behind dead tuples that must be cleaned up by the autovacuum process. Under heavy write and update workloads - the exact kind of workload people migrating off DynamoDB often have - autovacuum can fall behind, leading to table and index bloat that degrades query performance over time. DynamoDB has no equivalent concept exposed to the operator at all. This does not mean PostgreSQL cannot handle high write volumes; it means the operational burden of keeping it healthy is real and needs a team that understands pg_stat_user_tables, autovacuum tuning, and how to recognize bloat before it becomes a production incident.

Best Practices for the Migration

Start by classifying your existing DynamoDB access patterns rather than your existing DynamoDB schema. Every GSI you built exists because of a specific query, and each of those queries usually maps cleanly onto either a composite index or a straightforward join once you're in a relational model. Writing this list out before you touch a single line of DDL will save you from reflexively recreating a single-table design in PostgreSQL, which is almost always a mistake - single-table design exists to work around DynamoDB's query limitations, and those limitations do not exist in PostgreSQL, so carrying the pattern over just adds unnecessary complexity.

Keep your JSONB usage deliberate rather than habitual. It is tempting, especially early in a migration, to dump entire DynamoDB items into a single data jsonb column and call the migration done. This works, and it is sometimes the right first step, but it forfeits most of the benefit of moving to a relational database: constraints, typed columns, foreign keys, and efficient indexes all work better against real columns than against JSON paths. A good rule of thumb is to promote any field you filter on, sort by, or use in a foreign key relationship into a typed column, and reserve JSONB genuinely for attributes that vary by record type or that you do not yet have a firm schema for.

Plan for connection pooling and observability from day one rather than retrofitting them after an incident. Deploy PgBouncer (or your cloud provider's equivalent, such as RDS Proxy) in front of your database before you go to production, size your pool based on actual concurrent query counts rather than guesswork, and set up monitoring on pg_stat_activity, replication lag, and autovacuum activity. These are the operational surfaces that DynamoDB hides from you entirely, and treating them as first-class concerns rather than afterthoughts is what separates a smooth migration from a painful one.

Key Takeaways

  • Map DynamoDB partition and sort keys to a PostgreSQL composite primary key; this alone replicates most single-item and range-query access patterns without any extra design work.
  • Use JSONB deliberately, not as a dumping ground - promote frequently filtered or sorted fields into typed columns and reserve JSONB for genuinely variable attributes.
  • Replace each DynamoDB GSI with a purpose-built PostgreSQL index; you will likely end up with fewer, more powerful indexes than you had GSIs.
  • Deploy a connection pooler (PgBouncer or RDS Proxy) before launch, not after you hit a connection limit in production.
  • If your realistic write throughput exceeds what a well-tuned single primary can sustain, evaluate Citus or reconsider the migration rather than fighting vertical scaling limits indefinitely.

Conclusion

Replacing DynamoDB with PostgreSQL is not a downgrade or an upgrade in the abstract - it is a trade of one set of constraints for another, and the right call depends on whether your actual pain point is query flexibility or write-throughput scaling. Teams that outgrew DynamoDB's single-table design discipline, that need ad-hoc reporting queries, or that want real multi-row transactions tend to find PostgreSQL a substantial improvement in day-to-day developer experience. Teams that genuinely need unbounded horizontal write scaling with zero operational overhead will find that PostgreSQL, even well-tuned, asks more of them operationally than DynamoDB ever did.

The technical mapping between the two systems is more direct than it first appears: composite keys instead of partition and sort keys, JSONB instead of schemaless attributes, ordinary indexes instead of GSIs, and native transactions instead of a constrained transactions API. What matters more than the mapping itself is being clear-eyed about which of DynamoDB's guarantees you are giving up, and confirming - with real numbers about your actual write volume and query patterns, not projections about hypothetical future scale - that PostgreSQL's guarantees are the ones your system actually needs.

References