Skip to main content

Database

Store and query data.

Resources​

  • Database Cryptography Fur the Rest of Us | Soatok
    • A guide to the difficulty and possible solution for database encryption
    • Confused deputies, canonicalization attacks, multi-tenancy, and the additional complexity in NoSQL databases
    • Searchable encryption: order-preserving or order-revealing encryption, deterministic encryption, homomorphic encryption, searchable symmetric encryption, HMAC
  • How Figma Scaled to Multiple Databases
    • Tactical fixes: upgrade RDS, read replicas, new DB for new use cases, add PgBouncer
    • Vertical partition: Move groups of tables onto different databases
    • A detailed description of how the migration was done to limit the availability impact to <1 minute
    • PgBouncer to redirect traffic. Logical replication to copy tables. LSNs to verify synchronization.
  • Is ORM still an 'anti-pattern'? | GitHub @getlago
    • The paradigm mismatch between ORM data representation and relational data model, directed pointers vs. undirected graphs
    • Efficiency: The real issue is ORMs encourage poor practices. Data loaders can handle N+1 problems in ORMs
    • Visibility problem: Translating SQL error to ORM error confuses developers
  • Explaining the Postgres Meme
    • There are numerous concepts in the iceberg meme of database
    • More like explaining database concepts using Postgres as an example
  • Inside the New Query Engine of MongoDB | Nikita Lapkov
    • A writeup of the rewrite of the query execution engine in MongoDB version 7.0
    • 2 major reasons for rewrite and the idea of a Slot Based Engine
    • Architecture, data flow and compiler for the slot based engine
  • How to create a Streaming SQL Engine | Episo
    • Streaming SQL Engine: keep queries' results up to date without recalculating
    • Nodes processing messages in a format of (key: modification), can perform filtering, join, group by, etc.
    • Indexes and caching can optimize up to a point that they can't and streaming SQL engine just solves it
  • My Notes on GitLab's Postgres Schema Design | Shekhar Gulati (HN)
    • GitLab uses either serial or bigserial for primary keys, no UUID
    • text type with CHECK constraint to define length constraints
    • Use foreign key constraints except for immutable tables like logs and events
    • Partitioning on big tables to improve performance, e.g. PARTITION BY RANGE for time-series data
    • Trigram text search with pg_trm for faster LIKE search
    • updated_at only for tables that can be modified
    • Enums are smallint rather than character varying
  • Why SQLite Uses Bytecode
    • Bytecode implementation vs tree-of-objects implementation of the prepared statement
    • Bytecode: smaller, faster, easier to understand & debug, can be run incrementally
    • Tree-of-objects: query planning can be tweaked at runtime, dynamically self-tuning, and easy to parallelize

Soft delete

SQL