Не найдётся ли у вас оперативной памяти, мистер Нейман?

Из контекста ясно

Давайте скрин или ссылку по которой это ясно не только лично Вам.

Я писал

Какая разница? Грубость Вы допуcтили тут. Вот в этом обсуждении. И если Вы, когда писали в IBM, тоже употребляли слова "быдлоиндус" и "безграмотно", то удивительно, что Вам вообще ответили. Или Вы грубите только за глаза?

Где я утверждаю, что я непогрешим?

3) А где я это утверждал? Я лишь ответил на вопрос:

А если и д'Артаньян, то что с того?

Не найдётся ли у вас оперативной памяти, мистер Нейман?

1) Вы утверждаете

отражает единственную существенную разницу между гарвардской архитектурой и фон-неймановской

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

2) Вы именно грубо отозвались о человеке за глаза. Вот если бы Вы написали в STM или ARM и в письме указали на предполагаемую лично Вами ошибку - это было бы указание, которое автор мог бы оспорить. А так это была обыкновенная грубость.

3) Ничего, кроме того, что считаете себя непогрешимым. А значит читать Вами написанное не имеет никакого смысла. Вот если бы Вы допустили ошибку, признали её и исправили, вот тогда наоборот, читать написанное Вами действительно имело бы смысл. А раз Вы не ошибаетесь - то значит ничего не делаете )

Не найдётся ли у вас оперативной памяти, мистер Нейман?

Простите, что вмешиваюсь, но

единственную существенную разницу

безграмотно пишет

быдлоиндус

А Вы кто, чтобы решать, что существенно для схемотехники и так грубо отзываться о сотрудниках крупнейших разработчиков МК и CPU? Д'Артаньян?

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Попробовал на полном пересечии по Id без пересечений по диапазонам по миллиону записей в двух соединениях.

Для btree_gist вырос два запроса параллельно выполнялись 48 секунд. В случае с триггером и блокировками - 32 секунды. А это самый худший случай полного ожидания.

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

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

не поймёшь, что окажется эффективнее

Более чем двукратный запас на модификацию - это очень много. А 20% выигрыша на выборке тоже никуда не делись.

Есть ещё рекомендательные блокировки, это совсем быстро, но с ними надо аккуратно.

Если речь про advisory_lock, то их количество ограничено max_locks_per_transaction * max_connections. В отличии от количества заблокированных строк, которое не лимитируется.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

А вот и нет. Так как основная таблица не блокируется, то блокировка распостраняется только на проверку в триггере, но не на модификацию основной таблицы до проверки.

Но записи действительно блокировать эффективней. Так?

CREATE OR REPLACE FUNCTION
  tmp_test_not_range_after_insert_update_tfn()
RETURNS TRIGGER AS $func$
<<func>>
DECLARE
  ValidFrom  date;
  ValidUntil date;
BEGIN
  IF NEW.ValidFrom>COALESCE(NEW.ValidUntil,NEW.ValidFrom) THEN
    RAISE EXCEPTION 'ValidUntil must be higher or equal ValidFrom';
  END IF;

  PERFORM H.Id
  FROM tmp_test_range_header H
  WHERE H.Id=NEW.Id
  FOR NO KEY UPDATE OF H;

  SELECT F.ValidFrom, F.ValidUntil
  FROM (
    SELECT T.ValidFrom, T.ValidUntil 
    FROM tmp_test_not_range T
    WHERE T.Id=NEW.Id AND T.ValidFrom<>NEW.ValidFrom
      AND T.ValidFrom<=COALESCE(NEW.ValidUntil,'infinity'::date)
    ORDER BY T.ValidFrom DESC
    LIMIT 1 ) F
  WHERE COALESCE(F.ValidUntil,'infinity'::date)>=NEW.ValidFrom
  INTO func.ValidFrom, func.ValidUntil;

--  PERFORM pg_sleep(10);
  
  IF func.ValidFrom IS NOT NULL THEN
    RAISE EXCEPTION 'Id % ValidFrom % intersect with ValidFrom % and ValidUntil %',
      NEW.Id, NEW.ValidFrom, func.ValidFrom, func.ValidUntil;
  END IF;

  RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Но в общем да, это я и имел в виду, когда говорил, что разрыв сократится.

8 секунд добавилось, но разрыв в более чем в два раза - это все равно очень много. А с учетом 20% выигрыша на чтении - так вообще ставит крест на btree_gist. По крайней мере в этой области применения.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

С LOCK TABLE можно было и в старом варианте сделать, без заголовочной таблицы.

А что блокировать то? Запись, которая еще не зафиксирована? Так я блокирую целиком Id, не позволяя одновременно один и тот же Id модифицировать в версионной таблице из разных соединений.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Спасибо!

