Database

Store and query data. I will add more later.

Resources

• Database Normalization | Wikipedia — Structuring relational database for data integrity
• Database Fundamentals (HN)
  • The fundamental problems in the world of databases and common approaches to solve them

Links

• 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

• Easy, alternative soft deletion: deleted_record_insert
  • Hard delete and create a function that dump into a schemaless table
• Avoid the soft delete anti-pattern
  • Few alternatives proposed, e.g. add state, temp table, use data warehouse

Postgres

• Nine Ways to Shoot Yourself in the Foot with PostgreSQL (HN)
  • work_mem, functions and procedures, triggers, NOTIFY, EXPLAIN_ANALYZE, CTEs v.s. subqueries (debatable), recursive CTEs, missing indexes for foreign keys, = v.s. IS NOT DISTINCT FROM
• Understanding Database Indexes in PostgreSQL | Mastermind Dev (HN)
  • A guide on database indexes, how they work and what are the types (BTree, Hash, GiST, SP-GiST, GIN, BRIN)
  • Different ways to index: basic, partial, expression, covering
  • Maintenance: detecting unused indexes, bloats (unused space) or duplicates
• Securing PostgreSQL DB with Roles & Privileges | Romario
  • Scope and implement roles and privileges in PostgreSQL
  • Commands like CREATE, GRANT, ALTER DEFAULT PRIVILEGES, and REASSIGN OWNED
• Using JSONB in PostgreSQL | ScaleGrid
  • Includes patterns & antipatterns, data structures, operators & functions, indexing JSONB columns in Postgres
• The Unexpected Find That Freed 20GB of Unused Index Space (HN)
  • A few tricks like pg_stat_all_indexes and REINDEX free up space
  • Finding that Postgres index NULL values. Partial indexes can free up space
• Transaction Isolation in Postgres, explained
  • ACID - atomicity, consistency, isolation and durability
  • Isolation as defined in SQL 92 Standard: serializable isolation. Results of concurrent transactions are the same as running them sequentially
  • Anomalies: dirty reads, non-repeatable reads, phantom reads
  • Postgres MVCC: anything lower than the lowest active XIDs, new versions of existing rows and new rows are hidden
  • Postgres vacuuming: freeing XIDs for reuse since XIDs are limited to 32 bits
  • Postgres isolation test suite
• How postgres stores data on disk
  • Heap, segment, page, tuple
  • Using the pageinspect extension to see how data are stored

SQL

• Get records with max value for each group of grouped SQL results
  • Just a page that I keep coming back to