Understanding PostgreSQL Schemas

In the last couple of days, I’ve done some work on the database behind a large Rails app. We use PostgreSQL for the set of databases that sit behind our apps. One of the PostgreSQL specific features I came across was the “schema.”

I did a few database-centric internships before becoming a software engineer, so the word schema is pretty familiar to me. I’ve always understood schema as the framework of rules that a database is designed around - which is also what you will find if you check Wikipedia. However, the people who built PostgreSQL decided it would be a really great name for what is essentially a namespace.

Imagine in your PostgreSQL instance you’ve got a database with a default schema of public. In your application you have a profiles table, it would fall under that public schema. You could reference your table as public.profiles if you wanted. Now, if you need to use this same database instance with another table that is also called public, you could create a new schema called other. You could access your first table as public.profiles and your second table as other.profiles.

CREATE SCHEMA other;

CREATE TABLE other.profiles (
    /* columns */    
);

Another great way to think about a schemas in PostgreSQL is like a bucket. It’s like a bucket that contains a set of tables (see: a namespace).

The PostgreSQL docs list three reasons that you might want to use a schema with your database.

A schema, like a database, can have dependencies like tables and triggers so if you decide to drop it you’ll have to use CASCADE to delete all of those dependent objects.

DROP SCHEMA other CASCADE;

A note on portability: the PostgreSQL docs admit that this feature is not compliant with the SQL standard, it really is a reflection of some relational databases proclivity to using a username as a namespace. For example, you could create tables in your own namespace myname.mypersonaltable that were only accessible to you and/or an administrator. PostgreSQL tries to offer you some flexibility, allowing you to follow that convention or not.