TL;DR;

Вместо предисловия: данный пост - мой последний на Хабр, статья полусырая (не прогоняю даже через знакомого райтера), но в связи с желанием пошарить какие-то знания и минусы, прилетающие ко мне от другой статьи выкладываю почти как есть, только после 2-х прочитываний. Считайте это очередным - хабро самоубийством (опять год не буду писать сюда).

Тем кто “все знает” пожалуйста посмотрите самый последний кейс, может он быть у вас в проде, но конечно это очень редкое сочетание.

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

Доверьте дело профессионалам пока за него не принялись дилетанты.

Так же думаю тем коллегам, у кого нет DBA или DBA недоступны как божества будет полезна, но все же я бы рекомендовал обратиться за услугами к профессионалам. Одних я знаю, работал с ними, но указывать здесь их не имею права, так как не успел с ними согласовать возможность их упоминания. 

Упоминания любых коллег тут - согласовано с ними. 

Тут просто перечислены какие-то вещи, которые я использую, возможно это кому-то поможет в понимании, что может PostgreSQL.

Какие ваши доказательства? Докажи, что это работает быстро.

Когда я хочу проверить свой очередной трехэтажный select запрос, то обязательно прогоняю его через explain на саб реальных данных. 

explain текст запроса
-- или
explain analyze текст запроса.

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

Если есть желание побольше узнать об этом, смотрим тут.

Select мне быстрый запилил! Про индексы.

Конечно без индексов никуда, потому после анализа запроса обычно находишь не оптимальные чтения, конечно там можно фильтры навешать и т.д., но если мы принимаем решение добавить индекс на какие-то поля, то я стараюсь для начала проверить селективность данных, то есть Очевидно, что для boolean нет смысла делать индекс (с моей точки зрения). 

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

Я смотрю смысл применения индексного поля примерно таким образом.

select 
column_name, 
count(1)::float/(select sum(1) from table_name) * 100 
from table_name
group by column_name
order by 2 desc

Вопрос возможно не оптимален, но мне достаточно понять: чем ниже значние второго столбца, тем больше вероятность, что применение индекса будет оправдано.

Ой ой ой, а кто это сделал? Проверить использование индексов.

После создания индексов или получив созданные индексы в наследство, стоит проверить а используются ли они вообще? Подробнее тут. Далее основной запрос, который я использую:

Туц
SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END  AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched
FROM
    pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

То что не используется, попадает под удаление, ну точнее я предпочитаю удалить, если индекс занимает много места, но не используется, либо мутации на таблице работают медленно.

Чужой среди, да он просто среди. Битые и распухшие индексы.

Индексы умеют ломаться и разбухать. Ломаются индексы очень весело, так что потом ни один запрос не проходит. После такого можно индекс пересоздать. Когда я вижу, что размер индекса какой-то подозрительный (см запрос из предыдущей заметки поле index_size), то у меня тянется рука сделать переиндексацию индекса. Тут подробнее.

REINDEX INDEX название_индекса
REINDEX TABLE название_таблица -- пересоздает все индексы таблицы

Come In. Про выражение In.

Иногда приходиться юзать выражение in(значение, значение2…), но бывает необходимым искать по тюплам, ну точнее так:

select *
from table_name
where (column_one, column_2) in (('1','2'), ('h', 'z'));

Магические трюки. Если в In много парметров.

Иногда приходиться использовать выражение in (...) в запросах. Это не очень хорошо, когда в параметрах запросах передается много значений, но жизнь расставляет свои приоритеты, потому иногда полезно знать о такой конструкции.

select *
from table_name 
where column_name in (values ('a'), ('b'), ('c'));

В данной ситуации values создаст виртуальную таблицу и выборка будет немного быстрее, если у вас допустим 10К значений, но опять же повторюсь: зло - пытаться использовать много значений в in.

Check yourself. Проверь свой запрос.

