![Over-fetch
clustering = Clustering.query.get(33)
depth2cid = defaultdict(list)
for cl in clustering.clusters:
depth2cid[cl.level].append(cl.id)](https://image.slidesharecdn.com/how-to-choose-a-database-170409143011/85/How-to-choose-a-database-18-320.jpg)
![Over-fetch
clustering = Clustering.query.get(33)
depth2cid = defaultdict(list)
for cl in clustering.clusters:
depth2cid[cl.level].append(cl.id)
11.4 Gb RAM
50 seconds](https://image.slidesharecdn.com/how-to-choose-a-database-170409143011/85/How-to-choose-a-database-19-320.jpg)
![Proper-fetch
query = (
db.session
.query(Cluster.id, Cluster.level)
.filter(Cluster.clustering_id == 33))
depth2cid = defaultdict(list)
for cid, level in query:
depth2cid[level].append(cid)
54 Mb RAM
1 second](https://image.slidesharecdn.com/how-to-choose-a-database-170409143011/85/How-to-choose-a-database-20-320.jpg)
![Properer-fetch
7 Mb RAM
1.2 seconds
query = (
db.session
.query(Cluster.id, Cluster.level)
.filter(Cluster.clustering_id == 33)
.yield_per(1000))
depth2cid = defaultdict(list)
for cid, level in query:
depth2cid[level].append(cid)](https://image.slidesharecdn.com/how-to-choose-a-database-170409143011/85/How-to-choose-a-database-21-320.jpg)
How to choose a database
- 1. How to choose a database Vsevolod Solovyov
- 2. Why bother It's the most fundamental thing about the project Even programming languages are switched more often With wrong choice you'll suffer. But why?
- 3. Different cases I certainly know what I need I'm not sure, something will do This tech is cool! Serious business: risk/bene鍖t management Small project: ?? Pet project: fun/learning management
- 4. I know what I need Huge time series DB Cross-datacenter replication Petabytes of data ... This talk is not for you
- 5. This tech is COOL Pet projects Experiments Beware otherwise
- 6. Consider this Data correctness (ACID, enforced schema) Easy data modeling Operational complexity Migrations Scaling Project use cases no one knows them yet!
- 7. Data correctness Silently losing data is not much fun Schema-less is a lie Heterogeneous data is hard to analyze, change, display Especially true in data-heavy projects
- 8. Data modeling In document-oriented DB (e.g. MongoDB) we need to specially craft "tables" according to anticipated queries. And re-craft them when queries change! Much easier in RDBMS, just dump it in adequate tables and slap some indexes Datomic is best here: Entity-Attribute-Value
- 9. Migrations Often overlooked part Keep them in repository! Transactional DDL Developing migrations in REPL Downgrade migrations are useless
- 10. Migration tools Native migrations (SQL, CQL, Datalog, etc) are best Don't do auto-migrations ever Don't use tools that give you auto- migrations Use something like nomad, migrate
- 11. Effort Migration complexity ORM migration tools Native (SQL, etc) Tools learning curve
- 12. Complex migrations Create new column/table/database Read from the old place and write to both Migrate all old data to the new place Read from both places and compare Clean up old code and data
- 13. Performance Data model matters here
- 15. Low-level details Pages Rows, columns, column families MVCC TOAST Index types: B-Tree, hash, GIN, GiST, BRIN Sharding hash/range, sharding key
- 16. Know your tools! Log slow queries (pg_stat, slowlog, etc) EXPLAIN EXPLAIN (ANALYZE,VERBOSE, BUFFERS) htop, iotop, perf, etc
- 17. ORMs considered harmful They provoke massive data over-fetch Easy-to-miss 1+N queries Hard to refactor and move parts of data to other DBs Very leaky abstraction
- 18. Over-fetch clustering = Clustering.query.get(33) depth2cid = defaultdict(list) for cl in clustering.clusters: depth2cid[cl.level].append(cl.id)
- 19. Over-fetch clustering = Clustering.query.get(33) depth2cid = defaultdict(list) for cl in clustering.clusters: depth2cid[cl.level].append(cl.id) 11.4 Gb RAM 50 seconds
- 20. Proper-fetch query = ( db.session .query(Cluster.id, Cluster.level) .filter(Cluster.clustering_id == 33)) depth2cid = defaultdict(list) for cid, level in query: depth2cid[level].append(cid) 54 Mb RAM 1 second
- 21. Properer-fetch 7 Mb RAM 1.2 seconds query = ( db.session .query(Cluster.id, Cluster.level) .filter(Cluster.clustering_id == 33) .yield_per(1000)) depth2cid = defaultdict(list) for cid, level in query: depth2cid[level].append(cid)
- 22. Scale
- 23. Distributed FUD http://jepsen.io/ CORDS: Redundancy does not imply fault tolerance - the morning paper Do you need it really? RAM is plentiful
- 25. Scale
- 27. Scale
- 28. Well...
- 29. By that time you will know what you need
- 30. Horizontally scalable Citus VoltDB Cassandra CockroachDB ...
- 31. Pull out data bit-by-bit
- 32. A
- 33. AAvailability
- 34. Availability Services mostly die from other problems Untested "available" DB can be a problem Properly available system (CAP-available) is a pain and resource sink
- 35. NoDB Images Machine learning models ... File system, S3, B2, etc
- 36. At Cap'n Obvious Experiment at home Don't bring new DB in a big project just because it's interesting Not sure? Postgres to the rescue