Instances, Databases, Schemas and Roles: Remapping Your Mental Model

Previously, we looked at why teams move from SQL Server to PostgreSQL and got a local Postgres running. Now the real translation work begins — with the containment model, where the words look familiar but two of them mean something different.

The hierarchy, side by side

The big shocker: no cross-database queries

In SQL Server, one connection to an instance can reach every database on it with a three-part name: SELECT * FROM CrmDb.dbo.Customers works from anywhere. In PostgreSQL, it does not. A connection is to exactly one database, and databases within a cluster are isolated from each other. There is no three-part hop across them.

If your team uses databases as namespaces — Sales, Billing, Reporting on one instance, joined freely — this is the habit to rethink. In Postgres, the unit of grouping inside one database is the schema. The same design becomes one database with sales, billing, and reporting schemas, and now SELECT * FROM billing.invoices JOIN sales.orders ... works exactly as you'd hope. (A foreign data wrapper, postgres_fdw, can reach into another database — but treat it like a linked server, not everyday syntax.)

search_path: your default schema, pluralized

SQL Server gives each user a single default schema. PostgreSQL gives each session a search path — an ordered list of schemas to check when you use an unqualified name. The default is:

SHOW search_path;
-- "$user", public

SET search_path TO sales, public;
SELECT * FROM orders;   -- finds sales.orders first, then public.orders

Unqualified CREATE statements put objects in the first schema on the path. It's a small feature, but it's how multi-schema databases stay pleasant to query.

Logins and users become just: roles

SQL Server's two-level model — a server login mapped to a database user, plus roles on top — collapses into a single concept in PostgreSQL. Everything is a role. A role can log in (that makes it what you'd call a login), can own objects (a user), and can contain other roles (a group). One concept, three hats:

-- a group role that owns the application's objects
CREATE ROLE app_owner;

-- a role that can actually connect
CREATE ROLE app_service LOGIN PASSWORD 'change-me';

-- membership does what AD groups did for you
GRANT app_owner TO app_service;

Grants and permissions then work much as you're used to — GRANT SELECT ON ... TO role reads the same in both dialects. And if your organization leans on modern auth: PostgreSQL 18 added OAuth support, alongside the usual password and certificate methods.

Where did GO go?

Nowhere — it was never part of T-SQL to begin with. GO is a batch separator understood by SSMS and sqlcmd, not by SQL Server itself. Postgres tools don't have an equivalent because they don't need one: psql and every driver simply send statements ended with a semicolon, and you group them with plain BEGIN; ... COMMIT; when you want a transaction.

Here's the delightful part: in PostgreSQL, DDL is transactional. You can BEGIN, create three tables, alter a fourth, realize you got it wrong, and ROLLBACK the whole thing cleanly. Migration scripts become dramatically less scary.

The identifier gotcha you must know on day one

PostgreSQL folds every unquoted identifier to lowercase. Write CREATE TABLE Customers (...) and the table is stored as customers. That's harmless on its own — SELECT * FROM Customers also folds, so it still finds the table. The trouble starts with double quotes:

CREATE TABLE "Customers" (Id int);   -- stored exactly as: Customers

SELECT * FROM Customers;             -- ERROR: relation "customers" does not exist
SELECT * FROM "Customers";           -- works, and always requires the quotes

The advice from everyone who has been through this: don't fight the fold — embrace snake_case. Name things order_items, not OrderItems, and quoting never enters your life. This is exactly why the EF Core ecosystem around Postgres standardized on snake_case naming conventions, which we'll set up painlessly in Part 8.

Your remapped mental model

Cluster instead of instance, schemas doing the namespacing work you used databases for, one role concept instead of logins-plus-users, no GO, and snake_case as a way of life. With the containers sorted, we can talk about what goes in them: in Part 3 we build the data type translation table — including the one type decision responsible for most migration bugs.