Classifieds Marketplace System Design: Expiry, Geo Search and Scam Checks

Buyers see things that are already gone, far away or fake. In classifieds marketplace system design, a listing is perishable: it sells, expires or turns out to be a scam, and every copy of it must follow.

Picture a common setup. A city classifieds site in Bengaluru carries 2 lakh (200,000) live ads for phones, furniture, bikes and flats. The numbers are made up, but every failure below shows up in our tests.

A buyer near Koramangala searches for a used phone within 3 km. Their screen shows a phone sold yesterday, a flat whose ad expired last week, and the same 2 BHK flat posted twice, once at a suspiciously low rent.

Each problem has a different cause. The sold phone is a stale copy in the search index. The expired flat slipped through because the query trusted a status that a late job had not changed. The double flat is a scammer copying a real ad.

This guide builds the site one part at a time and tests each part. We ran the SQL on PostgreSQL 16.15 with PostGIS 3.4.2, and the geo commands on Redis 7.0.15.

You need seven words. Each one is tied to the Bengaluru site.

• Listing: one ad, such as a used phone with a title, price, photos, category and map location.
• Lifecycle: the states a listing moves through, from draft to active to sold, expired or removed.
• Source of truth: the one database whose copy wins when copies disagree. Here it is a PostgreSQL listings table.
• Spatial index: an index on map points that finds rows inside an area without reading every row. PostGIS adds one to PostgreSQL.
• Search index: a separate copy of listings arranged by words, so a query like used iphone 13 is fast. It is updated after the database, not with it.
• Near-duplicate: two ads that are almost the same, such as one copied with a new price. Exact matching misses them.
• Boost: paid or free extra visibility for a listing, such as a promoted slot near the top.

Classifieds marketplace system design is planning a site where people post local ads and others find them by place, words and price. The hard parts are freshness, location and trust, not raw traffic.

Compare it with a question-and-answer site. There, old answers stay useful for years. Here, a phone ad is worth something for a few weeks and is harmful once sold, because buyers waste trips and lose trust.

So the design starts from one rule. The listings table is the source of truth. Every other copy may lag by a few seconds but must catch up, and the listing's own page always reads the table.

Changes reach the copies through an outbox, a table of changes waiting to be sent. Each outbox row is written in the same transaction as the listing change it describes.

Reads far outnumber writes. In our made-up site, sellers post about 20,000 ads a day, and buyers run a few million searches. That is why searches go to copies built for reading, while writes stay in one database.

A listing moves through fixed states, and each move is one guarded UPDATE that only succeeds from the right starting state. Expiry is both a filter on every read and a batch job that tidies up later.

The naive version stores a status and lets the app set any value. That is wrong, because two clicks, a retry and the expiry job can race. A sold phone can become active again after a late retry.

The fix is to put the expected old state in the WHERE clause. Marking sold says: set status to sold where the id matches and status is active. A second identical click then updates 0 rows.

We ran these guards on PostgreSQL 16.15. Marking an expired listing sold updated 0 rows. Renewing it updated 1 row. Two sold clicks in a row updated 1 row, then 0. A CHECK constraint rejected an unknown status outright.

Every successful move also adds 1 to a version column. The version is how the copies later tell an old change from a new one.

Expiry is a filter first and a job second

Every buyer query includes expires_at later than now(). So an ad stops showing the moment its time passes, even if no job has touched it. The job then tidies status and tells the copies.

Pause and predict: in our 2 lakh row table, how many active rows were already past their expiry before the job ran? And how many did buyers see?

There were 34,919 such rows. Buyers saw none of them, because the read filter hid them all. The job then expired all 34,919 in 7 batches of up to 5,000. The code below uses 1,000; pick a size that keeps each transaction short.

Here is one batch, exactly as we ran it. BETWEEN '-infinity' AND now() simply means the expiry time is now or earlier.

-- One expiry batch. Run it in a loop until it inserts 0 rows.
WITH due AS (
  SELECT id FROM listings
  WHERE status = 'active'
    AND expires_at BETWEEN '-infinity' AND now()
  ORDER BY expires_at
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
), expired AS (
  UPDATE listings l
  SET status = 'expired', version = l.version + 1
  FROM due
  WHERE l.id = due.id
  RETURNING l.id, l.version
)
INSERT INTO search_outbox (listing_id, version)
SELECT id, version FROM expired;

What just happened? The WITH query picked up to 1,000 due listings and locked them. SKIP LOCKED passes over rows another worker holds, so two expiry workers do not wait on each other. The UPDATE and the outbox insert commit together.

