PostgreSQL and default Schemas


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.

Best Answer

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".