Databases

Relational

  • sqlite
  • postgres
  • MySQL
    • mariab db fork
      • galera multi master clustering
        • https://jepsen.io/analyses/mariadb-galera-cluster-12.1.2 shows faults with even single node
          • "exhibits Stale Read, Lost Update, and other forms of G-single in healthy clusters, when no faults occur. Transactions may (apparently) modify data in the interval between a single transaction’s reads and writes; read-modify-write patterns, like those used in many ORMs, are likely unsafe."
    • innodb storage engine prefetches and caches itself with default 16k record size
      • https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html
      • dzfsprefetchdisable=1, logbias=throughput, primarycache=metadata, atime=off, xattr=sa, recordsize=16k
      • innodblogwriteaheadsize=16384, innodbdoublewrite=0, innodbchecksumalgorithm=none, innodbflushneighbors=0, innodbusenativeaio=0, innodbuseatomicwrites=0
      • increase writes by disabling disk flushing with innodbflushlogattrxcommit=0 and syncbinlog=0. ZFS disable all explicit disk flushing to any one file system by setting sync=disabled so it flushes every ~5sec
    • https://enhancedformysql.github.io/The-Art-of-Problem-Solving-in-Software-Engineering_How-to-Make-MySQL-Better/
    • TiDB
      • somewhat compatible with MySQL
      • TiKV for key value storage
      • distributed db

olap

  • duckdb
    • column store for faster analytics
    • sqlite/postgres scanner support
    • no concurrent readers/writers yet
      • DuckLake adds multiple with postgres as catalog or sqlite as local
    • duckdb-wasm shell 1.5.2 does not support fts extension test or snippet function
    • lindel extension for morton/hilbert codes (z-ordering for physical column indices)
  • Clickhouse
  • https://transactional.blog/notes-on/disaggregated-oltp

notes

  • BTree based DB's are more cache friendly as nodes can have more than 2 children (abseil uses 62 children) unlike Binary Trees
    • binary tree's (red-black like C++ STL) are better with larger sized nodes (moving pointers) than smaller (moving values)
  • vitess (mysql compat) is used by planetscale and switched from AST interpreter(like postrgres) to bytecodevm(sqlite)