A partial index makes the search for due rows cheap. It covers only active rows, defined as CREATE INDEX ON listings (expires_at) WHERE status = 'active'. On our table it was 3,096 kB, and EXPLAIN showed it under the lock step.

Use the same job to send reminders. A listing that expires in three days can trigger a renew-or-mark-sold message, which keeps the site fresh without moderators.

Use a spatial index and a distance filter that can use it. In PostGIS that is ST_DWithin on a geography column with a GiST index, the usual PostGIS index type for points and shapes.

A geography column stores points as longitude and latitude on the Earth, and distances come back in metres. The PostGIS docs say ST_DWithin includes a bounding-box comparison that uses any available index.

The naive distance filter reads every row

The naive version computes the distance from the buyer to every listing and keeps those under 3,000 metres. It looks correct, and it is, but it cannot use the index.

Pause and predict: both queries return the same rows. How much slower is the naive one on 2 lakh listings?

Both returned the same 570 listings. The naive ST_Distance filter ran a parallel sequential scan, reading every row, and took 877 ms warm and 4,444 ms on a cold first run. ST_DWithin used the GiST index and took 140 to 160 ms.

SELECT id, title, price
FROM listings
WHERE ST_DWithin(location, ST_MakePoint(77.63, 12.95)::geography, 3000)
  AND status = 'active'
  AND category = 'mobiles'
  AND expires_at BETWEEN now() AND 'infinity';

Note the order inside ST_MakePoint: longitude first, then latitude. Swapped, our buyer landed near Svalbard in the Arctic, 8,012 km away, and the query quietly returned 0 rows.

Make the index match the question

The plain index still found 4,842 candidates in the box, across every category and status, before filtering to 570. Buyers almost always ask for active listings in one category.

So we built a partial GiST index on category and location, covering only active rows. The btree_gist extension is what lets a plain text column such as category sit inside a GiST index.

The same query then found 759 to 1,000 candidates and ran in 4 to 26 ms on our test machine. The partial index was 13 MB against 14 MB for the full one.

One catch comes from the PostgreSQL docs. A partial index is matched when the query is planned. A generic plan for a prepared statement with status as a parameter cannot use it.

In our runs, the default setting gave custom plans that used the partial index, 9 times out of 9. A forced generic plan fell back to the plain index. PostgreSQL may switch to generic plans after a few runs, so write status = 'active' into the SQL text.

Nearest first, and when Redis helps

For a nearest-first list, PostGIS has a KNN (k-nearest-neighbour) distance operator that returns the closest rows first. The docs say the index is used only when that operator is in ORDER BY.

The docs add that geography KNN measures on a sphere, not the spheroid. The spheroid is a slightly flattened Earth shape, more accurate than a perfect sphere, and it is what ST_Distance uses by default.

In our run the KNN distances differed from the spheroid ones by at most 1 metre, for listings within 350 metres. That is fine for ordering ads, but use ST_Distance for any number you print.

Redis GEO commands are an optional fast layer for hot pages, such as a city's nearby phones page. GEOADD stores points in a sorted set, and GEOSEARCH finds members in a radius or box.

GEOADD geo:blr:mobiles 77.6408 12.9784 listing:101 77.6245 12.9352 listing:102 77.6474 12.9116 listing:103 77.5938 12.9250 listing:104 77.7500 12.9698 listing:105
GEOSEARCH geo:blr:mobiles FROMLONLAT 77.6300 12.9500 BYRADIUS 5 km ASC WITHDIST
ZREM geo:blr:mobiles listing:102

With five listings loaded, the 5 km search on Redis 7.0.15 returned four, nearest first: listing:102 at 1.7508 km, then 101, 103 and 104. Whitefield, 13.19 km away, was correctly left out.

Two details catch people out. First, COUNT 2 ANY returned listing:104 and listing:103, not the two nearest. The docs warn that ANY stops at the first matches found, which may not be the closest.

Second, a geo set has no per-member expiry. The EXPIRE docs say a timeout belongs to a whole key. When we set one, all five listings vanished together. Redis 7.4.0 added expiry for single hash fields, but as of 27 October 2026 the docs list nothing similar for sorted set members.

So an expired or sold listing must be removed with ZREM, driven by the same outbox as the search index. The GEOADD docs say there is no GEODEL command because ZREM does the job.

Redis also accepts a small error. The GEOADD docs say it treats the Earth as a sphere, with errors up to 0.5% in the worst case. Against PostGIS spheroid distances, our worst gap was 0.465%.

