Set up Development Environment

Clone, Build

git clone https://github.com/postgres/postgres
cd postgres

./configure \
  --prefix=$(pwd)/.install \
  --enable-debug \
  --enable-cassert \
  --without-icu
bear - make
make install
export PATH=$(pwd)/.install/bin:$PATH

Initial database cluster

initdb -D .data

Configure Clangd

compile_commands.json: make with bear

.clangd:

If:
  PathMatch: .*\.h$
CompileFlags:
  Add:
    - -include postgres.h

Code Structure

The following is the main directories of Postgres codebase.

src/backend
├── access
│   ├── heap
│   ├── index
│   └── transam
├── catalog
├── executor
├── main
├── optimizer
├── parser
├── postmaster
└── storage
    ├── buffer
    ├── file
    ├── ipc
    ├── lmgr
    └── smgr

We will first look at storage and access. The former is responsible for managing pages on disk and their buffers in shared memory. The latter concerns the formats within a page, whether it is a heap table, B-tree index, or WAL log. The separation of page management and page format is convenient for extending access methods or storage. The following are examples:

pgvector is an extension that provides support for storing vector indexes inside Postgres.
neon is a distributed database built on top of Postgres. It replaces local disk management with S3-based page servers.

Heap Access Method

CREATE TABLE heap_test (
  id SERIAL PRIMARY KEY,
  a INT NOT NULL,
  b VARCHAR(255) NOT NULL,
  c BOOLEAN NOT NULL
);
INSERT INTO heap_test (a, b, c)
SELECT
  generate_series(1, 100),
  md5(random()::text),
  random() < 0.5;

Page Layout

Extension pageinspect can explore the physical page layout.

Build and install pageinspect

pushd contri/pageinspect
make
make install
popd
psql -c 'CREATE EXTENSION pageinspect;'

A page consists of a header and multiple tuples (also refered as lines or items).

+----------------+---------------------------------+
| PageHeaderData | linp1 linp2 linp3 ...           |
+-----------+----+---------------------------------+
| ... linpN |                                      |
+-----------+--------------------------------------+
|           ^ pd_lower                             |
|                                                  |
|             v pd_upper                           |
+-------------+------------------------------------+
|             | tupleN ...                         |
+-------------+------------------+-----------------+
|       ... tuple3 tuple2 tuple1 | "special space" |
+--------------------------------+-----------------+
                                 ^ pd_special

PageHeaderData is space management information generic to any page.

typedef struct PageHeaderData
{
  /* XXX LSN is member of *any* block, not only page-organized ones */
  PageXLogRecPtr pd_lsn;                     /* LSN: next byte after last byte of xlog
                                              * record for last change to this page */
  uint16 pd_checksum;                        /* checksum */
  uint16 pd_flags;                           /* flag bits, see below */
  LocationIndex pd_lower;                    /* offset to start of free space */
  LocationIndex pd_upper;                    /* offset to end of free space */
  LocationIndex pd_special;                  /* offset to start of special space */
  uint16 pd_pagesize_version;
  TransactionId pd_prune_xid;                /* oldest prunable XID, or zero if none */
  ItemIdData pd_linp[FLEXIBLE_ARRAY_MEMBER]; /* line pointer array */
} PageHeaderData;

typedef PageHeaderData *PageHeader;

pd_lsn: identifies xlog record for last change to this page.
pd_checksum: page checksum, if set.
pd_flags: flag bits.
pd_lower: offset to start of free space.
pd_upper: offset to end of free space.
pd_special: offset to start of special space.
pd_pagesize_version: size in bytes and page layout version number.
pd_prune_xid: oldest XID among potentially prunable tuples on page.

To inspect the page in table we created above:

postgres=# select * from page_header(get_raw_page('heap_test', 0));
    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/14E84B0 |        0 |     0 |   428 |   952 |    8192 |     8192 |       4 |         0
(1 row)

Line pointer (linp) is a fixed-size fat pointer to real tuple.

typedef struct ItemIdData
{
 unsigned lp_off:15,  /* offset to tuple (from start of page) */
          lp_flags:2, /* state of line pointer, see below */
          lp_len:15;  /* byte length of tuple */
} ItemIdData;

typedef ItemIdData *ItemId;

Each heap tuple consists of HeapTupleHeader and actual data.

typedef struct HeapTupleFields
{
  TransactionId t_xmin;   /* inserting xact ID */
  TransactionId t_xmax;   /* deleting or locking xact ID */

  union
  {
    CommandId t_cid;      /* inserting or deleting command ID, or both */
    TransactionId t_xvac; /* old-style VACUUM FULL xact ID */
  } t_field3;
} HeapTupleFields;

struct HeapTupleHeaderData
{
  union
  {
    HeapTupleFields t_heap;
   DatumTupleFields t_datum;
  } t_choice;

  ItemPointerData t_ctid;  /* current TID of this or newer tuple (or a
                            * speculative insertion token) */

  /* Fields below here must match MinimalTupleData! */

#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2
  uint16 t_infomask2;      /* number of attributes + various flags */

#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3
  uint16 t_infomask;       /* various flag bits, see below */

#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4
  uint8 t_hoff;            /* sizeof header incl. bitmap, padding */

  /* ^ - 23 bytes - ^ */

#define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5
  bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */

  /* MORE DATA FOLLOWS AT END OF STRUCT */
};

To inspect the first 5 tuples in the table:

postgres=# select * from heap_page_items(get_raw_page('heap_test', 0)) limit 5;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |                                         t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------------------------------------------------------------
  1 |   8120 |        1 |     66 |    746 |      0 |        0 | (0,1)  |           4 |       2306 |     24 |        |       | \x010000000100000043633736316331636439653330383035373531623465323266666437613432633901
  2 |   8048 |        1 |     66 |    746 |      0 |        0 | (0,2)  |           4 |       2306 |     24 |        |       | \x020000000200000043633736353635636135393064663437373330373534643664616631326236326101
  3 |   7976 |        1 |     66 |    746 |      0 |        0 | (0,3)  |           4 |       2306 |     24 |        |       | \x030000000300000043376132346439643734333731643563356163333966616230636566373034353700
  4 |   7904 |        1 |     66 |    746 |      0 |        0 | (0,4)  |           4 |       2306 |     24 |        |       | \x040000000400000043663562626233633534376435643162373465623034633362333063316637333100
  5 |   7832 |        1 |     66 |    746 |      0 |        0 | (0,5)  |           4 |       2306 |     24 |        |       | \x050000000500000043373035653238393330366232383664306434366137633534373035633361633800
(5 rows)

DQL

Heap Scan

EXPLAIN (COSTS OFF)
SELECT ctid, id, a, b, c
FROM heap_test
WHERE a >= 95;

Seq Scan on heap_test
  Filter: (a >= 95)

ExecSeqScan -> ExecScan -> SeqNext
  -> table_beginscan -> heap_beginscan
  -> table_scan_getnextslot -> heap_getnextslot
    -> heapgettup_pagemode
      -> heap_prepare_pagescan
        -> page_collect_tuples

The call path has two branches: SeqNext() starts the scan once through table_beginscan(), then requests each tuple through table_scan_getnextslot(). The table-AM wrappers dispatch both operations to the heap implementation. The remaining heap functions fetch pages, collect visible offsets, and return one tuple.

These layers reject tuples for different reasons. page_collect_tuples() checks MVCC visibility. heapgettup_pagemode() can apply table-AM scan keys, but this sequential scan passes zero scan keys. Finally, ExecScan() evaluates a >= 95 and projects ctid, id, a, b, c only after the tuple passes that filter.

ExecSeqScan() delegates to ExecScan(), which filters and projects tuples from SeqNext().

/* src/backend/executor/nodeSeqscan.c:ExecSeqScan, SeqNext */
static TupleTableSlot *
ExecSeqScan(PlanState *pstate)
{
    SeqScanState *node = castNode(SeqScanState, pstate);

    return ExecScan(&node->ss,
                    (ExecScanAccessMtd) SeqNext,
                    (ExecScanRecheckMtd) SeqRecheck);
}

/* src/backend/executor/execScan.c:ExecScan */
TupleTableSlot *
ExecScan(ScanState *node,
         ExecScanAccessMtd accessMtd,
         ExecScanRecheckMtd recheckMtd)
{
    ExprContext *econtext = node->ps.ps_ExprContext;
    ExprState *qual = node->ps.qual;
    ProjectionInfo *projInfo = node->ps.ps_ProjInfo;

    if (!qual && !projInfo)
    {
        ResetExprContext(econtext);
        return ExecScanFetch(node, accessMtd, recheckMtd);
    }

    ResetExprContext(econtext);

    for (;;)
    {
        TupleTableSlot *slot;

        slot = ExecScanFetch(node, accessMtd, recheckMtd);
        if (TupIsNull(slot))
        {
            if (projInfo)
                return ExecClearTuple(projInfo->pi_state.resultslot);
            return slot;
        }

        econtext->ecxt_scantuple = slot;

        if (qual == NULL || ExecQual(qual, econtext))
        {
            if (projInfo)
                return ExecProject(projInfo);
            return slot;
        }

        InstrCountFiltered1(node, 1);
        ResetExprContext(econtext);
    }
}

/* src/backend/executor/nodeSeqscan.c:SeqNext */
static TupleTableSlot *
SeqNext(SeqScanState *node)
{
    TableScanDesc scandesc = node->ss.ss_currentScanDesc;
    EState *estate = node->ss.ps.state;
    TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;

    if (scandesc == NULL)
    {
        scandesc = table_beginscan(node->ss.ss_currentRelation,
                                   estate->es_snapshot, 0, NULL);
        node->ss.ss_currentScanDesc = scandesc;
    }

    if (table_scan_getnextslot(scandesc, estate->es_direction, slot))
        return slot;
    return NULL;
}

table_beginscan() dispatches scan_begin; heap_beginscan() initializes the heap scan and its read stream.

/* src/include/access/tableam.h:table_beginscan */
static inline TableScanDesc
table_beginscan(Relation rel, Snapshot snapshot,
                int nkeys, struct ScanKeyData *key)
{
    uint32 flags = SO_TYPE_SEQSCAN |
        SO_ALLOW_STRAT | SO_ALLOW_SYNC | SO_ALLOW_PAGEMODE;

    return rel->rd_tableam->scan_begin(rel, snapshot,
                                       nkeys, key, NULL, flags);
}

/* src/backend/access/heap/heapam.c:heap_beginscan */
TableScanDesc
heap_beginscan(Relation relation, Snapshot snapshot,
               int nkeys, ScanKey key,
               ParallelTableScanDesc parallel_scan,
               uint32 flags)
{
    HeapScanDesc scan;

    RelationIncrementReferenceCount(relation);
    scan = (HeapScanDesc) palloc(sizeof(HeapScanDescData));
    scan->rs_base.rs_rd = relation;
    scan->rs_base.rs_snapshot = snapshot;
    scan->rs_base.rs_nkeys = nkeys;
    scan->rs_base.rs_flags = flags;
    scan->rs_base.rs_parallel = parallel_scan;
    /* ... */

    initscan(scan, key, false);

    if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN ||
        scan->rs_base.rs_flags & SO_TYPE_TIDRANGESCAN)
    {
        ReadStreamBlockNumberCB cb;

        if (scan->rs_base.rs_parallel)
            cb = heap_scan_stream_read_next_parallel;
        else
            cb = heap_scan_stream_read_next_serial;

        scan->rs_read_stream =
            read_stream_begin_relation(READ_STREAM_SEQUENTIAL,
                                       scan->rs_strategy,
                                       scan->rs_base.rs_rd,
                                       MAIN_FORKNUM,
                                       cb, scan, 0);
    }

    return (TableScanDesc) scan;
}

table_scan_getnextslot() dispatches scan_getnextslot; heap_getnextslot() runs the heap scan and stores its result.

/* src/include/access/tableam.h:table_scan_getnextslot */
static inline bool
table_scan_getnextslot(TableScanDesc sscan, ScanDirection direction,
                       TupleTableSlot *slot)
{
    slot->tts_tableOid = RelationGetRelid(sscan->rs_rd);
    /* ... */
    return sscan->rs_rd->rd_tableam->scan_getnextslot(sscan,
                                                       direction, slot);
}

/* src/backend/access/heap/heapam.c:heap_getnextslot */
bool
heap_getnextslot(TableScanDesc sscan, ScanDirection direction,
                 TupleTableSlot *slot)
{
    HeapScanDesc scan = (HeapScanDesc) sscan;

    if (sscan->rs_flags & SO_ALLOW_PAGEMODE)
        heapgettup_pagemode(scan, direction,
                            sscan->rs_nkeys, sscan->rs_key);
    else
        heapgettup(scan, direction,
                   sscan->rs_nkeys, sscan->rs_key);

    if (scan->rs_ctup.t_data == NULL)
    {
        ExecClearTuple(slot);
        return false;
    }

    pgstat_count_heap_getnext(scan->rs_base.rs_rd);
    ExecStoreBufferHeapTuple(&scan->rs_ctup, slot, scan->rs_cbuf);
    return true;
}

heapgettup_pagemode() fetches pages and points the current tuple at each selected page item.

/* src/backend/access/heap/heapam.c:heapgettup_pagemode */
static void
heapgettup_pagemode(HeapScanDesc scan, ScanDirection dir,
                    int nkeys, ScanKey key)
{
    HeapTuple tuple = &(scan->rs_ctup);
    Page page;
    int lineindex;
    int linesleft;

    if (likely(scan->rs_inited))
    {
        page = BufferGetPage(scan->rs_cbuf);
        lineindex = scan->rs_cindex + dir;
        linesleft = ScanDirectionIsForward(dir)
            ? scan->rs_ntuples - lineindex
            : scan->rs_cindex;
        goto continue_page;
    }

    while (true)
    {
        heap_fetch_next_buffer(scan, dir);
        if (!BufferIsValid(scan->rs_cbuf))
            break;

        heap_prepare_pagescan((TableScanDesc) scan);
        page = BufferGetPage(scan->rs_cbuf);
        linesleft = scan->rs_ntuples;
        lineindex = ScanDirectionIsForward(dir) ? 0 : linesleft - 1;

continue_page:
        for (; linesleft > 0; linesleft--, lineindex += dir)
        {
            OffsetNumber lineoff = scan->rs_vistuples[lineindex];
            ItemId lpp = PageGetItemId(page, lineoff);

            tuple->t_data = (HeapTupleHeader) PageGetItem(page, lpp);
            tuple->t_len = ItemIdGetLength(lpp);
            ItemPointerSet(&tuple->t_self, scan->rs_cblock, lineoff);

            if (key != NULL &&
                !HeapKeyTest(tuple, RelationGetDescr(scan->rs_base.rs_rd),
                             nkeys, key))
                continue;

            scan->rs_cindex = lineindex;
            return;
        }
    }

    if (BufferIsValid(scan->rs_cbuf))
        ReleaseBuffer(scan->rs_cbuf);
    scan->rs_cbuf = InvalidBuffer;
    scan->rs_cblock = InvalidBlockNumber;
    tuple->t_data = NULL;
    scan->rs_inited = false;
}

heap_prepare_pagescan() prunes and locks the page, then delegates visibility collection to page_collect_tuples().

/* src/backend/access/heap/heapam.c:heap_prepare_pagescan */
void
heap_prepare_pagescan(TableScanDesc sscan)
{
    HeapScanDesc scan = (HeapScanDesc) sscan;
    Buffer buffer = scan->rs_cbuf;
    BlockNumber block = scan->rs_cblock;
    Snapshot snapshot = scan->rs_base.rs_snapshot;
    Page page;
    int lines;
    bool all_visible;
    bool check_serializable;

    heap_page_prune_opt(scan->rs_base.rs_rd, buffer);
    LockBuffer(buffer, BUFFER_LOCK_SHARE);

    page = BufferGetPage(buffer);
    lines = PageGetMaxOffsetNumber(page);
    all_visible = PageIsAllVisible(page) && !snapshot->takenDuringRecovery;
    check_serializable =
        CheckForSerializableConflictOutNeeded(scan->rs_base.rs_rd, snapshot);

    if (likely(all_visible))
    {
        if (likely(!check_serializable))
            scan->rs_ntuples = page_collect_tuples(scan, snapshot, page, buffer,
                                                   block, lines, true, false);
        else
            scan->rs_ntuples = page_collect_tuples(scan, snapshot, page, buffer,
                                                   block, lines, true, true);
    }
    else
    {
        if (likely(!check_serializable))
            scan->rs_ntuples = page_collect_tuples(scan, snapshot, page, buffer,
                                                   block, lines, false, false);
        else
            scan->rs_ntuples = page_collect_tuples(scan, snapshot, page, buffer,
                                                   block, lines, false, true);
    }

    LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
}

page_collect_tuples() records the offset of every normal tuple visible to the scan snapshot.

/* src/backend/access/heap/heapam.c:page_collect_tuples */
static int
page_collect_tuples(HeapScanDesc scan, Snapshot snapshot,
                    Page page, Buffer buffer,
                    BlockNumber block, int lines,
                    bool all_visible, bool check_serializable)
{
    int ntup = 0;

    for (OffsetNumber lineoff = FirstOffsetNumber;
         lineoff <= lines;
         lineoff++)
    {
        ItemId lpp = PageGetItemId(page, lineoff);
        HeapTupleData loctup;
        bool valid;

        if (!ItemIdIsNormal(lpp))
            continue;

        loctup.t_data = (HeapTupleHeader) PageGetItem(page, lpp);
        loctup.t_len = ItemIdGetLength(lpp);
        loctup.t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
        ItemPointerSet(&loctup.t_self, block, lineoff);

        valid = all_visible ||
            HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);

        if (check_serializable)
            HeapCheckForSerializableConflictOut(valid, scan->rs_base.rs_rd,
                                                &loctup, buffer, snapshot);

        if (valid)
            scan->rs_vistuples[ntup++] = lineoff;
    }

    return ntup;
}

Tuple Passing

PostgreSQL keeps tuples in shared buffers and passes references whenever the consumer’s lifetime allows it.

Method or stage	What passes to the next stage	Tuple copy
Buffer manager	A page in a shared-buffer frame	Page transfer on a miss; not a per-tuple copy
`heapgettup_pagemode()`	`HeapTupleData` whose `t_data` points into the page	No
`ExecStoreBufferHeapTuple()`	The tuple pointer plus a buffer pin	No
Predicate in `ExecScan()`	The same scan slot	No
Projection in `ExecProject()`	`Datum` values in a virtual slot	No full tuple copy; expressions may create values
Parent executor node	Usually a `TupleTableSlot *`	No
`ExecMaterializeSlot()` or an owning plan node	Independently owned tuple data	Yes, when independent storage is required

Tuple references appear throughout a simple sequential scan:

heapgettup_pagemode() stores the pointer from PageGetItem() in scan->rs_ctup.t_data.
ExecStoreBufferHeapTuple() stores that tuple reference in the scan slot and takes another pin on the page.
ExecProcNode() and ordinary parent/child calls pass a TupleTableSlot * between executor nodes.
A simple projection creates a virtual result slot. It copies pass-by-value attributes, such as an int, into Datum values, but pass-by-reference values usually continue to point into the pinned page.

tts_buffer_heap_store_tuple() stores the tuple pointer and pins its page instead of copying the tuple body.

/* src/backend/executor/execTuples.c:tts_buffer_heap_store_tuple */
static inline void
tts_buffer_heap_store_tuple(TupleTableSlot *slot, HeapTuple tuple,
                            Buffer buffer, bool transfer_pin)
{
    BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot;

    /* Cleanup of a previously materialized tuple omitted. */
    slot->tts_flags &= ~TTS_FLAG_EMPTY;
    slot->tts_nvalid = 0;
    bslot->base.tuple = tuple;
    bslot->base.off = 0;
    slot->tts_tid = tuple->t_self;

    if (bslot->buffer != buffer)
    {
        if (BufferIsValid(bslot->buffer))
            ReleaseBuffer(bslot->buffer);

        bslot->buffer = buffer;
        if (!transfer_pin && BufferIsValid(buffer))
            IncrBufferRefCount(buffer);
    }
    else if (transfer_pin && BufferIsValid(buffer))
        ReleaseBuffer(buffer);
}

For a simple sequential scan, the scan path makes zero full heap tuple copies after the page enters shared buffers.

A data copy happens when PostgreSQL needs to move data or own it independently:

On a buffer miss, the storage and buffer managers transfer the whole page into a shared-buffer frame. This is a page transfer, not an additional copy of each tuple.
ExecMaterializeSlot() copies a buffer-backed tuple before it releases the buffer pin.
ExecCopySlot() can copy tuple data when the source and destination slot formats cannot share the same buffer-backed representation. Compatible buffer slots can instead share the page with separate pins.
Sort, hash, materialization, and tuplestore nodes copy or serialize the data that they must retain beyond the input slot’s lifetime.
Projection expressions can allocate new values or detoast existing values, although a simple Var projection normally reuses the input Datum.

tts_buffer_heap_materialize() copies a buffer-backed tuple when the slot needs independent storage.

/* src/backend/executor/execTuples.c:tts_buffer_heap_materialize */
static void
tts_buffer_heap_materialize(TupleTableSlot *slot)
{
    BufferHeapTupleTableSlot *bslot = (BufferHeapTupleTableSlot *) slot;
    MemoryContext oldContext;

    if (TTS_SHOULDFREE(slot))
        return;

    oldContext = MemoryContextSwitchTo(slot->tts_mcxt);
    bslot->base.off = 0;
    slot->tts_nvalid = 0;

    if (!bslot->base.tuple)
        bslot->base.tuple = heap_form_tuple(slot->tts_tupleDescriptor,
                                            slot->tts_values,
                                            slot->tts_isnull);
    else
    {
        bslot->base.tuple = heap_copytuple(bslot->base.tuple);
        if (BufferIsValid(bslot->buffer))
            ReleaseBuffer(bslot->buffer);
        bslot->buffer = InvalidBuffer;
    }

    slot->tts_flags |= TTS_FLAG_SHOULDFREE;
    MemoryContextSwitchTo(oldContext);
}

DML

Insert

INSERT INTO heap_test (a, b, c) VALUES (1, 'test', true);

ExecModifyTable
  -> ExecInsert
    -> table_tuple_insert
      -> heapam_tuple_insert
        -> heap_insert

table_tuple_insert() dispatches through the relation's table AM, which maps heap insertion to heapam_tuple_insert().

/* src/include/access/tableam.h:table_tuple_insert */
static inline void
table_tuple_insert(Relation rel, TupleTableSlot *slot, CommandId cid,
                   int options, struct BulkInsertStateData *bistate)
{
    rel->rd_tableam->tuple_insert(rel, slot, cid, options, bistate);
}

/* src/backend/access/heap/heapam_handler.c:heapam_methods */
static const TableAmRoutine heapam_methods = {
    /* ... */
    .tuple_insert = heapam_tuple_insert,
    /* ... */
};

heapam_tuple_insert() converts the slot to a heap tuple and copies the assigned TID back to the slot.

/* src/backend/access/heap/heapam_handler.c:heapam_tuple_insert */
static void
heapam_tuple_insert(Relation relation, TupleTableSlot *slot, CommandId cid,
                    int options, BulkInsertState bistate)
{
    bool shouldFree = true;
    HeapTuple tuple = ExecFetchSlotHeapTuple(slot, true, &shouldFree);

    slot->tts_tableOid = RelationGetRelid(relation);
    tuple->t_tableOid = slot->tts_tableOid;

    heap_insert(relation, tuple, cid, options, bistate);
    ItemPointerCopy(&tuple->t_self, &slot->tts_tid);

    if (shouldFree)
        pfree(tuple);
}

The table-AM interface lets PostgreSQL support the built-in heap access method and extension-provided table access methods. See Table Access Method Interface Definition.

heap_insert() prepares the tuple, chooses a page, updates the shared buffer, and records the change in WAL.

heap_insert() prepares and places the tuple, marks the buffer dirty, writes WAL, and releases the buffer.

/* src/backend/access/heap/heapam.c:heap_insert */
void
heap_insert(Relation relation, HeapTuple tup, CommandId cid,
            int options, BulkInsertState bistate)
{
    TransactionId xid = GetCurrentTransactionId();
    HeapTuple heaptup;
    Buffer buffer;
    Buffer vmbuffer = InvalidBuffer;

    heaptup = heap_prepare_insert(relation, tup, xid, cid, options);

    buffer = RelationGetBufferForTuple(relation, heaptup->t_len,
                                       InvalidBuffer, options, bistate,
                                       &vmbuffer, NULL, 0);

    START_CRIT_SECTION();

    RelationPutHeapTuple(relation, buffer, heaptup,
                         (options & HEAP_INSERT_SPECULATIVE) != 0);
    /* Clear all-visible state when necessary. */
    MarkBufferDirty(buffer);

    if (RelationNeedsWAL(relation))
    {
        /* Build and insert the XLOG_HEAP_INSERT record. */
        /* ... */
    }

    END_CRIT_SECTION();

    UnlockReleaseBuffer(buffer);
    if (vmbuffer != InvalidBuffer)
        ReleaseBuffer(vmbuffer);

    /* Cache invalidation, statistics, and tuple cleanup omitted. */
}

Multi-Insert

COPY heap_test (a, b, c) FROM STDIN WITH (FORMAT csv);
101,multi-1,true
102,multi-2,false
103,multi-3,true
\.

CopyFrom
  -> CopyMultiInsertInfoFlush
    -> CopyMultiInsertBufferFlush
      -> table_multi_insert
        -> heap_multi_insert

CopyFrom() flushes buffered tuple slots through table_multi_insert().

/* src/include/access/tableam.h:table_multi_insert */
static inline void
table_multi_insert(Relation rel, TupleTableSlot **slots, int nslots,
                   CommandId cid, int options,
                   struct BulkInsertStateData *bistate)
{
    rel->rd_tableam->multi_insert(rel, slots, nslots,
                                  cid, options, bistate);
}

The heap table AM maps multi_insert directly to heap_multi_insert().

/* src/backend/access/heap/heapam_handler.c:heapam_methods */
static const TableAmRoutine heapam_methods = {
    /* ... */
    .multi_insert = heap_multi_insert,
    /* ... */
};

heap_multi_insert() follows the same basic process as heap_insert(), but it groups tuple placement, page locking, and WAL by page.

heap_multi_insert() prepares all tuples, fills each page, emits one multi-insert WAL record per page, and returns the assigned TIDs.

