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

В этом посте будут рассмотрены некоторые менее известные советы по настройке Postgres для долгосрочной работы: от пула соединений до основ безопасности, расширений и индексов.

Настройка пула соединений для Postgres с помощью PGBouncer

По умолчанию Postgres создает отдельный процесс для каждого клиентского соединения из основного процесса ОС. При небольших объемах время, необходимое для создания и уничтожения этих процессов (плюс тот факт, что они никогда не используются повторно), не будет иметь значения. Вы можете установить количество max_connections вручную, но в конечном итоге, по мере масштабирования, вы, скорее всего, столкнетесь здесь с проблемами. Пул соединений помогает вам по существу «кэшировать» эти процессы и повторное использование их, когда клиенты подключаются и отключаются от вашей базы данных.

Хотя ты может встроить пул соединений в логику вашего приложения, большинство выбирает сторонний инструмент, и в случае Postgres это PGBouncer. Это легкий пул соединений с открытым исходным кодом, который вы можете установить либо на сервере базы данных, либо на сервере приложений. У вас есть 3 уровня объединения на выбор:

  • Объединение сеансов в пул: остается верным модели «клиентские соединения неопределенны» и сохраняет соединение открытым в течение всего времени, пока подключен клиент.
  • Объединение транзакций: соединения действуют в течение одной транзакции, после чего отправляются обратно в пул.
  • Объединение операторов: соединения рассчитаны только на запрос, поэтому, если в транзакции их несколько, это вообще не сработает.

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

Влияние пула соединений на производительность

Однако вопрос на миллион долларов: это действительно работает? Percona провела серию тестов, чтобы выяснить, как PGBouncer влияет на производительность. При небольшом количестве одновременных клиентов (<60) PGBouncer фактически деградирует транзакций в секунду (TPS) значительно из-за накладных расходов на объединение в пулы. Но к тому времени, когда вы масштабируетесь до >100, вы начинаете видеть значительные преимущества в производительности.

PGBouncer

Так нужен ли вам сразу же пул соединений для поддержки первых нескольких пользователей? Возможно нет. Но использование PGBouncer поможет вам, когда вы достигнете даже низкого/умеренного трафика.

Безопасность Postgres для чайников

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

Ограничение доступа на уровне хоста или пользователя

Начнем с доступа. Postgres ограничивает доступ двумя способами:

  1. На уровень хоста – определение IP-адресов и доменов с правами доступа
  2. На уровень пользователя – определение пользователей базы данных и их разрешений

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

# Trust any connection via TCP/IP from this machine
host all 127.0.0.1 255.255.255.255 trust

Помимо простого «доверять любым соединениям с этой машины», существует множество различных способов аутентификации вашего клиента на сервере базы данных: от пароля до идентификатора и сертификатов. А если вы отказались от больших удобств RDS (или Kinsta) и используете свой сервер на том же сервере, что и ваша база данных, вы можете подключиться через сокеты Unix вместо TCP/IP.

Авторизация и привилегии

После того, как ваш клиент сам аутентифицирован, вам нужно разобраться с вопросом авторизации. Стандарт SQL определяет систему привилегий, и каждый объект в Postgres (например, таблица, строка и т. д.) имеет различные привилегии, относящиеся к нему, которые могут быть назначены пользователям: например, SELECT и UPDATEно и TRUNCATE, REFERENCES, TRIGGERи т. д. Вы предоставляете привилегии пользователям с GRANT команда.

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

Безопасность на уровне строк

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

ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY

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

ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY
CREATE POLICY jedi_only ON lightsaber_internals TO jedi
    USING (jedi = lightsaber_jedi);

Подобные политики RLS полезны, когда вам нужна безопасность на более детальном уровне, чем просто таблицы (ситуации с личными данными и т. д.).

Заранее подумайте о проблемах масштабирования

В каждом стартапе, над которым я когда-либо работал, база данных масштабировалась вручную. Однажды вы проснетесь, а Datadog сойдет с ума, потому что на вашем сервере Postgres полностью не хватает места. Вы проведете расследование, обновите страницу инцидентов и, в конечном итоге, увеличите размер диска, пока это не повторится (хотя в следующий раз это может быть проблема с оперативной памятью). Заблаговременность в этом вопросе может помочь! Несколько предложений:

1. Настройте мониторинг базы данных

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

2. Составьте рекомендации по вертикальному масштабированию

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

3. Пылесос и настройка автопылесоса

