A few days back, I was creating a table in a database for storing the user details.

The query was simple and was like this

CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

I was checking the command carefully, and a question popped into my mind. What actually happens when I execute a Create command in SQL? Yes, it will create a table, but how does that happen at the lower level? What are the steps that need to be completed to create a simple table? How is data stored on the disk?

SQL Parsing and Permission Checking

The first step in the execution of any SQL query is checking the syntax. First, the SQL engine parses the command to validate syntax. If the syntax is valid, then only whether the user has the permission to execute the query is checked; if the syntax is incorrect, it will throw an error.

Table Name Check

After checking the syntax and permission, the next check is done for the table name, whether the table name that the user is currently referring to create does it already exists in the given schema, or is it a new table? If the table already exists, then an error will be thrown; otherwise table will be created in the given schema.

Metadata Creation

A system catalog in a database management system is a central repository of metadata (data about the data), containing information about the database’s structure and internal components, such as tables, columns, indexes, users, and privileges. It stores this metadata in system tables or views, which users and applications can query to retrieve information about the database. Since a new table is being created, the system catalogs (metadata) are updated there with the new table details, including the new table, new columns, data types of the columns size of each

Physical Storage Allocation

After updating the catalog, PostgreSQL creates an entry for the table’s storage file under the database directory: base/<database_oid>/<relfilenode>. Here relfilenode is a unique identifier for the physical storage file. Initially, the file is empty (0 bytes). When rows are inserted, PostgreSQL allocates 8KB pages as needed, growing the file in multiples of that block size.

Transaction Processing

In SQL, every query is executed within the context of a transaction, whether explicitly started by the user or implicitly managed by the database. This means that when a CREATE statement is executed, the database doesn’t immediately and permanently apply the change; it first executes the command inside a transaction boundary. During this stage, the system updates internal catalogs, allocates storage if needed, and holds those changes in a pending state. The new table or object only becomes visible to other sessions once the transaction is successfully committed. If, for any reason, the transaction is rolled back — either due to an error or an explicit rollback request — the database undoes all the changes, and it is as if the CREATE statement was never issued.

Schema Assignment

The final step in creating a table is assigning it to a schema, which acts as a logical container or namespace within the database. When a CREATE TABLE statement is executed, the database checks whether the user has explicitly provided a schema name or not. If the schema name is provided, then the table is created inside that schema; if a schema is not provided, the table is placed into your default schema, such as public in PostgreSQL.

Query in Action

CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE
);

Executing the query, the result looks like

Let’s say I executed this query and want to generate a new table with the same name. Let’s see what happens…

We get an error..so we can see that the table name is checked in the catalog before creation..

Before creating the table, we can check in the catalog for the metadata about the tables.

After the creation of the table, it looks like this

Checking for the file on the disk..

In summary, a simple CREATE statement in SQL hides a surprisingly rich sequence of operations under the hood. From parsing and permission checks to catalog updates, physical storage allocation, transaction handling, and schema assignment, the database carefully ensures that the new table is valid, consistent, and securely integrated into its environment. While it might appear to the user as a single command, the database engine works through multiple layers to guarantee correctness and reliability.

Originally published on Medium.