Можно прочитать много книг по базам данных, написать кучу приложений на аутсорс или для себя. Но при этом невозможно не наступить на грабли, при работе с действительно большими базами/таблицами особенно, когда downtime на большом проекте хочется свести к минимуму, а еще лучше совсем избежать. Вот здесь самые простые операции, как например изменение структуры таблицы может стать более сложной задачей. Наиболее интересные случаи, проблемы, грабли и их решения из личного опыта с которыми нам на проекте Pushwoosh пришлось столкнуться описаны под катом. В статье нет красивых картинок, зато есть много сухого текста.

Добавление нового столбца в существующую таблицу

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

Задача: добавить новый столбец для существующей таблицы на продакшене.
Проблема: добавление нового столбца блокирует таблицу.
Решение: добавление нового столбца не блокирует таблицу если DEFAULT опущен или используется DEFAULT NULL.

Для тех кому, интересно можно почитать полную документацию .


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

Добавление нового индекса в существующую таблицу

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

Задача: добавить индекс для существующей таблицы на продакшене
Проблема: добавление индекса блокирует запись(insert/update/delete) в таблицу. Читать из такой таблицы по-прежнему можно.
Решение: использовать CREATE INDEX CONCURRENTLY, который не блокирует таблицу, но работает примерно в два раза дольше и требует больше системных ресурсов.

Для тех, кому интересно можно почитать полную документацию.

Мониторинг текущих запросов к базе

Важно знать какие запросы выполняются в данный момент, какой процесс их выполняет и сколько запрос уже работает. У PostgreSQL есть отличная служебная табличка pg_stat_activity полное ее описание можно найти в документации. Приведу только наиболее интересные поля

pid	integer	Process ID of this backend
query_start	timestamp with time zone	Time when the currently active query was started, or if state is not active, when the last query was started
query	text	Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed.

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

