4 ms·
Hm, question for people a bit more familiar with Postgres -- what is meant by "schema" here? My definition is "the columns and column types of a table", but, t
by pickledish 3y ago
Hm, question for people a bit more familiar with Postgres -- what is meant by "schema" here?
My definition is "the columns and column types of a table", but, that doesn't seem to make sense with what they're talking about here ("large" and "small" schemas probably aren't referring to wide and narrow tables for example, and I don't see how sharding by my definition of "schema" could even make sense anyways)
- Hovertruck 3y agoSchema is a proper noun in Postgres: https://www.postgresql.org/docs/current/ddl-schemas.html https://www.postgresql.org/docs/current/ddl-schemas.html
- mulander 3y agoSchemas 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).
- pickledish 3y agoAh, 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
- mulander 3y agoThey can be different, as long as your application can handle them being different (which makes sense for microservices as an example).
- pickledish 3y agoI see! Fascinating, thanks for the help :)
- ubertaco 3y agoI'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.
- paulddraper 3y agoNot 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.
- pickledish 3y agoAh 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 https://json-schema.org Thank you!
- hans_castorp 3y agoSchemas 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.
- pickledish 3y agoWhew 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!
- somat 3y agoI 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"