How to List All Schemas in PostgreSQL: A Full-stack Developer‘s Guide

As a full-stack developer, organizing your PostgreSQL database into logical schemas is a crucial efficiency boon allowing increased complexity while maintaining control.

Schemas empower developers to segment tables based on features, apps, access patterns – unencumbered by underlying implementation concerns.

In this comprehensive 2600+ word guide tailored for full-stack devs, we will tackle all facets around viewing, planning and managing Postgres schemas with the mindset of reducing developer overhead.

Database Schema Concept Recap

Let‘s briefly revisit schema concepts we should internalize before jumping into the technical details:

Schema – A namespace grouping database objects like tables and views. Each object "belongs" to exactly one schema.

Search Path – Ordered list of schemas consulted when resolving unqualified object references.

Default public Schema – Every new database has a public schema usable by all users. This is where objects get created by default.

So in summary, schemas act like filesystem directories or code libraries – they organize objects into modular units while avoiding name conflicts across groups.

Now why care so much about these technicalities as developers?

Schemas as a Developer Abstraction Tool

Here are the main developer benefits provided by leveraging PostgreSQL schemas:

1. Namespacing: Avoid object naming conflicts between teams, apps, domains

2. Access Control: Simplified database permissions at the schema level

3. Encapsulation: Each schema can have independent tables, data types, functions

4. Code Reuse: Common functions/views/tables can be segmented across groups

5. Decoupling: Reference objects by schema without knowing underlying details

Categorizing tables as shown below prevents overlapping teams from unintended breakage:

app1_schema
     - users table
     - products table

app2_schema
     - users table 
     - inventory table

This schema partitioning limits scope and promotes specialized optimization per-schema.

Additionally, changes can be rolled out progressively by schema – providing agility combined with safety.

With that context of why schemas help, let‘s deep dive into specifics around introspecting, designing and leveraging schemas from a pragmatic developer angle.

Introspecting Schemas

First we need visibility into existing schemas before making further plans. PostgreSQL offers several approaches to schema introspection, mainly via:

• psql meta-commands
• System catalogs
• Information schema
• pgAdmin

Having awareness here allows mapping reality to expectations, informing future schema strategy.

psql Meta-commands

psql has specialized commands for schema investigation at your fingertips:

See all schemas with \dn:

app_db=> \dn
    List of schemas
   Name    |  Owner   
-----------+----------
 public    | postgres 
 analytics | dw_admin  
 hr        | hr_admin
(3 rows)

This shows the schema name and owner only.

\dn+ provides additional comment info:

    List of schemas
  Name    |  Owner   | Access privileges |   Description   
----------+----------+-------------------+-----------------
 analytics | dw_admin |                   | Analytics tables
 hr        | hr_admin |                   | HR system data
 public    | postgres | postgres=UC/postgres+| standard public schema
(3 rows)

Comments clarify intended schema usage – extremely useful context for developers.

Lastly, \dE lists foreign tables, while \di shows indexes grouped by schema.

These meta-commands offer rapid yet targeted insight into your database‘s schema structure.

System Catalog Investigation

For programmatic interrogation or to extract additional properties, querying system catalogs offers max flexibility.

The pg_namespace table holds core schema metadata:

SELECT * 
FROM pg_namespace;

Key fields are:

• nspname: Schema name
• nspowner: Owner user OID
• nspacl: Access privilege array
• nspcomment: Optional comment

We can even join with pg_roles to resolve user OIDs:

SELECT 
  n.nspname AS schema, 
  r.rolname AS owner,
  n.nspacl AS privileges,
  n.nspcomment AS description
FROM pg_namespace n 
  JOIN pg_roles r ON r.oid = n.nspowner

This allows full introspection into all schema properties.

Schema Investigation via Information Schema

The SQL standard information_schema provides an additional centralized data dictionary across database objects.

It contains schema details under the schemata table:

SELECT * 
FROM information_schema.schemata;

Important columns are:

• schema_name: Name of schema
• schema_owner: Name of owner
• default_character_set_name: Character set

Visualizing Schema Metadata via pgAdmin

Lastly, pgAdmin as the administrative GUI for PostgreSQL offers convenient visualization of schema structures:

Plus options to recursively expand into tables, views, functions etc under any schema:

The graphical overview can quickly convey relationships between database objects at a glance.

Best Practices for Schema Introspection

• Always introspect early when inheriting a new database
• Leverage JOINs with system catalogs to uncover full context
• Use pgAdmin to visualize schema contents visually
• Read schema comments left by owners if they exist
• Identify irrelevant legacy schemas with no tables

Just like with source code, understanding existing schemas lays the foundation for improvement.

Schema Design Guidelines

Now equipped with introspection techniques, we can strategize additional schema creation tailored for your apps and teams.

Here are schema design best practices I follow in practice:

Start with Clear Business Scoping

Ultimately schemas should solve business problems, not just delivery technical value.

Before defining additional schemas, document the:

• Core apps, products, domains in play
• Team structures and personnel based on function
• Source systems, reporting needs per department
• Security risks, governance constraints to consider

This grounds schema purpose in tangible business logic.

Map Business Areas to Schemas

Next, diagram which business areas make sense as dedicated schemas based on the scoping exercise.

Plausible scenarios are:

• Departmental data – sales, engineering, finance schemas
• Product line schemas – payroll_db, inventory_db
• Multitenancy support via customer or company schemas
• Backend vs analytics vs reporting workloads

Determine if it‘s better to define broader departmental schemas or break things down further into specialized schemas per app/product.

Measure schema growth over time and don‘t prematurely optimize. Consolidate multiple single table schemas later if it simplifies governance.

Namespace by Application Identifier

I prefer naming database schemas using a descriptive business name plus app identifier suffix, like:

• sales_legacy
• dwh_users
• inventory_service

Explicit identifiers signify what software is utilizing that schema, improving clarity.

As architects we want "glanceable" context from schema names alone.

Enforce Standard Object Naming

Within each schema, use consistent naming conventions for objects like tables, views and functions using name spacing:

sales_legacy schema:

  - sales_legacy.customers
  - sales_legacy.orders
  - sales_legacy.order_details

This prevents conflicts scaling up to thousands of entities.

Prefer Roles over Users for Ownership

Instead of assigning users as schema owners, define a role like sales_dba, app_developer that contains users based on team or job function.

Then the role owns the schema:

CREATE SCHEMA sales OWNER sales_dba; 

This sustains access through staff turnover – just revoke from the old user, grant to the new user.

Centralize Shared entities into Public

The public schema effectively has unlimited visibility. Perfect for shared lookup tables needed across apps:

• Geographic data
• Standardized product catalogs
• Reference data like currencies, country codes

No need to duplicate public entities that multiple schemas require.

Encapsulate Storage in Dedicated Tablespaces

For large deployments, assigning tablespaces enables granular storage control per-schema:

CREATE SCHEMA analytics TABLESPACE ssd_fast;

Tablespaces map to storage volumes, eliminating IO contention.

This demonstrates real-world schema design wisdom accrued from extensive database architecting exposure. Let‘s now shift gears into utilizing schemas efficiently.

Managing Schemas Programatically

While planning is crucial, developers need to efficiently execute schema changes too.

I‘ll equip you with the full toolbox to create, modify and delete schemas on demand.

We will explore:

• Schema creation syntax options
• Key schema modification operations
• Commands to delete schemas
• Bulk schema metadata changes

Note PostgreSQL enforces close to full SQL compliance for schema DDL.

Let‘s dive in hands-on!

Creating Schemas

The SQL standard CREATE SCHEMA statement is the primary route for authoring new schemas:

CREATE SCHEMA marketing;

This constructs a schema owned by the creator.

Explicitly assign an owner role:

CREATE SCHEMA analytics 
    AUTHORIZATION dw_admin;

Additionally declare a tablespace for storage locality:

CREATE SCHEMA accounting
    TABLESPACE ssd_tier; 

CREATE SCHEMA also supports IF NOT EXISTS to ignore errors for existing schemas:

CREATE SCHEMA IF NOT EXISTS public;

Lastly, specify schema permissions through the ACL:

CREATE SCHEMA restricted
    AUTHORIZATION analyst
    CREATE TABLE

This allows analyst role to create tables only.

Modifying Schemas

Altering existing schemas utilizes ALTER SCHEMA:

Rename the schema itself:

ALTER SCHEMA sales RENAME TO sales_hq;

Transfer schema ownership via OWNER TO:

ALTER SCHEMA public 
    OWNER TO postgres;   

Or update schema comment:

COMMENT ON SCHEMA public IS 
    ‘Standard public schema for ad-hoc analysists‘;    

When scopes change, update schemas accordingly.

Dropping Schemas

Removing outdated schemas simplifies long-term governance through consolidation.