/* src/backend/access/heap/heapam.c:heap_multi_insert */
void
heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples,
                  CommandId cid, int options, BulkInsertState bistate)
{
    TransactionId xid = GetCurrentTransactionId();
    HeapTuple *heaptuples;
    int ndone = 0;

    heaptuples = palloc(ntuples * sizeof(HeapTuple));
    for (int i = 0; i < ntuples; i++)
    {
        HeapTuple tuple = ExecFetchSlotHeapTuple(slots[i], true, NULL);

        slots[i]->tts_tableOid = RelationGetRelid(relation);
        tuple->t_tableOid = slots[i]->tts_tableOid;
        heaptuples[i] = heap_prepare_insert(relation, tuple, xid, cid,
                                            options);
    }

    while (ndone < ntuples)
    {
        Buffer buffer;
        Page page;
        int nthispage;

        buffer = RelationGetBufferForTuple(relation,
                                           heaptuples[ndone]->t_len,
                                           InvalidBuffer, options, bistate,
                                           &vmbuffer, NULL,
                                           npages - npages_used);
        page = BufferGetPage(buffer);

        START_CRIT_SECTION();
        RelationPutHeapTuple(relation, buffer, heaptuples[ndone], false);

        for (nthispage = 1; ndone + nthispage < ntuples; nthispage++)
        {
            HeapTuple heaptup = heaptuples[ndone + nthispage];

            if (PageGetHeapFreeSpace(page) <
                MAXALIGN(heaptup->t_len) + saveFreeSpace)
                break;
            RelationPutHeapTuple(relation, buffer, heaptup, false);
        }

        MarkBufferDirty(buffer);
        if (needwal)
        {
            /* Emit XLOG_HEAP2_MULTI_INSERT for nthispage tuples. */
            /* ... */
        }
        END_CRIT_SECTION();

        UnlockReleaseBuffer(buffer);
        ndone += nthispage;
    }

    for (int i = 0; i < ntuples; i++)
        slots[i]->tts_tid = heaptuples[i]->t_self;
}

Compared with repeatedly calling heap_insert, multi-insert locks each heap page once and normally emits one WAL record per page rather than per tuple. A batch that spans several pages still performs one buffer and WAL cycle for each page. Afterward, COPY FROM creates index entries and runs row-level triggers one tuple at a time. An ordinary multi-row INSERT does not use this path; it calls table_tuple_insert for each row.

Delete

DELETE FROM heap_test WHERE id = 1;

ExecModifyTable
  -> ExecDelete
    -> ExecDeleteAct
      -> table_tuple_delete
        -> heapam_tuple_delete
          -> heap_delete

A heap delete is an MVCC operation. It does not immediately remove the tuple or its index entries. Instead, it records the deleting transaction in the tuple header; VACUUM can reclaim the storage after no snapshot can see the old version. The scan that finds the row supplies its TID to the modify-table executor.

ExecDeleteAct() passes the TID and the command's snapshots to the table access method.

/* src/backend/executor/nodeModifyTable.c:ExecDeleteAct */
static TM_Result
ExecDeleteAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
              ItemPointer tupleid, bool changingPart)
{
    EState *estate = context->estate;

    return table_tuple_delete(resultRelInfo->ri_RelationDesc, tupleid,
                              estate->es_output_cid,
                              estate->es_snapshot,
                              estate->es_crosscheck_snapshot,
                              true /* wait for commit */,
                              &context->tmfd,
                              changingPart);
}

table_tuple_delete() dispatches through rd_tableam.

/* src/include/access/tableam.h:table_tuple_delete */
static inline TM_Result
table_tuple_delete(Relation rel, ItemPointer tid, CommandId cid,
                   Snapshot snapshot, Snapshot crosscheck, bool wait,
                   TM_FailureData *tmfd, bool changingPart)
{
    return rel->rd_tableam->tuple_delete(rel, tid, cid,
                                         snapshot, crosscheck,
                                         wait, tmfd, changingPart);
}

The heap callback is a small wrapper around heap_delete().

/* src/backend/access/heap/heapam_handler.c:heapam_tuple_delete */
static TM_Result
heapam_tuple_delete(Relation relation, ItemPointer tid, CommandId cid,
                    Snapshot snapshot, Snapshot crosscheck, bool wait,
                    TM_FailureData *tmfd, bool changingPart)
{
    return heap_delete(relation, tid, cid, crosscheck, wait,
                       tmfd, changingPart);
}

heap_delete() locks the target page, checks concurrent tuple state, computes the new xmax, and marks the old tuple version as deleted.

heap_delete() checks the target tuple and records the deleting transaction without removing the tuple bytes.

/* src/backend/access/heap/heapam.c:heap_delete */
TM_Result
heap_delete(Relation relation, ItemPointer tid,
            CommandId cid, Snapshot crosscheck, bool wait,
            TM_FailureData *tmfd, bool changingPart)
{
    TransactionId xid = GetCurrentTransactionId();
    Buffer vmbuffer = InvalidBuffer;
    TransactionId new_xmax;
    uint16 new_infomask;
    uint16 new_infomask2;
    bool iscombo;
    BlockNumber block = ItemPointerGetBlockNumber(tid);
    Buffer buffer = ReadBuffer(relation, block);
    Page page = BufferGetPage(buffer);
    HeapTupleData tp;

    if (PageIsAllVisible(page))
        visibilitymap_pin(relation, block, &vmbuffer);
    LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);

    ItemId lp = PageGetItemId(page, ItemPointerGetOffsetNumber(tid));
    tp.t_data = (HeapTupleHeader) PageGetItem(page, lp);
    tp.t_len = ItemIdGetLength(lp);
    tp.t_self = *tid;

    TM_Result result = HeapTupleSatisfiesUpdate(&tp, cid, buffer);
    /* Wait for a concurrent writer and recheck when necessary. */
    if (result != TM_Ok)
    {
        /* Fill tmfd, release locks and pins, and return result. */
        /* ... */
    }

    HeapTupleHeaderAdjustCmax(tp.t_data, &cid, &iscombo);
    compute_new_xmax_infomask(HeapTupleHeaderGetRawXmax(tp.t_data),
                              tp.t_data->t_infomask,
                              tp.t_data->t_infomask2,
                              xid, LockTupleExclusive, true,
                              &new_xmax, &new_infomask,
                              &new_infomask2);

    START_CRIT_SECTION();
    PageSetPrunable(page, xid);

    if (PageIsAllVisible(page))
    {
        PageClearAllVisible(page);
        visibilitymap_clear(relation, block, vmbuffer,
                            VISIBILITYMAP_VALID_BITS);
    }

    tp.t_data->t_infomask &= ~(HEAP_XMAX_BITS | HEAP_MOVED);
    tp.t_data->t_infomask2 &= ~HEAP_KEYS_UPDATED;
    tp.t_data->t_infomask |= new_infomask;
    tp.t_data->t_infomask2 |= new_infomask2;
    HeapTupleHeaderSetXmax(tp.t_data, new_xmax);
    HeapTupleHeaderSetCmax(tp.t_data, cid, iscombo);
    tp.t_data->t_ctid = tp.t_self;
    MarkBufferDirty(buffer);

    /* Write XLOG_HEAP_DELETE when the relation needs WAL. */
    /* ... */
    END_CRIT_SECTION();

    /* Release locks and pins, remove external TOAST values, and count it. */
    return TM_Ok;
}

If another transaction is modifying the tuple, heap_delete() may release the buffer lock, acquire a heavyweight tuple lock, wait, and then recheck. It returns statuses such as TM_Updated, TM_Deleted, TM_SelfModified, or TM_BeingModified to the executor.

When WAL is required, heap_delete writes an XLOG_HEAP_DELETE record with the tuple offset, new xmax, relevant header flags, and replica identity data needed by logical decoding. It then releases the buffer lock, deletes external TOAST values using MVCC, releases the pins and tuple lock, and updates statistics.

Index entries are intentionally left in place because an older snapshot may still use them to find the tuple. A later VACUUM removes dead index entries and reclaims the heap space.

Update

ExecModifyTable
  -> ExecUpdate
    -> ExecUpdateAct
      -> table_tuple_update
        -> heapam_tuple_update
          -> heap_update

An update creates a new tuple version and marks the old version with xmax. It therefore resembles a delete followed by an insert, but PostgreSQL normally performs both parts inside heap_update() rather than calling heap_delete() and heap_insert() separately.

heap_update() links the old tuple’s t_ctid to the new version. When no indexed column changes and the new tuple fits on the same page, it can create a HOT update and avoid new index entries. A cross-partition update is the literal exception: ExecCrossPartitionUpdate() deletes the tuple from the old leaf and inserts it into the new leaf.

Concurrency

PostgreSQL combines relation locks, buffer synchronization, tuple locks, and MVCC. Each mechanism protects a different thing:

Mechanism	DDL	`UPDATE` / `DELETE`	`INSERT`	Plain `SELECT`
Relation lock	Varies; often `AccessExclusiveLock`	`RowExclusiveLock`	`RowExclusiveLock`	`AccessShareLock`
Buffer pin	Pins buffers as needed	Pins old and new heap pages	Pins the destination page	The scan and slot pin the current page
Buffer content lock	Varies by operation	Exclusive while changing a page	Exclusive while adding tuples	Shared while collecting visible offsets
Tuple lock	Usually none	Stores ownership in `xmax`; may use a heavyweight lock while waiting	None for the new tuple	None; locking clauses are exceptions
MVCC metadata	Not the primary mechanism	Sets `xmax`; an update creates a new version	Sets `xmin`	Tests `xmin`, `xmax`, and infomask against the snapshot