Перед тем как запихнуть свой, изменяющий данные в базе, запрос в миграцию я проверяю время и результаты его выполнения на около реальной/реальной базе данных. Это я делаю через транзакцию с rollback. 

begin;
--Запрос
--Проверка после результата запроса данных запроса
rollback;

Помимо того, что данный подход позволяет мне проверить корректность изменения данных, так же он позволяет мне понять сколько времени будет накатываться чейнджсет. Особенно это актуально для проектов в которых не разделена накатка миграций от старта приложения при развертывании в оркестраторе (допустим в k8s), так как если миграция замедлит выход инстанса в состояние live, то инстанс будет прибит планировщиком и начинается бесконечный цикл перезапусков. 

Учитывайте один момент, как подсказал @IvanVakhrushevв коментарии: Проверку лучше не производить на продовой базе, особенно, если у вас включена репликация или запрос может блокировать какие-либо записи (а он это будет делать) на критическое время, тем самым вы можете положить свой прод и/или нагрузить базу. Чуть глубже про то, что происходит с репликацией: дело в том, что во время лбой транзакции пишется WAL (Write Ahead Log) и он будет писаться и при транзакции с rollback, тольков в конце будет дана команда, что вот это что было - откати. Т.е. это все будет проиграно на слейве. Из этого еще раз делаем вывод: такое лучше использовать на суб реальных данных, ака стейдж.

Так же можно накатывать разного рода добавления полей, НО учитывайте, что при изменении DDL (структуры таблицы) могут случиться блокировки, особенно это актуально для PosgtreSQL < 11 версии.

Материализуй это. Про Materialized view.

Все знают про view, но во многих БД есть еще materialized view. Они почти как обычные view, но materialized (почитать про них можно тут).

Тут я хочу подсветить момент о том, что их нужно периодически рефрешить… если запустить REFRESH MATERIALIZED VIEW название_view, то вы получите блокировку всех запросов к этой view, но гарантированно консистентное состояние. Если консистентность состояния для вас не критична, то можно REFRESH MATERIALIZED VIEW CONCURRENTLY название_view.

Вечный кайф. Рекурсивные запросы.

Иногда мы имеем таблицу с ссылкой на себя:

Таблица
сreate table Human (
    id uuid primary key,
    parent uuid references Human(id)
)

Нужно выбрать всех предков. 

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

Допустим для этой таблицы запрос может выглядеть так:

Запрос
with recursive parrents (parent_id, child_id, level) as (
   select parent, id, 1 
  	from Human
   union all
   select h.parent, p.child_id, p.level+1 
  	from Human h, parrents p
   where p.parent_id = h.id and h.parent is not null
)

select pp.parent_id, pp.level from parrents pp
where pp.child_id=?
order by pp.level desc;

Обязан предупредить, что с рекурсивными запросами лучше не заигрываться.

Опасная группировка. Агрегация в jsonb.

Иногда нужно собрать какие-то данные в одно поле…. Это конечно очень спорный прием, но все же существует, так как существуют агрегирующие функции (). На примере предыдущего мы бы могли собрать все в вид: child и его предки.

Запрос
Опасная группировка. Агрегация в jsonb.
with recursive parents (parent_id, child_id, level) as (
   select parent, id, 1 
  	from Human
   union all
   select h.parent, p.child_id, p.level+1 
  	from Human h, parents p
   where p.parent_id = h.id 
  	and h.parent is not null
)

select pp.child_id, jsonb_agg(pp.parent_id) 
from parents pp
where pp.child_id=?
group by child_id;

Или еще веселее: собрать все в один jsonb объект...

Запрос
with recursive parents (parent_id, child_id, level) as (
   select parent, id, 1 
  	from Human
   union all
   select h.parent, p.child_id, p.level+1 
  	from Human h, parents p
   where p.parent_id = h.id 
  	and h.parent is not null
)

select 
json_build_object(
  'child', pp.child_id, 
  'parents', jsonb_agg(pp.parent_id)
) 
from parents pp
	where pp.child_id=?
