Blog

Storing null values is an important feature of any database. Knowing if data is stored is an essential operation to quickly sort and retrieve data. Providing your application function to pass nothing or no information is key to many workflows where partial or incomplete transactions should be persisted. Let's take a look into how to best leverage null values to optimize or database. <h3>The Silent Players </h3>Why should we care about storing nothing?&nbsp; Knowing that nothing has been stored for a particular column or field is just as important as understanding the value if something was stored. Thinking about how your database will handle null values should be defined when you first build your data model as well as when you are designing your queries to interact with the data. Additionally, nulls are key to understanding if data can be used for analytics or AI development.<h3>All Upside </h3>There are so many good things from using null values properly. This way databases can efficiently hold data that is empty or partial information to accurately reflect a business process. Nulls can provide the following:&nbsp;- Query performance improvements: Null values provide database professionals with the flexibility to index columns efficiently. This allows for faster search operations, as nulls are indexed separately, contributing to improved query performance.- Better data quality: By utilizing null values, database professionals can avoid the introduction of misleading information. Nulls serve as clear indicators of missing data, preventing misinterpretation, and ensuring that decisions based on the database are made with a comprehensive understanding of the available information.- Enforce referential integrity: Null values provide a controlled way to delete or update records without violating referential integrity constraints. Instead of forcing cascading updates or deletions, nulls permit a level of flexibility, allowing for the maintenance of relationships without compromising the integrity of the database. <h3>Not all Nulls are Equal</h3>The biggest issue that can arise with null usage is not setting them correctly. Sometimes team can insert 'NA', '&nbsp;&nbsp; ', 'BLANK', or other strings to indicate an empty value. This will have serious consequences on data quality and database performance. As one of these examples is still a string - so it takes up storage and must be read explicitly by queries when searched. The largest mistake people make with numeric numbers is treating a value of zero as NULL - because these values are not the same. Null numeric values of zero can skew computations and lead to misinterpretation in reporting. For example, a contract valued as NULL is a contract where the price has yet to be determined or is in negotiation. Whereas a contract with a value of zero means a contract is being completed free of charge or not for a monetary value. If both of these scenarios, were stored with a value of zero - you could see how that would lead to misinterpretation. In conclusion, leveraging nulls strategically empowers technical teams to build robust and efficient database systems that not only store and retrieve data effectively but also maintain the integrity of relationships between tables. By embracing null values as valuable contributors to the database ecosystem, we can harness them to create more resilient and adaptable systems.By Ellie Najewicz

Null Values - A Whole Lot about Nothing

gray and black telescope on brown concrete surface near body of water during daytime

