Cosine similarity in SQL — item-based recommendations without the matrix

I built a small listings API recently: three endpoints over a set of property listings, with CSV files as the source of truth and item-based recommendations on the detail page. Nothing about the shape is unusual, but two parts turned out to be more interesting than they looked, and both came down to the same question — where should this computation actually happen?

The recommendations are the obvious one. Every tutorial on collaborative filtering starts by building a user × item matrix in memory. You do not need it, and for this workload you do not want it.

The other is the CSV import. "Load a CSV into a table" is a one-liner until you ask what happens on the second run.

The data

Two files. Listings:

id;name;details;price;image
p1;"Lisbon: Apartment near the river";"...";550.000,00;https://...

And an interaction log — who looked at what:

property;user
p1;alice@example.com
p2;alice@example.com
p1;bob@example.com

Semicolon-delimited, quoted fields containing commas, European decimal format in the price column. That combination is worth noticing early, because it decides whether you can split on a character or need a real CSV parser. You need a real parser.

The interaction data is binary implicit feedback: a user either viewed a listing or did not. There are no ratings, no star scores, no strength. That single fact is what makes the rest of this cheap.

Cosine similarity, and why it collapses

Item-based collaborative filtering asks: for the listing being viewed, which other listings were viewed by a similar set of people? Represent each listing as a vector over users, then measure the angle between vectors:

                 a · b
sim(a, b) = ─────────────────
             ‖a‖ · ‖b‖

The textbook implementation builds the matrix, fills it with ratings, and runs the formula. But when every entry is 0 or 1, each term simplifies:

• a · b = Σ aᵢbᵢ, and each product is 1 only when both are 1 → that is the count of users who viewed both listings, |A ∩ B|.
• ‖a‖ = √(Σ aᵢ²), and 1² = 1 → that is √|A|, the square root of the number of users who viewed A.

So the whole thing becomes:

                |A ∩ B|
sim(A, B) = ─────────────────
             √|A| · √|B|

Three integers per pair. No vectors, no matrix, no floats until the final division. And every one of those integers is a COUNT — which is to say, the database already knows how to compute them, over indexed columns, without shipping a single interaction row to the application.

That is the whole idea. Everything below is consequences of it.

What the in-memory version costs

Worth being concrete about what is being avoided, because "it doesn't scale" is not an argument by itself.

The naive version loads all interactions and builds Map<String, Set<String>> from listing to viewers:

// The version I did not ship.
Map<String, Set<String>> viewersByListing = new HashMap<>();
for (PropertyView view : propertyViewRepository.findAll()) {
    viewersByListing
        .computeIfAbsent(view.getPropertyId(), k -> new HashSet<>())
        .add(view.getUserName());
}

Three problems, in increasing order of how much they will hurt:

1. findAll() on the interaction table. That table is the one that grows without bound — one row per view, forever. It is the single largest table in the system and this loads all of it, per request, into heap.
2. Comparing the target against every other listing is O(items × users-per-item) set intersections. Fine for three listings, not fine for thirty thousand.
3. It is all wasted work. The result is a handful of top matches; the other 99% of the computation is discarded.

The database can answer this with two grouped counts over an index. The application's job is to divide and sort.

The query

The similarity for every candidate listing, in one round trip:

WITH target_users AS (
    SELECT user_name
    FROM property_views
    WHERE property_id = :targetId
),
target_total AS (
    SELECT COUNT(*) AS total
    FROM target_users
),
common AS (
    SELECT property_id, COUNT(*) AS common_users
    FROM property_views
    WHERE user_name IN (SELECT user_name FROM target_users)
      AND property_id <> :targetId
    GROUP BY property_id
),
totals AS (
    SELECT property_id, COUNT(*) AS total_users
    FROM property_views
    GROUP BY property_id
)
SELECT c.property_id                        AS propertyId,
       c.common_users / (SQRT(tt.total) * SQRT(t.total_users)) AS similarity
FROM common c
JOIN totals t ON t.property_id = c.property_id
CROSS JOIN target_total tt
ORDER BY similarity DESC, c.property_id ASC
LIMIT :limit

Reading it in order: target_users is the viewer set of the listing being displayed. common counts, for every other listing, how many of those same viewers it shares. totals is the viewer count per listing. The final SELECT is the formula, verbatim.

