PostgreSQL Deep Dive Part 14 — Understanding Index Storage — Files, Structure, and Management

The Anatomy of Index Storage 🗄️

When you create an index in PostgreSQL, you're not just making an entry in a system table — you're initiating a complex series of filesystem operations. Let's break this down step by step.

File Location and Structure

-- First, let's see where PostgreSQL stores its files
SHOW data_directory;
 
-- Create a sample table and index
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT,
    price NUMERIC
);
 
CREATE INDEX idx_products_name ON products(name);
 
-- Find the physical location of our index
SELECT pg_relation_filepath('idx_products_name');

-- First, let's see where PostgreSQL stores its files
SHOW data_directory;
 
-- Create a sample table and index
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT,
    price NUMERIC
);
 
CREATE INDEX idx_products_name ON products(name);
 
-- Find the physical location of our index
SELECT pg_relation_filepath('idx_products_name');

The storage hierarchy looks like this:

$PGDATA/
└── base/
    └── $DATABASE_OID/
        ├── $TABLE_FILENODE
        ├── $INDEX1_FILENODE
        └── $INDEX2_FILENODE

$PGDATA/
└── base/
    └── $DATABASE_OID/
        ├── $TABLE_FILENODE
        ├── $INDEX1_FILENODE
        └── $INDEX2_FILENODE

File Management and Object IDs 🔍

PostgreSQL assigns unique Object IDs (OIDs) to manage these files. Let's explore this system:

-- Examining object IDs and file relationships
SELECT
    c.relname as object_name,
    c.relkind as object_type,
    c.relfilenode as file_node,
    c.oid as object_id,
    pg_relation_filepath(c.oid) as file_path,
    pg_size_pretty(pg_relation_size(c.oid)) as file_size
FROM pg_class c
WHERE c.relname IN ('products', 'idx_products_name');

-- Examining object IDs and file relationships
SELECT
    c.relname as object_name,
    c.relkind as object_type,
    c.relfilenode as file_node,
    c.oid as object_id,
    pg_relation_filepath(c.oid) as file_path,
    pg_size_pretty(pg_relation_size(c.oid)) as file_size
FROM pg_class c
WHERE c.relname IN ('products', 'idx_products_name');

Understanding the output:

• relname: Object name (table or index)
• relkind: Object type ('r' for table, 'i' for index)
• relfilenode: Actual filename on disk
• oid: Object identifier in the system catalog

This deep understanding of PostgreSQL's index storage mechanisms is crucial and can help you in many ways.

Remember: Every index you create has real storage implications. Understanding these helps make better decisions about index creation and maintenance strategies.

❖