Thread: 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