4 ms·

I deal with messy data often and there are definitely times when a JSON row should contain a field but doesn't. If I also needed to increment the field as part

I deal with messy data often and there are definitely times when a JSON row should contain a field but doesn't. If I also needed to increment the field as part of cleaning the data, it would also be nice to upsert in the cases where the field is missing. Extracting a field into its own column may make sense sometimes, but usually I just need the data to be correct and stored rather than be queried or updated frequently so adding another column is a waste of time and space.

brightball 8y ago
It seems like you're talking about a couple of different cases here. If you're going to be incrementing a field infrequently, then just resaving the JSONB shouldn't be a big deal. If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON. When you create a column with postgres that has a null default, it doesn't allocate space for it. It's one of the things that makes postgres so good for huge databases because adding an empty column is an instantaneous operation, rather than a locking operation. On the flip side, when you create a field in JSON you're duplicating the space used just to name the field in every single row. But the short version is, if you're updating something often enough that you're willing to fight around upserting into a JSON column rather that just creating that same column it might be worth it to take a step back and re-evaluate why doing it that way is important to you.
- rpedela 8y ago
  > If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON. Yes. When did I say the opposite?
  - brightball 8y ago
    I think I assumed based on your need for increment upsert on a JSON field. My mistake.

brightball 8y ago

It seems like you're talking about a couple of different cases here. If you're going to be incrementing a field infrequently, then just resaving the JSONB shouldn't be a big deal. If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON. When you create a column with postgres that has a null default, it doesn't allocate space for it. It's one of the things that makes postgres so good for huge databases because adding an empty column is an instantaneous operation, rather than a locking operation. On the flip side, when you create a field in JSON you're duplicating the space used just to name the field in every single row. But the short version is, if you're updating something often enough that you're willing to fight around upserting into a JSON column rather that just creating that same column it might be worth it to take a step back and re-evaluate why doing it that way is important to you.

rpedela 8y ago
> If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON. Yes. When did I say the opposite?
- brightball 8y ago
  I think I assumed based on your need for increment upsert on a JSON field. My mistake.