Postgresql

Series links: Part 1/5 – Introduction Part 2/5 – Database Planning (you are here) Part 3/5 – AWS Setup Part 4/5 – Backend APIs Part 5/5 – Frontend What A minimal PostgreSQL structure with just two tables: one for the transaction entry and one for the attached documents (bills/receipts). Why Keep it simple so you can start quickly. Easy to query, filter by date/text, and export. How Tables at a glance accounting_entry: one row per transaction (expense or income). ...

What Active-Active means two or more PostgreSQL databases can accept writes and stay in sync in near real-time. Unlike standard streaming replication (primary → replicas), this setup allows bi-directional writes. We’ll use AWS DMS to achieve this: Full Load: copy existing schema + data Change Data Capture (CDC): replicate ongoing changes from WAL logs Databases can be RDS, EC2 PostgreSQL, or on-premises. Latency is usually seconds. Why Multi-region, hybrid infrastructure, disaster recovery True bi-directional sync is complex; DMS simplifies it Avoid downtime and manual syncing Most failures happen during setup, not concept How 1. Decide DMS Deployment Provisioned: recommended for CDC. Runs 24/7, predictable performance. Serverless: flexible scaling, but dynamic cost and unsuitable for constant CDC. Rule: Always use Provisioned for bi-directional replication. ...