pushwoosh=# select now() - query_start, pid, waiting, query from pg_stat_activity where state != 'idle' order by 1 desc;
    ?column?     |  pid  | waiting |  query
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 23:42:13.468115 | 6877  | f       | DELETE FROM application_goals WHERE applicationid = '9254'
 00:30:51.943691 | 24106 | f       | SELECT applicationid, pushtoken FROM application_devices WHERE hwid in ('1abd5e5fd79318cd','1abd5f3eda7acbca','1abd601f2bafabf8','1abd62a0a092ac17','1abd6303b83accf9','1abd64726a98fb63','1abd676c087c3617','1abd67ebecb6f3ce','1abd68a3b78fb730','1abd697af6bc8552','1abd70ebb654aeb2','1abd7114a8576a67','1abd729a385caff8','1abd731ff62c4521','1abd738bd2d457eb','1abd7760f7210155','1abd79dbc085c2c0','1abd7ab46dc24304','1abd7d48bd5e04ab','1abd7e7aee3c0e58','1abd7e8129a53ab3','1abd827c8c21630','1abd82cd204c69a9','1abd843ee3dedb1','1abd88d346c74d67','1abd88e8bd01c168','1abd8ceac00808cc','1abd8d3b2cb72de3','1abd8e139f267260','1abd8e74a288204c','1abd8f00bb4a0433','1abd8fd7e8f4f125','1abd91c193455ada','1abd92448396a9bf','1abd946ac4cf0e22','1abd9594ed1bd791','1abd96cc0df2202b','1abd975a98849a0b','1abd997c96d3c9b1','1abd9b3cfb66852c','1abd9bead472be5','1abd9f5bed3cbbd8','1abd9f73b8122bf1','1abda233b9a00633','1abda2ee3db5bccb','1abda486901c3a14','1abdac09e0e3267b','1abdae8235cf19dd','1abdaf9e3a143041','1abdb54fe96'
 00:04:49.592503 | 18899 | f       | autovacuum: ANALYZE public.device_tags_values
 00:00:00.040265 | 11748 | f       | INSERT INTO device_tags_values (hwid,valueid) VALUES ('27976b81cc72c7ac','8470317') RETURNING uid

Из данного вывода мы видим, что процесс с PID'ом 6877 уже почти сутки исполняет запрос, который скорее вероятно не оптимален и нуждается в более детальном профилировании. Так же мы видим, что второй запрос исполняется полчаса и скорее всего тоже не оптимален, но мы не видим запрос полностью, он обрезан, а нам же интересен запрос целиком.
Задача: увидеть какие запросы в данный момент исполняются в базе данных.
Проблема: pg_stat_activity показывает текущие запросы не полностью(обрезает).
Решение: gdb.

Возможно, у этой задачки есть решение проще, но мы его не нашли. Берем PID из запроса выше и подключаемся к нему

gdb [path_to_postgres] [pid]

и после того как подключились к процессу выполняем

printf "%s\n", debug_query_string

Slow log

Для базы всегда нужно иметь мониторинг и видеть, какие запросы исполняются медленно. Можно решить это кодом, и в ORM или где-то глубже измерять время запроса и если оно больше пороговой величины записывать данный запрос в лог. Но всегда лучше не писать велосипед.
Задача: Мониторить медленные запросы
Проблема: Хочется сделать это на уровне базы данных
Решение: log_min_duration_statement

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

Давайте поправим конфиг PostgreSQL vim /var/lib/pgsql/9.4/data/posgresql.conf и поставим в нем 3 секунды как пороговое значение

log_min_duration_statement = 3000       # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds

Чтобы изменения вступили в силу не обязательно перезагружать базу, достаточно выполнить команду из psql, pgadmin или другого интерфейса к базе

SELECT pg_reload_conf();

или выполнить из командной строки

su - postgres
/usr/bin/pg_ctl reload

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

И после этого можно посмотреть в лог PostgreSQL, который у нас находится по такому пути /var/lib/pgsql/9.4/data/pg_log/postgresql-2015-07-07.log и здесь видно, что есть запрос который исполняется почти 6 секунд.

2015-07-07 09:39:30 UTC 192.168.100.82(45276) LOG:  duration: 5944.540 ms  statement: SELECT * FROM application_devices WHERE applicationid='1234' AND hwid='95ea842e368f6a64' LIMIT 1

Как вариант в дальнейшем, чтобы мониторить лог-файл можно сделать связку logstash+elasticsearch+kibana и сразу слать через zabbix уведомление о появление медленных запросов, если это является критичным для проекта.

Узнать какие запросы в данный момент делает процесс на продакшене

Если у вас много демонов, которые часто общаются с базой, и в один обычный день демон начал работать медленно или стало не понятно, что он делает, то на помощь придет strace, который покажет запросы к базе и время их выполнения, без остановки процесса, добавления логов в программу и ожидания следующего возникновения проблемы — неважно, на чем вы пишите php, python, ruby, etc. — strace подходит для всего.
Задача: узнать, что делает процесс(как пример какие запросы шлет в базу)
Проблема: процесс нельзя прерывать или останавливать.
Решение: strace

Для этого достаточно взять pid процесса указать длину и добавить опцию -T. В итоге вывод strace может быть примерно таким

strace -p 27345 -s 1024 -T 2> out

gettimeofday({1437846841, 447186}, NULL) = 0 <0.000004>
sendto(8, "Q\0\0\0005SELECT * FROM accounts WHERE uid='25143' LIMIT 1\0", 54, MSG_NOSIGNAL, NULL, 0) = 54 <0.000013>
poll([{fd=8, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=8, revents=POLLIN}]) <0.000890>

NULL и уникальные индексы

Этот пример не имеет отношения к продакшен среде. Начнем с простого факта NULL неравен NULL.

Предположим, что мы хотим создать составной уникальный индекс, в котором одно из полей может иметь значение NULL, а может быть числом. При этом уникальный индекс не сработает для полей содержащий NULL, но очень хочется фильтровать такие записи индексом.


Рассмотрим пример

psql=# create table test (
psql(#     a varchar NOT NULL,
psql(#     b varchar default null
psql(# );
CREATE TABLE
psql=# create unique index on test (a, b);
CREATE INDEX
psql=# insert into test values (1, null);
INSERT 0 1
psql=# insert into test values (1, null);
INSERT 0 1
psql=# select * from test;
 a | b
---+---
 1 |
 1 |
(2 rows)

Несмотря на то, что мы создали уникальный индекс, запись (1, null) вставилась дважды.

Задача: сделать составной уникальный индекс, одно из полей может быть null
Проблема: уникальный индекс не сравнивает null поля
Решение: разбить индекс на 2 разных индекса.

Для того чтобы избежать такого поведения, можно разбить индекс на 2 индекса.

sql=# create table test (
sql(#     a varchar NOT NULL,
sql(#     b varchar default null
sql(# );
CREATE TABLE
sql=# create unique index on test (a, b) where b is not null;
CREATE INDEX
sql=# create unique index on test (a) where b is null;
CREATE INDEX
sql=# insert into test values (1, null);
INSERT 0 1
sql=# insert into test values (1, null);
ERROR:  duplicate key value violates unique constraint "test_a_idx"
DETAIL:  Key (a)=(1) already exists.

Обработка дубликатов

Этот пример так же не имеет отношения к продакшен среде.
Задача: Уметь обрабатывать дубликаты, которые не позволил создать уникальный индекс.
Проблема: Нужно ловить эксепшены, парсить коды ошибок
Решение: keep calm and catch exception и ждать пока не выйдет PostgreSQL 9.5 в котором будет upsert