linux_tasks/module2/05_sql_postgresql/tasks.md

Задания

1. Установка

Установите postgresql на любую виртуальную машину. Перейдите в пользователя postgres.

$ sudo su - postgres

Вы суперпользователь базы данных.

2. Знакомство с командной строкой psql

В psql выполните инициализацию таблиц базы данных

\i scripts/weather.sql

Первая команда, которую нужно знать, это команда помощи:

\?

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

Чтобы вывести список возможных SQL команд, наберите

\h

Чтобы вывести информацию о команде, добавьте её после \h. Например

\h create database

Выведите список всех баз данных

\l

Из командой строки psql также можно выполнить команду linux. Например список файлов корневого каталога операционной системы

\! ls -la

2.1 Описание таблиц

Команда \d без параметров выведет список всех таблиц и объектов в текущей базе данных. Если добавить символ +, для каждой таблицы можно увидеть дополнительную информацию такую как количество занимаемого места на диске.

\d+ weather

Существует множество \d-команд. Наиболее часто используемые:

• \dt - вывод всех таблиц,
• \dn - вывод всех схем,
• \dv - вывод всех представлений (views),
• \du - вывод всех пользователей,
• \df - вывод всех функций,
• \dp - вывод привилегий доступа для таблиц и представлений.

2.2 Пейджер

По умолчанию пейджер psql, который разбивает вывод на страницы, включён. Вы можете отключить его установив значение параметра pager равным 0.

\pset pager 0

3. Язык SQL

Попробуйте дать базе данных простой запрос

SELECT event_type FROM weather LIMIT 20;

Это запрос на получение данных колонки event_type из таблицы weather. Количество результатов будет ограничено 20 записями.

В терминале вы также можете написать многострочный запрос. Вводите запрос как обычно, используя Enter для переноса строки. psql создаст так называемый буфер запроса. Запрос не будет интерпретирован, пока вы не введёте символ ;. Чтобы сбросить буфер при ошибке ввода, используйте \r.

\r
SELECT DISTINCT(event_type)  
FROM weather  
WHERE state = 'HAWAII';

3.1 Замер времени выполнения запроса

Вы можете замерить время выполнения запроса, включив его командой \timing

\timing
SELECT DISTINCT(event_type) FROM weather LIMIT 40;

3.2 Data Definition Language (DDL), Data Modification Language (DML) запросы

SQL запросы, связанные с модификацией данных таблиц называют DML (Data Modification Language) запросами. SQL запросы, связанные с модификацией базы данных называют DDL (Data Definition Language) запросами.

Попробуйте в командой строке простой DML запрос

UPDATE weather
SET magnitude = 40
WHERE episode_id = 57676;

Чтобы выйти из консоли введите \q.

4. Соединения таблиц

В реляционных базах данные разбиваются на таблицы так, чтобы избыточность хранимой информации была минимальной, запросы и долгосрочная поддержка были эффективными. Это приводит к необходимости объединять информацию из разных таблиц с помощью механизма JOIN.

В этом разделе мы рассмотрим выдуманную базу данных, созданную для HR отдела для управления человеческими ресурсами. Перед выполнением выполните инициализацию базы данных

\i scripts/hr.sql

\d departments

Схема таблицы Отделы (departments)

Колонка	Связь с
department_id
department_name
location_id	таблицей locations колонкой location_id

\d employees

Схема таблицы Сотрудники (employees)

Колонка	Связь с
employee_id
first_name
last_name
start_date
job_title
salary
manager_id	таблицей employees колонкой employee_id
department_id	таблицей departments колонкой department_id

Каждый сотрудник имеет свой идентификационный номер employee_id. В таблице хранится также важная информация как имя, фамилия, должность и зарплата. Принадлежность к отделу выражается в наличии связи с таблицей departments через поле department_id. Поле manager_id ведёт к менеджеру сотрудника.

4.1 Внутренние соединения (inner join)

Внутреннее соединение является наиболее распространенным и соединяет столбец в одной таблице и со столбцом в другой. В нашей базе данных HR мы хотели бы найти сотрудников и их отделы. Итак, допустим, мы хотим вывести сотрудников и отделы, в которых они числятся:

SELECT e.employee_id, e.first_name, e.last_name, d.department_name 
FROM employees e   
INNER JOIN departments d ON (e.department_id = d.department_id);

Обратите внимание, как мы обращаемся к employee_id, first_name, last_name из таблицы employees и к department_name из таблицы departments. Мы связываем эти два множества информации в части FROM. Обратите внимание, что INNER JOIN и ON зарезервированные слова. В примеры выше мы хотим получить пересечение элементов таблицы employees и departments по полю department_id.

Мы обратились к СУБД на специальном языке и СУБД взяла на себя самую сложную часть, избавив нас от необходимости писать специализированную программу для поиска и выгрузки информации.

4.2 Левые внешние соединения (left outer join)

При использовании механизма LEFT OUTER JOIN из левой таблицы вернуться все строки, и если справа не найдётся соответствия, то в столбцах правой таблицы вместо значений будет присутствовать NULL.

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

\d dependents

Схема таблицы Подчиненные (dependents)

Колонка	Связь с
dependent_id
first_name
last_name
employee_id	таблицей employees колонкой employee_id

Допустим, что кроме отдела сотрудника нам также нужно вывести его подчинённых:

SELECT e.employee_id, e.first_name, e.last_name, d.department_name, dep.first_name || ' ' || dep.last_name AS dependent 
FROM employees e 
  INNER JOIN departments d ON (e.department_id = d.department_id)   
  LEFT OUTER JOIN dependents dep ON (e.employee_id = dep.employee_id);

Этим запросом мы объединяем таблицу сотрудников слева и подчинённых справа. Если у сотрудника нет подчинённых, его строке будет сопоставлена строка с значениями NULL. Мы можем увидеть, чтоб PostgreSQL вернула сотрудника John Smith у которого в подчинении два человека: Spoiled and DoNothing Smith, и сотрудника Howard TheDuck, возможно middle python-программиста, у которого никого в подчинении нет.

Если бы мы использовали INNER JOIN, то исключили бы обычных сотрудников из результатов запроса.

4.3 Правые внешние соединения (right outer join)

В ситуации с RIGHT OUTER JOIN всё происходит наоборот. При отсутствии соответствующей записи слева для записи таблицы указанной справа значениями NULL заполняются столбцы левой таблицы.

Ранее в схеме departments мы видели, что каждый отдел связан с географическим положением через поле location_id таблицы Местоположения (locations).

\d locations

Схема таблицы Местоположения (locations)

Колонка
location_id
street_address
postal_code
city
state_province
country_id

Узнаем, где географически расположены отделы компании

SELECT d.department_name, l.city, l.state_province, l.country_id 
FROM departments d   
  RIGHT OUTER JOIN locations l ON (d.location_id = l.location_id);

Результат нам сообщают, что в городе Effingham штата Индиана США нет отдела. Значит офис был закрыт.

4.4 Соединения на себя (self join)

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

                                     Table "public.employees"
    Column     |          Type          | Collation | Nullable |             Default
---------------+------------------------+-----------+----------+----------------------------------
 employee_id   | integer                |           | not null | generated by default as identity
 first_name    | character varying(20)  |           |          |
 last_name     | character varying(25)  |           | not null |
 start_date    | date                   |           | not null | CURRENT_DATE
 job_title     | character varying(100) |           | not null |
 salary        | numeric(9,2)           |           | not null |
 manager_id    | integer                |           |          |
 department_id | integer                |           |          |

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

Используя соединение на себя мы можем запросить у базы данных список сотрудников и их менеджеров

SELECT e.first_name, e.last_name, e.job_title, format('%s %s', m.first_name, m.last_name) AS manager 
FROM employees e LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id); 

Поздравляем! Вы познакомились с наиболее типичными видами соединений таблиц.

5. Транзакции

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

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

1. Создать пользователя
2. Проверить, что он создан. Если не создан, завершить процесс с ошибкой.
3. Создать аккаунт.
4. Проверить, что он создан. Если не создан, удалить пользователя, завершить процесс с ошибкой.
5. Соединить аккаунт и пользователя.
6. Проверить, что соединение создано. Если не создано, удалить пользователя и аккаунт, завершить процесс с ошибкой.

С механизмом транзакций количество шагов уменьшается в 2 раза:

1. Создать пользователя
2. Создать аккаунт
3. Соединить аккаунт и пользователя

Транзакции позволяют не писать логику возврата в предыдущее состояние, до начала транзакции.

5.1 Простейшие транзакции

Самая простая транзакция состоит из одного запроса на модификацию данны, заключенного между BEGIN и COMMIT.

BEGIN;
INSERT INTO employees (first_name, last_name, start_date, salary, job_title, manager_id, department_id)
VALUES ('Cris', 'Winslett', current_date, 1, 'Director of the Highwire', 2, 2)
RETURNING employee_id;
COMMIT;

Чтобы подтвердить, что данные были успешно внесены в БД выполните запрос

SELECT * FROM employees
WHERE
  first_name = 'Cris' AND
  last_name = 'Winslett';

Однако, если вы решите откатить транзакцию после изменений вводом команды ROLLBACK, то вы увидите другой результат

BEGIN;
INSERT INTO employees (first_name, last_name, start_date, salary, job_title, manager_id, department_id)
VALUES ('Elizabeth', 'Christensen', current_date, 1, 'Master of the Highwire', 2, 2)
RETURNING employee_id;
ROLLBACK;

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

SELECT * FROM employees
WHERE
  first_name = 'Elizabeth' AND
  last_name = 'Christensen';

Однако некоторые изменения всё-таки происходят даже в случае отката. Убедитесь в том, что employee_id монотонно возрастает, выполнив транзакцию с откатом ещё несколько раз. Это происходит, чтобы у каждой транзакции гарантированно было уникальное значение, взятое из последовательности идентификаторов.

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

BEGIN;
  INSERT INTO employees (first_name, last_name)
  VALUES ('Tome', 'Jones')
  RETURNING employee_id;
COMMIT;

5.2 Комплексные транзакции

В реальной жизни транзакции состоят из множества действий.

BEGIN;   
  INSERT INTO employees (first_name, last_name, start_date, salary, job_title, manager_id, department_id) 
  VALUES ('Chris', 'Winslett', current_date, 1, 'Jr Director of the Highwire', 2, 2);   
  INSERT INTO dependents (first_name, last_name, employee_id)  
  VALUES ('oldest', 'kid', (SELECT employee_id FROM employees WHERE first_name = 'Chris' AND last_name = 'Winslett'));   
  INSERT INTO dependents (first_name, last_name, employee_id) 
  VALUES ('youngest', 'kid', (SELECT employee_id FROM employees WHERE first_name = 'Chris' AND last_name = 'Winslett')); 
COMMIT;

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

В следующем запросе попробуем внести null значение в обязательное поле first_name для второго подчинённого. Транзакция не выполнится из-за нарушеного ограничения.

BEGIN;   
  INSERT INTO employees (first_name, last_name, start_date, salary, job_title, manager_id, department_id) 
  VALUES ('Bob', 'Young', current_date, 1, 'Jr Director of the Highwire', 2, 2);   
  INSERT INTO dependents (first_name, last_name, employee_id) 
  VALUES ('oldest', 'kid', (SELECT employee_id FROM employees WHERE first_name = 'Bob' AND last_name = 'Young'));   
  INSERT INTO dependents (first_name, last_name, employee_id) 
  VALUES (null, 'kid', (SELECT employee_id FROM employees WHERE first_name = 'Bob' AND last_name = 'Young')); 
COMMIT;

Проверьте, что Bob Young не добавился.

5.3 Область видимости изменений транзакции

Что произойдёт, если вы запустили транзакцию, а в этом время к данным, которые вы модифицируете в новой транзакции обратился другой пользователь? Изменения в транзакциях невидимы другим транзакциям, пока транзакция производящие измнения не завершились.

В Postgres изменения структуры таблиц также не реализуются (committed) пока транзакция не выполнится успешно. Это уникальное свойство мире баз данных. Проведём два эксперимента - с корректной и некорректной транзакциями.

BEGIN;
  ALTER TABLE employees 
  ADD COLUMN niddle_name VARCHAR(50) DEFAULT NULL;
COMMIT;

Проверьте, что структура таблицы изменилась командой \d employees.

Теперь попробуем транзакцию с ошибкой:

BEGIN; 
  ALTER TABLE employees ADD COLUMN address_line_1 VARCHAR(50) DEFAULT NULL; 
  ALTER TABLE employees ADD COLUMN address_line_2 VARCHAR(50) DEFAULT NULL; 
  ALTER TABLE employees ADD COLUMN city VARCHAR(50) DEFAULT NULL; 
  ALTER TABLE employees ADD COLUMN province VARCHAR(50) DEFAULT NULL; 
  ALTER TABLE employees ADD COLUMN postal_code VARCHAR(50) NOT NULL; 
COMMIT;

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

Такой вид транзакций ещё называют транзакционным язык описания данных (Transactional DDL). Они жизненно важны для миграций баз данных.

5.4 Продвинутые транзакции с вложенными точками отката

Для некоторый специфичный случаев могут потребоваться вложенные транзакции. Postgres позволяет сделать даже больше - делать точки сохранения внутри транзакции. Точка сохранения (SAVEPOINT) предоставляет указатель на который может ссылаться комадна ROLLBACK.

BEGIN;   
  INSERT INTO employees (first_name, last_name, start_date, salary, job_title, manager_id, department_id) 
  VALUES ('Bob', 'Young', current_date, 1, 'Jr Director of the Highwire', 2, 2);   
  SAVEPOINT saved_employee;   
  INSERT INTO dependents (first_name, last_name, employee_id) 
  VALUES ('oldest', 'kid', (SELECT employee_id FROM employees WHERE first_name = 'Bob' AND last_name = 'Young'));   
  INSERT INTO dependents (first_name, last_name, employee_id) 
  VALUES (null, 'kid', (SELECT employee_id FROM employees WHERE first_name = 'Bob' AND last_name = 'Young'));   
  ROLLBACK TO SAVEPOINT saved_employee; 
COMMIT;

Теперь, если вы попробуете найти Bob Young - вы найдёте его, но не найдёте его подчинённых. Что случилось? Эту транзакцию стало возможным выполнить частично из-за использования команды SAVEPOINT.

5.5 Я в транзакции?

Чтобы узнать идентификатор транзакции, в которой вы находитесь, вызовите команду

SELECT txid_current();

Вы можете удивиться, ведь вы не запускали транзакцию. На самом деле Postgres выполняет все команды, даже самые простые в контексте транзакций.

6. Индексы

6.1 B-Tree

Познакомимся с наиболее распространённым индексом - B-Tree индексом - на примере тестовых данных weather.sql.

Попробуем найти всё про снежные бури.

SELECT *
FROM weather
WHERE event_type = 'Winter Storm';

Чтобы проанализировать запрос используйте EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT *
FROM weather
WHERE event_type = 'Winter Storm' limit 2;

Анализ покажет вам время планирования выполнения запроса Planning Time и время выполнения Execution Time.

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on weather  (cost=0.00..115.00 rows=11 width=622) (actual time=0.014..0.311 rows=11 loops=1)
   Filter: ((event_type)::text = 'Winter Storm'::text)
   Rows Removed by Filter: 1189
 Planning Time: 0.066 ms
 Execution Time: 0.332 ms
(5 rows)

Создадим B-Tree индекс для столбца event_type таблицы weather и повторим анализ запроса.

CREATE INDEX idx_weather_event_type ON weather(event_type);

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

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on weather  (cost=4.36..37.56 rows=11 width=622) (actual time=0.029..0.036 rows=11 loops=1)
   Recheck Cond: ((event_type)::text = 'Winter Storm'::text)
   Heap Blocks: exact=6
   ->  Bitmap Index Scan on idx_weather_event_type  (cost=0.00..4.36 rows=11 width=0) (actual time=0.025..0.025 rows=11 loops=1)
         Index Cond: ((event_type)::text = 'Winter Storm'::text)
 Planning Time: 0.234 ms
 Execution Time: 0.057 ms
(7 rows)

6.2 Многоколоночный индекс

Postgres позволяет создавать сложные индексы для нескольких колонок. Как и в случае с одной колонкой, если вы выполняете запрос по нескольких колонкам часто, такой индекс позволит выполнять его эффективнее.

Удалите старый индекс командой DROP INDEX idx_weather_event_type;. Выполним другой запрос - найдём снежные бури, которые привели к потере урожая

EXPLAIN ANALYZE
SELECT *
FROM weather
WHERE event_type = 'Winter Storm' 
  AND damage_crops > '0';

Выдача до создания индекса

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on weather  (cost=0.00..118.00 rows=8 width=622) (actual time=0.011..0.399 rows=11 loops=1)
   Filter: (((damage_crops)::text > '0'::text) AND ((event_type)::text = 'Winter Storm'::text))
   Rows Removed by Filter: 1189
 Planning Time: 0.073 ms
 Execution Time: 0.419 ms
(5 rows)

Создадим индекс

CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);

Выдача после создания индекса

                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on weather  (cost=4.36..29.69 rows=8 width=622) (actual time=0.022..0.029 rows=11 loops=1)
   Recheck Cond: (((event_type)::text = 'Winter Storm'::text) AND ((damage_crops)::text > '0'::text))
   Heap Blocks: exact=6
   ->  Bitmap Index Scan on idx_storm_crop  (cost=0.00..4.36 rows=8 width=0) (actual time=0.018..0.018 rows=11 loops=1)
         Index Cond: (((event_type)::text = 'Winter Storm'::text) AND ((damage_crops)::text > '0'::text))
 Planning Time: 0.203 ms
 Execution Time: 0.048 ms
(7 rows)

Время уменьшилось на порядок.

Вы можете вывести существующие индексы базы данных sql запросом

SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'weather'; 

6.3 Анализ производительности

Как мы увидели, добавление индексов может играет важную роль в производительности вашего приложения. Индексы особенно ценны для больших таблиц.

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

SELECT
  relname,
  100 * idx_scan / (seq_scan + idx_scan) percent_of_time_index_used,
  n_live_tup rows_in_table
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 0
ORDER BY n_live_tup DESC;

Идеальном случае мы хотим видеть 99% для таблиц больших чем 10000 записей. Если вы нашли такую таблицу, где это не так - она хороший кандидат для создания новых индексов.

Также полезной может быть информация о том, сколько блоков индексированных данных читается из кэша Postgres, а сколько с диска.

SELECT    
  sum(idx_blks_read) as idx_read,   
  sum(idx_blks_hit)  as idx_hit,   
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio 
FROM pg_statio_user_indexes;

Упомянем ещё пару хитростей.

Использование команды CREATE INDEX CONCURRENTLY позволит избежать блокировки таблицы.

Параметр maintenance_work_mem определяет объём памяти задействованный под процесс создания индексов и другие фоновые задачи, например vacuum. По умолчанию это значение равно 64 МБ (вы можете найти его из psql в списке select name, setting from pg_settings;). Подумайте о том, чтобы увеличить это значение перед созданием больших индексов.

7. Проблема разбухания базы данных Postgres

У Postgres есть особый способ обработки обновлений или удалений. При удалении данных место на диске не освобождается до тех пор, пока база данных не будет запущена специальный процесс очистки vacuum. Если база данных загружена большим количеством DML запросов на удаление строк, через какое-то время в ней появится много неиспользуемого пространства, что негативно повлияет на производительность, если ничего не сделать.

Попробуйте следующий запрос https://wiki.postgresql.org/wiki/Show_database_bloat, чтобы увидеть находится ли база данных в оптимальном или раздутом (bloat) состоянии.

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta /* very rough approximation, assumes all cols */
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;

Колонки показывающие объём места, занимаемого удалёнными данными wastedbytes и индекс wastedibytes покажут наличие проблемы.

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

SELECT relname, last_vacuum, last_autovacuum 
FROM pg_stat_user_tables;

Очистку также можно запустить вручную утилитой vacuumdb. Попробуйте это сделать и вызвать запрос ещё раз.

Релевантные источники

• Мартин Грабер — Понимание SQL
• https://www.postgresql.org/docs/
• https://theartofpostgresql.com
• https://www.crunchydata.com/developers/tutorials
• https://www.sql-ex.ru/
• https://sqlbolt.com/