PostgreSQL *

Не так давно встала необходимость самостоятельно оценить производительность и ресурсоёмкость всё более набирающей популярность noSQL СУБД MongoDB. Для наглядности решил заодно сравнить её с производительностью PostgreSQL, которая также небезызвестна и активно используется.

Здравствуйте, хабрачеловеки! Многие из Вас сталкивались с вынесением бизнес-логики в СУБД в виде хранимых функций/процедур, облегчая клиент. В этом есть как и преимущества, так и недостатки. Сегодня я бы хотел рассказать Вам как создавать хранимые функции в PostgreSQL, написанные на языке C. В статье будут самые основы, которые необходимо знать для начала работы с ними.

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

• материализированные представления
• обновляемые представления
• триггеры к событиям
• рекурсивные представления
• латеральное присоединение
• изменяемые внешние таблицы
• функции и операторы для работы с типом JSON

Привет.
Это топик о том, как относительно быстро и безболезненно настроить секционирование (партицирование) таблицы по месяцам, если вы используете Django+PostgreSQL. Многое из описанного подойдёт и для других фреймворков и ORM.

О том, что такое секционирование и зачем оно нужно, можно почитать, например, здесь, здесь и здесь.

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

Каждый раз писать запросы для включения секционирования не очень хочется, так что попробуем автоматизировать. Хорошо, если на выходе получится что-то, что может использовать и не сильно знакомый с SQL человек. I've read the docs, so you don't have to.

Когда Ваш проект начинает пользуется популярностью и каждая миллисекунда обработки запроса от пользователя становится критической, приходится искать узкие места в системе. Часто больше всего времени занимает выполнение SQL запроса из приложения к базе данных. Попробуем разобраться, что можно оптимизировать в программе при работе с БД.

PostgreSQL 9.3 выйдет с довольно-таки крутой фичей, называющейся материализованные представления. Фича была разработан Кевином Гриттнером и не так давно закоммичена:

commit 3bf3ab8c563699138be02f9dc305b7b77a724307
Дата: Воскресенье 4 Марта 18:23:31 2013 -0600
Автор: Кевин Гриттнер

Добавлены материализованные представления

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

Реализована минимальная функциональность, но и она может быть полезной во многих случаях. В настоящее время данные загружаются только “по требованию” инструкциями CREATE MATERIALIZED VIEW и REFRESH MATERIALIZED VIEW. Ожидается, что в будущих релизах будут добавлены инкрементальные обновления данных с различными настройками времени обновления, и будет дано более четкое определение самому понятию “свежие” данные. В какой-то момент даже запросы смогут использовать материализованные данные вместо данных самих таблиц, но это требует реализации описанного выше функционала в первую очередь.

Большая часть работы по составлению документации проделал Robert Haas. Ревью: Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja. Ревью по вопросам безопасности, включающее решение о том, как лучше реализовать sepgsql, ожидается от KaiGai Kohei.

Приветствую, хаброжители!
В этой статье я хочу поделиться опытом развертывания кластера Master-slave на СУБД PostgreSQL. Отказоустойчивость достигается с помощью возможностей pgpool-II (failover, online recovery).
pgpool — это прекрасное средство для масштабирования и распределения нагрузки между серверами и, думаю, немногие знают о возможностях автоматического создания failover на ведомом сервере при отказе ведущего и как добавить новые мощности в уже работающий кластер без отключения всего кластера.

Рассмотрим ситуацию: имеется статистическая таблица с колонками-идентификаторами и колонками-счётчиками. Требуется просуммировать счётчики по некоторому подмножеству. При этом нас не интересует, каким образом мы выбираем интересующее нас множество — про индексы и партицирование написано множество книг и статей. Будем считать, что все данные уже выбраны самым оптимальным способом и изучим, как быстрее суммировать.

Это не первое место, которое надо оптимизировать, если запрос тормозит, скорее последнее. Изложенные ниже идеи осмысленно применять когда план выполнения (explain) уже с виду идеальный и комар в нём носа не подточит, но хочется «выжать» ещё немного.

Хотя интернет уже переполнен статьями о «правильной» настройке связки PostgreSQL и 1C 8.2, для подводных камней всегда остается место. При сравнении производительности СУБД PostgreSQL на разных ОС, показатели различаются в разы. Самая большую обиду принесла любимая Ubuntu (человечность). После долгих дней и ночей проведенных за консолью этой ОС, она разочаровала окончательно.

Тормоза PostgreSQL в Ubuntu Server. Что с ними делать? Сколько раз можно наступать на грабли?

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

К сожалению, в сообществе node.js, на данный момент сложилась такая ситуация, что подавляющее большинство драйверов к распространённым сервисам имеет ряд существенных недостатков, не позволяющих приложениям достигать заслуженных высот эффективности и стабильности. Вы наверняка слышали все эти ужасающие истории о том, что «node.js течёт», оно “игрушечное”, не предназначенное для применения в настоящей высоконагруженной среде. Однако, как мы убедились на собственном опыте, с умом написанное ПО для ноды блестяще справляется со всеми испытаниями суровых боевых реалий. И здесь мы приходим к главному вопросу: что же мешает среднестатистическому node.js-драйверу нормальной работе?

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

Предположим что у нас есть postgresql в режиме потоковой репликации. master-сервер и hot-standby готовый заменить погибшего товарища. При плохом развитии событий, нам остается только создать trigger-файл и переключить наши приложения на работу с новым мастером. Однако, возможны ситуации когда вполне законные изменения были сделаны криво написанной миграцией и попали как на мастер, так и на подчиненный сервер. Например, были удалены/изменены данные в части таблиц или же таблицы были вовсе удалены. С точки зрения базы данных все нормально, а с точки зрения бизнеса — катастрофа. В таком случае провозглашение горячего hot-standby в мастера, процедура явно бесполезная…
Для предостережения такой ситуации есть, как минимум, два варианта…

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

