gokulnkreading listnotes

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

  1. indexes reduce the query time to constant time
  2. tradeoff
    1. addition space used for storage
      1. in a service we used index storage was 3 times that of the actual data.
    2. latency during the writes
      1. if read to write ratio is high we don't need to worry about it
  3. indexes are like hash tables
  4. "This means that indexes use a proportional amount of disk space and require I/O when being updated"

TODO

  1. I read recently that graph databases can be used in most scenarios. Need to read up more to validate this.
    1. #todo
  2. Read more about how indexes actually work in databases
    1. and the tradeoffs here

Tools

  1. Extension for @code -https://topshot.medium.com/dbizzy-56652b107819

Tips

  1. Whatever client you use see if it supports ER diagrams

Further Exploration

  1. Graph DB**
    1. Graphql
    2. Sparql
  2. Redash
  3. Optimisation of queries
    1. Use EXPLAIN (format json)
    2. Paste the execution plan in https://tatiyants.com/pev/#/plans/new
    3. You get an out put on the lines of
    4. ![](
    5. https://i.imgur.com/GBgSNUZ.png)
    6. N+1 queries - https://medium.com/doctolib/understanding-and-fixing-n-1-query-30623109fe89
  4. MVCC - https://chat.openai.com/c/d9f1e3a9-b80d-4baa-b3d3-867addd0fe9a
    1. conflict resolution strategy
  5. PostGres Primer
    1. https://github.com/vishesh92/pg-primer?lphiltid=621758b00a002303fc878a4e by Vishesh from Blinkit

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

  1. https://gokulnk.com/curious-about/database

Referenced in:

All notes
gokulnkreadinglistnotes