Melkij

Штука называется Protocols and Wrappers, могут быть даже самописные.
Ровно та же самая вещь, почему file_get_contents умеет делать http запросы.

Возможно ли не встравлять в таблицу записи, которые уже присутствуют в таблице?

Возможно. Если вы этим знанием свой предметной области поделитесь с СУБД и создадите уникальное ограничение. А скорей всего это вовсе натуральный первичный ключ.

on conflict работает как ни удивительно при конфликте уникальности. Поэтому пока у вас никто не гарантирует уникальность данных - on conflict ничего не сделает.

Попробуйте так:

SELECT Prop.*, coalesce("counters".countAllUnits, 0) AS "countAllUnits", coalesce("counters".countVacantUnits, 0) AS "countVacantUnits"
                    FROM "Property" AS Prop
                    INNER JOIN LATERAL
                    (
                     SELECT COUNT(*) AS countAllUnits,
count(*) filter(where "Unit".status = 'Vacant') as countVacantUnits
                     FROM "Unit"
                     WHERE Prop."id" = "Unit"."propertyId"
                    ) AS "counters" ON true
                    WHERE  Prop."userId" IN (
                          SELECT id FROM "User" WHERE  "companyId" = 200001
                    )
                    ORDER BY Prop."userId" ASC LIMIT 10 OFFSET 0

Ожидание: раннее отсечение limit 10 и nested loop именно от них.

Вы с какой-то очень не той стороны хотите подойти к задаче. Вопрос на который надо искать ответ - зачем вы это хотите?

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

Впрочем, event triggers могут быть ddl_command_start для ряда create команд.

Но первичный ключ является обязательным для каждой таблицы

С точки зрения реляционной теории и нормальных форм в частности.

С точки зрения SQL - не обязателен. Таблица может не объявлять первичный либо даже какой-то уникальный ключ. Дальше - детали реализации.

В PostgreSQL никакой даже суррогатный первичный ключ в этом случае создаваться не будет. Адресация строк в PostgreSQL выполняется по TID (tuple ID) - физическому адресу строки в датафайле (и потому TID уникален в таблице), в том числе при поиске по первичному ключу. Но TID - не индекс, никак не связан с хранимыми данными и не накладывает каких-либо ограничений на данные.

Динамические (вычисляемые) колонки в mysql есть начиная с 5.7 ветки: https://dev.mysql.com/doc/refman/5.7/en/create-tab...

Бывают virtual - вычисляются при обращении, бывают stored - вычисляются перед записью и хранятся на диске.

vacuum - запустить vacuum. В нём нет ничего страшного, если вы не отстрелили себе ноги, то база сама его запускает согласно настройкам.

vacuum full - история очень сильно отдельная, т.к. это ни разу не vacuum.
Места под vacuum full надо под копию таблицы с индексами и на запись этой копии в WAL. То есть теоретически до двукратного объёма.
Поскольку для рабочей базы возможность уйти на сколько-то часов штука неинтересная - vacuum full по большим таблицам не делают.
Есть pg_repack делающий с помощью триггера и чёрной магии копию таблицы (место всё так же нужно свободное), после накатывает изменения в основной таблице и заменяет основную на свежую копию. В целом - то что делает vacuum full, но без тяжёлой блокировки таблицы.
Есть pgcompacttable, идущий другим путём. postgresql пытается новые строки размещать в начале таблицы, поэтому фиктивные обновления строк в конце таблицы скорей всего переместятся в начало, а пустые страницы в конце может срезать обычный vacuum. Плюс перестроить индексы (правда не все). Обновление inplace, запас места под копию не требуется. Полную блокировку таблицы конечно тоже не берём.

Какого индекса? Возможно сюрприз, но они сильно разные.

1) PSQL создает индекс в одно-поточном режиме. Это 10% от всей производительности cpu.

psql - это клиент, он вообще не создаёт индексы.
postgresql - начиная с 11 версии умеет многопоточное создание btree. Хотя, наверное, вернее будет сказать сортировку.

2) Как я понял он считывает базу перерабатывает и сжимает. (В одно-поточном режиме это происходит оч медленно.)

Не базу, а таблицу. Один раз полный seqscan для create index (с блокировкой записи) или два seqscan для create index concurrently (без блокировки таблицы).
Ничего не сжимает.

3) Из-за того что он медленно перерабатывает данные, он не использует всю скорость ссд на котором расположена база.

Ну смотря какие диски. Дешёвые SSD вполне можно озадачить временными файлами в большом количестве и одним ядром CPU, видел и такое.

Для ускорения построения большого индекса нужна память maintenance_work_mem. Сколько не жалко. 10, 20, 30, 100гб.
На postgresql 11 - плюс настроить число воркеров max_parallel_maintenance_workers, max_worker_processes, max_parallel_workers

но не нашел как их реализовать

Если хотите поучаствовать в разработке самого postgresql - welcome в список рассылок pgsql-hackers. Как делали parallel create index можно почитать по ссылкам с коммита.

Скопируйте весь /var/lib/mysql/ и запустите базу, лучше той же major версии, поглядывая за логом.

where 'ct_head_short' like template
seqscan ожидаемо, но для малого числа строк работать будет