Load Test and Optimize Assessly for Scale

Write k6 load tests and optimize Supabase for exam-day burst traffic.

Introduction

30 Second Summary

Imagine 500 students all hitting "submit" at the exact same moment during a final exam. For most apps, that's the moment everything falls apart. Timeouts, database errors, and panicked emails from instructors.

In this project, you will write load test scripts that simulate real exam-day traffic for Assessly, discover exactly where your system breaks under pressure, and apply proven optimizations so it handles burst traffic gracefully.

What You'll Build

A complete performance engineering workflow that uses k6 load tests to simulate burst traffic, identifies bottlenecks through Supabase monitoring tools, and applies targeted database and connection optimizations.

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

  • A reusable k6 load testing suite that simulates realistic exam-day burst traffic with hundreds of virtual users.
  • Identified and documented performance bottlenecks using load test data, pg_stat_statements, and Supabase monitoring dashboards.
  • Optimized connection pooling and database indexes with measured before-and-after performance comparisons.
  • Secret Mission: Build a pre-warming strategy that prepares your infrastructure before known peak times like exam periods.

Are there any prerequisites?

This project assumes you already have Assessly deployed on Vercel with a Supabase backend. You should be comfortable with Node.js and basic SQL.

Install k6 and Verify Your Setup

When hundreds of students hit "submit" at the same time during an exam, you need to know how your system responds. Load testing is how you find out before your users do.

In this step, you'll install k6 (a load testing tool from Grafana) and set up a dedicated project folder for your Assessly load tests.

In this step, get ready to:
  • Install k6 on your machine.
  • Create a load testing project folder with a template script.
  • Run the sample test to confirm k6 outputs metrics correctly.
Install k6 on Windows

k6 is a free, open-source load testing tool that lets you write test scripts in JavaScript. You'll use it to simulate realistic traffic patterns against your Assessly deployment.

  • Press the Windows key to open your search bar.
  • Type cmd and press Enter to open Command Prompt.
  • Install k6 using the Windows Package Manager by running this command:
winget install k6 --source winget

What does this command do?

winget is the Windows Package Manager. It downloads and installs k6 from the official winget source, handling all the setup for you.

  • Verify the installation by running:
k6 version

✔️ I see a version number

k6 is installed and ready to go. You should see output like k6 v1.7.1 (your version number may differ). Move on to the next substep.

ⓧ Command not found

If your terminal doesn't recognize the k6 command, try closing and reopening Command Prompt. The winget installer needs a fresh terminal session to update your PATH.

If it still doesn't work after reopening, try running the install command again to make sure it completed successfully.

Create your load testing project folder

You'll keep your load test scripts in a dedicated folder separate from your Assessly application code. This keeps your testing concerns organized.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir assessly-load-tests
cd assessly-load-tests
  • Initialize a k6 script template by running:
k6 new

What does k6 new do?

k6 new generates a starter script.js file in the current directory. The default "minimal" template includes a basic HTTP request, a sleep() call, and an options block with virtual users (VUs) and duration configured.

You'll replace this template with your custom Assessly load test script in the next step.

  • Confirm the file was created by running:
ls

You should see script.js listed in the output.

Don't see script.js?

Make sure you ran k6 new from inside the assessly-load-tests folder. Run cd to check your current directory, then try k6 new again.

help me find where k6 created my script.js file

Run the sample test to confirm everything works

Before writing your custom Assessly test, you'll run the generated template to confirm k6 executes tests and reports metrics correctly on your machine.

  • Run the generated script by executing:
k6 run script.js

What should I see?

k6 displays a logo and progress bar in real-time as the test runs. When the test finishes (after about 30 seconds), you'll see an end-of-test summary with metrics grouped under HTTP, EXECUTION, and NETWORK headings.

The key metrics to look for are http_req_duration (how long each request took), http_reqs (total requests made), and iterations (how many times the test function ran).

  • Wait for the test to complete. You should see output including p(90) and p(95) response time percentiles, request counts, and data transfer statistics.

Seeing connection errors?

The default script hits https://test.k6.io which requires an internet connection. Make sure you're connected to the internet.

If you see a firewall prompt, allow k6 to access the network.

help me troubleshoot my k6 test run errors

✔️ Awesome, I've got everything!

Great. Double check you've seen the metrics output with http_req_duration, http_reqs, and iterations in the summary.

ⓧ I'd like to double check the full code

Here's what your script.js should look like after running k6 new:

// Default k6 template generated by 'k6 new'
import http from 'k6/http';
import { sleep } from 'k6';

export const options = {
  vus: 10,
  duration: '30s',
};

export default function () {
  http.get('https://test.k6.io');
  sleep(1);
}

Your load testing environment is ready. Next up, you'll replace this template with a custom test script that simulates real Assessly exam-day traffic patterns.

