Чем PostgreSQL лучше других SQL баз данных с открытым исходным кодом. Часть 2

Original author: Lisa Smith
  • Translation
Друзья, представляем вашему вниманию вторую часть перевода «Чем PostgreSQL лучше?». Надеемся, она вызовет такое же горячее обсуждение в комментариях, как и первая часть. А также с радостью продолжим с вами дискуссию лично на PG Day'16 Russia, до которой осталось совсем немного!

В слогане PostgreSQL заявляется, что это «Самая продвинутая база данных с открытым исходным кодом в мире». В первой части этой серии мы рассмотрели хранение данных — модель, структуры, типы и ограничения по размеру, — чтобы дать вам несколько причин, почему Постгрес подтверждает свои слова делом. Во второй части мы поговорим о манипуляциях с данными и поиске, включая индексирование, виртуальных таблицах и возможностях запросов. В этой серии мы выясняем, что выгодно отличает PostgreSQL от других баз данных с открытым исходным кодом, а именно — от MySQL, MariaDB и Firebird.



Индексирование


Постгрес предлагает возможности индексирования, которых нет у других БД с открытым исходным кодом. Помимо стандартных индексов, он поддерживает частичные индексы, функциональные индексы, GiST и GIN индексы. Давайте рассмотрим некоторые из них подробнее.

Частичные индексы

Частичные индексы могут быть созданы, когда вы хотите проиндексировать только отдельное подмножество таблицы. Например, только строки, где значения в столбцах соответствуют определённым условиям. Эта выигрышная функция даёт вам возможность сохранять адекватные размеры индексов, что позволит улучшить производительность и уменьшить занимаемое место на диске. Ключевым аспектом частичных индексов является то, что индексируемый столбец может отличаться от столбцов, по которым определяются условия. К примеру, вы хотите проиндексировать только аккаунты платящих пользователей, а не те, которые были созданы для внутреннего тестирования:

-- создание индекса только для платящих пользователей
CREATE INDEX paying_accounts_idx ON accounts (account_id)  
WHERE account_type <> 'test';

Важно отметить, что иногда в MySQL термин «частичный индекс» используется в отношении усечения индексированных значений до определенного количества байт, а не ограничения индексируемых строк на основании условия. Частичные индексы в описанном нами виде не поддерживаются в MySQL.

Функциональные индексы

Функциональные индексы (или индексы на основе выражений) могут быть созданы с помощью любой функции, чтобы предварительно вычислить столбец для индексирования. Новые значения индексируются и рассматриваются как константы для выполнения запросов, а не вычисляются всякий раз, когда запускается запрос. Например, если у вас есть веб-журнал кликов, который собирает клики по URL в каком бы формате они не поступали, вы можете захотеть создать индекс, приводящий ссылки к нижнему регистру для нормализации данных (PostgreSQL чувствителен к регистру: compose.io и Compose.io будут считаться разными результатами):

-- создание индекса для URL в нижнем регистре
CREATE INDEX webhits_lower_urls_idx ON webhits (lower(url));  

GIST и GIN (а также и BRIN!)

GiST (Generalized Search Tree, Обобщенное Дерево Поиска) позволяет объединять B-дерево, R-дерево и определяемые пользователем типы индексов для создания индивидуального индекса с расширенными возможностями запросов. GiST используется в PostGIS (который мы сделали стандартным для всех инсталляций PostgreSQL с января) и OpenFTS (полнотекстовой поисковый движок с открытым исходным кодом). Постгрес также поддерживает SP-GiST, который позволяет создавать партицированные поисковые индексы для невероятно быстрого поиска.

GIN (Generalized Inverted Index, Обобщенный Инвертированный Индекс) позволяет индексировать составные типы данных, которые дают возможность объединять другие типы данных разными способами для создания чего-то полностью индивидуального. Подробное описание составных типов данных вы можете найти в первой части этой серии.

Синтаксис для создания GIST и GIN индексов будет следующим: CREATE INDEX… ON… USING GIST|GIN.... Очень просто!

В PostgreSQL 9.5 был представлен BRIN (Block Range Index), который позволяет разбивать большие таблицы на диапазоны на основании столбца для индексирования. Это значит, что планировщик запросов может сканировать только диапазон, указанный в запросе. Также, при индексировании диапазонов необходимое для индексирования место на диске будет существенно меньше, чем при стандартном B-Tree индексе.

