Understanding the hierarchy of objects within PostgreSQL can help you avoid confusion as you get to know the system and read up on documentation.
PostgreSQL’s main “global” object is a database cluster, which is just the name given to the collection of databases managed by the PostgreSQL server. A database cluster contains databases, roles, and other “global” entities.
Schemas are defined within databases as a container for tables, functions, data types, and operators. Object names must be unique within a schema, but can be reused in different schemas, which allows users to share databases without naming collisions. They also help with grouping objects or managing third-party applications by segmenting and isolating objects with the database.
Tables and other objects are created within schemas. By default, a schema called “public” is used when defining tables and other objects if an alternative schema is not named. In practice, this makes segmentation using schemas an optional practice. It is helpful to use PostgreSQL schemas in many cases, but you can ignore them if you do not need them.
Inside the cluster are multiple databases, which are isolated from each other but can access cluster-level objects. Inside each database are multiple schemas, which contain objects like tables and functions. So the full hierarchy is: cluster, database, schema, table (or some other kind of object, such as a function).
Concepts:
- database cluster: In PostgreSQL terminology, a database cluster is a collection of databases and related objects managed by a single PostgreSQL server. A database cluster is the environment managed by a PostgreSQL server.
- database: A database is an object within a database cluster that defines schemas, roles, and other objects. Since roles are defined within databases, databases are the object that users authenticate against.
- schema: In PostgreSQL, a schema is a namespace object within a database. Schemas contain tables, data types, functions, and operators. Object names must be unique within a schema, but the same name may exist in different schemas.
- table: Tables are the primary data definition structure within PostgreSQL. Tables define fields and constraints to control the type of data that may be entered. Tables store data within the structure they define as records.
- index: Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance).
- view: A view is a database object that is of a stored query.
- function: function or a stored procedure is a set of SQL and procedural commands such as declarations, assignments, loops, flow-of-control etc.
Leave a Reply