Build a search page on your Postgres feed

A Postgres, Supabase or Bolt outbound feed keeps a jobs table current in your database. With the indexes below, plain SQL over that table can serve a job board’s search page. To set up the feed, start with the Postgres, Supabase or Bolt destination guide.

Turn on the indexes

When you create the feed, tick the searches you run under Indexes in step 3, Where jobs land. On an existing feed, open its Settings tab and find Indexes under Connection and data. Jobo adds the indexes on the next sync without blocking reads or writes, and removes any you untick.

Index	Makes this fast
Keyword search	Searching titles and descriptions
Title search	Titles containing what someone types, even part of a word (needs the `pg_trgm` extension)
Newest jobs first	Sorting by posting date, “posted in the last 7 days”, paging through the results
Live jobs, newest first	Newest-first lists that skip expired jobs (offered when the feed keeps expired jobs)
Recently changed	Fetching only jobs that changed since you last looked
Location	City, region or country filters
Company	Company name, website or industry filters
Job type	Employment type, remote or on-site, seniority
Occupation	Job category, subcategory or one occupation
Skills	Required or preferred skills
Salary	Pay floor within one currency and pay period
Language	The language the job is written in

Each index uses disk space and slows writes a little, so tick only what your page uses. Feeding a table you created yourself? Jobo adds the indexes only when its database user owns the table. Otherwise the Indexes panel shows the CREATE INDEX SQL to run as the table’s owner, and the queries below work the same with your column names.

Postgres uses an index only when the query matches the indexed expression; otherwise the query still runs, only slowly. Copy the WHERE clauses below as written. The keyword search expression must match character for character.

Keyword search with filters, newest first

Postgres combines the indexes it needs, so you can stack filters freely. Each line’s comment names the index it uses.

SELECT id, title, company->>'name' AS company, locations, date_posted
FROM jobs
WHERE to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
        @@ websearch_to_tsquery('english', 'registered nurse')   -- Keyword search
  AND workplace_type = 'Remote'                                  -- Job type
  AND employment_type = 'Full-time'                              -- Job type
  AND locations @> '[{"country": "United States"}]'              -- Location
  AND date_posted >= now() - interval '30 days'                  -- Newest jobs first
ORDER BY date_posted DESC
LIMIT 20;

websearch_to_tsquery accepts what people type into a search box: nurse -travel, "night shift", nurse or midwife. Word forms match too, so nurse also finds nurses. Job type values are the ones shown on the job:

employment_type: Full-time, Part-time, Contract, Internship, Freelance, Temporary
workplace_type: Remote, Hybrid, On-site

Rank by relevance

The index finds matches but can’t rank them. ts_rank re-reads the whole description of every match, so ranking a broad search like sales across a large table takes seconds. Rank on the title instead, which stays fast because titles are short. Jobs whose title matches come first, then jobs that match only in the description, newest first:

SELECT id, title, date_posted
FROM jobs
WHERE to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
        @@ websearch_to_tsquery('english', 'registered nurse')
ORDER BY ts_rank(to_tsvector('english', coalesce(title, '')),
                 websearch_to_tsquery('english', 'registered nurse')) DESC,
         date_posted DESC
LIMIT 20;

Title search as you type

Keyword search matches whole words. To match part of a title as someone types (engin finding Software Engineer), tick Title search and use ILIKE:

SELECT id, title, date_posted
FROM jobs
WHERE title ILIKE '%engin%'
ORDER BY date_posted DESC
LIMIT 20;

This index needs Postgres’s pg_trgm extension. Jobo never installs extensions; if your database lacks it, the Indexes panel says so. Run this once as a database owner, and the index builds on the next sync:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Supabase, Neon and most hosted Postgres allow it. Searches shorter than three characters can’t use the index, so wait for a third character before querying. The same index catches typos in titles. % keeps titles that are similar enough, and similarity ranks them:

SELECT id, title
FROM jobs
WHERE title % 'enginer'
ORDER BY similarity(title, 'enginer') DESC
LIMIT 10;

The other filters

-- Company: by name or industry
WHERE company @> '{"name": "Acme"}'
WHERE company @> '{"industries": ["Software"]}'

-- Skills: a must-have skill
WHERE qualifications @> '{"must_have": {"skills": [{"name": "Python"}]}}'

-- Salary: a floor, within one currency and pay period
WHERE compensation->>'currency' = 'USD'
  AND compensation->>'period' = 'yearly'
  AND (compensation->>'min')::numeric >= 100000

-- Language: an ISO 639-1 code
WHERE language = 'en'