Two details that are easy to get wrong:

COUNT(*) versus COUNT(DISTINCT user_name). The formula needs distinct users — the same person viewing a listing twice is one interaction, not two. You can enforce that in the query with COUNT(DISTINCT ...), or you can enforce it in the schema:

@Entity
@Table(
    name = "property_views",
    uniqueConstraints = @UniqueConstraint(columnNames = {"property_id", "user_name"})
)
public class PropertyView { ... }

With the constraint in place, a duplicate pair cannot exist, so COUNT(*) and COUNT(DISTINCT user_name) return the same number — and COUNT(*) is the cheaper of the two, since it skips the sort or hash the distinct requires. Push the invariant into the schema and the query gets simpler and faster. If you cannot guarantee uniqueness, keep the DISTINCT and pay for it.

The tie-break in ORDER BY. Sorting by similarity alone leaves ties in whatever order the database feels like returning them, which means the same request can produce different output on different runs. That is the kind of thing that makes a test flaky once a month and wastes an afternoon. Add a deterministic secondary sort.

The trap in the obvious version

My first version passed the target's viewer set from Java into the query:

@Query("""
    select pv.propertyId, count(distinct pv.userName)
    from PropertyView pv
    where pv.userName in :userNames
      and pv.propertyId <> :propertyId
    group by pv.propertyId
""")
List<Object[]> countCommonUsers(String propertyId, Set<String> userNames);

This works and reads nicely. It also has a ceiling: the bind parameters are expanded one per element, and PostgreSQL's protocol caps a statement at 65535 parameters. A popular listing viewed by 70,000 people does not produce a slow query, it produces a hard failure. Other databases have their own limits, some lower.

Before that ceiling there is a softer cost: the driver serialises the entire set on every request, and the query planner sees a different statement shape each time, so the plan cache is useless.

The fix is not to raise the limit. It is to notice that the set came from the database in the first place and never needed to make the round trip — which is what the IN (SELECT ...) subquery in the query above does. One statement, stable shape, no parameter explosion.

The general rule I keep relearning: if you are passing a collection into a query and that collection came out of a query, you have a join you have not written yet.

Indexes

The unique constraint above creates a composite index on (property_id, user_name). That covers half the access pattern — looking up the viewers of a listing uses property_id as the leading column, which is exactly what a composite index supports.

The other half does not work. WHERE user_name IN (...) needs user_name as a leading column, and in a (property_id, user_name) index it is second. A composite index is only usable from the left, which is the part that trips people up: the column is in an index, and the index still cannot be used for that predicate.

So the second index is not optional:

CREATE INDEX idx_property_views_user_name ON property_views (user_name);

Two indexes, one per direction of the join. This is the kind of thing that never shows up on a three-row test file and becomes the entire performance story on a real one.

Wiring it into Spring Data

A projection interface keeps the result typed without an entity:

public interface SimilarityRow {
    String getPropertyId();
    double getSimilarity();
}

@Repository
public interface PropertyViewRepository extends JpaRepository<PropertyView, Long> {

    @Query(value = """
        WITH target_users AS (...)
        SELECT c.property_id AS propertyId, ... AS similarity
        ...
        LIMIT :limit
        """, nativeQuery = true)
    List<SimilarityRow> findSimilarProperties(@Param("targetId") String targetId,
                                              @Param("limit") int limit);
}

The service then does the only two things that genuinely belong in the application: enforce a result cap, and join the scores back to the listing data for the response.

public List<RecommendationResponse> recommendationsFor(String propertyId, int limit) {
    List<SimilarityRow> rows = propertyViewRepository.findSimilarProperties(propertyId, limit);
    if (rows.isEmpty()) {
        return List.of();
    }

    Map<String, Property> byId = propertyRepository
        .findAllById(rows.stream().map(SimilarityRow::getPropertyId).toList())
        .stream()
        .collect(toMap(Property::getId, identity()));

    return rows.stream()
        .map(row -> {
            Property p = byId.get(row.getPropertyId());
            return p == null ? null : RecommendationResponse.of(p, row.getSimilarity());
        })
        .filter(Objects::nonNull)
        .toList();
}