The DROP SCHEMA command obliterates schemas:

DROP SCHEMA legacy;

Add CASCADE to force-delete all nested tables automatically:

DROP SCHEMA old_reports CASCADE;

Likewise, RESTRICT blocks removal if any objects exist:

DROP SCHEMA analytics RESTRICT;

Lastly, use IF EXISTS to silently ignore missing schemas:

DROP SCHEMA IF EXISTS temp_staging;

These construct give full capabilities to create, adjust or destroy schemas on demand.

Automating Metadata Changes

When implementing schema upgrades, leveraging PostgreSQL functions allows safely encapsulating complex logic and adds error-handling.

For example, we can wrap schema creation and user grants into a transactional function:

CREATE FUNCTION add_schema(text) RETURNS void AS $$
DECLARE
  schema_name ALIAS FOR $1;  
BEGIN
  CREATE SCHEMA IF NOT EXISTS schema_name;

  GRANT USAGE ON SCHEMA schema_name TO public;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

Execute providing the desired schema name:

SELECT add_schema(‘analytics‘);

This style avoids low-level errors when updating metadata at scale.

Encapsulating often repeated schema tasks into functions is a best practice among experienced PostgreSQL developers.

Schema Gotchas for Developers

While schemas may seem straightforward conceptually, some hidden caveats routinely trap developers.

Let‘s illuminate key issues to consider:

Schema Changes Break Views/Routines

Remember PostgreSQL compiles views and stored procedures against specific schemas referenced within.

Thus any schema drops or renames invalidate dependent objects!

Before renaming old_schema, find usages:

SELECT routine_name 
FROM information_schema.routines
WHERE routine_schema=‘old_schema‘;

And update to reference new_schema to avoid breakage.

Table Visibility Varies by User Schema Path

Due to search_path nuances, tables accessible to one user may require full qualification for another.

Set search_path universally:

ALTER DATABASE app SET search_path TO global_schema; 

Standardizing avoids path-based confusion accessing the same tables between developers.

Public Schema Stores Temporary Tables Too

Remember the public schema contains temporary tables only visible to a session additionally.

Explicitly clean up temp tables rather than assuming transactions rollback state.

When deploying changes, inspect public schema for leftovers.

Security Policies Can Limit User Access

Row-level security policies restrict table visibility. A developer can therefore access a table in one schema but not another.

Explicitly grantCONNECT to ensure a consistent security context:

GRANT CONNECT ON DATABASE app TO dev_role;  

Connection rights provide a baseline for all underlying schema access.

Hopefully these hard-learned lessons help circumvent frustrating schema gotchas!

Schema Migration Strategies

As full-stack developers, we often join projects mid-stream needing large schema re-organization.

How do we migrate legacy schemas forward balancing risk vs reward?

Here is an effective model:

1. Audit – Get full inventory of dependencies on old schemas from tables, views etc

2. Communicate – Message impacted teams well ahead of migration timeline

3. Transition – Temporarily set search_path to include both old and new schema

4. Decommission – Rename then drop original schema once usage ceases

This phased approach minimizes disruption for a smooth schema modernization.

How Schemas Contrast with Other Namespaces

To close out the discussion, let‘s contrast PostgreSQL schemas with other namespace options.

Domains – constrain data types for validation

Catalogs – group schemas sharing storage

How do application databases themselves compare with schemas?

Databases operate as completely isolated environments containing a set of schemas. Migrating databases instead of schemas provides stronger decoupling but reduces sharing.

Generally leverage schemas over databases until requiring full isolation, scaling limitations emerge or wanting independent users per database.

Understanding schema nuances in the broader PostgreSQL namespace ecosystem prevents overloading any specific abstraction prematurely.

Conclusion

We just toured several excursions into the importance, introspection and leverage of schemas for effective PostgreSQL application development.

Here are the major takeaways:

• Schemas logically group tables to prevent naming collisions
• Introspect existing schemas using psql, catalogs and pgAdmin
• Map real business domains to schemas during design
• Standardize object naming and owners per schema
• Manage schemas portably through CREATE/ALTER/DROP
• Encapsulate schema changes into upgrade functions
• Watch for visibility issues between schemas

While underused by some PostgreSQL developers, intentional design and leveraging of database schemas will pay dividends for organizational integrity.

I hope these guidelines give you a blueprint for harnessing schemas more intentionally in your next PostgreSQL project.

You now have the tools and wisdom to list, configure and manage PostgreSQL schemas at an expert level!