Вышло обновление безопасности для всех текущих версий PostgreSQL, включая 9.2.4, 9.1.9, 9.0.13 и 8.4.17. Это обновление исправляет особо опасную уязвимость в версиях 9.0 и новее. Всем пользователям крайне рекомендуется обновиться.

Главная проблема безопасности, исправленная в этой версии, CVE-2013-1899, позволяет злоумышленнику повредить или уничтожить некоторые файлы в директории сервера, отправив запрос на подключение к базе данных с именем, начинающимся на "-". Любой, кто имеет доступ к порту PostgreSQL может послать такой запрос.

По мере работы с базами данных, ознакомления с их плюсами и минусами, возникает момент, когда принимается решение миграции с одной СУБД в другую. В данном случае возникла задача переноса сервисов с MySQL на PostgreSQL. Вот небольшой перечень вкусностей, которые ждут от перехода на PostgreSQL, версии 9.2 (с более подробным списком возможностей можно ознакомится тут):

• наследование таблиц (есть ограничения, которые обещают в будущем исправить)
• диапазоны: int4range, numrange, daterange
• поддержка из коробки несколько языков для хранимых функций (PL/pgSQL, PL/Tcl, PL/Perl, PL/Python и голый C)
• оператор WITH, позволяющий делать рекурсивные запросы
• (планируется) материализованные представления (частично они доступны и сейчас — как IUD правила к представлению)
• (планируется) триггера на DDL операции

Как правило, существующие решения опираются на работу с уже готовым SQL дампом, который конвертируется в соответствии с синтаксисом целевой БД. Но в некоторых случаях (активно использующееся веб-приложение с большим объемом информации) такой вариант несет определенные временные затраты на создание SQL дампа из СУБД, его конвертации и загрузку получившегося дампа снова в СУБД. Поэтому оптимальней будет online-вариант (прямиком из СУБД в СУБД) конвертера, что может существенно уменьшить простой сервисов.

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

Решаемая задача. Исходные данные

Итак, имеем сервер БД, с которым работают клиенты, и резервный сервер, на который надо настроить репликацию с основной базы данных.
В моём случае используется PostgreSQL 9.2.1, который установлен на обоих серверах и поддерживает потоковую репликацию. Предположим что база данных на основном сервере развернута и работает, на резервном только установлен, но не настроен PostgreSQL. Для примера возьмем IP-адрес 192.168.1.1 за адрес основного сервера, IP-адрес 192.168.1.2 — за адрес резервного.

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

Но предположим, что у Вас небольшая задача, пара серверов и встроенная postgres-репликация. О ее настройке материалов достаточно, и о действиях в случае отказа мастера тоже можно найти.

А вот с вопросом восстановления мастера оказалась беда, поэтому делюсь с Вами собранным по кусочкам с просторов интернета руководством к действию, опробованным и протестеным мною на связках серверов Debian GNU/Linux и FreeBSD 8.2 с PostgreSQL 9.1

Есть концепции работы с базой, основанные на ORM, CodeFirst со своими преимуществами и недостатками. Предлагаемая здесь унификация базы основана в первую очередь на подходе Database First.

Схема базы данных приложений со сложной доменной моделью (к которым относятся системы ERP) обычно состоит из
нескольких сотен таблиц.
Поэтому на начальном этапе проектирования базы для избежания многократных дублирований и разбухания схемы важно
определиться с несколькими базовыми таблицами для хранения общих свойств базовых сущностей приложения
и все остальные таблицы уже проектировать как вспомогательные или дополнения основных таблиц.

Я обещал одному пользователю написать этот пост ещё 8 февраля, а обещания надо выполнять.

Сподвигло меня дать это обещание, конечно, не просто желание рассказать, почему же на нашем сайте серфинг (процесс получения рекомендаций) вечером того дня отдавал пятисотки, а более общие соображения.

А именно — юзернейм настойчиво нам советовал поднять мощности, а то ну вот невозможно же уже.
Мощностей у нас хватает. Безаппеляционность и самоуверенность юзернейма меня… огорчили, и вот поэтому я и решил написать про то, почему на самом деле зачастую ложатся сайты.

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

Классический сценарий

Вы работаете над проектом, где транзакционные данные хранятся в базе данных. Затем вы развёртываете приложение в рабочей среде, и производительность великолепна! Запросы проходят шустро, и задержка при их вводе практически незаметна. Через несколько дней/недель/месяцев база данных становится всё больше и больше, и скорость запросов замедляется.

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

Администратор базы данных (DBA) посмотрит и проследит, чтобы база данных была оптимально настроена. Он предложит добавить определённые индексы, убрать логирование на отдельную партицию, подправить параметры движка базы данных и убедиться, что база данных здорова. Можно также добавить выделенных IOPS (Input/Output Operations Per second) на EBS диске, чтобы увеличить скорость дисковых партиций. Это даст вам выиграть время и даст возможность решить главную проблему.

Рано или поздно вы поймёте, что данные в вашей базе данных являются узким местом (botleneck).
В базах данных многих приложений важность информации уменьшается со временем. Если вы сможете придумать способ избавиться от этой информации, ваши запросы будут проходить быстрее, время создания бэкапов уменьшится, и вы сэкономите кучу места. Вы можете удалить эту информацию, однако тогда она пропадёт безвозвратно. Вы можете послать множество DELETE запросов, вызвав создание тонн логов, и использовать кучу ресурсов движка базы данных. Так как же мы избавимся от старой информации эффективно, но не потеряв её навсегда?
В примерах мы будем использовать PostgreSQL 9.2 на Engine Yard. Вам также нужен git для установки plsh.