Summary of different database engines and popular databases can be found here - https://docs.google.com/spreadsheets/d/1xVUd1tN-05R00kj18hc3NiIxDonKX48LMCYXfFEniqE/edit#gid=1136247025
For practising SQL queries online check - https://onecompiler.com/mysql
Indexes
- indexes reduce the query time to constant time
- tradeoff
- addition space used for storage
- in a service we used index storage was 3 times that of the actual data.
- latency during the writes
- if read to write ratio is high we don't need to worry about it
- addition space used for storage
- indexes are like hash tables
- "This means that indexes use a proportional amount of disk space and require I/O when being updated"
TODO
- I read recently that graph databases can be used in most scenarios. Need to read up more to validate this.
- #todo
- Read more about how indexes actually work in databases
- and the tradeoffs here
Tools
- Extension for @code -https://topshot.medium.com/dbizzy-56652b107819
Tips
- Whatever client you use see if it supports ER diagrams
Further Exploration
- Graph DB**
- Redash
- Optimisation of queries
- Use
EXPLAIN (format json)
- Paste the execution plan in https://tatiyants.com/pev/#/plans/new
- You get an out put on the lines of
- ![](
- https://i.imgur.com/GBgSNUZ.png)
- N+1 queries - https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89
- Use
- MVCC - https://chat.openai.com/c/d9f1e3a9-b80d-4baa-b3d3-867addd0fe9a
- conflict resolution strategy
- PostGres Primer
How to Improve Database Performance: The Ultimate Guide https://www.percona.com/blog/ultimate-guide-to-improving-database-performance/
1.
Other Sources
https://db-engines.com/en/system/Amazon+Aurora%3BMongoDB%3BMySQL%3BPostgreSQL%3BRedis
Unstructured learning
Referenced in:
All notes