Use JSON columns with care

05 May, 2025

In postgresql you can easily create a json column to dump arbitrary json objects into. This is super convenient when you don't feel like creating a new column, running migrations, using brain juice to consider the implications of a data model etc.

Unfortunately you have now stumbled into the cross roads of developer happiness and despair.

If you treat this new column as a simple store to dump unstructured data, which will either be read in its entirety or inspected manually, then you get to travel to the road of happiness.

Some examples of this usage include:

• storing API request/responses in full
• serialized versions of an object
• various logs and contextual information about an operation

Now, if you decide this column will interact with business logic then you need to ensure a few things to avoid your future self cursing your past self.

Namely, you need to make sure you the column is backed by some sort of application layer schema. This can be as simple as the following dataclass:

@dataclass
class WidgetMetadata:
    foo: bool
    bar: str
    baz: list[int]
    bux: SomeEnumType

Great, now we have a structure which represents the json data with which we can use for serialization.

This approach has a few drawbacks though. How do we version these objects? What if we don't need foo anymore? How do we safely migrate the data? What happens when someone decides to not use the WidgetMetadata class and inserts data which doesn't match its shape?

These are all problems which can be solved with care, and if done correctly you can likely continue to walk down the path of developer happiness. However it is quite easy to start treading down the path of despair.

It starts with thinking we will only have a few options we need to save in this column, so we don't need to create a backing WidgetMetadata schema. We will definitely only update it in one place in the app so we really don't need the overhead of the object, just dump the whole thing!

One place then turns into two, a new property is added here, one deleted there, then all of a sudden you have created a bad version of mongodb (who knew that was possible). It now becomes difficult to understand the shapes and constraints of this column. Weird things can happen like its data shape silently drifting due to programmer error, i.e. the field was { foo: "bar" } but then accidentally turned into an array { foo: ["bar"] }.

Don't get me wrong, I think json columns are great! There just needs to be a good amount of care and consideration when adding them to your tables. Really think about how the data will be used because many of the fields might simply be more appropriate as dedicated columns.

Anyways, let me know if you have some rad use case for json columns that I didn't consider where you have walked the happy path. Happy jsoning.

P.S. Also be sure to use jsonb to save some bytes!