В PostgreSQL начиная с очень давних времён аж версии 8.0 вышедшей в далёком 2005 году для восстановления в определённую точку времени использовался специальный файл конфигурации recovery.conf. Этот же файл впоследствии стал использоваться для режима standby и потоковой репликации.

Однако начиная со следующего релиза PostgreSQL 12 больше recovery.conf работать не будет: я его сломал.
Но зачем?

Вас когда-либо просили посчитать количество чего-то на основании данных в бд за последний месяц, сгруппировав результат по каким-то значениям и разбив всё это ещё по дням/часам?
Если да — то вы уже представляете, что вам придётся написать что-то вроде такого, только хуже

SELECT hour(datetime), somename, count(*), sum(somemetric)
from table
where datetime > :monthAgo
group by 1, 2
order by 1 desc, 2

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

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

А что если я скажу, что прямо в PostgreSQL можно создать вьюху, которая на лету будет учитывать только новые поступающие данные в прямо подобном запросе, как выше?

Так вот — это умеет делать расширение PipelineDB

Привет! Это рассказ о том, что нового в нашем плагине для баз данных. Мы выпускаем его, как отдельный продукт DataGrip, и поставляем почти во все другие наши IDE. Будет много картинок и гифок. Для тех, кому лень их смотреть:

• Поддержка Cassandra
• Создание SQL-файлов из объектов схемы
• Новые инспекции
• Много новых штук в автодополнении
• Работа с источником данных через одно подключение
• Новый поиск
• Высококонтрастная цветовая схема

Спасибо тем, кто пробует EAP-версии и сообщает в наш трекер о проблемах: это помогает не дотащить их до релиза :) Активные пользователи уже получили бесплатные подписки на год.

Мы продолжаем знакомить вас с самыми интересными новостями по PostgreSQL.

Релизы

PostgreSQL 11.1
Вместе с этой версией вышли также 10.6, 9.6.11, 9.5.15, 9.4.20 и 9.3.25 (это последний релиз 9.3). В этой версии есть изменения, связанные с безопасностью. Исправили выявленные ошибки. Прочитать о релизах можно здесь, а скачать здесь.


Postgres Pro Standard 11.1.1, 10.6.1, 9.6.11.1, 9.5.15.1, Postgres Pro Enterprise 10.6.1, 9.6.11.1

Эти релизы основаны на соответствующих ванильных версиях PostgreSQL и на предыдущих версиях Standard и Enterprise. В разделе «Замечания к выпуску» в соответствующей версии документации можно прочитать об отличиях от предыдущей версии семейства. Например, в Enterprise 10.6.1 и 9.6.11.1 добавлена поддержка операционной системы Astra Linux «Смоленск» 1.6 и улучшена стабильность автономных транзакций.

pgAdmin 4 version 3.5.

В этом релизе не только исправлены баги (11 штук), но и добавлены новые возможности. Среди них:

• Миграция с Bootstrap 3 на Bootstrap 4
• Можно останавливать сервисные процессы (Backup/Restore/Maintenance...)

Бинарники для Windows и macOS, а также для Python Wheel, Docker-контейнеры и тарболы с исходниками можно скачать отсюда. RPMы и DEBы должны появиться в репозиториях postgresql.org в ближайшие дни.

TimescaleDB 1.0

В анонсе говорится, что это версия годится для использования в промышленном режиме. Эта СУБД, ориентированная на работу с временнЫми рядами, выполнена как расширение для PostgreSQL. Можно запускать разные версии TimescaleDB с одним и тем же экземпляром PostgreSQL Ядро команды разработчиков — выпускники Принстонского Университета.

Спешу поделиться хорошей новостью: жизнь авторов статей про PostgreSQL и их читателей стала немного лучше.

Как знают все хаброписатели, для оформления исходного кода используется специальный тег <source>, который подсвечивает синтаксис. Не секрет также, что подсветка не всегда получается идеальной, и тогда авторы (которым не все равно, как выглядят их статьи) вынуждены заниматься самодеятельностью — расцвечивать свой код с помощью <font color=...>.

