Build a large-scale order management system

Solution overview

In e-commerce, finance, and logistics, order volumes grow from millions to billions of rows as businesses scale. Traditional relational databases face storage bottlenecks and degraded query performance at this scale. The core challenge: querying massive order data by user, time range, and amount range while aggregating sales by product category.

A single Tablestore table supports petabyte-level storage and tens of millions of transactions per second (TPS). Search indexes support KEYWORD exact match, DOUBLE and LONG range queries, and TEXT tokenized fuzzy search. BoolQuery enables multi-condition filtering, and GroupBy handles aggregation.

Traditional order systems built on MySQL sharding with Elasticsearch face the following challenges at billion-row scale.

Dimension	Traditional (MySQL sharding + ES)	Tablestore
Storage	Requires sharding at billion-row scale with complex operations	Single table with petabyte-level storage and automatic sharding
Multi-dimensional queries	Requires a separate ES cluster; dual-write consistency is hard to maintain	Built-in search indexes with automatic data sync
Scaling	Shard migrations cause connection bottlenecks	Serverless with automatic scaling
Maintenance	Three systems to manage: MySQL, ES, and a sync pipeline	Single pay-as-you-go service, O&M-free

For new order systems, use Tablestore directly. For existing MySQL-based systems, use tools such as DTS, Canal, or DataX to synchronize data to Tablestore in real time. Tablestore handles multi-dimensional search and aggregation while MySQL focuses on online transaction processing.

Solution design

All order data goes into a single wide table. Attribute columns are schema-free. A search index on the table enables multi-dimensional queries and aggregation. The following table lists the fields and their index types.

Field type	Field	Data type	Index field type	Description
Primary key	_id	String	—	MD5 hash of the order ID. Distributes data across partitions to avoid write hotspots.
Attribute columns	orderId	String	KEYWORD	Order ID for exact-match queries.
	userId	String	KEYWORD	User ID for filtering orders by user.
	productName	String	TEXT	Product name. Uses max-semantic tokenization for fuzzy search.
	brand	String	KEYWORD	Product brand.
	category	String	KEYWORD	Product category (such as phones, computers, and headphones) for group-by aggregation.
	price	Double	DOUBLE	Unit price in CNY.
	quantity	Integer	LONG	Purchase quantity.
	totalPrice	Double	DOUBLE	Total order amount in CNY for range queries and aggregation.
	orderTime	Integer	LONG	Order timestamp in milliseconds for time-range queries and sorting.
	status	String	KEYWORD	Order status: paid, shipped, completed, or canceled.

Implementation

Before you begin, activate Tablestore and create an instance. Skip this if you already have one.

Note

The following examples use Java and Python. Before you begin, install and configure Java SDK or Python SDK.

Step 1: Create a data table and a search index

When you create a data table, specify only the primary key columns. Attribute columns are schema-free. After you create the table, create a search index. The index uses the following field types: KEYWORD for exact match, TEXT for tokenized fuzzy search, DOUBLE for range queries, and LONG for range queries and sorting.

Java

// Create a data table
private static void createTable(SyncClient client) {
    TableMeta tableMeta = new TableMeta("order_contract");
    tableMeta.addPrimaryKeyColumn(new PrimaryKeySchema("_id", PrimaryKeyType.STRING));

    TableOptions tableOptions = new TableOptions();
    tableOptions.setMaxVersions(1);
    tableOptions.setTimeToLive(-1);

    client.createTable(new CreateTableRequest(tableMeta, tableOptions));
}

// Create a search index
private static void createSearchIndex(SyncClient client) {
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    request.setTableName("order_contract");
    request.setIndexName("order_index");

    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
        new FieldSchema("orderId", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("userId", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("productName", FieldType.TEXT).setIndex(true)
            .setAnalyzer(FieldSchema.Analyzer.MaxWord),
        new FieldSchema("brand", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("category", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("price", FieldType.DOUBLE).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("quantity", FieldType.LONG).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("totalPrice", FieldType.DOUBLE).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("orderTime", FieldType.LONG).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("status", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true)
    ));
    request.setIndexSchema(indexSchema);
    client.createSearchIndex(request);
}

Python

def create_table_and_index(client):
    # Create a data table
    table_meta = tablestore.TableMeta("order_contract", [("_id", "STRING")])
    table_options = tablestore.TableOptions(time_to_live=-1, max_version=1)
    reserved = tablestore.ReservedThroughput(tablestore.CapacityUnit(0, 0))
    client.create_table(table_meta, table_options, reserved)

    # Create a search index
    fields = [
        tablestore.FieldSchema("orderId", tablestore.FieldType.KEYWORD,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("userId", tablestore.FieldType.KEYWORD,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("productName", tablestore.FieldType.TEXT,
                               index=True, analyzer="max_word"),
        tablestore.FieldSchema("brand", tablestore.FieldType.KEYWORD,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("category", tablestore.FieldType.KEYWORD,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("price", tablestore.FieldType.DOUBLE,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("quantity", tablestore.FieldType.LONG,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("totalPrice", tablestore.FieldType.DOUBLE,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("orderTime", tablestore.FieldType.LONG,
                               index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("status", tablestore.FieldType.KEYWORD,
                               index=True, enable_sort_and_agg=True),
    ]
    client.create_search_index("order_contract", "order_index",
                               tablestore.SearchIndexMeta(fields))

Step 2: Write test data

Use BatchWriteRow to write 10,000 test orders in batches. Each order contains a randomly generated user ID, product details, quantity, total amount, timestamp, and status. The primary key _id is the MD5 hash of the order ID. BatchWriteRow writes up to 200 rows per request.

Java

private static void writeTestData(SyncClient client) throws Exception {
    Random rand = new Random(42);
    long start = System.currentTimeMillis();
    int written = 0;
    long baseTime = 1704067200000L; // 2024-01-01 UTC
    long timeSpan = 2L * 365 * 24 * 3600 * 1000; // 2 years

    String[] PRODUCTS = {"iPhone 15", "MacBook Pro", "AirPods", "iPad Air",
                         "Galaxy S24", "Pixel 8", "ThinkPad X1", "Surface Pro",
                         "Xiaomi 14", "Huawei Mate 60"};
    String[] BRANDS = {"Apple", "Apple", "Apple", "Apple",
                       "Samsung", "Google", "Lenovo", "Microsoft",
                       "Xiaomi", "Huawei"};
    String[] CATEGORIES = {"Phone", "Computer", "Earphone", "Tablet",
                           "Phone", "Phone", "Computer", "Computer", "Phone", "Phone"};
    double[] PRICES = {7999, 14999, 1799, 4599, 6999, 4999, 9999, 8999, 3999, 6999};
    String[] USERS = {"u00001", "u00002", "u00003", "u00004", "u00005",
                      "u00006", "u00007", "u00008", "u00009", "u00010"};
    String[] STATUSES = {"Paid", "Shipped", "Completed", "Cancelled"};

    while (written < 10000) {
        BatchWriteRowRequest batch = new BatchWriteRowRequest();
        int batchSize = Math.min(200, 10000 - written);

        for (int i = 0; i < batchSize; i++) {
            int idx = written + i;
            String orderId = String.format("ORD%08d", idx);
            String id = md5(orderId);
            String userId = USERS[rand.nextInt(USERS.length)];
            int prodIdx = rand.nextInt(PRODUCTS.length);
            int quantity = 1 + rand.nextInt(5);
            double totalPrice = Math.round(PRICES[prodIdx] * quantity * 100.0) / 100.0;
            long orderTime = baseTime + (long)(rand.nextDouble() * timeSpan);

            PrimaryKeyBuilder pkb = PrimaryKeyBuilder.createPrimaryKeyBuilder();
            pkb.addPrimaryKeyColumn("_id", PrimaryKeyValue.fromString(id));
            RowPutChange row = new RowPutChange("order_contract", pkb.build());
            row.addColumn("orderId", ColumnValue.fromString(orderId));
            row.addColumn("userId", ColumnValue.fromString(userId));
            row.addColumn("productName", ColumnValue.fromString(PRODUCTS[prodIdx]));
            row.addColumn("brand", ColumnValue.fromString(BRANDS[prodIdx]));
            row.addColumn("category", ColumnValue.fromString(CATEGORIES[prodIdx]));
            row.addColumn("price", ColumnValue.fromDouble(PRICES[prodIdx]));
            row.addColumn("quantity", ColumnValue.fromLong(quantity));
            row.addColumn("totalPrice", ColumnValue.fromDouble(totalPrice));
            row.addColumn("orderTime", ColumnValue.fromLong(orderTime));
            row.addColumn("status", ColumnValue.fromString(
                STATUSES[rand.nextInt(STATUSES.length)]));
            batch.addRowChange(row);
        }
        client.batchWriteRow(batch);
        written += batchSize;
    }
    System.out.println("Written 10000 rows in " + (System.currentTimeMillis() - start) + "ms");
}

Python

import hashlib, random

def write_test_data(client):
    rand = random.Random(42)
    base_time = 1704067200000  # 2024-01-01 UTC
    time_span = 2 * 365 * 24 * 3600 * 1000  # 2 years

    PRODUCTS = ["iPhone 15", "MacBook Pro", "AirPods", "iPad Air",
                "Galaxy S24", "Pixel 8", "ThinkPad X1", "Surface Pro",
                "Xiaomi 14", "Huawei Mate 60"]
    BRANDS = ["Apple", "Apple", "Apple", "Apple",
              "Samsung", "Google", "Lenovo", "Microsoft", "Xiaomi", "Huawei"]
    CATEGORIES = ["Phone", "Computer", "Earphone", "Tablet",
                  "Phone", "Phone", "Computer", "Computer", "Phone", "Phone"]
    PRICES = [7999, 14999, 1799, 4599, 6999, 4999, 9999, 8999, 3999, 6999]
    USERS = [f"u{i:05d}" for i in range(1, 11)]
    STATUSES = ["Paid", "Shipped", "Completed", "Cancelled"]
    written = 0

    while written < 10000:
        batch_size = min(200, 10000 - written)
        put_rows = []

        for i in range(batch_size):
            idx = written + i
            order_id = f"ORD{idx:08d}"
            _id = hashlib.md5(order_id.encode()).hexdigest()
            user_id = rand.choice(USERS)
            prod_idx = rand.randint(0, len(PRODUCTS) - 1)
            quantity = 1 + rand.randint(0, 4)
            total_price = round(PRICES[prod_idx] * quantity, 2)
            order_time = base_time + int(rand.random() * time_span)

            pk = [("_id", _id)]
            cols = [
                ("orderId", order_id), ("userId", user_id),
                ("productName", PRODUCTS[prod_idx]),
                ("brand", BRANDS[prod_idx]),
                ("category", CATEGORIES[prod_idx]),
                ("price", float(PRICES[prod_idx])),
                ("quantity", quantity),
                ("totalPrice", float(total_price)),
                ("orderTime", order_time),
                ("status", rand.choice(STATUSES)),
            ]
            row = tablestore.Row(pk, cols)
            put_rows.append(tablestore.PutRowItem(
                row, tablestore.Condition(tablestore.RowExistenceExpectation.IGNORE)))

        request = tablestore.BatchWriteRowRequest()
        request.add(tablestore.TableInBatchWriteRowItem("order_contract", put_rows))
        client.batch_write_row(request)
        written += batch_size

Step 3: Query data

After writing data, wait for the search index to synchronize — typically seconds to minutes depending on data volume. The following examples cover three common query scenarios.

Scenario 1: Query orders by user

Use TermQuery to match a user ID exactly and return all orders for that user, sorted by order time in descending order.

Java

// Query all orders for user u00001, sorted by order time in descending order
TermQuery userQuery = new TermQuery();
userQuery.setFieldName("userId");
userQuery.setTerm(ColumnValue.fromString("u00001"));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(userQuery);
searchQuery.setGetTotalCount(true);
searchQuery.setLimit(5);
searchQuery.setSort(new Sort(Arrays.asList(
    new FieldSort("orderTime", SortOrder.DESC))));

SearchRequest request = new SearchRequest("order_contract", "order_index", searchQuery);
SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
columnsToGet.setReturnAll(true);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println("Total hits: " + response.getTotalCount());
for (Row row : response.getRows()) {
    System.out.println(row);
}

Python

# Query all orders for user u00001, sorted by order time in descending order
result = client.search("order_contract", "order_index",
    tablestore.SearchQuery(
        tablestore.TermQuery("userId", "u00001"),
        get_total_count=True, limit=5,
        sort=tablestore.Sort(sorters=[
            tablestore.FieldSort("orderTime", tablestore.SortOrder.DESC)])),
    tablestore.ColumnsToGet(return_type=tablestore.ColumnReturnType.ALL))

print(f"Total hits: {result.total_count}")
for row in result.rows:
    cols = {c[0]: c[1] for c in row[1]}
    print(f"orderId={cols['orderId']} | product={cols['productName']}"
          f" | totalPrice={cols['totalPrice']} | status={cols['status']}")

Scenario 2: Multi-condition compound query

Use BoolQuery to combine TermQuery (user filter), RangeQuery (amount range), and RangeQuery (time range). This retrieves orders for a specific user within a given time period where the total amount exceeds CNY 5,000.

Java

// Filter by user: userId = "u00001"
TermQuery userQuery = new TermQuery();
userQuery.setFieldName("userId");
userQuery.setTerm(ColumnValue.fromString("u00001"));

// Amount range: totalPrice >= 5000
RangeQuery priceQuery = new RangeQuery();
priceQuery.setFieldName("totalPrice");
priceQuery.greaterThanOrEqual(ColumnValue.fromDouble(5000));

// Time range: full year 2024
RangeQuery timeQuery = new RangeQuery();
timeQuery.setFieldName("orderTime");
timeQuery.greaterThanOrEqual(ColumnValue.fromLong(1704067200000L)); // 2024-01-01
timeQuery.lessThan(ColumnValue.fromLong(1735689600000L));           // 2025-01-01

// Combine three conditions with BoolQuery
BoolQuery boolQuery = new BoolQuery();
boolQuery.setMustQueries(Arrays.asList(userQuery, priceQuery, timeQuery));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(boolQuery);
searchQuery.setGetTotalCount(true);
searchQuery.setLimit(5);
searchQuery.setSort(new Sort(Arrays.asList(
    new FieldSort("totalPrice", SortOrder.DESC))));

SearchRequest request = new SearchRequest("order_contract", "order_index", searchQuery);
SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
columnsToGet.setReturnAll(true);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println("Total hits: " + response.getTotalCount());
for (Row row : response.getRows()) {
    System.out.println(row);
}

Python

# Filter by user
user_query = tablestore.TermQuery("userId", "u00001")
# Amount range: >= 5000
price_query = tablestore.RangeQuery("totalPrice",
    range_from=5000, include_lower=True)
# Time range: full year 2024
time_query = tablestore.RangeQuery("orderTime",
    range_from=1704067200000, range_to=1735689600000,
    include_lower=True, include_upper=False)

# Combine three conditions with BoolQuery
bool_query = tablestore.BoolQuery(
    must_queries=[user_query, price_query, time_query])

result = client.search("order_contract", "order_index",
    tablestore.SearchQuery(bool_query, get_total_count=True, limit=5,
        sort=tablestore.Sort(sorters=[
            tablestore.FieldSort("totalPrice", tablestore.SortOrder.DESC)])),
    tablestore.ColumnsToGet(return_type=tablestore.ColumnReturnType.ALL))

print(f"Total hits: {result.total_count}")
for row in result.rows:
    cols = {c[0]: c[1] for c in row[1]}
    print(f"orderId={cols['orderId']} | product={cols['productName']}"
          f" | totalPrice={cols['totalPrice']}")

Scenario 3: Aggregate by product category

Use GroupByField to group orders by product category and calculate the order count and total revenue per category, sorted by total revenue in descending order.

Java

SearchRequest request = SearchRequest.newBuilder()
    .tableName("order_contract").indexName("order_index")
    .searchQuery(SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .addGroupBy(GroupByBuilders.groupByField("orderCategory", "category")
            .size(20)
            .addSubAggregation(AggregationBuilders.count("orderCount", "orderId"))
            .addSubAggregation(AggregationBuilders.sum("totalRevenue", "totalPrice"))
            .addGroupBySorter(GroupBySorter.subAggSortInDesc("totalRevenue"))
            .build())
        .build())
    .build();

SearchResponse response = client.search(request);
GroupByFieldResult results = response.getGroupByResults()
    .getAsGroupByFieldResult("orderCategory");
for (GroupByFieldResultItem item : results.getGroupByFieldResultItems()) {
    long count = item.getSubAggregationResults()
        .getAsCountAggregationResult("orderCount").getValue();
    double revenue = item.getSubAggregationResults()
        .getAsSumAggregationResult("totalRevenue").getValue();
    System.out.println("Category: " + item.getKey()
        + " | Order count: " + count
        + " | Total revenue: " + String.format("%.2f", revenue));
}

Python

group_by = tablestore.GroupByField("category", size=20,
    sub_aggs=[
        tablestore.Count("orderId", name="orderCount"),
        tablestore.Sum("totalPrice", name="totalRevenue"),
    ],
    group_by_sort=[tablestore.SubAggSort(tablestore.SortOrder.DESC, "totalRevenue")],
    name="orderCategory")

result = client.search("order_contract", "order_index",
    tablestore.SearchQuery(tablestore.MatchAllQuery(), limit=0,
                           group_bys=[group_by]),
    tablestore.ColumnsToGet(return_type=tablestore.ColumnReturnType.NONE))

for item in result.group_by_results[0].items:
    aggs = {a.name: a.value for a in item.sub_aggs}
    print(f"Category: {item.key} | Orders: {int(aggs['orderCount'])}"
          f" | Total revenue: {aggs['totalRevenue']:.2f}")

Clean up resources

Note

To avoid unnecessary charges, delete the resources created in this tutorial when you no longer need them.

Delete the search index first, then the data table. If you created a new instance for this tutorial, release it last.

Java

private static void cleanup(SyncClient client) {
    // 1. Delete the search index
    DeleteSearchIndexRequest deleteIndexReq = new DeleteSearchIndexRequest();
    deleteIndexReq.setTableName("order_contract");
    deleteIndexReq.setIndexName("order_index");
    client.deleteSearchIndex(deleteIndexReq);

    // 2. Delete the data table
    client.deleteTable(new DeleteTableRequest("order_contract"));
}

Python

def cleanup(client):
    # 1. Delete the search index
    client.delete_search_index("order_contract", "order_index")
    # 2. Delete the data table
    client.delete_table("order_contract")