Indexes are an indespensible tool in acheiving optimization in database retreval. Creating and maintaining indexes is an essential part of any database management role. Indexes are universal across all sorts of different databases - structured or unstructured. Let's explore what an index is, how it works, and best practices for managing them.<h3>Sorting Out your Indexes</h3>What is an index, really? Most know that indexes improve performance, but few understand how indexes impact databases. An index is sorting logic applied to a specific column on a table to optimize the look up. In most cases an index aligns to the ‘WHERE’ part of a query. Essentially, an index is a sorted version of a column in question, so we can search that column in a faster way to achieve faster performance. Take the following example - lets say we were looking up rows where ‘CITY = Zebulon’ - in the list we have the query would search through the column in ascending order - so a total of 7 lookups. This is not optimized since it has to check every row, and if your record happens to be the last entry, the search will have to look through each row before successfully returning.<img id="xth6zm636ls0ffjup" src="https://cdn.durable.co/blocks/16acPK9rStmMMcxdDO8MNtsS1PlWVFifHZqezJk1DTno1P4xuBeEpC4GEziVwzl4.png" alt="" class="object-cover bg-gray-100" style="aspect-ratio: 3/2; object-position: undefined undefined;" data-corners="default" data-aspect="3:2" data-caption="" data-positionx="undefined" data-positiony="undefined" data-fullwidth="false" data-mce-src="https://cdn.durable.co/blocks/16acPK9rStmMMcxdDO8MNtsS1PlWVFifHZqezJk1DTno1P4xuBeEpC4GEziVwzl4.png" data-mce-style="aspect-ratio: 3/2; object-position: undefined undefined;">Now lets say we knew that that CITY list was in alphabetical order. And since we know z is at the end of the alphabet we could do a much more efficient sort. Indexes default to a Binary Tree - or B-tree - sorting method (more about alternative sorting methods later in this pattern). With this method, we can search the list much more efficiently, finding Zebulon after only 4 lookups. You can see how such a pattern would drastically improve searching across millions or billions of rows.<figure id="figure-59vczpio1ls0fh2he"><img id="59vczpio1ls0fh2he" src="https://cdn.durable.co/blocks/261jAo5aWgRGZ8ev06EYokhMKKKdyJialAewLYSOhh5P07Xc9CxLKeOApGa1J68C.png" alt="Artist tools" class="object-cover bg-gray-100" style="aspect-ratio: 3/2; object-position: center center;" data-corners="default" data-aspect="3:2" data-caption="" data-positionx="undefined" data-positiony="undefined" data-fullwidth="false" data-mce-src="https://cdn.durable.co/blocks/261jAo5aWgRGZ8ev06EYokhMKKKdyJialAewLYSOhh5P07Xc9CxLKeOApGa1J68C.png" data-mce-style="aspect-ratio: 3/2; object-position: center center;"></figure>All an index really contains is a mapping of a columns actual order in a database to a sorted list. When there is a filter on that column, it can search the sorted list stated in the index more efficiently than searching the table itself. Indexes do all this as well without duplicating data or creating another physical table.In a database there are two kinds of indexes: clustered and non-clusteredClustered indexes are just those that are created by default when a table with a primary key is created. To create this index there is nothing you need to do beyond creating the table with a declared primary key. Un-clustered indexes are created as needed by database administrators or engineers to optimize a heavily used query for best read performance. These indexes are added to the table configuration of the column being optimized. Un-clustered index can refer to one or multiple columns with any sort method. Within non-clustered indexes there is a subcategory on multi-column indexes.<h3>Behind the Curtain</h3>Why don’t we make indexes on every column that is searched? Indexes or too many of them of course have drawbacks. Consider the following before building one:Impact on Writes - There is no free lunch - and the consequence of indexes is that it can slow down your write operations. When you add a new row to the database, any fields that belong to an index will need to be added and sorted according to that index. Every time a write is made to the database, the indexes are unusable until they have updated. Therefore, if the database is constantly receiving writes, then the indexes will never be usable and cause congestion in your database.Increased Maintenance - Indexes are another database object - like a view - that has to be maintained in the schema. When you migrate databases, you must make sure to copy over all indexes and continuously apply updates as needed. Indexes should be monitored that they are first still being used and secondly that they are providing a performance improvement.Challenge of Multi-column Indexes - While multi-column indexes are very powerful, they are difficult to build, maintain, and sometimes can impact the write performance. Single column indexes are more flexible, where as multi-column indexes will result in poor performance when they do not work.<h3>Adding Value to Everyday Operations </h3>The follow are the best situations to add indexes to your database operations where drawback will be minimized and the most value will be gained. Primary Keys - With clustered indexes, we should always have a unique index for our primary key. This will cover read optimizations for most basic look ups or counts on the table. This is one of the major reasons to declare a primary key, as the index is simply created by default along with the table with no additional work.High Volume Queries - When you have a large percent of reads from a table covered by the same query, then you have an opportunity to leverage an index. For example, if your table’s reads are accounted for by 30% from one particular query (maybe the query that loads the landing dashboard in an application) this would lend to a good place to add an index. As your database becomes larger and larger, the more likely you are to see benefits from indexing.Foreign Keys - Along similar lines for query performance, if you are doing consistent joins on a table, building an index on the defined foreign keys could improve your performance. If you have foreign keys in your tables that is frequently filtered or joined on with other tables, consider adding an index.Read Heavy Databases - Overall, indexing should be a large operation for any database that is read heavy or where read performance is a priority. If your database is in a situation where over 50% of operations are reads, then indexing should be constantly re-evaluated and part of regular data operations. By the same logic, if your database is very write heavy, then indexing should be less of a priority and may be more costly to maintain than it’s worth.In conclusion, in the ecosystem of database objects, indexes play an important role to keeping the database running efficiently. By understanding the fundamentals, advantages, drawbacks, and best practices, you can harness the full potential of indexing to elelvate the performance of your database. By Ellie Najewicz

