#postgresql
#postgresql
Вопрос:
У меня очень большая база данных с более чем 1,5 миллиардами записей для данных устройства и растет.
Я справляюсь с этим, имея отдельную таблицу для каждого устройства, около 1000 устройств (таблиц) с индексной таблицей для ежедневной статистики. Некоторые устройства производят гораздо больше данных, чем другие, поэтому у меня есть таблицы с более чем 20 миллионами строк, а у других — менее 1 миллиона.
Я использую индексы, но запросы и обработка данных в больших таблицах выполняются очень медленно.
Я только что обновился до PostgreSQL 13 с версии 9.6 и попытался создать одну единственную таблицу с хэш-разделом с минимум 3600 таблицами, чтобы импортировать все таблицы в эту и ускорить процесс.
Но как только я это сделал, я смог вставить несколько строк, но когда я пытаюсь запросить или посчитать строки, у меня заканчивается общая память, и возникает проблема с максимальными блокировками на транзакцию.
Я попытался выполнить точную настройку, но безуспешно. Я сбросил таблицы до 1000, но при определенных операциях я снова получаю ошибку, просто для тестирования я снизился до 100, и это работает, но запросы выполняются медленнее с тем же количеством данных в отдельной таблице.
Я попробовал разделение диапазона в каждой отдельной таблице за год и улучшил, но будет очень сложно поддерживать тысячи таблиц с годовыми диапазонами (обратите внимание, что я работаю на сервере с 24 виртуальными процессорами и 32 ГБ ОЗУ).
Вопрос в том, возможно ли иметь хэш-раздел с более чем 1000 таблицами? Если да, то что я делаю не так?
Комментарии:
1. Ну, ошибка «максимальные блокировки за транзакцию» может быть легко устранена путем увеличения этого предела. Однако секционирование должно определяться либо требованием эффективного удаления большого количества строк, либо запросом, который вам нужно выполнить. Если вы хотите использовать секционирование по соображениям производительности, вам необходимо использовать ключ секционирования, который используется в предложении WHERE всех запросов (или, по крайней мере, всех запросов, связанных с производительностью). Возможно, разделение диапазона по устройствам имело бы смысл. Хотя 1000 разделов выходят за рамки, я думаю, что это должно работать в Postgres 13
2. Спасибо, я только что понял, что строка max_locks_per_transaction была прокомментирована по умолчанию, теперь она работает с 3653 таблицами разделов, я скопировал одну таблицу 5M rows в эту секционированную таблицу, и теперь она может считать и выбирать строки, используя предложение WHERE с ключом секционирования, но выполнение того же запроса в исходной таблице заняло 253ms в то время как в секционированной таблице потребовалось 6,5 секунд, поэтому я думаю отказаться от идеи единой секционированной таблицы и определить раздел диапазона для каждой таблицы устройств.