MySQL databases vs PostgreSQL schemas: what maps to what

Everybody coming to PostgreSQL from MySQL asks this within the first hour, and the confusion is not their fault. Both products use the word “schema” and they do not mean the same thing by it.

MySQL has two levels. PostgreSQL has three.

MySQL          server  →  database  →  table
PostgreSQL     cluster →  database  →  schema  →  table

The trap is assuming database maps to database. It does not. A MySQL database is a PostgreSQL schema. Everything below follows from that one line.

In MySQL, SCHEMA and DATABASE are the same keyword

Not similar. The same. You can create one with either word and the other will show it:

CREATE SCHEMA shop_a;
CREATE DATABASE shop_b;
SHOW DATABASES;

Both appear in the list. information_schema calls them schemata, which is where some of the confusion starts:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name IN ('shop_a', 'shop_b');

There is no way to nest one inside the other, and this is the part that surprises people. Selecting a database first does not make the next CREATE SCHEMA land inside it:

USE shop_a;
CREATE SCHEMA inner_one;
SHOW DATABASES LIKE 'inner_one';

inner_one is a sibling of shop_a, not a child. There is no level to nest into.

In PostgreSQL, a schema lives inside a database

Here the two words mean two different things, and both exist at once:

CREATE SCHEMA sales;
CREATE SCHEMA billing;

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name IN ('sales', 'billing');

Those are containers inside the database you are connected to. Create a table and the full name has three parts, database.schema.table, though you will rarely write the first one.

Which is why cross-database joins work in one and not the other

This is the practical consequence, and the reason the mapping matters rather than being trivia.

In MySQL, two databases on one server are namespaces. Qualify the table name and join them in a single query:

CREATE DATABASE crm;
CREATE DATABASE billing;

CREATE TABLE crm.customers (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE billing.orders (id INT PRIMARY KEY, customer_id INT, total INT);

INSERT INTO crm.customers VALUES (1, 'Acme');
INSERT INTO billing.orders VALUES (10, 1, 500);

SELECT c.name, o.total
FROM crm.customers c
JOIN billing.orders o ON o.customer_id = c.id;

That returns a row. In PostgreSQL the equivalent is refused before it runs, and the error says so plainly:

ERROR:  cross-database references are not implemented: "otherdb.public.t"

A PostgreSQL connection is attached to exactly one database and cannot see into another. Reaching across needs postgres_fdw or dblink, which are extensions, a connection to the other database, and a deliberate decision. It is not a qualified name away.

Joining across PostgreSQL schemas is free, exactly like joining across MySQL databases, because that is the level they actually correspond to.

So if you are moving between them

MySQL to PostgreSQL. Your databases become schemas inside one PostgreSQL database, not separate databases. Do it the other way and every join you rely on stops working. If they genuinely are separate applications sharing a server, separate databases is right, and you should expect to lose the joins.

PostgreSQL to MySQL. Your schemas become databases. The three-part name flattens to two. Anything relying on search_path needs another approach, since MySQL resolves unqualified names against the one database USE selected, and there is no search path to fall back through.

Reading either. information_schema.schemata and information_schema.tables exist in both and mean the container above tables in both. It is one of the few places the vocabulary lines up.

The one-line version

A MySQL database is a PostgreSQL schema. A PostgreSQL database is a level MySQL does not have.