#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. А вот перезапустить не получается, потому что родительский процесс при остановке инстанса умирает, убивая дочерний, который уже не может осуществить запуск нового инстанса. Так и сижу целый день. Придётся лезть на сервер и там тестировать локальным скриптом.. Ну или плюнуть на очистку кэшей и объективность результатов. Тем более что файловый кэш ОС всё равно останется.

https://www.youtube.com/watch?v=lV98atOLx2s&t=1214s&ab_channel=CitusData

#dba #postgresql #optimization

Оказывается, PG выравнивает предыдущие поля по размеру самого большого следующего поля. Т.е. просто от перестановки табличных полей в другом порядке можно сохранить до 20% пространства! Также интересно, что индексы всегда "добиваются" до 8 байт, так что если Ваш индекс по полю 4 байта, его можно сделать покрывающим (including) и там бесплатно хранить ещё одно 4-байтовое поле...