Melkij

Описание синтаксиса весьма красноречиво намекает о реализации
DO UPDATE SET ... WHERE condition

melkij=> create temp table foo (item int primary key, d date, price numeric);
CREATE TABLE
melkij=> insert into foo values (1, '2021-07-30', 100) on conflict (item) do update set price = excluded.price, d = excluded.d where excluded.d > foo.d;
INSERT 0 1
melkij=> table foo;
 item |     d      | price 
------+------------+-------
    1 | 2021-07-30 |   100
(1 строка)

melkij=> insert into foo values (1, '2021-08-01', 110) on conflict (item) do update set price = excluded.price, d = excluded.d where excluded.d > foo.d;
INSERT 0 1
melkij=> table foo;
 item |     d      | price 
------+------------+-------
    1 | 2021-08-01 |   110
(1 строка)

melkij=> insert into foo values (1, '2021-07-20', 80) on conflict (item) do update set price = excluded.price, d = excluded.d where excluded.d > foo.d;
INSERT 0 0
melkij=> table foo;
 item |     d      | price 
------+------------+-------
    1 | 2021-08-01 |   110
(1 строка)

Если это подходит для postgresql, то, вероятно, подойдёт и для вас: https://github.com/postgres/postgres/blob/REL_13_S...

(geteuid + getpwuid под linux)

Можно ли сделать так, чтобы запрос выполнялся дольше, но меньше загружал CPU -- может есть какие-то способы понизить приоритетность выполнения?

Обычный nice. Процессная же модель, никаких проблем понизить приоритет конкретного процесса средствами самой ОС.

Есть ли встроенные механизмы кэшированая, которым можно сказать, что для запроса Х нужно отдавать данные из кэша при вызове его чаще чем N минут\часов?

Нет, кеша запросов нет. И не будет.

ситуация когда с БД нужно собирать некоторую статистику и при этом сделать так, чтобы система не сильно тормозила

Не смешивать OLAP и OLTP вообще. Если база важная, то у неё будет hot standby. Там статистику и считать.

А зачем добавлять ещё раздел, если вы его в lvm так же хотите добавлять? Просто расширьте раздел
parted resizepart + pvresize

Не надо заниматься обфускацией запросов и пытаться сбить с толку планировщик. Он умный, но он тупой.

Потому что это соответствует вашим данным?

Если непонятно - смоделируйте элементарный nested loop на пальцах, выполняя чтения из ваших таблиц по одной.

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

но в таком случае, скорее всего, понадобиться таблица с именами провайдеров

А она у вас всё равно есть или будет.
Хоть вы сами работаете с API и к каждому сервису максимум один комплект данных авторизации (таблица, где будут ключи храниться и будет такой таблицей с именами провайдеров). Хоть вы даёте своим пользователям возможность добавить API себе - тогда у вас будет табличка user_id, api_provider_id, credentials

Невозможно.
В postgresql из коробки есть только heap - несортированная куча.

Обратите внимание, что в SQL вообще не существует сортировки кроме той, которую вы явно запросили в запросе, указав ORDER BY.

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

anyarray || anyelement → anyarray
Concatenates an element onto the end of an array (which must be empty or one-dimensional).
ARRAY[4,5,6] || 7 → {4,5,6,7}

Поставьте PDO::ATTR_ERRMODE в адекватный PDO::ERRMODE_EXCEPTION при подключении к базе.

Хинт: синтаксическая ошибка в запросе.