<aside> 📌

Буллеты про PostgreSQL, MySQL и SQL: индексы, планы запросов, транзакции, репликация, партиционирование, эксплуатация и миграции.

</aside>

Индексы и планы запросов

• Cut p95 query latency from Xms to Xms by replacing sequential scans with composite B-tree indexes on the X hottest tables.
• Rewrote the X slowest queries after EXPLAIN ANALYZE profiling, reducing average execution time by X%.
• Introduced partial indexes on soft-deleted rows, shrinking index size by X GB and speeding the main listing query by X%.
• Added covering indexes with INCLUDE columns to enable index-only scans, cutting buffer reads per query by X%.
• Replaced X redundant single-column indexes with X composite ones, reclaiming X GB of disk and cutting write amplification by X%.
• Diagnosed X plan regressions caused by stale statistics, tuning autovacuum and default_statistics_target to stabilise p99.
• Introduced GIN indexes on JSONB payloads, cutting attribute-filter queries from Xs to Xms.
• Built GiST-indexed geospatial queries with PostGIS, serving radius search over X million points in under Xms.
• Added pg_trgm trigram indexes for fuzzy name search, replacing an Xs LIKE scan with an Xms lookup.
• Eliminated N+1 access patterns across X endpoints by batching with ANY($1), cutting database round trips by X%.
• Tuned work_mem and enabled parallel query for analytical endpoints, reducing report generation from Xmin to Xs.
• Built pg_stat_statements dashboards ranking queries by total time, driving a X% reduction in overall database CPU.
• Removed X unused indexes identified via pg_stat_user_indexes, cutting write latency by X% and storage by X GB.
• Introduced extended statistics on correlated columns, fixing X cardinality misestimates that caused Xs query spikes.

Оптимизация запросов

• Replaced OFFSET pagination with keyset cursors across X endpoints, making deep pages Xx faster and constant-time.
• Rewrote correlated subqueries as lateral joins and CTEs, cutting the reporting query from Xs to Xms.
• Materialized X frequently joined aggregates into refreshable materialized views, cutting dashboard load from Xs to Xms.