Understanding PostgreSQL Schemas01 Dec 2017
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
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.
- To allow many users to use one database without interfering with each other.
- To organize database objects into logical groups to make them more manageable.
- Third-party applications can be put into separate schemas so they do not collide with the names of other objects.
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.