The database query optimization decision record: why the query profiling model you chose determines your N+1 detection gap and your query plan statistics drift surface

A 41-person B2B SaaS company built a project management platform for engineering teams — sprint planning, task assignment, progress tracking, and cross-team dependency mapping. The platform's backend was a Django application backed by PostgreSQL, and the engineering team had adopted the Django ORM from the project's inception. The ORM's lazy-loading model for related objects was familiar to the team and produced readable, concise view code. A typical project dashboard view fetched the authenticated user's projects, then displayed team members and the most recent status update for each project on a summary page.

The view code was approximately forty lines. It fetched the project queryset with a single Project.objects.filter(team=team) call, then iterated over the results in the template to render each project card. Each card accessed project.members.all() for the team member avatars and project.statusupdates.latest() for the most recent status update. The code had been reviewed, tested, and merged without comment. In the test suite, the dashboard view test ran against a fixture with twelve projects, and the test verified that the page returned an HTTP 200 with the correct project titles in the response body. Execution time in the test environment was 21 ms. No query count was asserted.

The N+1 pattern generated by this view was one query for the project list, one query per project for the members, and one query per project for the latest status update — a total of 1 + N + N queries where N was the number of projects. Against twelve projects in the test fixture, this produced twenty-five queries and completed in 21 ms. The Django Debug Toolbar was installed in the development environment and would have displayed the query count, but the engineer who implemented the view had it disabled in their local configuration. The test environment did not run the toolbar, and no assertion checked query count.

In production, the platform's highest-activity accounts had accumulated between 200 and 600 projects over eighteen months of use. A 200-project account generated 401 queries per dashboard load. A 600-project account generated 1,201 queries. The P99 latency on the dashboard endpoint was 45 ms in the first year of production, when accounts were smaller. By month eighteen, P99 had climbed to 3.8 seconds. The engineering team had tracked the latency increase in their Datadog dashboard but attributed it to traffic growth — as more users were active simultaneously, response times would increase, and the team had not yet hit the point where they needed to scale the database. The investigation that revealed the N+1 pattern was triggered not by a latency alert but by a customer email: a team lead at an enterprise account reported that the dashboard "sometimes takes 15 seconds to load on Monday mornings" when her entire team arrived at work and opened the app simultaneously.

The debugging session took forty minutes. The engineer added Django Debug Toolbar to the production-equivalent staging environment and loaded the dashboard with a fixture that replicated the enterprise customer's 580-project account. The toolbar reported 1,161 queries on a single page load, with a total query execution time of 2.9 seconds. The fix was two lines: adding select_related('team') and prefetch_related('members', 'statusupdates') to the queryset reduced the query count from 1,161 to 3 and P99 latency to 42 ms. The fix was deployed the following morning.

The retrospective identified three gaps. First, the test fixture had twelve projects — a dataset too small to make the N+1 pattern visible by execution time. Second, no query count assertion existed in any test, so the pattern was not detectable by the test suite at any dataset size. Third, the Django Debug Toolbar was disabled in the local development configuration of the engineer who implemented the view. None of these gaps were addressed as decisions at the time the ORM was adopted. The team's decision record for the ORM choice said: "Use Django ORM for data access. It is the standard Django data layer and the team has prior experience with it." There was no profiling model, no test dataset size requirement, no query count assertion policy, and no specification of which endpoints required query count verification before merge.

A 39-person marketplace company built a platform for specialty consumer goods — handcrafted furniture, artisan ceramics, and small-batch home goods — connecting independent makers with buyers through curated storefronts. The platform's database was MySQL 8, and the engineering team had configured the slow query log during the initial production deployment with long_query_time = 2.0. The engineer who set the threshold later described the reasoning: "Two seconds is already terrible for a user-facing query. If something's slower than that, we definitely need to fix it." The slow query log had never appeared in the team's operational documentation, and the threshold had never been reviewed.

