Топы

Best PostgreSQL GUIs in 2021 (Updated)

TOP-11 GUI от Retool. Поразительно, что Retool (GUI с web-интерфейсом) участник топа, но не входит в десятку — он скромно замыкает их список.

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

1. pgAdmin;
2. Navicat;
3. DBeaver;
4. HeidiSQL;
5. Datagrip;
6. OmniDB;
7. Beekeeper Studio;
8. TablePlus;
9. QueryPie;
10. SQLGate;
11. Retool.

Но это, в свою очередь, топ внутри топа, золото на пьедестале вот этого:

TOP-8: Что больше всего читали (кликали) на Postgres Weekly в 2021-м

1. A Roundup of Postgres GUIs in 2021.
2. 125 psql Tips.
3. Lots of Lesser Known Postgres Features.
4. Working with Hierarchical Structures in Postgres.
5. Postgres 14: It's The Little Things.
6. Do You Really Need Redis? How to Get Away with Just PostgreSQL.
7. Postgres vs Redis vs Memcached Performance.
8. Is Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL a Better Choice for Me?
   

То есть Redis хоть и не в призах, зато удостоился двух строчек. Две строчки приходятся на статьи компании Crunchy Data.

В двух словах о первой тройке (о #1 см. выше):

#2: psql Tips

В этом списке, составленном Летицией Авро (Lætitia Avrot) о каждом пункте по абзацу с примером. Летиция — соосновательница ассоциации Postgres Women и входит в комитет по хорошему поведению (PostgreSQL Code of Conduct Committee). У неё есть сайт My DBA Notebook.

Вот пример её советов — #31:

Если вам надо дебажить скрипт, можно использовать флаг -s или --single-step. С ним psql будет останавливаться после каждой команды.



Такое поведение возможно аж с версии 7.1. Но, как выясняется, даже многие бывалые пользователи psql забывают о -s.

#3: Lesser Known PostgreSQL Features

Хаки Бенита (Haki Benita) рассказывает о 18 действительно не самых известных возможностях PostgreSQL. Скажем, команда \crosstabview в psql.

Популярные расширения для PostgreSQL: как установить и для чего использовать

Это TOP-10 расширений по статистике компании Selectel. Их облачные PostgreSQL-базы поддерживают 40 расширений, но клиенты пользуются ими выборочно. На данный момент расширения есть у 26% пользователей DBaaS. Вот провайдер и решил расспросить клиентов, какие расширениям им нужны и для чего. Мы их тоже перечислим, но, поскольку многие ссылки в статье на документацию Postgres Professional, мы даём везде ссылки на PostgreSQL 14 (в статье немного хаотичный выбор версий).

PostGIS
Не нуждается в представлении, как говорится.

TimescaleDB
А в облачных базах данных Selectel оно и не добавляется как расширение, оно там представлено отдельным типом БД. Особенно полезно для ретроспективного анализа временнЫх рядов.

uuid-ossp
Расширение генерирует уникальный идентификатор UUID вместо обычного ID. Чтобы не было дублей при копировании, объединении и др. операциях с базами.

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

postgres_fdw
Среди применений — шардинг: и для решения конкретных задач, и для разработки серьёзных систем на базе PostgreSQL на будущее / на продажу.

hstore — немного неожиданно. Сейчас о JSON/JSONB говорят несравнимо больше, чем об hstore, появившемся раньше. Приятная справка: авторы hstore — Олег Бартунов и Фёдор Сигаев.

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

pg_trgm
Для неточного поиска в текстах (триграммы — последовательности из трех букв, входящие в индексируемый текст). Можно искать фамилии с опечатками. Ускоряет LIKE/ILIKE-запросы. Можно использовать в сочетании с полнотекстовым поиском.

citext
Расширение адаптирует данные для регистронезависимой проверки. Удобно при работе, например, с электронными адресами.

btree_gist
Подходит для типов данных, где не работает жесткая семантика сравнения — «больше», «меньше» или «равно», характерная для btree. Полезно для баз данных, где часть полей индексируется только с GiST, а другая — представляет собой простые типы данных.

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

Новогодние советы

Не в смысле на 1-е января — а на весь 2022-й год. Советы от Крейга Кирстинса (Craig Kerstiens, Crunchy Data). В Five Tips For a Healthier Postgres Database in the New Year он предлагает:

• устанавливайте таймауты для запросов (SET statement_timeout);
• отслеживайте запросы (с pg_stat_statements);
• логируйте медленные запросы, настраивайте log_min_duration_statement (не полагаясь на pg_stat_statements);
• лучше управляйте соединениями (с PgBouncer, даже если есть пулер на уровне среды приложения/ORM;
• найдите золотую середину: заводя индексы, не переусердствуйте.

У Crunchy есть интерактивные курсы, на которые ссылается Крейг. Некоторые темы там не пересекаются с документацией PostgreSQL.

Новый The PostgreSQL transition guide

Появился английский перевод второй версии Руководства по переходу на PostgreSQL (оригинал на французском). Она пока не переведена на русский.

Первая версия появилась в 2015-м тоже на французском, и в том же году была переведена на русский Олегом Бартуновым и Иваном Панченко. Русский перевод тогда вышел как Руководство по переходу на PostgreSQL: помощь в принятии решения и под шапкой Межминистерская дирекция по информационным системам и средствам связи. Межминистерский центр по свободному программному обеспечению, соответствующей оригиналу. Речь о французских институциях, конечно.

Новый вариант появился в 2019-м тоже сначала на французском, а переведён на английский в этом — в качестве дара сообществу.

2022 vs. 2021

Tech Trends in 2022: Gaze into the Cockroach Labs Crystal Ball

В блоге Cockroach Labs предсказывают будущее. Но сначала о настоящем. Год 2021 был годом, когда Kubernetes окончательно утвердился в мейнстриме. Свои интуиции автор, Мишель Жинау (Michelle Gienow) , подкрепляет ссылками на исследования, совместные с Rad Hat. По данным опросов 94% использовали Kubernetes уже в промышленной эксплуатации. Но уже в будущем году то же должно случиться с бессерверными вычислениями. Да, они не оправдали ожиданий (overhyped) в этом году, но стремительно приближаются к критической массе, о чём тоже свидетельствует опрос, где из опрошенных компаний 88% бессерверную архитектуру уже используют в проде или собираются использовать.

Но Мишель ещё и ведёт просветительскую деятельность по поводу нового понятия, которое маркетологи трактуют так, как им выгодно. В статье с полу-приличным названием WTF is Serverless, Anyway? предлагается 4 критерия, позволяющие отделить tru serverless от маркетинговой шелухи.

А в статье What is a Serverless SQL Database? есть критерии и для бессерверных баз данных. Вот они:

• от пользователя не требуется никакого управления (no server management);
• автоматическое эластичное масштабирование (Automatic elastic scale);
• встроенная отказоустойчивость (resiliency and fault tolerance);
• биллинг на основе потребления ресурсов (consumption-based billing);
• доступность мгновенно и всегда (instant access and always available).

Чем занималась Core Team

Core Team Report: June 2019 — December 2021

Это коротенький отчёт-обзор административной деятельности Команды за два с половиной года. Никаких революционных изменений за это время, кажется, не произошло. Заметна некоторая формализация деятельности (например, Contributors Committee, который теперь даёт статус контрибьютора, опираясь на некоторый набор объективных критериев). Говорится, что в список не вошла некоторая конфиденциальная информация. В абзаце Trademark Dispute речь об улаженном конфликте с Альваро Эрнандесом (Álvaro Hernández Tortosa) — о конфликте мы писали в Postgresso 34). Торговые марки торговыми марками, но Альваро вообще-то критиковал команду сразу по нескольким вопросам, особенно ему не нравилась непрозрачность решений.

Новости архитектуры

На вторниках ru#postgres событие: Александр Коротков представил проект, над которым работал более 4 лет: базу OrioleDB.

В середине декабря в беседе с Николаем Самохваловым он оттолкнулся от того, что ненавидит в Postgres: примерно то же, что и Рик Брэнсон в известной статье 2020-го года 10 Things I Hate About PostgreSQL. И сделал грустный слайд:



Чтобы радикально решать эти проблемы, демократичный и не слишком формализованный стиль управления сообществом не слишком подходит, поясняет Александр. Поэтому серьёзно перелопатить Postgres он взялся чуть ли не в одиночку (сейчас в компании, прописанной в Минске, 6 сотрудников).

Меняется вот что:
WAL на уровне блоков => WAL на уровне записи
Buffer mapping => прямые ссылки на страницы
Блокировки буфера => доступ без блокировок
MVCC => Undo log
Неуклюжая репликация WAL на уровне блоков => Мультимастер на Raft с репликацией WAL на уровне записи.

Переход к WAL на уровне записи (а не блока) помогает избавляться от многих блокировок, которые на машинах с большим количеством ядер серьёзно сажают производительность. Новый протокол WAL хорошо ложится на концепцию мультимастера. Число соединений тоже стало неплохо масштабироваться. Рассказ сопровождается графиками — тестами. Испытывался релиз, который выложен. Но он — настойчиво напоминал Александр — пока далёк от продакшн-версии. Для опробования и обмена впечатлениями.

Россияне создали СУБД, которая на «Эльбрусах» обгоняет PostgreSQL на Intel

Компания Релэкс, известная как разработчик СУБД ЛИНТЕР (последний её релиз был в 2018-м) представила прессе SoQoL. Слухами долго земля полнилась. Это, кажется, первое появление в прессе. Не слишком много из этой статьи на C News можно пока понять. SoQoL — распределённая база данных с ACID-транзакциями в распределённой среде, с динамическим расширением системы новыми узлами. В Рэлекс утверждают, что на серверах с «Эльбрусами» Soqol справляется с обработкой транзакций в TPC-C-тестах HammerDB лучше, чем PostgreSQL на Intel-машинах похожей конфигурации. Хм. Ждём подробностей.

Статьи

PostgreSQL 15: Часть 3 или Коммитфест 2021-11

Вместе с началом зимы, релизный цикл 15-й версии продвинулся еще на один, теперь уже третий, коммитфест. О предыдущих двух можно подробнее прочитать здесь:
2021-07, 2021-09. Поскольку этот выпуск Postgresso получился с уклоном в индексы, вот пункт по теме:

Индексы BRIN не блокируют HOT-обновления
commit: 5753d4ee

HOT-обновление не применяется, если хотя бы один изменяемый столбец команды UPDATE проиндексирован любым типом индекса.

Однако индексы BRIN не содержат ссылок на табличные строки. И использовать HOT для обновления столбцов с такими индексами вполне безопасно. Поэтому в 15-й версии индексы BRIN больше не будут препятствовать оптимизации HOT update:

15=# CREATE INDEX flights_bi ON flights USING brin(actual_departure);
15=# SELECT pg_stat_reset_single_table_counters('flights'::regclass);

15=# UPDATE flights SET actual_departure = actual_departure + '5 min'::interval
     WHERE flight_id = 1;

15=# SELECT n_tup_hot_upd FROM pg_stat_all_tables WHERE relname = 'flights';

 n_tup_hot_upd
---------------
             1

Ну и как всегда рекомендуем почитать сериал Егора Рогова об индексах.

How partial, covering, and multicolumn indexes may slow down UPDATEs in PostgreSQL

Статья Николая Самохвалова, по мотивам реальных событий (сообщает автор).


Самохваловский Слон очень красив, реальный претендент на Мисс/Мистер Слон.

В статье эксперименты с pgbench, а не просто теоретизирование. Создаются:

• индекс на 1 колонку;
• частичные индексы;
• покрывающий индекс;
• частичный покрывающий индекс

И что же? Читайте. Прогнозы и мечты не всегда сбываются. «Не навреди» — напоминает Николай о принципе Гиппократа.

Understanding Postgres GIN Indexes: The Good and the Bad

Обширное исследование индексов от автора — Лукаса Фиттля (Lukas Fittl, pganalyze) — многоколоночные GIN-индексы, комбинации GIN и B-tree-индексов. Недостатки GIN. Опять триграммы и уроки, извлеченные после их применения в GitLab. А-а, вот это всё к чему: используйте, господа pganalyze Index Advisor. Ну или не используйте, а статью почитайте — небесполезная.

Text Search example with the OMDB sample database

Швейцарец Франк Пашо (Franck Pachot) рассказывает, как использовал GIN-индексы для ускорения текстового поиска. О FTS (Full Text Search) он отзывается более, чему уважительно: greatest feature of PostgreSQL. Франк адресуется к пользователям Oracle, SQL Server и ElasticSearch. А дальше Франк говорит, что PostgreSQL не настолько great, как его родной Yugobyte. Но в любом случае обнажает интересные нюансы.

В другой статье — Triggers & Stored Procedures for pure data integrity logic and performance
— он сетует на то, что PostgreSQL не даёт ему сделать GIN-индекс, содержащий нужное ему поле, появляется ошибка: ERROR: access method «gin» does not support included columns and trying to add it in the indexed columns will raise ERROR: data type timestamp with time zone has no default operator class for access method «gin». После этого автор обращается к решению, основанному на ограничениях (constraints). Большая статья, много примеров (листингов), отнюдь не сводится к пропаганде Yugobyte.

Tricks for Faster Spatial Indexes

В конце статьи Пол Рэмси (Paul Ramsy, Crunchy Data) приходит к выводу, что пространственные индексы очень чувствительны к тому, в каком порядки приходят данные на вход. Сильно коррелированные входные данные могут привести к плохо сбалансированным и/или плохо организованным деревьям (а мы напомним, что деревья GiST, в отличие от SP-GiST, сбалансированы всегда). Рандомизация ввода помогает сбалансировать деревья, и это даёт измеряемый эффект.

Это помогает и в случае in-memory K-D-деревьев. А есть и более продвинутый способ: pre-seeding.

PostGIS Nearest Neighbor Syntax

Рамси напоминает о существовании оператора <->, который в комбинации с ORDER BY радикально ускоряет поиск ближайших соседей (KNN).

Should I Create an Index on Foreign Keys?

Чарли Батиста (Charly Batista, Percona) отвечает на этот вопрос уклончиво: нельзя сказать, что индексы по внешнему ключу всегда бесполезны. Но нередко. И приводит причины бесполезности и полезности. И сам вопрос, и статья возникли как результат вебинаров, которые он проводит.

Some Indexing Best Practices

Майкл Кристофайдес (Michael Christofides, pgmustard) даёт довольно очевидные советы. Но пусть будут здесь.

Primary Keys vs. UNIQUE Constraints

Ханс-Юрген Шёниг (Hans-Jürgen Schönig, Cybertec) напоминает: и первичные ключи, и уникальные ограничения подразумевают создание индексов. Но создаются они по-разному. Это особенно важно, когда имеются значения NULL. Вообще-то, в статье 2016-го года Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения и др об этом поподробней (не говоря уж о наших любимых статьях Егора Рогова).

JSON(B)

Проклятье TOAST и с каким маслом его ест JSONB

Статья по мотивам выступления Олега Бартунова на конференции Highload++ Олега Бунина.

Олег разбирает, что происходит, когда размер значения поля типа jsonb переваливает за 2КБ, и задействуется TOAST. Спойлер: ничего хорошего: время поиска увеличивается на порядок, если не на два, так как ходить приходится по сложной системе ссылок, да ещё и в 4 захода. В статье очень наглядные схемы. TOAST не был рассчитан на JSON(B) и вообще на структурированные данные, поэтому с ним такая морока. Видео самого доклада здесь.

What’s new with JSON in PostgreSQL v14

Сара Чайма Атуону (Sarah Chima Atuonwu) рассказывает о появившемся в PostgreSQL 14 subscripting — возможности обращения к элементам jsonb по индексу. Она, между прочим, обращает внимание на то, что изменение строки присваиванием значения по индексу и jsonb_set (как делалось до PG 14) дают разный результат.

Ещё статьи

What developers find surprising about Postgres transactions

Статья Лоуренса Джонса (Lawrence Jones, GoCardless) на эту непростую тему интересна «человеческим фактором», взглядом пользователя MySQL. Автор считает, что даже DBA со стажем в десятилетия могут попасться на неверных ожиданиях от транзакции: в MySQL по умолчанию уровень изоляции Repeatable Read, а в PostgreSQL по умолчанию Read Commited. Это можно знать, но по привычке, подсознательно ждать другого поведения. Лоуренс напоминает и о возможности добавить FOR SHARE к SELECT, затребовав явную блокировку.

Leaving MySQL

Стейнар Гундарсон (Steinar H. Gunderson) покинул Oracle (команду MySQL) и заявил: MySQL слабенькая база. Очень рекомендую подумать в сторону PostgreSQL. Это признание бурно обсуждалось на ycombinator.com.

The anatomy of a linux read call [ч.I] и [ч.II]

Фриц Хугланд (Frits Hoogland, Yugabyte) заныривает под капот Postgres до уровня ОС. В этой большой двухчастной статье немало утилит, полезных для диагностики сложных случаев.

Некоторые релизы

Database Lab Engine 3.0

Новое в версии 3.0:

• пользовательский интерфейс теперь в ядре, поэтому можно работать с одним экземпляром DLE;
• сохраняющиеся (persistent) клоны: они переживут рестарт DLE (или VM);
• в режиме логического разворачивания данных («logical» data provisioning mode) появилась возможность переключать перезагрузку состояния клона, используя снэпшот различных пулов/наборов данных (pool/dataset);
• упростилась загрузка и конфигурация;
• улучшилась одновременная работа нескольких DLE на одной машине;
• поддержка PostgreSQL 14.

К тому же отныне действует деперсонифицированная телеметрия, включённая по умолчанию (её можно отключить, а не сделать персонифицированной).

Заодно из релиза можно узнать (я не знал) словечко Сдвиг-влево тестирование (Shift-left_testing). Вот гитхаб проекта.

PostGIS 3.2.0beta3

«Подавать с PostgreSQL 14». Использует быстрое построение GiST, появившееся в 14-й. А если скомпилировано с только что появившимся GEOS 3.10.1, то ST_MakeValid будет работать лучше и быстрее. Появилось много новых функций для расширений postgis, postgis_raster и postgis_topology и для нового формата ввода/экспорта FlatGeobuf. Но по-прежнему поддерживаются PostgreSQL начиная с 9.6-14 и GEOS начиная с 3.6, proj начиная с 4.9. С деталями релиза можно ознакомиться здесь, к тому же они включены в тарбол исходников.

pg_graphql

Новость в том, что расширение pg_graphql, реализующее язык графовых запросов GraphQL, теперь отдан в опен сорс. Это объявил в блоге supabase Оливер Райс (Oliver Rice), самый активный контрибьютор этого проекта. Расширение умеет генерить схему GraphQL из проанализированной схемы PostgreSQL, парсить исходные запросы и отображать их в новую схему специальными резолверами, которые уже имеются на сервере базы данных, поэтому внешние сервисы не требуются]. Есть демо.

MySQL-to-PostgreSQL v5.5

Нового в 5.5:

• поддержка SSL-соединений в PostgreSQL;
• верифицированная поддержка Azure PostgreSQL и MySQL;
• верифицированная PostgreSQL 14.1;
• документация расширена;
• другие улучшения и исправления багов.

Демо-версия здесь. Она конвертирует не более 50 записей на таблицу, не конвертирует внешние ключи и представления.

PostgREST 9.0.0

Эта мажорная версия совместима с PostgreSQL 14, в ней новые фичи и исправления. Чейнджлог и бинарники на этой странице на GitHub.

pg_dirtyread

Интересное расширение: запись удалена, а вам она всё ещё доступна на чтение (если её ещё не удалил VACUUM). pg_dirtyread 1.0 было написано Филом Сорбером (Phil Sorber) в 2012-м. Кристофер Берг (Christoph Berg) в версии 1.1 в 2017-м добавил возможность возвращать системные столбцы и взял на себя дальнейшую поддержку.

An Introduction to pg_auto_failover project

Димитри Фонтейн (Dimitry Fontain) рассказывает историю создания расширения pg_auto_failover для автоматического переключения узлов Postgres, как оно работает и в чём его преимущества. Гитхаб проекта здесь.

Access-to-PostgreSQL v2.3

Это программа для миграции с баз Microsoft Access на PostgreSQL или Heroku. В зависимости от привилегий на целевом сервере можно экспортировать данные MS Access в базу или переписывать уже существующую.

Конференции

PGCONF NYC 2021

Прошла 2-3 декабря в Нью-Йорке. Программа была интересная. Например, Генриэтта Домбровская — соавтор книжки Оптимизация запросов PostgreSQL. Олег Бартунов рассказал о производительности JSONB — когда этот тип данных работает хорошо, какие проблемы остались, и какие их решения просматриваются.


Медаль за вклад в PostgreSQL 14. Такую же медаль я вручу всем российским контрибьюторам PG14. Тяжёлые они, кстати, — предупреждает Олег Бартунов.

Highload++ 2022

Должна состояться 17-18 марта 2022 в московском Крокус-Экспо.

С НАСТУПАЮЩИМ НОВЫМ ГОДОМ, КОЛЛЕГИ!