Мой коллега Егор Рогов из ППГ недавно издал книгу “PostgreSQL изнутри”. Книгу можно бесплатно скачать в формате pdf на сайте Postgres Professional. Для любителей читать книги, сделанные из переработанных трупов зверски убитых деревьев, ее без труда можно найти на сайте “ДМК Пресс”.

Иногда при эксплуатации проекта возникает вопрос, какие запросы в БД выполняются дольше всего или потребляют наибольшее количество времени или ресурсов.

До версии 9.2 неплохой ответ на этот вопрос можно было получить с помощью проекта pgBadger. Если прорваться через достаточно простую процедуру его настройки, описанную в документации, то в результате можно получить достаточно красивый отчет. К сожалению, этот подход имеет достаточно много слабых сторон. Во-первых, чтобы получить полную картину, необходимо писать логи всех запросов к БД, которые при значительной нагрузке отъедают огромное количество дискового пространства, а также производительность дисковой подсистемы. Во-вторых, в сухом остатке получается только суммарное время исполнения всех запросов и их количество. Это полезная информация, но хотелось бы знать много чего еще.

Два дня назад, 13 мая, вышла beta 1 PostgreSQL 9.3. Во-первых, это хороший знак, что уже пора обновляться на 9.2, либо выбирать 9.2 как основную БД. 9.3 планируется зарелизить в третьем квартале 2013 года.

Обо всех новых возможностях 9.3 можно почитать на официальной wiki.

Но чтобы не только почитать, но и попробовать, я напишу здесь, как поставить 9.3 beta 1 на OSX.

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

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

Чтобы рассказать о тонких моментах pg_repack, мне понадобится немного углубиться в тему блокировок в PostgreSQL. Конечно, лучше всего начать читать про них в официальной документации. Для этой статьи достаточно понимать, что эксклюзивная блокировка (ACCESS EXCLUSIVE LOCK) препятствует выполнению всех операций, включая SELECT, и она нужна для операции ALTER TABLE.

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

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

Вообще, сам не узнаю себя, уже ровно месяц прошел с выпуска Postgresql 9.2, даже вышло обновление 9.2.1, исправляющее некоторые баги, а я все еще ничего не написал об этом.

Поэтому сегодня рассказ будет о Index Only Scan — самом заметном, по моему мнению, изменении в 9.2. Кстати, именно это изменение стоит первым в Release Notes, а значит я не одинок.

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

Если вы делаете обычные бэкапы и запускаете в час ночи pg_dump, а в час дня ваш сервер БД умер, то вы потеряете данные за 12 часов и доверие ваших пользователей. Для многих web-сервисов такой сценарий неприемлем, и, чтобы минимизировать потери данных, стоит использовать непрерывный бэкап.

Я давно мечтаю об Ubuntu, которая работает так же хорошо, как OSX. К несчастью, большинство пользовательских программ в Ubuntu хуже, чем в OSX, а что касается серверной части - OSX очень сильно отстает от Ubuntu (Debian).

К примеру, по-умолчанию, в Postgresql в OSX сломана сортировка русских слов. Решение я нашел здесь.

В третьем квартале 2012 должна выйти версия PostgreSQL 9.2, в которой добавят много интересных возможностей. Среди них — базовая поддержка так полюбившегося всем web-разработчикам формата JSON. На данном этапе появится только возможность проверять JSON на валидность, но судя по списку рассылки, к версии 9.3 будет добавлена возможность строить индексы на JSON объектах подобно тому, как это можно сделать на hstore. Конечно, JSON объекты гораздо сложнее hstore, и как именно и в каком объеме будет реализована поддержка индексов в JSON — пока не ясно.

Наверное, не все знают, что для postgresql существует большое количество расширений, которые называются contrib модулями.

