5.1 Managing Databases, Schemas & Tablespaces

5.1 Managing Databases, Schemas & Tablespaces

[!NOTE] Architectural Concept: PostgreSQL strictly decouples its logical namespace hierarchy (Cluster -> Database -> Schema -> Object) from its physical storage mapping (Cluster -> Tablespace -> Directory -> Data Files). Mastering how logical objects map to filesystem storage is a foundational competency for the PostgreSQL Associate certification and production database administration.

Efficient PostgreSQL administration requires understanding how relational data is structured from high-level logical abstractions down to raw disk blocks on the operating system storage layer. PostgreSQL provides a layered hierarchy designed for isolation, multi-tenancy, and performance optimization.

---

The PostgreSQL Storage and Logical Hierarchy

The logical structure of a PostgreSQL installation consists of four distinct levels:

+-------------------------------------------------------------------------+
|                   PostgreSQL Instance / Database Cluster                |
|                   (Single postmaster daemon, shared RAM)                 |
+-------------------------------------------------------------------------+
       │
       ├── Database A (Completely isolated catalog & OID boundary)
       │     ├── Schema: public
       │     │     ├── Table: customers
       │     │     └── Index: idx_customers_email
       │     └── Schema: sales
       │           ├── Table: orders
       │           └── View:  v_active_orders
       │
       └── Database B (Isolated catalog; cannot cross-query directly)
             ├── Schema: public
             └── Schema: analytics

1. Database Cluster

A database cluster is a single running PostgreSQL server instance (the postmaster and its background workers) managing a single data directory ($PGDATA). A cluster shares a single set of memory segments (shared_buffers, wal_buffers) and global configuration files (postgresql.conf, pg_hba.conf).

2. Database

A database is an isolated catalog boundary within the cluster. Each database possesses its own set of system catalogs (e.g., pg_class, pg_attribute). Crucially, cross-database queries within the same SQL connection are strictly impossible in standard PostgreSQL. A client backend connects to exactly one database at a time. To query across databases, administrators must deploy external foreign data wrappers such as postgres_fdw or dblink.

3. Schema

A schema is a logical namespace within a single database. Schemas allow multiple users, microservices, or functional domains (e.g., sales, hr, billing) to share the same database without table name collisions. Unlike databases, tables across different schemas within the same database can be joined together in a single SQL query seamlessly.

4. Objects

Objects are the relational entities contained inside a schema: tables, indexes, views, sequences, composite types, and stored procedures. Unqualified object names (e.g., SELECT * FROM orders;) are resolved using the session's active search path.

---

Database Provisioning and Template Mechanics

Creating a database in PostgreSQL is not simply initializing empty metadata. Instead, PostgreSQL creates a new database by physically copying the filesystem files of an existing "template" database.

Syntax of CREATE DATABASE

CREATE DATABASE production_db
    WITH OWNER app_owner
    TEMPLATE template1
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TABLESPACE fast_ssd
    CONNECTION LIMIT 100;

Template Databases: template0 vs. template1

During cluster initialization (initdb), PostgreSQL creates two system template databases:

1. template1 (Default Blueprint):

   • Serves as the default source cloned during any CREATE DATABASE statement where the TEMPLATE parameter is omitted.
   • Modifiability: Administrators can deliberately connect to template1 to install global extensions (such as pg_stat_statements or uuid-ossp), define organization-wide domain types, or create utility functions. Any object added to template1 will automatically exist in all subsequently provisioned databases.
   • Connection Lockout: To clone template1, PostgreSQL must make a clean file-level copy. Therefore, no active client connections are allowed to template1 during the execution of CREATE DATABASE. If a user or monitoring tool is connected to template1, the engine raises an error: ERROR: source database "template1" is being accessed by other users.

2. template0 (Pristine System Baseline):

   • Represents the virgin, untouched catalog snapshot generated at initdb time.
   • Golden Rule: Administrators should never modify, connect to for application workloads, or create objects inside template0.
   • Recovery Role: If template1 is accidentally corrupted or polluted with unwanted test tables, template0 can be used to restore template1 or to instantiate completely pristine databases: CREATE DATABASE clean_db TEMPLATE template0;.