Write Your Assessly Burst Traffic Test Script

Your k6 installation is confirmed and ready to run tests. Now it's time to write a script that actually simulates what happens to Assessly on exam day.

When hundreds of students submit code at the exact same moment, your system experiences a traffic spike. In this step, you'll design a k6 test script that recreates that burst pattern and defines what "acceptable performance" means for Assessly.

In this step, get ready to:
  • Configure a staged traffic ramp that simulates exam-day burst submissions.
  • Add HTTP requests that mirror the real Assessly student flow.
  • Define checks and custom metrics that measure submission-specific performance.
Design the traffic pattern

The template script.js from k6 new sends a single GET request to a test server. You need to replace it with a staged ramp-up pattern that simulates exam-day traffic hitting Assessly.

  • In your code editor, open script.js inside the assessly-load-tests folder.
  • Select all existing content and delete it.
  • Replace it with the following imports, custom metrics, and test configuration:
import http from 'k6/http';
import { check, group, sleep } from 'k6';
import { Trend, Rate } from 'k6/metrics';

// Custom metrics
const submissionDuration = new Trend('submission_duration');
const submissionFailRate = new Rate('submission_failures');

// Test configuration - simulates exam-day burst traffic
export const options = {
  stages: [
    { duration: '30s', target: 100 },   // Ramp up to 100 users
    { duration: '1m', target: 100 },     // Hold at 100 (normal load)
    { duration: '10s', target: 500 },    // Spike to 500 (mass submission)
    { duration: '30s', target: 500 },    // Hold at 500 (sustained burst)
    { duration: '20s', target: 0 },      // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'],     // 95% of requests under 500ms
    http_req_failed: ['rate<0.01'],       // Less than 1% error rate
    submission_duration: ['p(95)<2000'],  // Submissions complete under 2s
  },
};

const BASE_URL = __ENV.BASE_URL || 'https://assessly.in';

What does this code do?

The stages array defines how many virtual users (VUs) k6 simulates over time. Each stage ramps to a target number of VUs over a duration.

  • The spike from 100 to 500 VUs in just 10 seconds recreates the moment an exam timer starts and everyone hits "submit" simultaneously.
  • thresholds define pass/fail criteria. If 95% of requests take longer than 500ms, k6 reports the test as failed.
  • Trend tracks timing distributions (like submission duration), while Rate tracks the percentage of failures.

You won't see a change yet. The next substep adds the test function that uses this configuration.

Add realistic Assessly API requests

k6 needs a default export function that describes what each virtual user does. You'll model the three-step journey a student takes: loading the assessment, submitting code, and checking results.

  • Add the following default function below the BASE_URL line in script.js:
export default function () {
  group('Student Assessment Flow', function () {

    // Step 1: Load assessment page
    const assessmentRes = http.get(`${BASE_URL}/api/assessments`, {
      headers: { 'Content-Type': 'application/json' },
    });
    check(assessmentRes, {
      'assessment list loaded': (r) => r.status === 200,
    });
    sleep(1);

    // Step 2: Submit code solution
    const startTime = new Date();
    const submissionRes = http.post(
      `${BASE_URL}/api/submissions`,
      JSON.stringify({
        code: 'function twoSum(nums, target) { const map = new Map(); for (let i = 0; i < nums.length; i++) { const comp = target - nums[i]; if (map.has(comp)) return [map.get(comp), i]; map.set(nums[i], i); } }',
        language: 'javascript',
        assessment_id: 'load-test-assessment',
      }),
      {
        headers: { 'Content-Type': 'application/json' },
      }
    );
    const endTime = new Date();
    submissionDuration.add(endTime - startTime);

    const submissionSuccess = check(submissionRes, {
      'submission accepted': (r) => r.status === 200 || r.status === 201,
    });
    submissionFailRate.add(!submissionSuccess);
    sleep(2);

    // Step 3: Check submission results
    const resultsRes = http.get(`${BASE_URL}/api/submissions/results`, {
      headers: { 'Content-Type': 'application/json' },
    });
    check(resultsRes, {
      'results retrieved': (r) => r.status === 200,
    });
    sleep(1);
  });
}

What does this code do?

  • group('Student Assessment Flow', ...) organizes all requests into one logical user journey. k6 reports metrics for each group separately.
  • The three HTTP requests mirror what a real student does: load assessments, submit code, then check results.
  • sleep() calls between requests simulate realistic "think time." Without them, k6 would fire requests back-to-back, which no real user does.
  • check() verifies each response has the correct status code. Failed checks are tracked in k6's output so you can spot broken endpoints.
  • Save script.js.
  • Run the test to confirm it executes without errors:
k6 run script.js
  • You should see k6 output showing real-time progress with VU counts ramping up, followed by an end-of-test summary with metrics like http_req_duration, http_reqs, and your custom submission_duration metric.

