Benchmark Updates with OrioleDB

Compare heap and OrioleDB performance on repeated PostgreSQL updates.

Introduction

30 Second Summary

A system can feel fast until the same information gets rewritten thousands of times. Those constant changes can leave invisible clutter that slows future work.

In this project, you will use Supabase to test standard Postgres storage against OrioleDB with the same update-heavy workload. You'll turn the measurements into a chart that makes the difference visible.

What You'll Build

Your finished benchmark presents a side-by-side chart of both storage engines under the same repeated-update test.

By the end of this project, you'll have:

  • A repeatable benchmark that sends the same constant-update workload to standard Postgres storage and OrioleDB.
  • A measured comparison showing the elapsed time and table size for each storage engine.
  • A bloat check showing where dead tuples collect after repeated updates.
  • Secret Mission: An optional challenge to push your benchmarking skills further.

Are there any prerequisites?

You'll need a free Supabase account plus basic SQL knowledge. The benchmark runs inside a free Supabase project.

Before We Start

This benchmark runs inside the Supabase dashboard. Before any table can exist, you need a browser that can reach the service.

This step gets your browser ready. You will also make sure a Supabase account is available for the next step.

In this step, get ready to:
  • Open a web browser that can reach the Supabase sign-up page.
  • Access an existing Supabase account or create a new one.
  • Confirm that the Supabase dashboard recognizes your account.
Open your browser

The Supabase dashboard works in your web browser. Opening it now gives you one workspace for every later step.

  • Press Cmd+Space on macOS or the Windows key on Windows to open the search bar.
  • Type the name of your web browser into the search bar.
  • Press Enter to open the browser.
  • Open the official Supabase sign-up page in your browser.

You should see a page headed Create a new account. Good start. Your browser can reach the service you will use for the benchmark.

Can't Reach the Sign-Up Page?

  • Check that another website loads in the same browser.
  • Reload the Supabase sign-up page after confirming your internet connection.

Ask for help with the browser connection: Why can't my browser load the Supabase sign-up page?

Sign in or create an account

A Supabase account gives you access to the dashboard where projects are managed. Choose the path that matches your current account status.

✔️ I already have an account

  • Open the official Supabase sign-in page.
  • Complete the sign-in flow with the method connected to your existing Supabase account.

Supabase should take you to the dashboard after it recognizes your account.

ⓧ I want to continue with GitHub

Supabase sends you to GitHub for approval before returning you to the dashboard.

  • Select Continue with GitHub on the Create a new account page.
  • Complete the approval prompt shown by GitHub.
  • Complete any account prompt shown after Supabase opens again.

Supabase should open your dashboard once the account flow finishes.

ⓧ I want to use email

Your password stays inside the Supabase account form. Keep it out of project files throughout this benchmark.

  • Enter your email address in the Email field.
  • Enter a new password that meets every displayed requirement in the Password field.
  • Select Sign up.
  • Follow the verification link from Supabase if email confirmation is requested.
  • Open the official Supabase sign-in page after completing the confirmation.
  • Complete the sign-in flow with your new account.

Supabase should open your dashboard once your email account is ready.

That is the account hurdle cleared. You now have access to the dashboard that hosts the benchmark.

Can't Access Your Account?

  • Use Sign in if Supabase says your email already belongs to an account.
  • Complete any pending email verification before trying the email sign-in flow again.
  • Use the same authentication method that you selected when creating the account.

Get help with your account flow: Why can't I access my Supabase dashboard after signing up or signing in?

Verify dashboard access

The next step depends on an active account session. The dashboard provides visible proof that Supabase recognizes you.

Before you check, what do you expect the dashboard to show when your account is ready?

You should see the dashboard with a Create a new project option. Your browser and Supabase account are ready.

Your account access is in place. Next, you will turn that access into the database environment for your benchmark.

Create Your Project and Enable OrioleDB

Your benchmark needs a database that can create both standard heap tables and OrioleDB tables. The storage engine must be chosen while the Supabase project is created.

You will create a free project with Postgres and OrioleDB. You will finish with the project's SQL Editor ready for the benchmark tables.

In this step, get ready to:
  • Create a free Supabase project in your preferred region.
  • Choose Postgres with OrioleDB during project creation.
  • Confirm the project is ready in the SQL Editor.
Sign in and fill in the project details

You are in. The project creation form now gives you control over the database that runs your benchmark.

  • Select the organization that will own your benchmark project.
  • Choose the Free plan.

The free plan is enough for this benchmark. Your results will reflect the capacity of a small test database.

  • Enter orioledb-update-benchmark as the project name.
  • Choose the region closest to you.

The project name keeps every result tied to this benchmark. A nearby region reduces network delay while you work in the dashboard.

Your database password controls direct access to the database. Treat it like a credential even though this project only contains test data.

  • Enter a strong password in the database password field.
  • Store the password in your password manager.

Your password is now saved outside the browser. You can recover it when a later database connection needs it.

Choose OrioleDB and create the project

Supabase binds OrioleDB to a project during creation. The engine cannot be added to an existing project or removed later.

  • Expand Advanced Configuration.

The expanded section reveals the database engine options for this project.

  • Select Postgres with OrioleDB under Postgres Type.

You will see Postgres with OrioleDB selected with a Public Beta label. This selection enables OrioleDB for every table that uses the engine in this project.

  • Crop your screenshot to the Advanced Configuration section so the database password stays hidden.

Provisioning takes a couple of minutes while Supabase configures the database. A short wait on the creation screen is expected.

  • Click Create new project.
  • Wait for the project dashboard to become available.

That is the critical setup complete. Your benchmark project now has OrioleDB built into its Postgres configuration.

Project creation not completing?

  • Review any highlighted form fields for a missing project name or region.
  • Check that the database password meets the requirements shown below its field.
  • Return to the new project form if OrioleDB is missing because it cannot be added after creation.

Help me troubleshoot my OrioleDB project creation. Visit Supabase Support for account-specific help.

Confirm the project and open the SQL Editor

Before the final check, what project name do you expect the dashboard to show?

  • Confirm the dashboard header shows orioledb-update-benchmark.
  • Select SQL Editor from the project sidebar.

You will see the SQL Editor inside orioledb-update-benchmark. The Postgres with OrioleDB selection you captured confirms the engine is installed because Supabase locks that choice when it creates the project.

Your Project Checkpoint

Enabled and installed in project 'orioledb-update-benchmark'.

Project named 'orioledb-update-benchmark' on the free plan, created in a learner-selected region with database access available through the Supabase SQL Editor.

Your OrioleDB project is ready for data. Next, you will create matching counter tables and seed the rows that drive your benchmark.

Create and Seed the Counter Tables

The OrioleDB extension is installed in your Supabase project. Your database is ready for a table that uses the new storage engine.

A fair benchmark needs two tables with the same structure. One table uses the default PostgreSQL heap engine. The other uses OrioleDB.

In this step, get ready to:
  • Create matching counter tables on the two storage engines.
  • Seed each table with 100000 identical rows.
  • Verify that both tables have the same starting data.
Create the matching table schemas

A table access method determines how PostgreSQL stores its rows. The first table uses the default heap access method to provide your benchmark baseline.

  • Replace the current query in the SQL Editor from earlier with this SQL:
-- Create the baseline table with PostgreSQL's default heap storage.
CREATE TABLE heap_counters (
  id bigint PRIMARY KEY,
  counter integer NOT NULL DEFAULT 0
);

What does this SQL do?

  • The id column gives every row a unique identifier.
  • The counter column stores the value that later workloads repeatedly update.
  • The DEFAULT 0 rule supplies the starting counter value when an insert omits that column.
  • The missing storage clause keeps heap_counters on PostgreSQL's default heap access method.
  • Click Run in the SQL Editor toolbar.

The results area should confirm that the query completed. Solid start. Your benchmark now has its heap baseline.

Heap Table Query Not Completing?

  • Check that the table name is heap_counters.
  • Check that the first column definition ends with a comma.
  • Move to the next table if the results indicate that heap_counters already exists.

Help me fix the heap table creation query.

The comparison table needs the same columns. Its storage clause sends those rows to OrioleDB.

  • Replace the current query with this SQL:
-- Create the comparison table with the same columns on OrioleDB.
CREATE TABLE oriole_counters (
  id bigint PRIMARY KEY,
  counter integer NOT NULL DEFAULT 0
) USING orioledb;

What changes in this table?

  • The column definitions match heap_counters exactly.
  • The USING orioledb clause selects OrioleDB as the table access method.
  • The matching schema keeps the later comparison focused on the storage engines.
  • Click Run in the SQL Editor toolbar.

The results area should confirm that the query completed. You now have one empty counter table on each storage engine.

OrioleDB Table Query Not Completing?

  • Check that the table name is oriole_counters.
  • Check that the final clause reads USING orioledb exactly.
  • Confirm that the OrioleDB extension from the previous step still appears as installed.

Help me fix the OrioleDB table creation query.

Seed both tables with identical rows

Both engines need the same starting dataset. The generate_series(1, 100000) function produces every identifier from 1 through 100000.

  • Replace the current query with this SQL:
-- Seed each engine with the same IDs and default counter values.
INSERT INTO heap_counters (id)
SELECT generate_series(1, 100000);

INSERT INTO oriole_counters (id)
SELECT generate_series(1, 100000);

How does the seed query work?

  • Each INSERT sends the generated identifiers to one benchmark table.
  • The generated series includes both endpoints of the range.
  • The insert targets only the id column.
  • The omitted counter column receives its default value of 0.
  • Click Run in the SQL Editor toolbar.

Keep the SQL Editor open while both inserts complete. The results area should confirm that both statements finished.

Both engines now contain the same generated identifiers. That shared starting point keeps the later workload comparison fair.

Seed Query Not Completing?

  • Check that both table names match the names created in the previous substep.
  • Check that each insert targets only the id column.
  • Move to verification if a primary-key error indicates that the seed query already ran.

Help me troubleshoot the seed query.

Verify the starting state

Matching row counts prove that both tables contain the same number of records. Checking the identifier range plus the counter limits confirms that their values match too.

  • Replace the current query with this SQL:
-- Compare the row range and counter values across both engines.
SELECT 'heap_counters' AS table_name,
       count(*) AS row_count,
       min(id) AS first_id,
       max(id) AS last_id,
       min(counter) AS lowest_counter,
       max(counter) AS highest_counter
FROM heap_counters
UNION ALL
SELECT 'oriole_counters' AS table_name,
       count(*) AS row_count,
       min(id) AS first_id,
       max(id) AS last_id,
       min(counter) AS lowest_counter,
       max(counter) AS highest_counter
FROM oriole_counters
ORDER BY table_name;

What does this check prove?

  • The row_count value confirms how many records each table contains.
  • The first_id plus last_id values confirm the complete identifier range.
  • The counter limits reveal whether every row still has its default value.

Before you run this check, do you expect any value to differ between the two result rows?

  • Click Run in the SQL Editor toolbar.

Both result rows should show 100000 in row_count. The first_id value should be 1.

The last_id value should be 100000. Both counter limits should be 0.

Counts or Values Do Not Match?

  • Check that both seed statements use generate_series(1, 100000).
  • Check that both table definitions set counter to DEFAULT 0.
  • Check that both insert statements completed in the results area.

Help me diagnose mismatched seed results.

✔️ Awesome, I've got everything!

Your two counter tables now match in structure. They also contain the same starting values.

ⓧ I'd like to double check the full code

Compare your SQL with the complete setup sequence below.

-- Create the baseline table with PostgreSQL's default heap storage.
CREATE TABLE heap_counters (
  id bigint PRIMARY KEY,
  counter integer NOT NULL DEFAULT 0
);

-- Create the comparison table with the same columns on OrioleDB.
CREATE TABLE oriole_counters (
  id bigint PRIMARY KEY,
  counter integer NOT NULL DEFAULT 0
) USING orioledb;

-- Seed each engine with the same IDs and default counter values.
INSERT INTO heap_counters (id)
SELECT generate_series(1, 100000);

INSERT INTO oriole_counters (id)
SELECT generate_series(1, 100000);

-- Compare the row range and counter values across both engines.
SELECT 'heap_counters' AS table_name,
       count(*) AS row_count,
       min(id) AS first_id,
       max(id) AS last_id,
       min(counter) AS lowest_counter,
       max(counter) AS highest_counter
FROM heap_counters
UNION ALL
SELECT 'oriole_counters' AS table_name,
       count(*) AS row_count,
       min(id) AS first_id,
       max(id) AS last_id,
       min(counter) AS lowest_counter,
       max(counter) AS highest_counter
FROM oriole_counters
ORDER BY table_name;

Your benchmark now has two equal starting datasets on different storage engines. Next, you will repeatedly update the heap table to measure its time plus table size.

Benchmark the Heap Table

Your Supabase project now has two matching counter tables with 100,000 rows each. That shared starting point keeps the storage-engine comparison fair.

Repeated updates make the default PostgreSQL heap create new row versions. In this step, you will apply a fixed workload to the heap table. You will capture its execution time plus its total size as your baseline.

In this step, get ready to:
  • Run a standardized update workload against the heap table.
  • Record the heap workload's two baseline measurements.
  • Verify that only the targeted counter rows changed.
Run the repeated-update workload

A fair benchmark uses the same number of operations against the same rows. This workload updates rows 1 through 1000 exactly 100 times.

  • Return to the SQL Editor from earlier.
  • Replace the current query with the benchmark block by pasting the code below:
-- Repeat one update 100 times to create a consistent heap workload.
DO $$
BEGIN
  FOR iteration IN 1..100 LOOP
    -- Increment the same 1,000 counters on every pass.
    UPDATE heap_counters
    SET counter = counter + 1
    WHERE id BETWEEN 1 AND 1000;
  END LOOP;
END
$$;

What does this workload do?

  • The DO block runs the temporary workload once without creating a stored function.
  • The FOR loop repeats its contents 100 times.
  • Each UPDATE increments the same 1,000 rows in heap_counters.
  • Every targeted counter starts at 0. Repeating the increment 100 times should leave each one at 100.

