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:
Yeah, it’s amazing how good SQLite is nowadays. I find I use it for a lot of local apps I make for myself cause it’s just good enough.
SQLite is pretty incredible. I think it currently has even faster read performance than postgres.
The only reason to not use it, is if you have a write-heavy DB, or you need some of postgres’s extra features.
Or if you want too host it on NFS backed storage
That’s my take as well, PostgreSQL is amazing, but in simpler DB schemes SQLite is undefeatable.
there have been some really neat projects using it too like this one https://github.com/biokoda/actordb
we have more performance issues developing gotosocial with postgres than with sqlite. if we supported multinode deployments or horizontal scaling such that sqlite’s concurrency got harder to reason-about I’m sure postgres would shine, but for a single process sqlite has been generally much faster and it hasn’t required anywhere near as much prompting to get the query planner to behave as required.
i also think when people assume sqlite is better for simpler applications, they think smaller. but no, for the same 25+GB databases sqlite has mopped the floor with performance.
single-process multi-threaded sqlite is fantastic.