Рассмотрим один из таких модулей - hstore. Этот модуль нужен для того, чтобы в одном поле в БД хранить много значений key/value, фактически, просто какой-то хеш. При этом и ключи и значения могут быть только строками. О том, чем это лучше, нежели просто хранить в текстовом поле сериализованный хеш, я расскажу чуть-чуть попозже. Понадобится это может в том случае, если у вас есть модели с произвольным набором полей.

Если вы работаете с postgresql и сталкиваетесь с затруднительными ситуациями, ответы на которые даже не ясно, как гуглить, то, скорее всего, вам не хватает каких-то фундаментальных знаний этой БД.

Хотя я так и не написал толком, как пользоваться PgQ и Londiste, но уже написал плагин, который облегчает его использование вместе с рельсами.

http://github.com/evtuhovich/pgq/tree/master

В README всё написано на плохом английском (с хорошим английским у меня плохо).

Совсем скоро я добавлю туда возможность прогонять миграции на master и slave базах данных одновременно. Тогда при очередной выкатке необходимо будет сделать только rake londiste:update в самом конце, после того, как все миграции прогонятся.

В продолжении темы о нескольких базах данных в одном rails-приложении расскажу о том, как изящно перенести одну большую таблицу в другую БД postgresql.

Пусть у нас есть таблица messages с большим количеством данных (10 миллионов записей), которые мы решили перенести на другой сервер. Мы сделали, как написано в статье, указанной выше, а также создали в новой БД таблицу messages с такой же структурой.

Теперь с помощью londiste настраиваем репликацию из первой БД во вторую для таблицы messages (об этом я напишу подробнее попозже, пока же можно прочитать об этом у Андрея Стихеева в рассылке ror2ru).

На таблице postgresql с большим количеством данных невозможно быстро создать либо пересоздать индекс. При создании индекса таблица блокируется для операций INSERT, UPDATE и DELETE. В таких случаях может помочь конкурентное создание индекса. Иногда на postgresql стоит пересоздавать индексы, чтобы уменьшить их фрагментацию (и увеличить скорость). Создание конкурентного индекса будет частным случаем его пересоздания.

Пусть имеется таблица orders с 1 миллионом записей (приблизительно) в которой хранятся заказы. И в этой таблице есть поля country_id, region_id, city_id, на которых создан индекс.

При большом количестве запросов к приложению (в широком смысле этого слова) иногда целесообразно ``размазать’’ пиковую нагрузку во времени. Для этого удобно использовать очереди. То есть, если какое-то запрос наверняка должен быть выполнен, но не имеет значение произойдет это прямо сейчас или чуть попозже, можно создать событие в очереди. И когда до этого события дойдет очередь —– оно будет выполнено. Таким образом можно развести сложные запросы во времени.

Очереди подходят для таких задач, как, например, рассылка большого количества сообщений и обновление различных счетчиков в БД (если их актуальность не очень критична). Использование такого подхода позволяет контролировать пиковую нагрузку, за счет чего сделать систему более стабильной в работе и отказоустойчивой. Также это позволяет оставить приемлемое время ответа сервиса, потому что он сможет отвечать что-то до фактического завершения длинной операции.

Миграции в rails — это очень правильный инструмент. Правда, иногда случаются казусы, потому что конкретная БД перестает быть «сферическим конем в вакууме», как только количество данных и нагрузка на нее становится существенной.

Пусть у нас есть таблица posts, в которой 10 миллионов записей. И мы решили добавить в нее поле is_searchable.

    $ script/generate migration add_is_searchable_to_posts

    class AddIsSearchableToPosts < ActiveRecord::Migration
      def self.up
        add_column :posts, :is_searchable, :boolean, :default => true, :null => false
      end

      def self.down
        remove_column :posts, :is_searchable
      end
    end

Если на базе development данных у вас немного, то миграция пройдет замечательно. На production базе она может занять несколько часов, блокируя таблицу posts. Заглянув в документацию по postgresql и немного подумав, можно переписать эту миграцию вот так: