Search Your Postgres Data

Add pg_pinecone search to local Postgres data and compare it with LIKE.

Introduction

30 Second Summary

Search boxes often miss useful results because people rarely type the exact words stored in a record. A relevant row can stay hidden even when its meaning fits the query.

In this project, you will load sample rows into a local Postgres database running in Docker. You will compare ranked results from pg_pinecone with plain SQL matching.

What You'll Build

Your side-by-side results file shows pg_pinecone surfacing ranked rows that a plain LIKE query misses.

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

  • A few hundred sample rows that you can query locally and confirm with a row count.
  • A working search index that returns ranked matches from text already stored in Postgres.
  • A saved results comparison you can show someone to explain where the two search methods differ.
  • Secret Mission: An optional challenge that extends your search workflow.

Are there any prerequisites?

You need Docker installed. You also need a terminal plus basic SQL knowledge.

Before We Start

Your search comparison needs a repeatable local database environment. Without that foundation, each query could run against a different setup.

In this step, you will prepare Docker for the local database. You will also prepare the terminal tools that let you run commands.

In this step, get ready to:
  • Get Docker running locally.
  • Prove Docker can run a container.
  • Prepare your terminal-based SQL workspace.
Check Docker's current state

Docker Desktop supplies the engine that runs local containers. The version check also reveals whether that engine is ready to accept commands.

  • Press Cmd+Space on macOS or the Windows key on Windows to open your search bar.
  • Type Terminal on macOS or PowerShell on Windows. Press Enter.
  • Check whether the Docker engine is available by running this command:
docker version

What does this command check?

The Client section describes the Docker command-line tool. The Server section confirms that the local Docker engine is responding.

✔️ I See Client and Server Details

Your Docker installation is ready. The terminal can communicate with the engine that runs containers.

ⓧ I See a Connection Error

The Docker command-line tool is installed. Docker Desktop still needs to start its local engine.

  • Press Cmd+Space on macOS or the Windows key on Windows to open your search bar.
  • Type Docker Desktop. Press Enter.
  • Wait until Docker Desktop reports that its engine is running.
  • Return to your terminal.
  • Press the Up Arrow key to recall the version check.
  • Press Enter to run the check again.

You should now see details for both the Docker client and server.

ⓧ Command Not Found

Docker Desktop is free for personal use. Educational use is also covered.

macOS

  • Open Docker's official macOS installation page in your browser.
  • Download the installer that matches your Mac's chip.
  • Double-click Docker.dmg in your Downloads folder.
  • Drag the Docker icon into Applications.
  • Press Cmd+Space to open Spotlight.
  • Type Docker Desktop. Press Enter.
  • Select Accept after reviewing the subscription agreement.
  • Wait until Docker Desktop reports that its engine is running.

The first start prepares Docker's local engine. Keep Docker Desktop open while you work through this project.

  • Return to your terminal.
  • Run the version check shown above again.

You should now see details for the Docker client and server.

Windows

  • Open Docker's official Windows installation page in your browser.
  • Download the installer that matches your Windows processor.
  • Double-click Docker Desktop Installer.exe in your Downloads folder.
  • Keep the recommended per-user installation mode selected.
  • Keep the default WSL 2 backend selected on the configuration screen.
  • Approve any operating system permission request.
  • Select Close when the installation succeeds.

Docker Desktop is now installed. The application still needs to start its local engine.

  • Press the Windows key to open search.
  • Type Docker Desktop. Press Enter.
  • Select Accept after reviewing the subscription agreement.
  • Wait until Docker Desktop reports that its engine is running.
  • Return to PowerShell.
  • Run the version check shown above again.

You should now see details for the Docker client and server.

Still Missing Docker Details?

Confirm that Docker Desktop remains open. Restart the application if the engine does not finish starting.

Windows users should confirm that the installer completed its WSL 2 setup. macOS users should confirm that Docker Desktop is installed in the Applications folder.

Help me diagnose why Docker Desktop is not returning client and server details.

Prove Docker can run a container

A container packages a process with the files it needs. This project uses that isolation to keep the local database separate from the rest of your computer.

Before you run the test, do you think Docker will reach its engine successfully?

  • Run Docker's test container by using this command:
docker run hello-world

What does this command prove?

  • Docker downloads a small test image when it is missing locally.
  • The engine starts a temporary process from that image.
  • The process prints a confirmation message before it exits.

You should see a confirmation message explaining that Docker is working correctly. That message proves your terminal can ask the local engine to run a container.

Test Container Did Not Run?

Check that Docker Desktop still reports a running engine. A blocked image download can also prevent the test from completing.

Help me troubleshoot my Docker test container.

That is the first local runtime check complete. Your machine can now start isolated services for the project.

Prepare your SQL workspace

You need a text editor for saved queries. You also need a command-line client for sending those queries to PostgreSQL.

  • Press Cmd+Space on macOS or the Windows key on Windows to open your search bar.
  • Type the name of your preferred text editor. Press Enter.
  • Create a blank document using the editor's new-file control.

You should see a blank editing area where you can save the project's SQL queries later.

The psql client sends SQL commands to PostgreSQL from your terminal. Installing the client tools alone keeps this step focused on preparation.

Before you run the check, do you think your terminal already knows where to find the client?

  • Check whether psql is available by running this command:
psql --version

What does this command check?

The --version option prints the installed psql version before the client exits. It does not connect to a database.

✔️ I See a psql Version

Your SQL client is ready. You now have a terminal command that can connect to the local Postgres container in the next step.

ⓧ Command Not Found

Your computer needs the PostgreSQL command-line tools. You can install those tools without creating the project's database.

macOS

  • Open the official PostgreSQL macOS packages page in your browser.
  • Select the interactive installer provided by EDB.
  • Download the macOS installer.
  • Open the downloaded installer.
  • Keep only Command Line Tools when the installer asks which components to include.
  • Finish the installation.
  • Close your current terminal window.
  • Use Spotlight to open a fresh Terminal window.

A fresh terminal reloads the locations where your computer looks for commands.

  • Run the version check shown above again.

You should now see the installed psql version.

Windows

  • Open the official PostgreSQL Windows installers page in your browser.
  • Select the interactive installer provided by EDB.
  • Download the Windows installer.
  • Open the downloaded installer.
  • Clear PostgreSQL Server on the Select Components screen.
  • Clear pgAdmin 4.
  • Clear Stack Builder.
  • Keep Command Line Tools selected.

Only the client utilities remain selected. The installer does not need to prepare the project's database.

  • Finish the installation.
  • Close your current PowerShell window.
  • Use Windows search to open a fresh PowerShell window.
  • Run the version check shown above again.

You should now see the installed psql version.

Still Cannot Find psql?

Some installers place PostgreSQL tools in an installation folder without adding that folder to your command search path. Reopen the installer to confirm that the command-line tools were included.

Help me make psql available in my terminal.

Your local toolkit is ready. Docker can run containers while your terminal can reach the SQL client.

Next up, you will start the local Postgres container with pg_pinecone installed. Then you will confirm that the extension is available.

Start Postgres with pg_pinecone

Search later in this project depends on Postgres finding the extension files on the server that runs it. A regular container does not include pg_pinecone.

This step uses a prebuilt Docker image to place PostgreSQL 17 inside one local container. You will finish with a live SQL connection to search_demo.

In this step, get ready to:
  • Start a local Postgres container that includes pg_pinecone.
  • Connect to the search_demo database.
  • Confirm that the extension catalog lists pg_pinecone.
Launch the extension-ready container

The official pg_pinecone image combines PostgreSQL 17 with pgvector. Its native search engine stays inside Postgres without a Pinecone account.

Why use the prebuilt image?

Building from source adds compiler setup before you can use the database. The prebuilt image already contains extension files matched to Postgres 17.

The fixed password in this command protects only this local practice database.

Expect the first start to take a few minutes while Docker downloads the image.

  • Create the pg-pinecone-search container by running this command:
docker run -d \
  --name pg-pinecone-search \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=search_demo \
  -p 127.0.0.1:5432:5432 \
  pineconeteam/pg_pinecone:0.11.0-pg17

What does this command do?

  • The --name flag gives the container its project-specific name.
  • The POSTGRES_DB setting creates the search_demo database during initialization.
  • The local port mapping exposes Postgres at localhost:5432.
  • The image tag selects 0.11.0-pg17.
  • Confirm Docker is running the named container by running this command:
docker ps --filter name=pg-pinecone-search

What should I see?

The output should include a row named pg-pinecone-search with port 5432 published on localhost.

Your first local service is live. Postgres is reachable at localhost:5432.

Container missing from the output?

