postgresql

Data import/export moves data between systems. I use COPY for bulk operations—orders of magnitude faster than INSERT. CSV format balances simplicity and performance. Binary format is faster but less portable. Streaming import handles large files witho

While ActiveRecord validations catch most invalid data, database constraints provide a safety net that prevents invariant violations even when validations are bypassed. I add null: false constraints for required columns, unique indexes for uniqueness

Migrations evolve database schema safely across environments. I follow strict conventions: one logical change per migration, descriptive names, reversible operations. Using change instead of up/down enables automatic rollback for most operations. For

As applications grow, read operations often dominate database load. Directing reads to replica databases while keeping writes on the primary reduces contention and improves response times. Rails makes this straightforward with connects_to and role-bas

Foreign Data Wrappers (FDW) query external data sources as tables. I use postgresfdw for remote PostgreSQL databases. filefdw reads CSV files. mysql_fdw connects to MySQL. Understanding FDW limitations prevents surprises. Predicates push down to remot

Understanding query execution plans is essential for optimizing slow queries. Rails provides explain method on ActiveRecord relations to show PostgreSQL's query planner output. I look for sequential scans on large tables (indicating missing indexes),

VACUUM reclaims storage from dead tuples. Updates and deletes leave dead rows—VACUUM removes them. Autovacuum runs automatically but needs tuning. VACUUM FULL rewrites entire table—requires lock, reclaims most space. Understanding bloat prevents perfo

LATERAL joins enable correlated subqueries in FROM clause. Each row can reference previous table columns. I use LATERAL for top-N-per-group queries. CROSS JOIN LATERAL iterates for each row. LEFT JOIN LATERAL includes rows without matches. Understandi

Materialized views store query results physically for fast access. I use them for expensive aggregations, complex joins, reporting queries. Unlike views, materialized views cache data—need manual refresh. REFRESH MATERIALIZED VIEW updates cached data.

Observability provides insight into database health and performance. I monitor key metrics—queries per second, connection count, cache hit ratio. Slow query logs identify performance problems. Query latency percentiles show user experience. Lock wait

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

Indexes dramatically speed up queries but slow down writes. B-tree indexes handle equality and range queries—default for most databases. I create indexes on foreign keys, frequently queried columns, and WHERE/ORDER BY clauses. Composite indexes order