How Does PostgreSQL Store Your Data?

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done.

Step 1

On a fresh install of Ubuntu with PostgreSQL 17, let us create a database.

stoker@ThinkPad:~$ createdb demo
Password: 
stoker@ThinkPad:~$ psql demo
Password for user stoker: 
psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1))
Type "help" for help.

Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory.

demo=# show data_directory;
       data_directory        
-----------------------------
 /var/lib/postgresql/17/main
(1 row)

This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory. 

Step 2

Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simple table is created and populated with data. 

demo=# create table ex01 (a int, b int);
CREATE TABLE

demo=# insert into ex01 (a,b) values (1,1);

INSERT 0 1

demo=# select * from ex01;
 a | b 
---+---
 1 | 1
(1 row)

Step 3

Now, we can ask the server for details about the new table.

demo=# select oid, relname, relnamespace 

       from pg_class where relname ='ex01';
  oid  | relname | relnamespace 
-------+---------+--------------
 16390 | ex01    |         2200
(1 row)
demo=# 

The oid or object identifier is how PostgreSQL keeps internal track of the new table. If you are trying this on your own system, be advised that the OID is instance-dependent - you ain't going to get the same number. In this case, the oid is 16390, and we can look on the file system for it.

We run find on the data directory we retrieved earlier to locate the table.

root@ThinkPad:/var/lib/postgresql/17/main# find . | grep 16390
./base/16389/16390

Step 4

We have located the file on the file system where PostgreSQL is storing our table. And there is some interesting stuff to note. 

 
root@ThinkPad:/var/lib/postgresql/17/main/base/16389# ls -l 16390
-rw------- 1 postgres postgres 8192 Jan 15 10:53 16390

The file size is 8,192 bytes. 8k is the default size; if the table grows, it will grow in 8k increments. 

We can even peek into the file to see how the data is stored.

root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390
0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037
0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0
0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
*
0017740 362 002  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
0017760 001  \0 002  \0  \0  \t 030  \0 001  \0  \0  \0 001  \0  \0  \0
0020000
r 

Next Time

We will peer into the file to get more details on how that data is stored.