A stale project container can be removed safely when it does not contain work you need.

  • Stop the local service already using port 5432 if Docker reports a port conflict.
  • Remove an unused container named pg-pinecone-search if Docker reports a name conflict.
  • Run the container command again after clearing the conflict.

Help me diagnose why my pg_pinecone container did not start. You can also ask the Pinecone Discord community.

Connect to search_demo

A running database needs a client session before it can accept your SQL. The image includes psql for this connection.

  • Connect to search_demo with the bundled SQL client by running this command:
docker exec -it pg-pinecone-search psql -U postgres -d search_demo

How does this connection work?

  • The docker exec command starts a process inside the running container.
  • The -it flags keep the SQL session interactive.
  • The -U postgres option selects the default database superuser.
  • The -d search_demo option selects your project database.

You should see an interactive prompt beginning with search_demo. Keep this session open for the extension check.

Cannot reach the database?

  • Confirm that your earlier Docker status output still includes pg-pinecone-search.
  • Check that the connection command uses the database name search_demo.
  • Wait a few seconds if the container has only just completed its first download.

Help me connect psql to the search_demo database.

Confirm pg_pinecone availability

An available extension has its files installed on the Postgres server. A later step activates those files inside search_demo.

Before you run this query, which extension names do you expect the image to make available?

  • Check the available extension catalog by running this SQL in your connected client:
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('vector', 'pg_pinecone');

What does this query check?

  • The name column identifies each available extension.
  • The default_version column shows the version packaged with the server.
  • The installed_version column shows whether an extension is already active in this database.
  • The filter limits the result to vector plus pg_pinecone.

You should see a row named pg_pinecone with a value under default_version.

The installed_version value can be empty because this step checks availability. A later step enables the extension in search_demo.

That catalog row confirms your extension-ready server works. You are ready to put data behind it.

Missing the pg_pinecone row?

  • Confirm that the query runs inside the search_demo prompt.
  • Compare the image tag in your original run command with pineconeteam/pg_pinecone:0.11.0-pg17.
  • Check the SQL spelling for pg_available_extensions.

Help me find why pg_pinecone is missing from the available extensions.

Your local Postgres server can see pg_pinecone. Next, you will load sample rows into search_demo and confirm their count.

Load Sample Search Data

Your local PostgreSQL container is ready. Its search_demo database has no content to search.

The next search comparisons need a stable dataset with enough rows to reveal meaningful differences. A saved SQL script also makes the load repeatable.

In this step, get ready to:
  • Create the documents table for searchable articles.
  • Populate the table with 300 sample rows.
  • Count the rows to verify the complete load.
Create the documents table

Each article needs a generated identifier. It also needs separate text fields for its title and body.

  • Switch back to the SQL client connected to search_demo.
  • Create a blank query using your client's new-query control.
  • Add the table definition by copying the SQL below into the blank query:
