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

Данные

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

• Количество объявлений: 142 062 628
• Количество объявлений, которые просканированы сервисом DaData: 40102289

Таблицы

Предыдущее решение оказалось недостаточно хорошим и имело множественные допущения, которые позже негативно сказались на общем фоне работы. Соответственно, было принято решение о пересборке базы данных. 

Ранее база данных с объявлениями была в одной таблице, а это серьезно сказывалось на работе с данными. После недельной работы были созданы четыре независимые друг от друга таблицы с одинаковым префиксом (adverts_2018, adverts_2019, adverts_2020, adverts_2021), куда были перекинуты данные с общей таблицы. 

Еще раньше возникла идея вынести адреса из этих таблиц, дабы полностью избавиться от постоянной выборки уникальных адресов по трем полям: номер региона, текстовое поле «город» и текстовое поле «адрес». Была создана таблица addresses с уникальным индексом по трем полям. Затем, был написан специальный запрос на основе CTE запросов в psql для копирования уникальных полей из таблиц с объявлениями в таблицы с адресами и вставки id первого в таблицы с объявлениями. Самого запроса не осталось, но остался псевдозапрос:

WITH insert_cte AS ( INSERT INTO addresses(id_region, city, address) SELECT id_region, city, address FROM adverts_X ON conflict (id_region, city, address) do nothing RETURNING addresses.id) UPDATE adverts_X SET guid_author=insert_cte.id FROM insert_cte;

Позже в эту таблицу были добавлены дополнительные поля и сейчас она имеет вид, как на рисунке ниже.

Поле gps вида ST_SetSRID(ST_MakePoint(geo_lat::double precision, geo_lon::double precision), 4326). 

Из-за того, что размер NVME был около 450 GB, то пришлось добавить дополнительную «партицию» на жестком диске, чтобы перекидывать на диск таблицы, а уже на NVME делать после этого VACUUM VERBOSE FULL;

У каждого объявления есть дополнительные параметры, которые указываются авторами объявлений (extra_options). По исследованиям с алгоритмами нейронок (random forest + xgboost) было выяснено, что поля object_type (тип объекта), building_type (тип строения), level (этаж), levels (этажность), rooms (количество комнат), area (площадь) и kitchen_area (площадь кухни) очень важны. По этим же полям можно узнать, что, например, два объявления, выложенных через два разных источника — это одна и та же квартира. 

В поле cache хранится массив массивов объявлений вида {‘ГГГММ’:[1,2,3]} которые подходят под данные аргументы. В качестве значений хранятся ассоциативные массивы в формате ключа ГГГГММ объявления, в которых уже хранятся списки номеров объявлений. При поиске, я вытаскиваю номера уже в отсортированном по месяцам формате, что также позволит учитывать фильтрацию по дате без прогонки по основным таблицам adverts_… .