Job matching on plain Postgres

A matching system spread across a search cluster, a vector store and the primary database has three consistency problems and three things to operate. Every one of those is a source of the failure where results reference a job that no longer exists.

Postgres does full-text search, vector similarity, JSON, and the relational joins you need for employers, applications and permissions. For a job board below serious scale, that combination is not a compromise — it is less to get wrong.

Transactions are the underrated part. A posting closing and disappearing from search in the same commit is a property you get for free here and have to engineer carefully in any split architecture, and it is precisely the failure candidates notice.

Built-in text search with proper configuration handles stemming, ranking and phrase queries perfectly well. For exact terms — a certification, a framework, a clearance level — it is not merely adequate but correct, and it is instantaneous on an indexed column.

Generate the search vector as a stored column so it stays in sync automatically. Hand-maintained denormalised search text drifts, and drift in a search index is invisible until someone reports that a job cannot be found.

Weighting the document sections is a cheap and large improvement. Giving the title the highest weight, requirements the next and the company boilerplate the least means a posting whose title is the search term outranks one that mentions it in passing, which is what a user expects and a flat index does not deliver.

Skill overlap, seniority distance, location feasibility and recency can all be expressed as SQL and computed in one pass. That is fast, transactional and trivially testable, and it avoids fetching thousands of rows into application code to score them there.

The limit arrives when scoring needs judgement rather than arithmetic — whether transferable experience counts, whether a domain is adjacent. That belongs in a model, applied to a shortlist the query produced.

Keep the weights in a table rather than in the query text. Tuning then becomes a row update you can change per experiment and roll back, instead of a deployment — and it makes it possible to answer why a given job ranked where it did three weeks ago.

• Hard filters in the WHERE clause, so ineligible rows never score
• Arithmetic scoring in SQL, over the filtered set
• Model judgement in the application, on the top rows only
• One query per request, not one query per candidate job
• Weights in a table, so tuning is data rather than a deploy

Some things are costly to compute and change slowly: a candidate’s aggregated skill profile, an employer’s response statistics, a job’s normalised requirement list. Recomputing them per request is waste.

Materialised views refreshed on a schedule handle this well. Refresh concurrently so reads are not blocked, and keep a timestamp so you can tell whether a surprising result came from stale data.

A concurrent refresh needs a unique index on the view, which is easy to forget until the first refresh blocks every read for a minute in production. It is worth adding at the same moment the view is created rather than after the incident.

Most disappointing performance here comes down to a query that could not use an index. A generated tsvector column with a GIN index over it, an index supporting the common eligibility filters, and an appropriate vector index are the three that matter.

Read the plan rather than assuming. When a filter is highly selective — one country, one employment type, active only — an exact scan over the surviving rows frequently beats an approximate vector index on both latency and accuracy, and the planner’s choice depends on statistics that change as the table grows.

Approximate vector indexes are the one place where a wrong result looks like a correct one. Recall degrades silently as parameters and data distribution drift, so comparing against an exact scan on a sample periodically is the only way to notice.

Staying on Postgres is right until specific things happen, and the point of naming them in advance is to avoid both premature migration and a panicked one.

Watch for vector index build time interfering with writes, matching queries consuming capacity your transactional workload needs, and p95 latency that tuning no longer moves. Those are measurable thresholds. "The data is getting big" is not.

There is an intermediate step before leaving, too, and it is usually enough: a read replica dedicated to matching. It removes the contention that triggers most of these thresholds without introducing a second data model or a second thing to operate.