Why not use jsonb instead of json for properties column at activity log table (Postgresql db)?

[igorespin]

Are there any drawbacks in changing the migration of the activity log table from json (default data type) to jsonb?
I know that inserts can be a little slower with jsonb. Could this change affect performance in a large application?

[Gummibeer]

Hey,

two things:

1. the migration is published so you can customize it to your needs.
2. yes, in general jsonb should be used if you are doing any JSON things in database with this column - filtering by key inside, indexing content or similar - because like you've said writing jsonb is slower and uses more storage space around/up to 30%. As this package doesn't use any json queries it would be an useless adjustment of the default migration - also because binary JSON is only supported by PostgreSQL.

If you are doing something with this column on DB side you should check if it's worth changing the column type and of so you can do it.
Depending on the amount of logged attributes per model and the amount of written activities in general the impact could be immense. Imagine a 30% higher storage usage of a 1GB/day activity logging table. With activity tracking for last month this would be a total of ~9GB of additional data. If that's only to speed up one single admin query used once a week I wouldn't recommend it.
So as always there isn't a golden recommendation and *it depends". 😉

[igorespin]

Great answer! Thank you....