-- Creating a customized template for testing
CREATE DATABASE test_template;
-- Connect and configure test_template...
ALTER DATABASE test_template WITH is_template = true;

-- Clone a new database from the custom template
CREATE DATABASE sprint_dev TEMPLATE test_template;

Terminating and Dropping Databases

A database cannot be dropped if any active sessions are connected to it, or if you are currently connected to it:

-- PostgreSQL 13+ FORCE option terminates active sessions automatically
DROP DATABASE legacy_analytics WITH (FORCE);

---

Schemas as Namespaces and search_path Resolution

Schemas organize database objects into distinct logical compartments and control visibility. Every newly initialized PostgreSQL database contains a default schema named public.

Creating and Dropping Schemas

-- Create a schema with an explicit owner
CREATE SCHEMA sales AUTHORIZATION sales_admin;

-- Create a schema only if it does not already exist
CREATE SCHEMA IF NOT EXISTS inventory;

-- Drop a schema
DROP SCHEMA sales RESTRICT; -- Default: fails if schema contains objects
DROP SCHEMA sales CASCADE;  -- Drops the schema AND all tables, views, functions inside it

[!WARNING] PostgreSQL 15+ Security Hardening on public: In PostgreSQL 14 and earlier, any authenticated user possessed the CREATE privilege on the public schema by default, introducing security vulnerabilities in shared multi-tenant environments. Beginning in PostgreSQL 15, the CREATE privilege on public has been revoked from PUBLIC (all users) by default. Only the database owner and superusers can create objects in public unless explicit permissions are granted.

The search_path Resolution Mechanism

When a SQL statement queries a table without qualifying its schema (e.g., SELECT * FROM items;), PostgreSQL searches through the schemas listed in the search_path configuration parameter from left to right. The first matching table name encountered is selected.

Default setting:

SHOW search_path;
-- Output: "$user", public

How resolution works:

1. "$user": Evaluates dynamically to the name of the currently connected database user (session user). If a schema exists with the exact same name as the current user, it is searched first.
2. public: If no object matches in "$user", the engine searches the public schema.
3. System Catalogs (pg_catalog): PostgreSQL implicitly searches pg_catalog first, before any schema in search_path, unless pg_catalog is explicitly listed in search_path.
4. Temporary Schemas (pg_temp_*): If a temporary table exists with the same name, it is searched first before any other schema.

Configuring search_path at Different Scopes

Administrators can configure search_path at four hierarchical levels, where narrower scopes override wider ones:

-- 1. Session Level (Applies only to current connection)
SET search_path TO sales, inventory, public;

-- 2. Role / User Level (Applies whenever this role logs in across any database)
ALTER ROLE analyst_user SET search_path TO reporting, analytics, public;

-- 3. Database Level (Applies to all sessions connecting to this specific database)
ALTER DATABASE ecommerce_db SET search_path TO store, catalog, public;

-- 4. Global Server Level (postgresql.conf - requires reload)
-- search_path = '"$user", public, shared_utils'

---

Tablespaces: Mapping Logical Storage to Physical Filesystems

By default, all tables, indexes, and system catalogs reside inside the primary data directory ($PGDATA/base). A tablespace allows an administrator to define an alternate physical location on the host operating system's filesystem where database objects can reside.

+---------------------------------------------------------------------+
|                   Physical Filesystem Layout                        |
+---------------------------------------------------------------------+
| $PGDATA/base/                     -> Standard spinning disk / array |
|   └── [Default: pg_default]       -> Holds system catalogs & tables |
|                                                                     |
| /mnt/nvme_storage/pg_fast/        -> High-speed NVMe PCIe SSD      |
|   └── [Tablespace: fast_ssd]      -> High-IOPS transactional tables |
|                                                                     |
| /mnt/nfs_backup/pg_archive/       -> Low-cost bulk cold storage     |
|   └── [Tablespace: cold_archive]  -> Historical audit partition tables|
+---------------------------------------------------------------------+

