#databases #postgres

Немного лучших практик Постгрес в ленту.

https://www.youtube.com/watch?v=0_CwH_lrPp0&ab_channel=HighLoadChannel

ORM: не пробовал, ни разу не увидел выгоды от них. Может, просто не разобрался.

Репликация: знаю интересный кейс с pg_logical: https://habr.com/en/company/alfa/blog/539350/.

Партицирование: для меня хорошо сработало расширение TimescaleDB, сделанное, по словам Панченко, на основе pg_partman. Не только создаёт партиции автоматически, но и может архивировать их, имеет мощные time_bucket функции. Пока не работает с PG15 (решают). На голову выше коробочного партицирования: не засоряет схему тысячами партиций (потому что пишет в отдельную схемку), не требует ручного создания разделов, поддерживает политики управления данных. Прозрачный бэкап, единственное, при восстановлении расширение уже должно стоять. Если у Вас (будет) большая база, сразу используйте таймскейл. Уж тем более, если данные представляют собой временные ряды - сам Бог велел.

По шардингу: в TimescaleDB он есть, но я ещё не пробовал. Распределённая гипертаблица сразу же подпадает под значительное число ограничений (https://docs.timescale.com/timescaledb/latest/overview/limitations/#distributed-hypertable-limitations). Citus/Greenplum было бы интересно попробовать тоже.

Config tuning: вот тут у постгреса (да и почти всех остальных СУБД) слабое место, разработчики ядра очень консервативны. Казалось бы, век информации, ML, DS, AI, а тут на тебе, СУБД даже не может посчитать сама оптимальные параметры для железа, на котором запущена, я уже не говорю о динамическом перераспределении параметров в зависимости от нагрузки. На Хабре даже ругался по этому поводу: https://habr.com/en/post/444018/, https://habr.com/en/post/458952/. Опять же, таймскейл предлагает при установке свои настройки в конфиг, не тестировал их преимуществ, но от дефолтных они сильно отличаются, это точно.

#postgres #storage #optimization #dba
Возникла в процессе работы над моделью соцсети проблема: гигантский размер таблицы фолловеров. При количестве юзеров порядка 130K количество связей между ними уже превышает 80M. Юзер имеет от нуля до 800K подписчиков (медиана 25, среднее 468) и сам подписан на от нуля до 160К инфлюенсеров (медиана 28, среднее 186).
Табличка вида
create table users_followers(
target_user_id text ,
following_user_id text,
link_prev_from bigint,
link_next_from bigint,
position smallint,
added_at timestamp default (now() at time zone 'utc'),
primary key (target_user_id,following_user_id)
);
занимает уже 6 Гб, а первичный ключ - 4Гб. link_prev_from/ link_prev_to/position это, по сути, оценки времени, когда юзеры подружились; added_at это момент времени, когда связь была установлена внешним наблюдателем.
Хранится всё в ванильном Постгре 15 на Ubuntu 20.04 LTS, FS=ext4. После недавнего инцидента никаких дисков кроме ssd для баз я использовать не хочу, следовательно, пространство дорого (скажем, на 1 машинке есть 3.5TB SSD storage). С учётом того, что данные после записи не модифицируются и не удаляются, а только читаются и вставляются, и всего ожидается около 100M юзеров, как бы Вы подошли к оптимизации хранения таких связей? Запросы сводятся к
1) случайной выборке флолловеров (и последующей проверке их id по другой табличке) SELECT following_user_id FROM users_followers TABLESAMPLE SYSTEM_ROWS(1000)
2) получению всех записей/всех полей по target_user_id в пакетном режиме.
Итоговым решением поделюсь позже, а пока буду рад советам ) Вдруг подобную задачу подписчики уже решали.
Пример строки target_user_id,following_user_id,link_prev_from,link_next_from,position,added_at:
"10000187a48d";"fc632fb4596f";1657686648645;1657686624495;23; "2023-01-11 13:02:11.064876"
Размеры заполненных полей в байтах: 13,13,8,8,2,8.

#dba #postgres #troubleshooting

Целая эпопея с тестированием СУБД. В свете предыдущего поста, у меня появилось несколько вариантов организации данных в табличке. Как человеку приличному, захотелось их объективно сравнить как по занимаемому на диске месту, так и по скорости вставки и чтения данных. Понятно, что замерять для это придётся несколько раз, и оценивать среднее и разброс. Также ясно, что после первого запроса данные уже будут в кэше, и остальные сравнения будут необъективны. Естественно, приходим к необходимости сброса кэшей. Оказывается, в постгре такого нет, сброс достигается только полным рестартом инстанса. Ну ладно, думаю, тогда хотя бы как рестартить инстанс СУБД sql запросом, наверняка же можно? Нет такого. Ладно, откапываем питоновский язык plpython3u и учимся его ставить на сервер. Теперь-то можно с помощью subprocess запускать системную команду sudo service postgresql restart? Фиг там... Инстанс-то запущен под отдельным несистемным юзером. Ну ладно, а pg_ctl-ом то можно? Оказывается, можно потушить с помошью /usr/lib/postgresql/15/bin/pg_ctl stop -D /var/lib/postgresql/15/main. А вот перезапустить не получается, потому что родительский процесс при остановке инстанса умирает, убивая дочерний, который уже не может осуществить запуск нового инстанса. Так и сижу целый день. Придётся лезть на сервер и там тестировать локальным скриптом.. Ну или плюнуть на очистку кэшей и объективность результатов. Тем более что файловый кэш ОС всё равно останется.

