PostgreSQL Databases & Schemas

A. Databases vs Schemas

Conceptual Hierarchy

text

PostgreSQL Instance (Cluster)
│
├── Database 1 (myapp)
│   ├── Schema 1 (public) - Default
│   │   ├── Table 1 (users)
│   │   ├── Table 2 (orders)
│   │   └── View 1 (active_users)
│   │
│   ├── Schema 2 (analytics)
│   │   ├── Table 1 (user_stats)
│   │   └── Materialized View 1 (daily_metrics)
│   │
│   └── Schema 3 (staging)
│       └── Table 1 (temp_data)
│
└── Database 2 (reports)
    ├── Schema public
    └── Schema archive

Key Differences

B. Creating / Dropping Databases

1. Creating Databases

sql

-- Basic database creation
CREATE DATABASE mydb;

-- With specific owner
CREATE DATABASE mydb OWNER = alice;

-- With template (clone existing database)
CREATE DATABASE mydb_copy TEMPLATE = mydb;

-- With encoding and collation
CREATE DATABASE mydb
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;  -- Use clean template

-- With connection limit
CREATE DATABASE mydb 
    CONNECTION LIMIT = 50;  -- Max 50 concurrent connections

2. Using Template Databases

sql

-- List existing databases (including templates)
\l

-- Create database from template1 (default, includes standard extensions)
CREATE DATABASE db_from_template1 TEMPLATE template1;

-- Create clean database from template0 (empty, no extensions)
CREATE DATABASE clean_db TEMPLATE template0;

-- Cannot copy database if there are active connections
-- Force close connections first:
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'source_db';

3. Dropping Databases

sql

-- Basic drop
DROP DATABASE mydb;

-- Drop if exists (prevents error)
DROP DATABASE IF EXISTS mydb;

-- Drop with force (terminate connections first)
-- In one transaction:
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb';
DROP DATABASE mydb;

4. Managing Databases via Command Line

bash

# Create database from terminal
createdb -h localhost -p 5432 -U postgres mydb

# Drop database from terminal
dropdb -h localhost -p 5432 -U postgres mydb

# With specific options
createdb -E UTF8 --locale=en_US.UTF-8 -T template0 mydb

# List all databases
psql -h localhost -U postgres -c "\l"

5. Database Properties & Modifications

sql

-- Alter database
ALTER DATABASE mydb RENAME TO newname;
ALTER DATABASE mydb OWNER TO new_owner;
ALTER DATABASE mydb SET enable_indexscan = off;  -- Per-database setting

-- Change connection limit
ALTER DATABASE mydb CONNECTION LIMIT 100;

-- Reset to default
ALTER DATABASE mydb RESET connection_limit;

-- View database information
SELECT * FROM pg_database WHERE datname = 'mydb';

-- Size of database
SELECT pg_size_pretty(pg_database_size('mydb')) as size;

C. Using Schemas

1. Default public Schema

sql

-- Every database has a 'public' schema by default
-- Objects created without specifying schema go here

CREATE TABLE users (id SERIAL, name TEXT);  -- Goes to public.users
-- Equivalent to:
CREATE TABLE public.users (id SERIAL, name TEXT);

2. Creating Custom Schemas

sql

-- Basic schema creation
CREATE SCHEMA analytics;

-- With authorization
CREATE SCHEMA analytics AUTHORIZATION alice;

-- Create schema if not exists (PostgreSQL 9.3+)
CREATE SCHEMA IF NOT EXISTS staging;

-- Create and set search path
CREATE SCHEMA app_schema;
SET search_path TO app_schema, public;

3. Schema Operations

sql

-- Create objects in specific schema
CREATE TABLE analytics.user_metrics (
    id SERIAL PRIMARY KEY,
    metric_date DATE,
    value NUMERIC
);

-- View all schemas in current database
\dn  -- psql command
SELECT schema_name FROM information_schema.schemata;

-- Drop schema
DROP SCHEMA analytics;
DROP SCHEMA analytics CASCADE;  -- Drop all objects in schema too

-- Rename schema
ALTER SCHEMA analytics RENAME TO business_intel;

-- Change owner
ALTER SCHEMA business_intel OWNER TO data_team;

4. Practical Schema Use Cases

sql

-- 1. Multi-tenant architecture (one schema per tenant)
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;

-- Same table structure in each schema
CREATE TABLE tenant_a.customers (...);
CREATE TABLE tenant_b.customers (...);

-- 2. Separation by function
CREATE SCHEMA app;
CREATE SCHEMA reporting;
CREATE SCHEMA audit;

-- 3. Version management
CREATE SCHEMA v1;
CREATE SCHEMA v2;
-- Migrate data between schemas for version upgrades

D. Search Path

1. Understanding Search Path

sql

-- Current search path
SHOW search_path;
-- Default: "$user", public

-- What it means:
-- 1. Look in schema with current user's name
-- 2. Look in 'public' schema
-- 3. If not found, error

-- Set search path for session
SET search_path TO analytics, public;

-- Set for specific user/role
ALTER ROLE alice SET search_path = 'app_schema, public';

-- Set for database
ALTER DATABASE mydb SET search_path = 'core, public, "$user"';

2. Search Path Examples

sql

-- Default behavior
SET search_path TO public;
CREATE TABLE test (id INT);  -- Creates public.test

-- With custom schema first
SET search_path TO app, public;
CREATE TABLE test (id INT);  -- Creates app.test

SELECT * FROM test;  -- Looks in app.test first, then public.test

-- Explicit reference always works
SELECT * FROM public.test;  -- Explicit schema
SELECT * FROM app.test;     -- Explicit schema

3. Schema-Specific Operations

sql

-- Move table between schemas
ALTER TABLE public.users SET SCHEMA app;

-- Grant permissions on schema
GRANT USAGE ON SCHEMA analytics TO reader_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO reader_role;

-- Revoke from public schema (security best practice)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydb FROM PUBLIC;

4. Information Schema Views

sql

-- Find all tables in specific schema
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'analytics';

-- Find all functions in schema
SELECT routine_name
FROM information_schema.routines
WHERE specific_schema = 'app';

-- List schemas with their owners
SELECT s.nspname as schema_name, 
       u.usename as owner
FROM pg_catalog.pg_namespace s
JOIN pg_catalog.pg_user u ON u.usesysid = s.nspowner;

5. Best Practices

sql

-- 1. Remove public schema CREATE privileges (security)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 2. Use schemas for logical separation
CREATE SCHEMA core;
CREATE SCHEMA staging;
CREATE SCHEMA reporting;

-- 3. Set appropriate search paths per role
ALTER ROLE app_user SET search_path = 'core, public';
ALTER ROLE analyst SET search_path = 'reporting, core, public';

-- 4. Use qualified names in applications
-- Instead of: SELECT * FROM users
-- Use: SELECT * FROM core.users

-- 5. Consider schema-based multi-tenancy vs row-based
-- Schema-based: Better isolation, easier backup per tenant
-- Row-based: Simpler queries, less overhead

6. Common Patterns

sql

-- Pattern 1: Application modules
CREATE SCHEMA auth;
CREATE SCHEMA billing;
CREATE SCHEMA inventory;

-- Pattern 2: Temporal separation
CREATE SCHEMA current_data;
CREATE SCHEMA archive_2023;
CREATE SCHEMA archive_2024;

-- Pattern 3: Testing/development
CREATE SCHEMA dev;
CREATE SCHEMA test;
CREATE SCHEMA prod;  -- Or use separate databases for prod

-- Pattern 4: Third-party extensions
-- Extensions often create their own schemas
CREATE EXTENSION postgis;  -- Creates postgis schema
CREATE EXTENSION uuid-ossp;  -- Creates functions in public

This structure allows for clean separation of concerns while maintaining the ability to join data across schemas when needed. Remember that schemas are primarily a logical separation mechanism, not a physical one.