Primary Use Cases for Tablespaces

• Tiered Storage: Placing write-heavy OLTP tables or B-tree indexes onto ultra-fast NVMe SSDs while placing cold, historical audit tables or partitioned ranges onto high-capacity SATA or network-attached storage.
• Disk Space Management: Preventing a single rapidly growing table or index from filling up the root filesystem containing $PGDATA and triggering database-wide read-only halts.

Creating a Tablespace

CREATE TABLESPACE fast_ssd LOCATION '/mnt/disks/ssd1/pgdata';

[!IMPORTANT] Strict Prerequisites for CREATE TABLESPACE:

1. The target directory (/mnt/disks/ssd1/pgdata) must already exist on the operating system.
2. The directory must be completely empty (no existing files or subdirectories).
3. The directory must be owned by the postgres operating system user.
4. The directory permissions must be locked down (typically mode 0700). If any of these conditions are violated, CREATE TABLESPACE immediately aborts with a filesystem permission or directory error.

Internally, PostgreSQL creates a symbolic link inside $PGDATA/pg_tblspc/ pointing directly to the target location path.

Built-in System Tablespaces

Every PostgreSQL cluster contains two predefined tablespaces:

• pg_default: Stores all user data, databases, and schemas when no specific tablespace is specified. It maps physically to $PGDATA/base.
• pg_global: Stores cluster-wide shared system catalogs (such as pg_database, pg_authid, and pg_tablespace). It maps physically to $PGDATA/global.

Assigning Objects to Tablespaces

Objects can be mapped to tablespaces during creation or altered later:

-- Create a table directly in the fast tablespace
CREATE TABLE transactions (
    tx_id BIGSERIAL PRIMARY KEY,
    amount NUMERIC(12,2),
    tx_time TIMESTAMPTZ DEFAULT clock_timestamp()
) TABLESPACE fast_ssd;

-- Create an index in a specific tablespace (common optimization)
CREATE INDEX idx_tx_time ON transactions(tx_time) TABLESPACE fast_ssd;

Moving Existing Tables and Indexes Across Tablespaces

Administrators can relocate existing objects online without dropping and recreating them:

-- Move a table to a new tablespace
ALTER TABLE transactions SET TABLESPACE cold_archive;

-- Move an index to a new tablespace
ALTER INDEX idx_tx_time SET TABLESPACE fast_ssd;

[!CAUTION] Locking Impact of ALTER TABLE ... SET TABLESPACE: Moving an existing table to a new tablespace requires physically copying all underlying 8KB data blocks from the source directory to the destination directory. During this entire copy operation, PostgreSQL acquires an ACCESS EXCLUSIVE lock on the table. This lock completely blocks all concurrent reads (SELECT) and writes (INSERT, UPDATE, DELETE). On multi-gigabyte or terabyte tables, this command causes significant application downtime unless executed during a scheduled maintenance window.

To move all tables belonging to a database across tablespaces in bulk:

ALTER TABLESPACE fast_ssd MOVE ALL IN DATABASE sales_db TO cold_archive;

---

Comparison: Storage & Logical Abstractions

---

Exam Tips and Common Pitfalls

• Exam Trap: Dropping vs Cascading Schemas: If a schema contains even one object, running DROP SCHEMA name; fails with an error under default RESTRICT behavior. You must append CASCADE to drop the schema and all child objects.
• Exam Trap: Template1 Connection Lock: If an exam scenario describes CREATE DATABASE failing with "source database is being accessed by other users", identify the culprit: a background script, psql terminal, or connection pooler is maintaining an open connection to template1.
• Exam Trap: Lock Level of SET TABLESPACE: Remember that ALTER TABLE ... SET TABLESPACE acquires an ACCESS EXCLUSIVE lock, not an EXCLUSIVE or SHARE UPDATE EXCLUSIVE lock. Reads are strictly blocked.
• Exam Trap: Tablespace Directory Requirements: Questions often ask why CREATE TABLESPACE failed. Look for: the directory does not exist, the directory is not owned by the OS postgres user, or the directory contains existing files.