Для сравнения

Другие рассматриваемые нами SQL базы данных сокращают разрыв, когда речь заходит о функциональных индексах. В MySQL 5.7.6 были представлены генерируемые столбцы, которые можно использовать как функциональные индексы. В MariaDB виртуальные (также известные как «генерируемые» или «вычисляемые») столбцы появились в версии 5.2, но поддерживают только использование встроенных функций для создания столбцов (определяемые пользователем функции отсутствуют). В версии 2.0 Firebird было представлено индексирование выражений с помощью вычисляемых столбцов. Тем не менее, ни одна из этих баз данных не поддерживает частичные, GiST или GIN индексы. Кроме того, мы упоминали в первой части, что нативные типы данных JSON не могут быть проиндексированы в этих базах данных.

Когда вы настроите все индексы и захотите проанализировать их производительность, не забудьте прочитать статью Мэтта Барра из mySidewalk «Простая проверка индексов в PostgreSQL».

Функции виртуальных таблиц


Виртуальные таблицы необходимы для многих запросов. Все сравниваемые нами SQL базы данных предлагают какую-нибудь функциональность виртуальных таблиц. PostgreSQL может дать вам больше.

CTEs и рекурсия

Постгрес поддерживает Common Table Expressions (CTE) с использованием выражения WITH. Мы демонстрировали эту функцию в статье PostgreSQL — Series, Random and With. CTE позволяют создавать виртуальные таблицы прямо в вашем запросе, выражая логическую последовательность операций. Таким образом, их гораздо проще читать и тестировать, нежели виртуальные таблицы, созданные с помощью вложенных запросов где-то в другой части запроса. CTE в PostgreSQL также могут быть использованы рекурсивно. Эта удобная возможность позволяет пройти насквозь через иерархию с многократно ссылающимся на самого себя запросом, пока не останется больше уровней данных, которые можно вернуть. Вот пример рекурсивного CTE, которое идентифицирует уровни, темы и родительские отношения в систематике темы:

-- запрос с рекурсией
CTE WITH RECURSIVE topic_taxonomy_recursive  
(level, parent_topic_name, topic_name)
AS (  
     SELECT 1, tt.parent_topic_name, tt.topic_name
     FROM topic_taxonomy tt
     WHERE tt.parent_topic_name = 'All Topics'
     UNION ALL
     SELECT ttr.level + 1, tt.parent_topic_name, tt.topic_name
     FROM topic_taxonomy_recursive ttr, topic_taxonomy tt
     WHERE ttr.topic_name = tt.parent_topic_name
) SELECT level, parent_topic_name, topic_name
  FROM topic_taxonomy_recursive;

MySQL и MariaDB не используют условие WITH и, следовательно, формально не поддерживают CTE. Хотя вы и можете с помощью вложенных запросов создать в этих базах данных производные таблицы, они не позволяют делать рекурсию. Кроме того, несмотря на то, что оптимизатор запросов в MySQL был усовершенствован со времен релиза версии 5.6, вложенные запросы в этой базе данных известны своей проблемностью и могут существенно повлиять на производительность. Firebird в этом вопросе опережает MySQL и MariaDB и совпадает по функциональности с Постгресом: поддерживает CTE с помощью WITH и предоставляет возможность рекурсии.

Материализованные представления

Материализованные представления (Materialized views) — это ещё одна удобная функция виртуальных таблиц, поддерживаемая PostgreSQL. Они, как и обычные views, представляют результат запроса, который вы будете часто использовать, но разница в том, что результат хранится на диске, как обычная таблица. Материализованные представления могут быть проиндексированы. Кроме того, в отличие от обычных представлений, которые пересоздаются каждый раз, когда их вызывают, представления с хранимым результатом фиксируются во времени. Они не обновляются, если не делать это намеренно. Это может существенно увеличить скорость, с которой осуществляются запросы, использующие materialized views. Вместо использования обычных представлений или необходимости совершать сложные объединения таблиц или выполнять группирующие функции в запросе, используйте materialized views, где все необходимые данные уже подготовлены и ждут на диске. Когда вам понадобится обновить данные в материализованном представлении с хранимым результатом, это можно будет сделать по требованию с помощью команды REFRESH. Приведём пример materialized view, которое выдаёт сводные данные о доходах:

-- создание материализованного представления, содержащего сводные данные о доходах
CREATE MATERIALIZED VIEW aggregatedMonthlyRevenue  
(year, month, total_revenue)
AS (  
     SELECT date_part('year', date) AS year,
          date_part('month', date) AS month,
          SUM(revenue) AS total_revenue
     FROM revenue
     WHERE date >= '2014-01-01'
     GROUP BY date_part('year', date),
          date_part('month', date)
     ORDER BY date_part('year', date),
          date_part('month', date)
);

-- обновление представления, при необходимости
REFRESH MATERIALIZED VIEW aggregatedMonthlyRevenue;  

Firebird, MySQL и MariaDB не поддерживают materialized views, хотя можно применить в этих базах данных своего рода обходной путь, создав обычную таблицу и используя хранимую процедуру или триггер, чтобы обновлять её по мере необходимости.

Возможности запросов


Возможности запросов в Постгресе обширны.

Мы уже немного говорили о WITH в предыдущем разделе. Давайте рассмотрим ещё пару дополнительных функций, которые могут быть использованы в запросах SELECT.

Объединение запросов

PostgreSQL предоставляет условия UNION, INTERSECT и EXCEPT для взаимодействия между запросами SELECT. UNION добавит результаты второго запроса SELECT к результатам первого. INTERSECT возвращает только те строки, которые соответствуют обоим запросам SELECT. EXCEPT возвращает только те строки из первого запроса SELECT, которые не совпадают со строками из второго запроса SELECT. Давайте рассмотрим пример с использованием EXCEPT, где мы хотим вернуть контактную информацию пользователей за исключением тех случаев, когда пользователь получил email в течение прошлой недели и ответил на него.

/*
запрос для выборки инфо
о пользователях, которые не получали
писем за последнюю неделю
*/
SELECT c.lastName, c.firstName, c.email  
FROM customers c  
EXCEPT  
SELECT e.lastName, e.firstName, e.email  
FROM email_log e  
WHERE e.email_date > current_date - interval '7 days'  
     AND e.email_action_date > current_date - interval '7 days'
     AND email_action_type = 'response';

Хотя MySQL, MariaDB, и Firebird поддерживают UNION, ни одна из них не поддерживает ни INTERSECT, ни EXCEPT. Тем не менее, используя в запросе объединения и условие EXISTS, можно получить тот же результат, что и в PostgreSQL. Но запрос при этом выйдет более сложным.

Оконные функции

Оконные функции, которые представляют собой агрегатные функции поверх некоторых строк результата (предоставляя «окно» в подмножество), могут быть чрезвычайно полезными. По сути, они позволяют произвести итерацию по строкам в секции, которые относятся к текущей строке, чтобы выполнить функцию. Стандартные функции включают ROW_NUMBER(), RANK(), DENSE_RANK() и PERCENT_RANK(). Ключевое слово OVER, опционально используемое с PARTITION BY and ORDER BY, показывает, что используется оконная функция. В качестве примера в параграфе «Функции и не только» ниже мы использовали оконную функцию с ROW_NUMBER() OVER..., чтобы определить медиану в серии числовых значений. Заметьте, что условие WINDOW в запросах с оконными функциями не является обязательным, но позволяет создавать и именовать окна для сохранения порядка.

Firebird, MySQL и MariaDB на данный момент не поддерживают оконные функции, хотя они и анонсировались несколько лет назад при планировании Firebird 3.

Латеральные вложенные запросы

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

-- запрос с использованием LATERAL во вложенном запросе
SELECT s.firstName, s.LastName, x.topic_name  
FROM students s  
JOIN content_log c ON c.student_id = s.id  
LEFT OUTER JOIN LATERAL (  
     SELECT t.topic_name
     FROM content_topics t
     WHERE t.parent_topic_name = 'Technology'
          AND t.id = c.topic_id
          AND c.date > current_date - interval '30 days'
) x ON true;

MySQL, Firebird и MariaDB на сегодняшний день не поддерживают латеральные вложенные запросы. Опять же, можно найти обходные пути, но это сделает запросы более сложными.

И ещё кое-что на заметку: MySQL и MariaDB не поддерживают FULL OUTER JOIN, но можно использовать обходной путь с использованием UNION ALL, чтобы объединить все строки двух таблиц.

