How PostgreSQL Stores Data onto Disk

Have you ever wondered how a database actually stores data onto disk? In this blog post I'll be using PostgreSQL to answer the question.

Quickest way to learn is to spin up a local PostgreSQL instance and go from there. I'm using pgAdmin 4 to interact with the database.

Let's start by creating a database. You can follow along by running these commands in pgAdmin 4.

CREATE DATABASE Movies

Create a table Movies:

CREATE TABLE MOVIES (ID SERIAL, TITLE VARCHAR)

Insert a few records into the Movies table:

INSERT INTO MOVIES (TITLE) VALUES 
('Toy Story'),('Gladiator'),('Signs'),('Alien'),('Titanic'),('Inception'),('Avatar'),('Groundhog Day'),('Save the Last Dance'),
('Cloudy with a Chance of Meatballs')

Let's now go through and see what happens under the hood. To find the directory where data is stored, you can use the show data_directory command:

SHOW data_directory 

Navigating to the directory (I'm using Windows), you will see a bunch of files and directories.

The base directory contains the databases for your cluster. Each database is represented as directories named after their object id.

Finding out which directory number relates to which database is simple:

SELECT oid as object_id, datname as database_name FROM pg_database

We can see that our Movies database has an object id of 24636. Hence, the database lives in C:\Program Files\PostgreSQL\17\data\base\24636 directory.

But which file contains the data? PostgreSQL uses a heap file that contains data related to one particular table. The heap file is divided into many 8 KB blocks or pages. Each block stores some number of tuples.

A block itself contains:

• Block information
• A collection of pointers that point to the actual data in the block
• A section dedicated to free space that is not being used by the block
• The actual data

In our example, we have a Movies table. Let's see how this looks on disk. To find the file path of the table:

SELECT pg_relation_filepath('movies')

Opening the file in a hex viewer (I'm using Notepad++ with the HEX-Editor plugin), you can see the inserted records are now stored and visible on disk.

This data is consuming one 8 KB block on disk: