PostgreSQL Schema Design
graphile.org
PostgreSQL Schema Design
1–10 of 42 posts
Re: PostgreSQL Schema Design
#2One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
Re: PostgreSQL Schema Design
#3One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
Schemas in databases are similar to what home directories are in operating systems.
In your phrasing, a database (really a database INSTANCE) is similar to a whole server itself, and carries with it overhead such as memory allocation, etc.
When you put multiple schemas in a single instance, the resources allocated to the database instance can be shared, whereas if you have every app in its own instance, you can’t share things like working memory between them because they are in separate processes.
Re: PostgreSQL Schema Design
#4One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
Schemas in databases are similar to what home directories are in operating systems. In your phrasing, a database (really a database INSTANCE) is similar to a whole server itself, and carries with it overhead such as memory allocation, etc. When you put multiple schemas in a single instance, the resources allocated to the database instance can be shared, whereas if you have every app in its own instance, you can’t sha…
But there's a middle layer between the database instance and the schema, which is also called a "database" (great naming /s). The "foo" in postgres://u:p@host/foo . What's that? So you have database host / instance, database "...", and schema (not to be confused with the schema being the column and view etc definition...!).
Re: PostgreSQL Schema Design
#5Earlier quoted context omitted.
Schemas in databases are similar to what home directories are in operating systems. In your phrasing, a database (really a database INSTANCE) is similar to a whole server itself, and carries with it overhead such as memory allocation, etc. When you put multiple schemas in a single instance, the resources allocated to the database instance can be shared, whereas if you have every app in its own instance, you can’t sha…
But there's a middle layer between the database instance and the schema, which is also called a "database" (great naming /s). The "foo" in postgres://u:p@host/foo . What's that? So you have database host / instance, database "...", and schema (not to be confused with the schema being the column and view etc definition...!).
In the PostgreSQL world, the "database instance" is called a "cluster". A cluster contains multiple databases (and the definitions of roles and memberships and which roles can access which databases); a database contains multiple schemas; and a schema is where your tables/views/functions and other database objects live.
Re: PostgreSQL Schema Design
#6One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
It is perfectly fine to have a one-to-one mapping between schema and database, thereby treating them as interchangeable. So far as I know, by convention the schema will be the “database name” and the database name would just be “public”.
Re: PostgreSQL Schema Design
#7One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
When you have a database engineer refactoring large tables, that's when this shines.
Use CREATE TABLE AS SELECT to have cheap copying from one schema to another.
Re: PostgreSQL Schema Design
#8One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
A schema is basically a namespace within a database.
Re: PostgreSQL Schema Design
#9One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
It is perfectly fine to have a one-to-one mapping between schema and database, thereby treating them as interchangeable. So far as I know, by convention the schema will be the “database name” and the database name would just be “public”.
You have it backwards. Database is named "foo" and the default schema in PG is called "public". PG uses a schema search path. When you select from table you're really selecting from public.table. Cross schema queries like SELECT public.client.id, billing.ledger.amount FROM public.client JOIN billing.leger ON ....
Re: PostgreSQL Schema Design
#10One thing that trips me up about postgres are schemas. What is the conceptual difference between a "schema" and a "database"?
In PostgreSQL, the hierarchy is: host > cluster > database > schema > object.
By host, I mean a server. A host can have many database clusters. Usually it has just one. To have more than one, you would have to have more than one PostgreSQL instance running, each listening on a separate port. The default port is 5432. I don't recommend more than one cluster per host. I just mention it as possible.
A cluster can have many databases.
A database can have many schemas.
A schema can have many objects. By object, the most familiar is a table, but there are other kinds of objects: views, functions, custom types, sequences. An object cannot exist directly in the database. It must be part of a schema. The default schema is called "public". Traditionally in other databases, there was a schema for each user. So if jdoe logs in, his default schema is also called jdoe. In fact in other databases this is the only schema a user can have. You cannot make more schemas and name them whatever you wish.
The advantage of a schema over a database is that you can make a query that uses objects in different schemas.
select *
from schema1.table1
join schema2.table2 on table1.col = table2.col
I have gotten a long way, with many applications over many years, with one host, one cluster, one database, and many schemas.