Schemas are namespaces (actually called that internally in Postgres).

The SQL standard defines a two level namespace hierarchy. A single "instance" of contains multiple catalogs and each catalog contains multiple schemas (and each schema then contains objects like tables, views, types, functions etc).

Many database products use the term "database" instead of "catalog" e.g. in Postgres and SQL Server. But "schema" is used quite uniformly. MySQL's "databases" are in fact "schemas" though.

Whew boy, that's a more sizable hierarchy than I thought was going on under the hood lol, I guess I've got some reading to do. TY for the pointers!

I always vaguely wonder why no one allowed a full hierarchical schema path. I expect it is probably because "the standard sez one level of schema only"

Schemas are groupings of tables and other entities that can be defined within a database. You can think of them like of a namespace in programming languages. You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data.

By large and small we are referring to the amount of data each schema holds currently. They can grow over time and some of them may become very big while others will remain small (storage wise).

Ah, interesting ok

> You can have the same table definition, within the same database, defined multiple times (each in a different schema) and each holding different data.

So in this respect, each table within a schema indeed already acts like a "shard" of the overall table

Is this enforced? Like, if I create a table "messages" in schema A and a table "messages" in table B, must they have the same columns/column types, or is that just convention

They can be different, as long as your application can handle them being different (which makes sense for microservices as an example).

I see! Fascinating, thanks for the help :)

Not to be rude...but the same things as every other ANSI SQL database. Nothing PostgreSQL specific here.

Schema is the thing you get from `CREATE SCHEMA`...a namespace of tables/functions/views/etc.

Ah I see! Yeah I guess it's just a concept I've never run into before; unfortunate that it shares a name with the "other" concept of schema used by e.g. https://json-schema.org

Thank you!

Schema is a proper noun in Postgres: https://www.postgresql.org/docs/current/ddl-schemas.html

I've seen MySQL clients/docs also use "schema" to refer to "this collection of tables", basically what SQLite would call "a database".

The words get really fuzzy here when you try to get a concrete and ubiquitous definition, though.

Best I can do is to say, in these queries:

    use foo;
    select id, name from foo.people where id < 10;

..."foo" is the schema in MySQL and, apparently, Postgres parlance.