The post has been translated automatically. Original language: Russian
Taken from the main technical channel of Postgres DBA (edits are possible in the original article).
GitHub - pg_expecto complex for statistical performance analysis and load testing of PostgreSQL DBMS
Philosophical_instruction_BETA_v5.1.md - Philosophical core + procedural skeleton of an autonomous AI agent with built-in self-verification. Epistemology, ethics of honesty, scientific method, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Sins). Maximum truthfulness, protection from hallucinations, and prompt injection.
A collection of promptes for a neural networkDeepSeek (in Russian), designed to analyze and optimize the performance of PostgreSQL databases.
GitFlic - pg_expecto - statistical analysis of PostgreSQL database performance and expectations
Glossary of terms | Postgres DBA | Zen
Learn more about the philosophical instruction and the PG_EXPECTO instruction
Detailed description of the instruction "Philosophical Instruction v5.1" | Postgres DBA | Zen
Traffic Lights in Philosophical Instruction v5.1 | Postgres DBA | Zen
Confidence levels in the pg_expecto instruction.1.1 | Postgres DBA | Zen
"Epistemic Protocol" and "Domain Methodology" | Postgres DBA | Zen
Introduction: initial data and tools
It all started with a planned upgrade of the productive 1C server.:Enterprise" from PostgreSQL 15.14.1 to 17.9.2 based on Postgres Pro Enterprise. After migration, monitoring showed an abnormal increase in disk subsystem utilization (PGDATA) and a sharp increase in query execution time. The following methods were used for diagnosis:
- pgpro_pwr is an extension for collecting and analyzing PostgreSQL performance statistics that generates detailed reports on load, expectations, and query plans;
- PG_EXPECTO is a domain—specific analysis methodology that prescribes checking the internal consistency of metrics, separating confirmed facts and hypotheses, and explicitly indicating the limits of applicability of conclusions.;
- philosophical instructions for the DeepSeek neural network model, ensuring epistemic honesty: each conclusion was provided with a level of confidence ("Confirmed by data", "Likely, but requires verification", "Assumption", "Impossible to estimate").
The analysis covered several time slices, from the first hours after the upgrade to later periods when new problems appeared. All periods had the same duration (one hour, 9:00-10:00), which made it possible to directly compare the absolute values of the metrics.
1. Starting point: alarming numbers after the upgrade
A comparison of the two pgpro_pwr reports — before the update (PG 15) and after (PG 17) — revealed a picture that was far from expected.:
- The total number of completed requests increased by 20% (from 33.7 million to 40.4 million);
- however, the total execution time jumped 2.6 times (from ~5,094 s to ~13,462 s);
- the processor time (the sum of user + system) increased by more than 5 times — from ~5,550 s to ~31,134 s;
- The peak number of sessions in the new period reached 1,317 (compared to 435 previously), while the max_connections parameter was set to an extreme 5,000.
The configuration parameters affecting caching and access costs (shared_buffers = 55 GB, efficie_cache_size = 165 GB, random_page_cost = 1.1) remained unchanged. But there were also differences.: work_mem doubled (from 32 MB to 64 MB), and max_parallel_workers_per_gather was apparently disabled or close to zero in both cases.
Problematic queries have retained their patterns: frequent calls to SELECT FASTTRUNCATE to clear temporary tables (up to 1.2 million times per hour), operations with the BinaryData table (reading and inserting blobs), active use of temporary files (16 GB in the new period). But their "price" in terms of processor time has increased dramatically. So, the FASTTRUNCATE query alone consumed 37.7% of the total CPU in the new period, although it was called 2.4 times less often than in the old one.
Primary output (Level‑2):
Degradation is most likely related to a change in the behavior of the optimizer or competition for resources against the background of an increased number of sessions. But an accurate answer required an analysis of specific implementation plans.
2. SQL Preparation: indexes, call frequency, and I/O waits
The next step is a detailed analysis of the five queries that led in execution time and the 12 queries that dominated in I/O waiting time. Since the Query IDs changed after the update, we had to look for text matches between the reports.
Findings on lead time :
- Request from _InfoRg12488 (LIMIT + ORDER BY): the plan has improved — instead of Bitmap Heap Scan, direct Index Scan has become more common. The average time per call decreased from 255 ms to 19.5 ms, but the frequency of calls increased 39 times (from 777 to 30 185). The total time has increased, but not critically.
- Request with _Reference109 (OR by two fields): the frequency increased 105 times (from 15 to 1,580), the Seq Scan plan remained, and the query became the main consumer of the CPU (1,197 with user_time).
- Requests from _InfoRg15516 and _Reference15235 showed catastrophic growth: with the same Seq Scan and a small number of calls, the average time soared from 0.33 seconds to 20.9 seconds and from 37 ms to 23.7 seconds, respectively. The reason is the multiple growth of data volumes in the absence of indexes for filtered fields.
Findings on I/O expectations:
Of the 12 analyzed queries, 11 showed an increase in DataFileRead time from 1.6 to 32 times with the same or even reduced number of calls. The execution plans for them have not changed. For example:
- INSERT in _InfoRg16813: with a 12% reduction in the number of calls, the total time increased 2.5 times, and the I/O time increased from 27.4 seconds to 84.2 seconds;
- INSERT in _InfoRg12488: the total time increased 5 times (from 4.5 seconds to 22.9 seconds) with almost the same number of calls;
- SELECT from _Reference109 by primary key: in the new period, the optimizer switched to a less efficient index (_reference109_no_pkey instead of _reference109ng_pkey1), which led to an increase in read blocks from 2,292 to 34,526 and I/O time from 0.11 s to 28.49 s.
Only one request (SELECT from _InfoRg13163) showed no degradation — it was fully serviced from the cache (I/O time = 0 in both periods).
Output (Level‑1):
A key factor in the slowdown is the multiple growth of physical disk reads. The plans have basically not changed, which means that the reason is not the regression of the optimizer, but the changed execution conditions. But why did the plans "float" for individual requests? The next stage gave the answer.
3. Turning point: cache_resets +3300%
A study of the "Load distribution among heavily loaded databases" section revealed an anomaly that redefined the direction of the investigation. The cache_resets (shared cache flushes) counter in the pgpro_stats_totals representation increased from 1 to 34 events per hour, i.e. by 3,300%. At the same time:
- Input/output time (I/O time) increased by 2310%;
- volume of physical reads (Shared blocks read) — by 248%;
- total execution time — by 536%, while reducing the number of requests by 10%.
The cache_resets metric records forced purges of the plan cache (and/or buffers) caused by events such as DISCARD ALL, ALTER SYSTEM, SET, DDL commands, or emergency restarts of serving processes. Each reset resets the accumulated statistics and forces the planner to make plans anew, often based on outdated data. Hypothesis No. 3 arose: something in the new configuration causes the server to regularly lose the cache of plans.
4. Double verification: autoprepare_threshold and online_analyze
A comparison of all configuration parameters between the old and new periods revealed three key differences, not counting the already known ones:
- autoprepare_threshold: 0 → 2 (automatic preparation of general plans is enabled);
- online_analyze.enable: on → off (automatic updating of statistics during DML operations is disabled);
- generic_plan_fuzz_factor: 1 → 0.9.
To confirm their involvement, a double analysis was performed using DeepSeek and the specialized neural network "Ask Postgres". Both systems independently arrived at the same mechanism.:
- With autoprepare_threshold = 2, the server starts caching generic plans in the local memory of each serving process (backend) after the second execution of the same query template with different literals. With a value of 0, the cache was empty, and there was nothing to reset.
- With online_analyze.enable = off, table statistics stopped updating immediately after INSERT/UPDATE/DELETE. Now it is updated only by the background autovacuum, and massively — when the threshold of changes accumulates (by default, 10% of the table + 50 rows).
- Each such ANALYZE changes the contents of the pg_statistic system directory, which for PostgreSQL serves as a trigger for disabling all cached plans that depend on modified tables. As a result, the cache filled with autoprepare_threshold is periodically completely cleared — hence the explosive growth of cache_resets.
- generic_plan_fuzz_factor = 0.9 adds a "tolerance" when comparing the cost of general and specialized plans, making the planner more conservative. Combined with outdated statistics, this means that the only way to get a new plan is to completely reset the cache.
Thus, activating auto-provisioning created the reset facility itself, and disabling online_analyze provided a regular trigger for them. But the question remained: why did the scheduler choose deliberately suboptimal sequential scans after resetting the cache? The answer was found in another new parameter of PostgreSQL 17.
5. Breakthrough: planner_upper_limit_estimation
The planner_upper_limit_estimation (boolean) parameter, which appeared in Postgres Pro 17, is off by default. However, in the configuration after the upgrade, it turned out to be on. According to the documentation, it "enables the query scheduler to overestimate the expected number of lines in expressions containing comparisons with an unknown constant." Simply put, for conditions for which there are no statistics, the planner applied a correction factor, artificially lowering the selectivity.
Full-scale experiments were performed to test the impact (Upgrade PostgreSQL 15→17: possible cause of cache_resets - planner_upper_limit_estimation = on, Upgrade PostgreSQL 15→17: Analysis of execution plans with planner_upper_limit_estimation = on/off , Upgrade PostgreSQL 15→17 : Hypothesis about the impact of planner_upper_limit_estimation ): the parameter was disabled and two new time zones were removed the report (periods 69-71 and 74-76). The results exceeded all expectations:
- Total time — from 76,563 s to 19,963 s (-73.9%);
- I/O time — from 45,356 s to 7,962 s (-82.4%);
- Blocks fetched — from 4.36 billion to 2.04 billion (-53.1%);
- Shared read blocks — from 532 million to 155 million (-70.9%);
- Cache resets — from 289 to 10 (-96.5%);
- Executed count — from 11.95 million to 13.43 million (+12.4%).
A comparison of execution plans showed that when on, Seq Scan was used for the key query to _InfoRg12488, and after switching to off, Bitmap Heap Scan and Index Scan were used. The share of sequential scans in the total number of access operations decreased from 19.2% to 16.5%. At the same time, the number of index scans remained virtually unchanged, but the number of rows returned through indexes dropped sharply (IxFet — by 43.9%). This meant that the scheduler began to estimate the cost more accurately and refused to make redundant readings.
Output (Level‑1): planner_upper_limit_estimation = on was the main culprit of the initial degradation. He forced the planner to systematically underestimate the selectivity of conditions without statistics, choosing suboptimal sequential scans. Combined with frequent cache flushes (due to autoprepare_threshold and online_analyze), this created a vicious circle: statistics were lost → scheduler was wrong → data was read from disk → cache was flushed even faster.
6. New challenge: drop in hit ratio with unchanged configuration
After disabling the problematic parameters, the acute phase was removed, but after a while, abnormal disk utilization was again recorded on the same server (Upgrade PostgreSQL 15→17: how disabling online_analyze and resetting statistics brought down the cache hit and generated Seq Scan, PostgreSQL + DeepSeek: how the analysis of hit ratio and wait events revealed the cause of abnormal disk disposal). A comparison of the periods 165-166 and 170-171 (already on PG 17 with planner_upper_limit_estimation = off) showed a different picture: with a completely identical configuration and stable query plans (12 of the 13 main queries retained plans),:
- The cluster's hit ratio dropped from 95.2% to 86.3% (on DB‑4— from 95.1% to 86.2%);
- DataFileRead's expectations increased 3.9 times and began to occupy 63.8% of all expectations (5,828 seconds versus 1,479 seconds);
- BufferIo expectations (competition of processes for the same buffer pages) soared 126 times (from 2.75 s to 347.9 s);
- The number of completed requests increased by 39% (from 4.74 million to 6.61 million), and the total time increased by 4.3 times (from 6,228 seconds to 26,856 seconds);
- The volume of temporary files (Temp blocks written) increased by 82%.
At the same time, the size of shared_buffers remained unchanged — about 12.3 GB. The working data set grew and stopped being placed in the buffer cache, which led to massive page displacement and physical disk reads. Plans have not changed — the slowdown was caused solely by a shortage of buffer pool.
Additionally, a 32 MB work_mem with a maximum number of 1,000 connections created the risk that many simultaneous sorts and hash connections would be dumped to disk in temporary files. The 82% increase in Temp blocks written indirectly confirmed this hypothesis.
Thus, the second wave of problems was of a fundamentally different nature: not a scheduler error, but a depletion of resources with increasing workload.
7. Methodological experiment: How prompta's formulation drives conclusions
A side but important study deserves special attention (Analysis of PostgreSQL SQL query execution plans using DeepSeek and philosophical instructions: a case study of reducing the hit ratio from 93.5% to 81.8%,DeepSeek + PostgreSQL: a study of how a small edit in the prompt changes the interpretation of query plans and load). It concerned the influence of the semantics of instructions for the DeepSeek neural network on the analysis results. Two versions of prompta were compared:
- v1: contained a strict assumption "the nature of the load has not changed dramatically";
- v2: without such an assumption, more neutral.
The results showed that v1 systematically inclined the model to confirm a previously hypothesized hypothesis, ignoring or belittling alternative explanations. For example, for query _InfoRg13163, report v1 interpreted the changes as a moderate improvement, while v2 recorded that the Seq Scan that was present in the old period disappeared in the new one — that is, the plan formally improved, but v1 did not note this, focusing on confirming the overall degradation.
Conclusion: the priming effect is real, and when using AI assistants in diagnostics, it is critically important to test prompta for stability and avoid implicit assumptions.
8. The final picture and practical recommendations
The investigation revealed that the abnormal disk disposal after migration was caused by two independent but overlapping problems.
Problem 1: Scheduler error (immediately after upgrade)
- The root cause: planner_upper_limit_estimation = on — overestimation of selectivity for conditions without statistics, which led to the choice of Seq Scan.
- Aggravating factors: autoprepare_threshold = 2 created a cache of plans, online_analyze.enable = off caused massive ANALYSIS and, as a result, frequent flushes of this cache (cache_resets +3300%). generic_plan_fuzz_factor = 0.9 increased the effect.
- The solution:Set planner_upper_limit_estimation = off.Enable online_analyze.enable or carefully adjust the autovacuum_analyze_scale_factor and autovacuum_analyze_threshold thresholds to minimize the lifetime of outdated statistics.Consider increasing autoprepare_threshold (for example, to 10) so that only really frequent requests get into the cache.
- Status: confirmed by direct measurements (Level‑1).
Problem 2: Resource depletion with increasing load (delayed)
- The root cause: The fixed shared_buffers (~12.3 GB) stopped accommodating the working dataset, which led to a drop in the hit ratio from 95.2% to 86.3% and an explosive increase in physical reads.
- Related factors: work_mem = 32 MB with high competitiveness (max_connections = 1000) caused the active use of temporary files; the increase in data volumes increased WAL generation.
- The solution:Increase shared_buffers based on available RAM (for example, up to 24-36 GB, if the hardware allows).Consider increasing the work_mem (for example, to 64-128 MB), but with a cautious estimate of peak memory consumption: with 1,000 simultaneous active requests, increasing the work_mem by 32 MB may require an additional 32 GB of RAM in the worst case. It is recommended to monitor actual usage and possibly limit concurrency at the connection pool level.Update the efficie_cache_size proportionally to the new shared_buffers (usually ~75% of the total RAM).Ensure regular monitoring of hit ratio, wait events, and temporary files to prevent repeated degradation.
- Status: the hit ratio and expectations are confirmed by direct measurements (Level‑1); the role of work_mem is probable (Level‑2).
9. Migration Lessons
- New parameters are a risk area. The scheduler settings (planner_upper_limit_estimation) introduced in the new version should not be accepted "as is", even if they are recommended by the documentation. Mandatory load testing on copies of a productive environment with profiling of query plans and I/O system metrics.
- autoprepare_threshold and online_analyze are a dangerous combination. If you enable caching of shared plans, make sure that statistics are updated frequently enough to avoid an avalanche of cache_resets. Disabling online_analyze in high-load systems requires compensation through aggressive autovacuum.
- pgpro_pwr + PG_EXPECTO + neural network = powerful tool, but requires discipline. The formulations of the suggestions affect the objectivity of the conclusions — cross-testing of hypotheses and explicit fixation of confidence levels are necessary.
- Insufficient buffer cache can simulate version regression. The growth of work data requires periodic review of shared_buffers and work_mem, especially in systems without regular reconfiguration.
- Look at the system as a whole. The problem rarely has a single cause. In our case, several factors came together at once: a new scheduler parameter, disabled statistics, turned on auto—training, and later - just a lack of memory for the increased load.
Conclusion
The history of this investigation is a good example of how systematic metric analysis, step—by-step hypothesis testing, and the competent use of automated tools make it possible to get to the bottom of the true causes of degradation. It turned out that "the new version is to blame for everything" is too gross an oversimplification. The real picture included several layered factors, each of which required its own detection method.
It was an integrated approach — from high—level profiles to point comparison of plans, from configuration parameters to waiting events and hit ratio - that allowed not only to find the root of the problem, but also to develop specific, working recommendations.
Limitations. The presented conclusions are based on data from a specific installation (Postgres Pro Enterprise, 1C platform). The absolute values of the parameters (shared_buffers, work_mem) are given for illustration purposes and should adapt to your equipment and load profile. All recommendations of the "Probably" level require verification in a test environment before being implemented in a product.
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Philosophical_instruction_BETA_v5.1.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.
Коллекция промптов для нейросети DeepSeek (на русском языке), предназначенных для анализа и оптимизации производительности СУБД PostgreSQL.
GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Подробнее о философской инструкции и инструкции PG_EXPECTO
Подробное описание инструкции "Philosophical Instruction v5.1" | Postgres DBA | Дзен
Светофоры достоверности (Traffic Lights) в Philosophical Instruction v5.1 | Postgres DBA | Дзен
Уровни уверенности в инструкции pg_expecto.1.1 | Postgres DBA | Дзен
«Эпистемический протокол» и «Доменная методология» | Postgres DBA | Дзен
Введение: исходные данные и инструментарий
Всё началось с планового обновления продуктивного сервера «1С:Предприятие» с PostgreSQL 15.14.1 до 17.9.2 на базе Postgres Pro Enterprise. После миграции мониторинг показал аномальный рост утилизации дисковой подсистемы (PGDATA) и резкое увеличение времени выполнения запросов. Для диагностики использовались:
- pgpro_pwr — расширение для сбора и анализа статистики производительности PostgreSQL, формирующее детальные отчёты по нагрузке, ожиданиям, планам запросов;
- PG_EXPECTO — доменная методология анализа, предписывающая проверять внутреннюю согласованность метрик, разделять подтверждённые факты и гипотезы, явно указывать границы применимости выводов;
- философская инструкция для нейросетевой модели DeepSeek, обеспечивающая эпистемическую честность: каждый вывод снабжался уровнем достоверности («Подтверждено данными», «Вероятно, но требует проверки», «Предположение», «Невозможно оценить»).
Анализ охватил несколько временных срезов — от первых часов после апгрейда до более поздних периодов, когда проявились новые проблемы. Все периоды имели одинаковую длительность (один час, 9:00–10:00), что позволяло прямо сопоставлять абсолютные значения метрик.
1. Исходная точка: тревожные цифры после апгрейда
Сравнение двух отчётов pgpro_pwr — до обновления (PG 15) и после (PG 17) — выявило картину, далёкую от ожидаемой:
- общее количество выполненных запросов выросло на 20% (с 33,7 млн до 40,4 млн);
- однако суммарное время выполнения подскочило в 2,6 раза (с ~5 094 с до ~13 462 с);
- процессорное время (сумма user + system) увеличилось более чем в 5 раз — с ~5 550 с до ~31 134 с;
- пиковое число сессий в новом периоде достигало 1 317 (против 435 ранее), при этом параметр max_connections был установлен в экстремальные 5 000.
Конфигурационные параметры, влияющие на кэширование и стоимость доступа (shared_buffers = 55 ГБ, effective_cache_size = 165 ГБ, random_page_cost = 1.1), остались неизменными. Но были и различия: work_mem удвоился (с 32 МБ до 64 МБ), а max_parallel_workers_per_gather в обоих случаях, по-видимому, был отключён или близок к нулю.
Проблемные запросы сохранили свои паттерны: частые вызовы SELECT FASTTRUNCATE для очистки временных таблиц (до 1,2 млн раз в час), операции с таблицей BinaryData (чтение и вставка BLOB-объектов), активное использование временных файлов (16 ГБ в новом периоде). Но их «цена» в терминах процессорного времени резко возросла. Так, один только запрос FASTTRUNCATE в новом периоде потреблял 37,7% всего CPU, хотя вызывался в 2,4 раза реже, чем в старом.
Первичный вывод (Уровень‑2):
Деградация, скорее всего, связана с изменением поведения оптимизатора или конкуренцией за ресурсы на фоне возросшего числа сессий. Но для точного ответа требовался анализ конкретных планов выполнения.
2. Препарирование SQL: индексы, частота вызовов и ожидания I/O
Следующий шаг — детальный анализ пяти запросов, лидировавших по времени выполнения, и 12 запросов, доминировавших по времени ожидания ввода-вывода. Поскольку после обновления идентификаторы Query ID изменились, пришлось искать текстовые соответствия между отчётами.
Находки по времени выполнения :
- Запрос с _InfoRg12488 (LIMIT + ORDER BY): план улучшился — вместо Bitmap Heap Scan стал чаще выбираться прямой Index Scan. Среднее время одного вызова сократилось с 255 мс до 19,5 мс, но частота вызовов выросла в 39 раз (с 777 до 30 185). Общее время увеличилось, но не критично.
- Запрос с _Reference109 (OR по двум полям): частота выросла в 105 раз (с 15 до 1 580), план остался Seq Scan, и запрос стал главным потребителем CPU (1 197 с user_time).
- Запросы с _InfoRg15516 и _Reference15235 показали катастрофический рост: при неизменном Seq Scan и небольшом числе вызовов среднее время взлетело с 0,33 с до 20,9 с и с 37 мс до 23,7 с соответственно. Причина — многократный рост объёмов данных при отсутствии индексов по фильтруемым полям.
Находки по ожиданиям I/O :
Из 12 проанализированных запросов 11 продемонстрировали рост времени DataFileRead от 1,6 до 32 раз при неизменных или даже снизившихся количествах вызовов. Планы выполнения для них не изменились. Например:
- INSERT в _InfoRg16813: при снижении числа вызовов на 12% общее время выросло в 2,5 раза, а время I/O — с 27,4 с до 84,2 с;
- INSERT в _InfoRg12488: общее время выросло в 5 раз (с 4,5 с до 22,9 с) при практически неизменном числе вызовов;
- SELECT из _Reference109 по первичному ключу: в новом периоде оптимизатор переключился на менее эффективный индекс (_reference109_no_pkey вместо _reference109ng_pkey1), что привело к росту прочитанных блоков с 2 292 до 34 526 и времени I/O с 0,11 с до 28,49 с.
Лишь один запрос (SELECT из _InfoRg13163) не показал деградации — он полностью обслуживался из кеша (I/O time = 0 в обоих периодах).
Вывод (Уровень‑1):
Ключевой фактор замедления — многократный рост физических чтений с диска. Планы в основном не изменились, значит, причина не в регрессе оптимизатора, а в изменившихся условиях выполнения. Но почему планы «поплыли» для отдельных запросов? Ответ дал следующий этап.
3. Поворотный момент: cache_resets +3300%
Изучение раздела «Load distribution among heavily loaded databases» вскрыло аномалию, которая переопределила направление расследования. Счётчик cache_resets (сбросов разделяемого кеша) в представлении pgpro_stats_totals увеличился с 1 до 34 событий за час — то есть на 3300%. Одновременно:
- время ввода-вывода (I/O time) выросло на 2310%;
- объём физических чтений (Shared blocks read) — на 248%;
- общее время выполнения (Total time) — на 536% при снижении числа запросов на 10%.
Метрика cache_resets фиксирует принудительные очистки кеша планов (и/или буферов), вызванные такими событиями, как DISCARD ALL, ALTER SYSTEM, SET, DDL-команды или аварийные перезапуски обслуживающих процессов. Каждый такой сброс обнуляет накопленную статистику и заставляет планировщика заново строить планы, часто на основе устаревших данных. Возникла гипотеза № 3: что-то в новой конфигурации заставляет сервер регулярно терять кеш планов.
4. Двойная верификация: autoprepare_threshold и online_analyze
Сравнение всех конфигурационных параметров между старым и новым периодами выявило три ключевых отличия, не считая уже известных:
- autoprepare_threshold: 0 → 2 (включена автоматическая подготовка общих планов);
- online_analyze.enable: on → off (отключено автоматическое обновление статистики при DML-операциях);
- generic_plan_fuzz_factor: 1 → 0.9.
Чтобы подтвердить их причастность, был проведён двойной анализ — с помощью DeepSeek и специализированной нейросети «Ask Postgres». Обе системы независимо пришли к одному и тому же механизму:
- При autoprepare_threshold = 2 сервер начинает кешировать общие (generic) планы в локальной памяти каждого обслуживающего процесса (бэкенда) — после второго выполнения одного и того же шаблона запроса с разными литералами. При значении 0 кеш был пуст, и сбрасывать было нечего.
- При online_analyze.enable = off статистика таблиц перестала обновляться немедленно после INSERT/UPDATE/DELETE. Теперь она обновляется только фоновым autovacuum, причём массово — когда накапливается порог изменений (по умолчанию 10% таблицы + 50 строк).
- Каждый такой ANALYZE изменяет содержимое системного каталога pg_statistic, что для PostgreSQL служит триггером инвалидации всех кешированных планов, зависящих от изменённых таблиц. В результате кеш, наполненный благодаря autoprepare_threshold, периодически полностью очищается — отсюда и взрывной рост cache_resets.
- generic_plan_fuzz_factor = 0.9 добавляет «допуск» при сравнении стоимости общего и специализированного планов, делая планировщик более консервативным. В сочетании с устаревшей статистикой это приводит к тому, что единственным способом получить новый план становится полный сброс кеша.
Таким образом, активация автоподготовки создала сам объект для сбросов, а отключение online_analyze обеспечило регулярный триггер для них. Но оставался вопрос: почему после сброса кеша планировщик стал выбирать заведомо неоптимальные последовательные сканирования? Ответ нашёлся в ещё одном новом параметре PostgreSQL 17.
5. Прорыв: planner_upper_limit_estimation
Параметр planner_upper_limit_estimation (boolean), появившийся в Postgres Pro 17, по умолчанию равен off. Однако в конфигурации после апгрейда он оказался включён (on). Согласно документации, он «включает возможность планировщика запросов завышать оценку ожидаемого количества строк в выражениях, содержащих сравнение с неизвестной константой». Проще говоря, для условий, по которым нет статистики, планировщик применял поправочный коэффициент, искусственно занижая селективность.
Для проверки влияния был поставлен натурные эксперименты (Upgrade PostgreSQL 15→17: возможная причина cache_resets - planner_upper_limit_estimation = on, Upgrade PostgreSQL 15→17: Анализ планов выполнения при planner_upper_limit_estimation = on/off , Upgrade PostgreSQL 15→17 : Гипотеза о влиянии planner_upper_limit_estimation ): параметр отключили (off) и сняли два новых часовых отчёта (периоды 69–71 и 74–76). Результаты превзошли все ожидания:
- Total time — с 76 563 с до 19 963 с (–73,9%);
- I/O time — с 45 356 с до 7 962 с (–82,4%);
- Blocks fetched — с 4,36 млрд до 2,04 млрд (–53,1%);
- Shared blocks read — с 532 млн до 155 млн (–70,9%);
- Cache resets — с 289 до 10 (–96,5%);
- Executed count — с 11,95 млн до 13,43 млн (+12,4%).
Сравнение планов выполнения показало: при on для ключевого запроса к _InfoRg12488 использовался Seq Scan, а после переключения в off — Bitmap Heap Scan и Index Scan. Доля последовательных сканирований в общем числе операций доступа снизилась с 19,2% до 16,5%. При этом количество индексных сканирований практически не изменилось, зато резко упало количество строк, возвращаемых через индексы (IxFet — на 43,9%). Это означало, что планировщик стал точнее оценивать стоимость и отказывался от избыточных чтений.
Вывод (Уровень‑1): planner_upper_limit_estimation = on был главным виновником первоначальной деградации. Он заставлял планировщика систематически недооценивать селективность условий без статистики, выбирая неоптимальные последовательные сканирования. В сочетании с частыми сбросами кеша (из-за autoprepare_threshold и online_analyze) это создавало порочный круг: статистика терялась → планировщик ошибался → данные читались с диска → кеш вымывался ещё быстрее.
6. Новый вызов: падение hit ratio при неизменной конфигурации
После отключения проблемных параметров острая фаза была снята, но через некоторое время на том же сервере вновь зафиксировали аномальную утилизацию диска (Upgrade PostgreSQL 15→17: как отключение online_analyze и сбросы статистики обрушили кэш-хит и породили Seq Scan , PostgreSQL + DeepSeek: как анализ hit ratio и wait events раскрыл причину аномальной утилизации диска ). Сравнение периодов 165–166 и 170–171 (уже на PG 17 с planner_upper_limit_estimation = off) показало иную картину: при полностью идентичной конфигурации и стабильных планах запросов (12 из 13 основных запросов сохранили планы) наблюдалось:
- Hit ratio кластера упал с 95,2% до 86,3% (на DB‑4 — с 95,1% до 86,2%);
- Ожидания DataFileRead выросли в 3,9 раза и стали занимать 63,8% всех ожиданий (5 828 с против 1 479 с);
- Ожидания BufferIo (конкуренция процессов за одни и те же буферные страницы) взлетели в 126 раз (с 2,75 с до 347,9 с);
- Количество выполненных запросов выросло на 39% (с 4,74 млн до 6,61 млн), а общее время — в 4,3 раза (с 6 228 с до 26 856 с);
- Объём временных файлов (Temp blocks written) увеличился на 82%.
Размер shared_buffers при этом оставался неизменным — около 12,3 ГБ. Рабочий набор данных вырос и перестал помещаться в буферный кеш, что привело к массовому вытеснению страниц и физическому чтению с диска. Планы не изменились — замедление было вызвано исключительно нехваткой буферного пула.
Дополнительно work_mem в 32 МБ при максимальном числе подключений 1 000 создавал риск, что множество одновременных сортировок и хеш-соединений будут сбрасываться на диск во временные файлы. Рост Temp blocks written на 82% косвенно подтверждал эту гипотезу.
Таким образом, вторая волна проблем имела принципиально иную природу: не ошибка планировщика, а истощение ресурсов при росте нагрузки.
7. Методологический эксперимент: как формулировка промпта управляет выводами
Отдельного внимания заслуживает побочное, но важное исследование ( Анализ планов выполнения SQL запросов PostgreSQL с помощью DeepSeek и философской инструкции: кейс снижения hit ratio с 93,5% до 81,8% , DeepSeek + PostgreSQL: исследование того, как малая правка в промпте меняет интерпретацию планов запросов и нагрузки ). Оно касалось влияния семантики инструкций для нейросети DeepSeek на результаты анализа. Сравнивались две версии промпта:
- v1: содержала жёсткое допущение «характер нагрузки кардинально не изменился»;
- v2: без такого допущения, более нейтральная.
Результаты показали, что v1 систематически склонял модель к подтверждению заранее выдвинутой гипотезы, игнорируя или принижая альтернативные объяснения. Например, для запроса _InfoRg13163 отчёт v1 интерпретировал изменения как умеренное улучшение, в то время как v2 зафиксировал, что Seq Scan, присутствовавший в старом периоде, исчез в новом — то есть план формально улучшился, но v1 этого не отметил, фокусируясь на подтверждении общей деградации.
Вывод: прайминг-эффект реален, и при использовании ИИ-ассистентов в диагностике критически важно тестировать промпты на устойчивость и избегать неявных предположений.
8. Итоговая картина и практические рекомендации
Расследование показало, что аномальная дисковая утилизация после миграции была вызвана двумя независимыми, но наложившимися проблемами.
Проблема 1: ошибка планировщика (сразу после апгрейда)
- Корневая причина: planner_upper_limit_estimation = on — завышение оценки селективности для условий без статистики, приводившее к выбору Seq Scan.
- Усугубляющие факторы: autoprepare_threshold = 2 создал кеш планов, online_analyze.enable = off вызывал массовые ANALYZE и, как следствие, частые сбросы этого кеша (cache_resets +3300%). generic_plan_fuzz_factor = 0.9 усилил эффект.
- Решение:Установить planner_upper_limit_estimation = off.Включить online_analyze.enable или тщательно настроить пороги autovacuum_analyze_scale_factor и autovacuum_analyze_threshold для минимизации времени жизни устаревшей статистики.Рассмотреть увеличение autoprepare_threshold (например, до 10), чтобы в кеш попадали только действительно частые запросы.
- Статус: подтверждено прямыми измерениями (Уровень‑1).
Проблема 2: истощение ресурсов при росте нагрузки (отсроченная)
- Корневая причина: фиксированный shared_buffers (~12,3 ГБ) перестал вмещать рабочий набор данных, что привело к падению hit ratio с 95,2% до 86,3% и взрывному росту физических чтений.
- Сопутствующие факторы: work_mem = 32 МБ при высокой конкурентности (max_connections = 1000) вызывал активное использование временных файлов; рост объёмов данных увеличил генерацию WAL.
- Решение:Увеличить shared_buffers с учётом доступной оперативной памяти (например, до 24–36 ГБ, если оборудование позволяет).Рассмотреть увеличение work_mem (например, до 64–128 МБ), но с осторожной оценкой пикового потребления памяти: при 1 000 одновременных активных запросов увеличение work_mem на 32 МБ может потребовать дополнительно 32 ГБ ОЗУ в худшем случае. Рекомендуется мониторинг фактического использования и, возможно, ограничение параллелизма на уровне пула соединений.Актуализировать effective_cache_size пропорционально новому shared_buffers (обычно ~75% от общего объёма ОЗУ).Обеспечить регулярный мониторинг hit ratio, wait events и временных файлов для предотвращения повторной деградации.
- Статус: hit ratio и ожидания подтверждены прямыми измерениями (Уровень‑1); роль work_mem — вероятна (Уровень‑2).
9. Уроки миграции
- Новые параметры — зона риска. Появившиеся в новой версии настройки планировщика (planner_upper_limit_estimation) не должны приниматься «как есть», даже если они рекомендованы документацией. Обязательно нагрузочное тестирование на копии продуктивной среды с профилированием планов запросов и системных метрик I/O.
- autoprepare_threshold и online_analyze — опасная комбинация. Если вы включаете кеширование общих планов, убедитесь, что статистика обновляется достаточно часто, чтобы избежать лавины cache_resets. Отключение online_analyze в высоконагруженных системах требует компенсации через агрессивный autovacuum.
- pgpro_pwr + PG_EXPECTO + нейросеть = мощный инструмент, но требующий дисциплины. Формулировки промптов влияют на объективность выводов — необходимо перекрёстное тестирование гипотез и явная фиксация уровней достоверности.
- Недостаточность буферного кеша может имитировать регрессию версии. Рост рабочих данных требует периодического пересмотра shared_buffers и work_mem, особенно в системах без регулярного реконфигурирования.
- Смотрите на систему в целом. Проблема редко имеет одну причину. В нашем случае сошлись сразу несколько факторов: новый параметр планировщика, отключённая статистика, включённая автоподготовка, а позже — просто нехватка памяти под возросшую нагрузку.
Заключение
История этого расследования — хороший пример того, как систематический анализ метрик, поэтапная проверка гипотез и грамотное привлечение автоматизированных средств позволяют докопаться до истинных причин деградации. Оказалось, что «новая версия во всём виновата» — слишком грубое упрощение. Реальная картина включила в себя несколько наслоившихся факторов, каждый из которых потребовал своего метода обнаружения.
Именно комплексный подход — от высокоуровневых профилей к точечному сравнению планов, от конфигурационных параметров к событиям ожидания и hit ratio — позволил не только найти корень проблемы, но и выработать конкретные, работающие рекомендации.
Ограничения. Представленные выводы основаны на данных конкретной инсталляции (Postgres Pro Enterprise, платформа «1С»). Абсолютные значения параметров (shared_buffers, work_mem) приведены для иллюстрации и должны адаптироваться под ваше оборудование и профиль нагрузки. Все рекомендации уровня «Вероятно» требуют проверки в тестовой среде перед внедрением в продуктив.
Share
