Melkij

Чтож, зависит от того что именно вы хотите сделать.

PostgreSQL позволяет сделать полностью собственную реализацию типа данных. Которая будет себя вести так как вы захотите - но всю эту логику вы должны разработать сами на C. Потому у create type и довольно объёмная документация.

range типы - это диапазоны. Например, дата бронирования номера гостиницы. Таблица бронирований, одно поле с диапазоном дат tsrange и exclude constraint - всё, база проконтролирует, что даты бронирования не пересекаются.

перечисления - это обычно подразумеваются enum. Но для вашей задачи это не подходит. enum - это фиксированный набор из строк.

что-то похожее на штатные типы данных, но с ограничениями - вы хотели найти не create type, а create domain:

CREATE DOMAIN smallint_between_10and200 AS smallint CHECK(value >= 10 and value < 200);

Всё, теперь в таблицах вы можете использовать тип данных smallint_between_10and200, в который тем не менее записать что-то не из диапазона 10..200 будет невозможно.

Оператор сравнения в plpgsql такой же как в SQL. is null для проверки является ли значение null и = для равенства.

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

В таблице items 12 млн записей, в using_items 11 млн. Разумеется количество гипотетическое и маловероятное.

Да нагенерируйте и посмотрите explain analyze. Были бы действительно объёмы, а не всего-то десяток миллионов строк.

not in дорог из-за требования стандарта по части обработки NULL в not in и отсутствии специальных оптимизаций для этого случая. Может быть оптимизация появится.
not exists оптимален.
через left join план может быть идентичным not exists

select k, count(*) from tablename join  jsonb_object_keys(data) as k on true group by k

?

Если материализованное view - то refresh materialized view
Если не материализованное - то закешировано быть не могло. Смотрите определение view и исследуйте, откуда строка в резуультате этого запроса получается.

Можем ли мы быть уверены, что в следующей конструкции обновление обязательно произойдет (считаем что SELECT вернул какие-то данные)?

Нет, не можем.
Два конкурентных запроса могут увидеть одни и те же версии строк в cte (не пересекутся на читающих блокировках даже с конкурентными писателями т.к. mvcc) и затем пойдут обновлять одни и те же строки. На блокировках обновления строк их транзакции и сериализуются. Кстати, можно и deadlock словить если строк к обновлению будет несколько и они вернутся из запроса в разном порядке.

приостановить запрос и посмотреть что будет, если произойдет X?

gdb может приостановить всё что угодно. Правда далее уже интересный момент что надо найти куда именно break point ставить.

Мой хрустальный шар совершенно верно показал.
Когда вы делаете add column columnname text - вы получаете NULL во всей этой колонке. Разумеется сразу после этого вы поставить NOT NULL не можете никак. Любая строка нарушает это ограничение и база отказывается ставить NOT NULL.
Когда вы делаете add column columnname text NOT NULL - чтож, это может сработать в одном случае - у вас пустая таблица. Тогда ни одна строка не нарушает NOT NULL ограничение, у вас просто 0 строк. Если хоть одна строка есть - это невозможно. Вы просите default NULL и одновременно NOT NULL - так не бывает. В сообщении об ошибке говорится именно о добавляемой в этот момент колонке, не о какой-то другой существовавшей ранее.

Вы должны указать какой-то не NULL default если хотите поставить NOT NULL на создаваемое поле. Либо очистить таблицу.
Либо создать без NOT NULL, заполнить поле, потом уже делать NOT NULL.

В postgresql действительно колонка не удаляется никогда. Но это деталь реализации и к сути вашего вопроса отношения не имеет.

Не "оператор" потому что а statement - и будет сразу немного понятнее о чём речь.

Первый delete пришёл через extended protocol так же известный как prepared statements. Рядом помимо execute должны быть ещё parse и bind.
Второй - через simple query протокол, то есть просто текст запроса.

Поставить pg_stat_statements, спустя какое-то время посмотреть что тот собрал. Например, нашим отчётом

Слово namespace здесь неверно.
Ищите по schema name и конкретно search_path

Странно что вы не знаете что такое search_path, но он у вас модифицирован. В дефолтную настройку схема public включена и потому оба ваших запроса обычно делают одно и то же.

В доктрине начиная с 2.5 схема указывается так:

/**
 * Clerk
 *
 * @Table(schema="schema")
 */
class Clerk { }

Не уверен что будет работать для public схемы если доктрина предполагает что оно в search_path есть и так.

Настраиваете мониторинг, запускаете проект. Правите настройки или сам проект по ситуации.
Знаю системы где пяти коннектов базы хватает на сотни транзакций в секунду. Знаю системы где полсотни коннектов постоянно заняты непойми чем.