Over the following sixteen months, the platform grew from 8,000 active product listings to 4.2 million listings across 340 maker storefronts. The product search and listing queries — the hot path for every buyer session — were originally fast: at 50,000 listings, the primary listing fetch query completed in 180 ms at P99, well within the team's informal "under 500 ms" target for interactive pages. The query used a single-column index on category_id with a secondary sort on created_at. As the listing count grew, the query's characteristics changed: the index selectivity on category_id degraded as popular categories accumulated hundreds of thousands of listings, and the secondary sort required a filesort on the 180-column listings table when the result set exceeded the sort buffer. By month fourteen, the query was completing in 1.4 seconds at P99. By month sixteen, it reached 1.8 seconds at peak traffic. Neither value crossed the 2.0-second slow query log threshold.

The same pattern affected twenty-two other queries: search filter queries, maker storefront aggregation queries, and the recommended-items query used on each listing page. Each had started the same period at under 250 ms and had drifted upward as the table grew, index fragmentation accumulated, and the join paths traversed larger intermediate result sets. None crossed 2.0 seconds. None appeared in the slow query log.

Detection came from an unexpected source. The platform used Stripe webhooks to receive payment confirmation events, and the Stripe webhook handler fetched the listing record to update its sold status after payment. Stripe's webhook delivery had a 30-second timeout, and Stripe retried webhooks that received no response within 30 seconds. In month sixteen, Stripe's dashboard began reporting webhook delivery failures — the platform was responding to some webhooks with no acknowledgment within 30 seconds. The on-call engineer traced the slow webhook responses to a JOIN between the payments table and the listings table that was completing in 24 seconds at P99 under concurrent load — a query far above the slow query log threshold that had simply not existed when the threshold was set. Investigating that query led the engineer to run pt-query-digest against the slow query log for the first time, which revealed the twenty-three sub-2.0-second queries averaging 1.4 seconds that had been accumulating invisibly.

The remediation sprint took three weeks and covered composite index additions on the most critical query paths, a query rewrite for the listing search path that replaced a filesort with a covered index, and a MySQL configuration change that lowered long_query_time from 2.0 to 0.1. The threshold change immediately surfaced the twenty-three regressions in the slow query log — they were now logging thousands of entries per hour and visible in the first pt-query-digest run after the configuration change. The team's post-incident review documented: "We had the slow query log enabled but the threshold was useless for detecting user-visible regressions. The threshold was a guess, it was never revisited as traffic grew, and we had no process for reviewing query performance outside of emergency incidents."

A 47-person SaaS company built a financial reporting platform for mid-market accounting and finance teams — general ledger reconciliation, multi-entity consolidation, custom report generation, and automated variance analysis. The platform's database was PostgreSQL 14, and the reporting module's core query was a multi-table join that aggregated journal entry records by account, period, and entity to produce the trial balance view — the foundation for every report the platform generated.

At the platform's launch with twelve customer accounts, the trial balance query executed against a journal_entries table with approximately 2.3 million rows. The query planner chose a sequential scan on a 14,000-row filter result (the current period's entries for a single entity), which took 8 ms at P99. The team reviewed the EXPLAIN output once during initial optimization and noted that the sequential scan was correct: at 14,000 rows, a sequential scan was faster than an index scan due to the sequential I/O advantage on heap pages. The EXPLAIN output was not stored in version control, and the query was not added to any monitoring baseline.

Eighteen months later, the platform had grown to 134 customer accounts with significantly larger finance operations. The journal_entries table had grown to 400 million rows. Autovacuum ran on the default configuration: autovacuum_analyze_scale_factor = 0.2, meaning ANALYZE ran after 20% of the table's rows were modified or inserted. On a 400 million row table, 20% was 80 million rows — at the platform's current write rate of approximately 600,000 journal entries per day, autovacuum's ANALYZE threshold was crossed roughly every 130 days. The statistics in pg_statistic for the entry_date and entity_id columns — the primary filter columns in the trial balance query — were 130 days stale.

In month eighteen, a new enterprise customer onboarded with ten years of historical journal entry data migrated from their legacy system. The bulk data load inserted 52 million rows over a weekend. Autovacuum ran after the load and updated statistics for the high-churn columns — but the statistics target was the PostgreSQL default of 100 for all columns, producing a 100-bucket histogram for entity_id, which now had 847 distinct entity IDs ranging from a single-user entity with 40,000 entries to the new enterprise customer's consolidation entity with 18 million entries. The histogram's 100 buckets were too coarse to represent the distribution accurately: the planner estimated 2,400 rows for a WHERE entity_id = 412 AND entry_date BETWEEN '2026-01-01' AND '2026-03-31' predicate that actually matched 840,000 rows.

