Maria Garcia

Recursive CTEs traverse hierarchical data—org charts, category trees, graphs. I use WITH RECURSIVE for self-referential queries. Base case provides starting rows. Recursive case joins to previous iteration. Understanding termination prevents infinite

JSON and JSONB store semi-structured data. JSONB is binary format—faster, indexable. I use JSONB for flexible schemas, API responses, configuration. JSON operators extract values, filter documents. GIN indexes enable fast JSONB queries. Containment op

Query optimization maximizes performance through efficient execution plans. I analyze queries with EXPLAIN ANALYZE. Understanding sequential scans vs index scans guides optimization. Join order affects performance dramatically. Subquery optimization v

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

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

Geospatial data represents geographic locations and shapes. I use PostGIS for spatial queries. Point data stores coordinates—latitude, longitude. LineString represents paths. Polygon defines areas. Spatial indexes (GIST) enable fast proximity queries.

Time-series data tracks measurements over time—metrics, logs, sensor data. I use TimescaleDB for time-series workloads. Hypertables automatically partition by time. Continuous aggregates precompute rollups. Time-based retention policies auto-delete ol

Constraints enforce data integrity at the database level. PRIMARY KEY ensures uniqueness and identifies rows. FOREIGN KEY maintains referential integrity. NOT NULL prevents null values. UNIQUE prevents duplicates. CHECK validates data conditions. DEFA

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

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.

Schema migrations evolve database structure safely. I use migration tools like Flyway, Liquibase, or framework migrations. Version-controlled migrations track schema changes. Up migrations apply changes, down migrations revert. Idempotent migrations c

Connection pooling reuses database connections across requests. Creating connections is expensive—pooling amortizes overhead. I use PgBouncer for PostgreSQL, ProxySQL for MySQL. Session pooling maintains session state. Transaction pooling is more effi