Функции и не только


PostgreSQL предоставляет надежные встроенные операторы и функции, в том числе такие, которые поддерживают специализированные типы данных, подробно рассмотренные в первой части этой серии. Кроме того, он позволяет вам создавать собственные операторы и функции (включая агрегаты), а также хранимые процедуры и триггеры. Мы не сможем детально рассмотреть их все, поскольку тема слишком обширная (!), но давайте разберем пару простых примеров функций.

Постгрес поддерживает 4 вида определяемых пользователем функций: язык запроса, процедурный язык, язык C и внутренний. Каждый вид может брать и возвращать как базовые, так и составные типы данных. Заметьте, что в PostgreSQL команда CREATE FUNCTION используется не только для создания функций, но и хранимых процедур.

Давайте рассмотрим пример создания функции, которая возвращает составной тип данных:

-- создаем новый составной тип под названием "datetext"
CREATE TYPE datetext AS (  
     date date,
     date_as_text text
);

/*
создаем функцию, которая принимает значение даты
и возвращает дату и соответствующий ей datetext
*/
CREATE FUNCTION show_date_as_text(date)  
RETURNS datetext -- this is our composite type  
AS  
$$ SELECT $1, to_char($1, 'FMMonth FMDD, YYYY') $$
LANGUAGE SQL;

-- запрос с применением функции
SELECT show_date_as_text('2015-01-01');

-- Возвращает: (2015-01-01,"January 1, 2015")

А вот пример реальной функции для нахождения медианы в серии числовых данных:

-- создаем функцию, которая находит медиану в серии числовых данных
CREATE FUNCTION median(numeric[])  
RETURNS numeric  
AS  
$$ SELECT AVG(x.result)
   FROM (
        SELECT result, 
             ROW_NUMBER() OVER (ORDER BY val) as ra,
             ROW_NUMBER() OVER (ORDER BY val DESC) as rd
        FROM unnest($1) result -- notice the use of array "unnest"
        ) AS x
   WHERE x.ra BETWEEN x.rd - 1 AND x.rd + 1;
$$
LANGUAGE SQL;

-- запрос с применением функции
SELECT median(ARRAY[1,2,3,4,5,6,7]);

-- Возвращает: 4

Хотя другие SQL базы данных с открытым исходным кодом, учавствующие в этом сравнении, также позволяют вам создавать свои собственные функции, хранимые процедуры и триггеры, у них нет такого многообразия типов данных и возможностей для индивидуализации, как у Постгреса. К тому же, PG позволяет создавать собственные операторы. Другие сравниваемые базы данных не поддерживают определяемые пользователем операторы.

Возможности индивидуализации Постгреса и не имеют равных среди MySQL, MariaDB и Firebird.

Языковые расширения


Для PostgreSQL имеется множество языковых расширений. Некоторые из них являются частью дистрибутива, а множество других доступны через сторонних разработчиков.

В Compose мы поддерживаем только доверяемые (trusted) расширения языка для PostgreSQL, чтобы обеспечить безопасность ваших инсталляций. Мы добавили поддержку PL/Perl в феврале и PL/v8 (процедурный язык на основе JavaScript) — в августе. Эти языковые расширения, которые имеют больше встроенных функций, чем встроенный язык PL/pgSQL на основе SQL (также доступный в инсталляциях Compose), позволяют создавать изысканные скрипты для манипуляций и обработки данных на сервере.

Подводя итог


PostgreSQL чрезвычайно богат функциональными возможностями, с множеством встроенных “фич” и бесчисленным количеством способов их индивидуализации и расширения для удовлетворения ваших потребностей. Добавьте к этому общепризнанную надежность и зрелость, и станет ясно, почему это решение для баз данных стоит усилий любого крупного предприятия. При этом он остаётся доступным и эффективным также и для небольших проектов.

Несмотря на то, что мы рассказали вам лишь о небольшом множестве возможностей, которые выделяют Постгрес на фоне других SQL решений с открытым исходным кодом, на деле их гораздо больше (и ещё больше появилось в версии 9.5!). Мы надеемся, что эта серия из двух статей обеспечила убедительный обзор причин, по которым вам стоит выбрать PostgreSQL.
Share post

Similar posts

AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 32

    +2
    Когда вам понадобится обновить данные в материализованном представлении с хранимым результатом, это можно будет сделать по требованию с помощью команды REFRESH.


    К сожалению, отсутствие поддержки инкрементных обновлений сильно сужает область применения материализованных представлений в Postgres. На эту тему встретил разве что упоминание от Джоша Беркуса в одном из комментариев:
    I believe that Kevin is still working on “incremental update” of Matviews. That’s not in the cards for 9.5, though, sadly.
      +2
      Эх, ни слова о шардировании/партиционировании/федерализации :(
        0
        Вот презентация с последнего хайлоада на тему шардинга в постгресе. Вроде чета пилят, но пока это нескоро всё будет.
        Мне бы на самом деле подошло какое-нибудь изкоробочное решение хотя бы для HA-кластера, а то все эти танцы с pgpool-II, пока что не внушают доверия
          0

          А о (полу)мертвых — или хорошо, или ничего. К сожалению, постгрес в этом плане относится к "типичному опнсорсу" — всё работает отлично, пока ты не пытаешься собрать кластер. Вот пара приколов из нашего опыта (мастер, один слейв, синхронная репликация):


          • репликация синхронная, но чтение со слейва не всегда возвращает данные, которые только что были записаны на мастер
          • хост со слейвом упал, мастер при попытке вставки строки возвращает ошибку, но строку вставляет и транзакцию коммитит.

          Хотя может мы его готовим неправильно — настройка и администрирование кластера постгреса — это отдельная песня.

            0
            1) А она и не должна, репликация «отпускает» коммит когда логи доехали, а не когда они были применены, в 9,6 для этого появится remote apply michael.otacoo.com/postgresql-2/postgres-9-6-feature-highlight-remote-apply
            2) Есть такое, но для синхронной репликации крайне не рекомендуется иметь всего 1 машину, т.к. в случае её выпадения мастер «встанет колом»

            При условии что у вас всего 2-е машины (мастер-слейв) и пункта 1 вопрос: вам точно нужна синхроная репликация?
              0

              Нам нужно избежать потери данных, если НЛО внезапно похитит мастер-ноду. Мы рассчитывали, что синхронная репликация мастер-слейв с автоматическим переключением на слейв в read-only mode нам обеспечит целостность, отсутствие даунтайма и возможность выполнять часть запросов на слейве. А вы тут рассказываете, что это невозможно в принципе...

                0
                Замечу, что я не рассказываю что это невозможно, я обращаю ваше внимание на то, что работает репликация именно так. Если вы можете часть своих запросов выполнять на данных которые могут отставать — выполняйте, это частый сценарий.
                На сколько сильно у вас отстает мастер при асинхронной репликации и зачем вы «связались» с синхронной при 2-х машинах?
                  0

                  Практически не отстает, но мы не хотим терять данные, вообще не хотим терять данные. Что хотелось бы иметь:


                  • после окончания коммита транзакции на мастере, она сразу видна на слейве. Т.е. можно балансировать селекты между обоими нодами
                  • при падении мастера слейв берет на себя нагрузку в read-only mode с нулевым даунтаймом и актуальными данными
                  • при падении слейва мастер продолжает работать
                  • при поднятии мастера обратно с актуальными данными репликация восстанавливается и нагрузка переключается на мастер.
                  • почему два хоста? потому, что нужен-то мастер-слейв и потому, что три хоста дороже, чем два.

                  Если это возможно сделать, пожалуйста посоветуйте как или ткните куда читать.

                    0
                    Все хотят чтоб «без разрыва», но, увы, пока в природе чуда которое сответствует вашим требованиям не существует.

                    Можно попробовать разобраться в проблеме.

                    1. «Балансировать селекты между нодами»

                    Постгрес, как и любая современная РСУБД, способен спокойно держать пару десятков тысяч OLTP-транзакций в секунду с достаточно среднего железа. Вам «селекты» нужно балансировать чтоб железо не простаивало или действительно есть потребность?

                    2. «почему два хоста? потому, что нужен-то мастер-слейв и потому, что три хоста дороже, чем два.»

                    «Мастер-слейв» не спасают вас от потери данных, truncate cascade в данном случае может превратить не 1, а 2-е машины в «тыкву» (синхронно или асинхронно он это сделает выбирать вам) посмотрите в сорону создания бекапов (PITR). Поэтому и ваше желание «вообще не терять данные» и «не дорого» — это взаимоисключающие понятия

                    По фейловеру можете посмотреть исходники Patroni, Stolon чтоб понять что там и как или попробовать использовать одно из решений

                      0

                      инкрементальные бэкапы — это всё замечательнои у нас они есть, но обязательное требование — автоматическая доступность базы при отказе одного хоста. И крайне желательно, чтобы при отказе мастера не пришлось потом руками медленно и печально восстанавливать недореплицированные данные. Неужели писать координатор самому — единственное решение?

                        0
                        Координатор при 2-х машинах достаточно ненужное решение.
                        При живом архиве на слейве можно/нужно указать restore_command на слейве,
                        в случае падения просто rsync'нуть недостающие wal'ы на слейв с мастера и сделать ему promote, если отставание не большое, как вы пишите, то это достаточно шустро, при синхронной rsync'аться не надо, но если данные важны то перепровериться стоит и, опять же, promote.

                        В любом случае вам нужно будет время на определение того что мастер «ушел» чтоб не переключаться на «каждый чих», а это даунтайм если ваше приложение не способно уходить в «RO» на слейв

                        PS: посмотрите еще repmgr, в последние дни его как ругают так и хвалят )
          –2
          Пусть меня закидают гнилыми помидорами, но не могу не сказать, что вижу много статей типа «Чем PostgreSQL лучше MySQL», но не видел, чтобы было наоброт… Видимо нет необходимости рекламировать то, что просто работает…
            +7
            Может быть просто крыть нечем? Я работал и с той базой и с той, и могу сказать что практически нечем.
              +1
              Читайте комментарии выше про кластеризацию. У меня из коробки стоит перконовский кластер и я даже в какой-то момент забыл, что он у меня есть, он просто работает, в то время как решение на pgpool (самое адекватное решение под постргрю) у нас так нормально и не заработало, всё что-то надо допиливать… За время работы с постгрёй я не увидел ни одного реального преимущество перед муськой, т.е. того, что в бою, при нормальной архитекруте, будет давать преимущества в скорости работы, разработке и т.п.
                +3
                У нас тоже перконовский кластер мастер-мастер в работе, и я да я скажу что это круто — когда тебе не приходится пересоздавать мастер заново когда произошел свитч на слейв. Это плюс бесспорный из немногих.
                На счет pgpool2 он у нас тоже не заработал, но зато заработал https://github.com/2ndQuadrant/repmgr, и прекрасно себя чувствует, там хоть что то автоматизировано в сравнении с голым постгрес. И все танцы с бубном вокруг фэйловинга и репликации как по мне покрываются Point In Time Recovery. Когда после сбоя потеря данных после восстановления минимальна, вплоть до нескольких секунд. Главное данные, а админы переживут. Один раз настроил и все работает.
                Да конечно постгрес не для личного бложика на вордпрес, а для действительно серьезных проектов и относительно больших нагрузок. А там уже нет таких вопросов как — «Тут даже нет ничего из коробки?!!!»
                  –1
                  Многи крупные проекты используют MySQL, тот же badoo, mamba, это очень большие проекты. Решает общая архитектура проекта, а не конкретный инструмент… Кстати, по скорости работы, залил геобазу maxmind в постгрю, поиск по IP-адресу около 40 мс против 2 мс на одной машинке, обе базы из коробки. Конечно, проводить такое сравнение не совсем корректно, но вот таких мелочей почему-то возникает много, когда с постгрёй реально приходиться бороться, а не просто использовать хороший, быстрый и функциональный инстурмент, о котором пишут в подобных статьях…
                    0
                    Я так понял 40мс — поиск в постгре, 2мс — поиск в мускуле? Это интересно, какое кол-во строк, индексы создавали? Если можете покажите план выполнения, пожалуйста (для постгрес добавьте опцию ANALYZE).
                      0
                      Строк было под 4М. Таблица была простая — ip_from, ip_to, location_id. В начале использовалось поле inet, потом cidr, потом переделал под целое число, но скорость выросла незначительно. В MySQL использовались целые числа. Индекс использовался составной по полям ip_from и ip_to. План не могу дать, база с данными не сохранилась.
                        +3
                        Честно говоря, не знаю откуда у вас такие цифры по поводу времени выполнения запроса вообще. Создал базу с тремя полями, два BIGINT, одно VARCHAR. Заполнил числами от 111111111111 до 111121111111, и того 10М. Создал составной индекс. Итого:
                        1. При запросе с выборкой небольшого диапазона, где планер использует индекс: постгрес — avg 0.2 ms, мускуль — avg 0.3 ms. на 10 запросах.
                        2. При запросе очень большого диапазона, где планер решает использовать последовательное сканирование вместо индекса: постгрес — примерно 2 секунды, мускуль — 19 секунд.

                        Тесты конечно же чисто для примера. И в запросах где используется индекс разницы практически нет, но при Seq Scan, разница огромная и я честно говоря не знаю с чем это связано.
                        Все тесты проводились с настройками из коробки и ничего не подкручено.
                          0
                          Возможно я где-то допустил ошибку, сейчас нет возможности повторить эксперимент. Помню, что достаточно долго копался.

                          При Seq Scan, очевидно, что последовательное сканирование на больших диапазонах будет быстрее, так как не требует обращения к индексу и последовательное чтение данных из файла быстрее рандомного. Даже если индекс в памяти и планировщик пытается оптимизировать доступ к данным, чтобы он был максимально последовательным, всё равно последовательное чтение может давать существенный выигрыш. Зависит от распределения данных и их типа…
                            +1
                            Вы абсолютно правы. Но я имел ввиду почему последовательное сканирование в постгрессе занимает 2 секунды, а на мускуле 19 при равных условиях. Я не говорю мускуль такой плохой, возможно где-то что-то упустил.
                              0
                              Думаю, для этого надо зарываться в исходники и смотреть реализацию… Скорее всего постгря лучше оптимизирует доступ к данным чем муська…
                      0
                      В подобных ситуациях проблема постгреса не в отсутствии кластера, а в настройках по умолчанию )
                        0
                        Эти комментарии не связаны, здесь я комментировал скорость работы безотносительно кластеризации…
                      0
                      У нас тоже перконовский кластер мастер-мастер в работе, и я да я скажу что это круто — когда тебе не приходится пересоздавать мастер заново когда произошел свитч на слейв. Это плюс бесспорный из немногих.

                      С момента появления pg_rewind в постгресе тоже не обязательно пересоздавать мастер заново когда произошел свитч на слейв.
                        +1
                        Пересоздавать нет, но нормальной мастер-мастер репликации в постгрес нет. Я не пробовал но то что есть, удручает количеством ограничений — https://wiki.postgresql.org/wiki/BDR_Command_Restrictions.
                      +1
                      Если честно, то самое реальное преимущество PostgreSQL которое даст «скорость работы, разработке и т.п» — это отдавать большую часть работы на сторону СУБД за счет большей поддержки SQL и, конечно, PL/pgSQL, но это, по разным причинам, не для всех. Плюсом ко всему в современном многоядерном/многопроцессорном мире Postgres со своей «процессной моделью» гораздо лучше масштабируется по ядрам/физическим процессорам. Я вас не призываю к использованию Postgres, кто-то испытывает боль при работе с ним, кто-то с другим решением, но сравнивать Postgres и MySQL достаточно тупиковое решение — они очень разные
                    +2
                    Вот например наоборот
                    habrahabr.ru/post/268949
                    Спойлер: всё равно получилось «Чем PostgreSQL лучше MySQL»
                    +1
                    Да PostgreSQL против MySQL — это как MAC против Windows или Desktop Linux. Но вот когда нужна большая горизонтальная мастабирумость то адекватного решения для создания кластера просто не существует. Единственное, что более менее похоже на зачатки нормальной кластеризации это PostgreSQL BDR. Но в ней нет самого необходимого, тех же последовательностей в масштабах кластера. Хотя MySQL Galera Cluster работает по принципу поставил и забыл (почти). Надеемся разработчики наконец-то допилят кластеризации, а то как большая столовая ложка дегтя в стакане меда.
                      0
                      вопрос — если это перевод, то где ссылка на оригинал? (в т.ч. на оригинал первой части)
                        0
                        В «подвале» статьи (там где рейтинг и голосование). Аналогичная ссылка есть в статье-переводе первой части.
                          0
                          понял, спасибо. у хабра как-то это сделано, что не поймешь, то-ли это имя автора перевода, то-ли еще что…

                      Only users with full accounts can post comments. Log in, please.