Melkij

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

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

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 - не индекс, никак не связан с хранимыми данными и не накладывает каких-либо ограничений на данные.

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 можно почитать по ссылкам с коммита.

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

А где тут ошибки нет?
В теге указан postgresql, где синтаксис create database вот такой: https://www.postgresql.org/docs/current/sql-create...

А ваш запрос как раз начиная со слова on никак не похож и явно от какой-то другой СУБД.

Что такое include?

чтобы в найденные объекты Lead добавлялись поля Unit, основываясь на unitId.

Банальный join.

Аккаунт ОС не имеет никакого отношения к пользователю БД. База может быть настроена проверять подлинность пользователя по соответствию имени пользователя - peer для unix sock. Но это не обязательно и это единственное что может связывать пользователя ОС и базу. (кроме учётки владельца, у того есть доступ напрямую к файлам)

Но пользователь базы должен уже существовать. А раз вам нужно создать сперва пользователя - то тогда же создайте и базу для него.

Далее следует сказать, что рядовой пользователь вовсе не может создавать базы данных. Это право должно быть выдано явно.
Подключиться к postgresql можно только к конкретной уже существующей БД и у вашего пользователя должны быть права как на прохождение аутентификации указанным в hba методом так и права на подключение к нужной базе.

archive_command = '/usr/local/bin/backup_wal.sh'

cat /usr/local/bin/backup_wal.sh
#!/bin/bash

. /etc/wal-g.d/server-s3.conf

wal-g backup-push /var/lib/pgsql/9.6/
wal-g backup-list

Аааа?... Это вы сейчас серьёзно?

Не будет это работать. archive_command вызывается на каждый сегмент WAL - 16мб изменений обычно. То есть легко десятки раз в секунду на нагруженных базах. Пытаться отсюда делать бекап кластера - идея ужасная.

wal-push в archive_command должен быть. backup-push отдельно, из крона обычно.

Не надо изобретать кривой велосипед с очередной очередью. Оно не будет работать под нагрузкой. Возьмите pgq.

Ну а если всё равно хотите пособирать эти грабли:

update tablename set status = 1
where status = 0 and id in (
select id from tablename where ..... order by ... limit ... for update
)
returning ...

select sum(pg_catalog.pg_database_size(d.datname)) from pg_catalog.pg_database d;

Вот только полный размер кластера не то же самое что сумма размера всех БД. Размер кластера считается как du -s от $PGDATA