group by child_id;

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

Пусть весь мир подождёт! Про блокировки.

Тут просто я хотел бы напомнить или тем кто не знал, что в базах данных существуют пессимистические блокировки…. Вот тут можно про них почитать. 

Один момент: этот механизм лучше не использовать совсем, но бывает, что нужно и условный select * from table_name where id = ? for update вам может пригодиться.

Виагра (Все встало). Про эксклюзивные блокировки

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

А тут просто один из частоиспользуемых мной запросов:

Запрос
SELECT blocked_locks.pid     			AS blocked_pid,
       blocked_activity.usename  	AS blocked_user,
       blocking_locks.pid     		AS blocking_pid,
			 blocking_activity.usename  AS blocking_user,
			 blocked_activity.query     AS blocked_statement,
       blocking_activity.query   	AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
		JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
				AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
				AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
				AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
				AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
				AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
				AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
				AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
				AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.granted;

Также бывает полезно посмотреть вообще активные на текущий момент запросы:

select * from pg_stat_activity;

Как в ней фильтровать - можно достаточно быстро разобраться.

Партицианируй это. Про партиционирование таблицы.

В случае когда табличка стала ну неприлично весить, то можно ее партиционировать.

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

Главное для меня как для разработчика - понимать, что запросы к партиционированным таблицам должны использовать ключ партиционирования, иначе можно сотворить очень медленные запросы. 

Ключем партиционирования может быть какая-либо дата, так же можно делать вложенные партиции с другим ключем партиционирования (допустим город), но тогда нужно будет использовать эту саму дату и город в запросах к партиционированным таблицам.

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

Убери руки от моей базы, она уже просто мертва. Про master/slave. (Привет моему соседу Сергею, который мне напомнил про ряд кейсов, включая этот).

Если у вас нагруженная система и есть необходимость убрать читающие операции с матер базы (когда всякие BI и прочие аналитики лезут в базу со своими запросами), то учтите пожалуйста один момент: данные на slave могут неприлично отставать от мастера. Понять что это происходит можно таким запросом.

select pg_last_xact_replay_timestamp();

Я не решал проблем с лагом репликации, но смотрел как это делали DBA аутсорс компании DataEgret (разрешили мне их указать, вот их сайт), кстати, классные ребята..

ИМО: разделение на горячее/холодное чтение должно быть обосновано и да, если это для того чтобы справиться с нагрузкой, то возможно ваш проект уже приносит прибыль, то стоит взять DBA в штат или воспользоваться услугами профи. Если у вас сочетание pgbouncer в transaction mode + наш любимый Hibernate и где-то в коде вы изменяете уровень транзакций, то вы должны знать - тут могут быть казусы.

PgBouncer (transaction mode) + Hibernate (Hikari Pool) + Разные уровни транзакций = селедка с молоком. История одной проблемы.

Как-то мы с Антоном из KazanExpress заметили, что Repeatable Read не всегда есть… ну точнее у нас глобально был Read Committed. И вот этот самый RR как будто пропадал в какие-то моменты. Иначе говоря: у нас есть операция, она под RR, но иногда происходит так, что эта операция выполняется в обычном RC.

В общем мы долго искали проблему, пока DBA аутсорс компании DataEgret (разрешили мне их указать, вот их сайт) не выяснили куда девается наш этот RR.

Наш доблестный Hikari pool открывал сессию и выставлял уровень изоляции RR, дальше открывалась транзакция, если сходились звезды и не хватало коннектов к БД, то pgbouncer жонглировал ими и выполнение транзакции попадало в другой коннекшн, так как pgbouncer в режиме transaction гарантирует выполнение одной транзакции на одном подключении.

То есть получалось примерно такое:

set transaction isolation level read committed; -- connection one
begin; -- connection two
update..... -- connection two
end; -- connection two

А нужно по фен-шую pgbouncer:

begin;
set transaction isolation level read committed;
update.....
end;

В такой ситуации выставление уровня изоляции будет верным.

Btw: Сергей из KazanExpress починил это у себя, пропатчив jdbc драйвер, но это специфик патч и не уверен, что его примут в кодобазу драйвера. 

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

Кстати, у ребят из KazanExpress, есть что рассказать про использование pgbouncer + Hibernate, может они когда-то решаться на публикацию своих наработок.

Можно было бы добавить эту заметку еще какими-либо вещами, необходимыми начинающему backend java разработчику, если есть что-то предложить или поправить, с радостью это приму в комментариях.

PS

Это я писал до того, как понял, что статья обречена, как и остальные мои попытки с чем-то поделиться. Да, бывает такая ерунда у меня хочу чем-то поделиться или что-то поменять. К сожалению некоторые разработчики не знают как записать атомарно изменения в 2 таблицы… Надеюсь кому-то эта статья поможет кому-то. И да, классно осознавать, что ты попадаешь в точку, когда твою статью колбасит от - до + (Спасибо Саше из одного банка, специалист пожелал остаться инкогнито).

Троллинг. Слабонервным не читать.

Хочу обратиться с таким же как и я синьёрам-подмидорам: коллеги, для вас все изложенное в статье может быть очевидным и избитым, но знаете, я давно уже собеседую людей разного уровня и с разного уровня специалистами работаю. Знаете что заметил? То что и на интервью и на практике даже Специалисты с 20-ю годами опыта не имею представления ни о jsonb, ни о рекурсивных запросах и так далее. Я понимаю, что вам достаточно ваших знаний и вашего бесценного опыта (моя прелесть), но пожалуйста обратите внимание сколько раз в день вы отвечаете: Я знаю.

