How to build a recommender system in Postgres

01Ruling out the standard approaches

A recommender has one job. Given a person and a pile of items, decide what order to put the items in. Everything difficult about it comes from the specifics, and the specifics vary enough between products that a design which is obviously correct for one is obviously wrong for the next.

Four things decide most of it: how many items there are, how long an item stays relevant, how much you know about each person, and how many engineers will maintain whatever you build.

The worked example throughout this post is Hango, an events app I built and run in Sydney. Its answers:

Those four numbers eliminate most of the standard toolkit before a line of code is written.

Matrix factorization and any learned item embedding need a stable item set. There isn't one. By the time a model finished training, a good fraction of what it had learned would have already happened and been deleted. Collaborative filtering needs enough overlapping users per item to estimate anything, and with a few hundred users spread over two thousand venues, the median event has been seen by nobody. A two-tower retrieval model wants labelled interactions in the hundreds of thousands, and wants somewhere to run.

What survives is content-based recommendation. Describe each item, describe each person in the same terms, compare the two. That constraint drives the whole design, and the rest of this post is the sequence of decisions that follow from it.

Each section below is one decision. What the problem is, what the reasonable options are, which constraint settles it, and what the implementation looks like. Several of these would go the other way at a different scale, and I have tried to be specific about where the line sits.

02Scoring in the database

Putting items in an order means giving each one a number and sorting on it. Everything from here on is about producing that number, and the first question is which part of the system computes it. There are three usual answers, suiting different situations.

Precompute in a batch job. A nightly process writes a ranked list per user and the app reads it. Cheapest thing to operate, and the right answer for most small products. It falls over when the ranking has to respond to something that changed after the batch ran, which for events means anything selling out, being cancelled, or happening this evening.

A serving stack. A model server holds the ranker, a feature store holds its inputs, and the app calls both at request time. This is what you build when the model needs its own hardware, or when several products consume the same ranking and it has to stay consistent between them. It is also two more systems with their own deploys, their own failure modes and their own on-call.

Inside the database. The scoring runs as a stored procedure, next to the rows it reads.

The third option is only sensible under one condition. The expensive part of producing a feed has to be the filtering rather than the arithmetic, and it is worth being concrete about why that holds here.

Deciding which events are even eligible for a given person means a spatial predicate (a venue inside their radius), a temporal one (an instance starting in the future), and a set of exclusions (already saved, disliked, or shown too often lately). Those are four joins against tables that already live in Postgres. The scoring that follows is roughly fifteen thousand dot products, which pgvector does in a few milliseconds.

Doing that arithmetic elsewhere means reading a few thousand 768-dimensional vectors out of the database, computing dot products in another process, and sending the results back to be joined against the exclusions anyway. The transfer costs more than the computation saves. The data is already in Postgres, so the ranking goes to the data.

In practice the feed is one PL/pgSQL function. It reads the caller's interest model, filters candidates with PostGIS, scores what is left, writes the result into a queue table, and returns assembled JSON.

Keeping it in one place also settles the authorization question, which otherwise needs its own design. The function is SECURITY DEFINER and reads the caller's identity from auth.uid() instead of accepting it as a parameter, with row-level security on every table underneath. A client cannot request somebody else's feed, because the request has nowhere to name a user.

03Representing a person

To compare a person with an event, both have to be points in the same space. The events are already there. Each carries a 768-dimensional embedding built during ingest from a short description of what kind of thing it is, with the title and venue deliberately left out so that two similar nights land close together.1 The open question is what shape the person should be.

One vector per person. Average the embeddings of everything they liked. Cheap, easy to reason about, and wrong for anybody with more than one interest. Someone who goes to techno nights and string quartets averages out to a point near neither, and the events closest to that point are an incoherent middle.

A learned user representation. Train a model to turn an interaction sequence into a user vector. The right answer with enough data, and it needs both training data I do not have and training infrastructure I would have to keep alive.

Several vectors per person. Keep a small set of centroids, one per distinct interest, updated online as interactions arrive. Multi-modal taste survives intact and there is no training step at all. The cost is that you now own a clustering algorithm, along with every parameter it needs.

Hango uses the third, capped at six centroids. Six because a feed of eleven cards cannot meaningfully represent more interests than that, and because the cap is what forces the model to forget.

Interactions arrive carrying a weight, which is how much moving the model each one justifies:

An obvious way to apply those is to add each one as it happens. That produces a bad model, and the reason is worth spelling out. Somebody opens a card, reads the details, then saves it. Adding all three moves their model by 1.30 for a single decision, while somebody who saved straight from the feed moves theirs by 1.00 for the same decision. The score ends up partly measuring how much a person had to poke at a card before committing, which is a property of the card rather than of the person.

So interactions are stored as a ledger instead of a log. One row per person and event holds the highest weight that pair has ever reached, and an update applies only the difference. The three actions above move the model by 1.00 in total, because the save supersedes the two weaker signals instead of stacking on them.

When an interaction arrives, its event embedding is compared with each of that person's centroids. If the nearest is within a threshold, which the next section is about, the cluster absorbs it and the centroid moves to the weighted mean of where it was and where the event is:

w is the cluster's current weight, e the event embedding, Δ the increment from the ledger

Negative interactions are treated asymmetrically, and this is the part of the update rule I would most expect an argument about. The symmetric version subtracts, pushing the centroid away from the disliked event. That sounds principled and behaves badly, because moving away from a point in a high-dimensional space moves you toward everything on the far side of it, and nobody expressed an opinion about any of that. So a dislike lowers the cluster's weight and leaves its position alone. The reading is that the interest was identified correctly and overestimated.

When nothing falls within the threshold, the interaction wants a new cluster. Two guards decide whether it gets one. A new cluster needs a weight of at least 1.0, so opening a card cannot claim a slot that a save should have had. And once six clusters exist, something has to be evicted, which only happens when the incoming interest is stronger than the weakest one already there. Clusters that fall below 0.05 are deleted.

Interests fade, so weights decay by 3% per day since the cluster was last touched, a half-life of about 23 days. Decay could be applied by a nightly job that rewrites every row. It is instead computed at read time from a timestamp, so a person's model is correct even if nothing has run for a week, and there is one less scheduled task whose silent failure would be invisible.

Two interactions arriving at once would otherwise interleave and produce a centroid that averages a race condition, so the update takes a transaction-scoped advisory lock keyed on the user id. Phones on poor connections retry, and retries arrive in pairs.

One more thing hangs off each cluster. A feed that offers no account of itself is hard to trust and impossible to correct, so alongside the centroid each cluster keeps a running tally of the tags it has absorbed, weighted the same way and capped at the top 25. Those tags never enter the ranking. They exist so a card can say Live Jazz · Lounge, and the person can see the reasoning and disagree with it.

04Picking the distance threshold

The clustering has one parameter that decides its behaviour: how close an event must be to an existing centroid to count as the same interest. Set it loose and every interest merges into one blurry cluster. Set it tight and every save opens a new cluster, evicting the previous one before it can grow, so the model never remembers anything for longer than six interactions.

The parameter is a cosine distance, which runs from 0 for two vectors pointing the same way to 2 for opposite. Reasonable-sounding values are easy to invent and worthless, because what matters is how this particular corpus is spread through the space, and that is a property of the data rather than of the metric. So I measured it. 420 events sampled, cosine distance computed between all 87,990 pairs.2

The whole corpus fits between 0 and 0.27. Two randomly chosen events are typically 0.156 apart, and the most dissimilar pair in eighty-seven thousand draws was 0.267. Nothing is even a third of the way to orthogonal, because everything in the catalogue is a short English phrase about going out in one city.

The practical consequence is that intuition here is wrong by an order of magnitude. A threshold of 0.5, which sounds strict, treats every event in the catalogue as the same interest. The value in use is 0.09, which sits at the 3.75th percentile of random pairs and is far more selective than the number suggests.

How much of a distance metric's range your corpus occupies is a measurement, not a default.

Measuring surfaced a second problem, one that only appears per person rather than in aggregate. Take a single real interest cluster and rank every event currently live near that user by distance from its centroid.

Ranking on raw distance and then multiplying by the cluster's weight produces a score dominated entirely by the weight, because the distances differ in the third decimal place while the weights differ by factors of three. The feed collapses into everything from your strongest interest, then everything from your second, with no resolution left to decide which jazz night. So similarity is min-max normalized inside each cluster before anything else touches it:

over that cluster's own candidate set, so its best candidate scores 1 and its worst scores 0

Now the cluster weight decides how many slots each interest earns and the normalized similarity decides which events fill them. Those are two different jobs, and before the normalization they were fighting each other for the same range of numbers.

05Candidate generation

Most events cannot be shown to most people. They are in the wrong part of the city, already saved, already seen three times this week, or in a genre that person has blocked. Producing a feed means combining that eligibility test with the similarity ranking, and the order of the two determines what the system can guarantee.

Search first, filter afterwards. Ask the vector index for the nearest few hundred events to each centroid, then discard the ones that fail the filters. This is what an approximate nearest neighbour index is built for and it is very fast. The difficulty is that the index knows nothing about the filters, so the number of survivors is unknown until after the search has run. Ask for 200 and you might keep 8, and no fixed over-fetch factor is safe when the filters vary per person, which these do. Everybody has a different radius, a different save history and a different set of blocks.

