Effects of Storing Financial Numbers as Integers over Decimals: Huge Data Savings

Eric
Goal: Explore the effects and benefits of storing financial numbers as integers instead of doubles in SQLite.

My broker’s API returns financial data as doubles (numbers like 9.999999997) so I’ve been storing them as 8-byte REALs in SQLite. With periodic vacuuming, the databases have grown in line with expectations. However, I’ve always wanted to explore storing the data as integers. There are three amazing benefits to storing financial numbers as integers:

  1. There are no rounding errors when storing integers.
  2. SQLite can store integers using between 1 byte and 8 bytes (ref).
  3. Analysis using integers is inherently faster than with floats and doubles.

After transforming the table schemas and data of about 212 GB, here are the results:

Before (212 GB)

StartEndOpenHighLowCloseVolumeStatus
INT 4BINT 4BREAL 8BREAL 8BREAL 8BREAL 8BINT 4BINT 1B

After (142 GB)

StartEndOpenHighLowCloseVolumeStatus
INT 4BINT 4BINT 1~8BINT 1~8BINT 1~8BINT 1~8BINT 4BINT 1B

Here are some specific reduction examples.

Before and after database sizes
Before and after database sizes

I took two databases and migrated them to all integers then vacuumed them. With active symbol ABX I achieved a 29% reduction, and with retired symbol CBW I achieved again a 29% reduction. The benefits are immediate and real.

Data representation with integers

Here is a before and after animation of ABX showing how the financial numbers are represented now. I multiplied all the REALs by 100,000, rounded them naturally, and cast them to INTEGERs. This maintains a five-decimal precision and supports huge financial numbers into the trillions and beyond.

Data representation before and after
Data representation before and after
Success: Multiplying all the doubles by 100,000, rounding them naturally, and casting them to integers in SQLite maintains a five-decimal precision and reduced my database footprint by about 33% (~70 GB so far).