Storing JSON Payloads in PostgreSQL

Arminius ingests and stores JSON payloads, some of which are cryptographically signed. For these signed payloads, it is critical that the exact byte-for-byte representation is preserved.

Decisions

Signed JSON Payloads Will Be Stored Using PostgreSQL’s json Data Type

This type stores an exact copy of the input text, preserving all whitespace and key order, which is essential for validating cryptographic signatures. The downside is that such fields need to be validated and parsed whenever they are queried or selected. Also, they cannot be indexed, leading to full table scans in most situations.

Unsigned JSON Payloads Will Be Stored Using PostgreSQL’s jsonb Data Type

This type stores json data in an optimized binary format, which is more efficient with regards to parsing and storage. Whitespaces and key order are not preserved for this type, which should not be a problem for non-signed payloads.

All JSON Fields That Are Frequently Queried or Joined Will Also Be Stored as Normalized, Single-Value Columns

This enables the creation of standard indexes on these columns, ensuring efficient query and join performance, which is not always optimal when querying inside JSON or JSONB blobs.

Alternatives Considered

Storing All JSON as jsonb

Rejected due to the need for exact textual preservation for signed payloads, which jsonb does not guarantee.

Storing Only as Text

Rejected because json and jsonb types enforce JSON validity.