Cluster, Cluster, Schema, Schema

June 04, 2012

There are 500k to 1-million words in the English language, so you would think that Postgres would be able to find a unique word for every aspect of the database, but unfortunately, that is not true. There are two cases where Postgres uses a single word to mean two different things, sometimes leading to confusion.

The first word is "cluster". One meaning represents the data directory and postmaster that controls it, e.g. a Postgres instance. One install of Postgres binaries can be used to create several Postgres clusters on the same machine. The second usage of "cluster" is as an SQL command called cluster, which reorders a table to match an index (to improve performance). The good news it that the meaning of cluster is usually clear from the context.

The same is not true of our second case, "schema". Traditionally, when someone talks about a "database schema", they are referencing table or object structure defined using a data-definition language (ddl) command, e.g. create table. The second use of "schema" is to define a namespace to contain objects, e.g. create schema. (I have already covered the value of using such schemas.)

The double-meaning for the word "schema" is often more problematic than for "cluster". For example, the pg_dump manual page uses both meanings of the word "schema":

 

 


-N schema
--exclude-schema=schema
Do not dump any schemas matching the schema pattern.
 
-s
--schema-only
Dump only the object definitions (schema), not data.

 

 

Tell me that is not confusing! One hint that we are talking about two different meanings for "schema" here is that the first one defaults to -N, as a reference to "namespace". Internally, Postgres calls schema containers "namespaces", e.g. system table pg_namespace holds the list of schema containers.

So, if you are having a conversation, and you hear the words "cluster" or "schema", don't be surprised if you get a little confused — hopefully things soon get clarified and the conversation can continue. (Two more words for the Postgres drinking game?)

Share this