list of schema with sizes relative and absolute in a postgresql database with code examples

PostgreSQL is one of the popular open-source relational database management systems. It is known for its powerful and flexible features that allow developers and programmers to build complex database structures. One essential feature of PostgreSQL is schema, which is a logical container that can hold multiple related database objects like tables, views, indexes, and more.

In this article, we will discuss the list of PostgreSQL schema with sizes relative and absolute and provide code examples for each type.

1. public

Public is the default schema in PostgreSQL, and all new database objects will be added to this schema unless you specify a different schema. Its size is relative to the size of the database. Here is an example of how to create a table in the public schema:

CREATE TABLE public.mytable (
   id INTEGER,
   name VARCHAR(50),
   age INTEGER
);

2. information_schema

Information_schema is a special schema that provides access to metadata about a database such as column names, data types, and more. It doesn't contain any user data and does not contribute to the overall size of the database. Here is an example of how to query the information_schema.tables view:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';

3. pg_catalog

Pg_catalog is another special schema that contains all the system catalog tables that PostgreSQL uses to manage the database. It is also known as the system catalog schema and does not contribute to the overall size of the database. Here is an example of how to query the pg_catalog.pg_tables view:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

4. pg_toast

Pg_toast is a hidden schema that stores large values that cannot fit in a single table row. It is used to store values like long text or large objects, and is automatically managed by PostgreSQL. It does not contain any user data and does not contribute to the overall size of the database.

5. pg_temp

Pg_temp is a temporary schema that can be used to store temporary tables and session data. It is created automatically by PostgreSQL when a session is initiated and is deleted automatically when the session ends. Its size is relative to the size of the data stored in it.

CREATE TEMP TABLE pg_temp.mytemp (
  id serial PRIMARY KEY,
  name VARCHAR(50)
);

6. pg_global

Pg_global is also a hidden schema that stores data used by global temporary tables. It is similar to pg_temp but is used for global temporary tables that can be accessed by multiple sessions. It does not contain any user data and does not contribute to the overall size of the database.

In conclusion, PostgreSQL provides many different schemas that can be used to organize and manage database objects more efficiently. Understanding these schemas and their sizes is crucial to optimizing PostgreSQL database performance. We hope that this article has provided you with a clear understanding of these schemas and how to use them.

let's delve deeper into the topic of PostgreSQL schema and provide more details on each type.

1. Public Schema

The public schema is the default schema in PostgreSQL, and it's used to store all newly created database objects unless you specify a different schema while creating the object. The public schema is always present in every PostgreSQL database, and its size is relative to the size of the database.

In addition to storing the default database objects, the public schema can also be used to organize related objects in a logical manner. For example, you can create different tables in the public schema to store data related to different parts of your application, like customers, orders, or products.

Here is an example of how you can create a table in the public schema:

CREATE TABLE public.my_table (
   id INTEGER,
   name VARCHAR(50),
   age INTEGER
);

2. Information_Schema

The information_schema is a special schema present in every PostgreSQL database that provides access to metadata about the database. Information_schema is used to obtain table and field information about the database, such as column names, data types, and constraints.

It is a read-only schema that does not contain any user data. It is used to simplify querying metadata and reduce the amount of verbose system table queries. Since it does not contain any user data, it does not contribute to the overall size of the database.

An example of how you can query view information_schema.tables to get information on tables in the public schema:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';

3. Pg_Catalog

The pg_catalog schema is another special schema present in every PostgreSQL database that contains all the system catalog tables that PostgreSQL uses to manage the database. The system catalog tables are used to store information about database objects, such as tables, indexes, views, sequences, and system functions.

The pg_catalog schema is also a read-only schema that does not contain any user data and does not contribute to the overall size of the database. An example of how you can query view pg_catalog.pg_tables to get information on tables in the public schema:

SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public';

4. Pg_Toast

The pg_toast schema is a hidden schema in PostgreSQL that is used to store large values that cannot fit in a single table row. It is used to store values like text, binary large objects, and large user-defined types.

The pg_toast schema is automatically managed by PostgreSQL, and it does not contain any user data. Also, it does not contribute to the overall size of the database. By default, the pg_toast schema is not visible to the user, as it is usually behind-the-scenes.

5. Pg_Temp

The pg_temp schema is a temporary schema in PostgreSQL that is used to store temporary tables and session data. It is created automatically by PostgreSQL when a session is initiated, and it is deleted when the session ends.

The pg_temp schema is useful when you need to store information temporarily, such as when performing a complex query or when storing the result of a function. Its size is relative to the size of the data stored in it.

Here is an example of how you can create a temporary table in the pg_temp schema:

CREATE TEMP TABLE pg_temp.mytemp (
  id serial PRIMARY KEY,
  name VARCHAR(50)
);

6. Pg_Global

The pg_global schema is also a hidden schema in PostgreSQL that is used to store data used by global temporary tables. It is similar to the pg_temp schema but is used for global temporary tables that can be accessed by multiple sessions.

As with pg_temp, the pg_global schema does not contain any user data and does not contribute to the overall size of the database. It is created automatically by PostgreSQL when a global temporary table is created.

In conclusion, PostgreSQL schema is an essential feature of the database that helps to organize data more efficiently. Understanding the different schema types and their sizes is crucial to optimizing PostgreSQL database performance.

Popular questions

1. What is the default schema in a PostgreSQL database, and how does it contribute to the overall database size?

Answer: The default schema in PostgreSQL is the public schema, and it contributes to the overall database size, as all newly created database objects are added to it.

2. What is the information_schema schema, and how is it used in PostgreSQL database management?

Answer: The information_schema schema is a special schema in PostgreSQL that provides access to metadata about a database, including information about tables, columns, data types, and more. It is used to simplify querying metadata and reduce verbose system table queries. It does not contain any user data, and it does not contribute to the overall size of the database.

3. What is the pg_catalog schema, and how is it used in PostgreSQL database management?

Answer: The pg_catalog schema is another special schema in PostgreSQL that contains all the system catalog tables that PostgreSQL uses to manage the database. It is used to store information about database objects, such as tables, indexes, views, sequences, and system functions. It is a read-only schema that does not contain any user data and does not contribute to the overall size of the database.

4. What is the pg_temp schema, and how is it used in PostgreSQL database management?

Answer: The pg_temp schema is a temporary schema in PostgreSQL that is used to store temporary tables and session data. It is created automatically when a session is initiated and is deleted automatically when the session ends. Its size is relative to the size of the data stored in it. It is useful when you need to store information temporarily when performing a complex query or when storing the result of a function.

5. What is the pg_toast schema, and how is it used in PostgreSQL database management?

Answer: The pg_toast schema is a hidden schema in PostgreSQL that is used to store large values that cannot fit in a single table row. It is used to store values like text, binary large objects, and large user-defined types. It is automatically managed by PostgreSQL, and it does not contain any user data or contribute to the overall size of the database. By default, the pg_toast schema is not visible to the user, as it is usually behind-the-scenes.

Tag

"SchemaMetrics"