#postgres #zfs #optimization #debugging #profiling

https://postgrespro.com/blog/pgsql/5969223

#postgres #zfs

https://people.freebsd.org/~seanc/postgresql/scale15x-2017-postgresql_zfs_best_practices.pdf

#postgres #zfs

https://vadosware.io/post/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux/

#postgres #rdbms

Выбирают постгре-совместимую СУБД для распределённого хранения данных (на многих серверах), частично они состоят из временных рядов (неизменяемые, append-only) без первичного ключа (типа показаний сенсоров) , частично из записей с меткой времени, но всё же подверженных нечастому изменению, уже с первичным ключом. часть с json, часть нормализованная. Таблицы большие. Рассматриваю TimescaleDB vs Citus. Если есть опыт использования, отпишите впечатления в комменты, плиз.

#postgres #tuning

https://www.cloudbees.com/blog/tuning-postgresql-with-pgbench

#postgres #pgbench #zfs

Потестил pg_bench PG15 на SSD в ext4 и zfs (с шифрованием/сжатием и без) в Ubuntu 22.04.

ZFS ENCRYPTED & COMPRESSED (LZ4):

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 4.891 ms
initial connection time = 34.946 ms
tps = 2044.768698 (without initial connection time)

sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 4.552 ms
initial connection time = 17.378 ms
tps = 2196.925697 (without initial connection time)

ZFS ENCRYPTED & COMPRESSED (LZ4) TUNED WITH TIMESCALEDB:

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 4.031 ms
initial connection time = 31.661 ms
tps = 2480.899924 (without initial connection time)

latency average = 3.850 ms
initial connection time = 31.273 ms
tps = 2597.574037 (without initial connection time)

latency average = 3.889 ms
initial connection time = 32.503 ms
tps = 2571.192661 (without initial connection time)


sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 3.784 ms
initial connection time = 27.329 ms
tps = 2642.536852 (without initial connection time)

latency average = 3.741 ms
initial connection time = 17.922 ms
tps = 2673.011469 (without initial connection time)

latency average = 3.679 ms
initial connection time = 16.235 ms
tps = 2718.262035 (without initial connection time)


ZFS NON-ENCRYPTED & COMPRESSED (LZ4):

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 4.660 ms
initial connection time = 38.052 ms
tps = 2146.130682 (without initial connection time)

latency average = 4.507 ms
initial connection time = 39.378 ms
tps = 2218.757361 (without initial connection time)

latency average = 4.266 ms
initial connection time = 36.697 ms
tps = 2344.329379 (without initial connection time)


sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 3.936 ms
initial connection time = 21.317 ms
tps = 2540.753366 (without initial connection time)

latency average = 3.915 ms
initial connection time = 17.523 ms
tps = 2554.329177 (without initial connection time)

latency average = 4.076 ms
initial connection time = 21.361 ms
tps = 2453.572519 (without initial connection time)


ZFS NON-ENCRYPTED & COMPRESSED (LZ4) TUNED WITH TIMESCALEDB:

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 3.648 ms
initial connection time = 34.840 ms
tps = 2741.206504 (without initial connection time)

latency average = 3.473 ms
initial connection time = 39.593 ms
tps = 2879.122987 (without initial connection time)


latency average = 3.592 ms
initial connection time = 34.987 ms
tps = 2783.957080 (without initial connection time)

sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 3.346 ms
initial connection time = 21.312 ms
tps = 2988.591798 (without initial connection time)

latency average = 3.178 ms
initial connection time = 18.359 ms
tps = 3147.071197 (without initial connection time)

latency average = 3.337 ms
initial connection time = 19.320 ms
tps = 2996.486500 (without initial connection time)

EXT4 NON-ENCRYPTED NON-COMPRESSED TUNED WITH TIMESCALEDB

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 2.596 ms
initial connection time = 86.466 ms
tps = 3852.682810 (without initial connection time)

latency average = 2.751 ms
initial connection time = 89.400 ms
tps = 3635.586811 (without initial connection time)

latency average = 2.719 ms
initial connection time = 92.922 ms
tps = 3677.184800 (without initial connection time)

sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 2.643 ms
initial connection time = 50.687 ms
tps = 3784.041569 (without initial connection time)

latency average = 2.518 ms
initial connection time = 42.306 ms
tps = 3971.158272 (without initial connection time)

latency average = 2.560 ms
initial connection time = 40.711 ms
tps = 3906.430062 (without initial connection time)

#postgres #json #nosql

