Здравствуйте, мои маленькие любители программирования. Сегодня мы поговорим о недопонимании.

Да, Васечкин, Маша не всегда понимает твои знаки внимания, и иногда даже игнорирует их. Как и наша сегодняшняя героиня - База Данных - которая не поняла намёков программиста “работай быстрее, ну пожалуйста”, и продолжала методично тратить свои ресурсы на, казалось бы, простой запрос.

Расклад Ссылка на заголовок

Выделенная таблица в БД с простой структурой. В таблице мы храним некий каталог ресурсов со счётчиком ссылок на ресурс. Когда счётчик уменьшается до нуля, то спустя какое-то время запись удаляется.

class ResourceModel(Base):
    __tablename__ = "resources"

    id = Column(sa.String(32), primary_key=True)
    created_at = Column(sa.DateTime(timezone=True), nullable=False)
    updated_at = Column(sa.DateTime(timezone=True), nullable=False)
    name = Column(sa.String(255), nullable=False, server_default="")
    format = Column(sa.String(128))
    reference_count = Column(sa.Integer, nullable=False, server_default="0")

Index('ix_resource_created', ResourceModel.created_at.asc())

Для выборки неиспользуемых более записей изначально был написан простой запрос:

query = session.query(
	ResourceModel
).filter(
    ResourceModel.reference_count == 0,
    ResourceModel.updated_at <= cutoff_updated_at,
).order_by(
	ResourceModel.created_at.asc()
)

Всё работало хорошо, пока объём данных был небольшой, и seqscan отрабатывал незаметно для глаза. Когда же число записей подобралось к миллиону, простое решение перестало быть эффективным.

Первая попытка оптимизации (надо больше индексов). Ссылка на заголовок

Программист, взявшийся за оптимизацию, решил добавить частичный индекс - “дай мне записи, в которых поле reference_count равно нулю”. Логичная идея, нелогичное исполнение, ибо индекс был написан так:

Index(
    "ix_resources_unused_only",
    ResourceModel.reference_count,
    postgresql_where=(ResourceModel.reference_count == 0),
)

После построения, этот индекс “знал” про все записи, которые надо удалить, но не более того. Как записи упорядочены, когда именно была создана или изменена та или иная запись - индекс был без понятия. И БД, получив из индекса ссылки на записи с reference_count == 0, начинала методично искать данные в памяти (если повезло), или зачитывать с диска (если не повезло) - чтобы проверить потом на условие по полю updated_at. И это мы ещё не добрались до сортировки!

Вторая попытка оптимизации (лечим симптомы). Ссылка на заголовок

Вобщем, индекс есть, но толку от него маловато. И тут программиcт, вероятно, воскликнул - “У меня же есть индекс по полю created_at! Индекс упорядочен! Я использую эту фичу в коде и всё будет хорошо!”

Где-то здесь загрустило поле updated_at, как та девушка справа с известной фотографии Distracted Boyfriend. Но разработчика было уже не остановить, и он написал следующее:

age_buckets_days = [3650, 1825, 1460, 1095, 730, 365, 30, 7, 1, 0]  # old -> recent
bucket_ranges = list(zip(age_buckets_days, age_buckets_days[1:]))

selected_ids: list[str] = []

for older_days, newer_days in bucket_ranges:
    upper_created = now - timedelta(days=newer_days)
    lower_created = now - timedelta(days=older_days)

    q = session.query(
        ResourceModel.id
    ).filter(
        ResourceModel.reference_count == 0,
        ResourceModel.created_at > lower_created,
        ResourceModel.created_at <= upper_created,
        ResourceModel.updated_at <= cutoff_updated_at,
    ).limit(
        max_count
    )

    with pg_statement_timeout(session, timeout="5min"):
        selected_ids.extend([rid for (rid,) in q.all()])

Да, теперь запрос начал использовать индекс по полю created_at. И если данные оказывались в оперативной памяти (особенно для бакета с ближайшими датами) - то время выполнения действительно уменьшалось. Но что взамен?

Отложим пока антипаттерн “SQL в цикле”, крайне не рекомендованный в production-коде. Без него идею бакетов вряд ли удалось бы реализовать. Но даже при работе с одним бакетом, БД всё равно приходилось фильтровать полученные, благодаря индексу ix_resource_created, записи. Фильтровать и по полю updated_at, и по полю reference_count. И чем дальше бакет был от текущего момента, тем медленнее всё это работало.

Отдельно стоит отметить выражение with pg_statement_timeout(session, timeout="5min"). Что-то мне подсказывает, мои маленькие любители программирования, что это не было продиктовано оптимизацией. Скорее всего, это была неуверенность в предложенном решении, и, как следствие, “подстеленная соломка” на случай, если что-то пойдёт не так.

Кроме того, к неудачному тюнингу производительности добавился “семантический дрифт”. Теперь порядком выборки данных управляет поле created_at, точнее, попадание значения этого поля в тот или иной бакет. Пострадал детерменизм выборки (привет, unit-тесты!). И чтобы понять логику работы кода, придётся потрудиться.

Третья оптимизация (а если немного подумать). Ссылка на заголовок

Когда время выполнения “доработанного” запроса стало приближаться к запланированному таймауту - пришлось разбираться и в задаче, которую решает код, и в причинах неудач попыток ускорения. Напомню, запрос используется в задаче по очистке таблицы resources от ненужных более записей - своего рода Garbage Collector. Причём предусматривается определённое время на то, чтобы сделать ресурс снова востребованным. И записи лучше всего удалять от более старых к более свежим.

Сначала был исправлен код генерации запроса:

query = session.query(
    ResourceModel
).with_entities(
    ResourceModel.id
).filter(
    ResourceModel.reference_count == 0,
    ResourceModel.updated_at <= cutoff_updated_at,
).order_by(
    ResourceModel.updated_at.asc(),
).limit(
    max_count
)

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

… Да, Петров, верно замечено, что мы почти вернулись в начало рассуждения, где похожий код начинал было неимоверно “тормозить”. Чтобы добавить решению лёгкости, неудачный индекс ix_resources_unused_only был заменён на такой:

 Index(
     "ix_resources_updated_at_id_conditional",
     ResourceModel.updated_at,
     ResourceModel.id,
     postgresql_where=(ResourceModel.reference_count == 0),
)

Это так называемый covered-индекс, который как бы “покрывает” потребности в данных запроса - ибо содержит все нужные поля. Рассмотрим подробнее логику работы составного B-Tree индекса:

  • Индекс частичный, по условию reference_count == 0. Попали в первое условие фильтрации.
  • Первое поле в индексе updated_at, данные отсортированы по возрастанию (из прошлого в будущее). Попали и во второе условие фильтрации, и в итоговую сортировку результатов.
  • Второе поле в индексе id. Именно то поле, что требуется в запросе. Получаем ачивку Index Scan Only и, счастливые, смотрим на миллисекунды выполнения плана запроса.

Вывод Ссылка на заголовок

Дело тут не в новом индексе, хотя именно таким он должен быть изначально. Куда важнее было избавиться от кода, которым разработчик пытался скомпенсировать непонимание вывода EXPLAIN ANALYZE. Казалось бы - логика бакетов даёт выглядит продуманно. Таймаут на выполнение запроса даёт чувство безопасности (ну за пять-то минут точно отработает!). Дополнительный индекс, как масло в каше, лишним не будет (будет, но это тема для отдельной истории). Но всё это признаки одного и того же: мы “танцуем” вокруг БД вместо того, чтобы проектировать для неё. Понимание сути убрало ненужную сложность, добавленную в ходе “оптимизаций”.

На сегодня всё, все свободны, до новых встреч!