All posts
PostgreSQL Basics #8 Views, CTEs, Window Functions: Keeping Complex Queries Readable
Three tools for structuring queries that have outgrown one screen: views that give a repeated query a name and materialized views as their physical-copy sibling, WITH clauses (CTEs) that let a query read top to bottom, the basic anatomy of window functions (OVER, PARTITION BY) and the canonical ROW_NUMBER pattern for top-N per group, how window functions differ from aggregates (rows are not collapsed), and LAG for month-over-month comparisons.
Rust Basics #8 Traits and Generics: Shared Behavior in a Language Without Inheritance
The part that settles the IOUs. Traits as definitions of shared behavior with default implementations, generics with trait bounds, the standard traits derive generates for you (Debug, Clone, Copy, PartialEq), monomorphization — why generics compile down to zero runtime cost — and the dynamic dispatch behind the dyn in Box<dyn Error>.
SQLAlchemy 2.0 #7 Alembic and Production Setup: Migrations, Async, Team Rules
The series finale on operational topics: why create_all cannot handle schema changes and Alembic takes over, what autogenerate detects and what it misses, the review rules for migration files, going async with create_async_engine and AsyncSession and the lazy-loading restriction that comes with it, laying out models, sessions, and settings in a project, and the team rules worth enforcing.
PostgreSQL Basics #7 JSONB: Schema Flexibility Inside a Relational Database
JSONB is what let PostgreSQL reach into NoSQL territory. The difference between json and jsonb (and why jsonb is the default), the extraction operators (->, ->>, #>>) and the existence and containment operators (?, @>), the GIN index that backs JSONB search, the nature of updates (jsonb_set), and most importantly the design boundary of what stays a column and what goes into JSONB.
Rust Basics #7 Collections and Iterators: Vec, String, HashMap, and Chains Instead of for
The three collections that make up the body of practical code. Vec and why get is used instead of indexing, why String cannot be indexed at all (UTF-8 and the difference between bytes and characters), HashMap and the entry API, and the iterator style — map, filter, collect chains with lazy evaluation — that replaces for loops.
SQLAlchemy 2.0 #6 Advanced Queries: Joins, Aggregation, Subqueries, Bulk Operations
Building real-world queries on select() alone: the difference between scalars and execute return shapes, combining conditions with or_, joins and explicit ON clauses, reading group_by aggregates through labels, subqueries and EXISTS, LIMIT-OFFSET pagination and its limits versus keyset pagination, and bulk INSERT and UPDATE that bypass the ORM unit of work.
PostgreSQL Basics #6 Transactions and MVCC: The Fundamentals of Concurrency
How PostgreSQL preserves consistency when many connections touch the same data at once. The basics of BEGIN, COMMIT, and ROLLBACK and atomicity, the core of MVCC (multi-version concurrency control) — readers never block writers — the fact that UPDATE actually creates a new row version and the dead tuples that foreshadows, what the default Read Committed isolation level means and its trap, and when to raise to Repeatable Read or Serializable.
Rust Basics #6 Error Handling: Result, the ? Operator, and the Boundary Between panic and unwrap
Error handling in a language with no exceptions. Result, which puts the possibility of failure into the return type; the ? operator that collapses nested match blocks into one character and propagates errors; the practical form where main returns a Result; and the judgment calls for when unwrap, expect, and panic are acceptable and when they are not.
SQLAlchemy 2.0 #5 Relationships: One-to-Many, Many-to-Many, and the N+1 Problem
The ORM at its best and its most dangerous — relationship(): how foreign keys and relationship() divide the work, declaring bidirectional one-to-many with back_populates, many-to-many through a secondary table, cascade and delete-orphan for parent-child lifecycles, spotting the N+1 problem that lazy loading creates by reading the echo log, and choosing between selectinload and joinedload to fix it.
PostgreSQL Basics #5 Reading EXPLAIN: Diagnosing Queries with Execution Plans
How to confirm why a query is slow with the execution plan instead of guesswork. The difference between EXPLAIN and EXPLAIN ANALYZE (the latter actually executes), reading the plan tree from the innermost node outward, what Seq Scan, Index Scan, Index Only Scan, and Bitmap Scan mean, comparing cost and actual time and estimated vs actual rows, the remedy when estimates are badly off (ANALYZE), and the habit of checking read volume with BUFFERS.
Rust Basics #5 Structs and Enums: match, Option, and Designing Without null
The two axes of structuring data: structs and enums. Attaching methods with impl blocks, the expressiveness of Rust enums whose variants carry different data, match with compiler-enforced exhaustiveness, and Option — the type that expresses "there may be no value" instead of null. The tools that form the skeleton of Rust code.
SQLAlchemy 2.0 #4 The Session: Change Tracking, flush vs commit, and the Four Object States
The heart of the ORM, the Session: how it batches changes as a unit of work and emits them as SQL, the difference between flush and commit, the four object states — transient, pending, persistent, detached — the identity map that returns the same object for the same row, why attribute access after commit triggers a new query, and the one-session-per-request scoping rule for web applications.