Filter first, then scan. Reduce to the eligible set with ordinary relational predicates, then compute distances across whatever survives. Costs a sequential scan of the survivors. Returns exactly as many candidates as it was asked for.

The filters here take the catalogue from about 10,800 embedded events down to the 2,400 or so that are both upcoming and near a given person, before per-user exclusions. Scanning 2,400 rows once per cluster takes a few milliseconds. An index would save some of that and give up the guarantee, so the filter runs first.

PostGIS does the geography in two stages, a bounding-box overlap against a pre-expanded envelope to eliminate most venues cheaply, then an exact ST_DWithin on what remains. Exclusions are anti-joined out before any vector arithmetic happens, so no distance is ever computed for an event that could not have been shown.

That leaves how many candidates each cluster contributes, and by what rule. A distance cutoff is the obvious choice and behaves inconsistently, because a tightly focused cluster has hundreds of events inside any given radius while a diffuse one has three. Taking the nearest 300 by rank means the same thing for both. Each cluster contributes that core of 300, plus a ring of the next 300 that section 06 uses for a different purpose.

There is also an absolute ceiling of 0.30, which given Figure 3 never actually excludes anything. It stays in the query because the ingest description format will change eventually, and if the space spreads out the function should return fewer candidates instead of quietly ranking noise.

06Making room for discovery

Ranking by similarity alone produces a feed that is accurate and useless. Every card is something the person already knows they like, nothing on it is a discovery, and within a week they have seen the entire neighbourhood around their own centroids. The system has to spend some of its slots on things it is less sure about.

Multi-armed bandits are the principled treatment of that trade-off, and they need feedback volume to converge. Each arm has to be pulled enough times for its estimate to mean anything, and a few hundred users generate nowhere near that across a catalogue this size. A bandit that has not converged is a random number generator that is harder to debug than a random number generator.

Blending popularity into the relevance score is the common shortcut. One score, one sort, easy to reason about. It also makes the two forces compete inside a single number, so the only way to guarantee that anything obscure ever appears is to weight popularity low enough that the feed stops surfacing the concert everybody is going to.

Fixed slots give each intent its own allocation. Less clever, and it makes the guarantee structural. This many cards are safe bets, this many are obscure, this many are reaching. Eleven cards is small enough that the allocation can just be a decision.

So the feed is split into fixed lanes. Every event carries an importance score from 0 to 100, assigned during ingest by a model that reads the listing, searches for context, and grades cultural weight against city-level benchmarks. A stadium tour lands in the nineties, a pub trivia night in the teens. Normalize that within the cluster the same way as similarity, call it prominence, and the three lanes are three ways of combining the two:

Similarity is weighted twice as heavily as prominence in both, which is a claim that a good match to an obscure thing beats a poor match to a famous one. Deep cuts invert prominence to reach the small events that any popularity term buries, with a hard cap at importance 65 so an arena show cannot be presented as a find no matter how well it matches. Explore drops prominence entirely and draws from the ring, which is how the feed reaches past the neighbourhood it already knows.

All three lanes then pass through the same multipliers:

w the decayed cluster weight, ρ = 0.7 if this event was already shown in an earlier cycle, f the number of the person's friends who saved it, β = 0.5 in hero and deep cut, 0.7 in explore

The friend term is logarithmic because the first friend to save something is most of the information. The sixth adds very little, and under a linear term one popular event inside one friend group would push everything else off the screen.

Left alone, the strongest cluster wins every slot. Scores are therefore multiplied by a geometric decay of their rank within their own cluster, 0.5 per position for hero and 0.75 for deep cuts, so a cluster contributes one strong pick, a noticeably weaker second and effectively nothing by its fourth. This is a blunter instrument than MMR or a determinantal point process, and across eleven cards the difference is not observable.

Deep cuts and explore also get a multiplicative jitter of ±15% and ±30% at the moment candidates are selected into the pool. Ordering inside a cycle stays deterministic, so the feed does not shuffle while somebody is reading it. What varies between cycles is which near-ties made the pool at all.

07Counting impressions

Two separate mechanisms need to know what a person has already seen. The feed needs it to stop offering the same event every eight hours. The popularity score needs it as a denominator, because two saves out of three views and two saves out of four hundred are not the same event.

The obvious source is the analytics SDK the app already has, and it is the wrong one. Analytics clients batch, retry, drop on network failure and deduplicate loosely, which is entirely acceptable when the output is a funnel chart and unacceptable when the output is a denominator. Those numbers also live outside the database and would have to be shipped back in before a SQL ranking function could use them.

