PostgreSQL 7.3: Utilizing Schemas for Better Database Isolation

In 2003, PostgreSQL is quickly becoming the "advanced" open-source database choice, especially compared to MySQL 4.0 which still lacks basic features like subqueries. With the release of PostgreSQL 7.3, we finally get Schemas.

Before schemas, if you wanted to host multiple modules or applications in a single database, you had to manually prefix your tables (e.g., blog_users, shop_users) to avoid name collisions.

What is a Schema?

Think of a schema as a namespace or a directory within a database. A database contains one or more schemas, and each schema contains tables, views, and functions.

-- Create two separate schemas
CREATE SCHEMA production;
CREATE SCHEMA staging;

-- Create tables with the same name in different schemas
CREATE TABLE production.users (
    id serial PRIMARY KEY,
    username text
);

CREATE TABLE staging.users (
    id serial PRIMARY KEY,
    username text
);

The search_path Variable

The real power of schemas comes from the search_path. This tells PostgreSQL which schemas to look in when you reference a table without a prefix.

-- Set the search path for the current session
SET search_path TO production, public;

-- This will now query production.users
SELECT * FROM users;

-- Switch to staging
SET search_path TO staging;
SELECT * FROM users; -- Now queries staging.users

Multi-tenant Architecture

In 2003, we're starting to build more complex web applications. Schemas allow for a very clean multi-tenant approach. You can give each customer their own schema with the exact same table structure. This provides excellent data isolation while still keeping all data in one physical database for easier backups.

-- Security: Revoke access to public, then grant per-schema
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA customer_a TO user_a;
GRANT ALL ON ALL TABLES IN SCHEMA customer_a TO user_a;

PostgreSQL 7.3 is a major leap toward the enterprise. With schemas, we can finally organize our data in a way that reflects our application architecture, rather than the limitations of the database engine.