Database Insights

Choose the right Cassandra compaction strategy: STCS for write-heavy workloads, LCS for read-heavy point lookups, and TWCS for time-series data with TTL-based expiry.

Enable and tune PostgreSQL parallel query for analytical workloads. Covers max_parallel_workers settings, PARALLEL SAFE functions, JIT compilation, and execution plan verification.

Implement PostgreSQL full-text search with tsvector columns, GIN indexes, and ts_rank scoring. Covers phrase search, weighted fields, and ts_headline snippets.

Manage Elasticsearch indexes with ILM policies, correct shard sizing, and force merge for historical indices. Covers hot/warm/delete phases and cluster health monitoring.

Optimize MongoDB aggregation pipelines by ordering stages correctly, using indexes in $match and $sort, projecting early, and tuning $lookup with pipeline filters.

Diagnose and fix PostgreSQL query plan cache issues. Covers generic vs custom plans, plan_cache_mode setting, DEALLOCATE, statistics targets, and pg_stat_statements variance detection.

Configure the MySQL slow query log and analyze it with pt-query-digest to find top offenders by total time. Includes EXPLAIN diagnosis and performance_schema alternative.

MySQL 8 introduced comprehensive password management — password validation policies, reuse history, expiration, and dual passwords for zero-downtime credential rotation.

Implement MySQL table partitioning with RANGE and HASH strategies. Covers instant partition drops for data lifecycle, partition pruning verification, and key gotchas.

Tune autovacuum for high-churn PostgreSQL tables. Lower scale factors, monitor dead tuple accumulation, detect wraparound risk, and use pg_stat_progress_vacuum.

Measure and reclaim PostgreSQL index bloat using pgstatindex and REINDEX CONCURRENTLY. Learn safe online index rebuilding and autovacuum tuning to prevent future bloat.

Set up PgBouncer in transaction pooling mode to handle thousands of PostgreSQL connections. Configuration, monitoring with SHOW POOLS, and common pitfalls.