findAllById on the collected ids rather than a lookup per row. It is one line either way, and one of them is N+1.

The LIMIT matters more than it looks. Without it the endpoint returns every listing that shares a single viewer with the target, sorted — a response whose size grows with the catalogue, for a UI that shows four cards. Cap it in the query, not in the caller, so the database can stop early.

Where cosine gets it wrong

Worth writing down, because the formula is easy to trust more than it deserves.

Cosine on binary data has a popularity bias. A listing viewed by nearly everyone shares viewers with everything, so it surfaces as "similar" to every target. The √|B| in the denominator dampens this but does not remove it — dividing by the square root of a large number still leaves a large numerator. If your catalogue has a handful of blockbuster items, they will haunt every recommendation list.

There is also no time decay. A view from two years ago counts exactly as much as one from this morning, which is wrong for a domain where inventory turns over and intent is recent.

And there is a cold start on both ends: a new listing has no viewers, so it is similar to nothing and is recommended to no one — which is precisely the item that most needs exposure.

The fixes are all known — normalising by popularity, exponential time decay on the interaction weight, a popularity or content-based fallback for cold items — and all of them are more machinery than this needed. What matters is knowing that the honest description is "cosine over co-views", not "recommendations", and that the difference is where the next chunk of work lives.

CSV as a source of truth

The other half of the service: the files are the input, the database is the query layer, and the two must agree.

The obvious implementation truncates and reloads. It is correct exactly once. On the second run, every id is a new row, foreign keys break if anything references them, auto-increment counters drift, and any row the application added is silently destroyed. Worse, there is a window in the middle where the table is empty and a concurrent request sees nothing.

What I wanted instead is a sync that is idempotent: running it twice does nothing the second time, and running it after an edit applies exactly that edit.

Three parts.

Skip the work entirely when the file has not changed. Hash it and compare:

public static String sha256HexOrEmpty(String filePath) {
    Path path = Path.of(filePath);
    if (!Files.exists(path)) {
        return "";
    }

    try (InputStream in = Files.newInputStream(path)) {
        MessageDigest digest = MessageDigest.getInstance("SHA-256");
        byte[] buffer = new byte[8192];
        int read;
        while ((read = in.read(buffer)) != -1) {
            digest.update(buffer, 0, read);
        }
        return toHex(digest.digest());
    } catch (Exception e) {
        throw new IllegalStateException("Failed to compute file hash", e);
    }
}

Streamed in 8 KB chunks rather than Files.readAllBytes, so the memory cost does not scale with the file. Content hash rather than last-modified timestamp, because timestamps change when nothing did — a checkout, a copy, a container rebuild all touch mtime and would trigger a pointless full resync.

Upsert instead of replace. Compare each incoming row against what is stored and write only what actually differs. The version to avoid is a findById inside the row loop — that is one query per line of the file. Load once into a map, then decide in memory:

Map<String, Property> existing = propertyRepository.findAll().stream()
    .collect(toMap(Property::getId, identity()));

List<Property> toWrite = new ArrayList<>();
for (String[] line : rows) {
    Property incoming = parse(line);
    Property current = existing.get(incoming.getId());
    if (current == null || hasChanges(current, incoming)) {
        toWrite.add(incoming);
    }
    if (toWrite.size() >= BATCH_SIZE) {
        propertyRepository.saveAll(toWrite);
        toWrite.clear();
    }
}
if (!toWrite.isEmpty()) {
    propertyRepository.saveAll(toWrite);
}

Batched writes, because saveAll on a 100k-element list builds one enormous transaction and holds every entity in the persistence context until it commits.

Handle deletions with a keyset scan. Rows removed from the CSV must disappear from the table, which means walking what is stored and checking it against the file. Walking a large table with OFFSET degrades quadratically — the database counts and discards every skipped row on each page. Keyset pagination does not:

@Query("""
    select pv.id, pv.propertyId, pv.userName
    from PropertyView pv
    where (:lastSeenId is null or pv.id > :lastSeenId)
    order by pv.id asc
""")
List<Object[]> findRowsAfterId(@Param("lastSeenId") Long lastSeenId, Pageable pageable);

Each page starts from the last id seen, so every page is an index seek regardless of depth. Rows present in the database but absent from the file get collected and deleted in batches; whatever is left in the file set at the end is new and gets inserted.

