Программы для работы с postgresql linux
PostgreSQL – это опенсорсная реляционная СУБД. В статье будет рассматриваться процесс установки, настройки / управления, а также базовые операции с БД.
Если на вашей машине стоит MacOS, то процесс установки можно запустить командой:
На Linux СУБД устанавливается так:
Если у вас другая ОС, есть непонятные моменты или вопросы – обращайтесь в официальный хелп.
После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:
Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. Попробуйте ввести следующую команду:
Этой командой вы запустите утилиту psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.
Если вам нужна помощь, введите \help (или -h) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести \help [имя команды], если вам нужна помощь по конкретной команде. Например, если ввести \help UPDATE в консоли psql, вы увидите синтаксис команды update.
Если у вас возникает много вопросов – не стоит отчаиваться. Поиск в интернете предоставит массу примеров, ну и официальную документацию psql никто не отменял.
Первым делом необходимо проверить наличие существующих пользователей и баз данных. Выполните следующую команду, чтобы вывести список всех баз данных:
На рисунке выше вы видите три базы данных по умолчанию и суперпользователя postgres, которые создаются при установке PostgreSQL.
Чтобы вывести список всех пользователей, выполните команду \du. Атрибуты пользователя postgres говорят нам, что он суперпользователь.
Чтобы выполнять базовые действия в СУБД, нужно знать Structured Query Language (SQL).
Создание базы данных
Для создания базы данных используется команда create database. В приведенном ниже примере создается база данных с именем proglib_db.
Если вы забыли точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-», как показано на рисунке ниже. Это зачастую указывает на то, что необходимо завершить (дописать) запрос.
На картинке нам сообщают об ошибке из-за того, что в нашем случае база уже создана. Вы поймете, что к чему, когда начнете писать более длинные запросы.
Создание нового юзера
Для создания пользователя существует команда create user. В приведенном ниже примере создается пользователь с именем author.
Вы можете установить пароль для существующего пользователя. С этой задачей справится команда \password:
Чтобы задать пароль при создании пользователя, можно использовать следующую команду:
Удаление базы или пользователя
Для этой операции используется команда drop: она умеет удалять как пользователя, так и БД.
Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).
Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:
Давайте войдем в базу proglib_db под пользователем author. Нажмите \q, чтобы выйти из текущей БД, а затем выполните следующую команду:
В связи с новыми задачами на работе приходится изучать Postgres. Столкнулся с тем, что ни одна IDE мне не нравится. Пробовал Navicat, Datagrip, Pgadmin.. всё не то, да и дорого порой слишком.. возможно привык к функционалу PLSQL developer 14. В общем, не долго думая, решил сам написать себе IDE на Java (т.к. изучаю Java четвёртый год) и включить в неё самые удобные для меня функции (а может и не только для меня удобные). Назвал её PIDES ( Postgres + IDE + Sql :)
Если у вас есть какие-нибудь дополнения по функционалу или коду - предлагайте здесь. Лично я намучился с обработкой селекта внизу справа (запуск - F9). Выборка делается из одной таблицы, менять последовательность полей нельзя, но можно убирать ненужные.
Каждый может подключиться к своей БД, предварительно указав параметры подключения по кнопке с шестерёнкой.
Также программа работает в Linux (в mint 20.2 не поддерживается прозрачность окна (opacity), в ubuntu 20 не поддерживается TrayIcon).
настройки открываются кнопкой с шестерёнкой в(C:\Users\Public\Documents\Pides\config.txt)
избранные таблицы, отмеченные галочкой, а также комментарии сохраняются в файл C:\Users\Public\Documents\Pides\favorite.txt
приложение сворачивается в трей (нажатие правой кнопки мыши на иконке в трее вызывает меню с двумя командами Show и Close)
создана сплит панель с 2 таблицами: объекты базы данных (слева), содержимое объекта (справа)
возможно изменение прозрачности окна приложения
добавлена кнопка Refresh для обновления приложения после внесения изменений в файл конфигураций
добавлено окно ввода sql-запроса (пока выборка только из одной таблицы). Запуск - клавиша F9.
показан список таблиц, вью и матвью
по двойному клику на имени объекта открывается содержимое объекта
при нажатии на значок суммы в строке отображается количество записей в таблице
возможна сортировка в столбцах
при нажатии правой кнопкой мыши на заголовках копируется в clipboard:
имя конкретного заголовка
все названия заголовков через запятую
данные в конкретном столбце в виде столбца без запятой
добавлены checkbox для изменения списка объектов
возможность добавления комментариев по объекту
отображает строки выбранной слева таблицы (лимит строк задаётся в config.txt)
возможна сортировка в столбцах
контекстное меню по правой кнопке мыши на строке (копировать или удалить строку/строки)
двойной клик левой кнопкой мыши выделяет всю строку
при нажатии правой кнопкой мыши на заголовках копируется в clipboard:
имя конкретного заголовка
все названия заголовков через запятую
данные в конкретном столбце в виде строки: data1, data2, data3
данные в конкретном столбце в виде столбца без запятой
данные в конкретном столбце в виде выражения: in ('data1', 'data2', 'data3')
Конференция PG Day Russia растет и масштабируется: этим летом мы готовим для вас доклады и тренинги по всем наиболее популярным базам данных, а также по администрированию и хранению данных. в рамках подготовки мы запустили корпоративный блог, где планируем делиться ценной информацией о происходящем в мире баз данных. Первый пост посвящен инструментам разработки для PostgreSQL, его автор varanio будет рад ответить на ваши вопросы и комментарии!
Я поспрашивал различных людей, имеющих отношение к PostgreSQL, что они используют в реальной жизни для разработки приложений, использующих PG.
Это, конечно, не строго математическая выборка, но тем не менее получился некоторый список инструментов на слуху, которые достойны того, чтобы их "пощупать", что я и собираюсь сделать в этой статье.
Если вашего инструмента нет в списке, или у вас просто есть что сказать, добро пожаловать в комментарии.
Итак, вот неформальный топ и субъективное описание.
На первом месте psql, и это неудивительно. Надежный как автомат калашникова, бесплатный, стоит из коробки, что еще надо для счастья? Для редактирования запросов используется редактор, указанный в переменной окружения EDITOR, обычно ставят vim, nano или что-то в этом духе. Ну и вообще, psql — это unix-way, т.е. можно его запускать со своим редактором, своим пейджером для отображения результатов, ему можно на вход подавать sql-запрос через пайп, и вывод направлять куда надо.
Из минусов можно отметить слабенький автокомплит, а также то, что приходится заучивать неинтуитивные команды из серии \d \dt+ \sf и т.д. (впрочем, все описания команд доступны через команду \? )
Ну, и работа в консоли и в виме — это не всех устраивает почему-то :)
На самом деле, иногда хочется иметь где-нибудь слева полный список таблиц/вьюх и иметь возможность щелкнуть мышкой по нужной, чтобы посмотреть, что там вообще. Т.е. хоть какой-то GUI. Работа в psql хоть и эффективна, но напоминает работу в темной комнате с маленьким фонариком, освещающим лишь только один объект за раз.
datagrip
IDE для баз. Несмотря на то, что продукт относительно свежий, он уже используется повсеместно. В основном за счет того, что сразу встроен в мегапопулярные продукты от компании JetBrains: IntelliJ IDEA, PyCharm, PhpStorm и т.д.
Собственно, эта его встроенность одновременно является и главной киллер-фичей продукта: вы редактируете, например, php-код, в котором есть строка с sql-запросом, и внезапно понимаете, что IDE вам подсказывает (прямо в вашем коде) синтаксис SQL, названия таблиц и их полей, подчеркивает красненьким, если что-то написано не так, форматирует SQL и многое-многое другое. Конечно, в этом же IDE можно делать и то, что умеют другие GUI для баз: просматривать списки таблиц и других сущностей, отдельно делать запросы, экспорт таблиц в разные форматы и многое другое.
Из особенностей я бы отметил следующие вещи:
- можно выделить несколько insert'ов и нажать "Edit as table" (см. картинку). После чего отредактировать это в удобном табличном виде вместо sql-синтаксиса, причем там же можно добавлять строки, колонки, экспортировать в csv и т.д.
- Можно сравнивать результаты двух запросов. Это полезно, когда пытаешься упростить сложный запрос, и при этом ничего не сломать.
- встроенность в код проработана не до конца. К примеру, при переименовывании в каком-либо интерфейсе колонки таблицы, IDE не находит нужные строки с SQL в коде (при этом автокомплит в этих строках работал), и наоборот, находит какую-то чушь.
- Визуальной разработки не очень много. Т.е. вы можете сделать таблицу, но view уже не можете. Если таблица содержит какие-то id с foreign key (допустим, ссылка на некий словарь), хотелось бы при в вводе данных в таблицу выбирать значения из словаря, а не вбивать айдишки.
- Если посмотреть таблицу в какой-нибудь из схем, то Datagrip посылает запрос set search_path = имясхемы, что приводит к плохим последствиям, если используется pgbouncer (а он используется почти всегда в случае с php или когда много серверов), так что для dev-разработки лучше использовать разные подключения: для работы кода — через pgbouncer, для ide — напрямую к базе.
Datagrip активно развивается, в частности, исправлены некоторые раздражающие баги с подсветкой синтаксиса.
В целом хороший современный инструмент, рекомендую.
pgAdmin
Им многие пользуются, но, скорее по привычке. Или потому что это бесплатно. pgAdmin4 — продукт странноватый, при этом в описании сказано, что это самый лучший опенсорс продукт для разработки и администрирования.
Как его использовать с точки зрения разработки — еще менее понятно. Субъективно, интерфейс в целом не удобен для разработки. Несмотря на то, что четвертую версию переписали на python + JS с jQuery, по сути, осталось всё то же самое.
Чтобы немного пояснить ситуацию, в голове разработчика такая картина: есть база на каком-то серваке, в ней — схемы, в схемах — таблицы и вьюхи. Т.е. таблица — максимум, 3-й уровень. А если база одна, то вообще второй уровень. Ткнул по таблице — увидел несколько первых строк.
В голове разработчика pgAdmin как-то так: "Смерть Кощеева на конце иглы, та игла в яйце, то яйцо в утке, та утка в зайце, тот заяц в сундуке, а сундук стоит на высоком дубу, и то дерево Кощей как свой глаз бережёт", а именно (см. картинку):
Есть группа серверов, в ней есть сервер, на сервере существуют базы, роли и т.д., из баз можно выбрать конкретную базу, в ней видно схемы, языки, еще бог знает что. В схемах можно выбрать нужную схему, в схеме 100500 всего, и где-то в конце списка "таблицы". В таблицах можно выбрать нужную таблицу, по ней надо кликнуть правой кнопкой мыши, там в большом списке выбираешь "view data", в этой "view data" есть "view first 100 rows" и уже там наконец-то смерть кощеева несколько строк для ознакомления.
Киллер-фичей pgAdmin является возможность дебажить хранимые процедуры pl/pgsql. Других бесплатных программ с этой возможностью я не встречал.
EMS Studio
EMS Studio, похоже, работает только под Windows. Это его главный недостаток, потому что, как известно PostgreSQL очень редко используют под виндой.
Я этот софт посмотрел только один раз под Wine, поэтому могу ошибаться, но вообще мне жутко не понравилось. Бешенное нагромождение непонятных иконок, невнятный интерфейс. Кстати, у меня под Wine заглючили всплывающие подсказки, и я играл в "угадай функциональность по картинке". Очень тяжело.
До кучи там зачем-то сделан визуальный конструктор запросов. Где вместо того, чтобы текстом написать where >, надо нажать мышкой несколько кнопок и понавыбирать из выпадающего списка. Тем, кто знает SQL — это не нужно, тем кто не знает — это не поможет.
Фичи, которые называют как удобные: auto-complete с алиасами, экспорт результата выполнения запроса в SQL формате (insert), удобный GUI для экпорта базы, возможность выполнять только выделенную часть SQL.
Умеет дебаг pl/pgsql. В общем, много чего умеет, но какой-то выдающейся особенности, что отличало бы от других, я не могу назвать.
NAVICAT
Navicat — это, наверное, самая богатая фичами программа. Она умеет всё, что умеют другие GUI для БД: дизайнер объектов, просмотрщик таблиц, автокомплит, инструменты проектирования базы, отладка pl/pgsql, импорт/экспорт и так далее.
Поистине всеобъемлющий софт, который работает практически на любой ОС. Навскидку, намного удобнее EMS Studio.
Киллер-фичей, на мой взгляд, является сравнение баз. Т.е. можно взять две базы, узнать, чем они отличаются по структуре и сформировать запросы для синхронизации.
Ценник, правда, что называется, "конский" — в два раза дороже, чем EMS. Но тут, похоже, это полностью оправдано.
PGCLI
Те, кто пробует работать с psql, сразу начинают мечтать о более богатом функционале, например, автодополнении. Для реализации этих хотелок существует pgcli.
pgcli умеет автодополнять ключевые слова, функции, таблицы, колонки, колонки в алисах. Умеет подсвечивать синтаксис, редактировать SQL в многострочном режиме без отдельного редактора и т.д.
Короче, pgcli — это, по сути, psql на стероидах.
phppgadmin
Многие из тех, кто перешел с MySQL, инстинктивно ищут аналоги phpmyadmin, и натыкаются на phppgadmin. К сожалению, phppgadmin не развивается уже несколько лет, так что о мертвых или хорошо, или ничего. В общем, промолчим, пожалуй.
Не по всем из этих инструментов у меня есть опыт использования, поэтому прошу высказаться в комментариях. Что используете вы?
Также надо отметить, что на конференцию pgday приедут разработчики популярных инструментов не только для постгреса, но и других бд, можно будет их помучать вопросами и высказать какие-то пожелания по фичам. В любом случае, приглашаем всех посетить это полезнейшее мероприятие, которое пройдет в Санкт-Петербурге 5-7 июля!
TOP-11 GUI от Retool. Поразительно, что Retool (GUI с web-интерфейсом) участник топа, но не входит в десятку — он скромно замыкает их список.
- pgAdmin;
- Navicat;
- DBeaver;
- HeidiSQL;
- Datagrip;
- OmniDB;
- Beekeeper Studio;
- TablePlus;
- QueryPie;
- SQLGate;
- Retool.
В этом списке, составленном Летицией Авро (Lætitia Avrot) о каждом пункте по абзацу с примером. Летиция — соосновательница ассоциации Postgres Women и входит в комитет по хорошему поведению (PostgreSQL Code of Conduct Committee). У неё есть сайт My DBA Notebook.
Если вам надо дебажить скрипт, можно использовать флаг -s или --single-step . С ним psql будет останавливаться после каждой команды.
Такое поведение возможно аж с версии 7.1. Но, как выясняется, даже многие бывалые пользователи psql забывают о -s .
Хаки Бенита (Haki Benita) рассказывает о 18 действительно не самых известных возможностях PostgreSQL. Скажем, команда \crosstabview в psql.
Это 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-персонами недели — там в интервью всегда есть вопрос ваше любимое расширение. Но предлагаем читателям полистать эти интервью самостоятельно. Ну или распарсить их собственным скриптом, если есть азарт.
Новогодние советы
- устанавливайте таймауты для запросов (SET statement_timeout);
- отслеживайте запросы (с pg_stat_statements);
- логируйте медленные запросы, настраивайте log_min_duration_statement (не полагаясь на pg_stat_statements );
- лучше управляйте соединениями (с PgBouncer, даже если есть пулер на уровне среды приложения/ORM;
- найдите золотую середину: заводя индексы, не переусердствуйте.
Новый The PostgreSQL transition guide
Появился английский перевод второй версии Руководства по переходу на PostgreSQL (оригинал на французском). Она пока не переведена на русский.
Первая версия появилась в 2015-м тоже на французском, и в том же году была переведена на русский Олегом Бартуновым и Иваном Панченко. Русский перевод тогда вышел как Руководство по переходу на PostgreSQL: помощь в принятии решения и под шапкой Межминистерская дирекция по информационным системам и средствам связи. Межминистерский центр по свободному программному обеспечению, соответствующей оригиналу. Речь о французских институциях, конечно.
Новый вариант появился в 2019-м тоже сначала на французском, а переведён на английский в этом — в качестве дара сообществу.
2022 vs. 2021
В блоге Cockroach Labs предсказывают будущее. Но сначала о настоящем. Год 2021 был годом, когда Kubernetes окончательно утвердился в мейнстриме. Свои интуиции автор, Мишель Жинау (Michelle Gienow) , подкрепляет ссылками на исследования, совместные с Rad Hat. По данным опросов 94% использовали Kubernetes уже в промышленной эксплуатации. Но уже в будущем году то же должно случиться с бессерверными вычислениями. Да, они не оправдали ожиданий (overhyped) в этом году, но стремительно приближаются к критической массе, о чём тоже свидетельствует опрос, где из опрошенных компаний 88% бессерверную архитектуру уже используют в проде или собираются использовать.
Но Мишель ещё и ведёт просветительскую деятельность по поводу нового понятия, которое маркетологи трактуют так, как им выгодно. В статье с полу-приличным названием WTF is Serverless, Anyway? предлагается 4 критерия, позволяющие отделить tru serverless от маркетинговой шелухи.
- от пользователя не требуется никакого управления (no server management);
- автоматическое эластичное масштабирование (Automatic elastic scale);
- встроенная отказоустойчивость (resiliency and fault tolerance);
- биллинг на основе потребления ресурсов (consumption-based billing);
- доступность мгновенно и всегда (instant access and always available).
Чем занималась Core Team
Это коротенький отчёт-обзор административной деятельности Команды за два с половиной года. Никаких революционных изменений за это время, кажется, не произошло. Заметна некоторая формализация деятельности (например, Contributors Committee, который теперь даёт статус контрибьютора, опираясь на некоторый набор объективных критериев). Говорится, что в список не вошла некоторая конфиденциальная информация. В абзаце Trademark Dispute речь об улаженном конфликте с Альваро Эрнандесом (Álvaro Hernández Tortosa) — о конфликте мы писали в Postgresso 34). Торговые марки торговыми марками, но Альваро вообще-то критиковал команду сразу по нескольким вопросам, особенно ему не нравилась непрозрачность решений.
В середине декабря в беседе с Николаем Самохваловым он оттолкнулся от того, что ненавидит в Postgres: примерно то же, что и Рик Брэнсон в известной статье 2020-го года 10 Things I Hate About PostgreSQL. И сделал грустный слайд:
Чтобы радикально решать эти проблемы, демократичный и не слишком формализованный стиль управления сообществом не слишком подходит, поясняет Александр. Поэтому серьёзно перелопатить Postgres он взялся чуть ли не в одиночку (сейчас в компании, прописанной в Минске, 6 сотрудников).
Меняется вот что:
WAL на уровне блоков => WAL на уровне записи
Buffer mapping => прямые ссылки на страницы
Блокировки буфера => доступ без блокировок
MVCC => Undo log
Неуклюжая репликация WAL на уровне блоков => Мультимастер на Raft с репликацией WAL на уровне записи.
Переход к WAL на уровне записи (а не блока) помогает избавляться от многих блокировок, которые на машинах с большим количеством ядер серьёзно сажают производительность. Новый протокол WAL хорошо ложится на концепцию мультимастера. Число соединений тоже стало неплохо масштабироваться. Рассказ сопровождается графиками — тестами. Испытывался релиз, который выложен. Но он — настойчиво напоминал Александр — пока далёк от продакшн-версии. Для опробования и обмена впечатлениями.
Статьи
Вместе с началом зимы, релизный цикл 15-й версии продвинулся еще на один, теперь уже третий, коммитфест. О предыдущих двух можно подробнее прочитать здесь:
2021-07, 2021-09. Поскольку этот выпуск Postgresso получился с уклоном в индексы, вот пункт по теме:
HOT-обновление не применяется, если хотя бы один изменяемый столбец команды UPDATE проиндексирован любым типом индекса.
Однако индексы BRIN не содержат ссылок на табличные строки. И использовать HOT для обновления столбцов с такими индексами вполне безопасно. Поэтому в 15-й версии индексы BRIN больше не будут препятствовать оптимизации HOT update:
Ну и как всегда рекомендуем почитать сериал Егора Рогова об индексах.
Статья Николая Самохвалова, по мотивам реальных событий (сообщает автор).
Самохваловский Слон очень красив, реальный претендент на Мисс/Мистер Слон.
- индекс на 1 колонку; ; ;
- частичный покрывающий индекс
Обширное исследование индексов от автора — Лукаса Фиттля (Lukas Fittl, pganalyze) — многоколоночные GIN-индексы, комбинации GIN и B-tree-индексов. Недостатки GIN. Опять триграммы и уроки, извлеченные после их применения в GitLab. А-а, вот это всё к чему: используйте, господа pganalyze Index Advisor. Ну или не используйте, а статью почитайте — небесполезная.
Швейцарец Франк Пашо (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.
В конце статьи Пол Рэмси (Paul Ramsy, Crunchy Data) приходит к выводу, что пространственные индексы очень чувствительны к тому, в каком порядки приходят данные на вход. Сильно коррелированные входные данные могут привести к плохо сбалансированным и/или плохо организованным деревьям (а мы напомним, что деревья GiST, в отличие от SP-GiST, сбалансированы всегда). Рандомизация ввода помогает сбалансировать деревья, и это даёт измеряемый эффект.
Это помогает и в случае in-memory K-D-деревьев. А есть и более продвинутый способ: pre-seeding.
Рамси напоминает о существовании оператора , который в комбинации с ORDER BY радикально ускоряет поиск ближайших соседей (KNN).
Чарли Батиста (Charly Batista, Percona) отвечает на этот вопрос уклончиво: нельзя сказать, что индексы по внешнему ключу всегда бесполезны. Но нередко. И приводит причины бесполезности и полезности. И сам вопрос, и статья возникли как результат вебинаров, которые он проводит.
Майкл Кристофайдес (Michael Christofides, pgmustard) даёт довольно очевидные советы. Но пусть будут здесь.
Ханс-Юрген Шёниг (Hans-Jürgen Schönig, Cybertec) напоминает: и первичные ключи, и уникальные ограничения подразумевают создание индексов. Но создаются они по-разному. Это особенно важно, когда имеются значения NULL. Вообще-то, в статье 2016-го года Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения и др об этом поподробней (не говоря уж о наших любимых статьях Егора Рогова).
JSON(B)
Статья по мотивам выступления Олега Бартунова на конференции Highload++ Олега Бунина.
Олег разбирает, что происходит, когда размер значения поля типа jsonb переваливает за 2КБ, и задействуется TOAST. Спойлер: ничего хорошего: время поиска увеличивается на порядок, если не на два, так как ходить приходится по сложной системе ссылок, да ещё и в 4 захода. В статье очень наглядные схемы. TOAST не был рассчитан на JSON(B) и вообще на структурированные данные, поэтому с ним такая морока. Видео самого доклада здесь.
Сара Чайма Атуону (Sarah Chima Atuonwu) рассказывает о появившемся в PostgreSQL 14 subscripting — возможности обращения к элементам jsonb по индексу. Она, между прочим, обращает внимание на то, что изменение строки присваиванием значения по индексу и jsonb_set (как делалось до PG 14) дают разный результат.
Ещё статьи
Статья Лоуренса Джонса (Lawrence Jones, GoCardless) на эту непростую тему интересна «человеческим фактором», взглядом пользователя MySQL. Автор считает, что даже DBA со стажем в десятилетия могут попасться на неверных ожиданиях от транзакции: в MySQL по умолчанию уровень изоляции Repeatable Read, а в PostgreSQL по умолчанию Read Commited. Это можно знать, но по привычке, подсознательно ждать другого поведения. Лоуренс напоминает и о возможности добавить FOR SHARE к SELECT, затребовав явную блокировку.
Фриц Хугланд (Frits Hoogland, Yugabyte) заныривает под капот Postgres до уровня ОС. В этой большой двухчастной статье немало утилит, полезных для диагностики сложных случаев.
Некоторые релизы
- пользовательский интерфейс теперь в ядре, поэтому можно работать с одним экземпляром DLE;
- сохраняющиеся (persistent) клоны: они переживут рестарт DLE (или VM);
- в режиме логического разворачивания данных («logical» data provisioning mode) появилась возможность переключать перезагрузку состояния клона, используя снэпшот различных пулов/наборов данных (pool/dataset);
- упростилась загрузка и конфигурация;
- улучшилась одновременная работа нескольких DLE на одной машине;
- поддержка PostgreSQL 14.
«Подавать с 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, реализующее язык графовых запросов GraphQL, теперь отдан в опен сорс. Это объявил в блоге supabase Оливер Райс (Oliver Rice), самый активный контрибьютор этого проекта. Расширение умеет генерить схему GraphQL из проанализированной схемы PostgreSQL, парсить исходные запросы и отображать их в новую схему специальными резолверами, которые уже имеются на сервере базы данных, поэтому внешние сервисы не требуются]. Есть демо.
- поддержка SSL-соединений в PostgreSQL;
- верифицированная поддержка Azure PostgreSQL и MySQL;
- верифицированная PostgreSQL 14.1;
- документация расширена;
- другие улучшения и исправления багов.
Эта мажорная версия совместима с PostgreSQL 14, в ней новые фичи и исправления. Чейнджлог и бинарники на этой странице на GitHub.
Интересное расширение: запись удалена, а вам она всё ещё доступна на чтение (если её ещё не удалил VACUUM). pg_dirtyread 1.0 было написано Филом Сорбером (Phil Sorber) в 2012-м. Кристофер Берг (Christoph Berg) в версии 1.1 в 2017-м добавил возможность возвращать системные столбцы и взял на себя дальнейшую поддержку.
Димитри Фонтейн (Dimitry Fontain) рассказывает историю создания расширения pg_auto_failover для автоматического переключения узлов Postgres, как оно работает и в чём его преимущества. Гитхаб проекта здесь.
Это программа для миграции с баз Microsoft Access на PostgreSQL или Heroku. В зависимости от привилегий на целевом сервере можно экспортировать данные MS Access в базу или переписывать уже существующую.
Конференции
Прошла 2-3 декабря в Нью-Йорке. Программа была интересная. Например, Генриэтта Домбровская — соавтор книжки Оптимизация запросов PostgreSQL. Олег Бартунов рассказал о производительности JSONB — когда этот тип данных работает хорошо, какие проблемы остались, и какие их решения просматриваются.
Медаль за вклад в PostgreSQL 14. Такую же медаль я вручу всем российским контрибьюторам PG14. Тяжёлые они, кстати, — предупреждает Олег Бартунов.
Что такое графический интерфейс PostgreSQL? Зачем он нужен? Как это может помочь вам в управлении базами данных? Узнайте о лучшем программном обеспечении Postgre GUI, которое можно попробовать в 2021 году.
Перевод публикуется с сокращениями, автор оригинальной статьи Ilon Adams.
PostgreSQL – это передовая открытая система управления объектно-реляционными базами данных. В основном она используется на предприятиях и поддерживает запросы SQL и JSON.
По данным Stack Overflow, PostgreSQL является второй наиболее используемой СУБД после MySQL в 2021 году. Более 40% из 70 000+ опрошенных предпочитают Postgres базам данных SQLite, MongoDB, Redis и другим.
У пользователя, есть два способа администрирования СУБД:
- писать запросы через CLI (не всем это нравится);
- использовать графический пользовательский интерфейс (GUI) Postgres.
Второй вариант намного удобнее, т. к. он позволяет повысить производительность. Давайте рассмотрим наиболее используемые инструменты GUI .
Что такое GUI PostgreSQL?
Графический интерфейс PostgreSQL – это инструмент управления базами данных PostgreSQL. Он позволяет любому пользователю запрашивать и визуализировать данные, а также манипулировать данными и анализировать их. Вы можете получать доступ к серверам баз данных и перемещаться по ним с помощью графического интерфейса.
Основные причины, по которым пользователи предпочитают графический интерфейс:
- длинная кривая обучения работе с CLI и сложная адаптация;
- не очень приятный в использовании интерфейс командной строки;
- недостаток информации, которую консоль предоставляет за один раз;
- трудности при просмотре и мониторинге базы данных с помощью консоли.
Использование GUI дает следующие преимущества:
- ярлыки, которые можно использовать для быстрого доступа к данным;
- широкие возможности визуализации данных;
- удаленный доступ к серверу;
- легкий доступ к операционной системе.
Лучшее программное обеспечение с графическим интерфейсом
Вероятно для кого-то будет неожиданностью, что ориентированное на Postgres приложение pgAdmin не является единственным доступным инструментом.
Прежде всего есть низкоуровневый конструктор внутренних инструментов UI Bakery . Изначально он не был создан для управления Postgres, однако с его помощью вы можете подключить несколько источников данных (базы данных, сторонние приложения, REST API) в одном UI . Bakery обладает широкими возможностями визуализации данных для отображения PostgreSQL, MongoDB, MySQL, Microsoft SQL, Redis и т.д.
Поскольку UI Bakery ориентирован на веб, не нужно тратить время на его установку и настройку. Вы можете создать графический интерфейс для своей базы, используя ряд готовых компонентов: таблицы, диаграммы, графики, карты, кнопки, выпадающие списки и т.д. Этот процесс занимает минуты или часы вместо недель ручного кодинга.
Подход с низкоуровневым кодом к управлению базами данных гораздо более экономичен и гибок, чем использование традиционных графических инструментов. Тем не менее, давайте рассмотрим и другие продукты.
1. pgAdmin
pgAdmin – кроссплатформенный графический инструмент с открытым исходным кодом.
- совместим с Linux, Windows, macOS;
- позволяет работать с несколькими серверами одновременно;
- экспорт в CSV;
- планирование запросов;
- возможность отслеживать ваши сеансы, блокировки БД с помощью панели мониторинга;
- ярлыки в редакторе SQL для более удобной работы;
- встроенный отладчик процедурного языка;
- тщательная документация и активное сообщество.
- медленный и не всегда интуитивно понятный пользовательский интерфейс по сравнению с платными конкурентами;
- тяжелый;
- высокий порог вхождения;
- для работы с несколькими базами данных одновременно потребуются продвинутые навыки.
2. DBeaver
DBeaver – инструмент управления PostgreSQL с открытым исходным кодом, поддерживающий коннект к нескольким базам данных.
- кроссплатформенность;
- поддержка более 80 баз данных;
- визуальный конструктор, позволяющий добавлять запросы без навыков работы с SQL;
- несколько представлений данных;
- импорт/экспорт данных в CSV, HTML, XML, JSON, XLS, XLSX;
- повышенная безопасность данных;
- полнотекстовый поиск данных и возможность отображения результатов в виде таблиц/представлений;
- доступен бесплатный тарифный план.
- низкая производительность по сравнению с конкурентами;
- слишком частые обновления, что раздражает;
- после некоторого времени бездействия DBeaver отключается от базы данных.
3. Navicat
Интуитивно понятный (с недавнего времени проприетарный) GUI для Postgres.
- простая и быстрая установка;
- поддержка Windows, Linux, iOS;
- удобный визуальный конструктор SQL;
- автодополнение кода;
- инструмент моделирования данных: управление объектами базы данных, схемами проектирования;
- планировщик заданий: запускайте задания, получайте уведомления о завершении задания;
- синхронизация источников данных;
- импорт/экспорт данных в Excel, Access, CSV и другие форматы;
- защита данных с помощью SSH и SSL;
- использование облачных сервисов Amazon, Google и др.
- низкая производительность GUI;
- высокая цена по сравнению с конкурентами;
- одна лицензия ограничена одной платформой (вам понадобятся 2 отдельные лицензии для PostgreSQL и MySQL);
- множество дополнительных возможностей, требующих времени для изучения.
4. DataGrip
Продвинутая IDE для работы с несколькими базами данных, созданная в JetBrains .
- кроссплатформенность (поддержка Windows, macOS, Linux);
- простая навигация по схеме;
- настраиваемый UI с консолью для обеспечения безопасности выполняемой работы;
- быстрое обнаружение ошибок;
- встроенная система контроля версий;
- поддержка MySQL, SQLite, MariaDB, Cassandra и других;
- отчеты с возможностью их интеграции с диаграммами и графиками;
- автодополнение кода.
- высокая цена;
- высокое потребление оперативной памяти;
- сложный процесс отладки ошибок;
- длинная кривая обучения;
- не предназначен для использования в качестве облачного веб-приложения;
- не подходит для одновременного управления несколькими базами данных.
5. HeidiSQL
Инструмент с GUI и открытым исходным кодом для Postgres (и не только). Пока поддерживается только Windows .
- простая установка, легковесная по сравнению с конкурентами;
- поддержка PostgreSQL, MySQL, Microsoft SQL Server, MariaDB;
- возможность подключения и управления несколькими серверами баз данных в одном окне;
- прямой экспорт SQL из одной базы данных в другую;
- массовый просмотр и редактирование таблиц;
- автодополнение кода и подсветка синтаксиса;
- сообщество с активной поддержкой и регулярные обновы;
- экспорт таблиц и данных в Excel, HTML, JSON, PHP;
- зашифрованное соединение.
- не кроссплатформенное приложение;
- частые проблемы со стабильностью;
- нет отладчика процедурного языка.
6. TablePlus
Программное обеспечение с графическим интерфейсом для управления базами данных SQL и NoSQL. С закрытым исходным кодом.
- высокая производительность;
- настраиваемый UI;
- подсветка синтаксиса;
- высокий уровень безопасности данных обеспечивается за счет сквозного шифрования в соединении.
- часто возникают проблемы с UX при работе с другими базами данных, кроме PostgreSQL;
- недешево, а пробная версия предлагает ограниченную функциональность;
- поддержка клиентов оставляет желать лучшего.
7. OmniDB
Простой открытый инструмент с GUI для PostgreSQL.
- кроссплатформенность (поддержка Windows, Linux, macOS);
- поддержка PostgreSQL, Oracle, MySQL, MariaDB;
- очень отзывчивый и легкий по сравнению с некоторыми альтернативами;
- автозаполнение SQL;
- подсветка синтаксиса;
- возможность создания настраиваемых диаграммы для отображения релевантных метрик БД;
- встроенная отладка.
- не самый лучший вариант, если вы работаете с несколькими базами одновременно;
- отсутствие поддержки и документации.
Заключение: UI Bakery – неочевидный, но мощный вариант
Когда вы выбираете программное обеспечение с GUI , основывайте окончательное решение на нескольких аспектах:
- размер команды;
- используемые ОС;
- тип СУБД;
- количество баз данных, с которыми вы планируете работать.
DBeaver, DataGr i p и HeidiSQL больше подходят для одного человека, работающего с одной базой. Navicat – выбор для команды благодаря возможности совместной работы. Почти все упомянутые инструменты являются кроссплатформенными за исключением HeidiSQL, который поддерживает только Windows.
Низкоуровневая UI Bakery отлично подходит, если вам нужно объединить несколько различных источников данных – будь то базы данных, сторонние инструменты или API.
Похоже, что pgAdmin и другое классическое ПО теряет популярность. Низкоуровневый подход к управлению базами данных позволяет получать гораздо лучшие результаты за меньшее время.
Читайте также: