PostgreSQL Terminal Simulator
Practice the psql tool and Postgres engine in an interactive browser terminal. Explore with meta-commands, read real EXPLAIN plans, add an index to turn a sequential scan into a bitmap index scan, and use BEGIN and ROLLBACK to make changes safe, all against a small e-commerce database with verified PostgreSQL output.
Category: Database
What You Will Learn
- Explore a database with psql meta-commands like \dt, \d, \di, and \l
- Read a real EXPLAIN plan and recognise a sequential scan
- Create a B-tree index and watch the plan switch to a bitmap index scan
- Compare estimated cost (3971 to 145) and use EXPLAIN ANALYZE for actual rows
- Wrap changes in BEGIN and undo them safely with ROLLBACK
- Understand why the prompt changes to shop=*# inside an open transaction
- Tell psql client meta-commands apart from SQL sent to the Postgres server
Topics covered: postgresql, psql, database, explain, indexes, transactions, sql, educational, interactive
// simulator
PostgreSQL Terminal Simulator
Practice the psql tool and Postgres engine in an interactive browser terminal. Explore with meta-commands, read real EXPLAIN plans, add an index to turn a sequential scan into a bitmap index scan, and use BEGIN and ROLLBACK to make changes safe, all against a small e-commerce database with verified PostgreSQL output.
// psql lab
PostgreSQL Terminal Simulator
Practice the psql tool and Postgres engine in a safe browser lab. Explore with meta-commands, read real EXPLAIN plans, add an index and watch a sequential scan become a bitmap index scan, then wrap changes in a transaction and roll them back.
5
Tables
5
Indexes
12
Orders
List every table in the current database.
Connected to database "shop" as user "neondb_owner".
Type "\?" for meta-command help, or follow the current task above.
About this PostgreSQL simulator
This is a companion to the SQL Terminal Simulator, but it teaches the tool and engine instead of the SQL language. You practice the psql client and how the Postgres planner and storage behave: meta-commands, EXPLAIN, indexes, and transactions.
What you'll learn
- How psql meta-commands (\dt, \d, \di, \l, \conninfo) inspect a database
- How EXPLAIN shows the plan the planner would run, without executing it
- Why a filter with no index becomes a full sequential scan
- How CREATE INDEX turns that scan into a bitmap index scan and cuts the cost
- How EXPLAIN ANALYZE reports actual rows and where time really goes
- How BEGIN, COMMIT, and ROLLBACK make changes safe to undo
Key psql commands
- Explore: \dt, \d NAME, \di, \l, \conninfo, \x, \timing, \?
- Query: SELECT count(*) FROM orders;, SELECT * FROM customers;
- Plan: EXPLAIN and EXPLAIN ANALYZE on a filtered query
- Tune: CREATE INDEX on the column you filter by
- Transact: BEGIN, DELETE, ROLLBACK, COMMIT
How this lab runs
Nothing connects to a real server. The lab models a small e-commerce database (customers, products, orders, order_items) plus a 200,000-row big_events table, and every meta-command listing, EXPLAIN plan, and psql-aligned table is reproduced from real PostgreSQL output. The cost dropping from 3971 to 145 after you add an index is exactly what Postgres reports, so the intuition you build here carries straight over to a real database.
Practice on real Postgres
A simulator builds intuition, but running these commands against a live database is what makes them stick. You can spin up a free Postgres in seconds with Neon and run \dt, EXPLAIN, and transactions for real, or run managed PostgreSQL on DigitalOcean (new accounts get $200 in credits). Load a table, run EXPLAIN before and after CREATE INDEX, and compare the plans against what you saw here.
Why learn Postgres this way?
- EXPLAIN stops being scary once you have watched a seq scan turn into an index scan.
- Most day-to-day database work is a handful of meta-commands plus reading a plan.
- Knowing that ROLLBACK undoes an open transaction is what makes changes safe to try.
Related reading
The plans you read here are downstream of how you design the schema. These deep dives cover the choices that decide whether Postgres stays fast as the data grows:
- Stop Using Random UUIDs as Primary Keys — how a random primary key scatters index writes, and the time-ordered fix.
- Stop Paginating With OFFSET — why deep OFFSET pages read and discard everything before them, and how keyset pagination keeps EXPLAIN flat at any depth.
Learn the SQL side too
This lab is about the psql tool and the query planner. To practice writing the queries themselves, try the SQL Terminal Simulator.
Try next
// simulator
SQL Terminal Simulator
Practice SQL in an interactive browser terminal. Learn SELECT, WHERE, ORDER BY, DISTINCT, aggregates, GROUP BY, HAVING, JOINs, subqueries, and window functions against a small e-commerce schema, with single-table queries evaluated live and verified Postgres output.
// simulator
Database Indexing Simulator
Learn how database indexes work with an interactive simulator. See the difference between full table scans and index seeks, understand B-tree structure, and learn when to create indexes.
// simulator
Database Replication, Sharding & Scaling
Explore database replication, sharding, and scaling with an interactive simulator. Compare sync vs async replication, shard key choices, hot spots, failover behavior, read replicas, write scaling, connection pooling, cost, and latency.