View a markdown version of this page

PERF03-BP04 Implement strategies to improve query performance in data store - AWS Well-Architected Framework

PERF03-BP04 Implement strategies to improve query performance in data store

To keep a data store efficient and scalable as demand grows, optimize both data layout and query execution.

Desired outcome:

  • Your queries are optimized for your workload's access patterns.

  • Your data layout, including partitioning, file format, and indexing, supports the queries you run most.

  • You identify and remediate slow queries using query plans and metrics.

  • You validate optimization strategies before applying them in production.

Common anti-patterns:

  • Teams do not partition data in the data store.

  • Teams store data in a single file format regardless of the query pattern.

  • Teams run without indexes, or maintain indexes that collide too often.

  • Teams do not use query result caching where it would help.

  • Teams write broad queries, such as selecting all columns or filtering inefficiently.

  • Teams distribute tables without regard to join patterns, which moves data between nodes on every query.

Benefits of establishing this best practice:

  • Queries return faster, which improves user experience.

  • Resource use and cost fall because queries scan less data.

  • The data store scales further before it needs larger instances.

  • Bottlenecks from contention and full scans become rare.

Level of risk exposed if this best practice is not established: Medium

Implementation guidance

Query performance depends on data layout and query execution. Data layout covers partitioning, file format, compression, and denormalization. Query execution covers indexes, join strategy, materialized views, and caching. Use the query plan to see where time and I/O go, then decide which to tune.

Data layout trade-offs depend on whether the workload is analytical or transactional. Columnar formats, such as Parquet and ORC, read only the columns a query needs, which suits large analytical scans. Row-based formats suit transactional access that touches whole records. Partitioning reduces the data a query scans. Denormalization speeds frequent joins, but it duplicates data and complicates writes, so use it deliberately for hot paths.

Indexes speed reads on the columns a query filters, but each index slows writes and adds maintenance overhead. Materialized views precompute a frequent query's result. They trade storage and refresh work for fast reads, and help only when the underlying data changes less often than the query runs. For repeated queries against slowly changing data, use query result caching. Weigh cache freshness against the workload's staleness tolerance.

Query planners use statistics to choose execution paths. When statistics drift, plans degrade even though nothing else changed. Keep plans accurate with regular maintenance, such as vacuuming, reindexing, and refreshing statistics. Test changes in a non-production environment against representative load before you apply them. AWS provides diagnostic tools, including query plan analysis in Amazon Redshift, EXPLAIN and EXPLAIN ANALYZE in Amazon Athena, and query analysis in Amazon RDS Performance Insights.

If your data store distributes data across nodes, choose the distribution key from your most frequent joins. An even distribution avoids skew but can force rows to move between nodes for each join. A key-based distribution keeps matching rows together and avoids that movement, but a key with few distinct values creates skew. In Amazon Redshift, see distribution styles, and check the query plan and skew before you adopt a key.

Implementation steps

  1. Analyze your critical queries and access patterns: Identify the queries that run most often or matter most to users, and document the access patterns behind them. This focuses optimization on the queries that affect performance rather than on every statement.

  2. Identify slow queries using query plans: Find slow-running queries and read their execution plans to see where time and I/O go.

  3. Optimize data layout: Choose a columnar file format for analytical workloads and a row-based format for transactional ones. Compress data to reduce I/O while monitoring compression ratios, and partition data by frequently filtered columns while avoiding over-partitioning. For key-value stores, design keys around partitions and data distribution.

  4. Optimize indexes and materialized views: Index the columns your common queries filter on, monitor index usage, and remove unused indexes that only add write overhead. Use materialized views for frequent queries whose underlying data changes less often than the query runs.

  5. Optimize query structure and joins: Select only the columns a query needs, and write precise filter conditions. When joining tables, choose the join type that fits the requirement. Consider denormalization for tables that are joined on hot paths.

  6. Add query result caching: For repeated queries against slowly changing data, enable result caching with features, such as the Amazon Athena query result cache or Amazon Redshift result caching, and set freshness limits the workload can tolerate.

  7. Run regular maintenance: Schedule vacuuming, reindexing, and statistics refresh so the query planner works from accurate information and continues to choose good plans.

  8. Test strategies in a non-production environment: Apply each optimization against representative data and load before promoting it, and confirm the change improves the target queries without harming others.

  9. Align the distribution key with join patterns: Where your data store uses a distribution key, base it on your most frequent joins, and confirm in the query plan that data movement between nodes drops.

Resources

Related best practices:

Related documents:

Related workshops: