A Practical Guide to Vector Databases for Personalized Recommendation in UGC Communities

1. Scenario and Goals

UGC communities share some defining traits: huge content volume, rapid updates, and a heavy long tail. A recommendation system has to balance both the user’s immediate, in-the-moment interest and their stable, long-standing preferences, completing multi-path candidate recall and fusion within a single request while keeping latency in the millisecond range. This article presents a practical approach built on dual-vector user interest + multi-path recall in a single SQL statement, with OceanBase native vectors at the database layer. Structured data and vectors live in the same database, sidestepping the “two-database sync / consistency” trap.

---

2. Why OceanBase (Three Quick Reasons)

All-in-one: Structured tables + VECTOR columns + HNSW/IVF vector indexes all live in the same database, with native support for transactional consistency (you can update the view count and the short-term vector within a single transaction).

MySQL-compatible stack: Low onboarding and operational cost, with smooth migration.

Distributed elasticity: When your content store grows and QPS fluctuates, horizontal scaling stays comfortable.

---

3. Schema Design (Modeling Example)

Content table (posts / short videos, etc.)

CREATE TABLE posts (
  post_id        BIGINT PRIMARY KEY,
  author_id      BIGINT,
  title          VARCHAR(255),
  content        TEXT,
  created_at     DATETIME,
  status         TINYINT DEFAULT 1,           -- 1 = published
  pop_7d         FLOAT DEFAULT 0,             -- popularity over the last 7 days
  content_vector VECTOR(768),                 -- native vector
  VECTOR KEY idx_vec (content_vector)
    WITH (DISTANCE = COSINE, TYPE = HNSW, M=16, EF_CONSTRUCTION=200, EF_SEARCH=64)
);

User table (dual vectors: short-term + long-term)

CREATE TABLE users (
  user_id            BIGINT PRIMARY KEY,
  short_term_vector  VECTOR(768),  -- immediate interest (updated at the second level)
  long_term_vector   VECTOR(768),  -- stable preference (updated daily / hourly)
  region             VARCHAR(32),
  updated_at         DATETIME
);

Behavior table (dedup / features)

CREATE TABLE user_actions (
  user_id  BIGINT,
  post_id  BIGINT,
  action   ENUM('view','like','collect','comment','share'),
  ts       DATETIME,
  PRIMARY KEY(user_id, post_id, action, ts)
);

---

4. Dual-Vector Interest: How to Produce It and How to Update It

4.1 Training and Production (In Brief)

Long-term vector: A two-tower / contrastive-learning model aggregates behavior over many historical days (mean / attention pooling), refreshed daily or hourly.

Short-term vector: A session-level sequence (the most recent N impressions / clicks) is fed through a lightweight Transformer / SASRec, updated in real time at the second level.

4.2 Online Updates

Update the view count and the short-term vector within the same transaction, avoiding a “count and profile out of sync” situation:

BEGIN;
UPDATE posts SET view_count = view_count + 1 WHERE post_id = ?;
UPDATE users SET short_term_vector = ? WHERE user_id = ?;
COMMIT;

Online fusion of the short-term vector

def update_short_term_vector(user_id, post_vec, action):
    w = {'view':0.1, 'like':0.3, 'collect':0.5}.get(action, 0.1)
    new_vec = 0.85 * current_short_vec(user_id) + 0.15 * w * post_vec
    sql("UPDATE users SET short_term_vector=? WHERE user_id=?", [new_vec, user_id])

Put simply: the short-term vector chases novelty, the long-term vector holds steady. Only when both coexist do you avoid recommendations that grow “ever narrower” or “ever slower.”

---

5. One Query, Multi-Path Recall (The Core SQL)

The goal: in a single request, recall both “short-term interest neighbors” and “long-term interest neighbors,” layer in freshness and popularity, then rank everything together.

Key principle: strong filtering comes first (published status, time window, region/category, etc.); don’t skip filtering and then watch your P95 blow up.

