Postgres

С этим тегом используют

Postgresql IT SQL Все

21 пост сначала свежее

kznalp

3 дня назад

Postgres DBA

Серия СУБД PostgreSQL

Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL⁠⁠

Взято с основного технического канала Postgres DBA

Очередная иллюстрация -ЛЮБОЙ ответ нейросетей необходимо экспериментально проверять.

Экспериментальная проверка рекомендации DeepSeek по настройке autovacuum для PostgreSQL Нейронные сети, Субд, Postgres, Тестирование, Настройка, Длиннопост, DeepSeek

В случае использования советов нейросетей - древняя мудрость особенно актуальна.

Вопрос DeepSeek

Как изменится производительность СУБД PostgreSQL при нагрузке создаваемой pgbench и количестве подключений от 5 до 100 для СУБД размером 300MB при ресурсах CPU = 2 и RAM = 2GB при изменении настроек автовакуума с настроек по умолчанию на агрессивные настройки ?

Ответ DeepSeek

Исходные параметры:

Размер БД: 300 MB
Ресурсы: 2 CPU, 2 GB RAM
Количество подключений: 5–100
Нагрузка: pgbench в режиме TPC-B (смесь SELECT/UPDATE).
Сравнение:
Настройки по умолчанию:

autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 200

Агрессивные настройки:

autovacuum_vacuum_scale_factor = 0.05 # Запуск при 5% изменений вместо 20%
autovacuum_vacuum_threshold = 25 # Нижний порог срабатывания
autovacuum_vacuum_cost_delay = 5ms # Меньшая задержка между операциями
autovacuum_vacuum_cost_limit = 1000 # Больше операций за один проход

Прогнозируемые результаты:

1. Низкая нагрузка (5–20 подключений):

По умолчанию:
Автовакуум запускается редко. Мертвые кортежи накапливаются, но не критично. Производительность стабильна.
TPS: ~600–800.
Агрессивные настройки:
Автовакуум срабатывает чаще, но нагрузка от него незначительна. Мертвые кортежи удаляются своевременно.
TPS: ~650–850 (+5–10%).

2. Средняя нагрузка (30–60 подключений):

По умолчанию:
Накопление мертвых кортежей приводит к:
Увеличению размера таблиц (из-за "раздувания").
Замедлению SELECT (сканирование большего объема данных).
Блокировкам при UPDATE из-за долгих вакуумных операций.
TPS: падение до 400–500.
Агрессивные настройки:
Частые, но короткие вакуум-сессии. Меньше "раздувания", быстрее доступ к данным.
TPS: ~550–700 (+20–40%).

3. Высокая нагрузка (80–100 подключений):

По умолчанию:
Критическое накопление мертвых кортежей. Автовакуум не успевает очищать данные, что вызывает:
Резкое падение TPS (до 200–300).
Рост времени отклика (блокировки, конкуренция за I/O).
Агрессивные настройки:
Плюсы: Минимизация "раздувания", стабильный TPS (~450–600).
Минусы: Конкуренция за ресурсы CPU между фоновым вакуумом и рабочими процессами. При 2 CPU возможны просадки из-за нехватки вычислительной мощности.

Ключевые метрики для сравнения:

TPS (Transactions Per Second):
Агрессивные настройки дадут прирост на 20–40% при 30–60 подключениях.
При 80–100 подключениях рост TPS будет менее значительным (+10–15%) из-за ограничений CPU.
Задержки (Latency):
В агрессивном режиме задержки будут стабильнее, особенно в диапазоне 30–60 подключений.
Размер таблиц (Table Bloat):
Агрессивный автовакуум уменьшит "раздувание" на 40–60% по сравнению с настройками по умолчанию.
Использование CPU:
При агрессивных настройках нагрузка на CPU вырастет на 15–25% из-за частого выполнения вакуума.

Заключение:

Агрессивные настройки автовакуума улучшат производительность на 20–40% при 30–60 подключениях, предотвращая "раздувание" таблиц. Однако при экстремальной нагрузке (80–100 подключений) и ограниченных ресурсах CPU (2 ядра) возможна конкуренция за процессорное время, что снизит эффективность. Тестирование на реальной инфраструктуре с мониторингом CPU/I/O обязательно.

Результаты сравнительных экспериментов

Конфигурация виртуальной машины

CPU = 2RAM = 2GB
Версия СУБД:Postgres Pro (enterprise certified) 15.8.1 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

Сценарий нагрузки "Mix Without median time"

Эксперимент-1 : Настройки автовакуума по умолчанию

autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_vacuum_cost_limit = -1
vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms

Эксперимент-2 : "агрессивные" настройки автовакуума

autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_threshold = 25
autovacuum_analyze_scale_factor = 0.005
autovacuum_analyze_threshold = 10
autovacuum_vacuum_cost_limit = 2000
vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms
maintenance_work_mem = 128MB
autovacuum_naptime = 5s

Результаты сравнительных экспериментов

Операционная скорость