The pattern generalises well beyond CSV — it is the same shape as any reconciliation loop against an external source of truth.

The mistake: doing this in the request path

My first version called the sync from the read path, so a request to the listings endpoint hashed the file, resynced if needed, then queried. It is a tempting design: the data is always fresh and there is no scheduler to configure.

It is also wrong in three separate ways.

It puts unbounded work in a request. The request that happens to arrive right after someone edits the file pays for the entire import — parse, diff, delete, insert — while the client waits. Every other request is fast. Tail latency becomes a function of when the file changed, which is untestable and unexplainable.

It is not thread-safe. The "have we synced this version" flag lived in a plain field on a singleton bean:

private String lastFileHash = null;   // read and written by every request thread

Two concurrent requests both read the stale value, both decide a sync is needed, and both run it — against the same tables, at the same time. The write is not volatile either, so there is no guarantee a thread ever observes another's update. It happens to work under a load test that never runs two requests at once, which is the worst possible property for a concurrency bug to have.

It ties freshness to traffic. No requests, no sync. The data is current only when someone happens to ask.

The fix is to move it out of the request entirely and give it a real trigger:

@Component
public class CsvSyncRunner implements ApplicationRunner {

    private final CsvSyncService csvSyncService;

    @Override
    public void run(ApplicationArguments args) {
        csvSyncService.syncAll();
    }

    // Import is idempotent, so a missed or repeated run is harmless.
    @Scheduled(fixedDelayString = "${listings.csv.sync-interval:PT5M}")
    public void refresh() {
        csvSyncService.syncAll();
    }
}

and to make the guard state actually safe:

private final AtomicReference<String> lastHash = new AtomicReference<>();
private final ReentrantLock syncLock = new ReentrantLock();

public void syncAll() {
    if (!syncLock.tryLock()) {
        return;   // a sync is already running; skipping is correct, not a compromise
    }
    try {
        String current = FileHashes.sha256HexOrEmpty(csvPath);
        if (current.equals(lastHash.get())) {
            return;
        }
        doSync();
        lastHash.set(current);
    } finally {
        syncLock.unlock();
    }
}

tryLock rather than lock: if a sync is already in progress there is nothing to gain by queueing behind it, since the second run would find the same hash and do nothing anyway.

Requests now only read. Freshness is bounded by the refresh interval and does not depend on traffic. And because the whole thing is idempotent, the scheduler's guarantees can be weak without consequence.

Two Spring traps worth knowing

Self-invocation defeats annotations. Caching the recommendation computation looks like this:

@Cacheable(value = "recommendations", key = "#propertyId")
public List<RecommendationResponse> recommendationsFor(String propertyId, int limit) { ... }

If another method in the same class calls recommendationsFor(...), the cache never engages. Spring implements these annotations with a proxy that wraps the bean; an internal call goes straight to this and never touches the proxy. The same applies to @Transactional, @Async, and @Retryable — and it fails silently, which is what makes it expensive. Nothing errors; the cache is simply always cold, or the transaction simply never starts.

The workaround you will find is to inject the bean into itself:

@Lazy
@Autowired
private MyService self;   // works, and tells you the class is doing too much

It does work. But needing a proxy reference to call your own method is the design telling you those are two responsibilities. Splitting the recommendation logic into its own bean makes the call external, makes the annotation fire, and leaves a class whose name says what it does:

@Service
public class RecommendationService {
    @Cacheable(value = "recommendations", key = "#propertyId")
    public List<RecommendationResponse> recommendationsFor(String propertyId, int limit) { ... }
}

The default cache is an unbounded map. spring-boot-starter-cache with no provider on the classpath gives you ConcurrentMapCacheManager. It has no maximum size, no TTL and no eviction: one entry per key, retained until the process dies. With a listing id as the key, that is one cached list per listing ever requested — a memory leak with a slow fuse and no error message.

Adding Caffeine and stating the bounds takes four lines:

spring:
  cache:
    cache-names: recommendations
    caffeine:
      spec: maximumSize=10000,expireAfterWrite=10m

The TTL also covers the case the explicit eviction misses. Recommendations depend on the interaction data and on the listing data, so a resync must invalidate the cache — and it is easy to wire eviction to one source and forget the other. An expiry bounds the staleness either way.