The cheap alternative is to count everything written into the feed queue as shown, which needs no client work at all. It also overcounts badly, because most people never scroll to the bottom of a feed. An event sitting in position 30 would accumulate impressions it never received, and its popularity would be permanently understated.

So viewability is measured on the client and recorded through the database. A card counts as seen once at least half of it has been continuously visible for one second, roughly the IAB display standard. The client buffers ids and flushes them at ten, after four and a half seconds of stillness, on screen blur, and when the app goes to background.

Buffering across four triggers means the same ids will sometimes be sent twice, so the write has to absorb that. Each feed generation carries a timestamp, every impression is stamped with the generation it belongs to, and a second write inside the same generation does nothing. Retries and scrolling back up are both free.

Those counts feed the cooldown. An event shown five times, or shown at all within the last 48 hours, is excluded from the next regeneration. An event somebody engaged with at strength 0.5 or above is excluded permanently, because a save is a request to remember something, not a request to keep being shown it.

08Popularity with few users

Every feed wants a popularity signal, and every popularity signal is a ratio of engagement to exposure. With a few hundred users that ratio is mostly noise. An event shown to three people and saved by two scores 0.67. One shown to four hundred and saved by two hundred scores 0.50. The first is a coin flip and the ranking calls it the more popular of the two.

A minimum exposure floor is the usual first fix. Require fifty impressions before an event is eligible for the chart. It works, and it makes the chart useless for exactly the events that most need it, because plenty of listings never accumulate fifty impressions until after the night they were advertising.

A frequentist lower bound such as the Wilson interval is the standard better answer. It ranks by the bottom of a confidence interval, so thin evidence is penalised in proportion to how thin it is, with no hard cutoff anywhere. It is also deliberately agnostic. With three observations it says almost nothing, because it refuses to assume anything about an event it has not seen enough of.

That agnosticism is what makes it the wrong choice here, because there is something to assume. Ingest already assigns every event an importance score, which is an informed guess at how much interest it should attract. Starting from ignorance would be strange when the guess is sitting in the same table.

Shrinkage toward a prior uses it. The score starts at the prior and moves toward the observed rate as evidence accumulates:

E time-decayed engagement over 10 days, I impressions over the same window, q the importance score over 100, m = 5 pseudo-observations

Five pseudo-observations is a deliberate statement about how much the prior is worth. It is enough that the two-out-of-three event lands near 0.5 and stops beating things with real evidence behind them. It is small enough that thirty genuine impressions leave almost nothing of it.

Popularity alone still gets the timing wrong in both directions. A festival that sells out four months ahead is worth showing somebody now. A Tuesday night gig four months away is worth showing nobody, including the people who will eventually go. Two terms handle that, both keyed to a booking window that ingest assigns per event, 90 to 180 days for a mega festival and 3 to 7 for a local gig:

P proximity, σ scarcity, φ = 0.6 a floor so distant events are damped instead of erased, a = 0.3

Proximity halves for each booking window still to run, so each kind of event climbs on roughly the schedule at which people buy tickets for that kind of event. Scarcity rises as an event's final date approaches, which is what lifts a closing exhibition in its last fortnight.

The whole calculation is a materialized view refreshed hourly. I retune that interval fairly often, so it lives in the same config row as everything else and a trigger on that row reschedules the cron job whenever it changes.

One last problem with any popularity shelf. If everybody in the city sees the same ten events, it stops being worth opening after the second day. So the top thirty by score are hashed together with the viewer's id and today's date, and ten are drawn from that. Everyone is looking at genuinely popular events, and two people rarely see the same ten.

09New users

Everything above assumes the person has interacted with something. Somebody who installed the app ten seconds ago has not. They have no clusters, so there is nothing to compare events against, and the feed function returns an empty list.

The standard remedy is to detect that state and serve something else, usually popular events, until enough signal accumulates to switch over. It works, and it costs more than it looks. Two rendering paths now exist that have to stay in agreement. The switchover is a visible discontinuity where the whole feed changes character in one refresh. And the path that runs for every new user is the one that gets the least attention, because everybody building the product stopped being a new user months ago.

An alternative is to ask questions and map the answers onto a taxonomy. Pick your favourite genres, and the feed filters on genre until real signal arrives. This avoids the empty feed and introduces a second description of taste, maintained by hand, that has to be kept aligned with the one the recommender actually uses.

