Akina

WITH cte AS (
  SELECT *,
         SUM((flag IS NULL) :: INT) OVER (PARTITION BY user_id ORDER BY product_id ASC) grp
  FROM test
  )
SELECT product_id, user_id, flag,
       DENSE_RANK() OVER (PARTITION BY user_id ORDER BY grp ASC) "rank"
FROM cte
ORDER BY 1,2;

fiddle

Запрос

SELECT * FROM base WHERE domain LIKE '%habr%' LIMIT 10000;

Какой это нахрен поиск "по совпадениям"??? Это, блин, поиск по подстроке!!! причём в любом месте строки.

С таким шаблоном поиска можете с обычными индексами вообще не трахаться, по причине абсолютной бессмысленности действа. Читайте про GIN, GiST, RUM и прочие виды индексов и поиск с их использованием.

Будет что-то вроде

SELECT *,
       ( SELECT value
         FROM tablename t2
         WHERE t1.year - 1 = t2.year
         ORDER BY created_at DESC LIMIT 1 
         ) AS prev_value
FROM tablename t1;

Схематично:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn
    FROM tablename
    WHERE type = @type
    )
SELECT /* DISTINCT */ t2.*
FROM cte t1
JOIN cte t2 ON t2.rn IN (t1.rn - 1, t1.rn, t1.rn + 1)
WHERE t1.status = @status
ORDER BY t2.rn

Задача заключается в том, чтобы добавить еще одну колонку для мультиязычности, назвав DocTypeEn, в котором будет перевод.

Ага... а потом захочется на немецкий, на китайский... так и будешь поля добавлять?

Классический подход - таблица текстовых литералов

CREATE TABLE translation (
    token_id INT,      -- идентификатор строки
    language_id INT,   -- идентификатор языка
    PRIMARY KEY (token_id, language_id),
    value VARCHAR(100) NULL DEFAULT NULL
    );

Соответственно зная номер строки, который нужен, и язык, получаем значение

SELECT value
FROM translation
JOIN language USING (language_id)
WHERE token_id = @token_id
  AND language_name = @language_name;

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

SELECT COALESCE((
    SELECT value
    FROM translation
    JOIN language USING (language_id)
    WHERE token_id = @token_id
      AND language_name = @language_name;
    ), (
    SELECT value
    FROM translation
    JOIN language USING (language_id)
    WHERE token_id = @token_id
      AND language_name = @default_language_name;
    )) value;

То есть для литералов. имеющих перевод, возвращаются именно они, а для ещё не имеющих - значение на языке по умолчанию.

============

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

{
    "ru":"Выход",
    "en":"Quit"
}

Но и для такой схемы получение литерала для дефолтного языка при отсутствии перевода - актуально.

Как реализовать надежную запись списка данных без пропусков значения автоинкремента id?

Никак НЕ РЕАЛИЗОВЫВАТЬ.

Оставь автоинкремент в покое - он обязан обеспечивать только уникальность, и не более. Нужна непрерывная нумерация? Создай для неё ОТДЕЛЬНОЕ поле, и нумеруй программно. Триггеры в помощь.

Оператор ?| в PostgreSQL выполняет поиск строковых значений в списке элементов массива или ключей (имён атрибутов) объекта:

jsonb ?| text[] → boolean

Do any of the strings in the text array exist as top-level keys or array elements?

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

https://www.postgresql.org/docs/current/functions-...

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

Если принять такую формулировку:

Первичным является товар, одновременно с которым куплено больше всего других товаров

то запрос на получение "первичных" товаров будет такой:

WITH cte AS (
    SELECT t1.cat_id, RANK() OVER (ORDER BY COUNT(t2.cat_id) DESC) rnk
    FROM user_to_cat t1
    JOIN user_to_cat t2 ON t1.user_id = t2.user_id
                       AND t1.cat_id <> t2.cat_id
    GROUP BY 1
    )
SELECT category.code
FROM cte
JOIN category ON category.id = cte.cat_id
WHERE rnk = 1

На данных из fiddle запрос даст 2 товара - orange и pear, оба они участвуют в 6 парах, тогда как apple участвуют в 4, а lemon в 2 парах.

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

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

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

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

Хотя как по мне, следует отделить систему безопасности Постгресса от авторизации в приложении. Мнение по данному вопросу от 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... т.е. значения полей хранятся в выражении индекса как дополнительные данные, и не используются при сортировке.