PostgreSQL *

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

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

Я решил не затягивать выход обновления справочника и выпустил 2 версию «Работа с Postgresql: настройка, масштабирование».

Как и раньше, в книге иследуются вопросы по настройке производительности Postgresql, репликации и кластеризации.
Добавил пару слов о расширениях PostgreSQL, методики решения проблем, сниплеты(пока в процессе). Произвел коррекцию ошибок в манах настроек. Пока что только сконвертирован pdf. Все правки и замечания прошу на этой странице github.com/le0pard/postgresql_book/issues или в комментариях.

Страница книги: postgresql.leopard.in.ua
Исходники: github.com/le0pard/postgresql_book

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

В одном из докладов недавно проходившего CNews Forum я увидел «СУБД для предприятий» в списке ПО, которое навсегда останется проприетарным. Я не могу согласиться с таким тезисом, поэтому написал небольшую статью.

Привет всему хабросообществу.

Время не стоит на месте. После публикации моего справочника по Postgresql очень многое успело поменяться, а точнее добавиться в эту отличную СУБД. После выхода PostgreSQL 9 версии я понял, что потребуется добавить информацию о нововведениях для этой версии. Тем более, что 9 версия знаменуется выходом репликации из коробки.

Доброго времени суток. Учитывая, что с момента релиза PostgreSQL 9 прошло уже некоторое количество времени — я решил пощупать одну из его новых функций — нативную репликацию. Как известно, новый механизм основан на пересылке XLOG`a от мастера к слейву. Одним из жирных плюсов можно назвать нормальную обработку ALTER`ов. Иными словами — администратор 9й версии может обойтись без Slony.

Написал небольшой мануал по настройке и масштабированию PostgreSQL. Сподвигло меня на это то, что материала по этой теме мало, он устарел или его просто нет.

Скачать можно здесь:
http://github.com/le0pard/postgresql_book/raw/master/postgresql.pdf
Исходники:
http://github.com/le0pard/postgresql_book/

Страница:
http://postgresql.leopard.in.ua/

Все уже наверняка обратили внимание, что в PostgreSQL 8.4 появился новый режим работы базы данных: Warm standby. При нём во время работы базы данных Write-Ahead логи (WAL) транслируются на подчинённую базу данных, на которой в реальном времени применяются, как если бы это происходило на основной системе. Поэтому, если основная база данных по какой-то причине (молния/торнадо/третья мировая война/другие стихийные бедствия) выйдет из строя, можно будет мгновенно переключиться на подчинённую базу данных (данные в которой будут достаточно актуальны по сравнению с основной базой) и использовать её дальше.
Но, к сожалению, «тёплый стэндбай» подразумевает, что на подчинённой системе непрерывно происходит процесс восстановления базы данных; из чего следует, что пока основная база жива, подчинённой базой пользоваться нельзя.
Если вы читаете хотя бы блог depesz (не говоря уже про коммит-логи), то вы уже знаете, к чему я веду; если же нет, то… 19-го декабря прошлого года в разрабатываемую версию PostgreSQL 8.5 была добавлена функциональность Hot standby. Теперь, при настройке репликации WAL, подчинённая база данных тоже может использоваться для запросов SELECT (и только SELECT, по понятным причинам). Если раньше второй сервер со второй базой данных простаивал в ожидании форс-мажора, и админу приходилось краснеть перед менеджерами при вопросах об эффективности использования оборудования — то теперь этот сервер, при правильном построении логики приложений, поможет разгрузить основную базу данных.
Подробности о функционировании Hot standby можно прочитать в соответствующей статье из документации разрабатываемой версии PostgreSQL.

Как-то по долгу службы появилась необходимость добавить к поиску на сайте всем известную фичу, сервис «Возможно вы имели в виду…» или «Поиск с опечатками». Стали думать как реализовывать. Сторонние сервисы и api использовать не хотелось, ибо время до чужого сервера и назад, да и в целом не очень хорошо. Как раз кстати пришелся модуль pg_trgm, который ищет близкие к запросу слову на основе триграммного индекса.

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

