Build a Privacy-Safe Nearby Grid

Build a PostGIS nearby-users backend with fast, privacy-safe discovery.

Introduction

30 Second Summary

Nearby apps are useful when they reveal who is close enough to meet. The same feature becomes dangerous when repeated searches expose where someone is standing.

In this project, you will build a local nearby-discovery prototype with PostgreSQL and PostGIS. You will test its performance at scale before hardening the response against location inference.

What You'll Build

From Terminal, you'll search around a fictional San Francisco viewer while the final response filters unsafe profiles and reveals only coarse distance bands.

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

  • A runnable nearby grid that returns fictional profiles around a stored viewer location.
  • A 100,006-profile workload where you can compare a sequential scan with a GiST index access path.
  • A privacy boundary that hides policy-ineligible profiles and returns coarse distance bands.
  • Secret Mission: Add a per-viewer query budget that rejects the sixth discovery call within one minute.

Are there any prerequisites?

Bring backend system design experience plus access to Docker Desktop, VS Code, Terminal, and a browser.

The database runs in Docker Desktop, so you do not need PostgreSQL installed on your Mac.

Before We Start

A nearby-users feature can help people discover others around them. Before any hands-on work begins, you will commit to a fictional design that protects people from location inference by withholding exact locations and exact distances.

Start a Mac-Compatible PostGIS Lab

A nearby search depends on a spatial database that behaves consistently across different Macs. The lab must run natively on Intel Macs or Apple Silicon Macs.

This step runs PostgreSQL 18.6 with PostGIS 3.6.4 inside Docker Desktop. The container keeps the database isolated from your Mac.

In this step, get ready to:
  • Confirm Docker Desktop and Docker Compose are available.
  • Create the Compose configuration and spatial database schema.
  • Start the database and verify the PostgreSQL and PostGIS versions.
Confirm Docker and Compose

Docker Desktop supplies the container engine used by this lab. Docker Compose reads the project configuration that defines the database service.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Docker and press Enter to open Docker Desktop.
  • Wait for Docker Desktop to finish starting.
  • Press Cmd+Space (macOS) or the Windows key (Windows) to return to your search bar.
  • Type Terminal and press Enter to open Terminal.
  • Check the Docker engine and Docker Compose by running:
docker version
docker compose version

What Do These Checks Prove?

  • The first command asks the Docker client to communicate with the running Docker engine.
  • The second command confirms that Docker Compose is available through the Docker CLI.

✔️ I see both version reports

Your local container tooling is ready. You can now build the database lab from a reproducible configuration.

ⓧ Docker is not responding

  • Return to Docker Desktop from earlier.
  • Wait for Docker Desktop to finish starting.
  • Return to Terminal.
  • Run the two checks above again.

ⓧ I see command not found

Docker Desktop includes the Docker engine, Docker CLI, and Docker Compose. A missing command means the local installation needs attention.

Still Blocked by Docker?

Confirm that Docker Desktop finishes starting before repeating the checks. A running app without a ready engine cannot start the database container.

If the checks still fail, help me troubleshoot Docker Desktop on my Mac.

Create the Compose project and schema

A Docker Compose file captures the database image, local credentials, storage, mounted SQL files, and health check. This makes the lab reproducible whenever you restart it.

Why Use the Kartoza Image?

The selected Kartoza PostGIS container image publishes a linux/amd64 variant. It also publishes a linux/arm64 variant.

These variants let Intel Macs and Apple Silicon Macs use native container architectures. The lab avoids relying on processor emulation.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.

You'll see the VS Code welcome window.

  • Click File in the top menu bar.
  • Click Open Folder.

You'll see the folder selection window.

  • Select your Desktop folder.
  • Click New Folder.

You'll see a field for the new folder name.

  • Enter nearby-grid and press Enter.

You'll see the new nearby-grid folder on your Desktop.

  • Select the nearby-grid folder.
  • Click Open.

The Explorer sidebar now shows an empty nearby-grid workspace.

  • Select the nearby-grid folder in the Explorer sidebar.
  • Click New File in the Explorer header.

You'll see a field for the file name inside nearby-grid.

  • Enter compose.yaml and press Enter.
  • Add the database service by pasting this configuration into compose.yaml:
services:
  db:
    image: kartoza/postgis:18.6-3.6.4--v2026.08.30
    environment:
      POSTGRES_DB: nearby
      POSTGRES_USER: nearby
      POSTGRES_PASS: nearby-local-only
      POSTGRES_MULTIPLE_EXTENSIONS: postgis
    volumes:
      - nearby_pgdata:/var/lib/postgresql
      - ./sql:/work/sql:ro
    healthcheck:
      test: ["CMD", "test", "-f", "/tmp/postgres-ready"]
      interval: 5s
      timeout: 2s
      retries: 20

volumes:
  nearby_pgdata:

What Does This Configuration Do?

  • The db service uses the pinned PostgreSQL 18.6 and PostGIS 3.6.4 image.
  • The environment values create the nearby database user and database.
  • The nearby_pgdata volume preserves database files across container restarts.
  • The ./sql:/work/sql:ro mount exposes your local SQL files to the container as read-only content.
  • The health check waits for the image's readiness file before Compose reports the service as healthy.
  • Save compose.yaml by pressing Cmd+S (macOS) or Ctrl+S (Windows).

The saved compose.yaml file now appears in the Explorer sidebar.

  • Click Terminal in the top menu bar.
  • Click New Terminal.
  • Validate the Compose model by running:
docker compose config

What Does This Command Check?

Docker Compose parses the file and renders the resolved project model. Successful output confirms that the YAML structure is valid.

You'll see the resolved configuration in Terminal without a parsing error.

Compose File Not Valid?

Check the indentation under db, environment, volumes, and healthcheck. YAML uses spaces to represent nesting.

If the configuration still fails, help me compare my Compose YAML with the required structure.

The database also needs tables for fictional profiles and mutual block relationships. A WGS84 geography point stores each profile location in longitude and latitude coordinates.

  • Select the nearby-grid folder in the Explorer sidebar.
  • Click New Folder in the Explorer header.

You'll see a field for the folder name.

  • Enter sql and press Enter.

The sql directory now appears under nearby-grid.

  • Select the sql directory in the Explorer sidebar.
  • Click New File in the Explorer header.

You'll see a field for a file inside the sql directory.

  • Enter 01-schema.sql and press Enter.
  • Add the spatial schema by pasting this SQL into sql/01-schema.sql:
CREATE EXTENSION IF NOT EXISTS postgis;

DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS profiles;

CREATE TABLE profiles (
    id bigint PRIMARY KEY,
    handle text NOT NULL UNIQUE,
    location geography(POINT, 4326) NOT NULL,
    last_seen timestamptz NOT NULL,
    is_visible boolean NOT NULL DEFAULT true
);

CREATE TABLE blocks (
    blocker_id bigint NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    blocked_id bigint NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    PRIMARY KEY (blocker_id, blocked_id),
    CHECK (blocker_id <> blocked_id)
);

What Does This Schema Create?

  • The extension statement enables PostGIS features in the nearby database.
  • The profiles table stores a unique handle and a geography(POINT, 4326) location for each fictional profile.
  • The last_seen field supports freshness rules later in the project.
  • The is_visible field supports profile visibility rules.
  • The blocks table records directed block relationships between profiles.
  • Save sql/01-schema.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

The Explorer sidebar now shows compose.yaml beside the sql directory. The directory contains 01-schema.sql.

✔️ Awesome, I've got everything!

Your two project files are saved. Continue when the Explorer sidebar shows both paths.

ⓧ I'd like to double check the full code

Compare your complete compose.yaml with this reference.

services:
  db:
    image: kartoza/postgis:18.6-3.6.4--v2026.08.30
    environment:
      POSTGRES_DB: nearby
      POSTGRES_USER: nearby
      POSTGRES_PASS: nearby-local-only
      POSTGRES_MULTIPLE_EXTENSIONS: postgis
    volumes:
      - nearby_pgdata:/var/lib/postgresql
      - ./sql:/work/sql:ro
    healthcheck:
      test: ["CMD", "test", "-f", "/tmp/postgres-ready"]
      interval: 5s
      timeout: 2s
      retries: 20

volumes:
  nearby_pgdata:

Compose File Check

Your file should define one db service and one nearby_pgdata named volume. The local sql directory should be mounted at /work/sql in read-only mode.

Compare your complete sql/01-schema.sql with this reference.

CREATE EXTENSION IF NOT EXISTS postgis;

DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS profiles;

CREATE TABLE profiles (
    id bigint PRIMARY KEY,
    handle text NOT NULL UNIQUE,
    location geography(POINT, 4326) NOT NULL,
    last_seen timestamptz NOT NULL,
    is_visible boolean NOT NULL DEFAULT true
);

CREATE TABLE blocks (
    blocker_id bigint NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    blocked_id bigint NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    PRIMARY KEY (blocker_id, blocked_id),
    CHECK (blocker_id <> blocked_id)
);

Schema File Check

Your schema should enable PostGIS before creating the two tables. The foreign keys in blocks should reference profile IDs.

Start the database and verify PostGIS

The first container start may take a few minutes while Docker downloads the image. A quiet Terminal during the download does not mean the command has stalled.

  • Return to the integrated terminal from earlier.
  • Start the database and wait for its health check by running:
docker compose up -d --wait

What Does This Command Do?

Detached mode leaves the database running in the background. The wait option holds the command until the db service reports a healthy state.

You'll see the db service reach a running or healthy state.

Database Not Healthy?

Confirm that Docker Desktop is still running. Check that the health check in compose.yaml matches the reference exactly.

If startup still fails, help me diagnose the unhealthy PostGIS container.

The healthy database can now execute the mounted schema file. The read-only mount protects your local SQL from changes inside the container.

  • Load the PostGIS extension and database tables by running:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/01-schema.sql

How Is the Schema Loaded?

  • Docker Compose executes psql inside the running db service.
  • The database connection uses the nearby user and the nearby database.
  • The file option reads the mounted /work/sql/01-schema.sql script.

You'll see completion messages for the PostGIS extension and both tables. This confirms that profiles and blocks now exist.

Schema Load Failed?

Confirm that 01-schema.sql sits inside the local sql directory. Confirm that the volume mount uses ./sql:/work/sql:ro.

If the schema still fails, help me troubleshoot the mounted SQL schema.

Before you run the final check, predict the two version numbers you expect the database to report.

  • Verify the database and spatial extension versions by running:
docker compose exec -T db psql -U nearby -d nearby -c "SELECT version(), PostGIS_Full_Version();"

What Does the Final Check Prove?

The query asks PostgreSQL for its server version. It also asks PostGIS for its full extension version and build details.

That's the local lab online. You'll see PostgreSQL 18.6 and PostGIS 3.6.4 in the output.

Versions Do Not Match?

Compare the image tag in compose.yaml with the full-code reference. An older local container can remain after an image tag changes.

If the output still differs, help me verify the PostgreSQL and PostGIS versions in my container.

Your spatial database is healthy and ready for fictional profile data. Next, you'll run the first nearby search and see exact distances appear.

Build the First Nearby Grid

Your local PostgreSQL database is healthy. PostGIS is ready to calculate geographic distances.

A small fictional grid creates the project's first visible nearby result. This first pass exposes exact distances.

The query also skips privacy policy. Its scaling limit remains untested until the dataset grows.

In this step, get ready to:
  • Seed six fictional profiles with different freshness states and visibility states.
  • Write a radius query that calculates exact distances from Alex.
  • Run the nearby query to inspect its exact-distance output.
Seed six fictional profiles

A repeatable fixture gives each later query the same locations and policy states. The six profiles represent active users plus deliberately stale or hidden cases.

  • Switch back to VS Code from earlier.
  • Select the sql folder in the file sidebar.
  • Click the new-file control at the top of the file sidebar.
  • Enter 02-seed-small.sql as the file name.
  • Paste the fixture into sql/02-seed-small.sql using the code below:
TRUNCATE blocks, profiles;

INSERT INTO profiles (id, handle, location, last_seen, is_visible) VALUES
    (1, 'Alex',   ST_Point(-122.4194, 37.7749, 4326)::geography, now(), true),
    (2, 'Casey',  ST_Point(-122.4140, 37.7790, 4326)::geography, now(), true),
    (3, 'Drew',   ST_Point(-122.4090, 37.7840, 4326)::geography, now(), true),
    (4, 'Morgan', ST_Point(-122.4300, 37.7600, 4326)::geography, now() - interval '30 minutes', true),
    (5, 'Riley',  ST_Point(-122.4200, 37.7700, 4326)::geography, now(), false),
    (6, 'Jordan', ST_Point(-122.4180, 37.7755, 4326)::geography, now(), true);

INSERT INTO blocks (blocker_id, blocked_id) VALUES (1, 6);