The planner's 2,400-row estimate caused it to choose a sequential scan over the index scan: at an estimated 2,400 rows from a 400 million row table, the index scan's random I/O cost was estimated as more expensive than a targeted sequential scan — a correct heuristic at 2,400 rows and catastrophically wrong at 840,000 rows. The trial balance query for the enterprise customer's consolidation entity now performed a sequential scan on 400 million rows, joining against two lookup tables, with a sort on the result. Execution time: 47 seconds.

The engineering team received no automatic alert. The first signal was a cluster of support tickets from the new customer's finance team: report generation was "stuck loading" and eventually timing out. The on-call engineer reproduced the issue by running the trial balance query for the enterprise entity in psql and watching it run for 47 seconds. Running EXPLAIN (ANALYZE, BUFFERS) revealed the sequential scan and the 2,400-row estimate against 840,000 actual rows. The fix was two-part: ALTER TABLE journal_entries ALTER COLUMN entity_id SET STATISTICS 500 followed by ANALYZE journal_entries corrected the planner's estimate to 839,400 rows; the planner immediately switched to an index scan on the (entity_id, entry_date) index, reducing execution time from 47 seconds to 340 ms. The longer-term fix was reducing autovacuum_analyze_scale_factor to 0.01 for the journal_entries table — so ANALYZE ran after 1% of rows were modified (4 million rows) rather than 20% (80 million rows) — and increasing statistics targets to 500 for all columns used in the trial balance query's WHERE clause.

The post-incident review noted: "The statistics target was never a decision. It was the default. We knew autovacuum existed and assumed it kept statistics current. Nobody had looked at pg_statistic for the journal_entries columns, and nobody had specified what 'current statistics' meant for a table that grows 600,000 rows per day. The query worked correctly for eighteen months and then stopped working on a Monday morning after a customer onboarding. The EXPLAIN output from the initial optimization is gone."

Structural properties set by the query optimization decision

Three structural properties are determined when a team decides — or fails to explicitly decide — how to optimize database query performance: what the query profiling model selection determines about the N+1 visibility surface as ORM abstractions generate query counts that are invisible without explicit measurement, what the slow query threshold calibration determines about the regression detection gap as queries drift into user-visible latency ranges below the threshold, and what the query plan statistics model determines about the execution plan drift surface as planner estimates diverge from actual row distributions after data volume changes. None of these properties are labeled as decisions in the conversations that produce them. The query profiling model emerges from the ORM adoption decision as an unstated assumption that developers will notice query count issues during development — without a specification of what noticing requires (query count assertions, minimum test dataset sizes, profiling tools enabled by default). The slow query threshold emerges from a default configuration value or a guess at "obviously bad" latency without reference to the application's actual latency SLO. The query plan statistics model emerges from autovacuum's default scale factor, which was designed for OLTP tables of moderate size and provides insufficient statistics freshness for high-growth analytical tables.

