Building Job Search with PostgreSQL Full-Text Search

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 JobsDataAPI.

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

Why PostgreSQL Works for Job Search

Job search has a few common requirements:

• keyword matching against titles and descriptions
• filtering by location, salary, seniority, and remote status
• ranking newer or more relevant jobs higher
• simple deployment and operational cost

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

Syncing JobsDataAPI 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

JobsDataAPI 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 JobsDataAPI filter model: search handles relevance, while normalized fields handle precise filtering.

Location Filters

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 JobsDataAPI normalization is simpler, cheaper, and easier to operate.

Recommended Setup

Start with:

1. Sync normalized JobsDataAPI 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.