company_banner

Трюки с SQL от DBA. Небанальные советы для разработчиков БД

Автор оригинала: Haki Benita
  • Перевод
  • Tutorial

Когда я начинал свою карьеру разработчика, моей первой работой стала DBA (администратор базы данных, АБД). В те годы, ещё до AWS RDS, Azure, Google Cloud и других облачных сервисов, существовало два типа АБД:

  • АБД инфраструктуры отвечали за настройку базы данных, конфигурирование хранилища и заботу о резервных копиях и репликации. После настройки БД инфраструктурный администратор время от времени «настраивал экземпляры», например, уточнял размеры кэшей.
  • АБД приложения получал от АБД инфраструктуры чистую базу и отвечал за её архитектуру: создание таблиц, индексов, ограничений и настройку SQL. АБД приложения также реализовывал ETL-процессы и миграцию данных. Если команды использовали хранимые процедуры, то АБД приложения поддерживал и их.

АБД приложений обычно были частью команд разработки. Они обладали глубокими познаниями по конкретной теме, поэтому обычно работали только над одним-двумя проектами. Инфраструктурные администраторы баз данных обычно входили в ИТ-команду и могли одновременно работать над несколькими проектами.

Я админ базы данных приложения


У меня никогда не было желания возиться с бэкапами или настраивать хранилище (уверен, это увлекательно!). По сей день мне нравится говорить, что я админ БД, который знает, как разрабатывать приложения, а не разработчик, который разбирается в базах данных.

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

Содержание:



Обновляйте лишь то, что нужно обновить


Операция UPDATE потребляет довольно много ресурсов. Для её ускорения лучше всего обновлять только то, что нужно обновить.

Вот пример запроса на нормализацию колонки email:

db=# UPDATE users SET email = lower(email);
UPDATE 1010000
Time: 1583.935 ms (00:01.584)

Выглядит невинно, да? Запрос обновляет адреса почты для 1 010 000 пользователей. Но нужно ли обновлять все строки?

db=# UPDATE users SET email = lower(email)
db-# WHERE email != lower(email);
UPDATE 10000
Time: 299.470 ms

Нужно было обновить всего 10 000 строк. Уменьшив количество обрабатываемых данных, мы снизили длительность исполнения с 1,5 секунд до менее чем 300 мс. Это также сэкономит нам в дальнейшем силы на сопровождение базы данных.


Обновляйте лишь то, что нужно обновить.

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

При больших загрузках отключайте ограничения и индексы


Ограничения — важная часть реляционных баз данных: они сохраняют консистентность и надёжность данных. Но у всего своя цена, и чаще всего расплачиваться приходится при загрузке или обновлении большого количества строк.

Давайте зададим схему небольшого хранилища:

DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    price INT NOT NULL
);
INSERT INTO product (name, price)
    SELECT random()::text, (random() * 1000)::int
    FROM generate_series(0, 10000);

