CleanJobData

Building Job Search with PostgreSQL Full-Text Search

CleanJobData Engineering

TL;DR

PostgreSQL's tsvector, websearch_to_tsquery, and ts_rank_cd cover keyword search, relevance ranking, and freshness decay well enough that most job boards don't need Elasticsearch. Sync CleanJobData listings into a Postgres table with a generated search_vector column and a GIN index, then combine full-text search with normal SQL filters (location, salary, remote) rather than trying to express filtering inside the search query itself. Move to a dedicated search engine only once ranking sophistication or query volume genuinely outgrows what Postgres can do.

You do not need Elasticsearch to build a strong job search experience. For many job boards, PostgreSQL full-text search is enough, especially when your job data is already normalized by CleanJobData.

The backend already uses PostgreSQL search vectors for title relevance and freshness. You can apply the same pattern in your own database when syncing CleanJobData listings.

Job search has a few common requirements:

PostgreSQL gives you all of that with tsvector, websearch_to_tsquery, ts_rank_cd, and normal SQL filters.

Syncing CleanJobData Data

A practical jobs table can mirror the API fields you need:

CREATE TABLE jobs (
  id bigint PRIMARY KEY,
  title text NOT NULL,
  location text,
  locations jsonb,
  application_url text NOT NULL UNIQUE,
  published timestamptz,
  has_remote boolean DEFAULT false,
  employment_type text,
  salary_min bigint,
  salary_max bigint,
  salary_currency text,
  salary_text text,
  experience_level text,
  experience_levels text[],
  company jsonb,
  description text
);

When you ingest a job, store the original API object or selected fields. Keep application_url unique so you can avoid duplicates.

Add a Search Vector

Use a generated tsvector column for searchable text:

ALTER TABLE jobs
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  to_tsvector(
    'english',
    coalesce(title, '') || ' ' ||
    coalesce(location, '') || ' ' ||
    coalesce(salary_text, '') || ' ' ||
    coalesce(description, '')
  )
) STORED;
 
CREATE INDEX jobs_search_idx ON jobs USING GIN(search_vector);

For most job boards, title and description are enough. Including location and salary_text can help when users search for compensation or place names.

Querying Jobs

A basic search query looks like this:

SELECT
  id,
  title,
  location,
  salary_min,
  salary_max,
  salary_currency,
  experience_level,
  has_remote
FROM jobs
WHERE search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY
  ts_rank_cd(search_vector, websearch_to_tsquery('english', $1)) DESC,
  published DESC
LIMIT 20;

For production, compute the tsquery once and reuse it:

WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
  jobs.*,
  ts_rank_cd(search_vector, q.query) AS rank
FROM jobs, q
WHERE search_vector @@ q.query
ORDER BY rank DESC, published DESC
LIMIT 20;

Ranking with Freshness

CleanJobData ranks title matches with a freshness decay so newer jobs do not disappear behind older but slightly better keyword matches. You can apply the same idea:

WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
  jobs.*,
  ts_rank_cd(search_vector, q.query)
    / power(greatest(extract(epoch from (now() - coalesce(published, now()))) / 86400.0, 0.0) + 1.0, 1) AS rank
FROM jobs, q
WHERE search_vector @@ q.query
ORDER BY rank DESC, published DESC
LIMIT 20;

This keeps search results relevant while still rewarding recent postings.

Combining Search with Filters

PostgreSQL shines when keyword search is combined with structured filters:

WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
  jobs.*,
  ts_rank_cd(search_vector, q.query) AS rank
FROM jobs, q
WHERE search_vector @@ q.query
  AND has_remote = $2
  AND experience_level = ANY($3::text[])
  AND (salary_max >= $4 OR salary_max IS NULL)
  AND (salary_min <= $5 OR salary_min IS NULL)
ORDER BY rank DESC, published DESC
LIMIT 20;

This mirrors the CleanJobData filter model: search handles relevance, while normalized fields handle precise filtering.

Location Filters

If your product needs a city-name autocomplete box on top of this, pair it with geo-suggest rather than rolling your own — it resolves free-text input to the same city_id values used below.

Use normalized location IDs instead of string matching:

SELECT *
FROM jobs
WHERE locations @> '[{"city_id": 123}]'::jsonb
ORDER BY published DESC;

For country-level filtering, use country_id or country_code in the locations JSON array. Avoid matching raw strings like location ILIKE '%CA%'; it will mix California, Canada, and unrelated text.

When to Add a Dedicated Search Engine

PostgreSQL is a strong default. Consider a dedicated engine only when you need:

  • extremely large catalogs with complex synonym handling
  • typo tolerance beyond PostgreSQL capabilities
  • custom analyzers for many languages
  • heavy faceted search over millions of documents
  • sub-10ms latency at very high concurrency

For most vertical job boards, PostgreSQL full-text search plus CleanJobData normalization is simpler, cheaper, and easier to operate.

Start with:

  1. Sync normalized CleanJobData jobs into Postgres.
  2. Add a generated search_vector.
  3. Index it with GIN.
  4. Combine websearch_to_tsquery with structured filters.
  5. Rank by relevance and freshness.
  6. Add pagination with cursor or keyset logic.

That gives you a fast, relevant job search without introducing a separate search stack.