Soft Deletes & Multitenancy — Series 2

Preview — 3 of 10 questions

What is the materialized path pattern for hierarchical data?

javascript

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  path TEXT NOT NULL  -- e.g. '1/4/9/'
);

AEach row stores a text (or array) representation of the full chain of ancestor IDs from the root down to itself, allowing "all descendants of node X" to be found with a single LIKE 'X/%'-style prefix match, without any recursion

BIt's another name for the adjacency list model — parent_id and path are synonyms

CThe path column stores a serialized JSON tree of the entire hierarchy in every row

DIt requires PostgreSQL's ltree extension to work at all — plain TEXT cannot be used

What does the ltree extension add over a plain TEXT materialized path?

javascript

CREATE EXTENSION ltree;

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  path LTREE NOT NULL
);

CREATE INDEX idx_categories_path ON categories USING GIST (path);

-- All descendants of 'electronics.laptops'
SELECT * FROM categories WHERE path <@ 'electronics.laptops';

-- All ancestors of 'electronics.laptops.gaming'
SELECT * FROM categories WHERE path @> 'electronics.laptops.gaming';

Altree is a completely different, unrelated indexing structure for full-text search

Bltree provides a dedicated label-path data type with specialized operators (<@ for ancestor check, @> for descendant check, ~ for lquery pattern matching) and can be indexed with a GiST index specifically built for hierarchical path queries — faster and more expressive than manual TEXT prefix matching

Cltree replaces the need for a categories table entirely

Dltree only works with numeric path segments, not named labels

A logs table stores 500M+ rows and grows by 10M/day. Why might you design it as a RANGE-partitioned table by month from the start, rather than adding partitioning later?

javascript

CREATE TABLE logs (
  id BIGINT,
  event TEXT,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2026_01 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- Retention: drop an entire month instantly, no row-by-row DELETE
DROP TABLE logs_2025_01;

APartitioning always makes individual row lookups by primary key faster than an unpartitioned table

BPostgreSQL requires all large tables to be partitioned once they exceed 1 million rows

CRetroactively partitioning a huge existing table requires a full data migration (copying all rows into the new partitioned structure), which is disruptive at scale — designing with partitioning from the start avoids that one-time migration cost, and lets you drop old partitions instantly (DROP TABLE) instead of slow bulk DELETEs for data retention

DPartitioning eliminates the need for indexes on the table entirely

Answer all 10 questions (7 more), see explanations for every answer, and track your score.