Melkij

Вы в after insert триггере на табличке со странным названием lechenie выполняете безусловный insert в эту же самую таблицу. Вопрос к вам: почему вы в результате ожидаете что-то кроме бесконечной рекурсии?

В postgresql все текстовые типы следуют кодировке базы. case insensitive utf8 в pg не предусмотрено.
Поэтому если у вас база в utf8 - то "STEM" varchar(50) not null
Если база не в utf8 - не надо так делать.

Всё очевидно:
суперпользователь базы - лицо доверенное и база доверяет этому пользователю. И суперпользователь множеством хитрых и не очень способов способен сделать с сервером на котором запущена база всё на что хватит прав у пользователя, от которого запущен postgres.
Например, запустить любую произвольную команду даже простой и очевидной командой copy, не говоря уже о каких-то извращениях.

Конкретная атака по-видимому уже разобрана и описана вот тут: https://habrahabr.ru/post/351452/

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

2. Нужно ли включать order by в таких огромных таблицах?

Не зависит от размера базы.
Или у вас есть order by и поведение ожидаемое или у вас нет order by и СУБД имеет полное право на каждый запрос выдавать любые подходящие под фильтр данные в любом порядке.
Запрос выдающий неправильные данные обычно никому не нужен, даже если он и быстрый.

offset для пагинации вообще вещь неуклюжая. https://use-the-index-luke.com/no-offset
Быстрая пагинация - это передача уникального идентификатора, последнего на просматриваемой странице. Т.е. запрос на выборку следующих 100 записей начиная после такого-то id.
Бонусом консистентное поведение, если, например, что-то из строк предыдущей страницы удалили. Оффсет тупо потеряет строку из выборки и пользователь может не найти то что искал.

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

Вы изобрели то что делает джойн. База в общем случае его сделает лучше.
Будет ли огромная временная таблица - смотрите план.

pg_repack - это community extension, форк более раннего pg_reorg. И не отрицая вклад участников pgpro в разработку postgresql в целом - я не вижу их среди контрибьютеров pg_repack.

Мы активно используем обе. Основные моменты касательно pg_repack vs pgcompacttable в общем-то именно я и изложил в README компактора. Если говорить на русском:
- pg_repack делает копию таблицы, т.е. требуется двукратный запас диска. Компактор работает в рамках одной таблицы и требует место только под копию наиболее толстого индекса и то, толстые индексы обрабатываются самыми последними. Т.е. расчёт на работу когда места уже мало.
- компактор намеренно выполняется медленно чтобы не задевать работающий проект
- компактор не может обрабатывать toast таблицы. Репак - может.
- pg_repack характеризуется очень заметными пиками io и wal. Вся таблица едет одной транзакцией и это никак не регулируется. Поэтому может аффектить прод на неважных дисках и в том числе оторвать реплики при недостатке сетевой полосы.
- pg_repack умеет перемещать между tablespace и может делать команду cluster

(или лучше подстроить автовакум)?

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

А с кавычками выводит "ничего" т.е. ноль полей.

С какими именно?

user='40'
'user'='40'
"user"='40'

Это три абсолютно различных условия, выполняющих совершенно разные вещи.

user - зарезервированное слово по требованию стандарта. По стандарту не уверен что обозначает в контексте where, надо искать (может как часть create user и подобных только зарезервирован), а в postgresql обозначает имя текущего пользователя. Имеет тип name, что и провоцирует ошибку.
Т.е. первое условие в принципе не имеет никакого отношения к каким-либо пользовательским таблицам.

'user' - просто текстовая константа. Никто не мешает в условии сравнивать одну константу с другой, where 1=1 в некоторых query builder отсюда же.

"user" - обращение к объекту, игнорируя зарезервированные слова. То есть если у вас в таблице proxy есть поле user - то запрос должен быть записан как
select * from proxy where "user"='40'

Но общая практика - лучше не надо использовать зарезервированные слова в качестве идентификаторов.

У postgresql есть всего одна директория - data_directory (aka PGDATA). Для работы необходима консистентная копия всей директории целиком, включая содержимое по всем симлинкам.

Можете поковырять pg_filedump, но лучше достаньте бекап.

объясните, пожалуйста, смысл ролей в постгрес

Пользователь - это алиас для системы ролей.

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

Это ложь. Необходимости нет.

Поэтому для созданного нами ранее пользователя с именем sammy соответствующая роль будет по умолчанию пытаться соединиться с базой данных sammy

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

можно ли так: несколько ролей и одна база данных?

Разумеется. Хоть дерево со множественным наследованием ролей.

Только важный момент - роли глобальны в пределах кластера. Вы не можете сделать пользователя user в базе foo с одним паролем и пользователя user в базе bar с другим паролем. А вот дать readonly на одну базу и полные права на другую - это пожалуйста.