Before you run the workload, do you expect its timing to resemble the earlier row-count check or take longer?

  • Click Run in the SQL Editor.
  • Wait for the SQL Editor to report that the query completed.
  • Record the displayed execution time here: your heap execution time.

That is your heap workload complete. You now have the first measurement for the storage-engine comparison.

Did the workload fail?

  • Check that the loop range is 1..100.
  • Check that the row condition targets IDs from 1 through 1000.
  • Confirm that every statement inside the block ends with a semicolon.

Help me diagnose the heap benchmark query.

Measure the heap table size

Execution time captures speed. Total relation size captures the table's complete on-disk footprint after the updates.

  • Replace the workload block in the SQL Editor with the size query below:
-- Return the complete on-disk footprint in bytes.
SELECT pg_total_relation_size('heap_counters') AS heap_table_size_bytes;

What does this measurement include?

The pg_total_relation_size() function returns the table's storage footprint in bytes. Its result includes the table data plus associated indexes and TOAST data.

Recording the raw byte value gives you a consistent number to compare with the OrioleDB table later.

  • Click Run in the SQL Editor.
  • Find the byte value under heap_table_size_bytes in the result row.
  • Record the byte value here: your heap table size in bytes.

No table size returned?

  • Check that the table name is spelled heap_counters.
  • Keep the single quotes around the table name inside pg_total_relation_size().
  • Confirm that the query uses the project where you created the two counter tables.

Help me fix the heap size query.

Verify the final counter distribution

The workload is valid only if every targeted row received 100 increments. The untouched rows must still hold their original counter value.

  • Replace the size query in the SQL Editor with the verification query below:
-- Check that only the targeted counters changed.
SELECT
  count(*) AS total_rows,
  count(*) FILTER (WHERE id BETWEEN 1 AND 1000 AND counter = 100) AS updated_rows,
  count(*) FILTER (WHERE id BETWEEN 1001 AND 100000 AND counter = 0) AS untouched_rows
FROM heap_counters;

What does this verification check?

  • The total_rows result confirms that the workload preserved every table row.
  • The updated_rows result counts targeted rows that reached the required counter value.
  • The untouched_rows result counts all remaining rows that stayed at zero.

Before you run the check, what three counts should prove that the workload changed only its intended rows?

  • Click Run in the SQL Editor.

You should see 100000 for total_rows. You should also see 1000 for updated_rows plus 99000 for untouched_rows.

Your heap baseline is ready. The timing and size now describe a verified workload with no missing rows or stray updates.

Do the counts differ?

  • Check whether you clicked Run on the workload more than once.
  • Confirm that the workload used the range 1..100.
  • Confirm that the workload targeted IDs between 1 and 1000.

Help me interpret unexpected heap counter values.

✔️ Awesome, I've got everything!

Your heap benchmark includes an execution time plus a total relation size in bytes. The verification counts also confirm that the workload ran exactly once.

ⓧ I'd like to double check the full code

This reference combines the three SQL sections used in this step.

  • Compare each section with the query you ran in the SQL Editor.
  • Do not run the combined script again because the workload would increment the targeted counters a second time.
-- Repeat one update 100 times to create a consistent heap workload.
DO $$
BEGIN
  FOR iteration IN 1..100 LOOP
    -- Increment the same 1,000 counters on every pass.
    UPDATE heap_counters
    SET counter = counter + 1
    WHERE id BETWEEN 1 AND 1000;
  END LOOP;
END
$$;

-- Return the complete on-disk footprint in bytes.
SELECT pg_total_relation_size('heap_counters') AS heap_table_size_bytes;

-- Check that only the targeted counters changed.
SELECT
  count(*) AS total_rows,
  count(*) FILTER (WHERE id BETWEEN 1 AND 1000 AND counter = 100) AS updated_rows,
  count(*) FILTER (WHERE id BETWEEN 1001 AND 100000 AND counter = 0) AS untouched_rows
FROM heap_counters;

You have a measured heap baseline from a controlled workload. Next, you will run the identical test against the OrioleDB table to see how its timing and storage footprint compare.

Benchmark the OrioleDB Table

Your heap benchmark now gives you a baseline. The Supabase SQL Editor holds the recorded execution time and table-size measurement.

A fair storage-engine comparison keeps the workload constant. This run sends the same 100 updates through the OrioleDB table so the two measurements are comparable.

In this step, get ready to:
  • Run the standardized update workload against oriole_counters.
  • Record the execution time and total table size.
  • Confirm the updated rows reached the expected counter value.
Run the matching update workload

The loop controls the workload shape. It revisits the same 1,000 rows 100 times so this run matches the heap benchmark.

  • In the SQL Editor from earlier, select the current query text.
  • Replace the selected text with the matching OrioleDB workload by pasting this SQL:
-- Repeat the same update 100 times for a fair engine comparison
DO $$
BEGIN
  FOR iteration IN 1..100 LOOP
    -- Revisit the same 1,000 rows to model a hot update workload
    UPDATE oriole_counters
    SET counter = counter + 1
    WHERE id BETWEEN 1 AND 1000;
  END LOOP;
END;
$$;

What does this workload do?

  • The DO block holds the complete workload in one query.
  • The FOR loop repeats the update 100 times.
  • Each pass increments counter for IDs 1 through 1,000.
  • The table name is the only workload detail that changes from the heap benchmark.

Each iteration touches 1,000 rows, so the editor may stay busy longer than it did for setup queries. That activity means the workload is still running.

Before you execute the query, do you expect its elapsed time to match the heap result exactly?

  • Execute the workload using the control in the SQL Editor toolbar.

The results area confirms that the query completed. The editor also shows the elapsed execution time for the workload.

  • Record the elapsed time shown by the editor as your OrioleDB execution time.

Workload query did not complete?

  • Confirm the update targets oriole_counters.
  • Compare the loop delimiters with the reference code before executing the query again.
  • Avoid repeating a completed workload because another run increases the first 1,000 counters to 200.

Help me troubleshoot my OrioleDB update workload.

Measure the OrioleDB table size

Execution time captures speed. Total relation size captures the table storage plus its indexes and any associated TOAST data.

Using the same size function from the heap benchmark keeps the storage measurement consistent.

  • Select the completed workload query in the SQL Editor.
  • Replace the selected text with this size query:
-- Measure the table and its associated storage in bytes
SELECT pg_total_relation_size('oriole_counters') AS oriole_total_bytes;

What does this size query measure?

  • The pg_total_relation_size() function measures the table and its associated storage.
  • The result is returned as a byte value.
  • The oriole_total_bytes alias gives the result column a clear name.
  • Execute the size query using the control in the SQL Editor toolbar.

You will see one row under oriole_total_bytes. That value is the total relation size for oriole_counters.

  • Record the returned byte value as your OrioleDB table size in bytes.

No table-size value?

  • Check that the quoted table name is oriole_counters.
  • Confirm the query calls pg_total_relation_size() with parentheses.

Help me troubleshoot the missing OrioleDB table-size result.

Verify the final row state

Timing alone cannot prove that both engines performed equal work. The final check confirms that the targeted rows reached 100 while every other row stayed at 0.

  • Select the table-size query in the SQL Editor.
  • Replace the selected text with this verification query:
-- Confirm the updated range and untouched range reached the expected values
SELECT
  COUNT(*) FILTER (WHERE id BETWEEN 1 AND 1000 AND counter = 100) AS updated_rows,
  COUNT(*) FILTER (WHERE id BETWEEN 1001 AND 100000 AND counter = 0) AS untouched_rows
FROM oriole_counters;

What does this verification check?

  • The first filtered count checks the 1,000 rows targeted by every update.
  • The second filtered count checks the remaining 99,000 rows.
  • Together, the counts confirm the workload changed only the intended range.

Before you execute this final check, do you expect both counts to cover all 100,000 rows?

  • Execute the verification query using the control in the SQL Editor toolbar.

You should see 1000 under updated_rows. You should see 99000 under untouched_rows.

That completes the matched workload. Both engines now carry the same update history for the rows in their benchmark tables.

Verification counts do not match?

  • Confirm the verification query reads from oriole_counters.
  • Review whether the update workload was executed more than once.
  • Avoid running the update workload again until you identify the current counter values.

Help me diagnose unexpected OrioleDB verification counts.

✔️ Awesome, I've got everything!

Your workload query, size query, and verification query match the benchmark for this step.

ⓧ I'd like to double check the full code

This is a reference copy of the three queries from this step. The first block changes the table every time it runs.

-- Repeat the same update 100 times for a fair engine comparison
DO $$
BEGIN
  FOR iteration IN 1..100 LOOP
    -- Revisit the same 1,000 rows to model a hot update workload
    UPDATE oriole_counters
    SET counter = counter + 1
    WHERE id BETWEEN 1 AND 1000;
  END LOOP;
END;
$$;

-- Measure the table and its associated storage in bytes
SELECT pg_total_relation_size('oriole_counters') AS oriole_total_bytes;

-- Confirm the updated range and untouched range reached the expected values
SELECT
  COUNT(*) FILTER (WHERE id BETWEEN 1 AND 1000 AND counter = 100) AS updated_rows,
  COUNT(*) FILTER (WHERE id BETWEEN 1001 AND 100000 AND counter = 0) AS untouched_rows
FROM oriole_counters;

