SQL playground

A real PostgreSQL database in your browser, loaded with a sample shop. Run queries and read their plans.

Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.

On this page
  1. What is in the sample database
  2. Things to try
  3. About the engine

A real PostgreSQL database runs in this page, inside your browser. It holds a small online shop: users, products, orders and the items in each order. Write any SQL you like: read, change, add an index, drop a table. Nothing leaves your machine, and Reset puts everything back the way it was.

What is in the sample database#

The same data is loaded every time, so the plans and numbers you see match the ones on the other pages.

sql
select 'users' as table_name, count(*) as rows from users
union all select 'products', count(*) from products
union all select 'categories', count(*) from categories
union all select 'orders', count(*) from orders
union all select 'order_items', count(*) from order_items
union all select 'events', count(*) from events;
Result 6 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
table_namerows
users20000
products2000
categories40
orders100000
order_items300350
events60000
TableColumnsWorth knowing
usersid, email, name, country, status, created_atcountry is skewed: about 40% IN, only about 2% NG. status is mostly active; 3% are banned. email is unique.
productsid, category_id, name, price40 categories.
ordersid, user_id, status, created_at, totalIds rise with created_at, about one order every ten minutes over two years. A few users order a lot: user 1 has hundreds of orders, the typical user has four. About 4% of orders are pending.
order_itemsorder_id, product_id, qty, priceOne to five products per order. The key is (order_id, product_id).
eventsid, user_id, kind, atPartitioned by month across 2024.

Every table has a primary key, and the foreign keys are declared, but there are no other indexes. Deciding which ones to add is what most of this section is about.

The skew is deliberate. Real data is rarely even, and a query plan that is right for a rare value can be wrong for a common one.

Things to try#

  1. Run One user's orders, then press Explain analyze. The database reads every one of the 100,000 orders to find a handful. Look for Seq Scan and Rows Removed by Filter.
  2. Run Add an index, go back to the same query and explain it again. The plan changes, and the work drops by orders of magnitude.
  3. Change user_id = 42 to user_id = 1. User 1 has hundreds of orders. Does the plan change again?
  4. Press Reset the database to throw your changes away.

About the engine#

The database is PGlite, PostgreSQL compiled to WebAssembly. It is the real PostgreSQL planner and executor, so plans read exactly as they would on a server. Two differences matter: it has a single connection, so two sessions cannot block each other here (the pages on locks use recordings from a real server instead), and it runs in your browser's memory, so absolute timings are slower than a tuned server. Compare plans and row counts, not milliseconds.

Esc
↑ ↓ to move↵ to open