The approach that avoids both is to seed the real structure directly. Onboarding shows 29 archetypes, from Techno & Raves to Thrift & Vintage Markets to Pub Trivia & Quiz Nights, and asks the person to pick the few that sound like them. Each archetype is a row in exactly the same shape as an event, with tags and an embedding produced by exactly the same code path, so each selection becomes a seed cluster at a fixed starting weight.

Two properties decide whether that works, and both can be checked against the data.

The archetypes have to stay distinct from each other. If two of them sit closer together than the 0.09 merge threshold from section 04, picking both produces one cluster instead of two, and somebody who told the system two things about themselves gets one thing back. Across all 406 pairs the closest are Techno & Raves and Hip-Hop & RnB Nights at 0.094, which clears the threshold by a hair and nothing else comes near it. Mean separation is 0.182, wider than the 0.156 between two randomly chosen events, so the seeds are spread further apart than the catalogue they are drawn from.

The set also has to cover the catalogue. A gap in it means some real interest has no seed anywhere near it, and the people who hold that interest start from a centroid pointing at something else and have to drag it across the space one save at a time. The archetypes were built by working through the genre and segment taxonomy that ingest assigns, then checked against the events themselves. All 2,707 currently live sit within 0.195 of some archetype, median 0.112. The median event is closer to its nearest seed than two random events are to each other.

An earlier version let people distribute a budget of points across archetypes to signal relative strength. The model it produced was marginally better and most people did not understand what they were being asked, so it is gone. Picking a few is a question anybody can answer in ten seconds, and the first few saves sort out the relative weights anyway.

The feed function reads six weighted centroids and has no way of knowing whether they came from an onboarding screen or from six months of saves, so day zero is produced by the same query, with the same tuning, as day three hundred. There is no switchover and no second path to keep in sync. The first real save lands on the nearest seed and starts moving it, and the archetype quietly becomes an interest.

One piece of special handling survives. If somebody's clusters are unusual enough that the hero lane comes up short, the remaining slots are filled from the popularity view, which is the correct answer for a nearly empty profile regardless.

10Recommending to a group

Four friends want to do something together. Each has their own model, and the system has to produce one list.

Intersection. Take what everybody's individual feed already contains. Precise, and usually empty, because four people's top-thirty lists rarely overlap over a catalogue of a few thousand.

Mean of scores. The obvious aggregation, and it optimises for the wrong thing. Averaging rewards whatever nobody objects to, which is how a group of four ends up looking at a list of pleasant, safe, unmemorable options. Plans do not form that way. They form because one person cares a lot and everybody else is willing.

Maximum. Take the best individual score. That models the enthusiasm correctly and ignores everybody else, so the most opinionated member's list becomes the group's list.

Max-plus. Take the maximum, then add back a discounted contribution from everybody else. The leader carries the recommendation and the rest still get a vote.

The group score is max-plus. Each member's affinity is first percentile-ranked inside their own candidate pool, which is what makes members with very different cluster weights comparable at all, and then:

β_n runs from 0.75 for a pair down to a floor of 0.35 as the group grows

The discount depends on group size for a reason. In a pair the other person is half the group, and their opinion should count for nearly as much as the leader's. In a group of eight any one lukewarm member should barely register, or nothing would ever score highly.

Two corrections sit on top. A breadth factor rewards events that a larger share of the group has some affinity for, which separates genuine common ground from one strong match surrounded by indifference. And a demotion driven by the gap between the leader's score and everybody else's stops one person's weekly ritual from being proposed to the group every single time.

11Limitations

Four things this design does badly.

• There is no offline evaluation. Every constant above lives in one config row, which makes a change trivial to ship and slow to judge, because the only ground truth is a PostHog funnel a week later. A replay harness over the interaction ledger is the obvious next build, and the ledger was shaped to make it possible.
• The only collaborative signal is the friend graph. Item-to-item co-save structure is sitting in the data unused, and it is the cheapest available source of the thing content-based recommendation is worst at, which is spotting the connection between two events that share no vocabulary.
• Candidate generation scans. Discussed in section 05, and the crossover is closer than it sounds, because what matters is live events rather than total events. A second city roughly doubles it.
• The clustering is greedy. Six slots, no re-clustering pass, first match wins. It drifts sensibly, and somebody whose taste changes sharply carries the old centroid for about a month until decay clears it.

All of it is one artefact. Ranking, exclusions, cooldown, the popularity refresh and the authorization boundary are the same Postgres instance. Every constant is a row I can change from a SQL console. A feed is one round trip from a phone. None of that would be worth much to a team of thirty, and for one person it has been worth more than any individual scoring improvement in it.

Oltan Sevinc

PhD candidate at UNSW working on event-based vision and perception for robotics, and the sole engineer behind Hango. Sydney. Email · GitHub · LinkedIn