Job search on Postgres and pgvector

A job matching system needs vectors and also needs everything else: employers, applications, candidates, statuses, permissions. Those relationships are relational, and putting the vectors in a separate system means every query spans two stores.

The decisive benefit is transactional consistency. Insert the job and its embedding in one transaction and they cannot disagree. With a separate vector store, a partial failure leaves an orphaned vector or a job with no vector, and reconciling that is ongoing work.

The failure that removes is the one candidates notice. A posting closing in the primary database while its vector survives in a separate index produces search results pointing at roles that no longer exist, and two dead links are enough to lose a user’s trust in the whole feed.

Do not put a single vector column on the jobs table, because you will want to embed individual requirements rather than whole postings. Use a separate table with one row per embedded chunk, referencing its parent.

Store the model name and chunking version on each row. Without that you cannot migrate models safely, and mixing vector generations produces results that look reasonable and are meaningless.

Make the active version part of the query rather than a column somebody remembers to filter on. A query that cannot return the wrong generation cannot accidentally mix two, which is a stronger guarantee than a convention every future query must observe.

• job_embeddings: job_id, chunk_index, chunk_text, embedding, model, version
• A foreign key with cascade so deleting a job removes its vectors
• An index on job_id for retrieving all chunks of one posting
• The vector index built on the embedding column

The practical trade-off is between an index that builds quickly and queries adequately, and one that builds slowly, uses more memory and queries faster with better recall. For a job board with continuous inserts, build time and incremental updates matter more than peak query speed.

Whichever you pick, the mistake to avoid is tuning for latency without measuring recall. Compare against an exact scan on a sample — an index returning results quickly while missing the best matches is invisible until someone notices the matching "got worse".

Track that number over time rather than measuring once. Recall drifts as the data distribution changes, so a parameter that was right at fifty thousand vectors is not necessarily right at five hundred thousand, and nothing will alert you.

This is where Postgres genuinely shines: your filters are ordinary SQL against columns that already exist. Active jobs, in these countries, within this salary band, posted recently — all in the same query as the similarity ordering.

Watch the planner though. With a highly selective filter, an approximate index can be the wrong choice and an exact scan over the filtered subset is faster and more accurate. Check the plan rather than assuming the vector index is always the right path.

The case that catches people is the middle one. A filter selective enough to matter but not enough for the planner to abandon the index leaves the approximate search exploring rows the filter then removes, so the query returns fewer results than requested — which reads as a thin market rather than a plan problem.

Job data turns over constantly, which makes freshness a correctness property rather than housekeeping. A posting that becomes searchable six hours after publication is one the early applicants already took, and a filled role still in the index costs trust.

Embed on insert where you can and run a sweeper for whatever the insert path missed. A pending-embedding flag processed in batches with retries degrades gracefully during a provider outage, where a nightly full scan hides the backlog until postings go missing.

Prefer soft deletion with an active flag over hard deletes from an approximate index. Removing rows is expensive and sometimes incomplete, and filtering on a boolean is both cheaper and easier to reason about — with space reclaimed on a schedule instead.

The honest limits are index build time as the corpus grows, memory pressure from a large index competing with your ordinary workload, and query concurrency at a level where vector search starts affecting the rest of the database.

Those are measurable. Watch build duration, index size against available memory, and p95 latency under real concurrency — and move when the numbers say so rather than when the corpus merely feels large.

Try a read replica first, because it is usually enough. A replica dedicated to matching removes the contention that triggers most of these thresholds without introducing a second data model, a second consistency story or another system to operate.