Часть 2

Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации, но разве ж это меня остановит?).

Вслед за Ораклом со своим ‘connet by prior ‘ все остальные СУБД вводят свои реализации иерархических запросов (ИЗ). Хотелось бы рассказать широкой аудитории как это сделано в PostgreSQL.

Postgresql, без сомнения, великолепная СУБД. Она обладает обширнейшими возможностями, отличной документации, и при всем при этом является бесплатной. Однако, всегда найдется что-то, чего пользователю не будет хватать. И в postgresql это легко исправляется, ведь он позволяет создавать функции на языках на любой вкус, будь то Plpgsql, Perl или даже Java.

Приведу пример. Мне всегда не хватало функции, получающей DDL выбранной таблицы. В oracle, например, вы можете воспользоваться для этого средствами пакета dbms_metadata. А вот в postgresql аналога почему-то нет. То есть можно конечно использовать pgdump, но это уже немного не то, мне хотелось бы иметь функцию бд. И так далее, думаю у каждого найдется несколько таких небольших «хотелок».

В любой моей базе я создаю в схеме «public» определенный набор вот таких облегчающих мне жизнь функций. В этом топике я хочу поделиться ими. Приглашаю всех также поделиться в комментариях своими наработками.

В этом топике я хочу рассказать о задаче, очень часто встречающейся в веб-проектах — определение местоположения по ip-адресу. Начну с того, что для того, чтобы определить местоположение пользователя — нужна некая geoip база. Приведу здесь два популярных бесплатных варианта:
IpGeoBase — очень хорошая бесплатная база, но, к сожалению, только по российским ip-адресам.
MaxMind — огромная база по ip-адресам всех стран. Предоставляют бесплатную lite-версию базы. Точность базы по российским ip-адресам не настолько хороша, как у IpGeoBase. Также предоставляют некое API для работы со своей базой, которое позволяет производить выборки очень быстро.

Допустим вы скачали эти базы и залили их в таблицы вашей БД Postgresql (сама закачка — несколько out of scope, если у кого-то возникнет желание — я могу в будущем рассказать о том, что такое команда COPY и с чем ее едят). В общем случае вы получите таблицу такой структуры:

startip	endip	location_id
2130706433	2130706433	1

Здесь:
startip — это начало блока ip-адресов в формате long
endip — конец блока ip-адресов в формате long
location_id — идентификатор локации (город, регион, страна и т.д., maxmind даже координаты содержит).

Сегодня вышел PostgreSQL 8.4

Основные изменения:
* Windowing Functions
* Common Table Expressions and Recursive Queries
* Default and variadic parameters for functions
* Parallel Restore
* Column Permissions
* Per-database locale settings
* Improved hash indexes
* Improved join performance for EXISTS and NOT EXISTS queries
* Easier-to-use Warm Standby
* Automatic sizing of the Free Space Map
* Visibility Map (greatly reduces vacuum overhead for slowly-changing tables)
* Version-aware psql (backslash commands work against older servers)
* Support SSL certificates for user authentication
* Per-function runtime statistics
* Easy editing of functions in psql
* New contrib modules: pg_stat_statements, auto_explain, citext, btree_gin

8.4 Release Notes, PostgreSQL 8.4 Feature List

UPD: русскоязычный обзор на opennet.ru

Предмет описываемой проблемы

При работе с базой данных в PostgreSQL необходимо не забывать, в какой локали (locale) был инициализирован кластер БД — так в постгре называется директория (обычно /var/lib/pgsql/data), в которой хранятся данные всех баз этой установки PostgreSQL.

Очень тяжело начать писать статью. Т.е очень тяжело придумать вступительное слово. Хочется рассказать обо всём и сразу :) Но нет. Будем последовательны.
Начну с того что совсем недавно проходил Highload++ 2008 на котором мне удалось побывать.
Скажу сразу — мероприятие было проведено по высшему клаcсу, докладов было много и все были очень интересными.
Одной из самых запомнившихся презентаций была лекция Аско Ойя об инфраструктуре серверов баз данных в Skype. Лекция в большей степени касалась различных средств с помощью которых достигается такая производительность серверов.
По словам Аско, база данных Skype выдержит даже если все жители Земли захотят подключится к скайп в один момент.

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

UPD: Как правильно заметил хабраюзер descentspb в статье присутствует досаднейшая ошибка. В следствие своей невнимательности я подумал что PgBouncer надо устанавливать между прокси и клиентом. Но, как оказалось, та проблема которую я решал с помощью PgBouncer не решится если установить его именно так. Правильнее надо устанавливать боунсер между нодами и прокси. Мало того, именно так и рекомендуется делать в оффициальном мануале на сайте PL/Proxy.
В любом случае использование PgBouncer так как указано на моей схеме также даст прирост производительности. (Разгрузит Proxy).

30 июня мы провели онлайн-сессию «Вопросы к ведущим мировым экспертам PostgreSQL». В течение трёх часов любой желающий мог задать вопрос одному из приглашённых гостей и получить исчерпывающий ответ. В результате было получено более 40 вопросов, ни один из которых не остался без внимания.

Участники: Брюс Момджан (Bruce Momjian) (PGDG, EnterpriseDB), Максим Богук (Rambler, Мастерхост), Фёдор Сигаев (PGDG), Марко Креэн (Marko Kreen) (Skype).

Вот избранные вопросы и ответы:

Вопрос №3

kaikai:
Одна из досадных штук в работе — это отсутствие конструкции «INSERT OR UPDATE». В MySQL можно сделать так: «INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com') ON DUPLICATE KEY UPDATE email = 'jo@email.com'». Появится ли что-нибудь похожее в PostgreSQL?

Брюс Момджан, лидер сообщества PostgreSQL, эксперт компании EnterpriseDB:
Стандартный (ANSI) способ для таких вещей — это MERGE или UPSERT. Оба способа находятся у нас в TODO-листе и, насколько я знаю, кто-то работает над этой функциональностью для PostgreSQL 8.4, хотя лично я ещё не видел этого патча. Мы, вне всяких сомнений, нуждаемся в таком синтаксисе.

На работе в новом проекте используется СУБД PostgreSQL. Так как до сих пор я работал с MySQL, сейчас приходится изучать и открывать для себя Постгри. Первая проблема, которая меня заинтересовала — замена мускулевского SQL_CALC_FOUND_ROWS. При использовании этой константы в MySQL можно получить количество всех найденных по запросу записей, даже если запрос с limit'ом — это незаменимо при постраничном выводе поисковых результатов, когда используются «тяжелые» запросы.
Сходу готового решения найти не удалось. На форумах просто констатировали, что SQL_CALC_FOUND_ROWS в Постгри нет. Некоторые писали, что надо юзать count(*). И больше никакой информации. Но еще из MySQL мне было известно, что поиск с count()-запросом работает почти в 2 раза медленнее, чем с SQL_CALC_FOUND_ROWS. Я консультировался у тех, кто пользуется PostgreSQL, день мучал google и в результате получил 4 варианта замены SQL_CALC_FOUND_ROWS в PostgreSQL, один из которых вполне приемлимый по скорости.

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

Объём запущенной год назад базы данных достиг 2 петабайт. Система создана для аналитических целей, в ней хранится история поведения веб-пользователей (утверждается, что в месяц сохраняются данные о полумиллиарде пользователей). Помимо прочего, интернет-гигант заявляет, что это не только самая большая БД в мире, но ещё и самая нагруженная — в сутки в ней регистрируются данные о 24 млрд событиях.

Более 200 разработчиков, более 300 патчей, 15 месяцев напряжённой работы разработчиков и тестировщиков… И вот — новейшая версия лучшей СУБД в мире готова к использованию в промышленных условиях!

4 февраля 2008-го года Глобальная группа разработчиков PostgreSQL (PostgreSQL Global Development Group) анонсировала долгожданный релиз версии 8.3 самой развитой открытой СУБД, факт выхода которой ещё более укрепляет позиции PostgreSQL как и самой производительной СУБД из систем с открытым исходным кодом. Среди новшеств, касающихся производительности, стоит выделить: