Postgres in depth · Chapter 46 of 65

Memory

By the end of this chapter you can say where Postgres keeps the pages it reads, look inside its cache with pg_buffercache, read a hit ratio, and give a slow sort the memory it needs. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Inside Postgres
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

LCO records every play, pause and seek of every lesson. Arjun's morning report on those events is slow the first time and fast after; Kabir wants to know whether a bigger server would help, and how much memory to buy. To answer, you need to know what Postgres keeps in memory, what it reads from the operating system, and what it writes to temporary files when a query runs out of room. From chapter 42 you know a table is 8 KB pages on disk. This chapter follows those pages into memory.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    buffer-math.sql: how big is the cache?

    Priya wants the size of shared buffers as three numbers, asked from the server: how many buffers there are, how many bytes one buffer (one page) holds, and the total size in megabytes (whole megabytes).

    Return three columns: buffers, block_bytes and total_mb.

    Careful: SHOW prints shared_buffers as text with a unit; pg_settings has the number and the unit it counts in.

  2. Task 2

    cached.sql: what is in the cache?

    The Check builds lesson_events exactly as the lesson does and reads it once. For lesson_events and its index lesson_events_pkey, return how many of their pages (the main fork only) are in shared buffers right now, and that count as a percentage of the relation's size in pages, rounded to 1 decimal.

    Return three columns: relname, buffers and pct_cached, one row per relation, ordered by relname.

    Careful: shared buffers hold every database's pages, and copies of a database share file node numbers, so keep only this database's buffers.

  3. Task 3

    sort-memory.sql: a sort that stays in memory

    Arjun's export reads every column of lesson_events sorted by watched_seconds, largest first, then by id. With the default work_mem the sort spills to disk.

    Set work_mem for this session only, to a size that keeps this sort in memory (64MB at most), and end the file with EXPLAIN ANALYZE of that query. Afterwards the plan must say the sort ran as a quicksort.

    Careful: work_mem is per sort and per connection; never change it for the whole server to fix one query.

  4. Task 4 · interview question

    Interview: hit-ratio.sql, the cache hit ratio

    "How often does this database find the page it needs in shared buffers?" Return one row for the current database: the block requests found in shared buffers, the ones read from outside them, and the hit ratio as a percentage rounded to 2 decimals.

    Return four columns: datname, blks_hit, blks_read and hit_pct.

    Careful: a brand-new database may have no counts yet, so do not divide by zero; and a read may still come from the operating system's cache.

What you will learn

  • Shared buffers: Postgres's own page cache, shared by every connection, and the operating system's cache beneath it.
  • pg_buffercache: which pages are in memory right now, dirty or clean.
  • EXPLAIN (BUFFERS) and hit ratios: hits, reads, and what a "read" really is.
  • work_mem: the memory one sort or hash may use before it spills to disk.

More in Inside Postgres

Build "Memory" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.