PostgreSQL *

Лет так 6 назад, когда слоник был только в 8.0, а я плотно сидел на MySql, часто слышал призывы сменить DB. Я помню как это было болезненно начать. Но после того, как решился, ни разу не жалел и на мускул уже вряд ли вернусь. Уж очень много тут плюсов, но пост не об этом.

Пришла задача: написать магазин, большой в перспективе. А-ля Фотос, Хотлайн. Ну и стандартная задача для таких площадок — это фильтр.

3 ноября в московском офисе Mail.Ru Group состоится встреча #PostgreSQLRussia. В этот раз главной темой будет переход на PostgreSQL с других СУБД. У нас в стране в последние годы всё больше компаний переходят с проприетарных на открытые системы, в частности на PostgreSQL. Процесс этот имеет свои нюансы, и каждому крупному проекту есть что рассказать о своём опыте миграции. Эта информация была бы полезна не только тем, кто ещё планирует перейти на PostgreSQL, но и достаточно опытным пользователям.

Хочу на простом примере рассказать о том, как иногда можно сильно оптимизировать вполне простые на первый взгляд запросы. Возьмем такой код, для примера на PostgreSQL 9.3, но принцип подходит ко всем субд, в которых присутствует hash join.

Задача простая — сджойнить две таблицы — одна весьма большая, другая маленькая — но джоин не простой, а золотой с OR. (Как реальный кейс — джоин таблицы проводок по счетам к самим счетам, учитывая, что в проводке два поля со счетом — для дебета и кредита.)

Как ни странно, чтобы понять рекурсию, в PostgreSQL не надо понимать рекурсию. Потому что WITH RECURSIVE, который присутствует в посгресе (и в других серьёзных базах) — это скорее вычисление чего-то итерациями до того, как будет выполнено некоторое условие.
Тем не менее это очень полезный функционал базы, который можно использовать, например, чтобы вывести все подкатегории заданной категории, если таблица задана в виде (id, parent_id, ...)

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

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

Привет, Хабр! Эта публикация — попытка развеять некоторые популярные мифы и легенды о MySQL. Я не ошибся с хабом, так как поводом для написания послужила публикация varanio Возможности PostgreSQL, которых нет в MySQL, и наоборот отсюда же. Сама публикация в части критики MySQL хоть и неидеальна, но вполне корректна, а вот комментарии к ней наводят на грустные размышления.

Вообще говоря, я собирался написать публикацию о возможностях MySQL, которые не реализованы или реализованы в PostgreSQL хуже. Но для того, чтобы не мешать много тем в одну публикацию, и учитывая довольно нелёгкую работу по сравнению того, что я знаю очень хорошо (MySQL) с тем, что я знаю очень плохо (PostgreSQL), такую публикацию я решил отложить на потом и для начала ответить сразу на многие комментарии из публикации varanio.

Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.

Хочу поделиться новым продуктом со всеми пользователями замечательной базы данных Postgresql. Встречайте — SQL Tabs — графическая SQL консоль для Postgresql. Это новый GUI клиент, в котором можно просматривать объекты базы данных, писать SQL запросы и строить графики.

На сегодняшний день можно скачать версии для OSX и Ubuntu. Пользователи других linux-дистрибутивов могут запустить приложение из исходников. К сожалению, версии для Windows пока нет. Хочется надеяться, что приложение понравится сообществу, и найдутся разработчики, способные помочь в выпуске пакетов под разные платформы: rpm, debian, windows.

Немного информации для разработчиков: SQL Tabs написано на стэке javascript, electron, react
 и использует native libpq драйвер для работы с базой. Выпускается под лицензий GPL3.


Теперь об основных особенностях SQL Tabs, отличиях от других похожих продуктов и основной мотивации, которая стоит за разработкой.



В свете недавнего поста про сравнение PostgreSQL и MySQL, в комментариях возникла проблема выбора удобного интерфейса для работы с постгресом. Я сам столкнулся с такой проблемой, решив поискать альтернативы всем известному phpMyAdmin / php*Admin, который считается стандартом у веб-мастеров.

Продолжаем цикл статей о технологиях, использующихся в работе хранилища данных (Data Warehouse, DWH) нашего банка. В этой статье я постараюсь кратко и немного поверхностно рассказать о Greenplum — СУБД, основанной на postgreSQL, и являющейся ядром нашего DWH. В статье не будут приводиться логи установки, конфиги и прочее — и без этого заметка получилась достаточно объёмной. Вместо этого я расскажу про общую архитектуру СУБД, способы хранения и заливки данных, бекапы, а также перечислю несколько проблем, с которыми мы столкнулись в ходе эксплуатации.



Немного о наших инсталляциях:

• проект живёт у нас чуть больше двух лет;
• 4 контура от 10 до 26 машин;
• размер БД около 30 Тб;
• в БД около 10000 таблиц;
• до 700 queries per second.

За тем, как оно работает, прошу под кат!

Многие боятся переходить с «мускуля» на «посгрес» из-за того, что лишь смутно понимают, что это даст. Некоторых останавливает мысль, что наверно Postgres — это слишком сложная база и требует обучения. А также, что возможно чего-то придется лишиться в связи с переходом. Попробую немного прояснить ситуацию.

Наблюдать за развитием разных баз данных — увлекательное занятие, особенно — если понимаешь подводные течения. Одно из самых сильных сообществ вокруг СУБД в России — это PostgreSQL-сообщество. Две тематические конференции в год, консалтинговая компания и даже компания-разработчик модулей к PostgreSQL.

Руководитель и идеолог международного сообщества, Брюс Момжан, вот уже какой год приезжает к нам на HighLoad++. Этот год не исключение, Брюс будет рассказывать про «Upcoming PostgreSQL Features» — кому рассказывать про будущее этой СУБД, как не Брюсу?