-- Store each article with a generated identifier.
CREATE TABLE documents (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

What Does This Table Store?

  • The id field is an identity column. PostgreSQL generates its value whenever you insert a row.
  • The primary key keeps every generated identifier unique.
  • The title field stores the short name of each article.
  • The body field stores the longer text that later searches examine.
  • Execute the table definition with your SQL client's run control.

Good progress. Your SQL client confirms that the statement completed successfully. The documents table now has a stable shape for every sample article.

  • Save the query on your Desktop as search_demo.sql.

Table Creation Failed?

  • Confirm that your SQL client is still connected to the search_demo database.
  • Check that every column definition ends with a comma except the final body definition.
  • Remove an older documents table through your SQL client's table controls if you created one during an earlier attempt.

Help me diagnose why the documents table was not created.

Load 300 sample articles

PostgreSQL's generate_series function produces a sequence of numbers. Here it supplies exactly 300 rows without requiring 300 handwritten statements.

  • Return to search_demo.sql in your SQL client.
  • Place your cursor after the CREATE TABLE statement.
  • Add the sample-data load by copying the SQL below:
-- Generate 300 rows across six searchable article topics.
INSERT INTO documents (title, body)
SELECT
    CASE
        WHEN sample_number <= 50 THEN 'Choosing shoes for long trail runs'
        WHEN sample_number <= 100 THEN 'Protecting an account with a second login step'
        WHEN sample_number <= 150 THEN 'Brewing smoother coffee at home'
        WHEN sample_number <= 200 THEN 'Charging a phone away from wall outlets'
        WHEN sample_number <= 250 THEN 'Growing herbs in a small apartment'
        ELSE 'Reducing background sound while working'
    END AS title,
    CASE
        WHEN sample_number <= 50 THEN 'Cushioned footwear helps runners stay comfortable on rocky paths.'
        WHEN sample_number <= 100 THEN 'An extra verification step keeps a user profile safer after a password leak.'
        WHEN sample_number <= 150 THEN 'A coarse grind with a slow pour can make a balanced cup without bitterness.'
        WHEN sample_number <= 200 THEN 'A portable solar panel can refill a battery pack during a camping trip.'
        WHEN sample_number <= 250 THEN 'Basil or mint can grow near a bright window with regular watering.'
        ELSE 'Over-ear headphones can soften office chatter to support concentration.'
    END AS body
FROM generate_series(1, 300) AS generated(sample_number);

How Does This Load 300 Rows?

The number sequence runs from 1 through 300. Each number becomes one inserted row.

The two CASE expressions divide those rows into six article topics. This gives later searches several meanings to rank.

  • Select the complete INSERT statement.
  • Execute the selected statement with your SQL client's run control.

That is the data load complete. Your SQL client confirms that 300 rows were inserted into documents.

  • Save search_demo.sql.

Sample Rows Not Inserted?

  • Confirm that the documents table exists under the search_demo connection.
  • Check that both CASE expressions finish with END.
  • Select only the INSERT statement before running it again.

Help me fix my 300-row sample data load.

Your saved script should now match the complete version below.

✔️ Awesome, I've got everything!

Your search_demo.sql file contains the table definition plus the complete sample-data load.

ⓧ I'd like to double check the full code

-- Store each article with a generated identifier.
CREATE TABLE documents (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

-- Generate 300 rows across six searchable article topics.
INSERT INTO documents (title, body)
SELECT
    CASE
        WHEN sample_number <= 50 THEN 'Choosing shoes for long trail runs'
        WHEN sample_number <= 100 THEN 'Protecting an account with a second login step'
        WHEN sample_number <= 150 THEN 'Brewing smoother coffee at home'
        WHEN sample_number <= 200 THEN 'Charging a phone away from wall outlets'
        WHEN sample_number <= 250 THEN 'Growing herbs in a small apartment'
        ELSE 'Reducing background sound while working'
    END AS title,
    CASE
        WHEN sample_number <= 50 THEN 'Cushioned footwear helps runners stay comfortable on rocky paths.'
        WHEN sample_number <= 100 THEN 'An extra verification step keeps a user profile safer after a password leak.'
        WHEN sample_number <= 150 THEN 'A coarse grind with a slow pour can make a balanced cup without bitterness.'
        WHEN sample_number <= 200 THEN 'A portable solar panel can refill a battery pack during a camping trip.'
        WHEN sample_number <= 250 THEN 'Basil or mint can grow near a bright window with regular watering.'
        ELSE 'Over-ear headphones can soften office chatter to support concentration.'
    END AS body
FROM generate_series(1, 300) AS generated(sample_number);
Count the loaded rows

A successful insert message confirms that the command ran. A direct count confirms that the table holds the complete dataset.

Before you run the check, how many rows do you expect the table to report?

  • Create a temporary query under the same search_demo connection.
  • Count every loaded row by running the SQL below:
-- Count every loaded document.
SELECT COUNT(*) AS document_count
FROM documents;

Why Does This Prove the Load Worked?

The COUNT(*) expression counts every row in documents. The document_count label makes the result easy to identify.

You will see document_count with a value of 300. That count proves the complete sample dataset is ready.

Count Not 300?

  • Run the saved INSERT statement once if the count is below 300.
  • Remove the documents table through your SQL client's table controls if the count is above 300.
  • Run search_demo.sql once after removing the table.

Help me get exactly 300 rows in my documents table.

Your search corpus is in place. Next, you will enable pg_pinecone for the database and build a search index over these rows.

Build the Search Index

Your documents table now holds 300 rows. Postgres still needs a fast route through the text in each row.

In this step, you enable pg_pinecone in search_demo. You then build a local text index named documents_search_idx over the body column.

In this step, get ready to:
  • Enable pg_pinecone in the search_demo database.
  • Build a text index over the documents body column.
  • Leave a repeatable SQL script with a verified index.
Enable pg_pinecone in search_demo

A Postgres extension adds new database features to the database where you enable it. Your container already has the pg_pinecone files installed.

  • Enable the extension in your current search_demo session by running this statement:
-- Enable pg_pinecone in the current database.
CREATE EXTENSION pg_pinecone;

What does this statement do?

  • The extension statement registers pg_pinecone inside the current database.
  • The extension becomes available to later queries in search_demo.

You should see a completion message confirming that the extension was created.

Extension not created?

  • Confirm that your prompt shows a connection to search_demo.
  • Check that the running container is still named pg-pinecone-search.
  • Confirm that your database role has permission to create extensions.

Help me diagnose why pg_pinecone could not be enabled in my search_demo database.

Build the local text index

pg_pinecone can use Postgres full-text search for its local search path. A matching GIN index gives Postgres a token map for the text in body.

  • Create documents_search_idx over the document text by running this statement:
-- Index the document text for local full-text queries.
CREATE INDEX documents_search_idx
ON documents
USING gin (to_tsvector('english', coalesce(body, '')));

How does this index work?

  • The to_tsvector() function turns each document body into searchable tokens.
  • The english configuration prepares those tokens for English text queries.
  • The coalesce() function treats a missing body as empty text.
  • The GIN structure maps each token to the rows that contain it.

You should see a completion message confirming that the index was created.

Index creation failed?

  • Confirm that the table name is documents.
  • Check that the searchable text column is named body.
  • Remove an older index with the same name before retrying only if you created one during an earlier attempt.

Help me diagnose why documents_search_idx could not be created on the documents table.

Save and verify the index

Your database now holds the extension plus the index. Saving both statements in search_demo.sql makes this setup repeatable.

  • Return to the search_demo.sql file from earlier.
  • Add the following section below the sample-data load statements:
-- Enable pg_pinecone in the current database.
CREATE EXTENSION pg_pinecone;

-- Index the document text for local full-text queries.
CREATE INDEX documents_search_idx
ON documents
USING gin (to_tsvector('english', coalesce(body, '')));

What belongs in your SQL file?

The extension statement prepares the database for pg_pinecone queries. The index statement creates the searchable token map for documents.body.

  • Save search_demo.sql.

✔️ Awesome, I've got everything!

Your saved SQL now includes the extension setup plus the local text index.

ⓧ I'd like to double check the full code

Compare the final search-index section of search_demo.sql with this reference.

-- Enable pg_pinecone in the current database.
CREATE EXTENSION pg_pinecone;

-- Index the document text for local full-text queries.
CREATE INDEX documents_search_idx
ON documents
USING gin (to_tsvector('english', coalesce(body, '')));

Before you check the database, do you expect both objects to appear in their Postgres catalog views?

  • Confirm the extension plus the index by running these catalog queries in your current database session:
-- Confirm that pg_pinecone is enabled in this database.
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_pinecone';

-- Confirm that the named text index exists on documents.
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'documents'
  AND indexname = 'documents_search_idx';

What do these checks prove?

  • The first query checks the extensions enabled in the current database.
  • The second query checks the indexes registered for the documents table.
  • The returned index definition confirms that documents_search_idx uses GIN over the body text.

You should see one row for pg_pinecone. You should also see one row for documents_search_idx with its index definition.

That is the search layer in place: your extension is active plus your 300 document rows now have a named text index.

Missing an extension or index row?

  • Confirm that both verification queries are running in search_demo.
  • Rerun the missing creation statement from this step.
  • Check the spelling of documents_search_idx in the creation statement.

Help me work out why my pg_extension or pg_indexes verification query returned no row.

Your document text is indexed. Next, you'll run the same searches through plain SQL matching plus pg_pinecone to compare the rows each method finds.

Compare Search Results

Your pg_pinecone index is ready. That is the infrastructure done.

A LIKE query looks for one continuous sequence of characters. Indexed full-text search in Postgres can match the important words when they appear in a different order or in different parts of a sentence.

In this step, get ready to:
  • Run three literal phrase searches with SQL LIKE.
  • Run the same three phrases through pg_pinecone.
  • Record the differences between each pair of results.
Compare the password search

The first phrase contains three words that belong to the same topic. The sample article places those words in different parts of its body.

  • Return to search_demo.sql from earlier.
  • Move to the bottom of the file.
  • Add the first query pair by copying this code:
-- Search 1 with literal substring matching.
SELECT id, title
FROM documents
WHERE lower(body) LIKE '%password reset email%'
ORDER BY id
LIMIT 5;

-- Search the same phrase through pg_pinecone.
SELECT d.id, d.title, s.score
FROM pinecone.search_docs(
    'documents',
    'password reset email',
    field => 'body',
    engine => 'local',
    k => 5
) AS s
JOIN documents AS d ON d.id = s.id::integer
ORDER BY s.rank;

What does this comparison prove?

  • The first query lowercases each body before LIKE checks for one uninterrupted phrase.
  • The pinecone.search_docs() call treats the phrase as searchable words.
  • The engine => 'local' argument keeps the search inside your local Postgres database.
  • The join retrieves each matching title from documents in ranked order.
  • Save search_demo.sql.
  • Select the two new statements in your editor.
  • Copy the selected SQL.
  • Switch back to the connected psql session from earlier.
  • Paste the copied SQL at the database prompt.
  • Press Enter to run both statements.

The literal query returns no rows because the body does not contain all three words as one phrase. The indexed query returns password-reset articles with relevance scores.

No indexed password results?

  • Check that both queries use the documents table.
  • Check that field => 'body' matches the indexed text column.
  • Confirm that the phrase remains password reset email.

Ask for help with the query output: Help me debug why my pg_pinecone password search returns no rows.

Test words spread across a sentence

The next phrase tests a billing article. Its three search words appear in one sentence without forming the exact substring used by the query.

  • Switch back to search_demo.sql.
  • Add the second query pair below the password queries by copying this code:
-- Search 2 with literal substring matching.
SELECT id, title
FROM documents
WHERE lower(body) LIKE '%invoice billing page%'
ORDER BY id
LIMIT 5;

-- Search the same phrase through pg_pinecone.
SELECT d.id, d.title, s.score
FROM pinecone.search_docs(
    'documents',
    'invoice billing page',
    field => 'body',
    engine => 'local',
    k => 5
) AS s
JOIN documents AS d ON d.id = s.id::integer
ORDER BY s.rank;

Why can the results differ?

  • The literal query searches for invoice billing page as one exact substring.
  • The indexed query looks for the individual search terms in the body.
  • The returned score measures relevance within this query.
  • The returned rank determines the displayed order.
  • Save search_demo.sql.
  • Select the two billing statements in your editor.
  • Copy the selected SQL.
  • Switch back to the connected psql session.
  • Paste the copied SQL at the database prompt.
  • Press Enter to run both statements.

The literal query returns no rows for the uninterrupted phrase. The indexed query returns invoice or billing articles whose bodies contain the three words.

Missing the billing matches?

  • Confirm that each statement ends with a semicolon.
  • Confirm that the indexed query uses ORDER BY s.rank.
  • Check that you copied invoice billing page into both queries.

Ask for help with this comparison: Help me compare my billing LIKE results with my pg_pinecone results.

Confirm the pattern across all searches

A third phrase checks whether the pattern holds for technical language. This example uses words from an article about a CSV import problem.

  • Switch back to search_demo.sql.
  • Add the final query pair below the billing queries by copying this code:
-- Search 3 with literal substring matching.
SELECT id, title
FROM documents
WHERE lower(body) LIKE '%csv header columns%'
ORDER BY id
LIMIT 5;

-- Search the same phrase through pg_pinecone.
SELECT d.id, d.title, s.score
FROM pinecone.search_docs(
    'documents',
    'csv header columns',
    field => 'body',
    engine => 'local',
    k => 5
) AS s
JOIN documents AS d ON d.id = s.id::integer
ORDER BY s.rank;

What does the final pair test?

  • The first query requires the three words to sit next to each other in the same order.
  • The second query can match a body where other words separate the search terms.
  • The LIMIT 5 clause keeps both result sets short enough to compare.
  • The shared phrase makes the matching method the only meaningful difference between the two queries.
  • Save search_demo.sql.

Before you run this pair, do you expect the literal query to return the same rows as the indexed query?

  • Select the two CSV statements in your editor.
  • Copy the selected SQL.
  • Switch back to the connected psql session.
  • Paste the copied SQL at the database prompt.
  • Press Enter to run both statements.

The literal query returns no rows for the exact substring. The indexed query returns CSV import articles because their bodies contain all three search words.

Do both searches look empty?

  • Confirm that the phrase is csv header columns in both statements.
  • Confirm that the join casts s.id to integer.
  • Check that the earlier data load still contains 300 rows.

Ask for help with the final check: Help me debug my empty CSV search results.

✔️ Awesome, I've got everything!

Your search_demo.sql file now contains all six comparison queries.

ⓧ I'd like to double check the full code

-- Search 1 with literal substring matching.
SELECT id, title
FROM documents
WHERE lower(body) LIKE '%password reset email%'
ORDER BY id
LIMIT 5;

-- Search the same phrase through pg_pinecone.
SELECT d.id, d.title, s.score
FROM pinecone.search_docs(
    'documents',
    'password reset email',
    field => 'body',
    engine => 'local',
    k => 5
) AS s
JOIN documents AS d ON d.id = s.id::integer
ORDER BY s.rank;

-- Search 2 with literal substring matching.
SELECT id, title
FROM documents
WHERE lower(body) LIKE '%invoice billing page%'
ORDER BY id
LIMIT 5;

-- Search the same phrase through pg_pinecone.
SELECT d.id, d.title, s.score
FROM pinecone.search_docs(
    'documents',
    'invoice billing page',
    field => 'body',
    engine => 'local',
    k => 5
) AS s
JOIN documents AS d ON d.id = s.id::integer
ORDER BY s.rank;

-- Search 3 with literal substring matching.
SELECT id, title
FROM documents
WHERE lower(body) LIKE '%csv header columns%'
ORDER BY id
LIMIT 5;

-- Search the same phrase through pg_pinecone.
SELECT d.id, d.title, s.score
FROM pinecone.search_docs(
    'documents',
    'csv header columns',
    field => 'body',
    engine => 'local',
    k => 5
) AS s
JOIN documents AS d ON d.id = s.id::integer
ORDER BY s.rank;

That comparison is the payoff: your database now finds relevant rows that literal phrase matching misses. Next up, you'll save these results as an artifact you can share.

Save Your Search Comparison

Your six SQL searches have already exposed differences between literal matching and the pg_pinecone search index. Those findings are still trapped in your terminal scrollback, which makes them difficult to share.

In this step, you will save all three comparisons in search_results_comparison.txt.

Each comparison will preserve the search phrase plus its paired result sets. You will also record the difference you observed.

In this step, get ready to:
  • Create a text file for your search comparison.
  • Capture all three search phrases with their paired results.
  • Explain the observed differences in the returned rows.
Create the comparison file

A consistent layout helps someone compare both search methods without reconstructing your terminal history.

  • Open search_demo.sql in the text editor you used earlier.
  • Create a plain-text file named search_results_comparison.txt beside search_demo.sql using your editor's new-file control.

You should now see both files in the same folder. The new comparison file should be blank.

Use One Repeatable Layout

  • Use Search phrase: to identify the input you tested.
  • Use LIKE results: to preserve the rows returned by literal matching.
  • Use pg_pinecone results: to preserve the rows returned by indexed search.
  • Use Observed difference: to record what changed between the result sets.
  • Switch back to the terminal session from the previous step.
  • Select the first search phrase plus the rows returned by its two queries.
  • Copy the selected text.
  • Return to search_results_comparison.txt.
  • Paste the copied text into the blank file.
  • Apply the four labels from the layout above.
  • Review the first section to confirm that each result set sits under the correct label.

What Makes a Useful Difference Note?

A useful note names a specific row that appears in only one result set. If the same rows appear in a different order, record that visible change too.

Concrete evidence makes the comparison understandable without access to your database.

  • Write a short note after Observed difference: using evidence from the first pair of results.
  • Save search_results_comparison.txt.

Good progress. Your first search comparison is now preserved outside the terminal.

Add the second comparison

Using the same layout keeps the evidence easy to scan. The next comparison should follow the structure you just created.

  • Switch back to the terminal session from earlier.
  • Select the second search phrase plus the rows returned by its two queries.
  • Copy the selected text.
  • Return to search_results_comparison.txt.
  • Paste the copied text below the first comparison.
  • Apply the same four labels to the second comparison.
  • Review the second section to confirm that the two result sets remain separate.

The second section should now show one search phrase with two clearly labeled groups of rows.

  • Write a note describing the observed difference in the second pair of results.
  • Save search_results_comparison.txt.

Your file now holds two side-by-side comparisons. The repeated layout makes their differences easier to spot.

Capture the final comparison

The third comparison completes the artifact. A final review confirms that every live search result now has a saved counterpart.

  • Switch back to the terminal session from earlier.
  • Select the third search phrase plus the rows returned by its two queries.
  • Copy the selected text.
  • Return to search_results_comparison.txt.
  • Paste the copied text below the second comparison.
  • Apply the same four labels to the third comparison.
  • Review the third section to confirm that the two result sets remain separate.

The file should now contain three sections. Each section should begin with a search phrase.

  • Write a note describing the observed difference in the third pair of results.
  • Save search_results_comparison.txt.

Before you review the finished file, how many labeled parts should each search comparison contain?

  • Review search_results_comparison.txt from top to bottom.
  • Confirm that the file appears beside search_demo.sql in your editor's file list.

You should see three search phrases. Each phrase should show the rows returned by LIKE.

Each phrase should also show the corresponding pg_pinecone rows. A note should describe the observed difference.

  • Choose the tab that matches your finished file:

✔️ My comparison is complete

That is the hard part done. Your saved comparison turns the live database demonstration into an artifact you can show to someone else.

ⓧ I want to double-check the file

  • Confirm that the file is named search_results_comparison.txt.
  • Confirm that all three search phrases are present.
  • Confirm that every phrase has a labeled LIKE result set.
  • Confirm that every phrase has a labeled pg_pinecone result set.
  • Confirm that every comparison includes a note about the returned rows.

Missing a Result Set?

  • Return to the matching query in search_demo.sql.
  • Rerun the query in the database session from earlier.
  • Copy the complete returned rows into the correct labeled section.

Ask for help if the saved query does not reproduce the result you saw earlier: Help me reconstruct the missing search comparison.

Secret mission

Audit Search Relevance

Your saved comparison shows what each search method returned. Turn those rows into a relevance scorecard that supports a search recommendation.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your local resources available, pause the running container for later, or remove the project completely. Everything runs on your machine, so there are no ongoing service charges.

Resources you used:

  • A local Docker container named pg-pinecone-search. It contains the search_demo Postgres database, the pg_pinecone extension, the documents_search_idx search index, and the 300-row documents table.
  • The local search_demo.sql and search_results_comparison.txt files.

Keep everything running

No action is needed while you are still experimenting with your searches. This option keeps the database ready for more comparisons.

  • Leave the pg-pinecone-search container running in Docker.
  • Keep search_demo.sql and search_results_comparison.txt in their current location.

You can continue querying the indexed 300-row dataset from your existing database connection.

Pause - I'll come back to this later

Pausing releases the memory used by the active container. The database state remains available inside the stopped container.

  • Return to Docker's container list.
  • Stop the pg-pinecone-search container.

Your terminal connection to search_demo closes after the container stops. You should still see pg-pinecone-search in the container list without a running process.

  • Start pg-pinecone-search from the same container list when you are ready to continue.

Your extension, search index, table rows, SQL script, and saved comparison remain ready for the next session.

Delete - I don't want to use this again

This option removes all project resources so you can start fresh. Deleting the container permanently removes the local database, extension, index, and table inside it.

  • Return to Docker's container list.
  • Stop the pg-pinecone-search container if it is still running.
  • Remove the stopped pg-pinecone-search container.

The container should disappear from Docker's container list. This confirms that search_demo and its indexed data have been removed.

The two local files remain separate from the container. Use the tab for your operating system if you also want to remove those artifacts.

macOS

  • Use Finder to locate search_demo.sql.
  • Move search_demo.sql to the Trash.
  • Use Finder to locate search_results_comparison.txt.
  • Move search_results_comparison.txt to the Trash.
  • Review the Trash for unrelated files you need to keep.
  • Empty the Trash after confirming its contents are safe to remove.
  • Confirm that neither project file remains in Finder or the Trash.

Windows

  • Use File Explorer to locate search_demo.sql.
  • Move search_demo.sql to the Recycle Bin.
  • Use File Explorer to locate search_results_comparison.txt.
  • Move search_results_comparison.txt to the Recycle Bin.
  • Review the Recycle Bin for unrelated files you need to keep.
  • Empty the Recycle Bin after confirming its contents are safe to remove.
  • Confirm that neither project file remains in File Explorer or the Recycle Bin.

Nice Work!

Nice Work!

What a finish! Your local Postgres database now searches 300 rows with pg_pinecone.

What you learned:

  • Ran a local Postgres environment in Docker through the pg-pinecone-search container.
  • Loaded 300 searchable rows into documents. Built documents_search_idx over their text content.
  • Compared three search phrases with paired LIKE queries plus pg_pinecone queries. Saved the returned rows in search_results_comparison.txt with notes on the observed differences.
  • Secret Mission: Completed the optional extension to push your local search comparison further.

Ready to quiz yourself?