Real-time Search Ranking Intervention Using Alibaba Cloud HybridDB for PostgreSQL

This article describes how Xianyu builds a real‑time intervention capability for search ranking by leveraging Alibaba Cloud HybridDB for PostgreSQL.

Challenges : the feed‑type scenarios require second‑level latency, heterogeneous data sources (offline product data, real‑time updates, algorithmic features) and the ability to recompute scores instantly when a product is created or modified.

Solution Overview : a pipeline that normalizes heterogeneous sources, merges data using JSONB, and notifies downstream services via PostgreSQL's NOTIFY mechanism.

PostgreSQL capabilities : native MPP, parallel and vector processing, JSON/JSONB full‑text search, and trigger/notification support.

Heterogeneous data source integration : all sources are converted to a unified JSON format and sent through an asynchronous message bus to a normalization service.

Normalization service performs data validation, enrichment, format conversion and monitoring before passing clean data downstream.

Data merge strategy stores product attributes in a JSONB column. The merge logic uses timestamps to resolve conflicts. Example table definition:

create table Test ( id int8 primary key, att jsonb );

The merge function (simplified) is:

create or replace function merge_json(jsonb, jsonb) returns jsonb as $$
  select jsonb_object_agg(key, value)
  from (
    select coalesce(a.key, b.key) as key,
           case when a.value::timestamp > b.value::timestamp then a.value else b.value end as value
    from jsonb_each($1) a full outer join jsonb_each($2) b using (key)
  ) t;
$$ language sql strict;

When a conflict occurs on id , the trigger calls this function to merge the JSON attributes.

Trigger design :

CREATE OR REPLACE FUNCTION notify1() RETURNS trigger AS $function$
DECLARE
BEGIN
  PERFORM pg_notify('a', format('CLASS:notify, ID:%s, ATT:%s', NEW.id, NEW.att));
  RETURN NULL;
END;
$function$ LANGUAGE plpgsql STRICT;

CREATE TRIGGER tg1 AFTER INSERT OR UPDATE ON Test
FOR EACH ROW EXECUTE PROCEDURE notify1();

The downstream service listens on channel a using JDBC/PGConnection, receives notifications, and re‑scores items via a rule engine, finally dumping the updated scores to the search engine.

Performance tests with 10 million rows show that the additional trigger overhead still meets the required write latency, and the real‑time intervention improves transaction volume by ~30% and positive feedback by ~28%.

In summary, the combination of PostgreSQL's JSONB merge, trigger/notify mechanisms, and Alibaba Cloud HybridDB enables second‑level real‑time search ranking adjustments for Xianyu’s marketplace.