A database is an organized collection of structured information, or data, usually stored electronically in a computer system. It is typically managed by a database management system (DBMS). Together, the data, the DBMS, and associated applications are referred to as a database system, often simply called a database.

In today's most common types of databases, data is usually modeled in rows and columns within a series of tables, facilitating efficient data processing and querying. This structured format allows for easy access, management, modification, updating, control, and organization of the data. Most databases utilize structured query language (SQL) for writing and querying data.

Schemas play a crucial role in the realm of databases, acting as the blueprint for how data is organized and structured. A schema is essentially a formal definition that outlines the logical configuration of all or part of a database, including the tables, fields, relationships, views, indexes, and other elements.

The importance of schemas cannot be overstated, as they provide a clear framework that ensures data integrity, consistency, and accuracy. By defining the structure and constraints of data, schemas facilitate efficient data management and retrieval, helping to maintain the quality and reliability of the information stored within the database. Moreover, schemas serve as a vital communication tool, allowing developers, database administrators, and other stakeholders to understand and interact with the database effectively.

Schema Definition

In PostgreSQL, schemas function as logical containers to organize database objects like tables, views, indexes, stored procedures, functions, and more. They offer a rational method to group related objects and separate them according to their functionalities or business domains. Essentially, a schema is a conceptual space that holds named database objects.

By grouping related views, tables, operators, stored procedures, and other entities, schemas streamline the management of complex database structures, making them easier to oversee and maintain.

Here are some key points about schemas in PostgreSQL:

• Separation of Concerns: Schemas enable the separation of different parts of a database into distinct namespaces. This logical organization helps keep the schema clean and maintainable.
• Ownership: Each schema is owned by a specific PostgreSQL user or role, who has the necessary privileges to create and manage objects within that schema.
• Object Visibility: Schemas control the visibility of database objects. Objects within a schema are only seen by users or roles with the required privileges, providing an additional layer of security and access control.
• Nesting: Schemas can be nested within other schemas, creating a hierarchical structure that allows for further organization and grouping of related schemas.
• Default Schema: Each PostgreSQL user has a default schema associated with their session. If a schema is not explicitly specified when referencing an object, the default schema is used.
• Schema Search Path: The schema search path determines the order in which PostgreSQL searches schemas when an object is referenced without explicitly specifying the schema. The search path can be set at the session level or user level.
• Schema Separation: It is good practice to separate database objects into different schemas based on functionality, module, or application. This promotes modularity, reusability, and maintainability of the database.
• Schema Manipulation: PostgreSQL provides various SQL commands and functions to manipulate schemas, such as CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, and RENAME SCHEMA.
• Schema Privileges: Privileges can be granted on schemas, allowing users or roles to have specific privileges (e.g., CREATE, USAGE) on the schema itself, which in turn grants privileges to the objects within the schema.

Schema Types in PostgreSQL

In the context of database management, schemas can be classified into different categories based on some factors, namely purpose, and access control. Public and custom schemas are two such types:

Public Schema

This is a shared schema type that is created by default when you initialize a PostgreSQL database without defining a name for the database. This means that for every object you create without specifying the schema name, PostgreSQL will put it in the public schema. Public schemas are accessible to all users, as they’re typically used for objects that need to be accessed and shared by multiple roles in a database.

Custom Schema

A custom schema, on the other hand, is a user-defined schema. Custom schemas are not general-purpose schemas and are not accessible to all users by default, unlike public schemas. This implies that only authorized users can access this type of schema and the objects within them.

How to create a schema

PostgreSQL provides the CREATE SCHEMA statement to create a new schema in a database. To execute the CREATE SCHEMA statement, you must have the CREATE privilege in the current database.

 CREATE SCHEMA schema_name;

You can optionally use the IF NOT EXISTS to conditionally create the new schema only if it does not exist.

CREATE SCHEMA IF NOT EXISTS schema_name;

For an example lets create a a new schema which we'll call sources

CREATE SCHEMA IF NOT EXISTS sources;

Once you have created your schema then you can check that it exists by running the following query

SELECT 
  * 
FROM 
    pg_catalog.pg_namespace
ORDER BY 
    nspname;

The above query would return results simiLar too below containing your new schema, in our case we created a new schema sources:

| 13212 | information_schema  |  10  | "{postgres=UC/postgres,=U/postgres}"                            |
| 11    | pg_catalog          |  10  | "{postgres=UC/postgres,=U/postgres}"                            |
| 99    | pg_toast            |  10  |                                                                 |
| 2200  | public              | 6171 | "{pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}" |
| 16485 | sources             |  10  |                                                                 |

You can also refine this query to get schema information by using the information_schema tables within Postgre

SELECT schema_name, catalog_name, schema_owner FROM information_schema.schemata;


| schema_name         | catalog_name  | schema_owner      |
|---------------------|---------------|-------------------|
| information_schema  |  threenine   |  postgres          |
| pg_catalog          |  threenine   |  postgres          |
| pg_toast            |  threenine   |  postgres          |
| public              |  threenine   |  pg_database_owner |
| sources             |  threenine   |  postgres          |

Conclusion

Schemas provide an effective way to organize and manage database objects. By leveraging custom and default schemas, developers enhance data organization and fine-tune access control. Proper schema design enables developers to build scalable applications that optimize performance and simplify maintenance, fully utilizing the capabilities of their chosen database, be it PostgreSQL or another platform.