What does this seed create?

  • The TRUNCATE statement resets both tables so rerunning the fixture stays predictable.
  • The profile rows place six fictional people around San Francisco using ST_Point.
  • Each point stores longitude before latitude.
  • Morgan has an older last_seen value. Riley has is_visible set to false.
  • The blocks row records Alex blocking Jordan.
  • Save sql/02-seed-small.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Confirm 02-seed-small.sql appears beneath the sql folder in the file sidebar.

The running database sees local files through the existing read-only mount at /work/sql. Loading the fixture turns those rows into queryable geography data.

  • Load the six-profile fixture into the running database by running this command in the Terminal from earlier:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/02-seed-small.sql

What does this command do?

The docker compose exec command runs psql inside the healthy db service. The -f option tells psql to execute the mounted fixture file.

You should see confirmation for one truncation plus two inserts. That fixture is now the controlled test bed for every distance check ahead.

Seed file not loading?

  • Confirm 02-seed-small.sql appears inside nearby-grid/sql.
  • Compare the saved file with the fixture shown above.
  • Confirm the Terminal is still inside the nearby-grid directory.

Ask for help with the fixture if the database still rejects it: help me troubleshoot why my PostGIS seed file is not loading

Write the exact-distance query

A nearby search needs one reference location for its viewer. WGS84 geography lets PostGIS report the distance between that location and each candidate in meters.

  • Select the sql folder in the VS Code file sidebar.
  • Click the new-file control at the top of the file sidebar.
  • Enter 03-naive.sql as the file name.
  • Paste the first nearby query into sql/03-naive.sql using the code below:
\pset pager off
\timing on

WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    round(ST_Distance(p.location, v.location))::bigint AS exact_distance_meters
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id <> 1
  AND ST_Distance(p.location, v.location) <= 1500
ORDER BY ST_Distance(p.location, v.location), p.id
LIMIT 20;

How does the nearby query work?

  • The viewer query selects Alex's stored location using profile ID 1.
  • The CROSS JOIN makes that viewer location available for each candidate row.
  • ST_Distance calculates each candidate's distance from Alex.
  • The radius filter keeps candidates within 1500 meters.
  • The result rounds each distance into exact_distance_meters.
  • The final clauses order candidates by exact distance before limiting the result to 20 rows.
  • Save sql/03-naive.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
  • Confirm 03-naive.sql appears beneath the sql folder in the file sidebar.

Query file not matching?

  • Check that the file name is exactly 03-naive.sql.
  • Check that both psql settings begin with a backslash.
  • Compare the query with the reference code below.

Ask for help if the query still differs: help me compare my nearby query with the expected SQL

✔️ Awesome, I've got everything!

Great. Double check that both new files are saved beneath the sql folder.

ⓧ I'd like to double check the full code

Your existing compose.yaml file should still match this reference.

services:
  db:
    image: kartoza/postgis:18.6-3.6.4--v2026.08.30
    environment:
      POSTGRES_DB: nearby
      POSTGRES_USER: nearby
      POSTGRES_PASS: nearby-local-only
      POSTGRES_MULTIPLE_EXTENSIONS: postgis
    volumes:
      - nearby_pgdata:/var/lib/postgresql
      - ./sql:/work/sql:ro
    healthcheck:
      test: ["CMD", "test", "-f", "/tmp/postgres-ready"]
      interval: 5s
      timeout: 2s
      retries: 20

volumes:
  nearby_pgdata:

Why is this file unchanged?

The database service already mounts the local sql directory at /work/sql. Both new files become available without changing the Compose configuration.

Your existing sql/01-schema.sql file should still define the two original tables.

CREATE EXTENSION IF NOT EXISTS postgis;

DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS profiles;

CREATE TABLE profiles (
    id bigint PRIMARY KEY,
    handle text NOT NULL UNIQUE,
    location geography(POINT, 4326) NOT NULL,
    last_seen timestamptz NOT NULL,
    is_visible boolean NOT NULL DEFAULT true
);

CREATE TABLE blocks (
    blocker_id bigint NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    blocked_id bigint NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    PRIMARY KEY (blocker_id, blocked_id),
    CHECK (blocker_id <> blocked_id)
);

Why is the schema unchanged?

The profiles table already holds location plus policy state. The blocks table already records directed block relationships.

Your complete sql/02-seed-small.sql file should look like this.

TRUNCATE blocks, profiles;

INSERT INTO profiles (id, handle, location, last_seen, is_visible) VALUES
    (1, 'Alex',   ST_Point(-122.4194, 37.7749, 4326)::geography, now(), true),
    (2, 'Casey',  ST_Point(-122.4140, 37.7790, 4326)::geography, now(), true),
    (3, 'Drew',   ST_Point(-122.4090, 37.7840, 4326)::geography, now(), true),
    (4, 'Morgan', ST_Point(-122.4300, 37.7600, 4326)::geography, now() - interval '30 minutes', true),
    (5, 'Riley',  ST_Point(-122.4200, 37.7700, 4326)::geography, now(), false),
    (6, 'Jordan', ST_Point(-122.4180, 37.7755, 4326)::geography, now(), true);

INSERT INTO blocks (blocker_id, blocked_id) VALUES (1, 6);

What should you compare?

Check the six profile IDs plus their coordinates. Check the final block relationship between Alex and Jordan.

Your complete sql/03-naive.sql file should look like this.

\pset pager off
\timing on

WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    round(ST_Distance(p.location, v.location))::bigint AS exact_distance_meters
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id <> 1
  AND ST_Distance(p.location, v.location) <= 1500
ORDER BY ST_Distance(p.location, v.location), p.id
LIMIT 20;

What should you compare here?

Check the viewer ID plus the 1500 meter filter. Check the exact-distance alias plus the 20 row limit.

Run the first nearby search

The database now has a controlled fixture. The saved query can reveal which profiles a distance-only rule considers nearby.

Before you run the search, which profiles do you think a radius-only filter will return?

  • Run the nearby query against the fixture by entering this command in the Terminal from earlier:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/03-naive.sql

What should you see?

You should see Jordan, Riley, Casey, and Drew ordered from nearest to farthest beneath the exact_distance_meters column.

The result is the intended shortfall. Riley is invisible in the seed.

Jordan is blocked by Alex. Both profiles still appear because this query checks distance only.

The exact meter values also expose unnecessary precision. The grid now has a privacy gap you can measure.

Nearby handles not appearing?

  • Confirm the seed command completed before the query command.
  • Compare sql/02-seed-small.sql with the full-file reference.
  • Compare sql/03-naive.sql with the full-file reference.

Ask for help if the result still differs: help me debug my first PostGIS nearby query

You have your first nearby result, complete with a measurable privacy gap. Next, you'll scale the fixture until the naive distance scan exposes its retrieval cost.

Expose the Naive Scan at Scale

Your six-profile grid already returns nearby handles with exact distances. Now you will test that same query against enough data to expose how PostgreSQL retrieves candidates.

The EXPLAIN (ANALYZE, BUFFERS) statement runs the query and reports its actual work. The resulting query plan shows whether the naive distance predicate can avoid checking most profiles.

In this step, get ready to:
  • Generate 100,000 deterministic fictional profiles.
  • Refresh PostgreSQL's planner statistics.
  • Capture the naive query's scan path and execution evidence.
Generate the large workload

The generate_series(1, 100000) function creates a repeatable workload without maintaining a huge seed file. Its coordinate formulas place every generated profile inside the same bounded region.

  • Switch back to the nearby-grid folder in VS Code.
  • Expand the sql directory in the left sidebar.
  • Create sql/04-scale-and-explain.sql with the new-file control in the sidebar.
  • Add the deterministic profile insert by pasting this code into the new file:
INSERT INTO profiles (id, handle, location, last_seen, is_visible)
SELECT
    1000 + n,
    'user_' || n,
    ST_Point(
        -122.55 + (((n * 73) % 10000)::double precision / 10000.0) * 0.40,
        37.60 + (((n * 37) % 10000)::double precision / 10000.0) * 0.40,
        4326
    )::geography,
    now() - ((n % 60) * interval '1 minute'),
    n % 20 <> 0
FROM generate_series(1, 100000) AS s(n)
ON CONFLICT (id) DO NOTHING;

How does the workload stay deterministic?

  • The generated IDs run from 1001 through 101000.
  • The coordinate formulas distribute profiles across a bounded longitude and latitude region.
  • The last_seen expression staggers timestamps across a 60-minute range.
  • The n % 20 <> 0 expression marks every twentieth generated profile as invisible.
  • The ON CONFLICT (id) DO NOTHING clause keeps repeated runs from duplicating IDs.
  • Save sql/04-scale-and-explain.sql.
  • Switch back to the Terminal window from earlier.
  • Load the generated profiles by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/04-scale-and-explain.sql

What changed in the database?

This first execution adds 100,000 deterministic profiles while preserving the original six. That is the scale jump complete. Your table now contains 100,006 fictional profiles.

Did the bulk load fail?

Confirm that 04-scale-and-explain.sql is inside the sql directory. Save the file before running the command again.

Check that the local db service from earlier is still healthy.

Help me diagnose the failed profile load.

Measure the naive execution plan

Planner statistics describe the table's current data distribution. Refreshing them after the bulk load gives PostgreSQL current information before it chooses an execution plan.

  • Return to sql/04-scale-and-explain.sql in VS Code.
  • Place your cursor after the existing insert statement at the bottom of the file.
  • Append the statistics refresh and measured query by pasting this code:
ANALYZE profiles;

SELECT count(*) AS profile_count FROM profiles;

EXPLAIN (ANALYZE, BUFFERS)
WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    ST_Distance(p.location, v.location) AS distance_meters
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id <> 1
  AND ST_Distance(p.location, v.location) <= 1500
ORDER BY ST_Distance(p.location, v.location), p.id
LIMIT 20;

What does this analysis measure?

  • The ANALYZE profiles statement refreshes statistics after the bulk insert.
  • The profile_count query confirms the total workload size.
  • The EXPLAIN (ANALYZE, BUFFERS) statement executes the nearby query while collecting actual timing and buffer activity.
  • The radius condition still uses ST_Distance across the candidate table.
  • Save sql/04-scale-and-explain.sql.

Before you run it, which plan node do you think will perform most of the work? The next command tests your prediction.

  • Execute the complete scale test by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/04-scale-and-explain.sql

What should you see?

The profile count is 100,006. This confirms that the six seeded profiles remain alongside the 100,000 generated profiles.

The plan contains Seq Scan on profiles. Its actual timing shows how long this node ran.

The buffer statistics show data-page activity. The rows removed by the filter show how many candidates failed the radius test.

This sequential scan is the intended shortfall. Your working query now has measurable evidence of its scaling cost.

Cannot find the sequential scan?

Confirm that the radius condition still reads ST_Distance(p.location, v.location) <= 1500. Check that the execution plan appears after the bulk insert in the same file.

Help me inspect my query plan.

Record the baseline evidence

A measured baseline gives you concrete evidence for the retrieval change that follows. Execution time can vary with Docker resources and cache warmth.

  • Record the scan node as your scan node.
  • Record the actual execution time as your baseline execution time.
  • Record the buffer statistics as your baseline buffer statistics.
  • Record the rows removed by the filter as your baseline rows removed.

✔️ Awesome, I've got everything!

Great. Your baseline now captures the scan path and the work performed by the naive distance query.

ⓧ I'd like to double check the full code

  • Compare your complete sql/04-scale-and-explain.sql file with this reference:
INSERT INTO profiles (id, handle, location, last_seen, is_visible)
SELECT
    1000 + n,
    'user_' || n,
    ST_Point(
        -122.55 + (((n * 73) % 10000)::double precision / 10000.0) * 0.40,
        37.60 + (((n * 37) % 10000)::double precision / 10000.0) * 0.40,
        4326
    )::geography,
    now() - ((n % 60) * interval '1 minute'),
    n % 20 <> 0
FROM generate_series(1, 100000) AS s(n)
ON CONFLICT (id) DO NOTHING;

ANALYZE profiles;

SELECT count(*) AS profile_count FROM profiles;

EXPLAIN (ANALYZE, BUFFERS)
WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    ST_Distance(p.location, v.location) AS distance_meters
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id <> 1
  AND ST_Distance(p.location, v.location) <= 1500
ORDER BY ST_Distance(p.location, v.location), p.id
LIMIT 20;

Why is this script safe to rerun?

The conflict clause skips generated IDs that already exist. Each run still refreshes statistics and produces a new measured plan for the same 100,006-row workload.

You have turned a hidden scaling cost into measurable evidence. Next, you will change candidate retrieval so PostgreSQL can narrow the search before calculating distances.

Optimize Retrieval with GiST

Your baseline test proved that the nearby query works across 100,006 profiles. The PostgreSQL plan also exposed a full Seq Scan on profiles for candidate retrieval.

This step adds a geography GiST index for the stored locations. PostGIS can use ST_DWithin to narrow the candidate set before ST_Distance orders the results.

In this step, get ready to:
  • Create a geography GiST index for profile locations.
  • Use ST_DWithin to retrieve nearby candidates through the spatial index.
  • Compare the indexed query plan with the sequential-scan baseline.
Create the GiST index

A GiST index organizes the geography values into a spatial search structure. Refreshed planner statistics help PostgreSQL evaluate that new access path.

  • Select the sql folder in the VS Code Explorer sidebar.
  • Click the New File button in the Explorer toolbar.
  • Enter 05-indexed.sql as the file name.
  • Add the spatial index setup by pasting the following code:
CREATE INDEX IF NOT EXISTS profiles_location_gix
    ON profiles
    USING GIST (location);

ANALYZE profiles;

Why GiST for locations?

  • The profiles_location_gix index gives PostgreSQL a spatial access path for the location column.
  • The USING GIST clause creates the search structure used by index-aware spatial predicates.
  • The ANALYZE profiles statement refreshes the planner statistics after the index is created.
  • Save sql/05-indexed.sql.
  • Switch back to the Terminal from earlier.
  • Create the index inside the running database by executing the new SQL file:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/05-indexed.sql

What does this command do?

Docker Compose executes psql inside the running db service. PostgreSQL reads /work/sql/05-indexed.sql to create the index and refresh the table statistics.

That bottleneck now has a targeted escape route. Your profiles table has a spatial index ready for candidate retrieval.

Index setup did not complete?

Confirm that 05-indexed.sql is inside the sql folder within nearby-grid. The read-only Compose mount exposes that folder at /work/sql inside the container.

Compare the index name and location column with the code above if index creation fails.

Help me diagnose the failed GiST index setup.

Use ST_DWithin for candidate retrieval

The radius predicate determines whether PostgreSQL can use the spatial index. ST_DWithin performs an index-aware bounding-box check before confirming the geography distance in meters.

  • Place your cursor below the statistics refresh statement in sql/05-indexed.sql.
  • Add the indexed query plan by pasting the following code:
EXPLAIN (ANALYZE, BUFFERS)
WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    ST_Distance(p.location, v.location) AS distance_meters
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id <> 1
  AND ST_DWithin(p.location, v.location, 1500)
ORDER BY ST_Distance(p.location, v.location), p.id
LIMIT 20;

What does this query change?

  • The viewer common table expression retrieves the stored location for profile 1.
  • The ST_DWithin predicate narrows the candidates to profiles within 1500 meters.
  • The ST_Distance calculation orders the reduced candidate set by distance.
  • The LIMIT 20 clause bounds the returned plan workload.
  • Save sql/05-indexed.sql.

Before you run the indexed plan, which access path do you expect PostgreSQL to choose now?

  • Switch back to the Terminal from earlier.
  • Run the completed indexed query script with this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/05-indexed.sql

What does this run prove?

The script keeps the index available through its guarded creation statement. It then executes EXPLAIN (ANALYZE, BUFFERS) against the ST_DWithin query.

The resulting plan reports the chosen access path. It also gives you actual timing and buffer statistics for comparison with the baseline.

You should find profiles_location_gix in an Index Scan or Bitmap Index Scan path. The spatial index now drives candidate retrieval.

Spatial index missing from the plan?

Search the complete plan for profiles_location_gix. The surrounding node can vary between an index scan and a bitmap index scan.

Confirm that the radius filter uses ST_DWithin(p.location, v.location, 1500). A distance-only filter can lead PostgreSQL back to scanning the profiles table.

Help me find why the indexed plan is missing.

✔️ Awesome, I've got everything!

Great. Double check that you saved sql/05-indexed.sql before comparing the two query plans.

ⓧ I'd like to double check the full code

Your complete sql/05-indexed.sql file should match this code exactly.

CREATE INDEX IF NOT EXISTS profiles_location_gix
    ON profiles
    USING GIST (location);

ANALYZE profiles;

EXPLAIN (ANALYZE, BUFFERS)
WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    ST_Distance(p.location, v.location) AS distance_meters
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id <> 1
  AND ST_DWithin(p.location, v.location, 1500)
ORDER BY ST_Distance(p.location, v.location), p.id
LIMIT 20;

How does the full file work?

The first section creates profiles_location_gix and refreshes planner statistics. The second section measures an index-aware radius query around viewer 1.

Candidate filtering uses ST_DWithin. Distance calculation remains available for ordering the smaller result set.

Compare the query plans

Before you compare the plans, which query do you expect to use the spatial access path?

  • Return to your baseline evidence from sql/04-scale-and-explain.sql.
  • Compare its Seq Scan on profiles node with the current access path.
  • Compare the actual execution times reported by both plans.
  • Compare the buffer statistics reported by both plans.

Your comparison is complete when the indexed plan names profiles_location_gix as part of candidate retrieval. Your own timing and buffer measurements provide the performance evidence for this Mac.

Your nearby search now uses a measured spatial access path across 100,006 fictional profiles. Next, you will pressure-test what that fast response reveals before enforcing the privacy boundary.

Harden the Privacy Boundary

Strong work. Your PostGIS query now uses a GiST index to keep nearby retrieval off a full profiles scan.

Speed alone leaves the response unsafe. Exact distances support location inference from repeated probes.

The raw query also exposes stale profiles. It ignores visibility settings and mutual blocks. This step makes those failures visible before moving retrieval behind a privacy boundary.

In this step, get ready to:
  • Pressure-test exact-distance disclosure from three probe positions.
  • Enforce freshness, visibility, mutual-block, radius, and distance-band policies.
  • Render a production architecture with retrieval, policy, throttling, caching, and auditing boundaries.
Pressure-test the exact-distance response

A trilateration pressure test compares exact distances from several known positions. Changes across those measurements reveal information about where a target is located.

  • Create `sql/06-privacy-pressure.sql` inside the `nearby-grid/sql` folder in VS Code.
  • Add the raw policy exposure query by pasting this code into `sql/06-privacy-pressure.sql`:
\pset pager off

WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    round(ST_Distance(p.location, v.location))::bigint AS exact_distance_meters,
    CASE
        WHEN NOT p.is_visible THEN 'invisible but returned'
        WHEN p.last_seen < now() - interval '15 minutes' THEN 'stale but returned'
        WHEN EXISTS (
            SELECT 1
            FROM blocks AS b
            WHERE (b.blocker_id = 1 AND b.blocked_id = p.id)
               OR (b.blocker_id = p.id AND b.blocked_id = 1)
        ) THEN 'blocked but returned'
        ELSE 'eligible'
    END AS policy_failure
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id IN (2, 4, 5, 6)
ORDER BY p.id;

What does this query expose?

  • The `viewer` CTE retrieves Alex's stored location.
  • The query calculates an exact distance for each selected profile.
  • The `policy_failure` column labels visibility, freshness, and block violations without filtering them.
  • Save `sql/06-privacy-pressure.sql`.

Before you run the query, which policy failures do you think the fast retrieval path still exposes?

  • Run the policy exposure query from the `nearby-grid` Terminal by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/06-privacy-pressure.sql

What should you see?

You should see Casey marked as eligible. Morgan is stale but returned. Riley is invisible but returned. Jordan is blocked but returned.

The index made candidate retrieval faster. The raw response still bypasses the policies that protect each profile.

Missing the policy failures?

Confirm that `sql/06-privacy-pressure.sql` is saved inside the mounted `nearby-grid/sql` folder.

Check that the selected IDs remain `2`, `4`, `5`, and `6`.

Ask for help with debugging the policy exposure query.

The first result exposes policy failures in one request. Repeated exact-distance measurements add a second leak by showing how the target's distance changes from different positions.

  • Append the three-probe pressure test to `sql/06-privacy-pressure.sql` by adding this code below the first query:
WITH probes(label, location) AS (
    VALUES
        ('probe_west',  ST_Point(-122.4244, 37.7790, 4326)::geography),
        ('probe_north', ST_Point(-122.4140, 37.7840, 4326)::geography),
        ('probe_south', ST_Point(-122.4140, 37.7740, 4326)::geography)
)
SELECT
    probes.label,
    round(ST_Distance(target.location, probes.location))::bigint AS exact_distance_to_casey_meters
FROM probes
CROSS JOIN profiles AS target
WHERE target.id = 2
ORDER BY probes.label;

How does the pressure test work?

  • The `probes` CTE defines three fictional observation points around Casey.
  • Each row calculates an exact distance from one probe to Casey.
  • The changing measurements demonstrate how repeated requests can reveal more than one nearby result should disclose.
  • Save `sql/06-privacy-pressure.sql`.

Before you rerun the file, do you expect the three probes to report identical measurements?

  • Run the completed pressure test by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/06-privacy-pressure.sql

What did the probes reveal?

You should see separate rows for `probe_north`, `probe_south`, and `probe_west`. Each row reports Casey's exact distance from that probe.

Those changing measurements make the privacy leak observable. The next query removes exact-distance disclosure from the discovery response.

Probe rows missing?

Confirm that the probe query sits below the first query in `sql/06-privacy-pressure.sql`.

Check the closing parenthesis after the three probe values.

Ask for help with fixing the three-probe SQL query.

✔️ Awesome, I've got everything!

Your pressure test now exposes the raw policy failures and Casey's exact distance from three probe positions.

ⓧ I'd like to double check the full code

Compare your complete `sql/06-privacy-pressure.sql` file with this reference.

\pset pager off

WITH viewer AS (
    SELECT location
    FROM profiles
    WHERE id = 1
)
SELECT
    p.id,
    p.handle,
    round(ST_Distance(p.location, v.location))::bigint AS exact_distance_meters,
    CASE
        WHEN NOT p.is_visible THEN 'invisible but returned'
        WHEN p.last_seen < now() - interval '15 minutes' THEN 'stale but returned'
        WHEN EXISTS (
            SELECT 1
            FROM blocks AS b
            WHERE (b.blocker_id = 1 AND b.blocked_id = p.id)
               OR (b.blocker_id = p.id AND b.blocked_id = 1)
        ) THEN 'blocked but returned'
        ELSE 'eligible'
    END AS policy_failure
FROM profiles AS p
CROSS JOIN viewer AS v
WHERE p.id IN (2, 4, 5, 6)
ORDER BY p.id;

WITH probes(label, location) AS (
    VALUES
        ('probe_west',  ST_Point(-122.4244, 37.7790, 4326)::geography),
        ('probe_north', ST_Point(-122.4140, 37.7840, 4326)::geography),
        ('probe_south', ST_Point(-122.4140, 37.7740, 4326)::geography)
)
SELECT
    probes.label,
    round(ST_Distance(target.location, probes.location))::bigint AS exact_distance_to_casey_meters
FROM probes
CROSS JOIN profiles AS target
WHERE target.id = 2
ORDER BY probes.label;

What should match?

The file contains two complete queries. The first exposes policy failures. The second measures Casey from three probe positions.

Build the policy-enforcing function

A single database function gives every nearby read the same policy checks. Candidate retrieval remains index-aware while the returned columns expose only an ID, a handle, and a coarse distance band.

The function is split into two readable chunks. Together they create one complete `nearby_profiles(p_viewer_id bigint)` definition before you run the file.

  • Create `sql/07-private-discovery.sql` inside the `nearby-grid/sql` folder in VS Code.
  • Add the function signature and candidate filters by pasting this first chunk into `sql/07-private-discovery.sql`:
CREATE OR REPLACE FUNCTION nearby_profiles(p_viewer_id bigint)
RETURNS TABLE (
    profile_id bigint,
    profile_handle text,
    distance_band text
)
LANGUAGE sql
STABLE
AS $$
    WITH viewer AS (
        SELECT location
        FROM profiles
        WHERE id = p_viewer_id
    ),
    candidates AS (
        SELECT
            p.id,
            p.handle,
            ST_Distance(p.location, v.location) AS distance_meters
        FROM profiles AS p
        CROSS JOIN viewer AS v
        WHERE p.id <> p_viewer_id
          AND p.is_visible
          AND p.last_seen >= now() - interval '15 minutes'
          AND ST_DWithin(p.location, v.location, 5000)

How does the function begin?

  • The table return type limits the response to `profile_id`, `profile_handle`, and `distance_band`.
  • The `viewer` CTE retrieves the stored location for the supplied viewer ID.
  • The `candidates` CTE keeps visible profiles with recent locations inside the 5,000-meter search radius.
  • Complete the function by adding this chunk directly below the `ST_DWithin` line:
          AND NOT EXISTS (
              SELECT 1
              FROM blocks AS b
              WHERE (b.blocker_id = p_viewer_id AND b.blocked_id = p.id)
                 OR (b.blocker_id = p.id AND b.blocked_id = p_viewer_id)
          )
    ),
    bucketed AS (
        SELECT
            id,
            handle,
            CASE
                WHEN distance_meters < 1000 THEN 'under 1 km'
                WHEN distance_meters < 3000 THEN '1-3 km'
                ELSE '3-5 km'
            END AS band,
            CASE
                WHEN distance_meters < 1000 THEN 1
                WHEN distance_meters < 3000 THEN 2
                ELSE 3
            END AS band_rank
        FROM candidates
    )
    SELECT id, handle, band
    FROM bucketed
    ORDER BY band_rank, id
    LIMIT 20;
$$;

How does the policy finish?

  • The mutual block check rejects either block direction.
  • The `bucketed` CTE converts exact measurements into `under 1 km`, `1-3 km`, or `3-5 km`.
  • The final ordering uses `band_rank` followed by profile ID. It does not expose exact-distance ordering.
  • The `LIMIT 20` clause bounds each response.
  • Save `sql/07-private-discovery.sql`.
  • Create the `nearby_profiles` function by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/07-private-discovery.sql

What does this run prove?

The database should confirm that the function statement completed. The privacy policy now exists as a reusable database boundary.

Function creation failed?

Confirm that the second chunk begins directly after the `ST_DWithin` line.

Check that the file ends with the closing function delimiter.

Ask for help with debugging the nearby discovery function.

The function now exists. A focused verification query shows which named profiles cross the boundary and which profiles are suppressed.

  • Append the discovery checks to `sql/07-private-discovery.sql` by adding this code below the function:
SELECT * FROM nearby_profiles(1);

SELECT
    p.id,
    p.handle,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM nearby_profiles(1) AS n
            WHERE n.profile_id = p.id
        ) THEN 'returned'
        ELSE 'filtered'
    END AS discovery_result
FROM profiles AS p
WHERE p.id IN (2, 4, 5, 6)
ORDER BY p.id;

What do these checks prove?

The first query displays the bounded discovery response. It contains distance bands without exact measurements.

The second query checks Casey, Morgan, Riley, and Jordan against that response. It makes each policy decision visible.

  • Save `sql/07-private-discovery.sql`.

Before you run the hardened query, which of the four named profiles do you expect to cross the policy boundary?

  • Run the completed private discovery file by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/07-private-discovery.sql

What should you see?

You should see Casey returned with a distance band. The response does not include an exact distance column.

The policy report should mark Morgan, Riley, and Jordan as filtered. Freshness, visibility, and mutual blocks now affect the result before it leaves the database.

Unexpected profile returned?

Check that the `candidates` CTE includes the visibility condition and the 15-minute freshness condition.

Confirm that both block directions remain inside the `NOT EXISTS` condition.

Ask for help with finding a privacy policy gap.

✔️ Awesome, I've got everything!

Your `nearby_profiles` function returns banded results while filtering stale, invisible, and blocked profiles.

ⓧ I'd like to double check the full code

Compare your complete `sql/07-private-discovery.sql` file with this reference.

CREATE OR REPLACE FUNCTION nearby_profiles(p_viewer_id bigint)
RETURNS TABLE (
    profile_id bigint,
    profile_handle text,
    distance_band text
)
LANGUAGE sql
STABLE
AS $$
    WITH viewer AS (
        SELECT location
        FROM profiles
        WHERE id = p_viewer_id
    ),
    candidates AS (
        SELECT
            p.id,
            p.handle,
            ST_Distance(p.location, v.location) AS distance_meters
        FROM profiles AS p
        CROSS JOIN viewer AS v
        WHERE p.id <> p_viewer_id
          AND p.is_visible
          AND p.last_seen >= now() - interval '15 minutes'
          AND ST_DWithin(p.location, v.location, 5000)
          AND NOT EXISTS (
              SELECT 1
              FROM blocks AS b
              WHERE (b.blocker_id = p_viewer_id AND b.blocked_id = p.id)
                 OR (b.blocker_id = p.id AND b.blocked_id = p_viewer_id)
          )
    ),
    bucketed AS (
        SELECT
            id,
            handle,
            CASE
                WHEN distance_meters < 1000 THEN 'under 1 km'
                WHEN distance_meters < 3000 THEN '1-3 km'
                ELSE '3-5 km'
            END AS band,
            CASE
                WHEN distance_meters < 1000 THEN 1
                WHEN distance_meters < 3000 THEN 2
                ELSE 3
            END AS band_rank
        FROM candidates
    )
    SELECT id, handle, band
    FROM bucketed
    ORDER BY band_rank, id
    LIMIT 20;
$$;

SELECT * FROM nearby_profiles(1);

SELECT
    p.id,
    p.handle,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM nearby_profiles(1) AS n
            WHERE n.profile_id = p.id
        ) THEN 'returned'
        ELSE 'filtered'
    END AS discovery_result
FROM profiles AS p
WHERE p.id IN (2, 4, 5, 6)
ORDER BY p.id;

What should match?

The function contains `viewer`, `candidates`, and `bucketed` CTEs. The two queries below it verify the returned columns and named policy outcomes.

Map the production boundary

The database function protects one read path. A production system also needs clear boundaries around location writes, candidate retrieval, policy checks, throttling, caching, and audit events.

The Mermaid Live Editor turns the architecture definition into a diagram you can inspect. The flow keeps policy enforcement between candidate retrieval and the client response.

  • Create `architecture.mmd` inside the `nearby-grid` folder in VS Code.
  • Add the production architecture by pasting this code into `architecture.mmd`:
flowchart LR
    Client[Mobile client] -->|location update| Edge[Edge rate limiter]
    Client -->|nearby request| Edge
    Edge --> Location[Location ingestion service]
    Edge --> Discovery[Discovery service]
    Location -->|validate and write| PostGIS[(PostgreSQL and PostGIS)]
    Discovery -. optional hot candidate lookup .-> Redis[(Redis GEO cache)]
    Discovery --> Policy[Privacy policy boundary]
    Policy -->|ST_DWithin candidates| PostGIS
    Redis -. candidate IDs only .-> Policy
    Policy -->|visibility, freshness, and block checks| PostGIS
    Policy --> Audit[(Abuse audit log)]
    Policy -->|distance bands and bounded results| Client

What does this architecture show?

  • The edge rate limiter handles request pressure before discovery.
  • The discovery service sends candidates through the privacy policy boundary.
  • PostgreSQL with PostGIS remains the source of truth for locations and policy data.
  • An optional Redis cache supplies candidate IDs without replacing database policy checks.
  • The policy boundary returns distance bands and writes abuse audit events.
  • Save `architecture.mmd`.
  • Open the official Mermaid Live Editor in your browser.
  • Copy the full contents of `architecture.mmd`.
  • Paste the copied definition into the diagram editor.

