Skip to content
Code by Pawpu

Databases

SQL / PostgreSQL / MySQL

Indexes, plans, transactions, and the queries that decide whether a system stays fast.

SQL · PostgreSQL · MySQL · 3 published · 19 in the review queue. Written by Al Beltran.

  2. A single compact storage module glowing on a dark metal bench
    Architecture

    SQLite Is Back, and the Edge Is Why

    D1, Turso, LiteFS, and a decade of Postgres-by-default have teams rediscovering a file database. The win is locality. The bill is replication and migration discipline.

    #sqlite#edge#databases#architecture
  3. A dark ledger book open under a precise blue desk lamp
    Engineering

    SQL Is Still the Skill That Compounds

    Models can draft a query. They cannot own the index, the cardinality, or the 2 a.m. plan that scans the wrong partition. Relational literacy is still leverage.

    #sql#databases#performance#engineering

Review queue

Planned notes stay unpublished until they are written and reviewed. Titles are listed so the topic map is complete. They are not pages.

  1. PostgreSQL Interview Questions, With the Reasoning

    MVCC, indexes, JSONB, and the Postgres-specific answers that are not just "it's SQL."

    interview · mixed · high priority

  2. MySQL Interview Questions, With the Reasoning

    InnoDB, isolation, charsets, and the lock waits that show up in interviews because they show up in prod.

    interview · mixed · high priority

  3. Indexes That Match the Query, Not the Table

    Leftmost prefixes, selectivity, and the index you added that nobody uses.

    educational · intermediate · medium priority

  4. How to Read an EXPLAIN Plan Without Guessing

    Seq scan vs index, estimated rows, and the filter that ran after the damage.

    educational · intermediate · medium priority

  5. Transaction Isolation Levels in Plain Language

    What dirty, non-repeatable, and phantom actually mean for a checkout.

    educational · intermediate · medium priority

  6. PostgreSQL JSONB: When a Column Is a Document

    Flexible attributes vs queryable facts. Index GIN when you search it; do not if you only store it.

    comparison · intermediate · medium priority

  7. InnoDB Locks: Gap, Next-Key, and the Update That Waited

    Why a SELECT ... FOR UPDATE locked more rows than you expected.

    troubleshooting · senior · medium priority

  8. Foreign Keys vs Application Invariants

    The database can refuse an orphan. The app can still lie. Pick which layer you trust.

    architecture · intermediate · medium priority

  9. Schema Migrations Without Taking the Site Down

    Expand, deploy, contract. Lock time is a product feature.

    architecture · senior · medium priority

  10. Pagination That Uses an Index

    Why OFFSET 100000 is a full walk, and how a seek pagination query looks.

    problem-solving · intermediate · medium priority

  11. NULL and Three-Valued Logic: The Filter That Dropped Rows

    UNKNOWN is not false. NOT IN (NULL) will humble you.

    educational · beginner · medium priority

  12. Partial Indexes in PostgreSQL

    Index the open tickets, not the closed history, when the query always filters that way.

    educational · intermediate · medium priority

  13. MySQL Charsets and Collations: The Equality Bug You Cannot See

    utf8 vs utf8mb4, case, accents, and the unique key that was not unique.

    troubleshooting · intermediate · medium priority

  14. Covering Indexes: When the Index Is the Table

    Index-only scans, included columns, and the write cost you accept.

    educational · intermediate · medium priority

  15. Window Functions for Rank, Running Totals, and "Latest Row"

    ROW_NUMBER for latest-per-group, and the subquery you can delete.

    educational · intermediate · medium priority

  16. Connection Pooling: Size, Timeouts, and Serverless

    Why Lambda plus a naive pool melts max_connections, and what PgBouncer is for.

    troubleshooting · intermediate · medium priority

  17. Read Replicas and the Stale Read After Write

    Send the write to primary. Do not read the replica for the next screen if the user must see their save.

    architecture · intermediate · medium priority

  18. Schema Design When the Facts Are Events

    Append-only facts vs mutable snapshots, and the query you will need on day 30.

    architecture · senior · medium priority

  19. SQL Injection Is Still a Parameter Problem

    ORMs help until you concatenate. Prepared statements are the habit.

    educational · beginner · medium priority