How SHOPLINE Reduced API Latency by 76.7% with RisingWave

SHOPLINE, Asia's leading commerce enablement platform, reduced API response times by 76.7% after adopting RisingWave — a PostgreSQL-compatible streaming database. The key was replacing a complex dual-pipeline Lambda architecture with a single streaming SQL layer that keeps merchant dashboards fresh in real time.

The Problem: Slow Dashboards Hurt Merchant Trust

When merchants log in to check their storefront performance — GMV, order volume, conversion rates, channel breakdowns — they expect instant answers. A two-second lag might seem minor, but at scale it compounds into a credibility problem. Merchants making decisions about flash sale pricing, inventory reallocation, or campaign spend cannot afford stale data.

SHOPLINE's original architecture was a textbook Lambda setup: a batch pipeline computed daily and hourly aggregates in a data warehouse, while a separate streaming pipeline maintained real-time counters in a key-value store. Business logic had to be implemented twice — once in the batch layer and once in the streaming layer — which created consistency bugs, doubled maintenance costs, and still produced noticeable latency spikes whenever the two layers diverged.

The symptoms were familiar to any data engineer who has operated Lambda at scale:

• Duplicated logic: Any change to GMV calculation had to be deployed in two separate codebases.
• Inconsistency windows: The batch and streaming numbers disagreed for several minutes after each batch run.
• Slow API responses: Dashboard APIs queried the data warehouse directly, with aggregation happening at query time — under load, this pushed p95 response times well past acceptable thresholds.

How Streaming SQL Solves This

RisingWave is a PostgreSQL-compatible streaming database that incrementally maintains materialized views over streaming data. Instead of recomputing aggregates from scratch on every API request, RisingWave keeps those aggregates continuously updated as new events arrive from Kafka. When the API queries RisingWave, it reads pre-computed results — the equivalent of a cache that is always consistent with the source of truth.

This changes the performance equation entirely. API latency is no longer a function of query complexity or data volume; it is simply the time to do a point lookup or a small scan against pre-materialized results. That is why SHOPLINE saw a 76.7% reduction in API response times: the expensive work moved out of the query path and into the continuous maintenance layer.

The architectural shift also eliminated the Lambda duality. There is now one SQL transformation layer — no separate batch jobs, no dual codebases, no consistency windows.

Architecture: Bronze → Silver → Gold → Application DB

SHOPLINE's new stack is a Medallion architecture with three logical layers, all driven by streaming SQL.

Step 1: Data Source Setup

Raw order events land in Kafka from multiple channels — web storefronts, mobile apps, POS terminals, and marketplace integrations (Shopee, Lazada, and others). RisingWave connects to this stream directly:

CREATE SOURCE orders (
    order_id        VARCHAR,
    merchant_id     VARCHAR,
    customer_id     VARCHAR,
    channel         VARCHAR,        -- 'web', 'mobile', 'pos', 'marketplace'
    payment_method  VARCHAR,        -- 'card', 'alipay', 'paypal', 'cod'
    subtotal        NUMERIC,
    discount        NUMERIC,
    total           NUMERIC,
    status          VARCHAR,
    created_at      TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'ecommerce.orders',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

The Bronze layer is essentially this raw ingest — Kafka topics backed by object storage (S3 or compatible) for replay and auditing.

Step 2: Core Materialized View (Silver Layer)

The Silver layer cleans the data and builds wide tables that join orders with product and merchant metadata:

CREATE MATERIALIZED VIEW silver_orders AS
SELECT
    o.order_id,
    o.merchant_id,
    o.customer_id,
    o.channel,
    o.payment_method,
    o.subtotal,
    o.discount,
    o.total,
    o.status,
    o.created_at,
    m.store_name,
    m.region,
    m.tier
FROM orders o
JOIN merchants m ON o.merchant_id = m.merchant_id;

Because this is a materialized view in RisingWave, it is kept incrementally up to date — every new order event triggers an incremental update, not a full recomputation.

Step 3: Aggregations (Gold Layer)

The Gold layer pre-aggregates the metrics that merchant dashboards and internal analytics tools actually query:

CREATE MATERIALIZED VIEW gold_merchant_gmv AS
SELECT
    merchant_id,
    store_name,
    channel,
    WINDOW_START                         AS window_start,
    WINDOW_END                           AS window_end,
    COUNT(*)                             AS order_count,
    SUM(total)                           AS gmv,
    SUM(discount)                        AS total_discount,
    AVG(total)                           AS aov,
    COUNT(DISTINCT customer_id)          AS unique_buyers
FROM TUMBLE(silver_orders, created_at, INTERVAL '1 HOUR')
WHERE status != 'cancelled'
GROUP BY merchant_id, store_name, channel, WINDOW_START, WINDOW_END;

This is the view that the dashboard API queries. With results pre-computed and maintained continuously, each API call is a simple indexed lookup.

Step 4: Downstream / Serving

Aggregated results are sunk to the application database (e.g., PostgreSQL) that the merchant dashboard backend reads from:

CREATE SINK merchant_gmv_to_postgres
FROM gold_merchant_gmv
WITH (
    connector = 'jdbc',
    jdbc.url = 'jdbc:postgresql://app-db:5432/shopline',
    table.name = 'merchant_gmv_hourly',
    type = 'upsert',
    primary_key = 'merchant_id,channel,window_start'
);

The application database always has fresh, pre-aggregated data. Dashboard API endpoints do simple SELECT queries — no joins, no aggregations at query time.

Before vs. After: Lambda vs. Kappa

The SHOPLINE Outcome

"By adopting RisingWave, we moved to a cost-effective Kappa architecture. It provides the low-latency real-time analytics we need and delivers a much smoother experience for our merchants."

— Henry Chi, Manager, Data Team, SHOPLINE

The 76.7% reduction in API response times was not a one-time optimization; it is a structural property of the new architecture. As SHOPLINE adds new merchants and channels, the serving-layer query patterns stay fast because the aggregation work happens continuously in RisingWave, not on each API request.

FAQ

Q: Does RisingWave replace the data warehouse entirely? Not necessarily. In SHOPLINE's architecture, the Gold layer feeds both the application database (for low-latency dashboard queries) and the data warehouse (for ad hoc analysis and historical reporting). RisingWave handles the real-time path; the warehouse handles historical depth.

Q: How does RisingWave handle late-arriving events? RisingWave supports watermark-based late-data handling. You can configure a watermark offset on your time column, and the system will wait for late events up to that threshold before finalizing a window aggregate.

Q: Is SQL knowledge sufficient to operate this architecture? Yes. RisingWave uses standard PostgreSQL-compatible SQL. Data engineers familiar with CREATE MATERIALIZED VIEW, window functions, and JOIN syntax can build and maintain the full pipeline without learning a new DSL.

Key Takeaways

• SHOPLINE achieved a 76.7% reduction in API response times by moving from Lambda to Kappa architecture with RisingWave.
• The core insight: move aggregation work out of the query path and into continuous incremental maintenance.
• A Medallion architecture (Bronze → Silver → Gold → Application DB) provides a clean separation between raw ingest, transformation, and serving.
• RisingWave's PostgreSQL compatibility means no new language to learn — standard SQL drives the entire real-time pipeline.
• Eliminating dual pipelines reduces both operational complexity and the risk of batch/streaming inconsistency.

Get started | Slack