Большие данные или не очень?

БД PostgreSQL 12.2 с включенной поддержкой PostGIS.

Существует таблица Adverts из 58 млн записей, состоящая из полей, которые изображены на фото. Размер таблицы — около 165 GB. Идексация существует по полям: id — счетчик (2535 MB), id_region (2527 MB). Нужно отметить, что в поле fias_id записывается ключ из таблицы «fiasHome».

Задача: нормализовать адреса и сгруппировать объявления по адресу, выделить дополнительные параметры. Нормализация происходит при помощи сервиса DaData, которому отправляется строка адреса, а возвращается данные, например, фото:

Что сейчас делается с таблицей adverts. Обновление поля fias_id, когда идет нормализация. Поиск по id.

Вставка же новых объявлений от ads-api идет в такую же по структуре таблицу (называется adverts_api), ибо пока думаю, что с этим делать, а добавить в другую таблицу всегда можно.

Вариант решения 1:

1) Сделать секционирование БД. Многие советуют этот вариант. Например, https://habr.com/ru/company/barsgroup/blog/481694/ (в статье неравные условия тестирования скоростей)

Минусы:

— Преобразовать обычную таблицу в секционированную и наоборот нельзя

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

Из этого следует, что придется сделать некое COPY из обычной таблицы в секционированную (https://github.com/kibermat/pg), затем разобраться в этом тихом ужасе, прописать запросы. Это всё займет около недели или двух недель.

Плюсы:

++ Зная в какую секцию нам нужно заглядывать, мы можем сократить время на обработку данных. Но (дальше указаны «неудобства»)

++ Не нужно делать VACUUM FULL;

Что с запросами?

Как он идет сейчас? SELECT …поля… from adverts where id in (1, 1545, 44500545); Учитывая, что id-шники раскиданы по всей таблице, то и разница среди них очень сильная.

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

Как секционировать?

— По месяцам. В одном месяце около 5-6 млн объектов

— По годам. Получится около 70млн объектов.

— По регионам. Некоторые регионы придется группировать. У некоторых регионов количество будет расти в бесконечность.

— По регионам и годам. Слишком сложно

Вариант решения 2:

Дождаться 200млн записей, а там видно будет:)

Вернемся к нормализации адресов

Все данные, которые возвращает DaData, нужно хранить. Зачем? потому что там множество запросов завязаны на этом.

Какие запросы?

1) Поиск домов/объявлений, учитывая радиус от определенного объекта. На помощь приходит поле gps вида ST_SetSRID(ST_MakePoint(geo_lat::double precision, geo_lon::double precision), 4326)

2) Поиск домов. Поиск идет по fias_id (то есть реальный ФИАСID, взятый из ФНС дадатой).

3) Еще некоторый тип запросов, которые не могу сейчас вспомнить…

Это всё надо сохранять. Мне показалось не очень приятным эти данные приписывать к полям таблицы adverts. Почему? Да потому что один дом может иметь овер1000 объявлений и во все нужно добавлять одинаковые данные…

Поэтому нужно добавить еще одну таблицу. Но получилось, что добавил 3 штуки.

1 таблица. Текстовые названия улиц, районов, городов и тд, хранятся в отдельной таблице. Поиск по числам гораздо быстрее, чем по строке, также можно сэкономить на дублировании данных. Всё-таки символы больше занимают памяти, чем формат чисел. Количество строк на 27 августа: 21 484 штук.

2 таблица. В таблицу «fiasAddress» я сохраняю номера и фиасы (оказалось, это нужно для API с реформаЖКХ). Количество строк на 27 августа: 44 386 штук. Часто бывает, что некоторые поля отстутствуют, у них «0».

3 таблица.Таблица «fiasHome». Таблица, по которой работают запросы на поиск. Количество строк на 27 августа: 173 230 штук.

В поле cache хранится массив объявлений вида [1,2,3], которые подходят под данные аргументы. Поле gps вида ST_SetSRID(ST_MakePoint(geo_lat::double precision, geo_lon::double precision), 4326). В поле fias_id хранится ФИАС дома. В поле id_fias_address хранится номер из предыдущей таблицы.

Возможный баг, что по одному дому есть несколько записей, учтен.

Что думаю сделать?

В поле cache хранится как раз таки массив из первой рассматриваемой таблицы. Этот массив думал разбить на массив массивов по годам (или месяцам). Зачем? Будет ясно видно, какие данные за предыдущий месяц, а какие данные за текущий год. Клиенту можно передавать полный массив как и передавался раньше. Также можно будет дополнительно клиенту учитывать в запросе старость объявлений, например «Показать объявления по Москва ул Ленская 9 за 2019 год».

Нужно добавить, что еще по каждому дому из таблицы fiasHome делается запрос в РеформаЖКХ с 51 полями. Для «совместимости» есть поля id, id_fias_home и id_fias_address (на всякий). Данных много, так что мне показалось, что лучше хранить это в отдельной таблице.