AccessShareLock and RowExclusiveLock are compatible, so ordinary reads and writes can run together. AccessShareLock conflicts with AccessExclusiveLock, so a scan protects its relation from DROP TABLE, TRUNCATE, and DDL that requires exclusive access.

A page-at-a-time heap scan uses the page synchronization mechanisms in this order:

reader
  -> pin the buffer
  -> take a shared content lock
    -> inspect line pointers and evaluate MVCC visibility
  -> release the content lock
    -> follow tuple pointers and deform attributes without the content lock
  -> release the pins when the scan and slot leave the page

writer
  -> pin the buffer
  -> take an exclusive content lock
    -> add a tuple or change tuple metadata
  -> release the content lock and pin

VACUUM or page pruning
  -> check that the visibility horizon permits removal
  -> acquire a cleanup lock after competing pins leave
    -> physically remove or move dead tuples

The buffer pin keeps the page in the buffer pool and keeps tuple addresses stable. It does not prevent ordinary changes to that page. For example, a concurrent DELETE can take the exclusive content lock and set the tuple’s xmax while a reader keeps the page pinned. The delete does not remove the tuple bytes, so the reader can continue using the version selected by its snapshot.

The shared content lock protects the reader only while heap_prepare_pagescan() examines line pointers and visibility. After that function collects the visible offsets, the reader releases the content lock. The retained pin prevents VACUUM and page pruning from obtaining the cleanup lock that they need to move or remove those tuples. Tuple-at-a-time mode instead takes and releases the shared content lock for each selected tuple.

Normal updates also preserve user data in the old tuple version. They create a new tuple version and connect the versions through tuple metadata. A reader’s snapshot chooses the appropriate version, while tuple locks coordinate writers that target the same row. Plain SELECT never waits on those tuple locks.

Finally, the all-visible page flag lets a heap scan skip per-tuple visibility checks outside recovery. A writer clears that flag and the corresponding visibility-map bits before it makes the page no longer all-visible, so the fast path preserves the same snapshot semantics.

Catalog

Catalogs in PostgreSQL is also stored in (heap) tables. These tables have fixed Oid and attributes so that there is no recursive lookup.

Example: pg_class

CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,RelationRelation_Rowtype_Id) BKI_SCHEMA_MACRO
{
	/* oid */
	Oid			oid;

	/* class name */
	NameData	relname;

	/* OID of namespace containing this class */
	Oid			relnamespace BKI_DEFAULT(pg_catalog) BKI_LOOKUP(pg_namespace);

	/* OID of entry in pg_type for relation's implicit row type, if any */
	Oid			reltype BKI_LOOKUP_OPT(pg_type);

	/* OID of entry in pg_type for underlying composite type, if any */
	Oid			reloftype BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_type);

	/* class owner */
	Oid			relowner BKI_DEFAULT(POSTGRES) BKI_LOOKUP(pg_authid);

	/* access method; 0 if not a table / index */
	Oid			relam BKI_DEFAULT(heap) BKI_LOOKUP_OPT(pg_am);

	/* identifier of physical storage file */
	/* relfilenode == 0 means it is a "mapped" relation, see relmapper.c */
	Oid			relfilenode BKI_DEFAULT(0);

	/* identifier of table space for relation (0 means default for database) */
	Oid			reltablespace BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_tablespace);

	/* # of blocks (not always up-to-date) */
	int32		relpages BKI_DEFAULT(0);

	/* # of tuples (not always up-to-date; -1 means "unknown") */
	float4		reltuples BKI_DEFAULT(-1);

	/* # of all-visible blocks (not always up-to-date) */
	int32		relallvisible BKI_DEFAULT(0);

	/* OID of toast table; 0 if none */
	Oid			reltoastrelid BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_class);

	/* T if has (or has had) any indexes */
	bool		relhasindex BKI_DEFAULT(f);

	/* T if shared across databases */
	bool		relisshared BKI_DEFAULT(f);

	/* see RELPERSISTENCE_xxx constants below */
	char		relpersistence BKI_DEFAULT(p);

	/* see RELKIND_xxx constants below */
	char		relkind BKI_DEFAULT(r);

	/* number of user attributes */
	int16		relnatts BKI_DEFAULT(0);	/* genbki.pl will fill this in */

	/*
	 * Class pg_attribute must contain exactly "relnatts" user attributes
	 * (with attnums ranging from 1 to relnatts) for this class.  It may also
	 * contain entries with negative attnums for system attributes.
	 */

	/* # of CHECK constraints for class */
	int16		relchecks BKI_DEFAULT(0);

	/* has (or has had) any rules */
	bool		relhasrules BKI_DEFAULT(f);

	/* has (or has had) any TRIGGERs */
	bool		relhastriggers BKI_DEFAULT(f);

	/* has (or has had) child tables or indexes */
	bool		relhassubclass BKI_DEFAULT(f);

	/* row security is enabled or not */
	bool		relrowsecurity BKI_DEFAULT(f);

	/* row security forced for owners or not */
	bool		relforcerowsecurity BKI_DEFAULT(f);

	/* matview currently holds query results */
	bool		relispopulated BKI_DEFAULT(t);

	/* see REPLICA_IDENTITY_xxx constants */
	char		relreplident BKI_DEFAULT(n);

	/* is relation a partition? */
	bool		relispartition BKI_DEFAULT(f);

	/* link to original rel during table rewrite; otherwise 0 */
	Oid			relrewrite BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_class);

	/* all Xids < this are frozen in this rel */
	TransactionId relfrozenxid BKI_DEFAULT(3);	/* FirstNormalTransactionId */

	/* all multixacts in this rel are >= this; it is really a MultiXactId */
	TransactionId relminmxid BKI_DEFAULT(1);	/* FirstMultiXactId */

#ifdef CATALOG_VARLEN			/* variable-length fields start here */
	/* NOTE: These fields are not present in a relcache entry's rd_rel field. */
	/* access permissions */
	aclitem		relacl[1] BKI_DEFAULT(_null_);

	/* access-method-specific options */
	text		reloptions[1] BKI_DEFAULT(_null_);

	/* partition bound node tree */
	pg_node_tree relpartbound BKI_DEFAULT(_null_);
#endif
} FormData_pg_class;

The process of discovering a table:

Scanning the pg_class catalog table.
Scanning the pg_attribute table to get column definitions.

[[TODO]]

Partition Tables

A partitioned table is catalog metadata and has no heap of its own. Rows and ordinary indexes live in leaf partitions.

CREATE TABLE events (
  id bigint,
  happened_at date NOT NULL,
  payload text
) PARTITION BY RANGE (happened_at);

CREATE TABLE events_2024 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE INDEX events_happened_at_idx ON events (happened_at);

partitioned parent (`events`, no relation storage)
  -> partitioned index (`events_happened_at_idx`, no relation storage)
  -> leaf `events_2024` (heap + leaf index)
  -> leaf `events_2025` (heap + leaf index)

Object and Storage

A PostgreSQL object is a logical database entity represented by one or more system-catalog rows.

An ObjectAddress identifies an object by (classId, objectId, objectSubId).

/* src/include/catalog/objectaddress.h */
typedef struct ObjectAddress
{
    Oid   classId;      /* Class Id from pg_class */
    Oid   objectId;     /* OID of the object */
    int32 objectSubId;  /* Subitem within object (eg column), or 0 */
} ObjectAddress;

classId is the pg_class OID of the catalog that defines the object. Common defining catalogs are:

pg_class: relations such as tables, indexes, views, and sequences
pg_type: data types
pg_proc: functions and procedures
pg_namespace: schemas

objectId identifies the object within that catalog. objectSubId is zero for a complete object and identifies a subobject, such as a table column, when nonzero.