-- Occupation: a category, a subcategory, or one occupation by id
WHERE occupation @> '{"category": "Healthcare"}'
WHERE occupation @> '{"subcategory": "Nursing"}'
WHERE occupation @> '{"id": "esco:8d3e8aaa-791b-4c75-a465-f3f827028f50"}'

-- Live jobs only, newest first (feeds that keep expired jobs)
WHERE expired_at IS NULL
ORDER BY date_posted DESC

Pay periods are hourly, daily, weekly, monthly and yearly. Location, company, skills and occupation filters match exact values inside the JSON. {"city": "Berlin"} matches a job in Berlin, but not “berlin” or “Berlin, Germany”.

Occupation

The JSONB occupation column holds each job’s occupation, or NULL for a job that isn’t classified. Jobo adds the column to tables it created on the next sync. Rows written before then stay NULL until the job is delivered again or you run a full resync.

Unlike the API filters, these comparisons are case-sensitive. Use the category and subcategory exactly as they appear on the job (Healthcare, not healthcare).
Filter one occupation by its id, never its name: names can improve over time, IDs don’t. GET /api/occupations lists every value.
For speed on a large table, tick Occupation under Indexes and write the filters with @> as above. occupation->>'id' = '…' can’t use the index.
On a table you created yourself, map occupation to a jsonb column. json and text columns can’t hold this index.

If you built an occupation index by hand before the tick existed, such as jobs_occupation_idx, drop it once Jobo’s is ready. Two identical indexes only slow down writes.

-- Jobo's index is ready when this returns true
SELECT ix.indisvalid
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE ix.indrelid = 'jobs'::regclass AND i.relname LIKE 'jobo\_occupation\_%';

-- Then drop the one you built
DROP INDEX CONCURRENTLY IF EXISTS jobs_occupation_idx;

Paging

Page from the last row you showed, not with OFFSET. OFFSET 10000 still reads the 10,000 rows it skips; this reads only the next page.

-- First page
SELECT id, title, date_posted FROM jobs
WHERE date_posted IS NOT NULL
ORDER BY date_posted DESC, id DESC
LIMIT 20;

-- Next page: pass the date_posted and id of the last row you showed
SELECT id, title, date_posted FROM jobs
WHERE date_posted IS NOT NULL
  AND (date_posted, id) < ($1, $2)
ORDER BY date_posted DESC, id DESC
LIMIT 20;

Keep ORDER BY date_posted DESC, id DESC exactly as written:

Most jobs have a posting date without a time of day, so thousands can share one date_posted. The id breaks those ties, so no job is skipped or shown twice.
The Newest jobs first index includes id, so each page reads only its 20 rows.
date_posted IS NOT NULL keeps jobs without a posting date from crowding the top of a newest-first list.

Filter counts

Counts like “Remote (1,234)” are a GROUP BY over the same WHERE clause as the results:

SELECT workplace_type, count(*)
FROM jobs
WHERE to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
        @@ websearch_to_tsquery('english', 'registered nurse')
GROUP BY workplace_type;

The same works for any JSON field, for example a count per job category:

SELECT occupation->>'category' AS category, count(*)
FROM jobs
WHERE occupation IS NOT NULL
GROUP BY occupation->>'category';

Run one query per filter group. They’re quick while the matching set is in the tens of thousands, and take seconds across millions of matching rows.

Pull only what changed

To keep a cache or second system current, fetch the jobs that changed since your last run. Tick Recently changed to make this fast.

SELECT * FROM jobs WHERE updated_at > $1 ORDER BY updated_at;

When to use a search engine instead

Postgres with these indexes suits a search page over up to a few hundred thousand jobs. If you need any of the following, add an Elasticsearch, Algolia or Meilisearch outbound feed alongside this one:

Typo tolerance across descriptions (title search above covers titles only)
Instant filter counts across millions of jobs
Radius search, such as “within 50 km of Berlin”
Search tuned per language (keyword search here stems every job as English)

To write the jobs into your database with your own code instead of an outbound feed, see Sync a database.

Get started

How-to guides

Build with AI assistants

Connectors

Resources

Build a search page on your Postgres feed

Turn on the indexes

Keyword search with filters, newest first

Rank by relevance

Title search as you type

The other filters

Occupation

Paging

Filter counts

Pull only what changed

When to use a search engine instead

​Turn on the indexes

​Keyword search with filters, newest first

​Rank by relevance

​Title search as you type

​The other filters

​Occupation

​Paging

​Filter counts

​Pull only what changed

​When to use a search engine instead

Turn on the indexes

Keyword search with filters, newest first

Rank by relevance

Title search as you type

The other filters

Occupation

Paging

Filter counts

Pull only what changed

When to use a search engine instead