DROP TABLE IF EXISTS customer CASCADE;
CREATE TABLE customer (
    id serial PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO customer (name)
    SELECT random()::text
    FROM generate_series(0, 100000);

DROP TABLE IF EXISTS sale;
CREATE TABLE sale (
    id serial PRIMARY KEY,
    created timestamptz NOT NULL,
    product_id int NOT NULL,
    customer_id int NOT NULL
);

Здесь определяются разные типы ограничений, таких как «not null», а также уникальные ограничения…

Чтобы задать исходную точку, начнём добавлять в таблицу sale внешние ключи

db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk
db-# FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE
Time: 18.413 ms

db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk
db-# FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE
Time: 5.464 ms

db=# CREATE INDEX sale_created_ix ON sale(created);
CREATE INDEX
Time: 12.605 ms

db=# INSERT INTO SALE (created, product_id, customer_id)
db-# SELECT
db-#    now() - interval '1 hour' * random() * 1000,
db-#    (random() * 10000)::int + 1,
db-#    (random() * 100000)::int + 1
db-# FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 15410.234 ms (00:15.410)

После определения ограничений и индексов загрузка в таблицу миллиона строк заняла около 15,4 с.

Теперь сначала загрузим данные в таблицу, и только потом добавим ограничения и индексы:

db=# INSERT INTO SALE (created, product_id, customer_id)
db-# SELECT
db-#    now() - interval '1 hour' * random() * 1000,
db-#    (random() * 10000)::int + 1,
db-#    (random() * 100000)::int + 1
db-# FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 2277.824 ms (00:02.278)

db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk
db-# FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE
Time: 169.193 ms

db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk
db-# FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE
Time: 185.633 ms

db=# CREATE INDEX sale_created_ix ON sale(created);
CREATE INDEX
Time: 484.244 ms

Загрузка прошла гораздо быстрее, 2,27 с. вместо 15,4. Индексы и ограничения создавались после загрузки данных заметно дольше, но весь процесс оказался намного быстрее: 3,1 с. вместо 15,4.

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

Для промежуточных данных используйте UNLOGGED-таблицы


Когда вы меняете данные в PostgreSQL, изменения записываются в журнал с упреждающей записью (write ahead log (WAL)). Он используется для поддержания целостности, быстрой переиндексации в ходе восстановления и поддержки репликации.

Запись в WAL нужна часто, но есть некоторые обстоятельства, при которых вы можете отказаться от WAL ради ускорения процессов. Например, в случае с промежуточными таблицами.

Промежуточными называют одноразовые таблицы, в которых хранятся временные данные, используемые для реализации каких-то процессов. К примеру, в ETL-процессах очень часто загружают данные из CSV-файлов в промежуточные таблицы, очищают информацию, а затем грузят её в целевую таблицу. В таком сценарии промежуточная таблица — одноразовая и не используется в резервных копиях или репликах.


UNLOGGED-таблица.

Промежуточные таблицы, которые не нужно восстанавливать в случае сбоя и которые не нужны в репликах, можно задать как UNLOGGED:

CREATE UNLOGGED TABLE staging_table ( /* table definition */ );

Внимание: прежде чем использовать UNLOGGED, убедитесь, что полностью понимаете все последствия.

Реализуйте процессы целиком с помощью WITH и RETURNING


Допустим, у вас таблица пользователей, и вы обнаружили, что в ней есть дублирующиеся данные:

Table setup
db=# SELECT u.id, u.email, o.id as order_id
FROM orders o JOIN users u ON o.user_id = u.id;

 id |       email       | order_id
----+-------------------+----------
  1 | foo@bar.baz       |        1
  1 | foo@bar.baz       |        2
  2 | me@hakibenita.com |        3
  3 | ME@hakibenita.com |        4
  3 | ME@hakibenita.com |        5

Пользователь haki benita зарегистрирован дважды, с почтой ME@hakibenita.com и me@hakibenita.com. Поскольку мы не нормализуем адреса почты при внесении в таблицу, теперь придётся разобраться с дублями.

Нам нужно:

  1. Определить дубли по адресам, написанным строчными буквами, и связать дублирующихся пользователей друг с другом.
  2. Обновить заказы, чтобы они ссылались только на один из дублей.
  3. Убрать дубли из таблицы.

Связать дублирующихся пользователей можно с помощью промежуточной таблицы:

db=# CREATE UNLOGGED TABLE duplicate_users AS
db-#     SELECT
db-#         lower(email) AS normalized_email,
db-#         min(id) AS convert_to_user,
db-#         array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
db-#     FROM
db-#         users
db-#     GROUP BY
db-#         normalized_email
db-#     HAVING
db-#         count(*) > 1;
CREATE TABLE

db=# SELECT * FROM duplicate_users;
 normalized_email  | convert_to_user | convert_from_users
-------------------+-----------------+--------------------
 me@hakibenita.com |               2 | {3}

В промежуточной таблице содержатся связи между дублями. Если пользователь с нормализованным адресом почты появляется более одного раза, мы присваиваем ему минимальный ID пользователя, в которого свёрнём все дубли. Остальные пользователи хранятся в array column и все ссылки на них будут обновлены.

С помощью промежуточной таблицы обновим ссылки на дубли в таблице orders:

db=# UPDATE
db-#    orders o
db-# SET
db-#    user_id = du.convert_to_user
db-# FROM
db-#    duplicate_users du
db-# WHERE
db-#    o.user_id = ANY(du.convert_from_users);
UPDATE 2

Теперь можно безопасно удалить дубли из users:

db=# DELETE FROM
db-#    users
db-# WHERE
db-#    id IN (
db(#        SELECT unnest(convert_from_users)
db(#        FROM duplicate_users
db(#    );
DELETE 1

Обратите внимание, что для «преобразования» массива мы использовали функцию unnest, которая превращает каждый элемент в строку.

Результат:

db=# SELECT u.id, u.email, o.id as order_id
db-# FROM orders o JOIN users u ON o.user_id = u.id;
 id |       email       | order_id
----+-------------------+----------
  1 | foo@bar.baz       |        1
  1 | foo@bar.baz       |        2
  2 | me@hakibenita.com |        3
  2 | me@hakibenita.com |        4
  2 | me@hakibenita.com |        5

Отлично, все экземпляры пользователя 3 (ME@hakibenita.com) преобразованы в пользователя 2 (me@hakibenita.com).

Можем также проверить, что дубли удалены из таблицы users:

db=# SELECT * FROM users;
 id |       email
----+-------------------
  1 | foo@bar.baz
  2 | me@hakibenita.com

Теперь можно избавиться от промежуточной таблицы:

db=# DROP TABLE duplicate_users;
DROP TABLE

Всё хорошо, но слишком долго и нужна очистка! Есть ли способ получше?

Обобщённые табличные выражения (CTE)


С помощью обобщённых табличных выражений, также известных как выражение WITH, мы можем выполнить всю процедуру с помощью единственного SQL-выражения:

WITH duplicate_users AS (
    SELECT
        min(id) AS convert_to_user,
        array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
    FROM
        users
    GROUP BY
        lower(email)
    HAVING
        count(*) > 1
),

update_orders_of_duplicate_users AS (
    UPDATE
        orders o
    SET
        user_id = du.convert_to_user
    FROM
        duplicate_users du
    WHERE
        o.user_id = ANY(du.convert_from_users)
)

DELETE FROM
    users
WHERE
    id IN (
        SELECT
            unnest(convert_from_users)
        FROM
            duplicate_users
    );

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

Возврат результатов из CTE


Одно из преимуществ исполнения DML внутри выражения WITH заключается в том, что вы можете вернуть из него данные с помощью ключевого слова RETURNING. Допустим, нам нужен отчёт о количестве обновлённых и удалённых строк:

WITH duplicate_users AS (
    SELECT
        min(id) AS convert_to_user,
        array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
    FROM
        users
    GROUP BY
        lower(email)
    HAVING
        count(*) > 1
),

update_orders_of_duplicate_users AS (
    UPDATE
        orders o
    SET
        user_id = du.convert_to_user
    FROM
        duplicate_users du
    WHERE
        o.user_id = ANY(du.convert_from_users)
    RETURNING o.id
),

delete_duplicate_user AS (
    DELETE FROM
        users
    WHERE
        id IN (
            SELECT unnest(convert_from_users)
            FROM duplicate_users
        )
        RETURNING id
)

SELECT
    (SELECT count(*) FROM update_orders_of_duplicate_users) AS orders_updated,
    (SELECT count(*) FROM delete_duplicate_user) AS users_deleted
;

Результат:

orders_updated | users_deleted
----------------+---------------
              2 |             1

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

Внимание: Читатель Reddit указал мне на возможное непредсказуемое поведение исполнения DML в обобщённых табличных выражениях:

Подвыражения в WITH исполняются конкурентно друг с другом и с основным запросом. Поэтому при использовании в WITH модифицирующих данные выражений фактический порядок обновлений будет непредсказуемым

Это означает, что вы не можете полагаться на порядок исполнения независимых подвыражений. Получается, что если между ними есть зависимость, как в примере выше, вы можете полагаться на исполнение зависимых подвыражение до их использования.

В колонках с низкой избирательностью избегайте индексов


Допустим, у вас есть процесс регистрации, при котором пользователь входит по адресу почты. Чтобы активировать аккаунт, нужно верифицировать почту. Таблица может выглядеть так:

db=# CREATE TABLE users (
db-#    id serial,
db-#    username text,
db-#    activated boolean
db-#);
CREATE TABLE

Большинство ваших пользователей — граждане сознательные, они регистрируются с корректным почтовым адресом и немедленно активируют аккаунт. Давайте заполним таблицу пользовательскими данными, и будем считать, что 90 % пользователей активировано:

db=# INSERT INTO users (username, activated)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     random() < 0.9 AS activated
db-# FROM
db-#     generate_series(1, 1000000);
INSERT 0 1000000

db=# SELECT activated, count(*) FROM users GROUP BY activated;
 activated | count
-----------+--------
 f         | 102567
 t         | 897433

db=# VACUUM ANALYZE users;
VACUUM

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

db=# CREATE INDEX users_activated_ix ON users(activated);
CREATE INDEX

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

db=# EXPLAIN SELECT * FROM users WHERE NOT activated;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=1923.32..11282.99 rows=102567 width=38)
   Filter: (NOT activated)
   ->  Bitmap Index Scan on users_activated_ix  (cost=0.00..1897.68 rows=102567 width=0)
         Index Cond: (activated = false)

База решила, что фильтр выдаст 102 567 позиций, примерно 10 % таблицы. Это согласуется с загруженными нами данными, так что таблица хорошо справилась.

Однако если мы запросим количество активированных пользователей, то обнаружим, что база решила не использовать индекс:

db=# EXPLAIN SELECT * FROM users WHERE activated;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on users  (cost=0.00..18334.00 rows=897433 width=38)
   Filter: activated

Многих разработчиков сбивает с толку, когда база данных не использует индекс. Объяснить, почему она так делает, можно следующим образом: если бы вам нужно было прочитать всю таблицу, вы воспользовались бы индексом?

Вероятно, нет, зачем это нужно? Чтение с диска — операция дорогая, поэтому вы захотите читать как можно меньше. Например, если таблица размером 10 Мб, а индекс размером 1 Мб, то для считывания всей таблицы придётся считать с диска 10 Мб. А если добавить индекс, то получится 11 Мб. Это расточительно.

Давайте теперь посмотрим на статистику, которую PostgreSQL собрал по нашей таблице:

db=# SELECT attname, n_distinct, most_common_vals, most_common_freqs
db-# FROM pg_stats
db-# WHERE tablename = 'users' AND attname='activated';
------------------+------------------------
attname           | activated
n_distinct        | 2
most_common_vals  | {t,f}
most_common_freqs | {0.89743334,0.10256667}

Когда PostgreSQL проанализировал таблицу, он выяснил, что в колонке activated есть два разных значения. Значение t в колонке most_common_vals соответствует частоте 0.89743334 в колонке most_common_freqs, а значение f соответствует частоте 0.10256667. После анализа таблицы база данных определила, что 89,74 % записей — это активированные пользователи, а остальные 10,26 % — неактивированные.

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


Индекс для колонок с низкой и высокой избирательностью.

Используйте частичные индексы


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

Когда мы запросили количество активных пользователей, база не использовала индекс. А когда запросили количество неактивированных, база использовала индекс.

Возникает вопрос: если база не собирается пользоваться индексом для отфильтровывания активных пользователей, зачем нам индексировать их в первую очередь?

Прежде чем ответить на это вопрос, давайте посмотрим на вес полного индекса по колонке activated:

db=# \di+ users_activated_ix

 Schema |      Name          | Type  | Owner | Table | Size
--------+--------------------+-------+-------+-------+------
 public | users_activated_ix | index | haki  | users | 21 MB

Индекс весит 21 Мб. Просто для справки: таблица с пользователями занимает 65 Мб. То есть вес индекса ~32 % веса базы. При этом мы знаем, что ~90 % содержимого индекса вряд ли будет использоваться.

В PostgreSQL можно создавать индекс только для части таблицы — так называемый частичный индекс:

db=# CREATE INDEX users_unactivated_partial_ix ON users(id)
db-# WHERE not activated;
CREATE INDEX

С помощью выражения WHERE мы ограничиваем охватываемые индексом строки. Давайте проверим, сработает ли:

db=# EXPLAIN SELECT * FROM users WHERE not activated;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Index Scan using users_unactivated_partial_ix on users  (cost=0.29..3493.60 rows=102567 width=38)

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

У такого подхода есть ещё одно преимущество:

db=# \di+ users_unactivated_partial_ix
                                 List of relations
 Schema |           Name               | Type  | Owner | Table |  Size
--------+------------------------------+-------+-------+-------+---------
 public | users_unactivated_partial_ix | index | haki  | users | 2216 kB

Полный индекс по колонке весит 21 Мб, а частичный — всего 2,2 Мб. То есть 10 %, что соответствует доле неактивированных пользователей в таблице.

Всегда загружайте отсортированные данные


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

Допустим, у вас есть огромная таблица с конкретными продажами:

db=# CREATE TABLE sale_fact (id serial, username text, sold_at date);
CREATE TABLE

Каждую ночь в ходе ETL-процесса вы загружаете данные в таблицу:

db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000);
INSERT 0 100000

db=# VACUUM ANALYZE sale_fact;
VACUUM

Чтобы сымитировать загрузку, используем случайные данные. Вставили 100 тыс. строк со случайными именами, а даты продаж за период с 1 января 2020 и на два года вперёд.

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

db=# CREATE INDEX sale_fact_sold_at_ix ON sale_fact(sold_at);
CREATE INDEX

Взглянем на план исполнения запроса на извлечение всех продаж в июне 2020:

db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';

                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=108.30..1107.69 rows=4293 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Heap Blocks: exact=927
   ->  Bitmap Index Scan on sale_fact_sold_at_ix  (cost=0.00..107.22 rows=4293 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Planning Time: 0.191 ms
 Execution Time: 5.906 ms

Прогнав запрос несколько раз, чтобы прогреть кэш, длительность исполнения стабилизировалась на уровне 6 мс.

Сканирование по битовой карте (Bitmap Scan)


В плане исполнения мы видим, что база использовала сканирование по битовой карте. Оно проходит в два этапа:

  • Построение битовой карты (Bitmap Index Scan): база проходит по всему индексу sale_fact_sold_at_ix и находит все страницы таблицы, содержащие релевантные строки.
  • Сканирование по битовой карте (Bitmap Heap Scan): база считывает страницы, содержащие релевантные строки, и находит те из них, что удовлетворяют условию.

Страницы могут содержать много строк. На первом этапе индекс используется для поиска страниц. На втором этапе ищутся строки в страницах, отсюда следует операция Recheck Cond в плане исполнения.

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

Индексное сканирование (Index Scan)


Внесём небольшое изменение в загрузку данных.

db=# TRUNCATE sale_fact;
TRUNCATE TABLE

db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000)
db-# ORDER BY sold_at;
INSERT 0 100000

db=# VACUUM ANALYZE sale_fact;
VACUUM

На этот раз мы загрузили данные, отсортированные по sold_at.

Теперь план исполнения того же запроса выглядит так:

db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';

                           QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using sale_fact_sold_at_ix on sale_fact (cost=0.29..184.73 rows=4272 width=41)
   Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Planning Time: 0.145 ms
 Execution Time: 2.294 ms

После нескольких прогонов длительность исполнения стабилизировалась на уровне 2,3 мс. Мы получили устойчивую экономию примерно в 60 %.

Также мы видим, что в этот раз база не стала использовать сканирование по битовой карте, а применила «обычное» индексное сканирование. Почему?

Корреляция


Когда база анализирует таблицу, она собирает всю возможную статистику. Одним из параметров является корреляция:

Статистическая корреляция между физическим порядком строк и логическим порядком значений в колонках. Если значение около -1 или +1, индексное сканирование по колонке считается выгоднее, чем когда значение корреляции около 0, поскольку снижается количество случайных обращений к диску.

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


Корреляция = 1.

Если корреляция равна 1 или около того, это означает, что страницы хранятся на диске примерно в том же порядке, что и строки в таблице. Такое встречается очень часто. Например, у автоинкрементирующихся ID корреляция обычно близка к 1. У колонок с датами и временными метками, которые показывают, когда были созданы строки, корреляция тоже близка к 1.

Если корреляция равна -1, страницы отсортированы в обратном порядке относительно колонок.


Корреляция ~ 0.

Если корреляция близка к 0, это означает, что значения в колонке не коррелируют или почти не коррелируют с порядком страниц в таблице.

Вернёмся к sale_fact. Когда мы загрузили данные в таблицу без предварительной сортировки, корреляции были такими:

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db=# WHERE tablename = 'sale_fact';

 tablename | attname  | correlation
-----------+----------+--------------
 sale      | id       |            1
 sale      | username | -0.005344716
 sale      | sold_at  | -0.011389783

У автоматически сгенерированного ID колонки корреляция равна 1. У колонки sold_at корреляция очень низкая: последовательные значения разбросаны по всей таблице.

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

tablename | attname  |  correlation
-----------+----------+----------------
 sale_fact | id       |              1
 sale_fact | username | -0.00041992788
 sale_fact | sold_at  |              1

Теперь корреляция sold_at равна 1.

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

  • Когда корреляция была равна 1, база определила, что строки запрошенного диапазона, вероятно, будут в последовательных страницах. Тогда для чтения нескольких страниц лучше использовать индексное сканирование.
  • Когда корреляция была близка к 0, база определила, что строки запрошенного диапазона, вероятно, будут разбросаны по всей таблице. Тогда целесообразно использовать сканирование по битовой карте тех страниц, в которых есть нужные строки, и только потом извлекать их с применением условия.

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

Команда CLUSTER


Другой способ «сортировки таблицы на диске» по конкретному индексу заключается в использовании команды CLUSTER.

Например:

db=# TRUNCATE sale_fact;
TRUNCATE TABLE

-- Insert rows without sorting
db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000)
INSERT 0 100000

db=# ANALYZE sale_fact;
ANALYZE

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db-# WHERE tablename = 'sale_fact';

  tablename | attname  |  correlation
-----------+-----------+----------------
 sale_fact | sold_at   | -5.9702674e-05
 sale_fact | id        |              1
 sale_fact | username  |    0.010033822

Мы загрузили данные в таблицу в случайном порядке, поэтому корреляция sold_at близка к нулю.

Чтобы «перекомпоновать» таблицу по sold_at, используем команду CLUSTER для сортировки таблицы на диске в соответствии с индексом sale_fact_sold_at_ix:

db=# CLUSTER sale_fact USING sale_fact_sold_at_ix;
CLUSTER

db=# ANALYZE sale_fact;
ANALYZE

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db-# WHERE tablename = 'sale_fact';

 tablename | attname  | correlation
-----------+----------+--------------
 sale_fact | sold_at  |            1
 sale_fact | id       | -0.002239401
 sale_fact | username |  0.013389298

После кластеризации таблицы корреляция sold_at стала равна 1.


Команда CLUSTER.

Что нужно отметить:

  • Кластеризация таблицы по конкретной колонке может повлиять на корреляцию другой колонки. Например, взгляните на корреляцию ID после кластеризации по sold_at.
  • CLUSTER — тяжёлая и блокирующая операция, поэтому не применяйте её к живой таблице.

По этим причинам лучше вставлять уже отсортированные данные и не полагаться на CLUSTER.

Колонки с высокой корреляцией индексируйте с помощью BRIN


Когда речь заходит об индексах, многие разработчики думают о В-деревьях. Но PostgreSQL предлагает и другие типы индексов, например, BRIN:

BRIN спроектирован для работы с очень большими таблицами, в которых некоторые колонки обладают естественной корреляцией со своим физическим местоположением внутри таблицы


BRIN означает Block Range Index. Согласно документации, BRIN лучше всего работает с колонками, имеющими высокую корреляцию. Как мы уже видели в предыдущих главах, автоинкрементирующиеся ID и временные метки естественным образом коррелируют с физической структурой таблицы, поэтому для них выгоднее использовать BRIN.

При определённых условиях BRIN может обеспечить лучшее «соотношение цены и качества» с точки зрения размера и производительности по сравнению с аналогичным индексом типа B-дерево.


BRIN.

BRIN диапазон значений в пределах нескольких соседних страниц в таблице. Допустим, у нас в колонке есть такие значения, каждое в отдельной странице:

1, 2, 3, 4, 5, 6, 7, 8, 9

BRIN работает с диапазонами соседних страниц. Если задать три смежные страницы, индекс разделит таблицу на диапазоны:

[1,2,3], [4,5,6], [7,8,9]

Для каждого диапазона BRIN хранит минимальное и максимальное значение:

[1–3], [4–6], [7–9]

Давайте с помощью этого индекса поищем значение 5:

  • [1–3] — здесь его точно нет.
  • [4–6] — может быть здесь.
  • [7–9] — здесь его точно нет.

С помощью BRIN мы ограничили зону поиска блоком 4–6.

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

[2,9,5], [1,4,7], [3,8,6]

Индексирование трёх соседних блоков даст нам такие диапазоны:

[2–9], [1–7], [3–8]

Поищем значение 5:

  • [2–9] — может быть здесь.
  • [1–7] — может быть здесь.
  • [3–8] — может быть здесь.

В этом случае индекс вообще не сужает область поиска, поэтому он бесполезен.

Разбираемся с pages_per_range


Количество смежных страниц определяется параметром pages_per_range. Количество страниц в диапазоне влияет на размер и точность BRIN:

  • Большое значение pages_per_range даст маленький и менее точный индекс.
  • Маленькое значение pages_per_range даст большой и более точный индекс.

По умолчанию значение pages_per_range равно 128.


BRIN с более низким значением pages_per_range.

Для иллюстрации создадим BRIN с диапазонами по две страницы и поищем значение 5:

  • [1–2] — здесь его точно нет.
  • [3–4] — здесь его точно нет.
  • [5–6] — может быть здесь.
  • [7–8] — здесь его точно нет.
  • [9] — здесь его точно нет.

При двухстраничном диапазоне мы можем ограничить зону поиска блоками 5 и 6. Если диапазон будет трёхстраничным, индекс ограничит зону поиска блоками 4, 5 и 6.

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

Создаём BRIN


Возьмём таблицу sales_fact и создадим BRIN по колонке sold_at:

db=# CREATE INDEX sale_fact_sold_at_bix ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 128);
CREATE INDEX

По умолчанию значение pages_per_range = 128.

Теперь запросим период дат продаж:

db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                    QUERY PLAN
--------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=13.11..1135.61 rows=4319 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Rows Removed by Index Recheck: 23130
   Heap Blocks: lossy=256
   ->  Bitmap Index Scan on sale_fact_sold_at_bix  (cost=0.00..12.03 rows=12500 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Execution Time: 8.877 ms

База с помощью BRIN получила период дат, но в этом ничего интересного…

Оптимизируем pages_per_range


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

db=# CREATE INDEX sale_fact_sold_at_bix64 ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 64);
CREATE INDEX

db=# EXPLAIN (ANALYZE)
db- SELECT *
db- FROM sale_fact
db- WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=13.10..1048.10 rows=4319 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Rows Removed by Index Recheck: 9434
   Heap Blocks: lossy=128
   ->  Bitmap Index Scan on sale_fact_sold_at_bix64  (cost=0.00..12.02 rows=6667 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Execution Time: 5.491 ms

При 64 страницах в диапазоне база удалила меньше строк, найденных с помощью индекса — 9 434. Значит, ей пришлось делать меньше операций ввода-вывода, а запрос выполнился чуть быстрее, за ~5,5 мс вместо ~8,9.

Протестируем индекс с разными значениями pages_per_range:

pages_per_range Убрано строк при перепроверке индекса
128 23 130
64 9 434
8 874
4 446
2 446

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

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

Оценка размера индекса


Другим важным преимуществом BRIN является его размер. В предыдущих главах мы для поля sold_at создали индекс на основе В-дерева. Его размер был 2 224 Кб. А размер BRIN с параметром pages_per_range=128 всего 48 Кб: в 46 раз меньше.

Schema |         Name          | Type  | Owner |   Table   | Size
--------+-----------------------+-------+-------+-----------+-------
 public | sale_fact_sold_at_bix | index | haki  | sale_fact | 48 kB
 public | sale_fact_sold_at_ix  | index | haki  | sale_fact | 2224 kB

На размер BRIN также влияет pages_per_range. К примеру, BRIN с pages_per_range=2 весит 56 Кб, чуть больше 48 Кб.

Делайте индексы «невидимыми»


В PostgreSQL есть классная фича transactional DDL. За годы работы с Oracle я привык в конце транзакций использовать такие DDL-команды, как CREATE, DROP и ALTER. Но в PostgreSQL выполнять DDL-команды можно внутри транзакции, а изменения будут применены только после коммита транзакции.

Недавно я обнаружил, что использование транзакционного DDL может сделать индексы невидимыми! Это полезно, когда хочется увидеть план исполнения без индексов.

Например, в таблице sale_fact мы создали индекс по колонке sold_at. План исполнения запроса на извлечение июльских продаж выглядит так:

db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using sale_fact_sold_at_ix on sale_fact  (cost=0.42..182.80 rows=4319 width=41)
   Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))P

Чтобы увидеть, как выглядел бы план, если бы индекса sale_fact_sold_at_ix не было, можно поместить индекс внутрь транзакции и немедленно откатиться:

db=# BEGIN;
BEGIN

db=# DROP INDEX sale_fact_sold_at_ix;
DROP INDEX

db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                   QUERY PLAN
---------------------------------------------------------------------------------

 Seq Scan on sale_fact  (cost=0.00..2435.00 rows=4319 width=41)
   Filter: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))

