Akina

насколько оптимально такое решение?

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

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

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

Хотя как по мне, следует отделить систему безопасности Постгресса от авторизации в приложении. Мнение по данному вопросу от Everything_is_bad в комментарии - это не сарказм, а весьма правильное замечание.

Ну, скажем, так:

INSERT INTO Заказ (ПоставщикID, КлиентID, АдминистраторID, {остальные поля})
VALUES (
    (SELECT ПоставщикID FROM Поставщик WHERE Имя = 'Василий Пупкин'),
    (SELECT КлиентID FROM ...),
    (SELECT АдминистраторID FROM ...),
    {остальные значения}
)

А на свой фреймворк отображайте самостоятельно...

Прочитал, что в mysql в движке innodb индексы делятся на два больших типа - первичный и вторичный

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

В InnoDB - всего лишь есть дополнение, что при наличии первичного индекса он является также и кластерным. Для других движков и других СУБД такой зависимости может и не быть.

кластеризованный, как написано хранится с данными

Не-а. Кластерный индекс - это когда записи в теле таблицы (т.е. сами данные таблицы) хранятся в порядке сортировки по выражению этого кластерного индекса.

Всегда ли не кластиризованные индексы хранятся в оперативной памяти или это как-то можно регулировать?

Индексы всегда хранятся на диске. В оперативной памяти индексы могут всего лишь кэшироваться. Для ускорения доступа.

Я слышал что индексы должны быть в пределах мегабайт, а не гигабайт.

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

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

Вероятно, речь про INCLUDE-предложение в структуре индекса, имеющееся, например, в SQL Server... т.е. значения полей хранятся в выражении индекса как дополнительные данные, и не используются при сортировке.

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

Произвольный текст, не содержащий тегов, является валидным содержимым, но не XML документом. Не-XML фрагмент игнорируется при проверке.

Попробуйте вставить нечто, являющееся документом/фрагментом XML, но с ошибкой в разметке - тут же схлопочете ошибку.
DEMO

ОШИБКА: в целевой внешней таблице "users" нет ограничения уникальности, соответствующего данным ключам

Ошибка указывает на проблему, связанную со следующим внешним ключом:

FOREIGN KEY(user_id) REFERENCES users (user_id)

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

Так что смотрите, что именно нагенерила модель.

PS. Если определено PRIMARY KEY (user_id), то дополнительное UNIQUE (user_id) абсолютно бессмысленно.

Как-то вот так:

SELECT *
FROM table t1 
JOIN table t2
WHERE t1.id > t2.id
  AND ST_Intersect(t1.geom, t2.geom)

WITH
cte1 AS (
  SELECT jsonb_build_object('name',   users.name, 
                           'value',  comments.value,
                           'rating', comments.rating) single_user,
         comments.order_id,
         'comment' || ROW_NUMBER() OVER (PARTITION BY comments.order_id ORDER BY comments.id) num
  FROM users
  JOIN comments ON users.id = comments.user_id
),
cte2 AS (
  SELECT jsonb_build_object('order_id', order_id) ||
         jsonb_object_agg(num, single_user) single_order
  FROM cte1
  GROUP BY order_id
)
SELECT jsonb_agg(single_order) final_data
FROM cte2;

DEMO fiddle

with cte (cnt) as (
  select count(*)
  from test_sender 
  where statuse='crash'
  )
delete 
  from test_sender 
  using cte
  where cte.cnt = 0;