For more on how grids and trees split a map, read the SevyDevy guide on geohash and quadtree spatial indexes. The proximity service guide covers the same problem for shops and restaurants.

Beginners can stop here: you can now explain listing states, expiry and nearby search. The rest covers keeping the search index honest, ranking, duplicates, scams, reputation and paid boosts.

Write a small outbox row in the same transaction as every listing change, and let a relay process push those changes to the index. The index applies a change only if its version is newer than the one it holds.

The naive version calls the search engine from the API right after the database write. If that call fails or the process crashes between the two, the index silently keeps a sold phone until something reindexes it.

An outbox is a table of things to tell other systems. Because the outbox row commits with the listing change, a change can never be saved without its message, or the reverse.

An everyday picture helps. Think of a shop's price board kept by a clerk, with runners bringing slips that carry serial numbers. The clerk pins a slip only if its number beats the one already pinned.

The board is the search index, the slips are changes, and the serial number is the listing version. Runners can arrive late or twice, and the board still ends right. Where the picture breaks: the board forgets a removed item's number after a while.

Versions make order not matter

Elasticsearch's Index API supports external versions. The docs say a write succeeds only if its version is higher than the stored one, and a lower or equal version fails with a 409 conflict.

Our search engine downloads were blocked, so we ran this rule as Python logic, not on a real engine. We sent a sofa's three changes, listed at 9,000 rupees, cut to 8,000, then expired, in all six possible orders.

Plain last-write-wins left the expired sofa searchable in 4 of 6 orders. The version check got all 6 right, and 1,000 shuffled streams with every event sent twice also ended correctly.

The 60-second delete window

Hard deletes have a trap. The index settings docs say index.gc_deletes, how long a deleted document's version is remembered, defaults to 60 seconds. After that, a versioned write can no longer be checked against the delete.

In our model, an older update arriving 5 seconds after the delete was rejected. One arriving 120 seconds later brought the expired sofa back. Keeping the document with status expired, and filtering on status, avoided it both times.

The indexer also reads the current row by ID rather than copying the event's body. Then even a very late event writes today's truth, not last week's price.

How fresh is the index?

Near real time, not instant. The Elasticsearch docs say indices refresh every second by default, but only those that have had a search request in the last 30 seconds, unless refresh_interval is set explicitly.

The index settings docs add that the default is 5 seconds on the Serverless offering, as of 27 October 2026. Either way, a change takes a moment to become searchable.

The refresh parameter on a write can be true, wait_for or false. The docs warn that true creates tiny segments, small index files, and should be used only after careful checking. For a seller's own listings page, read from the database instead.

Combine three signals: how well the words match, how close the listing is, and how fresh it is. A search engine scores the words, then a decay function lowers scores smoothly with distance and age.

The Elasticsearch function_score docs describe decay functions named gauss, exp and linear. Each has an origin, such as the buyer's location or today, and a scale at which the score falls to the decay value, 0.5 by default.

Decay works on numeric, date and geo_point fields, so one query can favour listings that are close and recent. The geo_distance query then removes anything outside the buyer's radius altogether.

The same docs say geo_distance uses arc distance by default. The plane option is faster but inaccurate over long distances and near the poles, which hardly matters within a city.

If you start on PostgreSQL alone, its full-text search is enough for the first version. A tsvector is a column of normalised words, and a GIN index over it is the type the docs prefer for text search.

We tried it on five ads. A search for chair found both chairs and chair, because words are reduced to their stems. The query iphone 13 -cracked dropped the cracked phone, as websearch_to_tsquery treats a dash as not.

That function suits raw user input. The docs say it never raises syntax errors, and our test passed it stray brackets and symbols without an error.

Check each new listing against existing ones for near-copies of its text and photos, then add up risk signals into a score. Low scores publish at once, middle scores wait for review, and high scores are blocked.

Copying is a known scam pattern. The US Federal Trade Commission's rental scam advice says scammers reuse the pictures and description of a real listing with their own contact details, and advertise surprisingly low rent.

The same advice lists other signs: asking for a deposit or rent before you have seen the place, refusing to meet, and asking for wire transfers, gift cards or cryptocurrency.

Near-duplicate text with MinHash

Exact matching fails because a scammer changes the price and one line. Instead, cut each ad into shingles, short overlapping runs of three words, and compare the sets.

The overlap measure is Jaccard similarity: shared shingles divided by all shingles. A 1997 paper on clustering web pages defined this resemblance and showed that small sketches built from minimum hash values estimate it.

We ran this with datasketch 2.0.0 on three made-up flat ads. The repost with a lower rent and a pay advance line had a true Jaccard of 0.618 with the original, and MinHash estimated 0.641.