Errors: stop routing on strings

The first version of the error handling did this:

// Controller
if (message.equals("Login successful")) {
    return ResponseEntity.ok(new LoginResponse(message));
}
return ResponseEntity.badRequest().body(new ErrorResponse(message));

// Exception handler
HttpStatus status = ex.getMessage().startsWith("Property not found")
    ? HttpStatus.NOT_FOUND
    : HttpStatus.BAD_REQUEST;

Both of these route control flow on the text of a human-readable message. Rewording an error message changes an HTTP status code. Translating one breaks the API. And the second line throws NullPointerException for any IllegalArgumentException raised without a message, turning a 400 into a 500.

The type system already models this. A sealed result for the validation outcome:

public sealed interface LoginResult {
    record Success() implements LoginResult {}
    record Failure(String code, String message) implements LoginResult {}
}

and real exceptions for the not-found case, handled centrally rather than per controller:

@RestControllerAdvice
public class ApiExceptionHandler {

    @ExceptionHandler(PropertyNotFoundException.class)
    public ProblemDetail handleNotFound(PropertyNotFoundException ex) {
        ProblemDetail problem = ProblemDetail.forStatus(HttpStatus.NOT_FOUND);
        problem.setTitle("Property not found");
        problem.setDetail(ex.getMessage());
        return problem;
    }

    @ExceptionHandler(MethodArgumentNotValidException.class)
    public ProblemDetail handleValidation(MethodArgumentNotValidException ex) {
        ProblemDetail problem = ProblemDetail.forStatus(HttpStatus.BAD_REQUEST);
        problem.setTitle("Validation failed");
        problem.setProperty("errors", ex.getBindingResult().getFieldErrors().stream()
            .collect(toMap(FieldError::getField, FieldError::getDefaultMessage, (a, b) -> a)));
        return problem;
    }
}

ProblemDetail is RFC 7807 and built into Spring — a standard error shape for free, instead of inventing one. Collecting all field errors rather than getFieldError() matters too: that method returns the first error or null, so a client fixing a form gets one problem per round trip, and a validation failure with no field-level error NPEs the handler.

@RestControllerAdvice over per-controller handlers, so the second controller cannot forget.

Validation, and the rule that looked simple

The email rules were: present, structurally valid, and not from a specific blocked domain. Bean Validation covers the first two declaratively:

public record LoginRequest(
    @NotBlank(message = "Email is required")
    @Email(message = "Invalid email format")
    String email,

    @NotBlank(message = "Password is required")
    String password
) {}

The domain rule is where it got interesting. The obvious check:

email.toLowerCase().endsWith("@blocked.example");

user@blocked.example is rejected. user@mail.blocked.example is not — the @ in the pattern anchors it to the exact domain, so any subdomain sails through. Whether that is a bug depends entirely on intent, which is exactly why it is worth an explicit decision rather than a default:

private static final String BLOCKED_DOMAIN = "blocked.example";

private boolean isBlocked(String email) {
    int at = email.lastIndexOf('@');
    if (at < 0) {
        return false;
    }
    String domain = email.substring(at + 1).toLowerCase(Locale.ROOT);
    return domain.equals(BLOCKED_DOMAIN) || domain.endsWith("." + BLOCKED_DOMAIN);
}

lastIndexOf('@') rather than indexOf, because the local part of an address may legally contain an @ when quoted. Locale.ROOT rather than the default locale, because toLowerCase() in a Turkish locale maps I to ı and the comparison quietly stops matching — a genuine production bug, not a trivia question, and the reason Locale.ROOT belongs on every case-folding call that feeds a comparison.

Tests

Coverage percentage is the least interesting thing about a test suite, so: the parts that were worth writing.

The similarity math, against a hand-computed number. Three users, three listings, worked out on paper first:

sim(P1, P2) = 2 / (√2 · √3) = 0.8165

@Test
void cosineSimilarityMatchesHandComputedValue() {
    givenViews("p1", "u1", "u3");
    givenViews("p2", "u1", "u2", "u3");
    givenViews("p3", "u3");

    List<RecommendationResponse> result = recommendationService.recommendationsFor("p1", 10);

    assertThat(result).extracting(RecommendationResponse::propertyId)
        .containsExactly("p2", "p3");
    assertThat(result.get(0).similarityScore()).isCloseTo(0.8165, within(0.0001));
}