Ось X - нагрузка на СУБД . Ось Y - операционная скорость

Ожидания СУБД

Итоги и проверка гипотез DeepSeek

Операционная скорость для данной СУБД и данных ресурсах ВМ - не увеличилась и даже уменьшилась до -5% при оптимальной нагрузке.
Характерные ожидания - не изменились .
Гипотеза нейросети о влиянии "агрессивной" настройки автовакуума на производительность СУБД - не подтвердилась:

Агрессивные настройки автовакуума улучшат производительность на 20–40% при 30–60 подключениях, предотвращая "раздувание" таблиц.

Показать полностью 3

[моё] Нейронные сети Субд Postgres Тестирование Настройка Длиннопост DeepSeek

IliaHohlov

1 месяц назад

Задача на оптимизацию SQL запроса⁠⁠

Предлагаю ещё одну задачу по базам данных со своего Телеграм-канала по SQL и базам данных. Итак, среди прочих, имеется таблица выставленных счетов INVOICES:

В некотором отчёте АБС количество счетов, выставленных за текущий день формируется SQL запросом:

SELECT COUNT(*) COUNT_INV FROM INVOICES WHERE DATE(DATE_CREATE) = CURRENT_DATE

Тестирование IT Программирование Собеседование SQL База данных Аналитик Аналитика Программист Postgresql Postgres Oracle Mysql Telegram (ссылка)

user8360299

2 месяца назад

Postgres DBA

Конференция pgconf 2025⁠⁠

Я удивляюсь, как создатели этой конференции каждый год умудряются делать такой профессиональный праздник. Перезагрузка мозга и прямо лечебное общение с коллегами. Профессиональный санаторий какой-то. Пусть эта музыка будет вечной.
В этом году как-то особенно душевно получилось.

[моё] Postgresql Postgres База данных

IliaHohlov

7 месяцев назад

Лига программистов

Задача с собеседования по SQL с заложенной в неё ошибкой⁠⁠

Продолжаю тему разборов задач с собеседований по SQL. В этот раз выложу полное содержание, чтобы пост не удалили.

Итак, в базе данных есть таблица CLIENTS, содержащая три строки:

И есть таблица INVOICES, в которой две строки:

И сама задача звучит так: какое количество строк вернёт указанный ниже запрос:

SELECT * FROM CLIENTS WHERE EXISTS (SELECT COUNT(*) FROM INVOICES WHERE ID_CLIENT = CLIENTS.ID)

Какой бы вариант Вы выбрали?

Здесь нужно было заметить неправильное использование EXISTS.

Оператор EXISTS используют для проверки того, возвращается ли что-то подзапросом, указанным в скобках. Как правило, в скобках для оператора EXISTS/NOT EXISTS пишут:

SELECT *

или:

SELECT 1

В задаче выше нарочно допущена ошибка (в EXISTS нарочно написано SELECT COUNT).

SELECT COUNT всегда будет возвращать данные. Написанный в скобочках подзапрос всегда будет возвращать количество, хоть и иногда 0. Но данные-то есть. Ноль - это тоже данные. Поэтому EXISTS будет давать TRUE для каждой из трех строчек основного запроса.

Ещё больше интересных задач с собеседований я публикую в моем Телеграм-канале по SQL!

Показать полностью 4

[моё] Программирование IT Собеседование SQL Задача Oracle Postgresql Postgres Mysql Ms SQL База данных Программист Тестирование

Mathos

8 месяцев назад

Лига образования

Ответ на пост «Бесплатная книга про PostgreSQL 16»⁠⁠1

У них по этой книге есть видео лекции, держите ссылки:

https://www.youtube.com/playlist?list=PLaFqU3KCWw6JgufXBiW4dEB2-tDpmOXPH - для тупоконечников.

https://rutube.ru/plst/439869/ - для остроконечников.

Или наоборот, вы там сами как-то определитесь. Пост без рейтинга.

Postgres SQL Программист IT Текст Без рейтинга Ответ на пост YouTube (ссылка)

J0HNSS0N

9 месяцев назад

Лига программистов

Ответ на пост «Задачи с собеседования по SQL»⁠⁠1

Example Answer Structure

Question: What value will the following query return?

SELECT COUNT(*) FROM TMP_TABLE;

Answer:

To determine the result of the query SELECT COUNT(*) FROM TMP_TABLE;, we need to understand what the COUNT(*) function does in SQL. The COUNT(*) function counts the number of rows in a table, regardless of whether they contain NULL values or not.

Let's look at the data in the table TMP_TABLE:

column1column21212NULL131214NULLNULL1315

There are 5 rows in the table. The COUNT(*) function will count all these rows, including those with NULL values.

Therefore, the query:

SELECT COUNT(*) FROM TMP_TABLE;

will return the value 5.

This value is the total number of rows in the table TMP_TABLE, without any consideration of the content of the columns or whether they contain NULL values.
______
ChatGPT4o. Простите.