-- Fetch the user's two vectors
WITH user_vectors AS (
  SELECT short_term_vector AS svec, long_term_vector AS lvec
  FROM users WHERE user_id = :uid
),
-- Path 1: short-term interest recall (fast response, captures current focus)
short_pool AS (
  SELECT p.post_id, p.title,
         COSINE_SIMILARITY(p.content_vector, uv.svec) AS sim,
         p.created_at, p.pop_7d, 'short' AS src
  FROM posts p, user_vectors uv
  WHERE p.status=1
    AND p.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)           -- strong freshness filter
    AND NOT EXISTS (SELECT 1 FROM user_actions ua
                    WHERE ua.user_id=:uid AND ua.post_id=p.post_id) -- exclude already seen
  ORDER BY p.content_vector <-> uv.svec
  LIMIT 200
),
-- Path 2: long-term interest recall (stable preference)
long_pool AS (
  SELECT p.post_id, p.title,
         COSINE_SIMILARITY(p.content_vector, uv.lvec) AS sim,
         p.created_at, p.pop_7d, 'long' AS src
  FROM posts p, user_vectors uv
  WHERE p.status=1
    AND p.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
  ORDER BY p.content_vector <-> uv.lvec
  LIMIT 200
),
-- Fusion + scoring (semantic similarity + freshness + popularity)
merged AS (
  SELECT post_id, title, src,
         (CASE src WHEN 'short' THEN 0.7 ELSE 0.3 END) * sim      -- dual-vector weights
         + 0.1 * LOG(1 + pop_7d)
         + 0.2 * EXP(- TIMESTAMPDIFF(HOUR, created_at, NOW()) / 72.0) AS score
  FROM short_pool
  UNION ALL
  SELECT post_id, title, src,
         (CASE src WHEN 'short' THEN 0.7 ELSE 0.3 END) * sim
         + 0.1 * LOG(1 + pop_7d)
         + 0.2 * EXP(- TIMESTAMPDIFF(HOUR, created_at, NOW()) / 72.0) AS score
  FROM long_pool
)
SELECT post_id, title, MAX(score) AS final_score
FROM merged
GROUP BY post_id, title
ORDER BY final_score DESC
LIMIT 50;

Key takeaways

1. Use COSINE_SIMILARITY to get similarity directly (0–1) rather than computing distance and then 1 - distance. Threshold semantics stay crisp.

2. Apply freshness and popularity as light-weight boosts, so pure semantics doesn’t push “ancient posts” to the top.

3. The “already seen” filter must happen in the DB layer, avoiding inconsistencies from re-joining on the service side.

---

6. Re-ranking and Diversity (Simplified, Production-Ready)

Re-ranking: Start with a lightweight GBDT/LightGBM, using sim_short, sim_long, pop_7d, age, and the like as features; move to a DNN once you have the budget.

Diversity: Use MMR (Maximal Marginal Relevance) to penalize overly similar posts, controlling the share of topics/authors/price bands and avoiding filter bubbles.

Business constraints: Status, compliance, allow/deny lists, ad blending (normalize everything uniformly to avoid scale conflicts).

---

7. Performance and Operations (Lessons From the Trenches)

Strong filtering first: status, time window, and region/category must be filtered before KNN, to reduce the amount of data participating in the vector search.

Index parameters: A/B load-test HNSW’s M/EF_SEARCH (recall@K vs. P95); for very large content sets, use hot-cold tiering (hot: HNSW; cold: IVF-PQ).

Consistency: Update the user’s short-term vector and write the behavior record in the same transaction; when you switch model generations, remember to apply vector versioning and gray rollout.

Metrics: Monitor CTR/CVR, P50/P95, the contribution share of the short-term vs. long-term pools, the already-seen hit rate, and freshness metrics online.

---

8. MVP Roadmap (Deliverable in Two Weeks)

Create tables: posts / users / user_actions (as above).

Vectors: Produce long_term_vector offline; update short_term_vector via a real-time stream.

Recall: Use the “multi-path recall in a single SQL” above — it runs out of the box.

Re-ranking: Start with GBDT to support fast feature iteration.

Monitoring: Instrument and record sim_top1, source pool (short / long), latency, dedup rate, and the impression-to-click funnel.

Iteration: Each week, recalibrate the HNSW parameters and the 0.7/0.3 weights, plus the freshness decay coefficient.

---

9. FAQ (Answers Mapped to Real Pitfalls)

• Q: Can I just use a single user vector?

A: Not recommended. Interest in a UGC community is multimodal, and a single vector easily becomes “lopsided.” Short-term chasing novelty + long-term holding steady is the essence of this approach.

• Q: Why put fusion and ranking inside the database?

A: One fewer network round trip plus good data locality means more stable latency; the SQL is clear and auditable, making postmortems easier.

• Q: How do you cold-start new content?

A: Content vector + freshness weighting + small-traffic exploration (ε-greedy / UCB). If there’s a creator relationship, prioritize pushing to the short-term neighbors of the creator’s followers.

---

Closing Thoughts

Recommendation in a UGC community doesn’t need a fancy stack. It comes down to three things:

First, dual-vector modeling that covers both immediate interest and stable preference;

second, using OceanBase native vectors to keep “structured data + vectors + transactions” in a single database;

third, using multi-path recall in a single SQL to fuse the short-term pool, the long-term pool, and time and popularity together, for stable end-to-end speedups.

Ship the structured tables, SQL, and update strategy from this article, and you’ll have a fast, stable, and iterable personalized recommendation system for your UGC community.

Finally, I’d like to recommend the WeChat official account “Lao Ji’s Tech Talk” run by Lao Ji, the head of OceanBase Open Source. It continuously publishes all kinds of technical content related to #Databases, #AI, and #Tech Architecture. If you’re interested, feel free to follow!

“Lao Ji’s Tech Talk” hopes not only to keep bringing you valuable technical content, but also to contribute to the open source community together with everyone. If you appreciate the OceanBase open source community, light up a little star ✨! Every Star you give is fuel for our efforts.