Hi there, I'm Sheroz and by profession I'm a Software Engineer who likes to explore complex engineering topics such as the internals of Postgres and likes to make them simpler to understand for myself and others too. In this series we'll try to explore the workings of Postgres on a simplistic level. The aim of this first in a series article would be to serve as a notes for understanding how Postgres works in order for making the most of it.
There's 4 basic things we need to cover straight up:
- The physical structure of Postgres
- The logical structure of Postgres
- Heap table structure and its use in Postgres
1. The physical structure of Postgres
A PostgreSQL server runs on a single host and manages a single database cluster. A database itself is a collection of object each which can be referred by OIDs(Object IDs). These objects include: Indexes, Heap Tables, and so on. Each Table also has an OID. Cluster contains multiple databases.
2. The logical structure of Postgres
Logically Postgres can be thought of as a file structure wherein you have a base directory and sub directories. A table for instance is itself a sub directory under a base directory. Postgres also has the concept of tablespaces where you can have a 'space' exist outside of the base directory and it can contain its own sub directory structure. The files essentially tables and indexes can be identified by a relfilenode which may or may not be the same as the OID depending of whether the the file has been altered or not. Files also contains associated free space maps and visibility maps.
3. Heap table structure in Postgres
Tables are stored as Heap Table Files divided into fixed pages(block) of fixed size, numbered sequentially and cane be assessed dynamically. The size also increases dynamically. Each block contains heap tuples (record data), line pointers (pointers to tuples), and other metadata. The data is read and written using this structure.
Top comments (0)