Melkij

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

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

С точки зрения 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

Но в postgresql есть есть тип serial

неа, нет его.
Это именно синтаксический сахар вокруг create sequence + integer с DEFAULT nextval('users_id_seq'::regclass)
Посмотрите в pg_dump, не найдёте ни одного serial

вручную изменить тип поля на serial?

alter table foo alter COLUMN i type serial;
ERROR: type "serial" does not exist

А вот не сможете, нет такого типа данных.

2. unsigned
Оказывается его нет в postgresql. Заменить на CHECK (имя_поля >= 0)? Или ещё какие варианты (если есть, то чем они лучше)?

Да, unsigned типов нет. Можно check к полю добавлять, можно сделать create domain сразу с check и использовать имя этого домена в качестве типа данных.

И опять-таки в миграциях лары нет никакого check()

Я думаю в этих миграциях нет почти всего, даже из SQL92 стандарта. Утилиты миграций за исключением тех, что дают выполнять произвольный SQL, обычно ничего не умеют из возможностей базы, стоит лишь чуть копнуть вглубь.

3. массивы
Для mysql использовала string и туда записывала данные в виде json. Для postgresql пока выбрала прямо json. В миграциях он доступен. Или стоит выбрать тип массива? В чём разница между массивами и json в postgresql?

Есть json. В json бывают массивы, бывают объекты, скаляры.
Есть отдельно массивы в postgresql как отдельные типы данных. Вы можете сделать массив из json документов.
Разница между json и массивами - разные наборы функций, операторов.

Что-то laravel, как я пока вижу, мало адаптирован к работе с postgresql.

Раз для mysql вместо json используется string (text видимо всё-таки) - то и для mysql не очень адаптирован. Ну или у вас mysql какой-то очень сильно древний.
Такие конструкторы обычно адаптированы под людей, которые не знают и знать не хотят свою базу.

create table new_city (like city including all);
insert into new_city select * from city;

Если условия позволяют, то быстрее будет сдампить, переименовать и записать обратно.
pg_dump -t city -Fc -Z0 -f reimport.pgdump
alter table city rename to new_city;
pg_restore -f reimport.pgdump
Быстрее на индексах, которые лучше строятся после записи данных.

А чтобы сразу склонировать таблицу - такого вроде нет, очень узкий usecase