Identifying and Fixing Slow Queries

Slow database queries can significantly degrade application performance, user experience, and more. However, a few minor changes can dramatically reduce query latency. Start by identifying slow queries using pg_stat_statements in PostgreSQL or the sys.statement_analysis view in MySQL (with Performance Schema enabled). Once found, here are just a few of the many actionable fixes you can apply:

• Index Strategically: Use EXPLAIN to identify full table scans and ensure indexes exist on columns used in JOIN, WHERE, or ORDER BY clauses.
• Limit Overfetching: Replace SELECT * with specific columns and apply conscious WHERE conditions to reduce the retrieved data volume when appropriate.
• Simplify Query Structure: Eliminate unnecessary joins and subqueries, and denormalize or refactor the database physical model to streamline execution.
• Tighten Filters: Watch for WHERE clauses on low-cardinality columns and combine them with more selective filters to narrow results when retrieving rows.
• Align Data Types: Ensure the types in your predicates match the column definitions exactly; mismatches can prevent index use.
• Cache Stable Workloads: Identify repetitive queries with consistent inputs and responses. These are ideal candidates for caching.
• Collapse N+1 Queries: Repeated queries inside loops can crush performance with excessive round-trips. Use JOINs or batch fetches instead.

Check out this post for a deeper dive into identifying cacheable queries and the easiest way to reduce query latency.

🔍 What We’re Watching in the Database Ecosystem

Announcing open sourcing pgactive: active-active replication extension for PostgreSQL AWS announces the open sourcing of pgactive, a PostgreSQL extension for asynchronous active-active replication. It enables data streaming between database instances, improving resiliency and making it easier to support writers in different regions.

ClickHouse Raises $350 million Series C to power analytics for the AI era ClickHouse just raised $350 M in Series C funding to accelerate real-time analytics for AI-era workloads, bringing its total funding to over $650 M. The company plans to use the funds for global expansion, product development, and engineering partnerships.

HTAP is Dead Mooncake argues that HTAP has failed to deliver on its promise, as the needs of teams, workloads, and infrastructure are too divergent. Instead, modern architectures pair fast OLTP systems with external analytics engines.

In Case You Missed It

🔄 Catch the replay: Last week, Tanmay and Bianchi hosted the first in a series of webinars designed to help you optimize your database performance. Check out “[How to Find Your Problem Queries on MySQL](https://www.youtube.com/live/Dd2tlKTjF7E” for the full video.