JSONB support for semi-structured data

When you are defining a schema using JSON, ensure that common fields, such as , user_id, and device_id, are pulled outside of the JSONB structure and stored as columns. This is because field accesses are more efficient on table columns than inside of JSONB structures. Storage is also more efficient.

You should also use the JSONB data type, that is, JSON stored in a binary format, rather than JSON data type. JSONB data types are more efficient in both storage overhead and lookup performance.

note

When you index JSONB data across all fields, it is usually best to use a GIN (generalized inverted) index. In most cases, you can use the default GIN operator, like this:

For more information about GIN indexes, see the PostgreSQL documentation.

Index individual fields

JSONB columns sometimes have common fields containing values that are useful to index individually. Indexes like this can be useful for ordering operations on field values, multicolumn indexes, and indexes on specialized types, such as a postGIS geography type. Another advantage of indexes on individual field values is that they are often smaller than GIN indexes on the entire JSONB field. To create an index like this, it is usually best to use a on an expression accessing the field. For example:

In this example, the expression being indexed is the cpu field inside the data JSONB object, cast to a double. The cast reduces the size of the index by storing the much smaller double, instead of a string. The WHERE clause ensures that the only rows included in the index are those that contain a cpu field, because the returns true. This also serves to reduce the size of the index by not including rows without a cpu field. Note that in order for a query to use the index, it must have data ? 'cpu' in the WHERE clause.