Когда ты DELETE данные в Postgres или UPDATE data (что функционально эквивалентно удалению и вставке), Postgres фактически не удаляет эти данные сразу (😱). Вместо этого он «помечается» как удаленный путем сохранения идентификатора транзакции удаления в хмакс заголовок; причина этого в том, что это делает MVCC в Postgres более простым. Но если эти строки не Действительно если их в конечном итоге удалить, они начнут тратить дисковое пространство и создавать проблемы.

Самый простой способ избавиться от этих строк — использовать VACUUM команда. Вы можете запускать очистку вручную всякий раз, когда накапливаются мертвые строки, или даже просто настроить ее на запуск каждые x минут, но лучшая стратегия — автоматическая очистка в зависимости от того, сколько накопилось мертвых строк. Настройка автоочистки — это тонкая тема, выходящая за рамки этого поста: я настоятельно рекомендую прочитать об этом пост 2ndQuadrant.

4. Настройте реплику чтения (или две)

Это легко. Если вы ожидаете значительного увеличения трафика (предстоящий запуск и т. д.), вы можете легко создать реплики только для чтения (или хотя бы одну); они помогут разгрузить часть работы из основного экземпляра БД.

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

Добавьте индексы в ваши (ожидаемые) самые большие таблицы

Индексы базы данных помогают ускорить запросы на чтение за счет создания вспомогательных структур данных, которые ускоряют сканирование. Во многих случаях добавление индекса к одной или двум таблицам практически не составляет труда. В Postgres вы можете создать индекс с помощью CREATE INDEX команда (да). Когда вы запрашиваете таблицу, база данных проверит, существует ли индекс, и использует его, если он есть (вы можете убедиться, что это происходит с помощью EXPLAINкстати).

Самый популярный тип индекса в Postgres – и стандартный при использовании. CREATE INDEX – это Индекс B-дерева. По сути, он берет столбец, для которого вы хотите создать индекс, сортирует его и сохраняет указатели на отсортированные строки. Таким образом, вы можете повысить эффективность двоичного поиска по любому столбцу, который вам нужен, а не только по столбцу, по которому сортируется фактическая таблица (если он вообще существует). Более подробную информацию о том, как эти деревья реализованы, можно прочитать в документации Postgres здесь.

Несмотря на свою полезность, индексы — это не только развлечение и игра; они занимают место, и если вы не будете осторожны с тем, сколько и какого типа вы создаете, они могут фактически начать снижать производительность базы данных. Никто не говорит об этом лучше, чем сами документы Postgres:

«Индексы в основном используются для повышения производительности базы данных (хотя неправильное использование может привести к снижению производительности)».

Под капотом, когда вы создаете индекс, Postgres материализует таблицу поиска, содержащую индекс и указатель на запись индекса. Слишком многие из этих таблиц занимают дисковое пространство, делают запросы INSERT более длительными и вынуждают механизм запросов рассматривать больше вариантов, прежде чем выбирать, как выполнить запрос.

Бонус: добавьте несколько расширений Postgres

Уникальность Postgres заключается в встроенной поддержке расширений третьих сторон. Вы можете создать их из SQL и C, и они могут быть как небольшими, как пара операторов, так и большими, как целая программная библиотека. Использование общедоступных расширений/расширений с открытым исходным кодом помогает вам так же, как и использование пакета программного обеспечения; зачем писать свой собственный код, если можно использовать чужой? Вот несколько наиболее популярных расширений Postgres:

Сроки

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

ПостГИС

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

pg_stat_staments

pg_stat_statements создает представление в вашей базе данных Postgres со статистикой по каждому запросу, выполненному в базе данных. Вы можете увидеть статистику, например, сколько времени занимает выполнение запроса (среднее, медианное, среднее и т. д.), кто запускает запрос, попадания в кэш блоков, количество записанных блоков и многое другое (всего 44 столбца в этом представлении). Для установки просто добавьте его в свой файл .conf и перезапустите сервер.

pg_audit

pg_audit помогает компаниям, которые могут подвергаться детальному аудиту (например, государственному, финансовому и т. д.). Вы можете заставить Postgres регистрировать каждый оператор в базе данных, установив `log_statement=all`, но это не означает, что нужную вам информацию будет легко найти. pg_audit использует внутренние функции ведения журналов Postgres, чтобы упростить поиск и работу с теми журналами, которые могут понадобиться аудитору. Инструкции по установке вы можете найти здесь.

Краткое содержание

Postgres — отличный (и очень популярный) вариант для построения вашей компании, и мы с гордостью поддерживаем его в Kinsta. Мы надеемся, что эти советы помогут вам начать работу и подготовиться к масштабированию. Другие советы или мысли из вашего опыта? Дайте нам знать здесь.