Practice: Storage for ML: lakehouse, warehouse and operational data stores

Generated by Claude through GigAI 0.1.11.10 on 2026-10-11.

Main tool: PostgreSQL · PostgreSQL runs free on a laptop and lets you practise the lesson's main storage ideas in one engine: transactional metadata, partition layout and pruning, indexed key lookups, and partition-based lifecycle. · Alternatives: Redis or Amazon DynamoDB give lower-latency key-value lookups but no joins or SQL; BigQuery or Snowflake are managed columnar warehouses billed by data scanned or compute time; Delta Lake or Apache Iceberg tables over Apache Parquet files in Amazon S3 hold bulk training data cheaply, with AWS Glue Data Catalog as their metadata layer, but are not built for transactional row updates.

Every step links into the tool's own documentation; links were checked on 2026-10-11 · version 1

Setup (one time)

1. Install PostgreSQL 18 with a package installer or as a local container, start the server and connect with psql as a superuser.
Every step runs against a local server, and the cited pages describe version 18 behaviour, so matching the major version avoids surprises. Installation itself is not covered by the cited page, so use your installer's own instructions.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: A psql prompt is open and the version query reports major version 18.
2. Create a practice database and a plain, unpartitioned events table with an event date, an entity id and a value, then load a few million generated rows spread over about ninety days.
The unpartitioned table is the baseline you will compare partitioned layouts against. Ninety days of rows is enough for pruning to be visible on a laptop.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: A row count on the baseline table returns the number of rows you generated, spread across about ninety distinct dates.
3. Open a second psql session against the same practice database and keep both windows visible.
Concurrency behaviour can only be observed with two sessions working at the same time. Several later steps reuse the second session as a concurrent reader or writer.
Explained in: PostgreSQL: Documentation: 18: 13.1. Introduction (www.postgresql.org), checked 2026-10-11
Done when: Both sessions list the same baseline table and each reports a different backend process id.

Steps

1. Run a transaction in one session that updates rows without committing, and query the same rows from the second session.
PostgreSQL is used as the operational store because readers see a consistent snapshot and are not blocked by writers. Seeing it first hand explains why it suits transactional metadata and pipeline state.
Explained in: PostgreSQL: Documentation: 18: 13.1. Introduction (www.postgresql.org), checked 2026-10-11
Done when: The second session returns the old values immediately without waiting, and returns the new values only after the first session commits.
2. Create a training runs table as an operational metadata store, with fixed columns for run id, model name and status and a jsonb column for parameters and metrics, then insert a few dozen runs.
Run and model metadata has a stable core and a variable tail, which is the usual case for a relational table with one jsonb column. This is PostgreSQL doing the metadata store job from the lesson.
Explained in: PostgreSQL: Documentation: 18: 8.14. JSON Types (www.postgresql.org), checked 2026-10-11
Done when: A query returns runs with one parameter extracted from the jsonb column as its own output column.
3. Query the runs table with a jsonb containment condition, then create a GIN index on the jsonb column and compare the query plan before and after.
Metadata stores are queried by arbitrary keys, and an unindexed jsonb search reads every row. The plan comparison shows what the index changes.
Explained in: PostgreSQL: Documentation: 18: 8.14. JSON Types (www.postgresql.org), checked 2026-10-11
Done when: The plan for the containment query changes from a sequential scan to a scan that uses the GIN index.
4. Create a key-value feature table with the entity id as primary key and a jsonb value holding the feature vector, load one row per entity and run single-key lookups with timing switched on.
Online inference reads one entity by key with a tight latency budget, the access pattern a dedicated key-value store serves. Building it in PostgreSQL shows how far a primary key lookup gets you before a separate store is needed.
Explained in: PostgreSQL: Documentation: 18: Chapter 11. Indexes (www.postgresql.org), checked 2026-10-11
Done when: The plan for a single-key lookup shows an index scan on the primary key and the reported execution time stays near a millisecond or below.
5. Create a covering index on the feature table that includes the columns the lookup returns, vacuum the table, and compare the lookup plan with the previous step.
An index-only scan answers the lookup without visiting the table, the closest PostgreSQL equivalent of a pure key-value read. It also shows the write and storage cost each extra index adds.
Explained in: PostgreSQL: Documentation: 18: Chapter 11. Indexes (www.postgresql.org), checked 2026-10-11
Done when: The lookup plan shows an index-only scan with zero or near zero heap fetches.
6. Create a range-partitioned copy of the events table with one partition per day and load the baseline rows into it, as a local stand-in for the date-partitioned layout of a lakehouse table.
Lakehouse table formats organise bulk data into partitions so a reader can skip most files. Declarative partitioning lets you practise the same layout decision without paid cloud storage.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: Listing the parent table shows one child partition per day and each partition holds only rows from its own date.
7. Run the same one-week date-filtered aggregate against the baseline table and the partitioned table with explain analyze and buffers, then repeat with partition pruning disabled.
Warehouses bill by data scanned or compute time, so the partitions and buffers a query touches are a local proxy for spend. Turning pruning off shows what a badly laid out table costs.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: With pruning on, the plan lists only the seven matching partitions and reads clearly fewer buffers than both the baseline plan and the pruning-off plan.
8. Create a multicolumn index on the partitioned table led by entity id, then compare plans for a filter on entity id plus date and a filter on the second index column alone.
Warehouse clustering sorts data inside a partition so selective filters skip blocks, and column order in a multicolumn index has the same effect here. The comparison shows which filters benefit from the chosen order.
Explained in: PostgreSQL: Documentation: 18: Chapter 11. Indexes (www.postgresql.org), checked 2026-10-11
Done when: The entity filter uses the new index inside the pruned partitions, while the filter that skips the leading column uses it less efficiently or not at all.
9. Write a dataset versions table that records a version label, creation time, the date range of partitions the version covers and a jsonb manifest of row counts and schema, then register two versions.
Reproducible training needs a record of exactly which data a model saw, the job a catalog or table format snapshot does in a lakehouse. Keeping it in PostgreSQL ties the version to transactional run metadata.
Explained in: PostgreSQL: Documentation: 18: 8.14. JSON Types (www.postgresql.org), checked 2026-10-11
Done when: Joining a run to its dataset version returns the partition range and the row counts stored in the manifest.
10. Run a retention cycle on the partitioned table: detach the oldest daily partition, export or archive it as a standalone table, drop it, and create the partition for the next day.
Lifecycle management by partition removes old data as a quick metadata operation instead of a slow bulk delete. This is how storage growth and spend are kept bounded.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: The oldest date is gone from the parent table, the detached table still holds its rows until you drop it, and an insert for the new day succeeds.
11. Read the best practices and limitations parts of the version 17 partitioning page and write down a partition key and partition count for a table one hundred times larger than yours.
Too many partitions slow planning and use more memory, and too few make pruning ineffective. Reading the previous version also shows which behaviours to check before a major upgrade.
Explained in: PostgreSQL: Documentation: 17: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: You have a written partition key, granularity and expected partition count, each backed by a constraint or recommendation from the page.
12. Configure a production-like rotation: add a default partition, load the next day into a standalone table with a matching check constraint and attach it, then detach the oldest partition concurrently while the second session runs continuous inserts and date-filtered reads.
In production, partition maintenance must run while the table is live, so what matters is which locks each operation takes. Doing it under concurrent load shows whether the rotation is safe to schedule.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: The attach and the concurrent detach both complete while the second session keeps inserting and reading with no errors and no visible stall.

Back to Storage for ML: lakehouse, warehouse and operational data stores