How to Add Semantic Search to a PostgreSQL Table (Without pgvector)

You have a products table in Postgres. Someone searches for "waterproof jacket for hiking". Your query runs WHERE title ILIKE '%waterproof%', and the Gore-Tex shell you sell doesn't show up, because its title says "Alpine shell jacket".

Postgres search works by matching the words stored in your rows. The people searching usually don't know those words, so they describe what they want instead, and the results come back empty or wrong.

This post shows how to make a Postgres table searchable by meaning with the Vecstore PostgreSQL integration. You connect the database, pick the columns and sync. Nothing gets installed on your database and you don't need to write any embedding code.

The short version:

• LIKE and full-text search match words. Semantic search matches meaning, so different wording, typos and other languages still find the right rows.
• The usual DIY route is pgvector plus an embedding job. It works, but you end up owning a pipeline.
• With Vecstore you connect a table, choose text, image and metadata columns, and each row becomes a searchable document.
• You decide when to sync. Sync changes imports only edited rows. Full resync re-reads everything and removes deleted rows.

Why Postgres Search Misses Things

Postgres gives you two built-in ways to search text:

• LIKE / ILIKE looks for an exact substring. "couch" won't find "sofa". "beginer" won't find "beginner".
• Full-text search (tsvector, to_tsquery) handles word forms and ranking, so "running" can match "run". It still matches words, not meaning. "Flat near the river" won't find "waterfront apartment".

Both are fine when people search for things by name: an order number, an email, a SKU. They fall short when people describe what they want, which is most of the time in a search box. For a longer comparison, see keyword vs semantic search.

The DIY Route: pgvector

The common fix is pgvector. You add a vector column, generate embeddings for each row with a model, store them, build an index and query by distance.

It works, and for some teams it's the right choice. It also means you're now responsible for:

• Picking and hosting an embedding model, or paying for an embedding API.
• A job that embeds every new or edited row, plus backfills and retries when it fails.
• Re-embedding everything when you change models.
• Index tuning, and the memory it takes on your primary database.
• Image search is a separate model and a separate pipeline.

If search isn't the main thing your team works on, that's a lot to keep running. We wrote more about this tradeoff in you don't need a vector database.

The Vecstore Route

Vecstore reads rows from your table, turns them into searchable documents, and serves search over an API. Your database stays the source of truth and Vecstore never writes to it.

Step 1: Create a read-only user

Give Vecstore its own user that can only read the tables you want to search:

CREATE USER vecstore_reader WITH PASSWORD 'a-long-random-password';
GRANT CONNECT ON DATABASE shop TO vecstore_reader;
GRANT USAGE ON SCHEMA public TO vecstore_reader;
GRANT SELECT ON public.products TO vecstore_reader;

Your connection string then looks like postgresql://vecstore_reader:password@db.example.com:5432/shop.

Step 2: Connect the table

Sign up for Vecstore (100 free credits, no card required), open Integrations in the dashboard, choose PostgreSQL and click Connect table.

Paste the connection string and click Test connection. Vecstore lists the tables that user can see. Pick one and it reads the columns.

The connection string is stored server side and never shown again. The dashboard only shows the host.

Step 3: Pick your databases

Vecstore keeps documents in databases. Pick a text database to search by words, an image database to search by images, or both. You can create one from the same screen if you don't have one yet.

Step 4: Choose how rows are matched

• Key column: a primary key or other unique column. It ties each row to its document, so edits update the right one.
• Updated at column (optional): a timestamp, date or integer that goes up when a row changes, like updated_at. It lets you sync only changed rows later.
• Image column: if you picked an image database, the column that holds http(s) image URLs.

Step 5: Choose your columns

Each column can do two things:

• Searchable (up to 20): combined into the text people search. Usually title, description, tags.
• Metadata (up to 50): returned with each result and usable in filters. Usually price, category, stock.

Click Connect and import. Vecstore imports every row in the background and shows progress on the table's page.

Keeping It in Sync

Vecstore doesn't poll your database. You decide when to sync, from the dashboard or by calling the sync endpoint from your own code. There are two modes:

A setup that works for most teams:

• Run Sync changes after your app writes, or from a cron job every few minutes.
• Run a Full resync once a day or once a week to clear out documents for deleted rows.

If the table has no updated at column, every sync is a full resync. That's fine for small tables. For large ones, adding an updated_at column with a trigger is worth it:

ALTER TABLE products ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_touch BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Searching Your Rows

Once the import finishes, your rows are documents in your Vecstore databases. Try a few searches from the database page in the dashboard, then call the search API from your app. Each result comes back with the metadata columns you picked, so you can render it without another query to Postgres, or use the key to fetch the full row.

Some things that now work which didn't before:

• "waterproof jacket for hiking" finds the Gore-Tex shell.
• "invoice paid twice" finds a support ticket titled "Duplicate charge on order #2231".
• "beginer guitar" still finds the starter kit even with the typo.
• With an image database, "red leather armchair" finds rows whose photos show one, even if the text never says "red".

Tips for Better Results

Pick columns with real words. Titles, descriptions and tags help. IDs, codes and timestamps don't, so leave them out of searchable columns and add them as metadata if you need them.

Keep searchable text focused. You can pick up to 20 columns, but a few that describe the row usually work better than everything in the table.

Put filter fields in metadata. Price, category and stock work better as metadata, where you can filter on exact values.

Watch what people search. Turn on query logging in your database settings and searches show up under Analytics. It tells you what people look for and the words they use.

Wrapping Up

Postgres search only finds rows that contain the words people type. Semantic search finds rows that match what they mean, and with Vecstore you can add it without building an embedding pipeline yourself.

Connect a table with the Vecstore PostgreSQL integration, pick your columns, and sync when your data changes. Sign up to try it.

FAQ

Do I need pgvector or any extension?

No. Vecstore reads your rows over a normal connection and handles embeddings and indexing on its side. Nothing is installed on your database.

Does Vecstore write to my database?

No. It only reads. Use a read-only user that can see just the tables you want to search.

How often does my data sync?

Whenever you sync it, from the dashboard or by calling the sync endpoint from your code. A changes sync imports only rows edited since the last sync. A full resync re-reads every row and removes documents for deleted rows.

Can I search images stored in Postgres?

Yes, if a column holds http(s) image URLs. Choose it as the image column and connect an image database.

What if my table has no unique column?

Add a primary key first. Vecstore needs a unique column to match rows to documents.