Property 1: The query profiling model selection and the N+1 visibility surface. ORM frameworks generate SQL from high-level object graph traversal operations. The N+1 pattern — one query for the parent collection and one query per parent row for related child data — is not visible in the ORM method calls that produce it: project.members.all() looks like a single attribute access and does not indicate that it will generate a separate SQL query per project row in the iteration context. The N+1 visibility surface is the set of code paths where the pattern is invisible to the developer during implementation and testing: code paths where related objects are accessed inside loops, code paths where the test fixture has fewer than 30 parent rows, and code paths where the developer tooling (Debug Toolbar, query logger) is disabled or produces output that is not reviewed. Closing the N+1 visibility surface requires specifying a query profiling model as part of the ORM adoption decision: which tools report query count per request (and which are enabled by default in all developer environments), what test dataset size is required for ORM-backed integration tests (at minimum 50 parent rows to make N+1 patterns visible by query count), and what query count assertions are required for each endpoint category. The query profiling model is not a monitoring decision — it is a development practice decision that must be made when the ORM is adopted, not after the first N+1 incident. Connect this property to the ORM decision record: the ORM selection — Active Record, Django ORM, Hibernate, Sequelize — determines the N+1 query surface; different ORMs have different conventions for eager loading (Django's select_related/prefetch_related, Rails' includes, JPA's @ManyToOne(fetch = FetchType.EAGER)), and the ORM decision record should specify the eager loading convention to use for each relationship type and the enforcement mechanism (linter rule, code review checklist item, or query count assertion) that prevents lazy-loaded relationships on high-cardinality collections from merging.

Property 2: The slow query threshold calibration and the regression detection gap. The slow query log captures the tail of the query execution time distribution — the queries that exceed the configured threshold. Its value as a regression detection tool depends entirely on the gap between the threshold and the application's user-visible latency threshold. A threshold of 2.0 seconds means that a query degrading from 150 ms to 1,800 ms — a 12× regression that crosses every common page latency budget — generates zero log entries at any point in the regression. The regression detection gap is the latency range [acceptable performance, slow query threshold] within which queries can degrade indefinitely. At a 2.0-second threshold, the gap is approximately 1,700–1,900 ms for typical web application queries, large enough to contain the entire user-visible degradation range. Calibrating the threshold against the latency SLO closes the gap: for an interactive endpoint with a 300 ms P99 SLO, the slow query threshold for queries on that endpoint's critical path should be set at 100–150 ms, so that query regressions are visible in the log before they consume more than half the endpoint's latency budget. For MySQL, set long_query_time = 0.1 in my.cnf. For PostgreSQL, set log_min_duration_statement = 100 in postgresql.conf. The high log volume at 100 ms is addressed by aggregation: pg_stat_statements for PostgreSQL and pt-query-digest for MySQL aggregate slow queries by fingerprint and report by total execution time contribution, making high-frequency medium-slow queries (a 120 ms query running 100,000 times per day) more visible than low-frequency catastrophic queries (a 45-second query running once per day). Connect this property to the observability sampling decision record: the slow query log at 100 ms produces the same aggregate volume questions as a high-frequency trace sampling decision — the answer is the same: aggregate by query fingerprint, report by total contribution, and set alert thresholds on the aggregate metrics (total_exec_time per fingerprint per hour, calls per fingerprint per minute) rather than on individual log entries. Connect to the alerting threshold decision record: the slow query log threshold and the endpoint latency alert threshold are two distinct thresholds that should be calibrated together — the slow query threshold is set to detect regressions before they push the endpoint over the latency alert threshold, so that the on-call engineer sees a slow query alert before seeing an endpoint latency SLO breach alert, and can identify the root cause (a specific query fingerprint) rather than only the symptom (elevated P99 on an endpoint that runs dozens of distinct queries).

Property 3: The query plan statistics model and the execution plan drift surface. The PostgreSQL query planner selects an execution plan by estimating the number of rows that each plan node will process and computing the estimated cost of each candidate plan; the plan with the lowest estimated cost is executed. The row count estimates come from the column statistics stored in pg_statistic — specifically, from a histogram of each column's value distribution, a list of the most common values (MCVs) and their frequencies, and the estimated number of distinct values (n_distinct). The default statistics target of 100 produces a histogram with approximately 100 buckets and stores up to 100 most common values. For a column with a uniform value distribution (a UUID), 100 buckets and 100 MCVs provide adequate resolution. For a column with a skewed distribution (a status column where 95% of rows have the same value, or an account_id column where one enterprise account has 100× the rows of a typical account), 100 buckets is insufficient to represent the distribution accurately, and the planner's row count estimates will diverge systematically for the extreme values in the distribution. The execution plan drift surface is the set of queries whose optimal plan depends on accurate row count statistics for a column with a non-uniform distribution: queries that filter on a high-cardinality column with skewed distribution, queries that join on a column whose value distribution changes as new data arrives, and queries whose intermediate result set size estimation affects join order selection. The drift surface grows as the table grows, as new high-value customers onboard, and as data distribution shifts seasonally — none of which is visible to the query planner unless statistics are refreshed with sufficient resolution. Closing the drift surface requires specifying the statistics model as part of the query optimization decision: which columns have non-uniform distributions (identify from the distribution shape in pg_stats — columns where the most_common_vals list contains one value with frequency above 0.5 are candidates), what statistics target to set for each (500 is a practical upper bound that rarely needs to be exceeded), and what autovacuum scale factor to use for high-growth tables (0.01 rather than the default 0.2 for tables that grow more than 5% of their total row count per week). Connect this property to the database indexing strategy decision record: index selection and statistics quality are co-dependent — an index on (entity_id, entry_date) is only used by the query planner if the planner's row count estimate for the entity_id = 412 predicate is high enough that the index scan's random I/O cost is lower than a sequential scan's sequential I/O cost; when the statistics estimate 2,400 rows but 840,000 exist, the index exists but is not used; the indexing strategy decision should specify not only which indexes to create but also which column statistics targets are required to ensure the indexes are used correctly by the planner. Connect to the WhyChose extractor: the query optimization decisions are buried in architecture sessions where teams chose ORMs without profiling policies, in database setup sessions where slow query thresholds were configured without latency SLO context, and in performance incident retrospectives where the root cause was a statistics target nobody had specified. The extractor surfaces these decisions from your AI chat history before the next incident reveals their absence.

The query optimization decision ADR: five sections

Section 1: Query profiling model and N+1 prevention contract. Specify the query profiling model as a set of enforceable development practices rather than as aspirational guidance. The model has three components. (1) Developer tooling: name the tool that reports SQL query count per request during local development (Django Debug Toolbar, Rails Bullet gem, Hibernate Statistics JMX bean, or a custom query logging middleware) and specify that it must be enabled by default in all developer environments — it should not require any per-developer configuration to activate. A tool that is optional will be disabled by the engineers who find it noisy, who are the same engineers most likely to introduce N+1 patterns. (2) Test dataset size requirement: specify the minimum number of parent rows required in any integration test that accesses a one-to-many or many-to-many relationship through the ORM. The minimum is 50 parent rows, which produces N+1 patterns with 50 to 500 queries depending on the relationship depth. Test fixtures with fewer than 50 rows should be treated as insufficient for ORM-backed endpoint tests. (3) Query count assertions: specify that each endpoint integration test must assert the total number of SQL queries executed, using the framework's query count assertion mechanism (Django's assertNumQueries, Rails' assert_queries, or a custom middleware counter). The assertion should specify the maximum acceptable query count — typically 3–5 for a view that fetches one primary collection with one or two related collections — and fail if the count exceeds the bound at the required test dataset size. A query count assertion at 12-row dataset size is not sufficient: the assertion must be verified at 50+ rows to catch N+1 patterns that scale with N. Connect to the database connection pooling decision record: N+1 queries at production scale can exhaust the connection pool — a view that generates 1,200 queries per page load against a 20-connection pool will hold connections open for the duration of the query loop; at 50 concurrent users, this can saturate the pool and produce connection queue latency that is diagnosed as "database overload" rather than as N+1 query pattern; the connection pool sizing decision should account for the maximum query count per request on high-traffic endpoints, not assume that each request executes a fixed small number of queries.

Section 2: Slow query log threshold and regression monitoring specification. Specify the slow query log threshold as a derived value from the application's latency SLO, not as a configuration default or a heuristic. The derivation procedure: (1) identify the latency SLO for each major endpoint category — interactive user-facing endpoints, background processing endpoints, and reporting/analytics endpoints; (2) estimate the maximum query budget as one-third of the interactive endpoint SLO (leaving room for application logic, serialization, and network); (3) set the slow query log threshold at the query budget. For most interactive SaaS applications, this produces a threshold in the 100–200 ms range. Set log_min_duration_statement = 100 for PostgreSQL and long_query_time = 0.1 for MySQL. Document the threshold value, the SLO it was derived from, and the date it was set so that future engineers reviewing the configuration understand why the value is what it is and know to revise it if the SLO changes. Specify the slow query aggregation tool (pg_stat_statements for PostgreSQL, pt-query-digest for MySQL) and the alert condition: alert when any query fingerprint's total_exec_time per hour exceeds a threshold that represents 10% of the endpoint latency SLO budget × the expected request rate. For a 300 ms SLO and 10,000 requests per hour, alert when a single fingerprint contributes more than 300 ms × 0.10 × 10,000 = 300 seconds of total query time per hour. This alert fires for a 120 ms query that runs 3,000 times per hour (360 seconds total) but not for a 120 ms query that runs 100 times (12 seconds total — acceptable). Connect to the alerting threshold decision record: the slow query alert threshold is one of the most important latency-related thresholds in the system — it determines whether query regressions are detected by the engineering team (proactively, before customer escalations) or by customers (reactively, after the regression has accumulated). The alerting threshold decision should document the slow query alert threshold alongside the endpoint latency SLO breach threshold, specifying that the slow query alert should fire at a lower latency value than the SLO breach alert so that the engineering team has time to investigate and fix the regression before the SLO is violated.

Section 3: Query plan baseline and statistics monitoring specification. For each query that runs more than 1,000 times per day or that is on the critical path of a revenue-generating operation, specify a query plan baseline: store the EXPLAIN output (not EXPLAIN ANALYZE — use the non-executing form to avoid production impact) in version control alongside the query definition. The baseline is stored at table creation time, when the initial optimization is verified. The baseline is compared against the current EXPLAIN output in a weekly automated check that runs EXPLAIN for each baselined query against the production database schema (not a copy) and compares the node type and join type of each plan node against the baseline. A plan change — from Index Scan to Seq Scan, from Nested Loop to Hash Join — triggers an alert. The alert does not mean the query is broken; it means the planner has chosen a different plan, which may be correct (the table statistics correctly reflect a new distribution that makes the new plan optimal) or incorrect (statistics have drifted and the planner's row estimate is wrong). The responding engineer runs EXPLAIN (ANALYZE, BUFFERS) in a staging environment to verify whether the new plan produces acceptable performance. Document the columns that require a non-default statistics target and the rationale: for each column in a high-value query's WHERE clause or JOIN condition, record the statistics target set, the column's distribution type (uniform, skewed, multi-modal), and the estimated row count accuracy before and after the target change. This record makes it possible for a future engineer to understand why statistics = 500 was set for a specific column when reviewing the table definition, and to make an informed decision about whether to maintain or remove the target as the data distribution evolves. Connect to the database indexing strategy decision record: the index design and the statistics model are co-dependent; an index that is designed for a specific query predicate must be accompanied by a statistics configuration that ensures the planner will use the index under the full range of expected data volumes and distributions, not only at the initial data volume when the index was created and tested.

Section 4: High-growth table statistics freshness specification. For tables that grow more than 5% of their total row count per week, the default autovacuum autovacuum_analyze_scale_factor = 0.2 produces statistics that are up to weeks or months stale between ANALYZE runs. Specify a per-table autovacuum override for high-growth tables: set autovacuum_analyze_scale_factor = 0.01 and autovacuum_analyze_threshold = 1000 for any table that receives more than 100,000 row insertions per day. These values cause ANALYZE to run after 1% of the table's rows have been modified — for a 10 million row table, ANALYZE runs after 100,000 changes, or approximately once per day at the 100,000 insertions per day rate, rather than after 2 million changes (20%), which at the same rate would require 20 days between ANALYZE runs. Set the per-table autovacuum override using a storage parameter in the DDL migration that creates the table: ALTER TABLE journal_entries SET (autovacuum_analyze_scale_factor = 0.01, autovacuum_analyze_threshold = 1000);. Document the override in the migration with the growth rate assumption: "Set at 2026-09-26 for a table growing approximately 600,000 rows per day; at this rate the default scale_factor of 0.2 would run ANALYZE every 130 days. Re-evaluate if growth rate changes by more than 5×." In addition to autovacuum tuning, schedule a manual ANALYZE on high-growth tables after each bulk data load operation that inserts more than 1% of the table's current row count. Bulk loads (historical data migrations, customer onboardings with data imports) are exactly the operations most likely to shift the data distribution enough to invalidate the planner's statistics, and they occur at irregular intervals that autovacuum's threshold-based scheduling does not anticipate. Connect to the data pipeline decision record: the data pipeline that performs bulk inserts into high-growth tables should include an ANALYZE step as part of the pipeline's post-load procedure — the same pipeline step that verifies row counts, checks for constraint violations, and updates materialized views should also run ANALYZE on each table that received a significant number of new rows, ensuring that the query planner's statistics are updated before the first post-load query runs.

Section 5: Query optimization documentation standard and regression review cadence. Specify the documentation standard for query optimization decisions: any query change that achieves a latency improvement greater than 50 ms at P99 (measured in a staging environment with production-scale data) must be documented with a before/after EXPLAIN (ANALYZE, BUFFERS) output, the dataset size at which the optimization was tested, the specific index or query rewrite that produced the improvement, and a reference to the root cause (N+1 pattern, missing composite index, statistics drift, or ORM query generation issue). This documentation requirement serves three purposes: it creates a record that future engineers can consult when the optimized query regresses, it enforces testing against production-scale data by making the dataset size a required field, and it provides the baseline EXPLAIN output that the plan baseline comparison can use to detect future plan changes. Establish a monthly query performance review: using pg_stat_statements or pt-query-digest, identify the top 10 queries by total execution time contribution, the top 5 queries by mean execution time increase over the prior month, and any query whose plan changed in the prior month per the weekly baseline comparison. Review these queries in a 30-minute meeting, document any optimization actions taken, and update the query plan baselines for any queries whose plan changed to a better-performing plan. The monthly review prevents the accumulation of sub-threshold regressions: a query that increases mean execution time by 8% per month will double in execution time in nine months while never triggering any per-query alert, but will appear consistently in the month-over-month comparison. Connect to the CI/CD pipeline decision record: the query profiling model — query count assertions, minimum test dataset size, and plan baseline comparison — belongs in the CI pipeline as executable quality gates, not in documentation as aspirational practices; a CI job that runs the full integration test suite against a 50+ row dataset fixture with query count assertions enabled and that compares EXPLAIN outputs for critical queries against their stored baselines converts the query optimization decision record from a document that describes what should happen into a system that enforces it on every merge.

FAQ

Further reading

• ORM decision record — the ORM abstraction selection, the lazy-loading default behavior, and the N+1 query surface that ORMs create when related objects are accessed inside iteration loops without explicit eager loading; the ORM decision record should specify the eager loading convention for each relationship type and the enforcement mechanism that prevents lazy-loaded relationships on high-cardinality collections from merging.
• Database indexing strategy decision record — the index design, the composite index column ordering, and the covering index selection that determine whether high-value queries use index scans rather than sequential scans; index design and query planner statistics are co-dependent: an index created for a specific query predicate is only used if the planner's row count estimate for that predicate is accurate enough to make the index scan cost lower than the sequential scan cost.
• Database connection pooling decision record — the connection pool sizing, the checkout timeout, and the connection lifecycle that determine whether N+1 query patterns at production scale saturate the pool; a view that generates 1,200 queries per page load holds connections open for the full query loop duration, and at 50 concurrent users on a 20-connection pool, this produces connection queue latency that manifests as "database overload" rather than as an N+1 pattern.
• Observability sampling decision record — the trace sampling rate, the query log aggregation model, and the slow query detection granularity that determine whether query regressions are visible before they accumulate user-visible latency; slow query aggregation by fingerprint and total execution time contribution is the same sampling-and-aggregation problem as distributed trace sampling, and the same principle applies: aggregate by template, alert on contribution, and use individual entries for debugging only.
• Alerting threshold decision record — the slow query alert threshold, the endpoint latency SLO breach threshold, and the relationship between them; the slow query threshold should be calibrated to fire before the endpoint latency SLO breach threshold fires, giving the engineering team the opportunity to identify and fix a specific query regression before it pushes the endpoint over its SLO.
• Open-source extractor — find the query optimization decisions buried in your AI chat history: the architecture session where the ORM was adopted without a profiling model, the database configuration session where the slow query threshold was set without SLO context, and the performance incident retrospective where the root cause was a statistics target and autovacuum scale factor that nobody had ever specified as decisions.