Maria Garcia

Query debugging identifies performance and correctness issues. I use EXPLAIN ANALYZE to understand execution plans. Slow query logs reveal problematic queries. pgstatstatements tracks query statistics. Understanding sequential scans vs index scans hel

Database best practices ensure reliability and performance. I follow normalization principles—minimize redundancy. Use appropriate indexes—not too many, not too few. Implement constraints for data integrity. Choose correct data types for efficiency. R

Query plan caching improves performance by reusing execution plans. I use prepared statements to parse once, execute many times. PostgreSQL caches plans after 5 executions. Plan invalidation occurs when statistics change. Generic plans vs custom plans

Database design patterns solve recurring problems. I use the Repository pattern to abstract data access. Active Record maps objects to tables. Unit of Work tracks changes for batch commits. Identity Map caches loaded entities. The Specification patter

Database locks ensure data consistency in concurrent access. I understand lock types—row, table, advisory. Shared locks allow concurrent reads. Exclusive locks prevent all access. Understanding lock granularity prevents contention. Deadlocks occur whe

Database testing ensures schema and queries work correctly. I use transactions for test isolation—rollback after each test. Test fixtures provide consistent data. Factory patterns generate test data programmatically. Understanding schema migrations in

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

LISTEN/NOTIFY provides real-time pub-sub messaging. Publishers send notifications via NOTIFY. Subscribers receive notifications via LISTEN. I use it for cache invalidation, real-time updates, inter-process communication. Payloads up to 8000 bytes carr

Data archival moves old data to cheaper storage. I use partitioning for time-based archival. Detach old partitions, export to S3, drop table. Archive tables store historical data with reduced indexes. Understanding retention policies prevents runaway

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

Multi-tenancy serves multiple customers from one application. I implement tenant isolation via schemas, databases, or row-level security. Shared schema with tenant_id column is simplest—good indexing essential. Separate schemas per tenant improves iso

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