Postgres Guru | Базы данных

2023-04-24 17:01:51 ​​ Полезные запросы в PostgreSQL (мониторим процесс создания или перестроения индекса).

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

Нам неплохо бы знать на какой стадии находиться процесс создания или перестроения индекса и все ли идет по плану. В этом нам поможет следующий запрос:

SELECT pid, datname, command, phase, tuples_total, tuples_done, partitions_total, partitions_done
FROM pg_stat_progress_create_index;

pg_stat_progress_create_index - это представление, содержащее информацию о ходе выполнения команд CREATE INDEX или REINDEX.

Результат запроса будет примерно следующим (используется расширенный вывод):

-[ RECORD 1 ]----+-------------------------------
pid.        | 14944
datname       | postgres
command       | REINDEX
phase        | building index: scanning table
tuples_total    | 0
tuples_done     | 0
partitions_total | 0
partitions_done  | 0

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

https://postgrespro.ru/docs/postgrespro/15/progress-reporting#CREATE-INDEX-PROGRESS-REPORTING

#queries Открыть/Комментировать

2023-04-24 13:02:00 ​​ Настройка ОС Linux под работу PostgreSQL (оперативная память) Dirty background ratio.

В ядре ОС Linux есть несколько параметров, которые отвечают за кэш операционной системы и кол-во грязных страниц в нем.

Один из таких параметров vm.dirty_background_ratio. Это параметр отвечает за процент грязных страниц (измененных), находящихся в оперативной памяти, при достижении которого они должны быть записаны на диск. ОС записывает страницы на диск в фоне и не блокирует при этом работу приложения. Значения этого параметра устанавливаются в диапазоне от 0 до 100. В большинстве Linux дистрибутивах значение по умолчанию равно 10.

Если у вас сервер с большим количеством оперативной памяти, то значение этого параметра по умолчанию может быть слишком большим. Это приведет к тому, что ОС будет накапливать большой объем страниц в памяти, а потом может решить сбросить их все на диск за один раз. Таким образом у нас будут пиковые периодические нагрузки на диск и все преимущества контрольных точек PostgreSQL, которые стараются сбрасывать страницы на диск растянуто во времени пропадут. Плюс такое поведение ОС не слишком безопасно для базы данных с точки зрения неожиданного выключения сервера СУБД.

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

vm.dirty_background_bytes - это параметр схож с предыдущим, только значение устанавливается в байтах, а не в процентах.

В итоге какие значения этих параметров устанавливать? Оптимальные значения этих двух параметров зависят от вашего оборудования. Конкретно, от кол-ва оперативной памяти и скорости ваших дисков.

Базовые рекомендации такие: vm.dirty_background_ratio устанавливать в 5 % от скорости диска, а vm.dirty_background_bytes в 25%. В дальнейшем надо мониторить операции ввода/вывода на диск и регулировать значения этих параметров.

Меняем параметры так же как и раньше:

# cat "vm.dirty_background_ratio = новое значение" >> /etc/sysctl.conf

Применяем изменения:

# sudo sysctl -p

#pgos Открыть/Комментировать

2023-04-24 09:01:29 Полнотекстовый поиск в PostgreSQL (видео).

PostgreSQL уже давно заступила на NoSQL территорию. И это не может не радовать, так как в некоторых случаях не придется использовать разные СУБД под разные задачи и как-то синхронизировать данные.

Александр Алексеев, разработчик ядра СУБД PostgreSQL из компании Postgres Pro, расскажет о еще одной NoSQL возможности PostgreSQL, такой как полнотекстовый поиск.

Из видео вы узнаете:

О преимуществах использования полнотекстового поиска в PostgreSQL;

О возможностях полнотекстового поиска в PostgreSQL;

О специальных индексах для полнотекстового поиска в PostgreSQL;

Примеры использования полнотекстового поиска в PostgreSQL.

СМОТРЕТЬ ВИДЕО

#pgbase Открыть/Комментировать

2023-04-23 13:00:45 #pgmeme Открыть/Комментировать

2023-04-22 13:01:20 #pgmeme Открыть/Комментировать

2023-04-21 17:01:26 ​​ Основы Pl/PgSQL (объявление переменных).

Теперь, когда мы знаем основную структуру блока Pl/PgSQL, начнем разбираться с его составляющими.
Начнем с объявления переменных.

Все переменные, которые будут использоваться в дальнейшем в вашей функции, объявляются в первом блоке (DECLARE).

Синтаксис такой:

имя [CONSTANT] тип [NOT NULL]
[ {DEFAULT | : = | = } значение по умолчанию

где:

имя (обязательно) - имя переменной;

CONSTANT (не обязательно) - означает, что значение переменной нельзя изменить;

тип (обязательно) - тип переменной. Здесь можно указать любые SQL типы (строка, число, дата, время и т.д.) Но с помощью конструкции имя_таблицы%ROWTYPE, можно указать составной тип переменной со структурой заданной таблицы. Или с помощью конструкции имя_таблицы. имя_колонки%TYPE будет использована переменная с таким же типом как и указанная колонка таблицы;

NOT NULL (не обязательно) - значение переменной не может быть NULL;

DEFAULT (не обязательно) - значение переменной по умолчанию. Если мы указали NOT NULL, то этот параметр становится обязательным для заполнения.

Пример:

user_id integer DEFAULT 32;

Все объявленные переменные будут действовать в текущем блоке, во вложенном блоке и в секции обработки ошибок.

#pl/pgsql Открыть/Комментировать

2023-04-21 13:01:45 ​​ Полезные запросы PostgreSQL (опустошение таблицы).

Чтобы не быть героем мема, опубликованного в прошлое воскресенье, разберемся с командой TRUNCATE и чем она отличается от команды DROP TABLE.

TRUNCATE — удаляет данные из таблицы или из набора таблиц. Т.е. в отличии от DROP TABLE, после работы команды у нас останется пустая таблица с колонками. Команда работает очень быстро, так как при ее использовании не происходит сканирования всей таблицы. Ее можно эффективно использовать на таблицах большого размера. Эта команда хороша еще и тем, что немедленно высвобождает место на диске, т.е. после нее не нужно запускать команду VACUUM FULL.

Синтаксис команды такой:

TRUNCATE [ TABLE ] [ ONLY ] имя_таблицы [ * ]
[RESTART IDENTITY | CONTINUE IDENTITY ] [CASCADE | RESTRICT ]

Команду можно использовать и так TRUNCATE имя_таблицы, и так TRANCATE TABLE имя_таблицы, результат будет один и тот же. Последняя команда сделана для совместимости со стандартом SQL.

ONLY - очищается только заданная таблица. Если ONLY не указать, то очиститься и заданная таблица и все ее потомки (если они есть);

RESTART IDENTITY - перезапустить последовательности таблицы. Т.е. если у таблицы есть какие-то автоматически увеличивающиеся данные (последовательности), то данный параметр обнуляет их и начинает отсчет с начала;

CONTINUE IDENTITY - не перезапускать последовательности таблицы. Это поведение по умолчанию;

CASCADE - автоматически очистит все таблицы, которые ссылаются по внешнему ключу на очищаемую таблицу. Использовать данный параметр надо с осторожностью, а то можно снести пол базы данных ;

RESTRICT - этот параметр противоположен предыдущему, т.е. запрещает очистку таблиц, ссылающихся по внешнему ключу на очищаемую таблицу. Это поведение по умолчанию.

Пример использования:

TRUNCATE [TABLE] table1, table2 RESTART IDENTITY;

#queries Открыть/Комментировать

2023-04-21 09:01:45 Миграция приложения Oracle PL/SQL на Postgres pl/pgSQL: планирование, подготовка, переход и два года жизни с новой БД (видео).

Анатолий Анфиногенов -
заместитель директора научного центра, начальник отдела разработки ПО, кандидат физико-математических наук поделился своим опытом переноса системы ЭЛЬБРУС с СУБД Oracle на PostgreSQL.

ЭЛЬБРУС - это система планирования движения грузовых поездов по оптимальным расписаниям. Это приложение работает на всех железных дорогах России.

Стояла задача перевести приложение на СУБД PostgreSQL без его простоя. В базе данных Oracle на тот момент было написано около 200 хранимых процедур (60 000 строк кода ), только в одной схеме базы данных находилось порядка 250 таблиц и 50 Гб оперативных данных.

Одним словом задача стояла непростая и очень ответственная. Смотрим что из этого получилось.

Спойлер: все получилось

СМОТРЕТЬ ВИДЕО

#pgbase Открыть/Комментировать

2023-04-20 17:01:39 ​​ Полезные функции PostgreSQL (подсчет кол-ва строк в таблице).

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

Начнем с простой функции COUNT. Эта функция подсчитывает кол-во строк в таблице и возвращает результат.

Предположим у нас есть такая таблица с именем people:

id | name | age| address  
----+-------+-----+--------
 1 | Ivan | 32 | California
 2 | Maxim| 25 | Texas   
 3 | Ilya | 23 | Norway 
 4 | Pavel| 25 | Rich-Mond
 5 | Yura | 27 | Texas   

Чтобы подсчитать кол-во строк в ней, мы можем написать такой запрос:

SELECT COUNT(*) FROM people ;

Результат запроса будет следующим:

count
-------
  5
(1 row)

Также, функция COUNT может подсчитать кол-во строк, содержащих определенное значение.

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

SELECT COUNT(*) FROM people WHERE name='Yura';

Результат будет следующим:

count
-------
  1
(1 row)

#pgfunc Открыть/Комментировать

2023-04-20 13:01:44 ​​ Полезные запросы PostgreSQL (удаление таблицы).

Раз уж мы с вами теперь знаем как создать таблицу в PostgreSQL, теперь давайте разберёмся как ее удалить.

Для удаления таблицы используется команда

DROP TABLE имя_таблицы;

Команда удалит саму таблицу вместе с данными и все ассоциированные с ней индексы, триггеры, и ограничения.

С помощью команды DROP TABLE мы можем удалить сразу несколько таблиц, например вот так:

DROP TABLE имя_таблицы1, имя_таблицы2;

#queries Открыть/Комментировать