db=# ROLLBACK;
ROLLBACK

Сначала начнём транзакцию с помощью BEGIN. Затем дропнем индекс и сгенерируем план исполнения. Обратите внимание, что план теперь использует полное сканирование таблицы, словно индекса не существует. В этот момент транзакция всё ещё выполняется, поэтому индекс пока не дропнут. Для завершения транзакции без дропа индекса откатим её с помощью команды ROLLBACK.

Проверим, что индекс ещё существует:

db=# \di+ sale_fact_sold_at_ix
                                 List of relations
 Schema |         Name         | Type  | Owner |   Table   |  Size
--------+----------------------+-------+-------+-----------+---------
 public | sale_fact_sold_at_ix | index | haki  | sale_fact | 2224 kB

Другие базы, которые не поддерживают транзакционный DDL, позволяют достичь цели иначе. Например, Oracle позволяет вам пометить индекс как невидимый, и тогда оптимизатор будет его игнорировать.

Внимание: если дропнуть индекс внутри транзакции, это приведёт к блокировке конкурентных операций SELECT, INSERT, UPDATE и DELETE в таблице, пока транзакция будет активна. Осторожно применяйте в тестовых средах и избегайте применения в эксплуатационных базах.

Не планируйте начало длительных процессов на начало любого часа


Инвесторы знают, что могут происходить странные события, когда цена акций достигает красивых круглых значений, например, 10$, 100$, 1000$. Вот что об этом пишут:

[...] цена активов может непредсказуемо меняться, пересекая круглые значения вроде $50 или $100 за акцию. Многие неопытные трейдеры любят покупать или продавать активы, когда цена достигает круглых чисел, потому что им кажется, что это справедливые цены.

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


Типичная ночная нагрузка на систему.

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

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

Заключение


В статье приведены советы разной степени очевидности на основе моего опыта. Некоторые из них легко внедрить, некоторые требуют глубокого понимания принципов работы баз данных. Базы — это каркас большинства современных систем, так что время, потраченное на изучение их работы, будет хорошим вложением для любого разработчика!
Mail.ru Group
Строим Интернет

Комментарии 38

    –2
    Но нужно ли обновлять все строки?

    Шоке. Это бессмысленный вопрос, так как нужно или нет определяется бизнес задачей исключительно.
    С тем же успехом можно вообще убрать условие обновления, и посмотреть, пропадет ли scan/seek перед обновлением. Если не нужно будет искать записи для обновления, то это сильно ускорит выполнение.


    Хорошо бы добавить в заголовок или теги, что почти все советы касаются PostgreSQL и не применимы к другим СУБД.

      +3
      Вредный совет детектед :)
      2. При больших загрузках отключайте ограничения и индексы
      Отключили ограничения — лишились минимальной гарантии что данные в миграции правильно были переложены из одного места в другое.
        0

        Данные лучше проверить на ограничения ДО обновления, по-моему, это очевидно.

          +3
          Вредный, не вредный, а downtime с недели до суток снизить вполне может (был в практике такой случай). Разумеется, после загрузки, все индексы надо восстановить, а констрейнты включить. А если они не включатся (что тоже часто бывает), разбираться почему. Никто не обещал, что будет легко.
            +6

            Почему лишились? При включении ограничений они все равно будут проверены.

              0
              И например окажется что не так перекладывались данные в миграции, например появлялись дубли или два взаимоисключающих поля в состоянии true, да мало ли что еще может быть. Суть в том что с включёнными ограничениями вы про это узнали во время миграции и просто упала вся транзакция с миграцией. И соответственно зная ограничение на котором упала транзакция, можно выбрать записи с базы для изучения и для доработки миграции.

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

              Миграции с миллионами данных которые занимают недели/сутки делают по другому. Через дополнительные таблицы, через дополнительные базы, через файлы, короче вообще по другому, и там тоже нет вопроса с отключением ограничений.
                +1
                Нужно начинать с того какие данные мы льем.
                Если это OLTP там самое дешевое создать ограничения на уровне БД, и, почти всегда, этого достаточно. Хотя часто на обработку ошибок пользователей здесь работает клиентское приложение — меньше грузим сервер при массовом вводе данных.
                Если это ETL из «чужого» нам источника, то использование ограничений это скорее всего малоэффективно, т.к. во первых тормозит, а во вторых работает только на отказ, и не рассматривает варианты — здесь лучше будет работать приложение работающее через промежуточную таблицу и способное скорректировать некоторые данные, хотя конечно бывает что нам достаточно получить отказ и сказать поставщику — исправляйте.
                А при отлаженном ETL из знакомых нам источников данных в DWH, зачастую вообще нет никаких ограничений, т.к. они реально тормозят потоки, а вероятность случайной ошибки крайне низка. Индексы при этом действительно отключают и по завершению потока включают. т.к. это гораздо более эффективно. А если вы все таки получили ошибку по факту заливки — значит поток что-то не учитывает, но это редкая ситуация, или на источнике провели изменения о которых вы не знаете, всякое бывает, и для отлова таких ошибок создают DQC.
                  0
                  Все может оказаться. Но когда речь идёт о многогигабайтных заливках — выбора особого нет. Отключаем, заливаем, пытаемся включить, разгребаем мусор. Ну и данные тоже полезно подготавливать, чтобы потом проблем было меньше. И кстати, когда полдня лил и вдруг «упала вся транзакция» и пошёл откат на пару часов — тоже радости мало.
                    0
                    вот про откат долгой транзакции +100500… это бывает очень жестоко.
              0

              Эээ, Вы же когда обновляете знаете ЧТО обновляете и к какому виду хотите это привести, соответственно, что мешает в условие "бизнес-задачи" включить только те данные, которые НУЖНО обновить?


              К другим БД тоже применимы, но есть ньюансы.

                –3

                Именно. Задача прямо говорит, что надо обновить. В статье в примере говорится, что чем меньше обновить тем быстрее будет (если я правильно прочитал). Это совершенно корректно, но не применимо, так как в большинстве случаев нет выбора обновлять меньше или больше — надо обновлять столько, сколько требуется.


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


                Опять же, как уже сказали про большие загрузки — если речь идёт про сервис базы или разработку, то конечно быстрее залить, а потом построить индекс онлайн. (Хотя опять же это делается редко, можно и подождать). Но если это рутина, то такое дергание индексов и ограничений может аукнуться производительностью.

                  –1

                  Ну, так задача DBA, если архитектор просмотрел, именно в том и состоит, чтобы оптимизировать процесс, разве нет? Я видел и такие "бизнес-задачи", как логировать ВСЕ действия пользователя по триггеру, которые решались влоб, с гигабайтами логов за час, когда изменялось ОДНО поле, а логировались все пятьсот с неизменными данными.


                  Принципиально другие — согласен, но концепция даёт пищу для размышлений, вернее, для гугла и всё встаёт на свои места, заодно и теория подтягивается.


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

                +3
                Если не нужно будет искать записи для обновления, то это сильно ускорит выполнение.

                Ускорит только этап поиска (и то не всегда), но замедлит этап обновления, из-за большего количества ненужной работы. И последнее намного перевесит первое.


                почти все советы касаются PostgreSQL и не применимы к другим СУБД.

                Что именно, по-вашему, не применимо к другим СУБД (за исключением синтаксиса)?

                  0
                  Что именно, по-вашему, не применимо к другим СУБД (за исключением синтаксиса)?
                  Для промежуточных данных используйте UNLOGGED-таблицы
                  WITH и RETURNING
                  BRIN
                  Делайте индексы «невидимыми»

                  Это не синтаксис, это другие механизмы.

                  Ускорит только этап поиска (и то не всегда), но замедлит этап обновления, из-за большего количества ненужной работы
                  Конечно. Но обычно вы не можете выбирать сколько записей вы вы обновляете — вся работа «нужная». «Требуется сделать емейлы в таблице капитализированными. А давайте только половину из них сделаем, ради ускорения!» — это имеет мало смысла.
                    0
                    Для промежуточных данных используйте UNLOGGED-таблицы

                    Этот механизм есть в других СУБД, название может отличаться. Этот совет применим везде, где механизм доступен.


                    Делайте индексы «невидимыми»

                    Транзакционный DDL есть в других СУБД.


                    WITH и RETURNING

                    Тут согласен. Механизмы для возврата результата из DML есть в других СУБД, но они сильно отличаются.


                    BRIN

                    Наверное, совет должен звучать как "Изучите доступные типы индексов и используйте наиболее подходящий в конкретной ситуации.

                      +1
                      Но обычно вы не можете выбирать сколько записей вы вы обновляете — вся работа «нужная». «Требуется сделать емейлы в таблице капитализированными. А давайте только половину из них сделаем, ради ускорения!» — это имеет мало смысла.

                      Не передергивайте. Задача "требуется сделать так, чтобы все емейлы в таблице были в нижнем регистре". Ее можно решить несколькими способами, более или менее эффективными. Выбирать более эффективный имеет смысл.

                        0

                        Либо я не понял пример с обновлением, либо вы. В примере сказано, что обновление это тяжёлая операция, поэтому если обновлять не все записи, а некоторые, то это производительнее, чем обновлять все. По мне это капитанщина без смысла, так как понятно, что меньше = быстрее, но это решение не удовлетворяет условию задачи.
                        Возможно автор имел ввиду, что надо избегать обновлений, но это невозможно, мягко говоря. Возможно автор хотел сказать, что надо обновлять порциями, но это опять же зависит от задачи и конкретное решение зависит от множества факторов, начиная от объемов данных, заканчивая сервисными интервалами базы, критичностью данных и так далее и тому подобное. Нельзя так просто взять и обновить только часть записей потому, что это быстрее.

                          +3
                          по-моему пример в статье очевидный, обновлять там, только где это сработает… Действительно, зачем дергать лишние 99% строк
                            0

                            Я думаю, все-таки вы не поняли. Всего в таблице миллион емейлов, из них только 10 тыс. не в нижнем регистре. Остальные менять нет необходимости. Но можно и поменять, хуже не будет, конечный результат тот же. Только медленнее.


                            Это как перерисовывать весь экран при перемещении одного окна.

                              +1
                              My bad. Вывод постгре меня смутил и; не заметил. Я подумал, что UPDATE 10000 это что то вроде limit или как update top (...) в sql server, а это просто диагностический вывод.
                              Конечно же лучше обновить только то, что нужно обновить.
                    +2
                    concurrently это не «конкурентно» :)
                      +4

                      Советы легко могут превратиться в антисоветы в зависимости от способа использования субд...

                        0

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

                          +1

                          Исправьте заголовок — это советы только для СУБД Postgres. Либо пишите реальные советы, применимые для всех. А то слишком громкий заголовок, а пользы никакой для тех кто не работает с Postgres

                            0

                            Как человек, который работает с БД далеко не первый год, сильно плюсую. Лишь в середине статьи упоминается, что речь про PostgreSQL.
                            Большинство советов для применимы только для PostgreSQL и отчасти для Greenplum. Возьмите другую БД где индексы отсутствуют вовсе / работают по-другому; где нет такого понятия как UNLOGGED таблицы; где UPDATE отсутствует вовсе, либо работает очень быстро и т.д. Список можно продолжать. Во всех этих случаях статья станет бесполезна чуть менее, чем полностью.

                              +1
                              Не согласен насчет полезности. Часто бывает полезно выйти из своего мирка и посмотреть что как у других. Пусть даже примеры кода не применимы, идеи могут быть важны. Эта статья почти месяц пролежала у меня в закладках, но я счастлив, что прочитал ее прежде чем закончил миграцию данных из MS SQL в Salesforce (обратите внимание, Postgres нет и в помине). В старой CRM данные хранились годами, и именно в хронологическом порядке их будут выбирать в новой в большинстве случаев, применяя фильтры по годам. Я только сейчас осознал, что при записи важно сохранить хронологический порядок записей.
                              –1

                              Надеюсь во втором пункте была речь про временное снятие ограничений и их возврат после крупной вставки? Или все же речь про полный отказ от FK?

                                +5
                                Чтение с диска — операция дорогая, поэтому вы захотите читать как можно меньше. Например, если таблица размером 10 Мб, а индекс размером 1 Мб, то для считывания всей таблица придётся считать с диска 10 Мб. А если добавить индекс, то получится 11 Мб. Это расточительно.


                                Жесть какая. Считать 1 МБ — это полная фигня по сравнению с тем, что нужно будет сделать ~900 000 лукапов, чтобы добраться от индекса к данным. Именно поэтому индекс в данном случае использоваться не будет.

                                P.S. И перевод ужасно корявый. IMHO.
                                  –3
                                  В конкретном примере сработает

                                  db=# UPDATE users SET email = lower(email)
                                  db-# WHERE email != lower(email);


                                  Но так писать — закладывать потенциальные проблемы. Предпочитаю
                                  db=# UPDATE users SET email = lower(email)
                                  db-# WHERE (email) is distinct from (lower(email));

                                    0
                                    Возможно, но ведь тут такая проверка на null ни к чему
                                    0
                                    совет отключать индексы при больших загрузках это еще посчитать надо
                                    — зальешь 50 кк строк без индексов, а дальше что?
                                    А дальше — включай все индексы = полная перестройка — и где профит по времени и производительности?
                                    А если таблица 3 ярда строк — а ты влил 50 кк — перестраивать индексы будет дороже

                                    — Ограничения и так всегда отключены на работающих БД, а там где не отключены сделано специально для проверки корректности данных

                                    — Пометьте пожалуйста что это для Postgres
                                      +4
                                      Всё давно посчитано и проверено. Про заливку которая с индексами шла неделю, а без них выполнилась за сутки я писал выше. Индексы после этого строились несколько часов. Индексы гораздо быстрее строятся по уже залитым данным.
                                        +1

                                        Подтверждаю, тоже имел подобный опыт и не раз.

                                        • НЛО прилетело и опубликовало эту надпись здесь
                                            +2
                                            А как именно ложили данные в базу? single row insert, multi row insert или copy?
                                            +1
                                            А если таблица 3 ярда строк

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

                                            ну знаете, это на ваших БД может и так, но для начинающего разработчика это не очевидно, и часто люди после проектирования OLTP с теми же подходами садятся за DWH.
                                            Пометьте пожалуйста что это для Postgres

                                            Как разраб Oracle могу утверждать, что не только для Postgres, а для продвинутых реляционных БД. Есть конечно «фичи» типа BRIN, но это частности.
                                            А вот пометку что примеры приведены в синтаксисе Postgres можно.
                                              0
                                              Как разраб Oracle могу утверждать, что не только для Postgres, а для продвинутых реляционных БД.

                                              скорее это показывает, что в своём развитии postgre больше всего ориентировался на oracle.

                                                +1

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

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

                                          Самое читаемое