Особенно печально все было с PostgreSQL, поскольку подсветка охватывала более или менее стандартный SQL и категорически не понимала специфики нашей СУБД. Шло время, Алексей boomburum старательно исправлял мои font-ы на source (а я — обратно), пока не стало очевидно, что подсветку надо чинить. Наконец Далер daleraliyorov подсказал выход: добавить поддержку PostgreSQL в библиотеку highlightjs, которой пользуется Хабр. И вот — готово, встречайте.

Буквально на днях Яндекс открыл доступ для beta-пользователей к своему новому сервису — Яндекс.Облако. Так вышло, что это событие совпало с необходимостью выбора облачной платформы для одного из наших внутренних проектов и я решил сразу протестировать производительность решений Яндекса.

Для теста я взял PostgreSQL и старый добрый pgbench. Выбор на СУБД пал потому что было интересно протестировать и сравнить производительность не только виртуальных машин, то и managed database сервисов.

Disclaimer: автор не является ни профессиональным админом, ни DBA, ни специалистом по настройке облачных решений. Тестирование проводилось сугубо в личных целях и на объективность не претендует, поэтому прошу воспринимать статью «as is». Внутри не будет какого-то глубокого разбора, но будет экспресс-сравнение с Selectel VPC (на разных дисках) и различными конфигурациями AWS EC2/RDS в части производительности и стоимости решений. Возможно, это сэкономит кому-то немного времени.

Подробности Yandex.Cloud vs Selectel VPC vs AWS под катом.

Часть I. R извлекает и рисует

Конечно, PostgreSQL с самого начала создавалась как универсальная СУБД, а не как специализированная OLAP-система. Но один из больших плюсов Постгреса — в поддержке языков программирования, с помощью которых из него можно сделать что угодно. По изобилию встроенных процедурных языков ему просто нет равных. PL/R — серверная реализация R — любимого языка аналитиков — один из них. Но об этом позже.

R – удивительный язык со своеобразными типами данных — list, например, может включать в себя не только данные разных типов, но и функции (вообще, язык эклектичный, и говорить о принадлежности его к определенному семейству не будем, чтобы не порождать отвлекающие дискуссии). В нем есть симпатичный тип данных data.frame, который подражает таблице РСУБД — это матрица, у которой столбцы содержат разные типы данных, общие на уровне столбца. Поэтому (и по другим причинам) работать в R с базами данных довольно удобно.

Мы будем работать в командной строке в среде RStudio и соединяться с PostgreSQL через драйвер ODBC RpostgreSQL. Их несложно установить.

Поскольку R создавался как этакий вариант языка S для тех, кто занимается статистикой, то и мы приведем примеры из простенькой статистики с простенькой графикой. У нас нет цели знакомить с языком, но есть цель показать взаимодействие R и PostgreSQL.

Обрабатывать данные, хранящиеся в PostgreSQL, можно тремя путями.

Привет, Хабр! Представляю вашему вниманию перевод статьи «Architecture of a high performance GraphQL to SQL engine».

Это перевод статьи про то, как устроен изнутри и какие оптимизации и архитектурные решения несет в себе Hasura — высокопроизводительный легковесный GraphQL сервер, выступающий прослойкой между вашим веб-приложением и базой данных PostgreSQL.

Он позволяет генерировать GraphQL схему на основе существующей базы данных или создать новую. Поддерживает GraphQL Subscriptions из коробки на основе Postgres-триггеров, динамический контроль прав доступа, автоматическую генерацию join’ов, решает проблему N+1 запросов (batching) и многое другое.

Специальный выпуск POSTGRESSO, посвященный выходу официального релиза версии 11.

---

На улице PostgreSQL праздник. После четырех beta вышла PostgreSQL 11 General Availability, то есть официальная версия. В анонсе есть даже приветственное слово Брюса Момджана: «готовя этот релиз, сообщество особенно заботилось о добавлении функциональности, необходимой для работы с очень большими базами данных. Доказано, что PostgreSQL хорошо работает с транзакционными нагрузками, а теперь новая версия — PostgreSQL 11 — облегчит разработчикам еще и создание приложений для Big Data».

В release notes выделяют