You should see a left-to-right architecture that starts with the mobile client. The final response returns through the privacy policy boundary as distance bands and bounded results.

Diagram not rendering?

Confirm that the first line of `architecture.mmd` is the flowchart declaration.

Check that every node and connection matches the reference file.

Ask for help with debugging the Mermaid architecture.

✔️ Awesome, I've got everything!

Your rendered architecture shows the complete path from location requests to policy-safe responses and audit events.

ⓧ I'd like to double check the full code

Compare your complete `architecture.mmd` file with this reference.

flowchart LR
    Client[Mobile client] -->|location update| Edge[Edge rate limiter]
    Client -->|nearby request| Edge
    Edge --> Location[Location ingestion service]
    Edge --> Discovery[Discovery service]
    Location -->|validate and write| PostGIS[(PostgreSQL and PostGIS)]
    Discovery -. optional hot candidate lookup .-> Redis[(Redis GEO cache)]
    Discovery --> Policy[Privacy policy boundary]
    Policy -->|ST_DWithin candidates| PostGIS
    Redis -. candidate IDs only .-> Policy
    Policy -->|visibility, freshness, and block checks| PostGIS
    Policy --> Audit[(Abuse audit log)]
    Policy -->|distance bands and bounded results| Client

What should match?

The diagram includes edge rate limiting, location ingestion, discovery, PostGIS, the optional Redis candidate cache, privacy enforcement, banded responses, and audit logging.

Before the final check, do you expect the discovery response to expose exact measurements or policy-filtered bands?

  • Verify the hardened discovery boundary by running this command:
docker compose exec -T db psql -U nearby -d nearby -f /work/sql/07-private-discovery.sql

Your final verification

You should see Casey returned with a distance band. Morgan, Riley, and Jordan should each be reported as filtered.

The response columns contain `profile_id`, `profile_handle`, and `distance_band`. Exact distances and exact-distance ordering stay behind the privacy boundary.

Final check still differs?

Confirm that the Terminal command reads `/work/sql/07-private-discovery.sql` from the mounted `sql` folder.

Rerun the complete file after saving your latest changes in VS Code.

Ask for help with checking the final privacy boundary.

You closed the most important gap in the prototype. Nearby retrieval now combines indexed candidate search with enforceable privacy policy and a production-ready system boundary.

Secret mission

Stop Enumeration with a Query Budget

Your distance bands protect each response. Repeated probes can still map where those bands change. Add a per-viewer query budget that lets five calls through before rejecting the sixth inside one minute.

Clean Up Your Resources

Clean Up Your Resources

Everything you created runs through local Docker resources, so there are no ongoing costs.

Decide whether to keep the lab running, pause it for later, or delete it entirely.

Resources you used:

  • The local Docker Compose service named db plus its Compose network.
  • The named volume nearby_pgdata containing the PostgreSQL database, PostGIS objects, fictional profiles, spatial index, discovery functions, and request history.
  • The nearby-grid project folder on your Desktop containing compose.yaml, the sql directory, and the Mermaid architecture file.

Keep everything running

No action needed. Choose this if you want to keep testing the nearby grid or reviewing its query plans.

  • Your db service stays healthy and available for more discovery queries.
  • Your nearby_pgdata volume retains the 100,006 fictional profiles plus the privacy and throttling functions.
  • Your nearby-grid folder remains on your Desktop with every SQL script and architecture.mmd.

Pause - I'll come back to this later

Stop the database container to free local resources. Your named volume and project files remain ready for the next session.

  • Return to the Terminal panel you used for nearby-grid.
  • Stop the running Compose service by running this command:
docker compose stop

What Does This Command Do?

This stops the running container without removing it. The database remains stored in nearby_pgdata.

Docker reports that the db service has stopped. Your fictional profiles and discovery functions remain intact.

Compose Cannot Find the Project?

Confirm that Terminal is inside ~/Desktop/nearby-grid before trying again.

Ask for help diagnosing why the Compose project cannot be stopped.

  • Restart the retained service when you return by running this command:
docker compose start

How Does Restarting Work?

This starts the stopped container using the existing volume. Your database returns with the same profiles, indexes, functions, and request records.

Delete - I don't want to use this again

This permanently removes the local database and every project file. Save anything you want to reference before continuing.

  • Return to the Terminal panel from earlier.
  • Move into the nearby-grid folder on your Desktop by running this command:
cd ~/Desktop/nearby-grid

Why This Directory Matters

Docker Compose reads compose.yaml from this folder. Running the cleanup here targets the database resources created by this project.

  • Remove the container, Compose network, and named database volume by running this command:
docker compose down --volumes

What Gets Removed?

This removes the db container and its Compose network. It also deletes nearby_pgdata with all fictional profiles, indexes, functions, and request history.

Docker confirms the Compose resources have been removed. The local database can no longer accept discovery queries.

Database Resources Still Running?

Confirm that Docker Desktop is running. Check that Terminal is inside the folder containing compose.yaml.

Ask for help removing the remaining Compose resources.

  • Remove the nearby-grid project folder from your Desktop by running these commands:
cd ~/Desktop
rm -rf nearby-grid

What Happens to the Project Files?

These commands leave the project directory before deleting it. The removal includes every SQL script, compose.yaml, and architecture.mmd.

  • Use Finder to check your Desktop. You should no longer see the nearby-grid folder.

Nice Work!

Nice Work!

Outstanding work. Your privacy-aware nearby discovery prototype now searches 100,006 fictional profiles with PostgreSQL and PostGIS.

You've learned how to:

  • Expose a sequential-scan bottleneck with EXPLAIN ANALYZE. Replace the naive distance filter with a GiST index plus an ST_DWithin candidate filter. Confirm profiles_location_gix replaces the full profiles scan.
  • Pressure-test exact-distance disclosure from three probe positions. Build nearby_profiles(p_viewer_id bigint) to exclude stale profiles. Suppress invisible or mutually blocked profiles. Return distance bands within a bounded result set.
  • Design a production discovery architecture in architecture.mmd. Place rate limiting before discovery. Keep PostGIS as the source of truth. Separate policy enforcement from optional candidate caching. Send abuse activity to an audit log.
  • Secret Mission: Add a per-viewer query budget to nearby_profiles_limited(p_viewer_id bigint). Prove five calls succeed inside one minute. Reject the sixth enumeration attempt.

Ready to quiz yourself?