Keyboard shortcuts

/ or ⌘/Ctrl K
Find a note
j / k
Next / previous section or linked note
h / l
Collapse or go to parent / expand or enter
e or Alt-click
Read a linked note here
o
Open focused note on its own
g g / G
First / last section or linked note
g h / g a
Home / all notes
g b / g t
Backlinks / table of contents
t
Cycle System, Light, Dark
? / Esc
Show / close this reference

Search: ↑/↓ or Ctrl N/P, Enter to open. Shortcuts pause while typing.

postgres [1076478c]

Tags: Databases, Computers

1. Resources

2. Concepts

2.1. relfilenode

  • An OID represents the logical identity, relfilenode is the physical data. OIDs are static, but relfilenodes can change
  • On initialization, the table relfilenode is the same as the OID, but operates that rewrite the file (TRUNCATE/VACUUM FULL, CLUSTER, REINDEX) all create new relfilenodes
  • Physical location via SELECT pg_relation_filepath('tablename')
  • File segmentation -> large than 1GB, we get relfilenode segments like reifilenode.1, refilenode.2, etc
  • Free space map is stored in relfilennode_fsm and visbility map refilenode_vm
  • Entire table file is called a "heap file" (the relfilenode)
  • Inside this there are pages (blocks), which are 8KB
  • Inside the page, it contains "heap tuples", which are actual rows, and line pointers to these rows.

  • Indices have their own OID and relfilenodes

    Vaguely like

      FILESYSTEM (Directory: $PGDATA/base/[database_OID]/)
  │
  ├── File 1: THE TABLE (The "Heap") [relfilenode: 18740]
  │   │
  │   ├── Page 0 (8KB) [3]
  │   │   ├── Header [12]
  │   │   ├── Line Pointers (1, 2, 3...) [4]
  │   │   ├── [Free Space / Hole] [10]
  │   │   └── Heap Tuples (The actual Data: "John", "Doe") [4]
  │   │
  │   ├── Page 1 (8KB)
  │   │   └── ... (More pointers and tuples)
  │   │
  │   └── ... (More pages)
  │
  │
  └── File 2: THE INDEX (e.g., B-Tree) [relfilenode: 18750]
      │   (This is a separate file!) [8]
      │
      ├── Page 0 (Meta page/Root)
      │
      ├── Page 1 (Leaf Page)
      │   ├── Header
      │   └── Index Tuples [11]
      │       ├── Key: "Doe"
      │       └── TID: (Block 0, Offset 1)  ----->  Pulls row from Table File
      │
      └── ...

2.2. Slotted Pages

  • Heap is divided into fixed length pages, which have a:

    • header (24 bytes)
    • line pointer - array of pointers that track index into actual data
    • heap tuples
  • Internal fragementation can happen in the heap, external fragementation

2.3. Indices

  • Each tuple has an ID (TID)
  • Block number identifies which 8kb page in teh table file contains it, and offset identifies the line pointer

  • When you do an index scan, pg searches the b-tree index for the key, retreives the tid, and loads the page

            INDEX FILE (B-Tree)                     HEAP TABLE FILE (relfilenode)
     ┌───────────────────────┐             ┌────────────────────────────────────┐
     │ Key: "50"             │             │ Page 7 (8KB Block)                 │
     │ TID: (Block 7, Off 2) │ ──────────> │ ┌────────────────────────────────┐ │
     └───────────────────────┘             │ │ [ Page Header ]                │ │
                                           │ ├────────────────────────────────┤ │
                                           │ │ 1. [ Line Pointer 1 ]          │ │
                                           │ │ 2. [ Line Pointer 2 ] ───────┐ │ │
                                           │ │ 3. [ Line Pointer 3 ]        │ │ │
                                           │ ├────────────────────────────────┤ │
                                           │ │           (Hole)               │ │
                                           │ │      Free Space for new        │ │
                                           │ │      pointers/tuples           │ │
                                           │ ├────────────────────────────────┤ │
                                           │ │ [ Heap Tuple 3 Data ... ]      │ │
                                           │ │ [ Heap Tuple 2 Data ... ] <──┘ │ │
                                           │ │ [ Heap Tuple 1 Data ... ]      │ │
                                           │ └────────────────────────────────┘ │
                                           └────────────────────────────────────┘

3. Postgres Issues

3.1. Performance

3.1.1. TOAST Tables

  • hakibenita.com/sql-medium-text-performance TOAST tables slice up medium/large size text data using a separate TOAST partition
  • Indexes are fast in this, but you need to have large size text data
  • Medium sized text data will usually kill your performance

3.2. Scalablity

4. Queues

5. JSON Types

www.postgresql.org/docs/9.4/datatype-json.html

  • Can build an index with GIN
  • can use allballs to mean 0

6. Postgres GIST indicies

7. Multiranges

8. WAL