charypopper

Разница в том, что ограничения (сonstraints) призваны обеспечивать целостность данных, а индексы (index) — скорость доступа к данным. Это две абсолютно не связанные сущности. Причем если первое — часть SQL стандарта, то второе нет (ибо ни как не связанно с функциональностью языка, введение индексов — вынужденная мера). Разработчик сам решает, в каких случая применить эти механизмы и использование одного вовсе не требует использование другого.

Теперь касательно уникальности (unique). В данном случае при добавлении ограничения уникальности (unique constraint) Postgresql сам навешивает на указанное поле индекс. Это просто особенность реализации в данной СУБД. Разработчики решили, что вот так оно будет работать и все тут (причем небезосновательно). В другой же схожей ситуации они решили, что разработчик сам думает, нужно ли ему использовать этих два механизма вместе, или нет. Я говорю об ограничении целостности по внешнему ключу (foreign key). В Postgresql индексы по полям с данным видом ограничения не создаются (Индексы по внешним ключам в Postgresql). А, к примеру, в MySQL создаются. Это особенность реализации в MySQL.

Поэтому важно просто понимать, что это не связанные вещи, просто в некоторых реализациях они «сцеплены» между собой и создание некоторых видов ограничений приводит к автоматическому созданию индекса.

В целом, понимайте схемы как пространства имен. Схемы помогают логически организовать структуру БД. Различные приложения или компоненты приложений внутри себя имеют более высокую связность, чем между собой, тоже самое и с данными в БД - часто таблицы сами по себе организуются в логические группы, причем внутри группы связей довольно много (внешние ключи, какие-то общие хранимые процедуры и т.д.), а между этими группами - мало или нет вовсе.

Важно понимать, что различные БД плохо подходят для логического группирования, т.к. разбиение по базам данных нужно скорее для администраторов, а не для приложений. Плюс, в большинстве СУБД, где существует понятие схемы, возможно ставить внешние ключи на таблицы в другой схеме, но нельзя на таблицы в другой БД. Иными словами, отдельные БД удобно создавать тогда, когда вы разделяете данные абсолютно не связанных приложений или сервисов. Например, складского учета и форума поддержки пользователей. С другой стороны, если вы хотите логически разделить таблицы в соответствии с компонентами одного приложения (например, корпоративный портал: 4 таблицы для поддержки авторизации, 10 таблиц для поддержки форума, еще 5 для чата со службой поддержки или отделом продаж) - то именно схемы будут удобным механизмом для этого.

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

А что будет если несколько юзеров будут на одну public-схему коннектиться?

Помимо того, что схема - это пространство имен, в большинстве СУБД это еще и пространство безопасности. Даже в рамках одного многокомпонентного приложения имеет смысл ставить границы безопасности для ограничения возможных потерь и разрушений в случае компрометации одного из компонент.

Вот допустим, у вас есть отдельная схема для таблицы авторизации и аутентификации и отдельная - для корпоративного форума. Сервис авторизации у вас выполнен отдельно от форума (например, авторизация выдаёт токены пользователю, с которыми он потом может зайти на форум). С точки зрения безопаности было бы логичным выдать сервису авторизации и форума различных пользователей в базе - тогда, при взломе форума невозможно будет получить доступ к паролям в базе или изменить права на портале, подправив данные в таблице ролей. Конечно, многие СУБД разрешают ставить права на отдельные таблицы, однако схема в данном случае играет роль контейнера и позволяет проставить единые правила для всех таблиц внутри неё.

то есть при работе в постгре предпочтительнее вместо отдельных баз делать разные схемы в одной

Как вы уже поняли - для независимых приложений, которые могут и должны существовать отдельно друг от друга - лучше делать разные базы. Тогда администраторы смогут спокойно переносить базы с сервера на сервер, независимо их бэкапить и т.д. Для компонентов одного приложения нужно использовать схемы.

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