Introduction The setup Laying the foundation The foundation: schemas and user roles for modularity Domains Accounts, managed and external Transfers, constrained by a state machine and temporal periods Transfer state history Account auditing Transactions, the immutable events On maintaining business rules via meaningful constraints The transfer state machine Transactions must fall within the transfer period Pending transactions require a pending transfer No future transactions when closing a transfer On capacity planning Working set estimation On write throughput Enabling HOT Updates for Transfers Making sure there are no Unused indexes OLTP Listing The history of a transfer OLAP Balance ledger Incremental maintenance via triggers On serializable isolation On decoupling Benchmarking the startup scenario Seed data Write script: full transfer lifecycle Read script: activity stream and balance Running the benchmark Results Conclusion Appendix A: Full code suite. Introduction There is a deep cultural reflex in modern engineering: whenever a problem appears, reach for a packaged solution instead of thinking from first principles. The result is architectural cargo culting and lots of missed opportunities. Some intentionally absurd-but-familiar examples:
I swear Postgres always has new features whenever I look at it again.
I was building a system for long running durable job queues with multiple workers and of course Postgres has a solution for that. You can do row-level locking in a transaction to “reserve” jobs for the duration of that transaction, and you can use
SKIP LOCKEDto skip over reserved jobs so workers don’t block each other. There’s also LISTEN and NOTIFY if you want pub/sub to track when jobs complete, for example.Postgres has scope creep?
The row-level locking and
SKIP LOCKEDseem like appropriate features for a SQL-based DBMS. I find it odd that it’s not more common, actually. Even MSSQL doesn’t make any promises about locking just a row and might decide to lock a whole page instead, from what I read anyway.NOTIFY/LISTEN might be scope creep though. I’m not really sure what led to it being implemented.