sql

SQL joins combine data from multiple tables. INNER JOIN returns matching rows only. LEFT/RIGHT JOIN includes all rows from one table, nulls for non-matches. FULL OUTER JOIN combines both. I use CROSS JOIN for Cartesian products sparingly—performance k

Sqlx is a pure-Rust SQL client that checks queries at compile time against your database schema. The query! macro connects to your DB during compilation and validates that columns and types match. This catches typos and schema drift before runtime. It

EXPLAIN reveals database execution plans. I use EXPLAIN ANALYZE for actual runtime statistics. Understanding plan nodes—Seq Scan, Index Scan, Nested Loop, Hash Join—guides optimization. Cost estimates predict query expense. Rows estimates show expecte

Some read paths want a denormalized shape without materializing a new table. Postgres views are a clean option. Keep the view definition in a migration and map it with a read-only model.

Endpoints with optional filters often devolve into messy SQL string concatenation. I prefer building queries with squirrel so I can conditionally add WHERE clauses while keeping the final query parameterized. The pattern also helps keep ordering stabl

Not all queries benefit from a full-table index, especially when most rows are ‘inactive’ or archived. A partial index lets you index only the subset you care about (like active users or un-deleted records), which shrinks index size and improves cache

Postgres stability depends on respecting its limits. I configure pgxpool with explicit MaxConns and MaxConnLifetime so the service doesn't accidentally open too many connections during bursts. I also set a session statement_timeout in AfterConnect, wh

Prepared statements are useful when the same query runs in hot loops, but they come with lifecycle responsibilities. I prepare once (usually at startup), store the *sql.Stmt, and always close it on shutdown. The important detail is still using QueryRo

Advanced aggregations extract insights from data. ROLLUP creates hierarchical subtotals. CUBE generates all possible grouping combinations. GROUPING SETS specifies exact grouping combinations. FILTER clause conditions aggregations. String aggregation

Optimistic locking is great for most user edits, but sometimes you need strict serialization—like decrementing inventory or consuming a one-time token. In those cases I use SELECT ... FOR UPDATE inside a transaction. The lock is scoped to the transact