Почему же, несмотря на такую активность, это база данных по-прежнему далеко не так распространена, как, например «базулька» MySQL. В чем подвох? Эту тему мы активно обсуждали на конференции PGDay'15, которую организовал один из докладчиков HighLoad++ Илья Космодемьянский.

Для начала небольшое исследование:

1. Крупнейшие платные CMS в России (Битрикс, Netcat, UMI) не поддерживают PostgreSQL;
2. Самые популярные бесплатные CMS (Wordpress, Drupal, Joomla) тоже (или поддерживают с трудом или поддерживают недавно);
3. Только каждый третий хостинг провайдер предлагает поддержку PostgreSQL.

Предположим, у вас есть prod и test базы данных. В какой-то момент разработчик внес изменения в тестовую базу, но забыл внести эти изменения в боевую базу. Если это часто используемая таблица, то ситуация очень быстро становится очевидной, так как в логах появятся ошибки в SQL-запросах и вам начинает звонить начальник с упреками «какого @#$%».

Но иногда изменения затрагивают редко используемые таблицы, либо изменения на первый взгляд не совсем очевидны (например, кто-то изменил длину поля VARCHAR и у вас стали обрезаться строки, или кто-то добавил индекс, из-за которого запросы на тестовой базе выполняются на порядок быстрее).

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

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

Продолжаем обзор нововведений в PostgreSQL 9.5.
Часть 1. INSERT… ON CONFLICT DO NOTHING/UPDATE и ROW LEVEL SECURITY.
Часть 3. GROUPING SETS, CUBE, ROLLUP

Дисклеймер

Недавно по работе возникла задача генерации тайлов на основе OSM данных. Поискал, прочитал несколько статей, но везде фигурировали *nix-системы, а у меня был в наличии сервер под управлением Windows. В конце-концов, более-менее внятный туториал нашёлся, но он слегка устарел и не везде подробен, посему пришлось повозиться. После удачного завершения подумал, что мой опыт будет полезен.

Кого заинтересовал, прошу под кат.

Часть 2. TABLESAMPLE
Часть 3. GROUPING SETS, CUBE, ROLLUP
В 4 квартале 2015 года ожидается релиз PostgreSQL 9.5. Как всегда, новая версия кроме новых багов приносит новые фичи и «плюшки». В данной статье будут рассмотрены две из них, а именно INSERT… ON CONFLICT DO NOTHING/UPDATE и Row-level security. Уже вышла вторая альфа-версия, поэтому самые нетерпеливые могут её установить и попробовать новый функционал.
Скачать можно тут

Можно прочитать много книг по базам данных, написать кучу приложений на аутсорс или для себя. Но при этом невозможно не наступить на грабли, при работе с действительно большими базами/таблицами особенно, когда downtime на большом проекте хочется свести к минимуму, а еще лучше совсем избежать. Вот здесь самые простые операции, как например изменение структуры таблицы может стать более сложной задачей. Наиболее интересные случаи, проблемы, грабли и их решения из личного опыта с которыми нам на проекте Pushwoosh пришлось столкнуться описаны под катом. В статье нет красивых картинок, зато есть много сухого текста.

Решил поделиться с вами опытом настройки кластера PostgreSQL 9.3, состоящего из двух нод, управлением которого занимается pgpool-II, без использования Stream Replication (WAL). Надеюсь, кому-нибудь будет интересно.

Схема:



Как Вы понимаете, это будут два отдельно стоящих сервера, которыми будет управлять pgpool-II.

Конфигурация нод с PostgreSQL:
Оба сервера идентичны по своим аппаратным составляющим.

• 4vCPU;
• 16 Гб памяти;
• CentOS 6.5;

Диски:

• 50 Гб — система;
• 100 Гб — pg_xlog
• 500 Гб — каталог с данными

Пример,

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        48G  7.4G   38G  17% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/sda1       194M   28M  157M  15% /boot
/dev/sdb1        99G  4.9G   89G   6% /var/lib/pgsql/9.3/data/pg_xlog
/dev/sdc1       493G  234G  234G  50% /var/lib/pgsql/9.3/my_data

Конфигурация нод с pgpool-II:

• 4vCPU;
• 8 Гб памяти;
• CentOS 6.5;

Диски:

• 50 Гб — система;

Про установку PostgreSQL в детали вдаваться не буду, так как она стандартная.

Настройка pgpool-II.
За основу настройки pgpool-II, взял инструкцию с официального сайта: www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html

Хотел бы обратить внимание только самые важные моменты:

«Ландшафт» СУБД в проектах нашей компании до недавнего времени выглядел так: большую часть составляла Oracle, существенно меньшие — MS SQL и MySQL.

Но, как известно, нет ничего вечного, и недавно к нам поступил запрос о применимости Postgres в одном из наших проектов. К этой СУБД мы присматривались в последние пару лет очень пристально — посещали конференции, meetup’ы, но вот попробовать ее в «боевых» условиях до недавнего времени не доводилось.

^{Капитан Немо у штурвала «Наутилуса»}

Индексы — один из самых мощных инструментов в реляционных базах данных. Мы используем их, когда нужно быстро найти какие-то значения, когда объединяем базы данных, когда нужно ускорить работу SQL-операторов и т.д. Но что представляют собой индексы? И как они помогают ускорять поиск по БД? Для ответа на эти вопросы я изучил исходный код PostgreSQL, отследив, как происходит поиск индекса для простого строкового значения. Я ожидал найти сложные алгоритмы и эффективные структуры данных. И нашёл.

Здесь я расскажу о том, как устроены индексы и как они работают. Однако я не ожидал, что в их основе лежит информатика. В понимании подноготной индексов также помогли комментарии в коде, объясняющие не только как работает Postgres, но и почему он так работает.