PostgreSQL *

Подолжаю публиковать авторскую переработку Understanding EXPLAIN от Guillaume Lelarge.
Ещё раз обращу внимание, что часть информации для краткости опущено, так что настоятельно рекомендую ознакомиться с оригиналом.

Предыдущие части:

Часть 1

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

Здравствуйте, хабрачеловеки! Темой этой статьи будет создание расширений для PostgreSQL. В качестве примера, мы реализуем небольшую библиотеку для работы с 3D векторами. Параллельно будут рассмотрены пользовательские типы, операторы и приведения типов. Не будет лишним ознакомися с этим материалом, так как реализация хранимых функций будет на языке C. Надеюсь, друзья слонов помогут скрасить серый технический текст статьи.

PostgreSQL предлагает несколько вариантов резервирования данных. Обо всех них уже рассказано не раз, в том числе и на хабре. Но в основном рассказывается про технические особенности методов. Я же хочу постараться рассказать про общую стратегию резервного копирования, объединив все методы в эффективную систему, которая поможет вам сохранить все данные и уменьшить число погибших нервных клеток в критических ситуациях.
Вводные данные: сервер PostgreSQL 9.2, База размером >100Gb.

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

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

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

Когда Ваш проект начинает пользуется популярностью и каждая миллисекунда обработки запроса от пользователя становится критической, приходится искать узкие места в системе. Часто больше всего времени занимает выполнение 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) уже с виду идеальный и комар в нём носа не подточит, но хочется «выжать» ещё немного.

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

К сожалению, в сообществе 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 может послать такой запрос.

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

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

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

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

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

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

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

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

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

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

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

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

С тех пор как число активных пользователей Instagram стало постоянно расти, Postgres оставался нашим надежным фундаментом и неизменным хранилищем данных для большинства данных, создаваемых пользователями. И хотя меньше года назад мы писали о том, как мы храним большое количество данных на Instagram при 90 лайках в секунду, сейчас мы обрабатываем более 10000 лайков в секунду – и наша основная технология хранения данных не изменилась.

За последние два с половиной года, мы поняли несколько вещей и подобрали пару инструментов для масштабирования Postgres и мы хотим ими поделиться – то, что мы хотели бы знать при запуске Instagram. Некоторые из них специфичны для Postgres, другие представлены также и в других базах данных. Чтобы знать, как мы горизонтально масштабируем Postgres, смотрите наш пост Sharding and IDs at Instagram

Думаю многим известна особенность PostgreSQL, которая приводит к эффекту раздувания таблиц, или table bloat. Известно что она проявляет себя в случаях интенсивного обновления данных, как при частых UPDATE так и при INSERT/DELETE операциях. В результате такого раздувания снижается производительность. Рассмотрим почему это происходит и как с этим можно бороться.

Ни для кого не секрет, что проектирование структуры БД является одной из основных и порой очень трудозатратных задач при разработке любого ПО, работающего с данными. Все мы так или иначе проектируем БД, пытаясь представить себе схему взаимосвязей таблиц, а зачастую рисуем, визуализируем структуру БД, прежде чем перенести ее в СУБД. Для моделирования баз данных MySQL есть MySQL Workbench, поставляемый разработчиком, для MS SQL есть Database Diagrams; я до недавнего времени пользовался Dia, а кто-то, может быть, использует для этих целей MS Visio. Но для PostgreSQL я не встречал ни одного адекватного решения, которое позволяло бы максимально просто и точно перенести наброски структуры БД в код ее создания в самой СУБД.

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



Итак… (текст, много картинок)