Storing Order Book Snapshots as Timestamped Level Changes
Summary
The document discusses a compact relational schema for subsecond order book data. Rather than creating a separate table for each capture time, it proposes storing observations in one table with timestamp, price, size, depth level, and side fields. This lets each row identify a particular bid or offer level at a given time.
It also suggests recording only levels whose values change between snapshots. The example shows two changed levels producing two new rows, while unchanged levels need no duplicate records. To reconstruct a level at a chosen time, query its latest stored value at or before that time. This is a practical event-style storage approach that can reduce repeated data, though the document does not discuss retention, indexing strategy, database performance, or how to represent deleted levels beyond an example with zero size.
Key ideas
- Use one table with timestamp, price, size, depth, and bid-or-offer type fields.
- Store order book levels as rows rather than creating a table for each snapshot.
- Record only levels that changed since the prior snapshot to reduce repeated data.
- Retrieve a level's latest known value by selecting its most recent record at or before the target time.
Tags
Full text
# Orderbook db structure
# Orderbook db structure
I am currently saving a sub 1 sec snapshot of an orderbook to my SQL db. However I have quite the trouble on figuring out the architecture of this DB
What I'm currently doing is saving a table with the data and naming the table the current epoch. As you can see this is a horrible way of doing it.
What would you guys recommend on doing to make this more efficient and less messy.
The data i'm currently storing is only: time,size,price Thanks in advance you guys are awesome!
## Answer by Attack68 (score 2, accepted)
https://quant.stackexchange.com/a/51458
Have you considered storing it by adding the additional fields ('level' or 'depth') and ('type'), then you can have a table which looks like this;
```
time size price depth type
xxx 100 100.03 1 bid
xxx 2000 100.025 2 bid
xxx 0 100.02 3 bid
xxx 33 100.035 1 offer
xxx 45 100.04 2 offer
xxx 550 100.045 3 offer
```
Additionally you can even compare the next timesnap with the previous and only create a new entry if data changes. The smaller your time difference the greater the data storage efficiency will be. For example suppose the next time step there were only two changes; a new level 3 bid and the offer had been reduced at level 1 due to trading or ortherwise, then you only create 2 rows;
```
xxx+1 250 100.02 3 bid
xxx+1 23 100.035 1 offer
```
You can always index the table for a given timestamp with a simple SQL;
```
SELECT TOP 1 *
FROM table
WHERE type='bid' AND level=1 AND time <= xxx+1
ORDER BY time DESC
```Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.