Practice: SQL and data systems: schemas, indexing and partitioning in Postgres

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

Main tool: SQL · Schemas, indexes, partitions and query plans are all expressed and inspected in SQL itself, so practising it directly against a local Postgres is the only way to see what each design choice does. · Alternatives: MySQL and SQLite speak close SQL dialects but have fewer index types and weaker partitioning; DuckDB runs analytic SQL in-process with no server; ORM layers such as Prisma or Drizzle ORM generate the SQL for you and hide the plans; migration tools such as Alembic or Flyway version the schema changes but do not design them.

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 on your laptop with a package installer or a container, start the server and connect with psql.
Every later step runs SQL against a real server you fully control. A local instance lets you break things without touching anyone's production.
Explained in: PostgreSQL: Documentation: 18: Part II. The SQL Language (www.postgresql.org), checked 2026-10-11
Done when: A psql prompt is open and a query for the server version returns a version 18 string.
2. Create a practice database and a dedicated schema named shop inside it, and set your search path to that schema.
Keeping practice objects in their own schema mirrors how real deployments separate applications and makes cleanup a single drop.
Explained in: PostgreSQL: Documentation: 18: 5.10. Schemas (www.postgresql.org), checked 2026-10-11
Done when: Listing schemas in psql shows shop, and a new unqualified table is created in shop rather than public.
3. Generate sample data with the generate_series table function: about one million orders spread over two years, plus customers and products tables.
Index and plan behaviour only becomes visible at volume. Set-returning functions in the FROM clause build realistic data without any external file.
Explained in: PostgreSQL: Documentation: 18: 7.2. Table Expressions (www.postgresql.org), checked 2026-10-11
Done when: A row count on the orders table returns roughly one million rows.

Steps

1. Run a first set of queries that join orders to customers and products, filter with WHERE, and aggregate with GROUP BY and HAVING.
This is the first run: it confirms the data is usable and refreshes how FROM, joins and grouping compose before you start tuning anything.
Explained in: PostgreSQL: Documentation: 18: 7.2. Table Expressions (www.postgresql.org), checked 2026-10-11
Done when: A query returns revenue per customer per month and you can explain which join type each table uses.
2. Create a new version of the customers and orders tables whose primary keys are identity columns, and try inserting an explicit key value.
Identity columns are the standard way to get surrogate keys in Postgres, and the always versus by default choice decides whether application code can override them.
Explained in: PostgreSQL: Documentation: 18: 5.3. Identity Columns (www.postgresql.org), checked 2026-10-11
Done when: Inserts without a key get sequential ids, and an explicit id is rejected on the generated always table.
3. Add not null, check, unique, primary key and foreign key constraints to the tables, then run inserts and deletes that violate each one.
Constraints are where schema design encodes business rules. Seeing each violation message teaches you what the database will and will not protect you from.
Explained in: PostgreSQL: Documentation: 18: 5.5. Constraints (www.postgresql.org), checked 2026-10-11
Done when: Each bad statement fails with an error naming the constraint, and deleting a referenced customer behaves as your chosen on delete action says.
4. Add a generated column to orders that computes the total from quantity and unit price, then try to update it directly.
Generated columns keep derived values consistent without triggers or application code, and they have rules about what they may reference that you should meet first-hand.
Explained in: PostgreSQL: Documentation: 18: 5.4. Generated Columns (www.postgresql.org), checked 2026-10-11
Done when: The total is filled in automatically on insert and a direct update of that column is rejected.
5. Create one index of each main type on suitable columns: B-tree on order date, hash on an equality-only key, GIN on a JSON or array column, and BRIN on an append-ordered timestamp.
Each index type supports different operators and has a different size and write cost. Building them side by side makes the trade-offs concrete.
Explained in: PostgreSQL: Documentation: 18: 11.2. Index Types (www.postgresql.org), checked 2026-10-11
Done when: Listing the table's indexes in psql shows four access methods with visibly different sizes.
6. Create two multicolumn B-tree indexes on customer and order date in opposite column orders, and query with a filter on only one column at a time.
Column order decides which queries an index can serve efficiently. The leading column rule is the most common indexing mistake in the field.
Explained in: PostgreSQL: Documentation: 18: 11.3. Multicolumn Indexes (www.postgresql.org), checked 2026-10-11
Done when: You can state which of the two indexes serves a customer-only filter and which serves a date-only filter, and why.
7. Run EXPLAIN and then EXPLAIN ANALYZE with buffers on the queries from the previous steps, and read the plan tree from the innermost node outward.
The plan is the only reliable evidence of what a query costs. Reading scan types, join methods, estimated versus actual rows and timing is the core diagnostic skill.
Explained in: PostgreSQL: Documentation: 18: 14.1. Using EXPLAIN (www.postgresql.org), checked 2026-10-11
Done when: For one query you can point to the node that takes the most time and say whether it used a sequential, index or bitmap scan.
8. Query the planner statistics views for the orders table, load a skewed batch of rows, then run ANALYZE and compare the row estimates before and after.
Bad plans usually come from stale or missing statistics rather than missing indexes. Seeing estimates move after ANALYZE shows what the planner is really working from.
Explained in: PostgreSQL: Documentation: 18: 14.2. Statistics Used by the Planner (www.postgresql.org), checked 2026-10-11
Done when: The estimated row count in the plan for the skewed value moves close to the actual count after ANALYZE.
9. Write the same question, customers with no orders in the last ninety days, three ways using NOT EXISTS, NOT IN and an outer join, save each plan output to a file and compare them.
Saving and comparing plans for equivalent queries is how you prove a rewrite is faster rather than guessing. The subquery forms also differ in how they treat nulls.
Explained in: PostgreSQL: Documentation: 18: 9.24. Subquery Expressions (www.postgresql.org), checked 2026-10-11
Done when: Three saved plan files exist, the three queries return the same rows on null-free data, and you can name the cheapest form.
10. Create a range-partitioned copy of orders with one partition per month, load the data, attach a new partition, detach an old one, and check the plan of a one-month query.
Partitioning is the main tool for large time-series tables: it makes retention a metadata operation and lets the planner skip partitions that cannot match.
Explained in: PostgreSQL: Documentation: 18: 5.12. Table Partitioning (www.postgresql.org), checked 2026-10-11
Done when: The plan for a single-month query scans only one partition, and the detached month remains as a standalone table.
11. Load a second set of tables with deliberately cryptic names into a separate schema, then query the system catalog schema to list its tables, columns and foreign keys and write a glossary mapping each to a business concept.
On a customer site the schema is often undocumented, and the catalogs plus foreign key constraints are the most trustworthy map of how entities relate. Doing this read-only keeps production safe.
Explained in: PostgreSQL: Documentation: 18: 5.10. Schemas (www.postgresql.org), checked 2026-10-11
Done when: You have a one-page glossary naming each table's business meaning and its relationships, built only from catalog queries.
12. Configure a production-like layout: one schema per application owned by a dedicated role, create privilege revoked from the public schema, a read-only role with usage on the schema, and an explicit search path for each role.
This is how a shared production database is kept safe: ownership, privileges and search path decide who can see and change what. A read-only role is what you should ask for when querying a customer's system.
Explained in: PostgreSQL: Documentation: 18: 5.10. Schemas (www.postgresql.org), checked 2026-10-11
Done when: Connected as the read-only role, selects on the application schema succeed while a create table or an insert is refused.

Back to SQL and data systems: schemas, indexing and partitioning in Postgres