Both tables now have matching update histories and recorded measurements. Next, you will inspect dead tuples and bloat to see what the heap workload left behind.

Inspect Dead Tuples and Bloat

Your benchmark in the Supabase SQL Editor now contains the same 100-iteration workload for both engines. You captured elapsed time for each table.

You also captured each table's total size. Those measurements show the visible outcome of repeated updates.

A standard PostgreSQL heap leaves old row versions for vacuum to clean. OrioleDB uses an undo log to reduce that form of bloat.

In this step, get ready to:
  • Query the PostgreSQL table statistics for live rows, dead rows, updates, and vacuum activity.
  • Compare the current relation sizes for both benchmark tables.
  • Record the observed differences for the results table you build next.
Inspect the table statistics

The n_dead_tup column is PostgreSQL's estimated number of dead rows. Its value can move when autovacuum cleans the heap table.

  • Add a statistics query below your benchmark SQL by pasting this code:
-- Read the update and vacuum estimates for both benchmark tables.
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_upd,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('heap_counters', 'oriole_counters')
ORDER BY relname;

What does this query measure?

  • The n_live_tup column estimates the number of current rows.
  • The n_dead_tup column estimates how many old row versions remain.
  • The n_tup_upd column shows cumulative updates reported by the statistics system. This confirms that both tables received write activity.
  • The last_autovacuum column shows the latest completed automatic vacuum. An empty value means the table has no recorded automatic vacuum time.
  • OrioleDB manages row history through its undo-log design. Its dead-row statistic does not represent the same physical cleanup process as a heap table.
  • Execute the statistics query in the SQL Editor.

You should see one row for heap_counters plus one row for oriole_counters. Each row shows the statistics available for that table.

  • Record the heap result as your heap dead-tuple estimate.
  • Record the OrioleDB result as your OrioleDB dead-tuple result.

Missing a statistics row?

  • Confirm that the SQL Editor still targets the orioledb-update-benchmark project.
  • Check that both relation names use lowercase letters plus the underscore shown in the query.
  • Rerun the query after the previous benchmark transaction has finished.

Help me understand why one of my benchmark tables is missing from pg_stat_user_tables.

Measure the current storage size

Dead-row estimates show one side of bloat. Total relation size shows how much storage each table currently occupies with its indexes included.

  • Add a storage query below the statistics query by pasting this code:
-- Compare the total storage used by both benchmark tables.
SELECT
  'heap_counters' AS table_name,
  pg_total_relation_size('heap_counters') AS total_bytes
UNION ALL
SELECT
  'oriole_counters' AS table_name,
  pg_total_relation_size('oriole_counters') AS total_bytes;

What does this query measure?

  • The pg_total_relation_size function includes table storage, indexes, and TOAST data.
  • The total_bytes column uses the same measurement you recorded after each update workload.
  • The table layouts differ between the two engines. A precise bloat percentage needs a page-level method designed for the table engine.
  • This query only reads storage metadata. Both counter tables keep their existing values.
  • Execute the storage query in the SQL Editor.

You should see exactly two rows. Each row returns the current total size in bytes for its benchmark table.

  • Record the heap result as your current heap size in bytes.
  • Record the OrioleDB result as your current OrioleDB size in bytes.

Seeing a size query error?

  • Check that each table name is enclosed in single quotes.
  • Confirm that both table names match the relations used by your update workloads.
  • Execute only the storage query if the SQL Editor has another statement selected.

Help me troubleshoot the relation-size query for my two benchmark tables.

✔️ Awesome, I've got everything!

That check is complete. Both inspection queries now produce the measurements needed for your comparison.

ⓧ I'd like to double check the full code

-- Read the update and vacuum estimates for both benchmark tables.
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_upd,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('heap_counters', 'oriole_counters')
ORDER BY relname;

-- Compare the total storage used by both benchmark tables.
SELECT
  'heap_counters' AS table_name,
  pg_total_relation_size('heap_counters') AS total_bytes
UNION ALL
SELECT
  'oriole_counters' AS table_name,
  pg_total_relation_size('oriole_counters') AS total_bytes;
Record the comparison

A useful benchmark separates measured values from interpretation. Your notes should preserve the observed numbers even when automatic maintenance has changed the heap statistics.

  • Add one sentence comparing the two recorded dead-tuple results.
  • Add one sentence comparing the current total sizes with the measurements recorded after each workload.

Before the final rerun, make a quick prediction about which engine will show more dead tuples or total bytes.

  • Select the statistics query in the SQL Editor.
  • Execute the selected statistics query.

You should see heap_counters plus oriole_counters in the results. The heap row should provide an estimated dead-row count plus its recorded vacuum timing.

  • Select the storage query in the SQL Editor.
  • Execute the selected storage query.

