Ответы пользователя по тегу PostgreSQL
  • Как правильно работать с большим количеством данных?

    batyrmastyr
    @batyrmastyr
    - получение данных об общем количестве записей для построения пагинации, это 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 индексы.
    Ответ написан
    2 комментария
  • Как организовать пагинацию, если БД и поисковая машина - это раздельные сервисы?

    batyrmastyr
    @batyrmastyr
    1. Попробуйте искать целиком в сфинксе используя распределённые индексы.
    index rss {
    type = distributed
    agent_persistent = 127.0.0.1:9312:rss1
    ...
    agent_persistent = 127.0.0.1:9312:rssN
    agent_persistent = 127.0.0.1:9312:rssUpdates
    }
    В первые N частей закидываете все документы более-менее равномерно их распределив, например, по датам создания или номерам и индексируете редко, раз в сутки или неделю.
    В последний rssUpdates выбираете только документы изменившиеся с последней индексации, прописываете sql_query_killlist чтобы исключить из результатов устаревшие копии документов из других индексов.

    2.1. Точное число результатов довольно быстро можно узнать так из SHOW META:
    "SELECT ... WHERE <условия без сортировки> LIMIT 1; SHOW META like 'total_found';" и смотрите результат второго запроса.

    2.2 Дальше делаете "честные запросы" - фильтрацию, сортировки для получения нужной страницы, но с указанием max_matches: "SELECT .. WHERE ... LIMIT ... OPTION max_matches=страница * размер страницы". В типичной ситуации, когда пользователь угомонится на первых страницах, скорость значительно увеличится.

    3. В третьем сфинксе появился CREATE INDEX для обычных, не текстовых, столбцов. Может помочь.

    4. В Постгресе сейчас есть "обёртка сторонних данных" и через неё можно стучаться, например, к mysql. К Сфинксу стучаться не пробовал, но стоит попробовать схему "данные в постгресе, полнотекстовый индекс из сфинкса подключаем через JOIN".
    Ответ написан
    Комментировать
  • В чем основные отличия mySQL от Postgre?

    batyrmastyr
    @batyrmastyr
    Из простых преимуществ постгреса - многие запросы в нём отрабатывают шустрее, можно весьма гибко прописать ограничения на данные (если в поле "а" что-то есть, то в поле "б" может быть только "с"), даже крупному магазину может хватить настроек по-умолчанию при которых база довольствуется смешным объёмом памяти.

    Из недостатков по сравнению с Mysql - нет множеств (заменяется массивом перечислений), большая строгость работы (число или перечисление нельзя взять и сравнить со строкой "5 = '5'", нужно привести их к одному типу "5 = '5'::int" или " 5::text = '5'5 ", а ваша обёртка над базой может быть не готова к такому).

    В контексте nosql баз данных например вижу преимущества в быстродействии, например, причем на порядок.

    Увы, это преимущество скорее всего окажется мифом - сейчас как раз потихоньку выпиливаем MongoDB.
    Если говорить про MongoDB, то в моих задачах он работал либо не быстрее мускуля или постгреса при поиске, либо в разы (в 2 - 50 раз) медленнее при записи. При этом Монга жрала 1,5 гига памяти, мускуль - 300 Мб, а постгрес - меньше 15 Мб (да, меньше жалких пятнадцати мегабайт).
    Ответ написан
    3 комментария