mayton2019

Современные аналитики обычно не работают с БД напрямую. Особенно с той БД, где ходят клиенты
и активно работают короткие транзакции (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

Документация гласит

https://www.postgresql.org/docs/current/transactio...

Read Committed is the default isolation level in PostgreSQL.

P.S. В Оракле - тоже самое.

--prefix, менял postgresql.conf и pg_hba.conf только чтобы слушал во вне

А попробуй тоже самое собрать но без этих изменений.

Кажется я так делал.
docker network inspect .....

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

Ну в базах данных так не делают. Обычно все колонки именованы и имеют смысл.

У тебя есть два варианта КМК. Можно провести еще один сеанс нормализации и сделать табличку
так.

id| q  | value
1 | q1 | 1111....
1 | q2 | 2222....
1 | q3 | 3333333
2 | q1 |......

или сделать value как JSON и складывать туда массив

id| json_value
1 | [ "11111", "2222", "333333" ....]

Ну ты можешь в качестве ключа использовать UUID. Так делают в распределенных системах когда много генераторов данных и все друг о друге не знают и не синхронизируются.

Или можешь сам с собой договориться что каждому продюсеру данных дается диапазон. Для первого будет от 0 до 500 000 и для второго 500 001 до 1 000 000 и так далее.

Есть генераторы основанные на текущем времени и мак-адресе хоста. Да много чего можно придумать.

Игры в upsert или retry логикой могут заблокировать джобы надолго. Они могут кружиться в вальсе вместе
постоянно наступая на конфликты. И это трудно пофиксить.

Вот ошибка

duplicate key value violates unique constraint "universitySpecialties_pkey"

Кину 5 копеек по поводу работы с датами. Да это зло. Работа с датами в современном API это
самый большой технический долг начиная с Unix, когда дата представлялась секундами с 1970 года
в виде DWORD. Я не встречал ни одного языка программирования и ни одной DBMS где изначально
была-бы какая-то очень строгая и математичная концепция работы с временем. Везде были ограничители
в основном завязанные на примитивные типы либо на строки вариативной длины. В Java например
долгое время экплуатировался тип java.util.Date который сегодня считается дыркой (мутабельность)
и неточным и его заменяют на java.time.* семейство типов. Параллельно с ним где-то в космосе
висит java.sql.Date который декларирован в интерфейсах JDBC как основа для БД. С ним-же и работают
все драйвера реляционных бд.

По поводу вычислений на application tier. В последнее время DBMS девальвировали. И в основном
используются в микросервисах как хранилище таблиц без особой логики. В этом есть свои смыслы.
Например удобнее тестировать и хранить 100% кода в языках Java/Node/C#. Это создает гомогенность
языка в проекте. В противном случае логику пришлось бы неизбежно резать на 2 слоя и хранить
половину в application и другую половину деплоить через flyway/liquibase в БД при этом еще и
не забыть тестировать 100% совместимость тех-же функций для работы дат-времени (никто
кстати невкурсе что в Oracle год может быть 9999 а java.util.Date мне удалось сгенерировать
такую Aug 17 09:12:55 EET 292 278 994. .. оптимистичненько доживем до 290 миллионов
лет хотя проблема comparison этих типов остается) Стандарты ISO помогают но они скорее
декларируют намерения сохранить нужное значение. Вот и если вы новичек - то я гарантирую
что вы словите кайф в попытке в Java разобраться в проекте какой тип дат вам брать. И еще
помножите это все на типы данных БД (их там будет 4 штуки обычно. Парочка для зональных
и парочка для локальных).

Использовать или нет функции PG? Ответ - it depends. В некоторых случаях оптимизатор не видит
индекса если ты делаешь неявный кастинг из строки в дату например. Я тут не уверен надо проверять.
Но есть старая админская поговорка. Плохой execution plan - проверь типы данных в предикатах.
Беда реально существует для Spark/Databricks и даже включена в учебный план. По крайней мере int/Long
различается на уровне Catalyst-optimizer. Вобщем если вы - лентяй то можете лупить строки вместо дат
и надеятся что SQL машина правильно интерпретирует. Если вы хотите быть точным то делайте CAST или
to_date с явным описаловом YYYY-MM и т.д.

Еще один поинт в части где хранить логику. Это я пишу просто для кругозора. Чтобы топик
не циклился вокруг Постгреса а люди видели пошире. В классических БД данные качаются
к клиенту. Тоесть делаете SELECT * из миллирад строк - и этот миллиард будет прокачан до конца
когда вы читаете резалт-сет по сети. Такова парадигма. Или курсор. Но суть таже. А в BigData данные
лежат на месте но к ним "ходит" код. Вот такой метафизический парадокс. Сами понимаете что
тут получается что встроенных функций даже как бы ... и нет. Подчеркиваю разницу.