BLOBs, CLOBs, & LOBs - Oh My!

If you work on an relational database that has been in production for many years, you have probably noticed data types like BLOBs, CLOBs, DBLOBs, NCLOBs, etc. These objects are Large Objects  - commonly referred to as LOBs.

What is it?

Large Objects are a way to store unstructured data in a relational database. This kind of data type was introduced in the 1990’s to make up for the lack of unstructured data repositories. Now that there are several efficient data repositories for unstructured data, and the adoption of data fabric architectures, LOB design and use is an important architectural decision for data teams. They store a variety of data such as audio, video, documents, and so on. 

Why use them?

Data Flexibility - LOBs allow users to easily store unstructured data inside a relational database. This way LOB data can be queried easily along with other traditional data types. LOBs are traditionally stored in a separate tablespace than the base table. Which provides clear separation between LOB and non-LOB data.

Storge for Large Volumes - regular data types in relational databases cannot store such large volumes of data. Therefore, LOBs are the best format for large data size.

Data Aggregation - by keeping unstructured data along with the relational data makes it very convenient to work with both sets of data together.

What's the Catch?

Are there any downsides to storing LOB data? LOBs of course have drawbacks.

Performance -  LOBs are traditionally stored in a separate table space than the rest of the base table data. This means if you are running query operations that look up both LOB and non-LOB data, additional I/O is needed to complete the operation.

Volumes - LOBs are a major contributor to ever-growing data footprint. Make sure that as you set up structures to store such large volumes that you have a clear strategy to archive and delete these data volumes. As the size of data increases, so do the management concerns, such as lengthy elapsed times to run utilities on the data, slower performance of accessing the data, etc.

Complex to manage - as the large volumes increase, the complexity to manage them also increases. There are limitations as well surrounding how LOBs can be indexed. For example:

            - GROUP BY or ORDER BY clause cannot reference a LOB      column.

            - Cannot specify SELECT DISTINCT on a LOB. LOBs cannot be used in any predicate except EXISTS, LIKE and NULL.

            - Cannot enforce check constraints, primary key, unique, or foreign keys on LOBs.

Better Alternatives for Unstructured Databases - While LOBs provide a good alternative to storing unstructured data easily in a relational database, if storing lots of unstructured data you should consider a database that is built to hold this kind of data.

With adoption of data fabric architectures and tools to help query across multiple databases, storing unstructured data outside of relational databases is much more feasible than in the past. If you have a lot of unstructured data in your relational database, it is recommended to revisit your design and consider leveraging an unstructured database.

What's the Verdict?

LOBs will continue to be a staple in relational databases and are a key method of storing large unstructured data when needed. However, if you manage a legacy database with a heavy dependency on LOBs there are many NoSQL or document databases on the market today that may offer better performance and ease of maintenance. This may be an exciting area of data modernization for your organization.

By Ellie Najewicz