Комментарии (27)


  1. dimaloop
    23.05.2022 23:58
    +4

    И при чем тут спринг?


    1. hyragano Автор
      23.05.2022 23:59
      +2

      1. Я - разработчик на этом стеке.

      2. Есть момент с hibernate

      3. Spring jpa позволяет делать очень многое без знания sql, в итоге разрабы не знают даже этого минимума, к сожалению


      1. goot
        24.05.2022 08:28

        А как они не знают минимум и работают, еще и многие подолгу. Может это не минимум тогда?


        1. hyragano Автор
          24.05.2022 08:29

          Минимум слово относительное, вопрос в другом: какие решения выходят когда разраб не знает ряда вещей


          1. goot
            24.05.2022 08:37

            Если выходят и никто не жалуются, то думаю рабочие )


            1. hyragano Автор
              24.05.2022 09:00

              Это точно и даже хорошо.


      1. dimaloop
        24.05.2022 08:58

        Ну если не знать sql, то нет смыла заикаться про план запроса, джойны и вообще вот это все выше. Да ладно, не придираюсь, думал может часть текста не сохранилась или продолжение предполагалось)


        1. hyragano Автор
          24.05.2022 09:02

          Нет. Это фарш, телёнка не успел собрать, начинали расхабривать опять.


          1. dimaloop
            24.05.2022 09:14

            Пишите еще, полезно все равно


            1. hyragano Автор
              26.05.2022 15:40

              Вопрос в названии. Изначально я писал статью под названием: колходный DBA, типа когда у тебя недоступны DBA и приходиться самому им становиться отчасти, но когда накидал фарш получилось что это не совсем про DBA, а скорее какой-то непонятный мне сбор... Времени слепить из фарша теленка не было, так как мою статью про интервью начали минусть и прилетело в карму, т.е. не было времени все это пересобрать и поразмыслить.

              Потому было бы круто, если были бы предложения.

              В том числе думал ( и подсказывал коллега) подкинуть сюда еще пару вещей: ltree и windowed функции, но как есть.


              1. dimaloop
                26.05.2022 17:43

                Не готов из вашего фарша лепить теленка))

                Но это я еще про java не придирался. у Вас в материале она ни в одном кейсе не употребляется ведь

                Мне кажется если выпилить лишнее, то и останется суть. Ее и сформулировать будет проще. Ну, например "Советы начинающему разработчику при работе с PostgreSQL".


    1. Nialpe
      24.05.2022 09:35

      Я тоже не понял. Потому как название должности в парадигме автора следует писать так: Spring PostgreSQL java backend разработчик. Свою должность даже представить не могу.


      1. hyragano Автор
        26.05.2022 13:45

        К сожалению нормального названия так и не придумал, времени не хватило (пояснение в статье).

        Если есть предложение, то рад был бы принять.

        К сожалению, на текущий момент времени очень много именно Spring разработчиков, т.е. те кто знаком или выучил только данный фреймворк.


    1. hyragano Автор
      26.05.2022 13:51

      Нужно другое название заметик. Поможете? Буду признателен.


  1. hyragano Автор
    24.05.2022 00:01

    Я понял месс с названием, но один фиг оставлю его таким.


  1. GaDzik
    24.05.2022 03:20

    Для начинающих очень полезно.


  1. IvanVakhrushev
    24.05.2022 09:17

    В статье есть деструктивные примеры, которые лучше не применять на практике. В частности, "проверь свой запрос".

    Всё, что вы делаете в БД, остаётся в БД и влияет на её прямым образом.

    Нельзя просто так начать транзакцию, откатить ее и сделать вид, что ничего не было. База по-честному будет выполнять ваш запрос, используя backend процесс, потребляя CPU/диск и генерируя WAL.

    Бездумно используя подход с begin/rollback, можно выстрелить себе в ногу и на время положить кластер БД.


    1. hyragano Автор
      24.05.2022 09:38

      Могу Ваше замечение про мусор использовать для дополнения в заметке?


      1. IvanVakhrushev
        24.05.2022 23:58

        Да, конечно.


        1. hyragano Автор
          25.05.2022 00:12

          Спасибо. Заодно коллеги заинтересуются вопросов WAL и неявных блокировок.


    1. hyragano Автор
      24.05.2022 09:38

      Можете, пожалуйста, указать на проблемные моменты в заметке?


    1. hyragano Автор
      24.05.2022 09:40

      И да, верно понимаю, как Вы предлагаете проверить мутационный запрос на стейдже допустим?


      1. IvanVakhrushev
        25.05.2022 00:07

        Я предпочитаю проверять интеграционным тестом (с Testcontainers, например). Потом на тестовом стенде, который не жалко, и который в случае чего можно перераскатить с нуля.

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


        1. hyragano Автор
          25.05.2022 00:15

          Согласен. Тут главный момент: наличие субреальных данных, так как бывает, что разраб запихивает какой-то убер апдейт и потом, ну Вы поняли.

          Т.е. есть возможность проверить на суб реальных данных (стейдж), но не знают о такой возможности.

          Ну и конечно же кто-то пойдет в прод и будет там проверять.... но если у начинающего разраба есть доступ в прод, то это уже опасно изначально)))

          Спасибо Вам еще раз за подсвеченное мной упущение!


  1. GreyN
    26.05.2022 01:50

    не совсем понятно, почему про explicit локи написано "этот механизм лучше не использовать совсем".
    это стандартный механизм работы с блокировками в Postgres. Им очень даже можно пользоваться, когда нужно.
    Как и любым другим инструментом, блокировками нужно уметь пользоваться.
    Про устройство блокировок в Postgres и работу с ними есть прекрасный цикл статей @erogov(начиная с этой: https://habr.com/ru/company/postgrespro/blog/462877/)


    1. hyragano Автор
      26.05.2022 02:13

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

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

      В моей практике получалось так, что можно было выбрасывать эксплисит локи в 80%.


    1. hyragano Автор
      26.05.2022 11:30

      Кстати, спасибо за линк, очень крутые наработки!