json on child table or not
I have a table with lots of updates in a json field and few updates on other
fields. On that table I have several indices and it is main table of other
lots of child tables.
When querying this table I always do a lateral join with that json field, so
having that field on a separate table wouldn´t be a problem.
Then, for auto vacuum, for indices, for fillfactor and some other reasons,
is it better to have that json on a child table in a 1 to 1 relation ?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
How big does the data stored in that field get? More than 2KB? Real
question- is it getting stored plain, compressed inline, or toasted? Have
you set the storage strategy/type, or is it the "extended" default behavior
that compresses and then stores in the toast table if still more than 2000
bytes?
Always joining on a field that is toasted and compressed is going to be
considerably slower than a discrete field. Do you have an index (or
several) on the json field values? Also, are we actually talking about
jsonb?
Yes, they are jsonb.
Their length varies between 400 and 2.000 chars, when using
pg_column_size(jsonb field). When casting to text it goes to 1.800 to 3.500
of length.
I didn´t set any storage strategie for that table or field. It´s defined as
x for storage.
No, I don´t have any index on that field.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html