You should see one current byte value for each table. Your saved notes now contain dead-tuple evidence plus a storage-size comparison for both engines.

You have captured the evidence behind the performance difference. Next, you will save every benchmark measurement in a results table and turn the comparison into a chart.

Save and Chart the Results

The heap workload is complete. The matching OrioleDB workload is complete too.

Timing notes can disappear between reruns. Storage observations are just as easy to lose.

A PostgreSQL results table keeps the comparison inside your project. A chart makes the engine differences visible.

In this step, get ready to:
  • Store one comparison row for each engine.
  • Preserve the benchmark SQL as a reusable script.
  • Turn the saved metrics into a comparison chart.
Create the results table

Each row in benchmark_results represents one storage engine. Its primary key keeps one current row for each engine.

  • Create a blank query tab in the Supabase SQL Editor from earlier.
  • Define the results table by running this statement:
-- Store one final row for each storage engine.
create table if not exists benchmark_results (
  engine text primary key,
  elapsed_ms numeric,
  table_size_bytes bigint,
  dead_tuples bigint,
  recorded_at timestamptz
);

What does this table store?

  • The engine column identifies the storage engine.
  • The elapsed_ms column preserves the measured workload time.
  • The table_size_bytes column preserves the measured storage size.
  • The dead_tuples column preserves the observed dead-tuple value.
  • The recorded_at column timestamps the comparison.
  • Confirm the table creation by checking the result area beneath the editor.

The result area reports successful completion. Your project now has an empty benchmark_results table.

Table creation fails?

  • Check that every column definition is separated by a comma.
  • Check that the statement ends after the closing parenthesis.

Ask for help with the table definition.

Store the recorded measurements

An upsert makes this results table reusable. A future benchmark run can replace each engine's row without creating duplicates.

  • Prepare both engine rows by filling the six measurement placeholders in this statement:
-- Keep one current comparison row for each storage engine.
insert into benchmark_results (
  engine,
  elapsed_ms,
  table_size_bytes,
  dead_tuples,
  recorded_at
)
values
  ('heap', [[HEAP_ELAPSED_MS="your recorded heap elapsed time in milliseconds"]], [[HEAP_TABLE_SIZE_BYTES="your recorded heap table size in bytes"]], [[HEAP_DEAD_TUPLES="your recorded heap dead-tuple count"]], now()),
  ('orioledb', [[ORIOLE_ELAPSED_MS="your recorded OrioleDB elapsed time in milliseconds"]], [[ORIOLE_TABLE_SIZE_BYTES="your recorded OrioleDB table size in bytes"]], [[ORIOLE_DEAD_TUPLES="your recorded OrioleDB dead-tuple comparison value"]], now())
on conflict (engine) do update set
  elapsed_ms = excluded.elapsed_ms,
  table_size_bytes = excluded.table_size_bytes,
  dead_tuples = excluded.dead_tuples,
  recorded_at = excluded.recorded_at;

What does this upsert do?

  • The first value row stores your recorded heap measurements.
  • The second value row stores your recorded OrioleDB measurements.
  • The conflict clause updates an existing engine row during a future rerun.
  • The excluded values hold the newest measurements supplied by the statement.
  • Execute the completed upsert with the run control above the editor.

The result area reports successful completion. Both recorded comparisons are now stored in the database.

Before you check the table, do you expect the query to return one row or two?

  • Display the stored measurements by running this query:
-- Display the two stored records for the final comparison.
select
  engine,
  elapsed_ms,
  table_size_bytes,
  dead_tuples,
  recorded_at
from benchmark_results
order by engine;

What does this query show?

  • The selected columns expose every saved benchmark value.
  • The ordering keeps the two engine rows in a consistent sequence.

You'll see exactly two rows. One row is labeled heap.

The other row is labeled orioledb. Both rows show elapsed time plus table size plus the recorded dead-tuple comparison.

That's your benchmark captured. Both engine records now live beside the tables they describe.

Do you see fewer than two rows?

  • Confirm that all six numeric placeholders contain your recorded values.
  • Confirm that both value rows remain inside the same insert statement.
  • Run the completed upsert again before repeating the select query.

Help me restore the two comparison rows.

Create the comparison chart

These metrics use different units. Normalizing each engine against the larger value keeps every comparison visible on one chart.

Before you run the chart query, which engine do you expect to reach 100 for each metric?

  • Create six chart-ready rows by running this query:
-- Reshape raw values so one chart can compare unlike units.
with measurements as (
  select engine, 'elapsed_ms' as metric, elapsed_ms::numeric as value
  from benchmark_results
  union all
  select engine, 'table_size_bytes', table_size_bytes::numeric
  from benchmark_results
  union all
  select engine, 'dead_tuples', dead_tuples::numeric
  from benchmark_results
),
normalized as (
  select
    engine,
    metric,
    value,
    max(value) over (partition by metric) as metric_max
  from measurements
)
select
  engine || ' | ' || metric as comparison,
  value as recorded_value,
  case
    when metric_max = 0 then 0
    else round(value * 100 / metric_max, 2)
  end as relative_percent
