PostgreSQL 11: Мастерство разработки

PostgreSQL гарантирует, что столбец id будет уникальным, но при этом в индексе будет храниться дополнительное поле, так что при запросе обоих столбцов будет произведен просмотр индекса.

CREATE UNIQUE INDEX some_name ON person USING btree (id) INCLUDE (name);

Если вы хотите получить «настоящее время», то пользуйтесь функцией clock_timestamp(), а не now().

SELECT FOR UPDATE блокирует строки так же, как это сделала бы команда UPDATE.

BEGIN;
SELECT * FROM invoice WHERE processed = false FOR UPDATE;
** здесь приложение что-то делает **
UPDATE invoice SET processed = true ...
COMMIT;

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

test=# \timing
Секундомер включен.
test=# SELECT * FROM t_test WHERE id = 432332;
  id  | name
------+------
43232 | hans
(1 строка)
Время: 176.949 мс 

В PostgreSQL имеется команда CLUSTER, позволяющая перезаписывать таблицу в нужном порядке.

CLUSTER t_random USING idx_random;

Получение более точных ответов с помощью нечеткого поиска.

test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

Импорт данных из файла

test=# COPY t_oil FROM PROGRAM '
curl https://www.cibertec-postgresql.com/secret/oil_ext.txt ';
COPY 644

Журналы и статистика системы

-- Динамическая проверка выполняемых запросов
select * from pg_catalog.pg_stat_activity;

-- Если Вы хотите отключить всех пользователей, кроме себя самого, нужно выполнить такой запрос:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND backend_type = 'client backend';

-- Получение информации о базе данных
SELECT * from pg_catalog.pg_stat_database;

-- Получение информации о таблицах
select * from pg_catalog.pg_stat_user_tables;

-- Выявление таблиц, нуждающихся в индексах
select schemaname, relname, seq_scan, seq_tup_read,
seq_tup_read / seq_scan as avg, idx_scan
from pg_stat_user_tables
where seq_scan > 0
order by seq_tup_read DESC
LIMIT 25; 

-- Методика хранения сверхбольших атрибутов
select * from pg_catalog.pg_statio_user_tables;

-- Изучение индексов
select schemaname, relname, indexrelname, idx_scan,
	pg_size_pretty(pg_relation_size(indexrelid)) as idx_size,
	pg_size_pretty(sum(pg_relation_size(indexrelid)) over (order by idx_scan, indexrelid)) as total
from pg_catalog.pg_stat_user_indexes
order by 6;

Параметр work_mem ускоряет не только группировку:

SET work_mem TO default;
SET work_mem TO '1 GB';

Ускорение административных задач

-- CREATE INDEX
SET maintenance_work_mem TO '1 GB';

Интересная статья по оконным функциям https://habr.com/ru/post/268983/