Use PostgreSQL for transactional data instead of DynamoDB

Context

We need a primary data store for the platform's core transactional data: user accounts, payment records, and job state. The team has existing DynamoDB infrastructure for other workloads, which created pressure to default to it here too.

DynamoDB is a reasonable choice when access patterns are known upfront, data is denormalized, and strong consistency isn't required everywhere. For financial records, none of those conditions hold cleanly.

Decision

Use PostgreSQL (via RDS) for all transactional data. DynamoDB remains appropriate for high-throughput lookup tables and caching layers where eventual consistency is acceptable.

Reasoning

Transactions. Payment records require multi-table atomicity. DynamoDB transactions exist but are limited to 25 items and add latency. PostgreSQL transactions are first-class.

Query flexibility. Financial reporting requires ad-hoc aggregations across dimensions we can't fully predict at schema design time. A relational model lets us write the query when we need it. With DynamoDB, every new access pattern requires a new index designed upfront.

Referential integrity. Foreign key constraints at the database level catch bugs that application-layer validation misses. This matters more in a payment domain than most.

Operational familiarity. The team has more depth in PostgreSQL. Choosing a well-understood tool for the most critical data store reduces incident risk.

Consequences

• RDS requires more operational care than DynamoDB (backups, failover, connection pooling). We mitigate this with managed RDS and PgBouncer.
• Read scaling requires explicit read replicas; DynamoDB scales reads automatically. Acceptable at current volume.
• DynamoDB remains in use for non-transactional workloads: session tokens, rate limit counters, feature flags.

Status

Accepted. In production since May 2026.