Seeing errors or the test won't start?

  • If you see a syntax error, double check that all parentheses, braces, and backticks are balanced. Template literals use backticks (`) not single quotes (').
  • If you see HTTP errors (like 404 or connection refused), that's expected. Your Assessly endpoints may return errors under load testing conditions. The script still runs correctly.
  • If the test won't start at all, make sure you're running the command from inside the assessly-load-tests folder where script.js lives.

Why won't my k6 script run?

Add checks and custom metrics

Your script already includes checks and custom metrics woven into the request flow. Let's walk through how they work together to give you Assessly-specific performance data beyond generic HTTP timing.

How do the custom metrics track submission performance?

The submissionDuration Trend metric measures the full round-trip time from submitting code to receiving a response. It uses new Date() timestamps before and after the POST request, then records the difference with submissionDuration.add(endTime - startTime).

The submissionFailRate Rate metric tracks what percentage of submissions fail. It inverts the check() result with submissionFailRate.add(!submissionSuccess). If check() passes, it records 0 (no failure). If it fails, it records 1.

Together, these give you a clear answer: "How long do submissions take under load, and how many break?" The submission_duration threshold (p(95)<2000) means 95% of submissions must complete in under 2 seconds for the test to pass.

Why track these separately from http_req_duration?

k6's built-in http_req_duration averages ALL requests together. A fast GET to /api/assessments would mask a slow POST to /api/submissions. Custom metrics let you isolate the expensive operation (code evaluation) from lightweight reads.

✔️ Awesome, I've got everything!

Great. Double check you've saved script.js and that your k6 test ran without script-level errors. HTTP errors from the target server are fine at this stage.

ⓧ I'd like to double check the full code

Here's the complete script.js file. Make sure yours matches exactly:

import http from 'k6/http';
import { check, group, sleep } from 'k6';
import { Trend, Rate } from 'k6/metrics';

// Custom metrics
const submissionDuration = new Trend('submission_duration');
const submissionFailRate = new Rate('submission_failures');

// Test configuration - simulates exam-day burst traffic
export const options = {
  stages: [
    { duration: '30s', target: 100 },   // Ramp up to 100 users
    { duration: '1m', target: 100 },     // Hold at 100 (normal load)
    { duration: '10s', target: 500 },    // Spike to 500 (mass submission)
    { duration: '30s', target: 500 },    // Hold at 500 (sustained burst)
    { duration: '20s', target: 0 },      // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'],     // 95% of requests under 500ms
    http_req_failed: ['rate<0.01'],       // Less than 1% error rate
    submission_duration: ['p(95)<2000'],  // Submissions complete under 2s
  },
};

const BASE_URL = __ENV.BASE_URL || 'https://assessly.in';

export default function () {
  group('Student Assessment Flow', function () {

    // Step 1: Load assessment page
    const assessmentRes = http.get(`${BASE_URL}/api/assessments`, {
      headers: { 'Content-Type': 'application/json' },
    });
    check(assessmentRes, {
      'assessment list loaded': (r) => r.status === 200,
    });
    sleep(1);

    // Step 2: Submit code solution
    const startTime = new Date();
    const submissionRes = http.post(
      `${BASE_URL}/api/submissions`,
      JSON.stringify({
        code: 'function twoSum(nums, target) { const map = new Map(); for (let i = 0; i < nums.length; i++) { const comp = target - nums[i]; if (map.has(comp)) return [map.get(comp), i]; map.set(nums[i], i); } }',
        language: 'javascript',
        assessment_id: 'load-test-assessment',
      }),
      {
        headers: { 'Content-Type': 'application/json' },
      }
    );
    const endTime = new Date();
    submissionDuration.add(endTime - startTime);

    const submissionSuccess = check(submissionRes, {
      'submission accepted': (r) => r.status === 200 || r.status === 201,
    });
    submissionFailRate.add(!submissionSuccess);
    sleep(2);

    // Step 3: Check submission results
    const resultsRes = http.get(`${BASE_URL}/api/submissions/results`, {
      headers: { 'Content-Type': 'application/json' },
    });
    check(resultsRes, {
      'results retrieved': (r) => r.status === 200,
    });
    sleep(1);
  });
}

Your burst traffic script is ready. Next up, you'll run a proper baseline test and use Supabase's monitoring tools to see exactly where Assessly breaks under that pressure.

Run Baseline Tests and Identify Bottlenecks

Your load test script is ready to simulate exam-day traffic against Assessly. Now you need to actually run it, watch what happens under pressure, and find the weak spots.

This step is where you shift from writing test code to collecting real performance data. You'll run the test, monitor your Supabase database connections in real-time, and use built-in PostgreSQL tooling to pinpoint which queries are dragging down your response times.

In this step, get ready to:
  • Run your first baseline load test and record key metrics.
  • Monitor the Supabase Dashboard during the test to spot connection saturation.
  • Query pg_stat_statements to identify your slowest database queries.
Run your first baseline load test

A baseline test gives you a snapshot of how Assessly performs right now, before any optimizations. Without this "before" measurement, you have no way to prove your optimizations actually helped.

  • Run the load test against your deployed Assessly instance by running this command:
k6 run script.js

What does this command do?

This executes your script.js test locally. k6 will ramp from 0 to 100 virtual users, hold steady, then spike to 500 VUs to simulate a mass submission event.

The entire test takes about 2 minutes and 30 seconds to complete. Watch the real-time output in your terminal as it runs.

  • Watch the terminal output as the test progresses. You'll see metrics updating in real-time: request counts, error rates, and response time percentiles.
  • When the test finishes, k6 prints a summary. Note down these four values from the output:

What should I see?

At the end of the test, k6 displays a table of metrics. You'll see whether your thresholds passed or failed. A green checkmark means the threshold passed. A red cross means it was breached.

If you see threshold failures at this stage, that's expected. This is your baseline showing where the system struggles under load.

Save your baseline for later comparison

Copy the full summary output and paste it into a text file or note. You'll compare this against your optimized results in later steps.

Seeing connection timeouts or 0 requests?

Make sure your Assessly deployment is running and accessible at the URL configured in your script. If you set a custom BASE_URL environment variable, verify it points to your live Vercel deployment.

If all requests fail immediately, check that the API routes (/api/assessments, /api/submissions) exist in your deployed Assessly app.

help me debug why my k6 test is returning zero successful requests

Monitor Supabase during the test

Running the test is only half the picture. You also need to see what's happening on the database side while the load is hitting.

  • Open the Supabase Dashboard in your browser and navigate to your project.
  • In the left sidebar, click Reports under the Database section.
  • Run the k6 test again in a separate terminal window so you can watch the Dashboard simultaneously:
k6 run script.js
  • While the test is running, watch the Database connections chart on the Reports page. Look for the connection count climbing toward the maximum limit.
  • Also check the Shared Pooler (Supavisor) Client Connections chart to see how many client connections are hitting your pooler.
  • After the test finishes, switch to your Vercel Dashboard and check your function logs. Look for any errors mentioning connection slots being reserved or connection pool exhaustion.

What am I looking for?

The key signal is whether connections are maxing out during the burst phase (when VUs spike to 500). If the connection chart flatlines at the maximum, your serverless functions are exhausting the pool.

Connection exhaustion is the most common bottleneck for Vercel + Supabase apps because each serverless function invocation opens a new connection. This is what you'll fix in the next step with Supavisor transaction mode pooling.

Charts not updating during the test?

The Reports page refreshes on an interval rather than in real-time. You may need to manually refresh the page or wait a few seconds for charts to reflect current activity.

help me understand why my Supabase Dashboard isn't showing connection activity during my load test

Identify slow queries with pg_stat_statements

Now that you've seen the connection behavior, it's time to find which specific database queries are slowest. pg_stat_statements is a PostgreSQL extension that tracks execution statistics for every query that runs against your database.

  • In the Supabase Dashboard, click SQL Editor in the left sidebar.
  • Find your slowest queries by total execution time by running this query:
-- Find the slowest queries by total execution time
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

What does this query do?

This pulls the top 10 most expensive queries from pg_stat_statements, sorted by cumulative execution time. The total_exec_time column shows milliseconds spent across all invocations of that query.

The mean_exec_time column shows the average time per call. A query with high total time but low mean time is called frequently. A query with high mean time is slow per execution.

  • Now sort by mean execution time to find queries that are individually slow:
-- Find queries with the highest average execution time
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
  • Look at the query column in both result sets. Note which tables and columns appear in the WHERE clauses, JOIN conditions, and ORDER BY clauses of your slowest queries.
  • Write down the table names and column names involved. These are your indexing targets for Step 5.

Why these columns matter

Columns in WHERE clauses are filtered on every query execution. Without an index, PostgreSQL scans the entire table (a sequential scan) to find matching rows.

Columns in JOIN and ORDER BY clauses also benefit from indexes. You'll create targeted indexes on these columns in Step 5 to eliminate the sequential scans.

Getting an empty result from pg_stat_statements?

If the query returns no rows, the extension may need time to accumulate data. Run your k6 test first, then query pg_stat_statements again after the test finishes. The load test generates enough traffic to populate the statistics.

help me troubleshoot why pg_stat_statements returns empty results in my Supabase project

You now have a clear performance baseline and a hit list of slow queries. Next up, you'll optimize Supabase connection pooling to fix the connection exhaustion problem you observed.

Optimize Supabase Connection Pooling for Vercel

Your baseline test revealed where Assessly starts to struggle under load. One of the most common bottlenecks for Vercel serverless functions talking to Supabase is connection exhaustion. Every serverless function invocation creates a new database connection, and under burst traffic, those connections pile up fast.

The fix is connection pooling. In this step, you'll verify that your app uses Supavisor in transaction mode, tune the pool size for serverless workloads, and re-run your load test to measure the difference.

In this step, get ready to:
  • Verify your Vercel connection string uses Supavisor transaction mode.
  • Tune the Supabase connection pool size for serverless.
  • Re-run your k6 load test and compare results against the baseline.
Verify you're using Supavisor transaction mode

Vercel does not support IPv6. This means your app must connect to Supabase through Supavisor, the shared connection pooler that provides IPv4 addresses. Specifically, you need transaction mode on port 6543.

Transaction mode is ideal for serverless because it shares database connections across short-lived function invocations instead of dedicating one connection per function.

  • Open the Vercel dashboard and select your Assessly project.
  • Click Settings in the left sidebar.
  • Select Environment Variables from the left navigation.
  • Search for your DATABASE_URL variable in the list.
  • Click the three dots to the right of the variable name to reveal its value.

Your DATABASE_URL should follow this format:

postgres://postgres.[PROJECT-REF]:[PASSWORD]@aws-[REGION].pooler.supabase.com:6543/postgres

What am I looking for?

The two critical parts are the hostname ending in pooler.supabase.com (confirming you're using Supavisor) and port 6543 (confirming transaction mode).

If your connection string points to port 5432 instead, that's session mode. You'll need to update the variable to use port 6543 for transaction mode.

Why transaction mode for serverless?

In session mode, a database connection stays open for the entire session. Serverless functions are short-lived, so each invocation opens a new session and holds a connection until the function terminates. Under burst traffic, you quickly exhaust all available connections.

Transaction mode releases the connection back to the pool after each transaction completes. This means hundreds of serverless invocations can share a small number of actual database connections.

The Vercel-Supabase integration

If you used the official Vercel-Supabase integration, your environment variable may be named POSTGRES_URL instead of DATABASE_URL. Check both. The integration automatically configures the pooler connection string pointing to Supavisor.

Connection string doesn't point to the pooler?

If your connection string points directly to db.[PROJECT-REF].supabase.co instead of pooler.supabase.com, you're using a direct connection. Open the Supabase Dashboard, click the Connect button at the top of the page, and copy the Transaction pooler connection string. Update your Vercel environment variable with this string.

Remember to redeploy your Vercel project after changing any environment variable.

Still stuck? Help me fix my Supabase connection string for transaction mode pooling on Vercel.

Tune the pool size

Now that you've confirmed transaction mode is active, you need to right-size the pool. The pool size controls how many database connections Supavisor can open to Postgres on your behalf.

  • Open the Supabase Dashboard and select your Assessly project.
  • Navigate to Database in the left sidebar, then select Configuration.
  • Find the Connection pooling section.
  • If you're heavily using the Supabase Data API (PostgREST), keep the pool size at around 40% of your Database Max Connections.
  • If you're primarily using the pooler for direct database queries from serverless functions, you can commit up to 80% to the pool.
  • Click Save to apply your pool size change.

Why leave headroom?

Supabase services like Auth, Storage, and Realtime also consume database connections from the same pool of max connections. If you allocate 100% to Supavisor, those services will fail to connect.

The 40-80% range leaves adequate room for the Authentication server and other utilities while still giving your application ample pooled connections for burst traffic.

Next, you need to limit how many connections each serverless function invocation requests from the pool.

  • Open your Assessly application code where you configure the database client (typically in your Prisma schema, Drizzle config, or direct Postgres client setup).
  • Set connection_limit=1 per function invocation.
  • Save the file and push to trigger a Vercel redeployment.

Why connection_limit=1?

In serverless setups, each function invocation is short-lived. It only needs one connection to execute its query and return. If each invocation requests multiple connections, you exhaust the pool under burst traffic. Starting with connection_limit=1 and increasing cautiously is the recommended approach.

Not sure where to set connection_limit?

The exact location depends on your database client library. For Prisma, append ?connection_limit=1 to the end of your DATABASE_URL in your environment variables. For other clients, check the connection configuration options in your client's documentation.

Need help? Help me configure connection_limit=1 for my Supabase database client in a Vercel serverless environment.

Re-run the load test and compare

With connection pooling properly configured, it's time to see if it made a difference. You'll run the exact same k6 script as before so the comparison is apples-to-apples.

  • Return to your terminal in the assessly-load-tests folder.
  • Run your load test again by executing this command:
k6 run script.js

What should I compare?

Look at these metrics side-by-side with your baseline results:

  • p95 response time (http_req_duration): did latency improve?
  • Error rate (http_req_failed): did connection exhaustion errors disappear?
  • Throughput (http_reqs): did more requests succeed overall?
  • Submission duration (submission_duration): did the custom metric improve?
  • Save the summary output from this run. Label it as your "post-pooling" results.
  • Compare p95 latency, error rate, and throughput against your baseline from the previous step.

Connection pooling primarily helps with connection exhaustion errors. If your baseline showed errors related to running out of database connections, you should see a dramatic improvement in error rate. Latency improvements may be modest if your bottleneck is query speed rather than connection availability.

Results look the same as the baseline?

If you don't see improvement, check that your Vercel project actually redeployed with the updated connection string. Environment variable changes only take effect on new deployments. Verify by checking the deployment timestamp in your Vercel dashboard.

Also confirm you're testing against the production URL, not a preview deployment that might still use old settings.

Still no change? Help me figure out why my connection pooling optimization didn't improve my k6 load test results.

You now have a documented before-and-after comparison for connection pooling. Next up, you'll tackle the other half of the equation: making individual queries faster with targeted database indexes.

Optimize Database Queries with Targeted Indexes

Your connection pooling is tuned, but the queries themselves might still be slow. Connection pooling solves how many connections hit the database. Indexing solves how fast those queries execute once they arrive.

In this step, you'll use EXPLAIN ANALYZE to look inside your slowest queries, create targeted indexes to eliminate sequential scans, and then run a final load test to measure the full impact of all your optimizations combined.

In this step, get ready to:
  • Analyze slow queries with EXPLAIN ANALYZE to identify sequential scans.
  • Create targeted indexes (B-tree, BRIN, and partial) to eliminate those scans.
  • Run a final load test and compare all three performance runs.
Analyze your slow queries with EXPLAIN

In the previous step, pg_stat_statements told you which queries are slow. Now EXPLAIN ANALYZE tells you why they're slow by showing the exact execution plan Postgres uses.

  • Return to the Supabase SQL Editor in your browser.
  • Take the slowest query you identified from pg_stat_statements and run it with EXPLAIN ANALYZE by pasting this pattern into the editor:
-- Replace with your actual slow query from pg_stat_statements
explain analyze SELECT * FROM submissions WHERE assessment_id = 'some-id' ORDER BY created_at DESC;

What does this output show?

EXPLAIN ANALYZE runs the query and shows the actual execution plan. Look for nodes labelled Seq Scan on large tables. A sequential scan reads every row in the table, which gets slower as your data grows.

The output includes cost estimates, actual time in milliseconds, and row counts. When the actual time is high on a Seq Scan node, that's your prime indexing candidate.

  • Run EXPLAIN ANALYZE on each of your slowest queries from pg_stat_statements.
  • Note which tables and columns appear in Seq Scan nodes. These are the columns you'll index next.

Be careful with write queries

EXPLAIN ANALYZE actually executes the query. Avoid running it on INSERT, UPDATE, or DELETE queries unless you wrap them in a transaction you roll back. For SELECT queries, it's safe to run directly.

Create targeted indexes

Now that you know which columns cause sequential scans, you'll create indexes that let Postgres jump directly to the rows it needs instead of scanning the entire table.

  • In the Supabase SQL Editor, create indexes on the columns used in WHERE clauses, JOIN conditions, and ORDER BY for your most frequent queries by running SQL like this:
-- Create an index without locking writes on your table
-- Replace table_name and column_name with your actual values
CREATE INDEX CONCURRENTLY idx_submissions_assessment_id
ON submissions (assessment_id);

Why CONCURRENTLY?

CREATE INDEX CONCURRENTLY builds the index without locking your table for writes. A regular CREATE INDEX locks the table, which would block your app from inserting new submissions while the index builds. The tradeoff is that concurrent index creation takes longer.

  • For timestamp columns like created_at on your submissions table, create a BRIN index instead of a regular B-tree:
-- BRIN indexes are 10x smaller than B-tree for monotonically increasing values
CREATE INDEX CONCURRENTLY idx_submissions_created_at_brin
ON submissions USING brin (created_at);

What is a BRIN index?

BRIN stands for Block Range Index. It stores summary information about ranges of physical table blocks rather than indexing every individual row. For columns that always increase (like timestamps), BRIN indexes are roughly 10x smaller than equivalent B-tree indexes while still dramatically reducing scan times.

  • If you frequently filter by a specific status value, create a partial index that only covers those rows:
-- Partial index only covers rows matching the WHERE condition
-- Much smaller and faster than indexing the entire table
CREATE INDEX CONCURRENTLY idx_submissions_pending
ON submissions (assessment_id, created_at)
WHERE status = 'pending';

Why use a partial index?

A partial index only indexes the subset of rows that match a condition. If most of your queries filter for status = 'pending', a partial index targeting that subset is smaller and faster than indexing every row regardless of status.

  • After creating your indexes, verify they were created successfully by running:
-- List all indexes on your submissions table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'submissions';
  • You should see your new indexes listed in the results.

Index creation failed?

If you see an error about a concurrent index build failing, run DROP INDEX CONCURRENTLY on the failed index and try again. Failed concurrent builds leave behind invalid indexes that need to be cleaned up before retrying.

help me fix my CREATE INDEX CONCURRENTLY error in Supabase

Run final load test and measure total improvement

With your indexes in place, it's time to run the same burst traffic test one final time and compare all three runs side by side.

  • Run the k6 script one more time by running this command in your terminal:
k6 run script.js
  • Watch the real-time output for http_req_duration (p95), http_req_failed rate, and http_reqs throughput.
  • Compare all three runs you've completed:
  • Run 1 (baseline): your original performance before any optimizations.
  • Run 2 (post-pooling): after configuring Supavisor transaction mode and pool sizing.
  • Run 3 (post-indexing): after creating targeted indexes on your slowest queries.
  • Document your p95 latency improvement, error rate reduction, and throughput increase across the three runs.

What should I compare?

Focus on three key metrics across your runs. The p95 response time tells you how fast 95% of requests complete. The error rate shows how many requests failed under load. The http_reqs count shows total throughput (requests per second).

Connection pooling primarily reduces errors from connection exhaustion. Indexing primarily reduces latency. Together they give you a resilient system that handles burst traffic gracefully.

You now have a quantified scaling playbook for Assessly. The last piece is a warm-up script you can run before known traffic spikes to prepare the connection pool.

  • Create a new file called warmup.js in your assessly-load-tests folder by running:
k6 new warmup.js
  • Replace the contents of warmup.js with the following pre-warming script:
import http from 'k6/http';
import { check, sleep } from 'k6';

// Pre-warming script - run 5 minutes before expected peak
// Gradually establishes connections so the pool is hot when students arrive
export const options = {
  stages: [
    { duration: '1m', target: 10 },    // Gentle ramp
    { duration: '2m', target: 50 },    // Moderate warming
    { duration: '2m', target: 100 },   // Full warm-up
  ],
  thresholds: {
    http_req_failed: ['rate<0.05'],
  },
};

const BASE_URL = __ENV.BASE_URL || 'https://assessly.in';

export default function () {
  // Hit lightweight endpoints to warm the connection pool
  const res = http.get(`${BASE_URL}/api/health`);
  check(res, {
    'health check passed': (r) => r.status === 200,
  });
  sleep(0.5);
}

What does this script do?

The warm-up script gradually ramps virtual users (10, then 50, then 100) over 5 minutes, hitting a lightweight health endpoint. This forces the connection pool to establish and hold connections before the real traffic spike arrives.

You'd run this 5 minutes before a known exam start time. By the time students hit "submit", the pool is already hot and ready to serve requests without the cold-start latency of establishing new connections.

  • Save warmup.js.
  • Confirm the file exists by running:
ls
  • You should see both script.js and warmup.js listed in the output.

Don't see warmup.js?

Make sure you saved the file in the same assessly-load-tests directory where script.js lives. Run pwd to confirm your current directory.

help me find where my warmup.js file was saved

✔️ Awesome, I've got everything!

Great. Make sure both files are saved in your assessly-load-tests folder.

ⓧ I'd like to double check the full code

Here's the complete warmup.js file:

import http from 'k6/http';
import { check, sleep } from 'k6';

// Pre-warming script - run 5 minutes before expected peak
// Gradually establishes connections so the pool is hot when students arrive
export const options = {
  stages: [
    { duration: '1m', target: 10 },    // Gentle ramp
    { duration: '2m', target: 50 },    // Moderate warming
    { duration: '2m', target: 100 },   // Full warm-up
  ],
  thresholds: {
    http_req_failed: ['rate<0.05'],
  },
};

const BASE_URL = __ENV.BASE_URL || 'https://assessly.in';

export default function () {
  // Hit lightweight endpoints to warm the connection pool
  const res = http.get(`${BASE_URL}/api/health`);
  check(res, {
    'health check passed': (r) => r.status === 200,
  });
  sleep(0.5);
}

And here's the complete script.js (unchanged from Step 2):

import http from 'k6/http';
import { check, group, sleep } from 'k6';
import { Trend, Rate } from 'k6/metrics';

// Custom metrics
const submissionDuration = new Trend('submission_duration');
const submissionFailRate = new Rate('submission_failures');

// Test configuration - simulates exam-day burst traffic
export const options = {
  stages: [
    { duration: '30s', target: 100 },   // Ramp up to 100 users
    { duration: '1m', target: 100 },     // Hold at 100 (normal load)
    { duration: '10s', target: 500 },    // Spike to 500 (mass submission)
    { duration: '30s', target: 500 },    // Hold at 500 (sustained burst)
    { duration: '20s', target: 0 },      // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'],     // 95% of requests under 500ms
    http_req_failed: ['rate<0.01'],       // Less than 1% error rate
    submission_duration: ['p(95)<2000'],  // Submissions complete under 2s
  },
};

const BASE_URL = __ENV.BASE_URL || 'https://assessly.in';

export default function () {
  group('Student Assessment Flow', function () {

    // Step 1: Load assessment page
    const assessmentRes = http.get(`${BASE_URL}/api/assessments`, {
      headers: { 'Content-Type': 'application/json' },
    });
    check(assessmentRes, {
      'assessment list loaded': (r) => r.status === 200,
    });
    sleep(1);

    // Step 2: Submit code solution
    const startTime = new Date();
    const submissionRes = http.post(
      `${BASE_URL}/api/submissions`,
      JSON.stringify({
        code: 'function twoSum(nums, target) { const map = new Map(); for (let i = 0; i < nums.length; i++) { const comp = target - nums[i]; if (map.has(comp)) return [map.get(comp), i]; map.set(nums[i], i); } }',
        language: 'javascript',
        assessment_id: 'load-test-assessment',
      }),
      {
        headers: { 'Content-Type': 'application/json' },
      }
    );
    const endTime = new Date();
    submissionDuration.add(endTime - startTime);

    const submissionSuccess = check(submissionRes, {
      'submission accepted': (r) => r.status === 200 || r.status === 201,
    });
    submissionFailRate.add(!submissionSuccess);
    sleep(2);

    // Step 3: Check submission results
    const resultsRes = http.get(`${BASE_URL}/api/submissions/results`, {
      headers: { 'Content-Type': 'application/json' },
    });
    check(resultsRes, {
      'results retrieved': (r) => r.status === 200,
    });
    sleep(1);
  });
}

Secret mission

Pre-Warm Connections for Known Peak Times

Assessly has predictable traffic spikes. Exam start times, assignment deadlines, lab sessions. Build a pre-warming strategy that prepares your infrastructure before the burst hits, so connections are hot and ready when 500 students press submit simultaneously.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally and uses your existing Supabase project, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • k6 (installed via winget).
  • assessly-load-tests folder on your Desktop (contains script.js, warmup.js, sustained-load.js).
  • Database indexes created on your Supabase project.
  • Connection pooling and Custom Postgres Config changes on Supabase.

Keep everything running

No action needed. Choose this if you plan to keep using your load testing scripts and want the Supabase optimizations active for production traffic.

  • k6 stays installed on your machine for future load tests.
  • Your assessly-load-tests folder remains on your Desktop, ready to rerun anytime.
  • Database indexes and connection pooling settings continue improving Assessly's performance.

Pause - I'll come back to this later

Shut down any running processes to free up system resources, but keep your files and configurations intact for later.

  • If you have any k6 tests still running, press Ctrl+C in the terminal to stop them.
  • Your test scripts and Supabase configurations stay in place. You can pick up where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below.

  • Delete the assessly-load-tests folder by running this in your terminal:
  • On Windows:
cd %USERPROFILE%\Desktop
rmdir /s /q assessly-load-tests
  • On macOS/Linux:
cd ~/Desktop
rm -rf assessly-load-tests
  • Uninstall k6 from your machine by running:
winget uninstall k6
  • Remove the database indexes you created. In the Supabase SQL Editor, run DROP INDEX for each index you added. You can find your custom indexes by querying:
SELECT indexname, tablename
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
  • Then drop each custom index you created (not the ones Supabase created automatically):
-- Replace with your actual index names
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;
  • Revert your connection pooling changes. In the Supabase Dashboard, go to Database Settings > Connection Pooling and reset the pool size to the default value.
  • Revert your Custom Postgres Config changes. In the Supabase Dashboard, go to Database Settings > Custom Postgres Config and remove any parameters you added for peak-time pool sizing.

Nice Work!

Nice Work!

Nice work! You've built a complete performance engineering workflow for Assessly. When 500 students hit "submit" at the same time during an exam, you now know exactly what happens and have the infrastructure to handle it.

You've learned how to:

  • Write k6 load test scripts that simulate realistic burst traffic patterns, ramping to 500 virtual users to stress-test your Supabase backend.
  • Optimize connection pooling for Vercel's serverless architecture using Supavisor transaction mode, and create targeted database indexes to eliminate slow queries.
  • Measure and document before-and-after performance improvements with hard data, including p95 latency, error rates, and throughput.
  • Built a pre-warming strategy for predictable traffic spikes during exam times.

Ready to quiz yourself?