Melkij

Вникаете в предметную область той задачи, для которой эта таблица используется. Всё, ответ найден.

Нет ничего удивительного в таблицах, у которых нет поля id. В предметной области может быть достаточно хороший естественный ключ.

С точки зрения производительности - да, конечно, есть смысл не хранить ненужные данные. Меньше строка => меньше таблица => в тот же объём RAM влезает больше кеша и меньше IO. Немного, правда, чистыми 8гб на каждый миллиард строк получается всего для bigint. Но если поле больше ни для чего не нужно, то почему бы и нет?

Вы смотрите на совершенно бесполезный график. Смотрите на полезные.

Если у вас табличка с 10 индексами и к ней select * from foo where id = ? - то это минимум 11 AccessShareLock. Совершенно логично, что чем больше параллельно нагрузки - тем больше вы увидите AccessShareLock. И дальше куда с этим? Вот и получается совершенно бесполезный график. А задирание max_connections сделает только хуже.

Посмотрите график CPU, есть ли в принципе CPU свободный (помним что у вас наверняка есть HT, значит 100% утилизации недостижимы, реальный потолок окажется где-то в районе 70-90%)
Посмотрите графики латентности IO. Если проседает латентность чтения или записи под нагрузкой - то это будет причина замедления. СУБД очень чувствительны к латентности.
Дальше графики pg_stat_activity с разбивкой по state. Если растут idle in transaction - проверять как дела на приложении, а так же сеть.
Конечно, графики по pg_stat_statements. top5 запросов по времени выполнения, для начала.
И проверить наличие корреляции с графиком длительности самой старой транзакции.

Аутентификация через метод md5 выполнит парольную проверку как для пароля сохранённого с password_encryption = md5 (ныне уже deprecated, но встречается в большинстве баз, созданных не недавно), так и пароли сохранённые в scram-sha-256. Реализацию pg_hba md5 специально расширили при появлении scram-sha-256 таким образом, для облегчения миграции.

Указание scram-sha-256 в pg_hba будет требовать именно пароль согласно scram-sha-256 и не будет принимать пароли старых учётных записей, созданных с md5 encryption.

Вполне вероятно, что у вашей базы password_encryption сконфигурирован в scram-sha-256, все пользователи кто имеет пароли, так же в scram-sha-256 и нужды в md5 аутентификации в pg_hba нет.

Блокировки строк и блокировки таблиц (и прочих объектов) - два разных механизма. (а serializable изоляция и вовсе частично третий)

Блокировки строк отмечают информацию о себе специальными флагами в заголовке самой заблокированной строки. Запросы, которым нужна блокировка строки сначала проверяют, не отмечена ли эта строка блокировкой, затем проверяют что сейчас с той транзакцией, идентификатор transaction ID которой указан в информации о блокировке. Если транзакция ещё в работе - то начинаем ждать её завершение (или пропускаем строку для skip locked или кидает ошибку если nowait)
Блокировки строк, будь то select for share/for update или пишущие запросы, всегда работают с самой актуальной версией строки.

В то же самое время, любой запрос, трогающий таблицу (даже select) берёт блокировку на эту таблицу соответствующего своим потребностям уровня. Пока не возьмёт блокировку на таблицу - вообще не начнёт выполняться. Информация об этих блокировках размещается в сегменте shared memory памяти, потому доступны всем процессам базы. Если другие бекенды не держат блокировку таблицы конфликтующую с желаемым нашей транзакцией (или ещё только ждут блокировку более высокого уровня), то запрос блокировки удовлетворяется и работа продолжается.

должно быть

postgres=# create database mydb;

нет такого сокращение у парсера как create db, но, что важнее, SQL запрос должен завершаться символом ;

Приглашение командной строки потому и меняется с postgres=# на postgres-# как индикатор того, что psql ожидает дальнейший ввод многострочного SQL, пока не встретится ;
Метакоманды psql, в то же время, действительно продолжают обрабатываться во время многострочного ввода.

https://www.postgresql.org/docs/current/sql-syntax...
соответствовать синтаксису идентификаторов.

select distinct on (created_at::date) created_at, value from tablename order by created_at::date, value desc;

См. IEEE 754 потому что это именно он. И на всё наблюдаемое "странное" поведение типов с плавающей запятой ответ един - потому что IEEE 754.

Я выполняю 2 одинаковых запроса

"WHERE friends_info.user_id = 1" vs "WHERE friends_info.friend_id= 1"

о да, одинаковые, да как бы не так. Полностью разные запросы, естественно, могут иметь различные оптимальные планы выполнения.

Bitmap Index Scan on ix_friends_user_id (cost=0.00..61.56 rows=5465 width=0) (actual time=0.742..0.743 rows=88 loops=1)

Суть ошибки выбора плана. Какое распределение данных в таблице? Каков размер самой таблицы? Вероятно несколько пользователей занимают значительную часть таблицы и это сбивает оценку селективности.
Простое чуть приподнять SET STATISTICS по полю, собрать новый analyze и посмотреть на оценку числа строк.

PS: индекс ix_friends_user_id должен быть удалён как бесполезный при наличии friends_info_user_id_friend_id_key

А как между собой связаны postgres, пароль пользователя unix и способ авторизации ssh? Да никак.

ssh аутентификация по ключу не исключает наличия пароля пользователя, sudo может быть настроен без пароля, авторизация postgresql может быть настроена без sudo раз вы как-то собрали и запустили СУБД без использования запароленного sudo этого пользователя.

Проблема-то где?

А вы как думаете?

Бекапы делаются так, насколько это необходимо. От "никогда" (действительно существуют базы, потеря которых неважна или данные могут быть восстановлены за удовлетворительное время из других источников) до "непрерывный PitR с синхронным подтверждением архивации WAL" (когда приложение получит ответ на запись транзакции данных только когда этот WAL окажется уже в бекапе)

Может быть допустимо в случае чего потерять данные до двух месяцев, может быть вам критично потерять данные уже за час - это вопрос именно к потребностям и оценки рисков конкретного сервиса.

Аналогично с вопросом "за какой срок хранить бекапы" - за тот, который нужен и согласны оплачивать (сотня террабайт под бекап как-то не очень бесплатные).

Когда появляются требования ко времени восстановления из бекапа - то почти наверняка уже доросли до полноценного PiTR и лучше рассматривать именно полновесный бекап postgresql, чем сооружать снэпшоты.
Берёте pgbackrest, настраиваете архив WAL, желаемую периодичность снятия basebackup и сроки удержания. Ну либо barman или wal-g, тут по вкусу, смысл тот же.

Получаете полностью корректный с точки зрения самой базы снэпшот (ака basebackup), а так же возможность восстановиться на произвольную точку времени, а не только на момент снятия слепка. Без штрафа производительности записи от снэпшота LVM, без CoW файловых систем.

Реплика - не бекап, решают разные задачи. Реплика ответит на вопрос "как быстро мы можем восстановить базу при полном отказе основного сервера", но не ответит на вопрос "как быстро можем восстановить удалённую табличку". Если у вас важная база и всё ещё нет реплики - ну, это странно =)