The large object (LOB) type is used to store TEXT, BLOB, JSON, and GEOMETRY data.
LOB data can be stored by using INROW or OUTROW storage.
INROW storage
When INROW storage is used, LOB data is stored together with rows in the primary table. Only one access to storage is required to read LOB data.
OUTROW storage
When OUTROW storage is used, LOB data is stored in an LOB auxiliary table. To read LOB data, the database first obtains locators of the data by reading rows in the primary table and then reads actual LOB data from the LOB auxiliary table based on the locators. The whole process involves two accesses to the storage.
LOB storage switching
Whether INROW or OUTROW storage is used depends on the amount of data in a LOB column. Assume that you have set the storage switching threshold to 8,192 bytes. If data in a LOB column exceeds 8,192 bytes, OUTROW storage is adopted; otherwise, INROW storage is used.
obclient> CREATE TABLE t(pk int, data text) LOB_INROW_THRESHOLD = 8192;
The preceding DDL statement sets the INROW-to-OUTROW switching threshold to 8,192 bytes by specifying the LOB_INROW_THRESHOLD parameter.
- When data in a LOB column is within 8,192 bytes, the data is stored together with rows in the primary table.
- When data in a LOB column exceeds 8,192 bytes, the data is stored in an LOB auxiliary table.
INROW storage outperforms OUTROW storage since the former reduces the number of accesses to storage required to read LOB data, thus improving the efficiency. In scenarios where LOB data needs to be accessed frequently, INROW storage can speed up queries and reduce system overhead.