An unrelated 1 BHK ad scored 0.060, estimated 0.039. MinHash LSH, which groups similar sketches into buckets so you avoid comparing every pair, returned only the original as a candidate. The datasketch docs warn that LSH is approximate, so it can miss a pair or return a false one.

Copied photos with perceptual hashes

A cryptographic hash such as SHA-256 gives a different value if even one byte changes, so it misses a resized copy. A perceptual hash, pHash, summarises the picture's structure from its brightness, ignoring colour, so similar pictures get similar hashes.

With ImageHash 4.3.2, a made-up sofa photo shrunk to half size, saved as a low-quality JPEG and stamped with text had distance 0 from the original. A different photo scored 34 of 64 bits.

Cropping and mirroring are its weak spots. A 70% crop scored 28 and a mirror image 24, nearly as far as the unrelated photo's 34. The ImageHash README offers a separate crop-resistant hash but claims no crop tolerance for pHash.

A risk score with three outcomes

Rules give a clear first version that moderators can read. Each signal adds points, and the total picks an action.

In our run, the cheap 2 BHK repost scored 80: copied photo, a pay-advance line and a new account. That crossed 70, so it was blocked and sent to a human. An honest first-time seller scored 15 and went live.

Scores of 35 to 69 go to a review queue before going live. Track how many held ads reviewers approve; if most are fine, your weights are too harsh. The SevyDevy fraud detection guide covers learned models for the next step.

Use a Bayesian average: blend each seller's ratings with a site-wide prior, as if every seller started with a few average ratings. The more real ratings a seller has, the less the prior matters.

A plain average puts a seller with one 5-star rating above one with 120 ratings averaging 4.52. That rewards luck and makes fake reviews cheap.

We used a prior of 4.0 weighted as 10 ratings. The one-rating seller dropped from 5.00 to 4.09, the steady seller barely moved from 4.52 to 4.48, and a seller with two 1-star ratings rose from 1.00 to 3.50.

That last case is the point. One angry buyer cannot sink a new seller, but a long bad record still shows. Only count ratings from buyers who chatted with the seller, so strangers cannot rate.

Show the count beside the score, such as 4.5 from 120 ratings. Add plain facts too, such as phone verified and member since a date, because they are hard to fake.

A free bump moves an ad back to the top of newest first, limited to once every few days. A paid boost buys a labelled slot at fixed positions. Both must pass the same filters as every other result.

The naive version simply adds points to boosted ads' scores. That lets a far-away or wrong-category boost outrank what the buyer asked for, and the same ad can appear twice.

Our blend reserved positions 1, 6 and 12 on a 20-result page for boosted ads that already matched the query. A boosted ad that also ranked organically was taken out of the organic list, so the page had no repeats.

Three slots held three boosted ads, and a fourth paid ad waited for the next page. Rotate waiting boosts fairly, and show sellers how many times their boost was actually displayed.

Label every paid slot clearly, such as Promoted. India's Central Consumer Protection Authority issued dark pattern guidelines on 30 November 2023, specifying 13 dark patterns. Its press release names disguised advertisement as one such practice. Ask a lawyer how they apply to your site.

Lead with the shape of the problem: perishable local listings, many more searches than posts, and trust as a first-class feature. Then walk through the design in this order.

1. 1

   State the needs

   Post, edit, renew and mark sold. Search by words, category, price and distance. Hide sold and expired ads within seconds. Catch copies and scams before they go live.

2. 2

   Size it roughly

   Give made-up numbers and say so: 2 lakh live ads, 20,000 new a day, millions of searches. Reads dominate, so reads go to copies.

3. 3

   Pick the source of truth

   A PostgreSQL listings table with status, version and expires_at. Every move is a guarded UPDATE that checks the old state.

4. 4

   Handle expiry twice

   Filter expires_at in every query, and run a batch job with SKIP LOCKED to set expired and write outbox rows.

5. 5

   Serve nearby search

   PostGIS ST_DWithin with a partial GiST index first; a search engine with geo_distance at scale; Redis GEO only as a cache cleared by ZREM.

6. 6

   Sync the copies

   Outbox in the same transaction, a relay, an indexer that re-reads the row and writes with an external version. Prefer status updates over hard deletes.

7. 7

   Build trust in

   MinHash on text, perceptual hashes on photos, a readable risk score with publish, review and block, and Bayesian-average seller ratings.

8. 8

   Add boosts last

   Fixed, labelled slots for paid ads that already match the search, no duplicates on a page, and a free bump with a cooldown.