Программирование Собеседование IT SQL Задача Аналитика База данных Ms SQL Oracle Postgresql Postgres Mysql Тестирование Telegram (ссылка) Ответ на пост Текст

IliaHohlov

9 месяцев назад

Лига программистов

Задача по SQL с неочевидным решением⁠⁠

Здравствуй, уважаемый читатель! Спасибо, что заходишь ко мне на канал и изучаешь SQL со мной!

Недавно нашёл в интернете достаточно простую задачу с собеседования по SQL, в которой нужно было составить запрос к таблицам. Ссылку на источник размещу ниже. В своём Телеграм канале, где мы решаем разные задачи с собеседований и разбираем практические ситуации, я предложил всем поучаствовать в её решении. Надеюсь, тебе будет тоже интересно попробовать её решить. Ниже описание задачи.

В базе данных есть таблица анализов Analysis, имеющая следующие столбцы: an_id — ID анализа; an_name — название анализа; an_price — цена анализа; an_group — группа анализов. Есть, также, таблица заказов Orders: ord_id — ID заказа; ord_datetime — дата и время заказа; ord_an — ID анализа. Необходимо вывести название и цену для всех анализов, которые продавались 5 февраля 2020 и всю следующую неделю.

Тут сделай паузу и попробуй сначала сам решить задачу.

Итак, надеюсь, ты делал паузу и составил SQL-запрос. Далее будем решать вместе.

Напомню, что нужно было вывести список анализов и их цену, которые продавались 05.02.2022 и всю следующую неделю.

Первым решением напрашивается соединение таблицы анализов и таблицы продаж с применением условия на период. Кстати, именно это и указано в качестве ответа на задачу.

На самом деле, из-за того, что один анализ за выбираемый интервал времени мог быть продан сколько угодно раз (один и тот же анализ мог быть продан хоть тысячу раз и больше) заместо соединения таблицы анализов с таблицей заказов, я бы предложил использование EXISTS:

select a.an_name, a.an_cost from analysis a where exists (select 1 from orders where ord_an = a.an_id and ord_datetime between to_date('05.02.2022', 'dd.mm.yyyy') and to_date('05.02.2022', 'dd.mm.yyyy') + 7)

Благодаря тому, что мы не джоинили анализы с их заказами, в результирующей таблице анализы не замножатся по количеству присоединившихся к ним заказов. То есть в результате мы получим:

а не:

В примере, что я нашёл в интернете (ссылка ниже), помимо названий анализов и их стоимостей, выведен ещё и столбец с датами заказов. По условиям задачи вывод этого столбца не запрашивался. Не требовался вывод никакой информации о заказах/продажах. Поэтому и надобности в соединении я не усмотрел. Наоборот, это и потенциально замножит вывод анализов, которые покупали в запрашиваемый период, и прибавит лишнюю работу СУБД.

Ссылка на источник: https://tproger.ru/articles/5-zadanij-po-sql-s-realnyh-sobesedovanij

Поддержи статью лайком или подпиской!

Ещё больше интересных практических задач по SQL и задач с собеседований в нашем Телеграмм-канале и в интернете :)

Показать полностью 3

[моё] IT Программирование Собеседование Тестирование Фриланс SQL Oracle Программист Ms SQL Mysql Postgresql Postgres База данных Длиннопост

IliaHohlov

9 месяцев назад

Лига программистов

Задачи с собеседования по SQL⁠⁠1

Всем привет!

Продолжаем разбирать SQL задачи с собеседований с нашего Телеграм-канала!

В базе данных есть таблица TMP_TABLE, имеющая всего два столбца и содержащая следующие данные:

TMP_TABLE

column1 column2

-----------------------------------------

12 12

NULL 13

12 14

NULL NULL

13 15

Какое значение вернёт следующий запрос?

SELECT COUNT(*) FROM TMP_TABLE

Задачи с собеседования по SQL Программирование, Собеседование, IT, SQL, Задача, Аналитика, База данных, Ms SQL, Oracle, Postgresql, Postgres, Mysql, Тестирование, Telegram (ссылка)

Показать полностью 1

[моё] Программирование Собеседование IT SQL Задача Аналитика База данных Ms SQL Oracle Postgresql Postgres Mysql Тестирование Telegram (ссылка)

Посты не найдены

1 2 3

Postgres

С этим тегом используют

Вопрос DeepSeek

Ответ DeepSeek

Исходные параметры:

Прогнозируемые результаты:

1. Низкая нагрузка (5–20 подключений):

2. Средняя нагрузка (30–60 подключений):

3. Высокая нагрузка (80–100 подключений):

Ключевые метрики для сравнения:

Рекомендации:

Заключение:

Результаты сравнительных экспериментов

Конфигурация виртуальной машины

Эксперимент-1 : Настройки автовакуума по умолчанию

Эксперимент-2 : "агрессивные" настройки автовакуума

Результаты сравнительных экспериментов

Операционная скорость

Ожидания СУБД

Итоги и проверка гипотез DeepSeek