Пока получилось этот пример решить следующим образом.

Создал таблицу заголовка и связал по внешнему ключу с ней версионированную таблицу:

CREATE TABLE tmp_test_range_header (
  Id          integer       NOT NULL,
  Description varchar       NULL,
  CONSTRAINT tmp_test_range_header_PK_idx PRIMARY KEY (Id)
);

CREATE TABLE tmp_test_not_range (
  Id         integer       NOT NULL,
  ValidFrom  date          NOT NULL,
  ValidUntil date          NULL,
  Code       integer       NOT NULL,
  Amt        decimal(16,2) NOT NULL,
  CONSTRAINT tmp_test_not_range_PK_idx
    PRIMARY KEY (Id, ValidFrom) INCLUDE (ValidUntil),
  CONSTRAINT tmp_test_not_range_FK_Id_idx
    FOREIGN KEY (Id) REFERENCES tmp_test_range_header (Id)
);

В таблицу tmp_test_range_header залил все необходимые записи.

Функцию триггера изменил следующим образом:

CREATE OR REPLACE FUNCTION
  tmp_test_not_range_after_insert_update_tfn()
RETURNS TRIGGER AS $func$
<<func>>
DECLARE
  ValidFrom  date;
  ValidUntil date;
BEGIN
  IF NEW.ValidFrom>COALESCE(NEW.ValidUntil,NEW.ValidFrom) THEN
    RAISE EXCEPTION 'ValidUntil must be higher or equal ValidFrom';
  END IF;

  LOCK TABLE tmp_test_range_header IN SHARE ROW EXCLUSIVE MODE;

  SELECT F.ValidFrom, F.ValidUntil
  FROM (
    SELECT T.ValidFrom, T.ValidUntil 
    FROM tmp_test_not_range T
    JOIN tmp_test_range_header H ON H.Id=T.Id
    WHERE T.Id=NEW.Id AND T.ValidFrom<>NEW.ValidFrom
      AND T.ValidFrom<=COALESCE(NEW.ValidUntil,'infinity'::date)
    ORDER BY T.ValidFrom DESC
    LIMIT 1 ) F
  WHERE COALESCE(F.ValidUntil,'infinity'::date)>=NEW.ValidFrom
  INTO func.ValidFrom, func.ValidUntil;

  PERFORM pg_sleep(10);
  
  IF func.ValidFrom IS NOT NULL THEN
    RAISE EXCEPTION 'Id % ValidFrom % intersect with ValidFrom % and ValidUntil %',
      NEW.Id, NEW.ValidFrom, func.ValidFrom, func.ValidUntil;
  END IF;

  RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

Теперь Ваш пример уже не позволяет вставить пересекающиеся диапазоны. Но цена этого - вставка миллиона записей стала уже не 11-12 секунд, а 19-20 секунд. Что все равно, более чем в два раза быстрее, чем в случае btree_gist.

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

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Это я как раз делал. Но у меня же ситуация такая, что если запись X пересекается в диапазоне с записью Y, то и запись Y пересекается в диапазоне с записью X. Соответственно, валится по ошибке то, что коммитится позже.

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

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Согласен, можно упростить выражение до T.Id=NEW.Id AND T.ValidFrom<>NEW.ValidFrom . Это результат быстрой правки.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Можете привети пример? Я как ни пытался, но обойти его ограничения не смог.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

разрыв будет уже не такой драматичный

Наоборот получилось. При переходе на CONSTRAINT INITIALLY DEFERRED триггер стало даже немного быстрее.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Спасибо за замечание! Исправил в статье.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

GiST работает медленней, чем btree, это факт.

То что медленней, это понятно. Без триггера INSERT в таблицу с BTREE отработал бы за три секунды, вместо 12. А вот то, что даже с триггером, пожирающим 3/4 времени выполнения INSERT, BTREE выиграет в 4(!) раза - для меня самого было неожиданно.

Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

Поясните свою мысль, пожалуйста. Откуда могут взяться взаимопересекающиеся интервалы в триггере FOR EACH ROW?

Даже проверил с перепугу:

INSERT INTO tmp_test_not_range (Id,
  ValidFrom, ValidUntil, Code, Amt)
VALUES (0, '2020-04-01'::date, '2020-05-01'::date, 1, 1),
       (0, '2020-04-20'::date, '2020-05-20'::date, 1, 1);

SQL Error [P0001]: ERROR: Id 0 ValidFrom 2020-04-20 intersect with ValidFrom 2020-04-01 and ValidUntil 2020-05-01
  Where: PL/pgSQL function tmp_test_not_range_before_insert_update_tfn() line 25 at RAISE

Макроопределения для PostgreSQL

Странно. У меня в Chromium эти обратные слеши отображаются: