Как правильно работать с большим количеством данных?

И проблема тут не в незнании как работать с большими базами данных, а в неумении работать с БД в целом.

Про идею «всем индекс не поставишь» надо сразу забыть. Там где индекс нужен, он должен стоять без вариантов. Другое дело что тупо натыкать индексов по всем полям, по которым идёт поиск — это тоже глупость. Индекс в запросе может использоваться только один, и индексы по второму-третьему полю уже будут бесполезны. Надо анализировать запросы и, возможно, делать составные индексы.

Детсадовский запрос вида like ‘%…%’ — это отдельный ужас. Надо смотреть на полнотекстовый поиск. А лучше вообще его избегать. На крайний случай использовать внешние поисковые сервисы типа эластика. И только не говори что этот лайк у тебя идёт по полю типа джейсон или «через запятую»

Но самый конечно ужас — это select distinct для фильтров. То есть неумение проектировать бд на самом базовом уровне, непонимание самых начальных принципов реляционных бд, нормализации. Вот с этих принципов и надо начать. В потом уже хвататься за большие объемы. Очевидно, что поля по которым ты собрался делать «distinct» — это должны быть отдельные таблицы, от которых в основной таблице будет просто id. поле размером в 4 байта.

Непонятно, откуда взялись фантазии про гигабайтные индексы, кстати. Большая часть полей в нормальной бд — это не больше десятка байт. То есть индекс — это десятки мегабайт, а не «гигабайты».

В общем, куда лучше бы смотрелись здесь не абстрактные рассуждения про большие объёмы, а конкретный запрос, который «отваливается». С обязательным результатом EXPLAIN

А ответ на абстрактный вопрос «как работать с большими объемами» очень простой: точно так же, как с небольшими. Реляционные бд изначально проектировались под большие размеры. То есть надо просто уметь работать с бд. Читать про реляционную модель, нормализацию, индексы, оптимизацию запросов.

Конкретно для грида надо смотреть в сторону Эластика/Сфинкса. В смысле чтобы не только для полнотекстового поиска, а чтобы все фильтры, которые есть выборке, были забиты в поисковый индекс. И все выборки — через поисковый сервис, а не через прямой запрос к базе

— получение данных об общем количестве записей для построения пагинации, это SELECT count(id)

1. count(*), а не count(id)
2. если вас не сильно интересует абсолютно точное значение для миллионов результатов, то делаете оценку количества, начать проще с EXPLAIN <текст запроса> вы можете получить оценку количества результатов. Мы для себя решили, что если по оценке меньше 50 000 строк, то вслед за этим делаем обычный SELECT count(*) для получения точного количества.

Потом дергается запрос для получения данных на экспорт

1. Пожалуй, вам от этого нужно избавляться в первую очередь. Нажал человек на кнопку «экспортировать» — экспортируете, а до этого и дёргаться нет смысла. Фильтры можно получить либо при клике, либо из заголовка referer
2. Если вам нужно абсолютно все данные, то ставите задание на экспорт в очередь и выполняете его в отдельном процессе, сохраняете в файл. Для пользователя рисуете прогресс выполнения и выводите его в нажатую пользователем кнопку, хотя можно тупо на отдельной странице выводить список «заказанных» выгрузок и ссылки на скачивание.

Запросы на каждый выпадающий список в фильтрах — SELECT distinct field_name

Можно с какой-то периодичностью выгружать выхлоп таких запросов в материализованное представление / справочную таблицу / ENUM. Для обновления таких справочников «в реальном времени» можно повесить триггер на вставку в основную таблицу который будет делать INSERT INTO dictionary (value, column_oid) ON CONFLICT / ALTER TYPE ADD VALUE IF NOT EXISTS
После чего в основной таблице заводите рядом поле под идентификатор в справочнике и индексируете уже его.

Запрос при фильтрации и сортировке — SELECT * FROM some_table WHERE field_name LIKE ‘%value%’

1. если у вас значения длинные (от 8 — 10 символов), то стоит попробовать триграммные индексы. Но на коротких значениях они могут замедлить поиск раза в полтора-два.
2. Полнотекстовый поиск. В частности есть поиск лексемы по префиксу ts_tsquery(‘сло:*‘) (быстро найдёт и «слово» и «словарь», но не найдёт «однословное»)
3. Для полей по которым вы сделаете словари лучше делать поиск через словарь SELECT * FROM table WHERE column_dictionary_id IN (SELECT id FROM dictionary WHERE value LIKE ‘%текст%’). В словаре у вас наверняка на порядок — три меньше значений, а несколько сотен или тысяч значений в IN постгрес нормально пережуёт.

Полей много, разные даты, guid, названия проектов, данные из поля типа json, цены.

Активнее используйте функциональные и частичные индексы.
Например, у нас есть кадастровые номера. Триграммный индекс по ним весит 56 мбайт, а BTREE по номерам урезанным до кадастровых кварталов — 15 мбайт, в поиске к «cadastre_id LIKE ’11:22:333333:1%'» добавился «AND to_quarter(cadastre_id) = ’11:22:333333′», но сам поиск получается на порядок быстрее (~5 мсек вместо 50 — 70).
Главное не забывайте о стоимости этих самых функций — индекс по to_quarter может строиться всего в 1,5 раза дольше нефункционального, если делать LEFT(cadastre, -(position(‘:’ IN reverse(cadastre))), а может и в 100 раз, если использовать регулярку.

На все индексы не поставишь, тем более что один индекс может добавить гигов 5-10 к весу.

Если ещё не обновились, то обновляйтесь на 13-ю версию, там размер BTREE индексов уменьшили в 3 раза. Ну и посмотрите, возможно вам где-то нужны GIST, GIN или BRIN индексы.

а вообще — книжка
https://dmkpress.com/catalog/computer/databases/97…

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