4 ms·

Wait until you hear what "cluster" means in Postgres-speak.

by throwaway894345 5y ago

jelder 5y ago
Please go on.
- throwaway894345 5y ago
  > PostgreSQL uses the term cluster to refer to a “cluster” of databases, as opposed to the usual notion of a group of servers or VMs working in a co-ordinated fashion. https://www.opsdash.com/blog/postgresql-cluster.html https://www.opsdash.com/blog/postgresql-cluster.html Note that the "databases" above are logical databases, not database hosts.
  - pcthrowaway 5y ago
    So according to this usage, one database instance with a few databases would be a cluster?
    - throwaway894345 5y ago
      I'm not an expert, but as far as I can tell, even a single database instance with a single database (or maybe no databases at all?) could constitute a "cluster".
  - snthpy 5y ago
    Thank you for this. I didn't realise this and for the past few years have been thinking that I seem to be the only one that runs only a single database host as everyone else seems to have "clusters". I never realised this just means multiple databases.
- pdpi 5y ago
  A Postgres cluster is, roughly speaking, a server instance. That is, if you run two copies of postgres on the same box (One on the default 5432 port, another on, say, 6543) and have each of those copies manage its own independent config, data, etc, then those instances are what Postgres calls a cluster.
- munk-a 5y ago
  Clustering is, besides all the other definitions you're seeing here, a table property. A clustered table is a table that has, on disk, been aligned with a certain index[1] - each table can only be clustered to a single index and it essentially means that row retrieval for that specific index is much more efficient, it basically gets you one covering index for free. 1. https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html
munk-a 5y ago
Or schema!
- pdpi 5y ago
  Hmm, care to expand? The word schema, in Postgres terms, seems to mean exactly what I expect it to.
  - jrockway 5y ago
    I think people think schema means "a list of tables and indexes" but it's an actual object in postgres ("DROP SCHEMA public"). These do not diverge too far; that's what the object represents of course.
    - throwaway894345 5y ago
      Most people think schema means "shape/structure of data", not "list of tables and indexes". In Postgres (or maybe SQL more broadly) it roughly means "a container for tables".
      - chousuke 5y ago
        Not just tables, but everything else as well. The shape and structure of your data is always defined within a schema, and a single database may have more than one. A PostgreSQL server can contain multiple databases; they are independent and you can't access data in one database while connected to another (without dblink or something similar) As far as I know pretty much every database (and the SQL standard) except MySQL has schemas as an explicit database object and calls them that. What MySQL calls "databases" are actually schemas; they're just containers for database objects and you can query across them (and CREATE SCHEMA is an alias for CREATE DATABASE) EDIT: There's a fun trick you can do with multiple schemas that illustrate why they are schemas and not just "containers of things" You have a "data" schema that contains your table definitions; your actual, real data and indices etc. go here. Only privileged users can access this schema directly. Then you have an "interface" schema, that contains views and functions used by people; they can refer to the data schema, and with some clever view definitions, you can do it such that they can only access the data using the views and functions in your interface schema. At some point, you could create an "interface_v2" schema that provides better (or more) methods for accessing your data that's backwards incompatible. Old applications can continue using the "interface" schema by setting their schema search path to "interface" (which would be the default), but new applications can "overlay" the schemas by setting their search path to "interface_v2,interface" and opt-in to new functionality. The "structure" of your data is changed simply by opting in to the new schema. It's pretty rare for people to do this (they understand versioned web APIs better than versioned database APIs), but it's a thing you can do.
legulere 5y ago
The same as in Microsoft SQL Server? https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/in...
- throwaway894345 5y ago
  No doubt lots of relational database software share this quirk.
- SigmundA 5y ago
  No unfortunately PG does not have clustered indexes much to my dismay.

Wait until you hear what "cluster" means in Postgres-speak.

jelder 5y ago

Please go on.

throwaway894345 5y ago
> PostgreSQL uses the term cluster to refer to a “cluster” of databases, as opposed to the usual notion of a group of servers or VMs working in a co-ordinated fashion. https://www.opsdash.com/blog/postgresql-cluster.html https://www.opsdash.com/blog/postgresql-cluster.html Note that the "databases" above are logical databases, not database hosts.
- pcthrowaway 5y ago
  So according to this usage, one database instance with a few databases would be a cluster?
  - throwaway894345 5y ago
    I'm not an expert, but as far as I can tell, even a single database instance with a single database (or maybe no databases at all?) could constitute a "cluster".
- snthpy 5y ago
  Thank you for this. I didn't realise this and for the past few years have been thinking that I seem to be the only one that runs only a single database host as everyone else seems to have "clusters". I never realised this just means multiple databases.
pdpi 5y ago
A Postgres cluster is, roughly speaking, a server instance. That is, if you run two copies of postgres on the same box (One on the default 5432 port, another on, say, 6543) and have each of those copies manage its own independent config, data, etc, then those instances are what Postgres calls a cluster.
munk-a 5y ago
Clustering is, besides all the other definitions you're seeing here, a table property. A clustered table is a table that has, on disk, been aligned with a certain index[1] - each table can only be clustered to a single index and it essentially means that row retrieval for that specific index is much more efficient, it basically gets you one covering index for free. 1. https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html

munk-a 5y ago

Or schema!

pdpi 5y ago
Hmm, care to expand? The word schema, in Postgres terms, seems to mean exactly what I expect it to.
- jrockway 5y ago
  I think people think schema means "a list of tables and indexes" but it's an actual object in postgres ("DROP SCHEMA public"). These do not diverge too far; that's what the object represents of course.
  - throwaway894345 5y ago
    Most people think schema means "shape/structure of data", not "list of tables and indexes". In Postgres (or maybe SQL more broadly) it roughly means "a container for tables".
    - chousuke 5y ago
      Not just tables, but everything else as well. The shape and structure of your data is always defined within a schema, and a single database may have more than one. A PostgreSQL server can contain multiple databases; they are independent and you can't access data in one database while connected to another (without dblink or something similar) As far as I know pretty much every database (and the SQL standard) except MySQL has schemas as an explicit database object and calls them that. What MySQL calls "databases" are actually schemas; they're just containers for database objects and you can query across them (and CREATE SCHEMA is an alias for CREATE DATABASE) EDIT: There's a fun trick you can do with multiple schemas that illustrate why they are schemas and not just "containers of things" You have a "data" schema that contains your table definitions; your actual, real data and indices etc. go here. Only privileged users can access this schema directly. Then you have an "interface" schema, that contains views and functions used by people; they can refer to the data schema, and with some clever view definitions, you can do it such that they can only access the data using the views and functions in your interface schema. At some point, you could create an "interface_v2" schema that provides better (or more) methods for accessing your data that's backwards incompatible. Old applications can continue using the "interface" schema by setting their schema search path to "interface" (which would be the default), but new applications can "overlay" the schemas by setting their search path to "interface_v2,interface" and opt-in to new functionality. The "structure" of your data is changed simply by opting in to the new schema. It's pretty rare for people to do this (they understand versioned web APIs better than versioned database APIs), but it's a thing you can do.

legulere 5y ago

The same as in Microsoft SQL Server? https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/in...

throwaway894345 5y ago
No doubt lots of relational database software share this quirk.
SigmundA 5y ago
No unfortunately PG does not have clustered indexes much to my dismay.