Schemaless Document Model for Trading System Order Storage

Building on the previous discussion of event‑driven trading systems, this article introduces a document‑storage model for order data. Instead of multiple relational tables, each order is flattened into a JSON string and stored as a single record in a relational table, using a schemaless approach to lower schema‑change costs and increase business flexibility.

Problems with Relational Data Model The relational model causes frequent schema modifications, heavy index tuning, slow queries, deadlocks, and complex ER diagrams, especially when handling billions of orders.

Document Model Orders are split into hierarchical JSON nodes (e.g., quote snapshot, payment, fund, sub‑orders). Each top‑level node is serialized as a string and stored in a dedicated column, allowing easy structural changes in code and clearer representation of one‑to‑many, one‑to‑one, and many‑to‑many relationships. Queries based on order ID become more efficient because all data resides in one row.

Implementation on Relational Database A single "order_detail" table stores key‑value pairs where the key is the first‑level node name and the value is the serialized JSON. Each order occupies one row with a monotonically increasing version number to support optimistic locking and cache consistency.

Version‑Based Optimistic Lock Every update increments the order's version. Updates succeed only if the version read by the business matches the current version in the database, preventing lost updates and reducing lock contention across independent sub‑nodes.

Consistency Guarantees via Version Number Reads first check the cache, then the replica, and finally the primary database for the latest version. If the cached version is stale, the system falls back to the primary to ensure strong consistency while minimizing primary load.

Search Since the document model only supports order‑ID lookups via SQL, a separate Elasticsearch cluster is used for full‑text and other query types. Order changes are streamed in near real‑time to Elasticsearch, providing fast, albeit eventually consistent, search capabilities.

Conclusion By adopting a schemaless JSON document model with versioned optimistic locking, the trading system achieves lower schema‑change overhead, higher flexibility, and consistent data updates, while supplementing order‑ID queries with an Elasticsearch‑based search engine and offering API helpers to simplify development.