A test that recomputes the formula to check the formula proves nothing. The expected value has to come from outside the implementation.

CSV parsing edge cases, one file each. Quoted fields containing the delimiter, an empty file, a header with no rows, missing columns, rows with too few fields, multi-line quoted values, unusual characters. These are the fixtures that catch a "clever" split(";") the moment someone reaches for one.

Idempotency, explicitly. Sync twice, assert the second run changes nothing:

@Test
void secondSyncIsANoOp() {
    csvSyncService.syncAll();
    List<Property> afterFirst = propertyRepository.findAll();

    csvSyncService.syncAll();

    assertThat(propertyRepository.findAll())
        .usingRecursiveFieldByFieldElementComparator()
        .containsExactlyInAnyOrderElementsOf(afterFirst);
}

That property is the entire point of the import design, so it deserves a test that would fail loudly if someone "simplified" it back to truncate-and-reload.

And the one that is really about the build, not the code: the suite has to run on a machine that has nothing installed. My first version pointed at a local PostgreSQL with credentials in application.properties, and the tests were annotated @ActiveProfiles("test") — with no application-test.properties to back it. The profile resolved to nothing, the tests fell through to the main configuration, and they passed only on the one machine that happened to have that database running.

A clone-and-run failure is indistinguishable from a broken project to anyone who was not there. Two ways out, depending on how much fidelity you want:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

# src/test/resources/application-test.properties
spring.datasource.url=jdbc:h2:mem:listings;MODE=PostgreSQL;DB_CLOSE_DELAY=-1
spring.jpa.hibernate.ddl-auto=create-drop

H2 in PostgreSQL mode is instant and needs nothing installed, but it is not PostgreSQL — the native query above uses CTEs and SQRT, which both work, though a dialect difference will eventually bite. Testcontainers runs the real database in Docker and costs a few seconds of startup:

@Testcontainers
@SpringBootTest
class RecommendationIntegrationTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");
}

@ServiceConnection wires the datasource properties automatically, so there is no @DynamicPropertySource block to maintain.

The rule I settled on: unit tests and CSV parsing on H2 for speed, anything containing a native query on Testcontainers, because a query written in one dialect and tested in another is not tested.

Boundaries

Two smaller things that are easy to skip and annoying to retrofit.

Do not return entities from controllers. A JPA entity in a response signature couples the wire format to the schema: renaming a column changes the API, adding a field leaks it, and a lazy association serialises into either an N+1 or an exception depending on where the session ended. A record per response costs three lines:

public record PropertyResponse(String id, String name, String details,
                               String price, String image) {
    static PropertyResponse from(Property p) {
        return new PropertyResponse(p.getId(), p.getName(), p.getDetails(),
                                    p.getPrice(), p.getImage());
    }
}

Put the sync in a transaction. Delete-then-insert across separate transactions has a window where the data is neither the old state nor the new one, and a failure halfway leaves it there permanently. One @Transactional boundary around the reconciliation makes it atomic — and the batch flushes still keep the persistence context from growing without bound.

What I would do next

In rough order of value:

• Precompute the similarities. The query is fast enough per request, but recommendations do not change between interaction writes. A scheduled job writing the top-N per listing into a table turns every request into a single indexed read, and moves the cost off the request path entirely — the same move as the CSV sync, for the same reason.
• Handle cold start explicitly, with a popularity or attribute-based fallback when a listing has too few viewers for co-view data to mean anything.
• Add time decay, so a view from last year weighs less than one from this week.
• Paginate the listings endpoint. Returning the whole catalogue is fine at three rows and a mistake at thirty thousand.
• Replace ddl-auto with Flyway. Letting Hibernate generate the schema is fine for a demo and unacceptable the moment data must survive a restart — and the indexes above are exactly the kind of thing that belongs in a reviewed migration rather than in an entity annotation.
• Parse the price into BigDecimal. European decimal format stored as a string works right up until someone needs to sort or filter by it.

The thread running through all of it is the same one the recommendations started with: decide where each piece of work happens, and make sure it is not happening once per request because that was the easiest place to put it.