Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:

enter image description here

Now from my understanding Schemas are like folders for organization, etc. So the question I have is are all these schemas needed when I create a new DB? If so what are they used for on PG side as I won't ever use them myself.

I can understand information_schema as this is default for an install of MySQL on a server, though I don't get why a database would need it's own as opposed to the entire server, but to each DB type his own I guess.

share|improve this question

2 Answers

up vote 7 down vote accepted

What you're seeing is mostly one user interface's way of displaying the structure of a PostgreSQL database.

If you were using pgAdminIII, which is just another administrative interface, each database would usually show two "catalogs": information_schema and pg_catalog. It would also have a schema named "public".

Database objects named "pg_*" are system objects. The pg_toast schema holds TOAST storage for large tables.

The information_schema views are part of the SQL standard. They're supposed to provide a vendor-independent way to determining information that's stored in vendor-specific system tables.

You can make a good case for not exposing every namespace as if it were a user-level schema. (Internally, these are namespaces. Try select * from pg_namespace;.) The only schema in a newly created database that really matters to users is "public".

share|improve this answer
up vote 3 down vote

If you keep the default search_path and use the default schema public (automatically), you don't have to bother with schemas at all in your daily affairs.

But if your database grows, chances are you will happily make use of them to organize objects and privileges. By setting the search_path per user / database / session / .. you can manage schemas any way you want. I have assembled a couple of ways to do this in this related answer on SO.

Schemas are much like directories in the file system:

  • If you don't schema-qualify an object name (myschema.mytable), the first schema in your search_path is assumed when creating objects. That's like the "current directory" in the file system.

  • All schemas in the search_path are searched in sequence when using objects. Just like with a search path in the file system.

But:

  • There is no "root directory", meaning you can't store anything "without schema". Every object is created inside a particular schema.

  • There are no "subdirectories". You cannot nest schemas.

By default schemas are visible database-wide. (There are ways to extend this with dblink or other tools.) But the current user needs privileges to access a schema. Schemas are extremely useful if you want to organize things and still be able to access other "areas" in a simple query.

MySQL does not have anything similar. You need a separate database for many things you would use a separate schema for in PostgreSQL.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.