https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/

#zfs #postgres #tuning

https://bun.uptrace.dev/postgres/tuning-zfs-aws-ebs.html#zfs-recordsize

#postgres #backup

https://bun.uptrace.dev/postgres/pgbackrest-s3-backups.html

#postgres

"pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing. pg_repack is efficient to boot, with performance comparable to using CLUSTER directly."

https://github.com/reorg/pg_repack/

#db #tuning #postgres

Любопытная ситуация. За много лет DBA-шное сообщество, кажется, ничего так и не придумало для оптимизации параметров СУБД. Уж на примере Постгре точно. Разрабам ядра PG положить с прибором на это, они слишком старомодны и консервативны, до сих пор считают, что рабочую память, да и все остальные параметры, сисадмин должен ручками прописывать. Есть pg_bench, но неясно, как его толком применить к настройке. Ведь параметры СУБД взаимодействуют с настройками ОС и ФС, причем зачастую нелинейно. А есть же ещё параметры железа. А еще версии ОС, ФС, СУБД. А еще разные запросы и разное распределение данных в таблицах, поэтому универсальные рекомендации дать трудно. Надо или брутфорсить сотни тысяч комбинаций pgbench-ем (на сервере близком к боевому), или оверпровижинить, или забивать на оптимальность. Почему я один в этом вижу проблему? PostgresPro, возможно, как-то над этим работают, но с их ценником в миллион за ядро я их даже не рассматриваю.

#postgres #tuning

Отличное руководство по параметрам постгре, в логической разбивке по группам (память, планировщик, безопасность, подключения, репликация, итд.)

https://www.youtube.com/watch?v=13d4BDYSYyM&ab_channel=EDB

#postgres #tuning

Отличный рассказ про тюнинг параметров СУБД.

https://www.youtube.com/watch?v=IFIXpm73qtk&ab_channel=HighLoadChannel

#postgres #db

А тем временим готовится к релизу Постгре 16.

Из интересного:

Allow parallelization of FULL and internal right OUTER hash joins (Melanie Plageman, Thomas Munro)
Allow optimization of always-increasing window functions ntile(), cume_dist() and percent_rank() (David Rowley)

Add system view pg_stat_io view to track IO statistics (Melanie Plageman)
Record statistics on the last sequential and index scans on tables (Dave Page) This information appears in pg_stat_all_tables and pg_stat_all_indexes.

Create a predefined role and grantable privilege with permission to perform maintenance operations (Nathan Bossart) The predefined role is is called pg_maintain.
Add predefined role pg_create_subscription with permission to create subscriptions (Robert Haas)

Add support for regular expression matching on database and role entries in pg_hba.conf (Bertrand Drouvot)

Allow NUMERIC to process hexadecimal, octal, and binary integers of any size (Dean Rasheed) Previously only unquoted eight-byte integers were supported with these non-decimal bases.
Allow underscores in integer and numeric constants (Peter Eisentraut, Dean Rasheed) This can improve readability for long strings of digits.
Accept the spelling "+infinity" in datetime input (Vik Fearing)

Allow parallel application of logical replication (Hou Zhijie, Wang Wei, Amit Kapila)

Allow the STORAGE type to be specified by CREATE TABLE (Teodor Sigaev, Aleksander Alekseev) Previously only ALTER TABLE could control this

Allow subqueries in the FROM clause to omit aliases (Dean Rasheed)

Change date_trunc(unit, timestamptz, time_zone) to be an immutable function (Przemyslaw Sztoch) This allows the creation of expression indexes using this function.
Add functions array_sample() and array_shuffle() (Martin Kalcher)
Add aggregate function ANY_VALUE() which returns any value from a set (Vik Fearing)
Add function random_normal() to supply normally-distributed random numbers (Paul Ramsey)
Add error function erf() and its complement erfc() (Dean Rasheed)

Add libpq option sslcertmode to control transmission of the client certificate (Jacob Champion) The option values are "disable", "allow", and "require".

Add LZ4 and Zstandard compression to pg_dump (Georgios Kokolatos, Justin Pryzby)
Allow pg_dump and pg_basebackup to use "long" mode for compression (Justin Pryzby)

Add support for SSE2 (Streaming SIMD Extensions 2) vector operations on x86-64 architectures (John Naylor) вот это странно, этой поддержки не было, чтоль?
Add support for Advanced SIMD (Single Instruction Multiple Data) (NEON) instructions on ARM architectures (Nathan Bossart)

Require Windows 10 or newer versions (Michael Paquier, Juan José Santamaría Flecha) Previously Windows Vista and Windows XP were supported. Вот это вряд ли можно считать улучшением.


https://www.postgresql.org/docs/16/release-16.html

#databases #postgres

А тем временем вышла 17-я постгре! Много улучшений. Я не понимаю, как у них поднимается рука релизить заведомо слабые решения, а потом течение лет и десятилетий их постепенно допиливать, но хоть так.

https://www.postgresql.org/about/news/postgresql-17-beta-1-released-2865/