Topic hub · Databases and search

Databases and search investigations

Database and search investigations on PostgreSQL, SQLite, SQL, consistency, storage, locking, Rails query behavior and retrieval.

About this collection

Database behavior is easiest to reason about when query shape, storage, concurrency and application semantics stay connected. These notes move between PostgreSQL internals, Rails call sites and architectural choices: why a query spills to temporary files, how full-text ranking and trigrams complement each other, what optimistic locking actually protects, and where a database transaction stops being able to guarantee delivery to another system.

Search belongs in the same collection because retrieval quality depends on data representation and operational constraints, not only ranking formulas. The curated investigations cover search, consistency, pagination pressure and database selection. The complete chronology adds TOAST compression, Arel, SQL-first tooling and Active Record behavior. Together they favor explicit trade-offs over default-driven advice: SQLite and PostgreSQL solve different deployment problems, Kafka and PostgreSQL expose different consistency boundaries, and an elegant query is still suspect until its plan and workload are understood.

Start here

  1. SQLite in production: the advice that survives the failure modes A failure-mode-first guide to SQLite production use: WAL, busy handling, checkpoints, durability, backups, replication, measurement, and migration pressure.
  2. Weighted full-text search and trigrams in PostgreSQL How tsquery, tsvector weights, ranking, and pg_trgm fit together when building search inside PostgreSQL.
  3. Kafka, PostgreSQL, and consistency boundaries Why Kafka and relational databases solve different consistency problems, and how to think about transactions, events, outboxes, and human error.
  4. PostgreSQL temp files and Rails pagination PostgreSQL temp-file warnings as a Rails query-shape problem: eager loading, row multiplication, pagination, preload, strict loading, and indexes.
  5. SQLite, PostgreSQL, and the smallest useful database choice How to compare SQLite and PostgreSQL for a Rails app without turning the decision into a reflexive architecture debate.
  6. Relational algebra, SQL, and Arel A practical bridge from selection, projection, joins, and grouping to SQL and Rails Arel query construction.

All investigations

  1. SQLite in production: the advice that survives the failure modes
  2. Rails includes, find, and eager loading
  3. PostgreSQL temp files and Rails pagination
  4. Transactional memory after the hype
  5. The Rails job that ran before checkout committed
  6. Optimistic locking for editing a CRUD app
  7. sqlc, iBATIS, and SQL-first data access
  8. PostgreSQL compression, TOAST, and LZ4 storage
  9. Relational algebra, SQL, and Arel
  10. SQLite, PostgreSQL, and the smallest useful database choice
  11. Weighted full-text search and trigrams in PostgreSQL
  12. Kafka, PostgreSQL, and consistency boundaries

Other hubs