Indexing - Mastering the 'Look Up'

Metadata is quite underrated. However, it provides the blueprint for data
documentation, governance, and automated controls. Let's discuss what metadata
is, the value it has to an organization, and how to bring metadata into your
organization's standards.<h3>What is metadata and why should I care?</h3>Metadata is simply data about data - at a high level it indicates what the
data is supposed to represent, who created/updated/deleted a given piece of
data, and when this change occurred. Metadata exists around the data structure
which mostly defines what the data represents and any format rules it follows
(i.e. string of length 10 or double with two decimal points). Metadata is also
captured within each transaction which usually contains an audit trail for any
interactions in the database.Metadata is the linchpin to keeping your database in a good state and ensure
high understandability across your data. Metadata provides the standards to
compare against to ensure data quality and are a key metric to monitor to
control data changes are as expected. You can imagine how without quality
metadata enforcing data governance and active data access monitoring would be
very challenging.<h3>Enhanced Searchability</h3>One of the clear benefits from metadata is improved searchability. Your
organization will know what data exists where. Sounds like a basic
thing you should know, right? As organizations grow it's a common issue to see
redundant data repositories appear and the trusted source of information can
become blurred. Not understanding exactly what data you have is a large organizational
challenge as well as a security and privacy risk. This can all be fixed with
accurate and up-to-date metadata documentation.If you find yourself in this predicament, start with creating policies for
documenting metadata in a centralized catalog to identify redundancies and
gather an accurate inventory.<h3>Increased Collaboration</h3>With metadata, data and your database becomes less of a mystery and can
easily be understood by the entire development team. When there is
understanding of the data format and structure across the entire team,
development accelerates, and teams realize greater collaboration between
DBAs/data engineers and developers. Additionally, when trying to resolve bugs
that involve the database, more team members will be qualified to resolve if
the entire team has a basic understanding of the database behavior.To achieve this kind of improvement, metadata documentation should be
up-to-date and accessible to the entire team. As an example, all members of a
development team should know the following:- How is one record in the database stored?- How to know which user made what changes in the database?- How are records soft or hard deleted from the database?While in reality, teams practice separation of duties and no developer would
ever have access to production data, it is important team members have a basic
understanding of the data for better quality software.<h3>Governance Foundation</h3>Metadata will also be the building block of any governance strategy. Data
governance is the practice of classifying and regulating data based off of
those classifications. The most common area of governance is around privacy or
personal information (PI). It is essential to know where PI is in your data
and that it is tracked, protected, and accessed in an established manner.
Understanding what classification your data falls under all depends on accurate
and available metadata. Metadata on individual datasets will be what is scanned or controlled for
data governance. Not only is a database more maintainable with good metadata
documentation, but it’s also a necessity for compliance and automated controls.
If teams want to automate any governance activity, a sound metadata
documentation strategy must be established first for success.&nbsp;Overall, metadata is something that is only going to become more important
as data is an essential building block for our systems and AI. Data with
quality metadata tagging will be highly valued in the future as we require more
transparency and accuracy.&nbsp;By Ellie Najewicz

The Role of Metadata in Data Organization

