Logical Structure
In Physical Structure, we looked at PostgreSQL components from the perspective of space and files. In this post, we look at the things users actually manage: objects, schemas, databases, tablespaces, and clusters.
Below is a diagram of PostgreSQL's logical structure. We go through it in order, from the lowest-level concept, the object, up to the top-level element, the cluster.
Object
An object is a data structure used to store or reference data. It includes familiar items such as tables, indexes, stored procedures, sequences, and views. The table below compares them with the objects commonly used in Oracle.
OBJECT TYPE | Supported | Notes |
FUNCTION | Oracle, PostgreSQL | ㅤ |
INDEX | Oracle, PostgreSQL | PG: supports BRIN, GIN, GiST, and SP-GiST indexes |
INDEX PARTITION | Oracle, PostgreSQL | Local partitions supported since version 11 |
JOB | Oracle | Available as an extension in PG (pg_cron, pgAgent) |
MATERIALIZED VIEW | Oracle, PostgreSQL | ㅤ |
PACKAGE | Oracle | Can be replaced with a function in PG |
PACKAGE BODY | Oracle | Can be replaced with a function in PG |
PROCEDURE | Oracle | Can be replaced with a function in PG |
SCHEDULE | Oracle | Available as an extension in PG (pg_cron, pgAgent) |
SEQUENCE | Oracle, PostgreSQL | NOCACHE option not supported in PG |
SYNONYM | Oracle, PostgreSQL | ㅤ |
TABLE | Oracle, PostgreSQL | ㅤ |
TABLE PARTITION | Oracle, PostgreSQL | Supported since version 10 |
TRIGGER | Oracle, PostgreSQL | Can be replaced with a function in PG |
TYPE | Oracle, PostgreSQL | Can be replaced with a function in PG |
VIEW | Oracle, PostgreSQL | ㅤ |
XML | Oracle, PostgreSQL | ㅤ |
Schema
In PostgreSQL, a schema is a collection of objects. A database can have many schemas, and among them the public schema is the default schema, on which all privileges, including CREATE, are granted.
Schemas have the following characteristics.
- The schema owner can control access to the schema as needed.
- Ownership can be transferred.
- Objects with the same name (tables, functions, etc.) can exist in different schemas.
📌 This may be an unfamiliar concept for people used to Oracle. In Oracle, schema and owner are combined, so owner = schema. In PostgreSQL, however, a schema is simply a collection of objects and has nothing to do with user accounts such as roles, users, or owners.
Database
In PostgreSQL, a database is a collection of schemas. Unlike other DBMSs, PostgreSQL strictly separates databases and schemas. So, unlike Oracle, where the user (owner) and the schema mean the same thing, in PostgreSQL you connect in the order database → schema and build objects in that schema.
Characteristics
- When PostgreSQL is initialized (initDB), 3 databases are created: template0, template1, and postgres. template0 and template1 are template databases for creating user databases and contain the system catalog tables.
- Right after initDB, template0 and template1 have the same list of tables. However, users can create objects in the template1 database as needed.
- If you create a database without a specific
templateoption, it is created by copying the template1 database.
📌 template1 database : the default database referenced when a database is created
template0 database : the database used when something goes wrong with template1
Role
A role is a named collection of database-related privileges provided by PostgreSQL. Up to version 8.0, accounts and privileges were managed with users and groups, but since version 8.1, the concepts of user and group have been merged into roles.
In other words, in current versions there is no functional difference between a user and a role, as in the example below, and a role granted the
LOGIN privilege can be used like a regular user account.📌 CREATE USER section of the PostgreSQL 8.1 documentation
CREATE USER defines a new database role and is now (PostgreSQL 8.1 and later) an alias for CREATE ROLE. The difference is that CREATE USER includes the LOGIN option, while CREATE ROLE does not.
CREATE GROUP section of the PostgreSQL 8.1 documentation
CREATE GROUP defines a new database role and is now (PostgreSQL 8.1 and later) an alias for CREATE ROLE.
Characteristics
- Roles are shared at the cluster level, not per database.
- Roles can own objects (tables, functions, etc.) and manage privileges on objects.
- Before dropping a role, the objects it owns must be handled first (dropped or reassigned to another owner).
- The types of role privileges are as follows.
Role Type | Description |
SUPERUSER | Includes all roles except LOGIN |
LOGIN | Role for connecting to the database |
CREATEDB | Role for creating databases |
CREATEROLE | Role for creating, altering, and dropping roles |
REPLICATION | Role for using replication |
PASSWORD | Sets the password |
CONNECTION LIMIT | Sets the number of connections |
INHERIT | Inherits the privileges granted to the role |
BYPASSRLS | Role that bypasses the row security system |
Tablespace
A tablespace defines the file system location where objects (tables, indexes, etc.) are stored and is generally used for space management. Also, unlike in Oracle, a PostgreSQL tablespace can be shared by multiple databases.
By default, PostgreSQL has the following 2 default tablespaces.
- pg_default : stores all user data (location : $PGDATA/base)
- pg_global : stores all global (cluster) data (location : $PGDATA/global)
📌 For the tablespace directory structure, see Physical Structure.
Besides these default tablespaces, you can also use user tablespaces, and you can specify
OWNER and LOCATION when creating them. In this case, the tablespace's actual data is located at the LOCATION path, and a symbolic link pointing to the LOCATION is created under $PGDATA/tblspc.Characteristics
- The user (role) that creates a tablespace is its owner.
- You can assign ownership by specifying a name in the OWNER clause when creating a tablespace.
- The path defined in the LOCATION clause when creating a tablespace is where the actual data is stored, so that file system must be owned by PostgreSQL's system user (OS user). (read/write permission)
- You can optimize performance by placing objects on different storage according to their usage patterns. For example, you can put frequently used objects in a tablespace on a fast disk such as an SSD, and rarely used objects in a tablespace on a slower disk.
📌 A tablespace maps to a single file system location. On the other hand, a single file system can hold multiple tablespaces.
Cautions
- A tablespace depends on metadata in the data directory, so it cannot be attached to another database cluster or backed up separately.
- If a tablespace fails (deleted files, disk failure), the database cluster stops working.
Cluster
In PostgreSQL, a cluster is a collection of databases, roles, and tablespaces. A cluster contains multiple databases that are isolated from one another. When PostgreSQL is first installed, the postgres, template0, and template1 databases are created by default, and this collection of databases is called a cluster.
- Collection of objects → schema
- Collection of schemas → database
- Collection of databases + roles + tablespaces → cluster
📌 In Oracle, a cluster refers to the shared-node concept of a RAC environment: multiple nodes that look at one storage, forming an Active-Active environment through Oracle Grid Infrastructure (clusterware software), and the shared nodes built through the clusterware are called a cluster.
Related articles
