Compression

JSON documents can be big! When JSON is stored as text (in an nvarchar(max) column), Nevermore comes up with different strategies for reading it, based on whether the table always has smaller documents (<1K) or not.

However, Nevermore also supports compressing the JSON. You can use this if you use SQL Server 2016 or higher.

Why use compression?

Much of the data in JSON documents is repetitive. Property names, types and ID's are repeated over and over.

In Octofront, for example, the LicenseKeys table keeps a history of the changes made to each license key. Each history entry has a type, date, and some properties. Those strings are repeated potentially hundreds of times. Compressing this data reduces the size of the table at the time of writing from 435 MB to 118 MB.

Good examples to try in Octopus would be Variables, DeploymentProcess. Bigger documents with collections as properties.

The benefits of compression are:

1. Less space used in the database
2. Less space used in data transfer

The downside, of course, is CPU time spent compressing and decompressing when reading data.

In testing with a local SQLEXPRESS and documents of different sizes with a 50% compression ratio, these benefits canceled each other out:

Method	Compressed	DocumentSize	Mean	Error	StdDev	Gen 0	Gen 1	Gen 2	Allocated
'Load big object'	False	65536	1.560 ms	0.2761 ms	0.0151 ms	64.4531	29.2969	-	357.26 KB
'Load big object'	True	65536	1.703 ms	0.3508 ms	0.0192 ms	66.4063	31.2500	-	356.59 KB

Enabling compression

To use compression:

1. Change the JSON column type to varbinary(max). (See tips on migrations below).
2. In your DocumentMap, set Compressed to true.

That's it!

When inserting or updating data, Nevermore will use GZIP to write the compressed data to the table. When reading, Nevermore will use GZIP to read it back.

If you are querying in SQL, and want to use JSON_VALUE for example, you still can. Just call the DECOMPRESS function in SQL 2016 or newer, and cast it to a string.

select cast(DECOMPRESS([JSON]) as nvarchar(max)) from dbo.BigObjectCompressed