Tis the season to make wish lists - after completing my Certified in Data Protection (CDP) learning, I wanted to summarize my main takeaways. We know security is important and data protection is at the heart of cybersecurity. &nbsp;As we navigate the complexities of the digital age, certain must-haves emerge as cornerstones for building a resilient fortress against cyber threats. Let's delve into the essential components that constitute the blueprint for data security excellence.<h3>Know your Data</h3>Before fortifying your defenses, it's crucial to know what you're protecting. First you should establish a comprehensive inventory of your data assets. Then categorize and classify information based on sensitivity and importance. By understanding the landscape of your data, you can lay the foundation for targeted security measures.<h3>Guarding the Gates</h3>Access control is the gatekeeper of your digital information. Implement stringent access policies that grant permissions based on roles and responsibilities. Make sure to lean on the principle of least privilege and separation to duties when building out access roles.&nbsp;Regularly review and update access privileges, ensuring that only authorized personnel have entry to sensitive data. This not only mitigates the risk of insider threats but also safeguards against unauthorized external access.<h3>Shielding Your Secrets</h3>In the age of cyber espionage, properly implemented encryption is the foundation for shielding your data. Employ end-to-end encryption for data both in transit and at rest. This transforms your information into an unreadable code, even if intercepted. Encryption is not merely a safeguard; it's an insurance policy against data breaches.<h3>Be prepared for Disaster </h3>Data security isn't just about preventing unauthorized access; it's also about ensuring the availability of your information. Implement high-availability solutions to guarantee uninterrupted access to critical data, even in the face of unforeseen challenges such as hardware failures or cyber-attacks. Redundancy and failover mechanisms become your allies in maintaining seamless operations.<h3>Vigilantly Monitor </h3> A proactive defense is an effective defense. Regularly audit and monitor your systems for any anomalies or unauthorized activities. Implement intrusion detection systems and log analysis tools to promptly identify and respond to potential security threats. Vigilance is key in staying one step ahead of evolving cyber risks.In conclusion, data security must-haves are the foundation of a resilient defense strategy. By creating a thorough inventory, implementing robust access controls, embracing encryption, and ensuring high availability, you fortify your data against an ever-evolving array of cyber threats. In the dynamic landscape of data security, these essentials serve as the guardians of your most precious asset—information.By Ellie Najewicz

Data Security Must Haves

The transition from a relational database to a NoSQL database marks not just a technological shift, but a change in mindset. Let's explore how embracing NoSQL transforms the way we think about data and the processes they support.<h3>Increased Flexibility</h3>Relational databases thrive on structured schemas, imposing constraints around data storage. This comes from when databases were the main control layer for data quality - now we have UIs and APIs to ensure data quality before it hits the database. NoSQL databases, however, break free from these constraints. With dynamic and table-less structures, NoSQL accommodates diverse and evolving data models, allowing the flexibility to add in controls only when needed. NoSQL will force our software to align to logical and simple structures that align to events.<h3>Closer collaboration across database and software development</h3>NoSQL's flexibility extends beyond data structures to development processes. Now that agile methodologies are standard, NoSQL databases align seamlessly with the principles of rapid iteration and flexibility. Developers can adapt to changing requirements without the need for extensive schema migrations, fostering a more agile and responsive development environment. Additionally, NoSQL structure easily aligns to how software is written. With NoSQL, information can be organized together whereas in a relational database, data may be distributed across many tables for one event. As a bonus, NoSQL databases are very developer friendly.<h3>Beyond tuple-structured data </h3>For many years, when we thought of data, we would think of an excel spread sheet. This led to data being held in tables or relational databases. Now, we have a much more diverse set of data - rich text, images, audio, video, etc. And NoSQL databases will be essential to store and make this type of information accessible. By using NoSQL, teams will be able to better leverage unstructured data and see how structured data could be combined with unstructured data.<h3>Will NoSQL replace SQL? </h3>Probably not. We will continue have a mix of relational and NoSQL data. Especially with the rise of AI, we will need to store more unstructured text data than we have in the past. NoSQL introduces specialized database models like document stores and graph databases, each tailored to specific types of data relationships. Document stores, such as MongoDB, DynamoDB, and Apache Cassandra, excel in handling semi-structured data, while graph databases, like Neo4j, excel in capturing complex relationships.In conclusion, the transition from a relational to a NoSQL database is not merely a change in technology but a shift in mindset. Adopting NoSQL opens the door to flexibility, scalability, and agility take center stage. As we navigate this shift in thinking, the database becomes not just a storage solution but a dynamic and adaptive partner in the ever-evolving landscape of modern data management.By Ellie Najewicz

Change how you think with NoSQL

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