> ## Documentation Index
> Fetch the complete documentation index at: https://jobo.world/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Build a search page on your Postgres feed

> Keyword search, filters, newest-first lists, paging and filter counts straight from the table your Postgres, Supabase or Bolt outbound feed writes — and which indexes make each one fast.

A Postgres outbound feed keeps a `jobs` table in your database current. With the right indexes, that table can power a job board's search page directly: keyword search, filters, newest-first lists and filter counts, all in plain SQL.

## Turn on the indexes

Open your feed's **Settings**, go to **Where jobs land**, and tick the searches you run under **Indexes**. Jobo adds them to the table on the next sync, without blocking reads or writes. Untick one and Jobo removes it.

| Tick | Makes this fast |
| - | - |
| Keyword search | Searching titles and descriptions |
| Newest jobs first | Sorting by posting date, "posted in the last 7 days" |
| 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 |
| 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 some disk space and slows writes a little, so tick only what your page uses.

<Note>
  Feeding a table you created yourself? Jobo adds the indexes when its database user owns the table. If it doesn't, the setup page shows the `CREATE INDEX` SQL to run as the table's owner — the queries on this page then work the same way, with your column names.
</Note>

<Warning>
  Postgres only uses an index when the query is written the way the index expects. Copy the `WHERE` clauses below as they are — especially the keyword search expression, which must match character for character.
</Warning>

***

## Keyword search with filters, newest first

One query covers the core of a search page. Postgres combines the indexes it needs, so filters can be stacked freely.

```sql theme={null}
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 — `Full-time`, `Part-time`, `Contract`, `Internship`, `Freelance`, `Temporary`; `Remote`, `Hybrid`, `On-site`.

<Tip>
  To rank by relevance instead of date, order by `ts_rank(<the same to_tsvector expression>, websearch_to_tsquery('english', 'registered nurse')) DESC`. Ranking is computed on the matching rows, so keep filters narrow and always set a `LIMIT`.
</Tip>

## The other filters

```sql theme={null}
-- Company: by name, website 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' = 'year'
  AND (compensation->>'min')::numeric >= 100000

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

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

Location, company and skills filters match exact values inside the JSON. `{"city": "Berlin"}` matches a job in Berlin; it does not match "berlin" or "Berlin, Germany".

## Paging

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

```sql theme={null}
-- 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;
```

`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:

```sql theme={null}
SELECT workplace_type, count(*)
FROM jobs
WHERE to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
        @@ websearch_to_tsquery('english', 'registered nurse')
GROUP BY workplace_type;
```

Run one per filter group. These are quick while the matching set is in the tens of thousands; across millions of matching rows they take seconds.

## Pull only what changed

To keep a cache or second system current, ask for the jobs that changed since your last run:

```sql theme={null}
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 and type-ahead** — "enginer" finding "engineer"
* **Instant filter counts** across millions of jobs
* **Radius search** — "within 50 km of Berlin"
* **Search tuned per language** — keyword search here stems every job as English


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.