mayton2019

вот эта штука с initdb выполняется секунд 10-20 поэтому её похоже приходится делать на старте (а можно ли как-то ускорить?)

Да. Это похоже на правду. В качестве утешения могу сказать что Azure AKS контейнеры могут и
больше 1 минуты стартовать независимо от содержания.

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

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

Excel я сделал форму, кнопки, добавить расходы, доходы, добавить пользователя.. жму и выполняется sql запрос и база заполняется...

Напоминает старый анекдот про "сделайте мне лялю"...

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

Экспорт-импорт из Excel в PG это задача решенная. И она даже не требует разработки. Это скорее
всего работа квалифицированного пользователя и дата инженера.

А вот все что касается формочек и кнопочек, нужен разработчик. И детальное ТЗ.

В реальной жизни ты должен использовать второй варинт. Потому что - транзакции.
Ими надо управлять. И приложение типа CRUD тоже должно всегда использовать второй вариант.

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

Данный SQL запрос - это конечно ужас-ужас и кроме того здесь есть такие
справочные сведенья которые надо-бы не хардкодить в коде а фиксировать
во таблицах справочниках. Иначе через некоторое время будет противоречие.
Код будет отставать от актуального положения вещей.

Тебе на самом деле выгоднее не делать select а сделать один раз процессинг таблицы
и декодировать поле baza один раз и сохранить его в другое поле.

Это кажется называется enrichment. Обогащение. Хранимой процедурой можно.

Непонятно каким образом вы от проблемы PostgresQL перешли к проблеме zfs?

Мне кажется что существует только один способ проверки работоспособности снепшота.
А именно - сразу после того как он сделан - поднять на нем БД и посмотреть что в журналах
нет ошибок. Если ошибки есть - повторить снепшот еще раз.

Sad, but true.

Непонятно зачем ты конструируешь ILIKE когда у тебя идет поиск цифр и нет
case-insensitive алгоритмов. Упрости, чтоб было системе проще.

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

Есть идея попробовать следующее. Можно конкатенировать все искомые поля в одно большое
виртуальное поле и построить по нему реальный триграммный индекс. Здесь мы сэкономим на количестве.
Вместо 5 индексов сделаем один и результат будет тот-же самый. Нам по сути безразлично
в каком поле найдена строка. Важно что просто была дизьюнкция предикатов.

CREATE TABLE messages(
    .......
    all_fields text GENERATED ALWAYS AS (CONCAT(sender,' ',message,' ',receiver,' ',sim))
);

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX messages_tr_idx ON messages USING GIN (all_fields gin_trgm_ops);

Точно так-же поступить с табличкой user_apps.

По поводу этого ограничителя.

....
order by "messageId" desc 
limit 51 offset 0

Судя по всему - это дизайн запроса для показа на UI. Обычно так делают чтобы
показать на формочке веба или в приложении.

Limit и order - это плохая комбинация. Если смысл сортировки по messageId - это показать
последние (оперативные) сообщения то я-бы заменил это на поиск по партишену сегодняшнего
дня (или опер-дня) если можно так сказать.

Разделение данных на оперативные и исторические ускорит поиск многократно. И если
допустии опер-день занимает 500 тыс строк а исторические данные - 13 млн. То вы
сразу получаете вместо 80 секунд ускорение ... эээ в сколько раз? 500 000/13 000 000 = x/80
это будет примерно 3 секунды. Вот. Это если предполагать линейную зависимость от объема.

Есть два важных смысла в ПК. Первое - это реляционная алгебра. Про это написанно миллиард
книжек и все преподаватели тебя зачморят если в таблице нет ПК и foreign key constraint не будет
работать.

Второе. Если ты создаешь например CREATE TABLE .... (.... PRIMARY KEY..); то бесплатно
бонусом получаешь создание индекса по полям где есть ПК и соответсвтенно поиск по ПК
будет идти очень быстро и не будет дубликатов.

Но в современном мире понятие "данные" стало очень широким и в базах очень часто лежат
таблицы без ПК. Это staging table (куда идет загрузка из внешних источников), логи и отчеты.
И аналитические представления. В области bigdata данные часто лежат в файлах (parquet, orc, delta)
и там вообще ПК нету. И ничего. Работает.

Современные аналитики обычно не работают с БД напрямую. Особенно с той БД, где ходят клиенты
и активно работают короткие транзакции (OLTP).

В крупных конторах наподобие банков и торговых сетей обычно для аналитиков отгружаются
все-все исторические данные, что проходили в БД. В денормализованном виде. Обычно
такие себе широкие таблицы по 100 - 500 колонок. И эти таблицы сливаются во всякие
аналитические системы (Databricks) в формате column-oriented tables (Delta-table). И аналитики
работают с этими данными на языках SQL/Python/R e.t.c. Строют всякие графики, краcивые
картинки и агрегации.

ОИБ здесь конечно при делах и не при делах. Рациональное зерно такого разделения
состоит в том что с БД транзакций снимается ненужная I/O нагрузка и БД работает легче
и аналитики не натворят бед с denial of service.

Когда говорят о базе данных, то 99% имеется в виду классическая реляционная БД типа Postgres/MySQL e.t.c.
Такие базы данных создавались для эффективного соединения кортежей и сортировок. Длина DataRow
при этом обычно не больашя (до 8К целый блок таких строк). Эта цифра имеет корни еще в 20м веке.
И если заставить их хранить html (обычно 5-100К) то такая деятельность может быть не очень
удобная для БД. Это как микроскопом гвозди забивать. Очень умная система будет использоваться как
файловое хранилище. Возникает идея - просто взять что-то ориентированное на файлы. Например S3,
BlobStorage, GoogleDrive. Это было-бы дешевле с точки зрения стоимости владения и бэкап делать
проще.

Я понимаю что мы живем в странное время, когда вместо расчета в калькуляторе - запускают гугл или вместо
расчета в MathCad спрашивают ChatGpt, но все-таки программист должен быть немного хозяйственник
и должен брать простые и дешевые решения там где они достаточны.

Вариант

_     | M | X | L
------+---+---+----
Red   |   | Y | Y
Green | Y |   |

Тебе проще новую базу создавать каждый раз. Я так делал. А старая пускай лежит как бекап.
Создаешь db1, db2, db3.... Потом удаляешь которые уже не нужны.

Или в докере разворачиваешь если пустая нужна.

Вот посмотри может это поможет https://www.postgresql.org/docs/current/dblink.html

Postgres очень быстро паркуется, если надо. Кажется pg_ctl там с аргументами или services stop postgresql.
Вот сделай скриптик.

До postgresql версии 13, если я не ошибаюсь, индексы были в полтора, а то и два раза больше. У нас на проекте версия 9, если не ошибаюсь, там индексы добавляют к памяти иногда по 5 гигов. Нормально ли это? Я слышал что индексы должны быть в пределах мегабайт, а не гигабайт.

Работаю с базами данных давно. Начинал с Oracle9i.
Большая часть индексов базируются на B+Tree. Хотя в последнее время в эпоху RocksDb/Cassandra/Tarantool
появились более интересные стурктуры такие как LSM-tree. Они по скорости записи более эффективны.

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

В Оракле есть положительный эффект от периодической перестройки индекса (alter index rebuild).
Этот эффект временный и обычно связан с фактором кластеризации. Его очень любят новички и
часто сам вопрос является троллингом Oracle-профессионалов. Но это было лет 20 назад. Щас
в эпоху облак всем стало пофиг.

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

Не знаю откуда ты такие вот факты черпаешь. Конечно лучше всю базу данных положить в память.
Но база обычно многократно превышает память и мы довольствуемся страничным кешем (page cache)
или buffer pool в других системах. И все они работают по принципу LRU (хранения наиболее горячих
блоков диска). А будет ли это таблица или индекс или еще какойто подвид объекта - это как повезет.
Во всех DBMS есть мониторинг этого страничного кеша. Вот посмотри что у тебя там лежит в час
наибольшей нагрузки. Это и будет самый правильный ответ на твой вопрос. И главное - практически
подтвержденный.

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

Приводи ссылки где ты читал потому-что в твоем пересказе получается мистика. Индекс обязан хранить
копии индексируемых столбцов. Иначе-бы поиск вообще не работал.
Если ты строишь композитный индекс по 3 полям то он и будет физически хранить 3 копии этих полей
и ROWID (физический указатель на позицию в таблице для строки). И при определенных условиях
оптимизатор может выдавать данные не из таблицы а прямо из индекса если в SELECT запросе
достаточно данных в индексе. Этим часто пользуются для оптимизации.

Есть альтернативные DBMS наподобие Amazon DynamoDB где индексов нет но есть полная реплика
таблицы которая по другому кластеризована. Динамо считает это индексом хотя с точки зрения
классической DBMS это просто маркетинговый обман.

UPD: R+Tree