Blog

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&nbsp; - commonly referred to as LOBs. <h3>What is it?</h3>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.&nbsp;<h3>Why use them? </h3>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. <h3>What's the Catch?</h3> Are there any downsides to storing LOB data? LOBs of course have drawbacks. Performance -&nbsp; 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: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - GROUP BY or ORDER BY clause cannot reference a LOB&nbsp; &nbsp;&nbsp;&nbsp; column. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - Cannot specify SELECT DISTINCT on a LOB. LOBs cannot be used in any predicate except EXISTS, LIKE and NULL.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - 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.<h3>What's the Verdict?</h3>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

BLOBs, CLOBs, & LOBs - Oh My!

orange metal railing white concrete stairs

As we embark on the ever-evolving journey of software and IT systems design, there's a compelling call — a call for simplicity. Let's explore the the positive impact of choosing simple solutions over complex features, shaping a digital ecosystem that lends flexibility over time and enduring clarity.&nbsp;<h3>The Tale of Two Choices: Complexity vs. Simplicity</h3>In the world of software design, the allure of complexity can be enchanting. Intricate architectures, multifaceted algorithms, and elaborate solutions may seem like the epitome of innovation. Not to mention more complex architectures are fun to build. Yet, within this complexity lies an underlying challenge that simplicity sidesteps.&nbsp;Many times, in my career, and I am sure in yours as well, I have experienced this intersection when building a new solution or feature. Consider the inception of a new software project. The team quickly skips to discussing what could be built, but never first considered if it should. As a result, you end up with an intricate design, promising sophistication, recognition, and cutting-edge technologies.&nbsp;The consequence of such complexity leads to larger overhead, less-room for failure, and limited flexibility to evolve and update in the future. Sometimes we get away with these decisions and they are successful, even though they could have been achieved through a simpler solution. However, over time such complexity continues to snowball and sets the foundation for future enhancements. Moving from a complex solution to a simpler one is usually very challenging. Navigating from complexity to simplicity is akin to retracing steps on an unfamiliar path—a formidable challenge that often results in so much effort that it never gets prioritized or achieved.<h3>Building Better</h3>How do we avoid complex solutions? In my experience I try to stay true to the following:&nbsp;- Always start simple: &nbsp;A simple start - although not always the most glamorous - is the best way to set your project up for success. Once the solution is captured in its simplest form, the door opens to clear enhancement and optimization. It's a conscious decision to allow complexity to emerge organically as the project matures.&nbsp;- Treat simple designs as strategic above any trendy technology: Simplicity is not a compromise; it is a strategic choice that lays the foundation for scalability, maintainability, and comprehensibility. Imagine a software architect faced with a choice: a convoluted, intricate solution versus a straightforward, elegant one. The call for simplicity is the latter — a starting point that fosters clarity and ease of understanding.- Explore alternatives when making decisions: When building or reviewing solutions it is essential to see alternatives explored. And if the more complex option was selected, it should be accompanied with clear reasoning why it is needed.&nbsp;<h3>The Elegance of Understandability</h3>Simplicity begets understandability—an often-overlooked feature in the world of software design. A system that is simple to comprehend is a system that invites collaboration, fosters quick onboarding for new team members, and reduces the cognitive load on those maintaining the codebase. In a world where change is constant and adaptability is paramount, simplicity eases the burdens of evolution and welcomes the challenges of innovation.So, in the future I hope you consider this hypothesis — where simplicity is not a concession but a strategic choice. As architects of the digital future, let's heed the call for simplicity, embracing the elegance of straightforward solutions that stand the test of time.&nbsp;By Ellie Najewicz

A Call for Simplicity

All databases, structured or unstructured, require a standardized way to define a unique record. At the core of any data organization is defining an identifier that is tied to a combination of fields that stamps a record as unique. Databases refer to this identifier as a Primary Key. Primary keys are essential to ensuring a database is modernized, high performing, and easily maintainable.<h3>What is it? </h3>A primary key is a unique identifier that’s value is distinct for one record in a database – think of a row in a relational table-based database. What makes a primary key efficient is that uniqueness is documented in one value – not a combination of values on a row. A combination of keys used to identify a one record is called a compound key. Take an example of a list of addresses – what would make an address unique? You could not just rely on the street address as it may not be unique across all cities. Therefore, you would have to look across all address fields – street address, City, State, and Zip Code – to identify a unique address – resulting in a compound key.&nbsp;<h3>What’s wrong with compound keys?</h3>When you have a long compound key it makes your data operations much more costly from a resource and speed perspective – any select statement must look across 4 columns for any basic look up or count. Additionally, linking this address information with other datasets becomes increasingly taxing as you would have to join on all 4+ columns. That is why in these cases, it is best to create a primary surrogate key that represents a unique address. A good key should be alphanumeric and have a long enough length to ensure uniqueness for your use case. While this is obviously essential for relational databases, it is also important for semi-structured and unstructured data as well. No matter the format, knowing the one key that uniquely identifies an object is essential for designing processes around that object.<h3>Value of a primary key</h3>Ensuring all data objects have primary keys is not new guidance in the data industry, however it is an essential part of modernization. Making sure data objects are properly identified is important to support distributed data systems – such as a data fabric – and ensure easy portability for your data. By following this pattern, you will see the following benefits:- Better performance - Reduced Data Gravity - Less maintenance - Increased ease of modernizationOverall, by ensuring you always have a primary key for each object, your database can achieve immediate performance enhancements and ease of maintenance. As a bonus, your database will be more portable making it easier to adopt modernized platforms and distribute data faster.By Ellie Najewicz

The Key to Order in your Database

person holding white and black checkered card

Physical data models are not the most intuitive and retain the information in the exact format for easy user consumption. Over time relational databases have developed a concept called views.<h3>Another Way to Cut the Pie</h3>Views are essentially created by writing a query to combine physical tables together in a virtual table. Then this virtual table can be easily queried by an application or ETL process. All views are to support read-only operations, any updates cannot be done on a view and will have to update the source tables themselves.Views are commonly used to abstract complex data structures as well as hide personal information. This way tables can be presented in a more logical format that developers will easily understand. An additional, common use case is for presenting aggregated or summarized data. Since it is a common principle to avoid storing calculated values, if you often need to display or access summations, averages, percent changes, etc. this can be tedious to build into the read query from the application. Therefore, we see a common use case for views to present these aggregate views to isolate the query complexity.There are many benefits to using views - however everything has a cost and in the case of views the cost is performance. It is important to understand the considerations when implementing views to ensure they are applied correctly to your database.<h3>Why do we need Views?</h3>Query Simplicity - By having views display filtered data by default - the queries to read from the views should be very simple. The query complexity is removed from the API or service integration and instead stored in the database. This is beneficial for development velocity while the view structure is not prone to changes. If the view structure is expected to change, then ensure you have a plan to manage your dependency on your DBA or whoever is managing your database views.Access Control - Access control can partially be managed through views. For example, you can hide PI, SPI, or other sensitive information from the view, so services can freely read the views and not the origin tables.&nbsp;Note: Although this is a common use case for views, access control should be managed through a stronger Access Control method. Relying on views is a weak access control policy since the access control configuration is not easily documented, monitored, or managed in a view.Hides Table Complexity - Physical tables can be complex and include auto-generated keys, ids, timestamps, and other data that might not make sense to someone reading the data. You can abstract a lot of this complexity in views by filtering out data that is not needed.&nbsp;Database Technology Agnostic - Views can be applied on any database. Across SQL and even NoSQL databases we can apply views.<h3>What Could Go Wrong</h3>With views helping so much, why don’t we make views for all reads? Views of course have drawbacks. Consider the following before building one:&nbsp;Performance - The biggest challenge views have is around performance. Since views are generated when they are queried - imagine if a view is read 100’s of times in an hour, it is the same performance impact of direct reads. If the source table for the view is part of many other views or being read directly, this could lead to conflicts and table locks. View creation may be worth the impact if the view structure is a complex query with large amounts of data transformation. However, for basic queries a view may not be worth the performance impact and you would be better off directly querying tables. A key method to counteracting performance issues is creating indexes on the source tables to tune them for the view.&nbsp;It is also important to consider the impact views have on the infrastructure. Because views are virtual tables and never persisted to disk, they will consume lots of RAM. Be aware as you implement views that the storage cost that is removed from disk is moved to your RAM utilization.&nbsp;Update Restrictions - While views are used for read operations, no writes or updates can be made to the views. Any writes must go directly to the source tables. If you have a set of tables that are taking on a heavy amount of reads and writes, then it may be too complex to use views for reads and direct queries for writes.&nbsp;Disjointed documentation and change control - Views need to be managed with care as one of the large challenges is that the SQL query used for the view is not visible to the development team or managed with the rest of the code base. Views are essentially stored procedures in a database and are hidden code. While views can reduce complexity of a direct read, that complexity does not go away, but instead resides in the database and maintained by a DBA/Data Engineer. Your team will need to think about maintenance and change control as these database changes are not managed through a traditional dev-ops process.&nbsp;If a view structure and query is expected to constantly change it may be better to leave that complexity in direct queries where the interactions can be managed through traditional change control with the reset of the codebase.<h3>Final Thoughts?</h3>All in all, views are a common tool in relational databases that can help teams boost their performance and organize their data. Although, views are frequently used as a band aid for poorly designed tables and can further complicate understanding the data. Use views with care and clear documentation on how they work and why they are needed.By Ellie Najewicz

To View or Not to View

We are generating more data than we ever have before, and the industry expects this trend to continue. To keep ahead of uncontrolled data growth, we need to put data right-sizing practices in place to curb volume increases.<h3>Cleaning your Data</h3>The first place to start when decreasing your data volumes is to look for data that can be permanently removed.Reference Data - Any data that your database is not the source of truth for, but you have replicated from another source for reference. Any older version of this data does not need to be maintained. If needed, you can pull from the trusted source. For example, if you reference currency rates for calculations, you only need to retain the current rates while older rates can be deleted.Tables with Large Objects - Tables with CLOBs and BLOBs are good candidates for deletion because they usually hold large volumes that will save you more storage space. Additionally, these large objects can hold data that should not be maintained in relational databases such as - compressed files, logs, messages, json files, etc.&nbsp;Tables with the most volume - Start where you have the most opportunity to reduce your volumes. You can find your largest schemas or tables in your database through investigative queries. <h3>Data Archival</h3>Archiving your database is an essential practice and important for modernized applications. It’s also essential to archive to then easily delete historic data that no longer has value. An archive is a subset of your database that is historic or less valuable and therefore&nbsp; can be stored in a compressed, more cost-efficient environment. This differs from backups in that it is not a complete replica of the database - but instead a portion of the database. Traditionally an archive is hosted on an object or file storage offering where storage costs are much cheaper.&nbsp;If you have not started archiving, first begin by defining when objects should be archived. There should be a point in time where data stops providing value and could be removed. An archive policy defines when each object should be archived and ultimately deleted for a given database. An archive leverages a cost-effective storage solution - like S3 - to hold archived data. The most efficient systems automate the archive data movement so it is not a manual task.<h3>See the Benefits</h3>Decreased infrastructure costs - If a team has never archived their database - they can save 20% or more off of their infrastructure and operations costs by building an archive for 20% of their oldest data. This archival pattern applies to all databases - including SQL and NoSQL. Not only is this good for our budgets but also for our sustainability effort as this will reduce your application’s carbon footprint.&nbsp;Performance Improvement - By removing fewer valuable data, the high value data is more accessible. Queries will run faster if searching through less information. This will also improve the effectiveness of other performance enhancements such as indexes and views. By lowering the amount of data retained, your database will also become more portable making it easier to migrate or replicate the data.&nbsp;Reduces impact of data compromises - By storing data in an alternate location you are improving the data's resiliency. Therefore, fortifying the integrity and confidentiality of the data. For example, if your database was compromised by ransomware, without an archive you would lose a longer history of data as opposed to a much smaller portion if you were archiving.&nbsp;Ease of automatic data deletion - By moving data to an archival database like Cloud Object Storage, automatic data deletion can be easily implemented. Archival set up usually inherits good data retention policies as well.In conclusion, right-sizing your database is not a one-time task, but an ongoing commitment to efficiency, sustainability, and performance. By regularly cleaning out irrelevant data and archiving historic information, databases will only hold data that is highly valuable and can be better managed.By Ellie Najewicz

DataSphereChronicle

Blog

BLOBs, CLOBs, & LOBs - Oh My!

A Call for Simplicity

The Key to Order in your Database

To View or Not to View

Rightsizing your Database