• секционирование:
  • добавлено секционирование по хешу;
  • PRIMARY KEY, FOREIGN KEY, индексы (см. ниже на эту тему) и триггеры;
  • секция по умолчанию для записей, вышедших за границы созданных секций;
  • UPDATE по ключу секционирования теперь может автоматически перемещать запись в соответствующую секцию;
  • PostgreSQL научился исключать ненужные секции (partition pruning) во время исполнения запросов SELECT;
• распараллеливание:
  • теперь можно параллельно создавать индекс в случае B-tree;
  • при CREATE TABLE… AS, CREATE MATERIALIZED VIEW и в некоторых случаях запросов с UNION;
  • улучшена производительность в параллельных HASH JOIN и SEQUENTIAL SCAN;
• появились хранимые процедуры, и в них возможно управление транзакциями;
• JIT-компиляция фрагментов запросов, выигрыш на вычислении выражений;
• оконные функции теперь поддерживают все фреймовые опции SQL:2011 стандарта, в том числе расстояния по RANGE у PRECEDING/FOLLOWING, режим GROUPS, возможность исключения строк из фрейма;
• появились покрывающие индексы [не покрывающие, а инклюзивные, строго говоря — прим. POSTGRESSO], использующие выражение INCLUDE при CREATE INDEX;
• из раздела «разное»: ALTER TABLE… ADD COLUMN c значениями NOT NULL по умолчанию: этот вариант команды теперь не перезаписывает все строки таблицы и, следовательно, работает быстро.

Введение

Привет, Хабр!

Хочу поделиться опытом написания миграций для postgres и django. Речь в основном пойдёт про postgres, django же здесь хорошо дополняет, так как из коробки имеет автоматическую миграцию схемы данных по изменениям модельки, то есть имеет довольно полный список рабочих операций по изменению схемы. Django можно заменить на любой любимый фрэймворк/библиотеку — подходы скорее всего будут похожи.

Не буду описывать как я к этому пришёл, но сейчас читая документацию ловлю на мысли, что нужно было с большей внимательностью и осознанием делать это раньше, поэтому очень рекомендую.

Перед тем как пойти дальше позволю себе сделать следующие предположения.

Можно разделить логику работы с базой данных большинства приложений на 3 части:

1. Миграции — изменение схемы базы данных (таблиц), предположим мы всегда запускаем их в один поток.
2. Бизнес логика — непосредственная работа с данными (в пользовательских таблицах), работает с одними и теми же данными постоянно и конкурентно.
3. Миграции данных — не изменяют схемы данных, работают по сути как бизнес логика, по умолчанию, когда будем говорить про бизнес логику, будем также подразумевать и миграции данных.

Даунтайм — это состояние, когда часть нашей бизнес логики не доступна/падает/грузится на заметное для пользователя время, предположим это пару секунд.

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

Предыдущая статья с описанием адресов ФИАС и функций для работы с ними в среде PostgreSQL вызвала интерес у небольшой части читателей.

Поэтому имеет смысл описать аналогичные функции на языке PL/pgSQL для работы со списком домов ФИАС, загруженным в базу данных под управлением PostgreSQL.


В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций, а также скрипты для создания таблицы с записями домов ФИАС, а также загрузки данных в эту таблицу из файла в формате CSV. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.

Мы продолжаем знакомить вас с самыми интересными новостями по PostgreSQL.

Релизы

PostgreSQL 11 Beta 4
В этом релизе починили баги, выявленные после выхода Beta 3. В том числе:

• теперь отключена по умолчанию JIT-компиляция.
• имена в constraint-ах должны быть уникальны.
• убрали утечку памяти при обращении к XMLTABLE
• исправили ошибки в хранимых процедурах
• доработали секционирование, в том числе выбор секций в момент исполнения (runtime partition pruning)

Подробнее здесь.

PostgreSQL 10.5
В этом релизе несколько десятков исправлений, касающихся WAL, libpq, VACUUM и FREEZE, индексов GIN, распараллеливания запросов, OpenSSL. Вот их список.

Postgres Pro Enterprise 10.5.2.
В этой версии есть следующие нововведения по отношению к Postgres Pro Enterprise 10.5.1, они касаются pgbench:

• pgbench теперь поддерживает составные команды;
• с помощью параметра --latency-limit теперь можно ограничить время, отведённое на повторение транзакций. Если при использовании данного параметра значение --max-tries=0, транзакции могут повторяться неограниченное число раз, пока не истечёт время, заданное параметром --latency-limit;
• при вычислении количества обработанных транзакций и скорости выполнения (TPS) пропущенные и неуспешные транзакции больше не учитываются.

Напомним, за время между нашими выпусками вышел релиз Postgres Pro Enterprise 10.5.1.. Там есть существенные изменения, о них можно прочитать здесь.

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

И в самом деле, в постгресе довольно большая куча всяких метрик, все они представлены в виде представлений и функций. С одной стороны это очень удобно, написал SELECT и получил данные. С другой стороны, такая периодическая писанина слегка утомляет.

В тот же раз, мне нужно было не просто так разово посмотреть на какую-то стату, мне нужно было отслеживать её изменение. Не помню что это было точно, возможно надо было смотреть на изменение лага репликации, или с какой частотой вызывался какой-то конкретный запрос…
Так вот, поймав себя на мысли что мне мне хватает инструмента я начал поиски утилит под свою задачу, но увы, не нашел ничего того что мне бы зашло. Так появился pgCenter.

17 октября в воронежском офисе Mail.Ru Group пройдёт митап по разработке игр. Разработчики Mail.Ru Group расскажут об интересных методах и инструментах, с которыми им довелось работать, и у вас будет время для обсуждения всех выступлений и обмена опытом.

Участие бесплатное, регистрация по ссылке.

15 сентября в Авито прошёл митап, на котором мы говорили о масштабировании приложений на PostgreSQL. Сегодня я хочу поделиться материалами с него — видеозаписями, презентациями от докладчиков, показать фото. Также под катом публикую разбор вопросов викторины, которую мы проводили здесь, на Хабре, перед митапом. И рассказываю о своих впечатлениях от встречи.

Масштабирование СУБД – это непрерывно наступающее будущее. СУБД совершенствуются и лучше масштабируются на аппаратных платформах, а сами аппаратные платформы наращивают производительность, число ядер, памяти — Ахиллес догоняет черепаху, но все еще не догнал. Проблема масштабирования СУБД стоит во весь рост.

Компании Postgres Professional с проблемой масштабирования довелось столкнуться не только теоретически, но и практически: у своих заказчиков. И не раз. Об одном из таких случаев и пойдёт речь в этой статье.

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

В некотором царстве, в некотором государстве... понадобилось мне добавить триггер в модель на pgModeler. Что сделать достаточно легко. А вот добавить триггерную функцию… Тоже легко, но пришлось немного поразбираться с параметрами, предлагаемыми для заполнения/выбора в интерфейсе.

pgModeler — это весьма неплохой инструмент для проектирования баз данных, который умеет генерировать sql-скрипты для PostgreSQL. Подробно об этом инструменте и его возможностях можно почитать на официальном сайте.

Когда то видел реализацию «истории записей» — версионирования, на стороне программы, работающей с SQL базой. Перед изменением записи, из базы получалась старая версия, записывалась в XML и полученная строка XML записывалась в отдельную таблицу версий.

Изначально, в своей программе планировал версионирование сделать когда нибудь потом, какой то срочной необходимости не было. Помню, было желание где-то использовать тип данных jsonb, как только додумался до простой и лаконичной реализации версионирования на стороне SQL, не сделать не смог. Всего лишь одна таблица версий с 5 колонками и одна триггерная функция в 3 строчки кода.

Для описания реализации одной таблицы версий мало, поэтому придется описать еще несколько таблиц для примера.

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

Пример таблицы пользователей:



Последние два поля на картинке, нужны для таблицы версий, также их можно назвать «автор версии» и «дата версии», но, при желании, можно обойтись без них.

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

В итоге набрёл на весьма неплохой инструмент pgModeler. Единственное, не очень понравилось, что sql-скрипты он умеет генерировать только для PostgreSQL. Но т.к. на тот момент (да и сейчас, а то и потом) использовалась эта база данных, то этого инструмента было вполне достаточно.

Приветствую всех любителей SQL!

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

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

Постарайтесь ответить самостоятельно, перед открытием спойлера.

Поехали!