(pg_class's OID,     table OID,    0) -> table
(pg_proc's OID,      function OID, 0) -> function
(pg_namespace's OID, schema OID,   0) -> schema
(pg_class's OID,     table OID,    2) -> second column of the table

Relation storage is the set of physical relation files managed by PostgreSQL’s storage manager (smgr). Only some relation kinds in pg_class own relation storage.

Relation object	`pg_class` row and OID	Own relation storage
Ordinary table	yes	yes
Leaf partition	yes	yes
Partitioned table	yes	no
Ordinary index	yes	yes
Partitioned index	yes	no
View	yes	no

pg_class.relfilenode identifies the relation's current physical relation files.

/* src/include/catalog/pg_class.h */
/* identifier of physical storage file */
/* relfilenode == 0 means it is a "mapped" relation, see relmapper.c */
Oid relfilenode BKI_DEFAULT(0);

A relfilenumber (RelFileNumber, historically called relfilenode) is the file-number component of a physical relation locator. It is not the relation’s logical OID.

A physical relation locator contains a tablespace OID, database OID, and relfilenumber.

relation OID
  -> pg_class row / relcache entry
  -> (tablespace OID, database OID, relfilenumber)
  -> physical relation files

A new ordinary relation commonly starts with a relfilenumber equal to its OID. The values can diverge after a relation rewrite. The OID remains the logical identity while PostgreSQL replaces the relation storage. A relfilenode value of zero identifies a mapped relation; PostgreSQL gets its relfilenumber from the relation mapper instead of pg_class.

A relation can use several physical files:

12345       main fork
12345_fsm   free-space map
12345_vm    visibility map
12345_init  initialization fork for an unlogged relation
12345.1     a later segment after the main fork grows large

A table’s TOAST table and indexes are separate relation objects. Each has its own OID and, when applicable, its own relfilenumber and files.

heap_create() disables create_storage for relation kinds without relation storage.

/* src/backend/catalog/heap.c:heap_create */
if (!RELKIND_HAS_STORAGE(relkind))
    create_storage = false;

A partitioned table has no relation storage. Its OID identifies the hierarchy root and its partitioning metadata. Heap and index files belong to leaf partitions. DML and scans must therefore select a leaf.

DDL

CREATE TABLE grammar (`gram.y`)
  -> ProcessUtilitySlow (`utility.c`)
  -> transformCreateStmt (`parse_utilcmd.c`)
  -> DefineRelation (`tablecmds.c`)
  -> heap_create_with_catalog (`heap.c`)
  -> partition-specific catalog updates

CREATE TABLE accepts optional PARTITION BY and PARTITION OF clauses. In CreateStmt:

PARTITION BY sets partspec.
PARTITION OF adds the parent to inhRelations and sets partbound.
A sub-partitioned partition sets both partbound and partspec.

ProcessUtilitySlow() transforms and executes CREATE TABLE and its generated subcommands.

/* src/backend/tcop/utility.c:ProcessUtilitySlow */
stmts = transformCreateStmt((CreateStmt *) parsetree, queryString);

while (stmts != NIL)
{
    Node *stmt = (Node *) linitial(stmts);
    /* ... */
    if (IsA(stmt, CreateStmt))
    {
        CreateStmt *cstmt = (CreateStmt *) stmt;

        address = DefineRelation(cstmt,
                                 RELKIND_RELATION,
                                 InvalidOid, NULL,
                                 queryString);
        /* ... */
        NewRelationCreateToastTable(address.objectId, toast_options);
    }
    else
        ProcessUtility(/* generated subsidiary command */);
}

ProcessUtilitySlow() passes RELKIND_RELATION. DefineRelation() changes it after detecting partspec. PostgreSQL transforms partition key and bound expressions after it creates and opens the relation.

Creating the partitioned parent

DefineRelation
  -> relkind = RELKIND_PARTITIONED_TABLE
  -> heap_create_with_catalog
       -> common pg_class / pg_attribute / pg_type / dependency rows
       -> no physical relation file
  -> ComputePartitionAttrs
  -> StorePartitionKey
       -> pg_partitioned_table
       -> dependencies for key columns, opclasses, collations, expressions

DefineRelation() marks a statement with partspec as RELKIND_PARTITIONED_TABLE.

/* src/backend/commands/tablecmds.c:DefineRelation */
if (stmt->partspec != NULL)
{
    if (relkind != RELKIND_RELATION)
        elog(ERROR, "unexpected relkind: %d", (int) relkind);

    relkind = RELKIND_PARTITIONED_TABLE;
    partitioned = true;
}
else
    partitioned = false;

heap_create_with_catalog() then runs the common relation-creation path. It creates pg_class, pg_attribute, and pg_type rows, plus any dependency, default, and constraint rows. The partition path differs in storage and partition-specific catalogs.

A partitioned parent has relkind = 'p'. This kind is absent from RELKIND_HAS_STORAGE and RELKIND_HAS_TABLE_AM.

/* src/include/catalog/pg_class.h */
#define RELKIND_PARTITIONED_TABLE 'p'

#define RELKIND_HAS_STORAGE(relkind) \
    ((relkind) == RELKIND_RELATION || \
     (relkind) == RELKIND_INDEX || \
     /* ... */)

#define RELKIND_HAS_PARTITIONS(relkind) \
    ((relkind) == RELKIND_PARTITIONED_TABLE || \
     (relkind) == RELKIND_PARTITIONED_INDEX)

#define RELKIND_HAS_TABLE_AM(relkind) \
    ((relkind) == RELKIND_RELATION || \
     /* ... */)

heap_create() suppresses file creation for the partitioned parent.

/* src/backend/catalog/heap.c:heap_create */
if (!RELKIND_HAS_STORAGE(relkind))
    create_storage = false;

needs_toast_table() rejects partitioned tables.

/* src/backend/catalog/toasting.c:needs_toast_table */
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
    return false;

The parent has no heap file, table-AM scan target, or TOAST table. Its tablespace and access-method settings provide defaults for partitions.

pg_partitioned_table stores one row for each partitioned table. It defines the table’s partition key. It does not store child bounds; each child bound is stored in pg_class.relpartbound.

FormData_pg_partitioned_table defines the pg_partitioned_table catalog schema.

/* src/include/catalog/pg_partitioned_table.h:FormData_pg_partitioned_table */
CATALOG(pg_partitioned_table,3350,PartitionedRelationId)
{
    Oid         partrelid BKI_LOOKUP(pg_class);
    char        partstrat;
    int16       partnatts;
    Oid         partdefid BKI_LOOKUP_OPT(pg_class);
    int2vector  partattrs BKI_FORCE_NOT_NULL;

#ifdef CATALOG_VARLEN
    oidvector   partclass BKI_LOOKUP(pg_opclass) BKI_FORCE_NOT_NULL;
    oidvector   partcollation BKI_LOOKUP_OPT(pg_collation) BKI_FORCE_NOT_NULL;
    pg_node_tree partexprs;
#endif
} FormData_pg_partitioned_table;

The columns have these roles:

partrelid: OID of the partitioned relation; also the catalog’s primary key
partstrat: strategy (h for hash, l for list, or r for range)
partnatts: number of partition key fields
partdefid: OID of the default partition, or zero
partattrs: parent attribute number for each key field; zero denotes an expression
partclass: operator class for each key field
partcollation: collation for each key field
partexprs: serialized expression trees for zero entries in partattrs

DefineRelation() transforms and stores the partition key.

/* src/backend/commands/tablecmds.c:DefineRelation */
stmt->partspec = transformPartitionSpec(rel, stmt->partspec);

ComputePartitionAttrs(pstate, rel, stmt->partspec->partParams,
                      partattrs, &partexprs, partopclass,
                      partcollation, stmt->partspec->strategy);

StorePartitionKey(rel, stmt->partspec->strategy, partnatts, partattrs,
                  partexprs, partopclass, partcollation);

StorePartitionKey() inserts the partition key into pg_partitioned_table.

/* src/backend/catalog/heap.c:StorePartitionKey */
values[Anum_pg_partitioned_table_partrelid - 1] =
    ObjectIdGetDatum(RelationGetRelid(rel));
values[Anum_pg_partitioned_table_partstrat - 1] = CharGetDatum(strategy);
values[Anum_pg_partitioned_table_partnatts - 1] = Int16GetDatum(partnatts);
values[Anum_pg_partitioned_table_partdefid - 1] =
    ObjectIdGetDatum(InvalidOid);
values[Anum_pg_partitioned_table_partattrs - 1] = PointerGetDatum(partattrs_vec);
values[Anum_pg_partitioned_table_partclass - 1] = PointerGetDatum(partopclass_vec);
values[Anum_pg_partitioned_table_partcollation - 1] =
    PointerGetDatum(partcollation_vec);
values[Anum_pg_partitioned_table_partexprs - 1] = partexprDatum;

CatalogTupleInsert(pg_partitioned_table, tuple);

Creating a leaf with `PARTITION OF`

DefineRelation
  -> MergeAttributes from parent
  -> heap_create_with_catalog
       -> ordinary relation catalogs
       -> leaf relation storage
  -> transformPartitionBound
  -> check_new_partition_bound
  -> StorePartitionBound
       -> pg_class.relpartbound
       -> pg_class.relispartition = true
  -> StoreCatalogInheritance
       -> pg_inherits
       -> child-to-parent dependency

A leaf uses RELKIND_RELATION unless it is also partitioned. In this example, events_2024 owns heap relation storage and has pg_class.relispartition = true.

DefineRelation() validates and stores the leaf's partition bound.

/* src/backend/commands/tablecmds.c:DefineRelation */
bound = transformPartitionBound(pstate, parent, stmt->partbound);
check_new_partition_bound(relname, parent, bound, pstate);

/* Update the pg_class entry. */
StorePartitionBound(rel, parent, bound);

/* Store inheritance information for new rel. */
StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL);

StorePartitionBound() stores the serialized bound in pg_class.relpartbound and marks the relation as a partition.

/* src/backend/catalog/heap.c:StorePartitionBound */
new_val[Anum_pg_class_relpartbound - 1] =
    CStringGetTextDatum(nodeToString(bound));
new_repl[Anum_pg_class_relpartbound - 1] = true;
newtuple = heap_modify_tuple(tuple, RelationGetDescr(classRel),
                             new_val, new_null, new_repl);
((Form_pg_class) GETSTRUCT(newtuple))->relispartition = true;

CatalogTupleUpdate(classRel, &newtuple->t_self, newtuple);

StoreSingleInheritance() stores the parent-child edge in pg_inherits.

/* src/backend/catalog/pg_inherits.c:StoreSingleInheritance */
values[Anum_pg_inherits_inhrelid - 1] = ObjectIdGetDatum(relationId);
values[Anum_pg_inherits_inhparent - 1] = ObjectIdGetDatum(parentOid);
values[Anum_pg_inherits_inhseqno - 1] = Int32GetDatum(seqNumber);
values[Anum_pg_inherits_inhdetachpending - 1] = BoolGetDatum(false);

CatalogTupleInsert(inhRelation, tuple);

A partition uses an AUTO dependency; ordinary inheritance uses a NORMAL dependency.

/* src/backend/commands/tablecmds.c */
#define child_dependency_type(child_is_partition) \
    ((child_is_partition) ? DEPENDENCY_AUTO : DEPENDENCY_NORMAL)

Ordinary INHERITS also uses pg_inherits, but it creates a NORMAL dependency and has no partition metadata. For a default partition, StorePartitionBound() also stores the leaf OID in pg_partitioned_table.partdefid.

The catalogs distinguish each relation type as follows:

Object	`pg_class`	`pg_partitioned_table`	`pg_inherits`	Relation storage
Normal heap	`relkind = 'r'`, `relispartition = false`	none	none normally	yes
Partitioned parent	`relkind = 'p'`	partition strategy and key	row only if it is itself a child	no
Leaf partition	usually `relkind = 'r'`, `relispartition = true`, bound in `relpartbound`	none	child-to-parent row	yes
Sub-partitioned child	`relkind = 'p'`, `relispartition = true`, bound	its own key	child-to-parent row	no

A parent index follows the same model. It has relkind = 'I' and no relation storage. Its leaf indexes are ordinary index relations.

Leaf column definitions

PARTITION OF
  -> MergeAttributes(parent)
  -> BuildDescForRelation
  -> heap_create_with_catalog
       -> independent pg_class row
       -> independent pg_attribute rows
       -> independent pg_type row

ATTACH PARTITION
  -> MergeAttributesIntoExisting
       -> match columns by name
       -> validate type, collation, and constraints

The parent and each leaf have separate column definitions. The parent defines the logical row type. Each leaf has its own TupleDesc and pg_attribute rows.

DefineRelation() builds a new partition's descriptor from its parent before creating the leaf relation.

/* src/backend/commands/tablecmds.c:DefineRelation */
stmt->tableElts =
    MergeAttributes(stmt->tableElts, inheritOids,
                    stmt->relation->relpersistence,
                    stmt->partbound != NULL,
                    &old_constraints);

descriptor = BuildDescForRelation(stmt->tableElts);

relationId = heap_create_with_catalog(/* ... */, descriptor, /* ... */);

An attached table must expose the same logical columns, but its physical attribute numbers can differ.

MergeAttributesIntoExisting() matches an attached table's columns by name.

/* src/backend/commands/tablecmds.c:MergeAttributesIntoExisting */
for (AttrNumber parent_attno = 1;
     parent_attno <= parent_desc->natts;
     parent_attno++)
{
    Form_pg_attribute parent_att =
        TupleDescAttr(parent_desc, parent_attno - 1);
    char *parent_attname = NameStr(parent_att->attname);

    tuple = SearchSysCacheCopyAttName(RelationGetRelid(child_rel),
                                      parent_attname);

    /* Validate type, typmod, collation, NOT NULL, and generation state. */
}

Tuple-conversion maps translate between parent and leaf attribute numbers. Structural changes on the parent recurse through the hierarchy and update each leaf’s catalog rows. Leaves can still have distinct defaults, indexes, additional constraints, statistics, and storage settings.

DML

`INSERT`: route one row to a leaf

ExecModifyTable
  -> ExecInsert
       -> ExecPrepareTupleRouting
            -> ExecFindPartition
       -> table_tuple_insert(leaf relation)
       -> ExecInsertIndexTuples(leaf indexes)

ExecModifyTable() reads one tuple from its subplan and calls ExecInsert(). A partitioned target has a PartitionTupleRouting structure.

ExecInsert() replaces the root ResultRelInfo with the selected leaf before writing the row.

/* src/backend/executor/nodeModifyTable.c:ExecInsert */
if (proute)
{
    ResultRelInfo *partRelInfo;

    slot = ExecPrepareTupleRouting(mtstate, estate, proute,
                                   resultRelInfo, slot,
                                   &partRelInfo);
    resultRelInfo = partRelInfo;
}

resultRelationDesc = resultRelInfo->ri_RelationDesc;

ExecFindPartition() evaluates the partition key and finds a matching child at each partitioning level.

/* src/backend/executor/execPartition.c:ExecFindPartition */
while (dispatch != NULL)
{
    /* ... */
    rel = dispatch->reldesc;
    partdesc = dispatch->partdesc;

    ecxt->ecxt_scantuple = slot;
    FormPartitionKeyDatum(dispatch, slot, estate, values, isnull);

    if (partdesc->nparts == 0 ||
        (partidx = get_partition_for_tuple(dispatch, values, isnull)) < 0)
        ereport(ERROR,
                (errmsg("no partition of relation \"%s\" found for row",
                        RelationGetRelationName(rel))));

    is_leaf = partdesc->is_leaf[partidx];
    /* Return the leaf, or descend into a sub-partitioned child. */
}

ExecPrepareTupleRouting() converts the root tuple to the selected leaf's physical attribute layout when needed.

/* src/backend/executor/nodeModifyTable.c:ExecPrepareTupleRouting */
partrel = ExecFindPartition(mtstate, targetRelInfo, proute, slot, estate);

map = ExecGetRootToChildMap(partrel, estate);
if (map != NULL)
{
    TupleTableSlot *new_slot = partrel->ri_PartitionTupleSlot;

    slot = execute_attr_map_slot(map->attrMap, slot, new_slot);
}

PostgreSQL initializes each leaf’s ResultRelInfo on first use. A single-row insert does not open every partition. After routing, table_tuple_insert() and ExecInsertIndexTuples() enter the normal leaf heap and index paths.

An insert through the partitioned parent has additional cost:

Evaluate and route each row.
Search again at each sub-partitioning level.
Convert the tuple when the leaf layout differs.

When traffic is distributed across leaves, partitioning may:

Update smaller leaf indexes.
Improve cache and data locality.
Separate hot heap and index pages across leaves.

Multiple rows and batch insertion

multi-row SQL INSERT
  -> ExecModifyTable
       -> route and insert one row at a time

COPY FROM
  -> ExecFindPartition for each row
  -> buffer rows by leaf
  -> table_multi_insert for each leaf buffer

ExecModifyTable() processes a multi-row SQL insert one row at a time.

/* src/backend/executor/nodeModifyTable.c:ExecModifyTable */
for (;;)
{
    /* fetch the next row from the subplan */
    context.planSlot = ExecProcNode(subplanstate);
    if (TupIsNull(context.planSlot))
        break;

    /* ... */
    slot = ExecInsert(&context, resultRelInfo, slot,
                      node->canSetTag, NULL, NULL);
}

Each row may route to a different leaf and reaches table_tuple_insert() separately. PostgreSQL reuses routing state and initialized leaf state. ExecBatchInsert() handles FDWs that implement ExecForeignBatchInsert; it does not batch local heap inserts.

COPY FROM routes each row and maintains a buffer for each destination leaf.

/* src/backend/commands/copyfrom.c:CopyFrom */
resultRelInfo = ExecFindPartition(mtstate, target_resultRelInfo,
                                  proute, myslot, estate);

/* ... determine whether this leaf supports multi-insert ... */
if (leafpart_use_multi_insert)
{
    if (resultRelInfo->ri_CopyMultiInsertBuffer == NULL)
        CopyMultiInsertInfoSetupBuffer(&multiInsertInfo, resultRelInfo);
}

CopyMultiInsertBufferFlush() writes one leaf buffer with a single table-AM call.

/* src/backend/commands/copyfrom.c:CopyMultiInsertBufferFlush */
table_multi_insert(resultRelInfo->ri_RelationDesc,
                   slots,
                   nused,
                   mycid,
                   ti_options,
                   buffer->bistate);

Triggers, volatile expressions, and an FDW without batch support can force COPY to use single-row insertion:

multi-row SQL INSERT: route row -> insert row -> repeat
COPY FROM:            route rows -> group by leaf -> table_multi_insert per leaf

`DELETE`

partition expansion and pruning
  -> leaf scan
       -> leaf ResultRelInfo + ctid
  -> ExecDelete
       -> ExecDeleteAct
       -> table_tuple_delete(leaf relation)

DELETE does not route rows by value. The planner expands and prunes the hierarchy. Each scan row carries the source leaf OID in a junk tableoid column and the row identity in ctid.

ExecModifyTable() uses tableoid to select the leaf's ResultRelInfo.

/* src/backend/executor/nodeModifyTable.c:ExecModifyTable */
if (AttributeNumberIsValid(node->mt_resultOidAttno))
{
    datum = ExecGetJunkAttribute(context.planSlot,
                                 node->mt_resultOidAttno,
                                 &isNull);
    resultoid = DatumGetObjectId(datum);

    if (resultoid != node->mt_lastResultOid)
        resultRelInfo = ExecLookupResultRelByOid(node, resultoid,
                                                 false, true);
}

ExecDelete() then calls table_tuple_delete() on that leaf. Tuple deletion and later index cleanup follow the normal heap path.

`UPDATE`

leaf scan
  -> ExecUpdate
       -> ExecUpdateAct
            -> partition constraint passes
                 -> table_tuple_update(current leaf)
            -> partition constraint fails
                 -> ExecCrossPartitionUpdate
                      -> ExecDelete(old leaf)
                      -> ExecInsert(root)
                           -> route to new leaf

An update starts on the leaf selected by the scan:

If the new row satisfies the leaf’s partition constraint, table_tuple_update() updates the leaf and its indexes.
If the new row violates the constraint, PostgreSQL deletes it from the old leaf and routes an insert to the new leaf.

ExecUpdateAct() chooses an in-leaf update or a cross-partition update.

/* src/backend/executor/nodeModifyTable.c:ExecUpdateAct */
partition_constraint_failed =
    resultRelationDesc->rd_rel->relispartition &&
    !ExecPartitionCheck(resultRelInfo, slot, estate, false);

if (partition_constraint_failed)
{
    /* DELETE from source, then route INSERT from the root. */
    if (ExecCrossPartitionUpdate(context, resultRelInfo,
                                 tupleid, oldtuple, slot,
                                 canSetTag, updateCxt,
                                 &result, &retry_slot,
                                 &inserted_tuple, &insert_destrel))
    {
        updateCxt->crossPartUpdate = true;
        return TM_Ok;
    }
    /* concurrent-update retry omitted */
}

/* If the row remains in this leaf, perform an ordinary update. */
result = table_tuple_update(resultRelationDesc, tupleid, slot,
                            estate->es_output_cid,
                            estate->es_snapshot,
                            estate->es_crosscheck_snapshot,
                            true, &context->tmfd,
                            &updateCxt->lockmode,
                            &updateCxt->updateIndexes);

ExecCrossPartitionUpdate() deletes from the old leaf and inserts through the root.

/* src/backend/executor/nodeModifyTable.c:ExecCrossPartitionUpdate */
ExecDelete(context, resultRelInfo,
           tupleid, oldtuple,
           false,  /* processReturning */
           true,   /* changingPart */
           false,  /* canSetTag */
           tmresult, &tuple_deleted, &epqslot);

/* Convert the old leaf layout back to the root layout if necessary. */
if (tupconv_map != NULL)
    slot = execute_attr_map_slot(tupconv_map->attrMap,
                                 slot, mtstate->mt_root_tuple_slot);

/* ExecInsert starts routing at the root and finds the new leaf. */
context->cpUpdateReturningSlot =
    ExecInsert(context, mtstate->rootResultRelInfo, slot, canSetTag,
               inserted_tuple, insert_destrel);

The delete and insert form one transactional SQL update, but modify two leaf heaps and their indexes. An update that directly targets a leaf cannot move the row outside that leaf; PostgreSQL reports a partition-constraint violation.

DQL

The shared planning path: expand, prune, append

expand_partitioned_rtentry
  -> PartitionDirectoryLookup
  -> prune_append_rel_partitions
       -> gen_partprune_steps
       -> get_matching_partitions
  -> build child RelOptInfo objects
  -> set_append_rel_pathlist
       -> choose an access path for each leaf
       -> build Append paths
  -> make_partition_pruneinfo for runtime pruning
  -> ExecInitAppend
       -> ExecInitPartitionPruning
  -> ExecAppend

expand_partitioned_rtentry() prunes the hierarchy and creates planner relations for surviving children.

/* src/backend/optimizer/util/inherit.c:expand_partitioned_rtentry */
partdesc = PartitionDirectoryLookup(root->glob->partition_directory,
                                    parentrel);

relinfo->live_parts = live_parts =
    prune_append_rel_partitions(relinfo);

/* ... */
while ((i = bms_next_member(live_parts, i)) >= 0)
{
    Oid childOID = partdesc->oids[i];
    Relation childrel = try_table_open(childOID, lockmode);

    expand_single_inheritance_child(root, parentrte, parentRTindex,
                                    parentrel, top_parentrc, childrel,
                                    &childrte, &childRTindex);
    childrelinfo = build_simple_rel(root, childRTindex, relinfo);
    /* Recurse if this child is also partitioned. */
}

set_append_rel_pathlist() builds access paths for each surviving child and combines them into append paths.

/* src/backend/optimizer/path/allpaths.c:set_append_rel_pathlist */
foreach(l, root->append_rel_list)
{
    /* ... locate childRTE and childrel ... */
    set_rel_pathlist(root, childrel, childRTindex, childRTE);

    if (!IS_DUMMY_REL(childrel))
        live_childrels = lappend(live_childrels, childrel);
}

add_paths_to_append_rel(root, rel, live_childrels);

prune_append_rel_partitions() performs plan-time pruning.

/* src/backend/partitioning/partprune.c:prune_append_rel_partitions */
if (!enable_partition_pruning || clauses == NIL)
    return bms_add_range(NULL, 0, rel->nparts - 1);

gen_partprune_steps(rel, clauses, PARTTARGET_PLANNER, &gcontext);
if (gcontext.contradictory)
    return NULL;

return get_matching_partitions(&context, gcontext.steps);

Append concatenates child results without sorting or duplicate removal. It is similar to UNION ALL, but also represents partition and inheritance scans. MergeAppend merges ordered child results. make_partition_pruneinfo() records pruning steps for values available only during executor startup or rescan.

ExecInitAppend() initializes only the matching subplans.

/* src/backend/executor/nodeAppend.c:ExecInitAppend */
if (node->part_prune_info != NULL)
{
    prunestate = ExecInitPartitionPruning(&appendstate->ps,
                                          list_length(node->appendplans),
                                          node->part_prune_info,
                                          &validsubplans);
    appendstate->as_prune_state = prunestate;
    nplans = bms_num_members(validsubplans);
}

ExecAppend() reads tuples from each surviving leaf subplan.

/* src/backend/executor/nodeAppend.c:ExecAppend */
subnode = node->appendplans[node->as_whichplan];
result = ExecProcNode(subnode);

if (!TupIsNull(result))
    return result;

/* Current leaf is exhausted; choose the next surviving subplan. */
if (!node->choose_next_subplan(node) && node->as_nasyncremain == 0)
    return ExecClearTuple(node->ps.ps_ResultTupleSlot);

For a parameterized plan, ExecFindMatchingSubPlans() runs again when a PARAM_EXEC value changes. The same expansion, pruning, and append path supports table and index scans. PostgreSQL chooses a scan method for each leaf, so one Append can contain both sequential and index scans.

Table scan

surviving leaf RelOptInfo
  -> set_rel_pathlist
  -> create_seqscan_plan
       -> scan_relid = leaf RT index
  -> SeqNext
       -> normal table-AM scan

For example:

SELECT * FROM events
WHERE happened_at >= DATE '2025-02-01';

pruning may remove events_2024. A typical shape is:

Append
  -> Seq Scan on events_2025

create_seqscan_plan() creates a scan for the selected leaf relation.

/* src/backend/optimizer/plan/createplan.c:create_seqscan_plan */
Index scan_relid = best_path->parent->relid;

scan_plan = make_seqscan(tlist,
                         scan_clauses,
                         scan_relid);

The partition layer ends after selecting the leaf RT index. SeqNext() then runs the normal table-AM scan.

Index scan

surviving leaf RelOptInfo
  -> create_index_paths for leaf indexes
  -> create_indexscan_plan
       -> baserelid = leaf RT index
       -> indexoid = leaf index OID
  -> IndexNext
       -> normal index scan

An index declared on the parent is a partitioned index. It defines an index hierarchy but is not scanned. The planner considers each leaf index and may produce:

Append
  -> Index Scan using events_2024_happened_at_idx on events_2024
  -> Index Scan using events_2025_happened_at_idx on events_2025

create_indexscan_plan() records the selected leaf table and leaf index in the scan plan.

/* src/backend/optimizer/plan/createplan.c:create_indexscan_plan */
Index baserelid = best_path->path.parent->relid;
IndexOptInfo *indexinfo = best_path->indexinfo;
Oid indexoid = indexinfo->indexoid;

/* ... */
scan_plan = (Scan *) make_indexscan(/* ... */,
                                    baserelid,
                                    indexoid,
                                    /* ... */);

Partitioning determines which leaf scan plans exist and execute. IndexNext() and the index AM then run an ordinary scan on the selected leaf index.

Dive into Postgres