Melkij

С insert или update по результату select (к слову, совершенно ненужному запросу) вы вляпываетесь в race condition
on conflict специально сделан для нормальной сериализации происходящего и использоваться и должен. Другие способы сериализации race condition из времён до on conflict производительность просаживают по вполне очевидным причинам.

Варнинг в мануале для чего дан? php.net/manual/en/function.sprintf.php

Warning Attempting to use a combination of the string and width specifiers with character sets that require more than one byte per character may result in unexpected results

Потому что указание длины строки в printf работает в байтах, а не символах.

select extract(epoch from now() - MAX(DATE_OP)) seconds_ago from tablename;

where datefield >= '2017-01-01' and datefield < '2018-01-01'

Или если несподручно считать следующую дату:

where datefield >= '2017-01-01' and datefield < '2017-01-01' + interval 1 year

Только не надо делать якобы простое where year(datefield) = 2017, который будет перебирать всю таблицу потому что в принципе не может использовать индексы.

Контроллер памяти в процессоре и ограничивает. Datasheet на 19-20 страницах описывает поддерживаемые модули - модулей по 32гб каждый там нет, максимум процессор умеет 4 DIMM по 16гб каждый = 64гб.
А на указанной материнской плате просто физически некуда вставлять столько памяти - слота всего два вместо 4, на которые рассчитан максимальный поддерживаемый объём.

json нужно использовать если у вам надо хранить json и сохранять полностью его форматирование. Форматирование именно самого json с пробелами, табами и переносами строк - т.е. как текст, но с валидацией что здесь именно json.
если вам нужно хранить json - лучше использовать jsonb
А для описанной задачи и просто массива достаточно, bigint[] или text[]. Контактные данные для заказа уместны именно в заказе, а по jsonb и массивам возможно вполне внятно и искать в том числе

Покрывающий (covering) индекс - это индекс, которого достаточно для ответа на запрос вовсе без обращения к самой таблице. В самом индексе хранится достаточно данных для ответа на запрос и, хоть и возможно по индексу достать всю строку данных - это просто не нужно. За счёт того, что не нужно дёргать непосредственно таблицу, а ответить можно используя только индекс - покрывающие индексы несколько быстрее (насколько - зависит от дисков, кэша и объёма горячей части базы). Но при этом, разумеется, сам индекс становится больше и злоупотреблять этим не нужно.

Для uuid в принципе не нужен текст, есть нативный одноимённый тип данных.

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

Проверьте память. Физическую на сервере. Подозрение что косячит.
И диски тоже проверьте.
Потому что есть всего одно место, где можно поймать "compressed data is corrupted" и связано оно с распаковкой сжатых данных из toast. reindex тоже говорит, что прочитано что-то совсем не то, что предполагалось.

поэтому проще будет найти файлы и все попереносить.

И так делать нельзя и ничего кроме бесполезного файлика с бинарным мусором вы в резуультате не получите.
Прочитать данные из raw файлика, а уж тем более подсунуть его в другую базу - очень сильно замучается даже опытный DBA, способный читать и понимать исходный код postgresql (это кстати минимальное требование к тому чтобы что-то достать из сырого файлика таблицы).

Потому что директория базы PostgreSQL - это один неделимый объект (плюс tablespace, которые отделять тоже нельзя от данных кластера). Для чтения данных из файлов таблицы (их много может быть) нужен системный каталог, нужен toast (если был создан), нужны clog и xlog чтобы понять, а что мы собственно в этом файлике видеть должны, а что просто ещё не вычистил вакуум.

Если вам нужны данные из конкретной таблички - сделайте логический снимок таблички уже упомянутым pg_dump.

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

Может лучше ваш список смежности вовсе заменить на штатное материализованное представление ltree?
Ну или во всяком случае на него можно заменить ваши массивы.

Рассматриваем 64 битное число, big endian byte order в hex представлении записанном как CCD044A12FDB3E13
В linux php x64 реализует целочисленный int как 64 битовое знаковое число независимо от версии интерпретатора - соответственно подходит.

Согласно синтаксису PHP шестнадцатеричная запись числа является беззнаковой, знак указывается явно. Число CC больше 7F - следовательно, в знаковом типе недостаточно разрядов (в int есть 63 разряда + бит знака, а требуется минимум 64 + знак) и число приводится к float
Если вы возьмёте 0x7cd044a12fdb3e13 - это число укладывается в 63 разряда + знак и будет помещено в int тип данных.

Если у вас машинное представление числа и его необходимо корректно разобрать - есть unpack:

var_dump(unpack('J', "\xCC\xD0\x44\xA1\x2F\xDB\x3E\x13")[1]);

Что даст искомый int(-3688372635733115373)
Плюс внимание на порядок байт. Что unpack позволяет учитывать.

Где-то это было описано... Хм. А, вот

All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

Для char, varchar и text оператором сравнения не учитываются пробелы справа и считается что их нет. LIKE - учитывает.

mysql/innodb всегда кластеризована по первичному ключу. Т.е. данные физически упорядочены в порядке задаваемом первичным ключом. Собственно данные и лежат в листьях первичного ключа.

postgresql умеет команду cluster которая одноразово переписывает таблицу в порядке указанного индекса (плюс внимание на блокировку таблицы на всё время перестроения). В дальнейшей работе пишущих запросов кластеризацию данных не поддерживает.

Имеет ли кластеризация смысл для вашей задачи - не уверен. Надо смотреть диски и объём горячих данных по отношению к буферной памяти.

А для графов вероятно лучше взять графовую, а не реляционную СУБД.

Индекс возможно повесить функциональный:

create index on tablename using btree(((features ->> 'capacity'::text)::integer));

Соответственно предикат по этому же самому выражению получит возможность использовать этот индекс.

Больше возможностей внятно индексировать jsonb для запросов на числовые диапазоны мне как-то не вспоминается. Сортировку по полю-то только btree и умеет из всех актуальных access method.

Т.е. индекс используется, хотя в данном случае index срабатывает на price столбце? Тогда почему запрос отрабатывает быстро?

Да, индекс используется - индекс по price. Потому что вы по нему сортируете. И планировщик опираясь на свою статистику надеется, что сможет быстро найти 25 строк читая строки в порядке требуемой сортировки по индексу и по пути проверять выкидывать неподходящие.