from normalized
order by metric, engine;

How does the chart query work?

  • The measurements section reshapes the three saved metrics into six rows.
  • The recorded_value column keeps each exact measurement visible.
  • The relative_percent column sets the larger engine value for each metric to 100.
  • A metric containing only zero values remains at 0.

The result grid shows six rows. Each row pairs one engine with one metric.

The chart query finishes the comparison logic. One final save makes the whole benchmark repeatable.

What belongs in the reusable script?

  • The table creation statements for heap_counters plus oriole_counters.
  • The seeding statements for both 100000-row tables.
  • The identical 100-iteration workload for each engine.
  • The timing plus size plus statistics queries from the earlier steps.
  • The results-table upsert plus the chart query from this step.
  • Assemble every benchmark statement into one SQL Editor query in its original execution order.
  • Save the assembled query as orioledb-update-benchmark with the SQL Editor's save control.

The named snippet now appears in your saved query list. You can reuse it when you want to benchmark another run.

Before the final run, which six engine-metric labels do you expect the saved chart query to return?

  • Select only the final chart query inside the saved snippet.
  • Execute the selection with the run control above the editor.
  • Switch the query results to chart view.
  • Choose comparison as the horizontal axis.
  • Choose relative_percent as the vertical axis.
  • Choose a bar chart layout.

You'll see six labeled bars. For each nonzero metric, the larger engine value reaches 100.

You have turned the benchmark into something you can read at a glance. The exact measurements remain available in the result grid.

Chart missing bars?

  • Confirm that the chart query returns six result rows before opening chart view.
  • Confirm that the horizontal axis uses comparison.
  • Confirm that the vertical axis uses relative_percent.

Help me fix the benchmark chart.

Secret mission

Build a Normalized Performance Scorecard

The scorecard expresses elapsed time as a ratio to the fastest run. It expresses table size as a ratio to the smallest table. A final column exposes extra dead tuples.

Clean Up Your Resources

Clean Up Your Resources

Your benchmark can stay active, be paused for later, or be deleted entirely. The Supabase Free plan keeps this project's main path at $0.

Resources you used:

  • One Supabase project named orioledb-update-benchmark. It contains the OrioleDB extension.
  • Two counter tables named heap_counters and oriole_counters.
  • One results table named benchmark_results.

Keep everything running

No action is needed. Choose this option while you are still rerunning the benchmark.

  • Keep orioledb-update-benchmark active in your Supabase Dashboard.
  • Keep the reusable SQL benchmark script with your recorded measurements.
  • Keep the comparison chart as a record of the heap and OrioleDB results.

Your tables remain available for another run. The project stays on the Free plan at $0.

Pause - I'll come back to this later

Pause the project to stop active database access while preserving the benchmark setup for a later rerun.

  • Return from the SQL Editor to the project overview for orioledb-update-benchmark.
  • Select the settings area in the project sidebar.
  • Choose the general project controls.
  • Scroll to the section for pausing the project.
  • Select the control that pauses the project.
  • Confirm the pause in the dialog.

You'll see the project status change to paused. The database stays unavailable until you restore the project.

Delete - I don't want to use this again

Remove the Supabase project when you have finished comparing the engines. This permanently removes every database object inside it.

Permanent deletion can feel like a big step. Your retained benchmark SQL gives you a clear route back to this test.

  • Save the reusable SQL benchmark script outside the Supabase project.
  • Save the recorded comparison results outside the Supabase project.
  • Return from the SQL Editor to the project overview for orioledb-update-benchmark.
  • Select the settings area in the project sidebar.
  • Choose the general project controls.
  • Scroll to the project deletion controls.
  • Select the control that permanently deletes the project.
  • Confirm the deletion using the details requested in the dialog.

You'll no longer see orioledb-update-benchmark in your dashboard project list. This confirms that the extension plus every benchmark table has been removed.

Nice Work!

Nice Work!

Benchmark complete! You built a repeatable storage test in Supabase that compares PostgreSQL heap storage with OrioleDB through measured results.

You've learned how to:

  • Create matching 100,000-row counter tables on PostgreSQL heap storage plus OrioleDB storage.
  • Run an identical 100-iteration update workload against each table to produce a fair engine comparison.
  • Record elapsed time, table size, plus dead tuples in benchmark_results. Turn those measurements into a comparison chart.
  • Secret Mission: Extend this benchmark through an optional challenge that pushes your measurement skills further.

Ready to quiz yourself?