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

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

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

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

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

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

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

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

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

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

Конечно без индексов никуда, потому после анализа запроса обычно находишь не оптимальные чтения, конечно там можно фильтры навешать и т.д., но если мы принимаем решение добавить индекс на какие-то поля, то я стараюсь для начала проверить селективность данных, то есть Очевидно, что для 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 название_таблица -- пересоздает все индексы таблицы

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

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

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

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

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

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

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

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

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

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

Все знают про 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;

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

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

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;

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

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

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

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

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

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

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

select pg_last_xact_replay_timestamp();

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

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

Как-то мы с Антоном из 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, может они когда-то решаться на публикацию своих наработок.

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

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