The Wonderful World Of Databases

A talk I gave at RuPy 2012 - discussing databases, their architecture and history, and how to make them happy.

1. ANDREW GODWIN // @andrewgodwin T H E WONDERFUL DATABASES - O F - W O R L D
2. ?
6. =
7. ! Databases are important
8. Journaling / WAL Transactions Schema changes JOINs
9. Journaling / WAL Database Disk
10. Journaling / WAL Database OS Disk
11. Journaling / WAL Database OS Disk Controller Disk Platter
12. Journaling / WAL Database OS Disk Controller Disk Platter RAID controller
13. Journaling / WAL Database OS Network card Switch Network card
14. Transactions Read Write time → Read Write Read Read t₁ t₂ t₃
15. Transactions Read Write time → Read Write Read Read t₁ t₂ t₃
16. Altering Schema Schema Data ?
17. Altering Schema Schema Data ? ?
18. Altering Schema Schema Data ?
19. JOINs
20. JOINs
21. Table A Table B
22. n²
23. n² Hash Joins Merge Joins Index scans
24. But wait! There's more!
25. Asynchronous Calls import psycopg2 import eventlet eventlet.monkey_patch(psycopg2=True) cur1 = psycopg2.connect().cursor() cur2 = psycopg2.connect().cursor() cur1.execute("BIG SELECT") cur2.execute("ANOTHER SELECT") results1 = cur1.fetchall() results2 = cur2.fetchall()
26. Document Stores • Easy to add data • You still need schema • Large updates can be slow
27. Distributed Databases Consistency Availability Partition Tolerance (pick any two)
28. Performance Tips
29. Don't use MySQL Really, don't.
30. Play to strengths Do search in a search engine. Don't put relational in MongoDB.
31. Use indexes! EXPLAIN is your friend.
32. # explain select * from aeblog_post where id = 50; QUERY PLAN --------------------------------------------------- Index Scan using aeblog_post_pkey on aeblog_post (cost=0.00..8.27 rows=1 width=1812) Index Cond: (id = 50)
33. # explain select * from aeblog_post where section = 'trip-notes'; QUERY PLAN --------------------------------------------------- Seq Scan on aeblog_post (cost=0.00..14.21 rows=1 width=1812) Filter: ((section)::text = 'trip-notes'::text)
34. Analytics. Everywhere. Slow queries. Number of queries.
35. Avoid giant IN queries Seriously. A JOIN is better.
36. Think. Learn.
37. Fin. Andrew Godwin @andrewgodwin http://aeracode.org Thanks.