Understanding Blocks in PostgreSQL

PostgreSQL, like many relational databases such as MySQL, Oracle, and Microsoft SQL Server, organizes data writes into blocks. Understanding how these blocks work can provide insight into how data storage and retrieval are managed, which can help you optimize your database performance.

What is a Block?

Blocks are the fundamental units of storage within PostgreSQL, and they play a critical role in how data is read from and written to disk.

The default block size is 8KB. This means that regardless of how small your data is, it will consume at least one 8KB block (I’ll show it to you below).

Demonstrating Block Usage with an Example

Create a table:

CREATE TABLE demo_table (ID SERIAL PRIMARY KEY, NAME VARCHAR(255) NOT NULL);

Insert a single value:

INSERT INTO demo_table (NAME) VALUES ('Example Name');

Single row that should consume only few bytes

Check the Disk Space Used:

You might expect that inserting a single row with just a few bytes of data would consume only a few bytes of disk space. However, PostgreSQL will consume at least one 8KB block due to the fixed block size. Let’s see the output:

SELECT pg_relation_size('demo_table') AS disk_size;

Single row with int and small text consumed 8kb

Conclusion (Efficient I/O vs Disk space)

The 8KB size is a trade-off to balance between efficient I/O operations and space utilization. Smaller block sizes could lead to excessive overhead due to too many I/O operations, while larger block sizes might waste space due to storing small amounts of data in large blocks.

Buy my a coffee