Blog

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

<p>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.</p><h3>Sorting Out your Indexes</h3><p><em>What is an index, really?</em> 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;"></p><p>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.</p><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><p>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.</p><p>In a database there are two kinds of indexes: <strong>clustered</strong> and <strong>non-clustered</strong></p><p><strong>Clustered indexes</strong> 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.<br></p><p><strong>Un-clustered indexes</strong> 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.</p><h3>Behind the Curtain</h3><p>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:</p><p><strong>Impact on Writes -</strong> 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.</p><p><strong>Increased Maintenance -</strong> 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.</p><p><strong>Challenge of Multi-column Indexes -</strong> 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.</p><h3>Adding Value to Everyday Operations<br></h3><p>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.<br></p><p><strong>Primary Keys -</strong> 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.</p><p><strong>High Volume Queries -</strong> 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.</p><p><strong>Foreign Keys -</strong> 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.</p><p><strong>Read Heavy Databases -</strong> 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.</p><p>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. </p><p><em>By Ellie Najewicz</em><br data-mce-bogus="1"></p>

Indexing - Mastering the 'Look Up'

<p>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.</p><h3>What is metadata and why should I care?</h3><p>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.</p><p>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.</p><h3>Enhanced Searchability</h3><p>One of the clear benefits from metadata is improved searchability. Your
organization will know <em>what data exists where</em>. 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.</p><p>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.</p><h3>Increased Collaboration</h3><p>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.</p><p>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:</p><p>- How is one record in the database stored?</p><p>- How to know which user made what changes in the database?</p><p>- How are records soft or hard deleted from the database?</p><p>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.</p><h3>Governance Foundation</h3><p>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. </p><p>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;</p><p>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;</p><p><em>By Ellie Najewicz</em></p>

The Role of Metadata in Data Organization

<p>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.</p><h3>Know your Data</h3><p>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.</p><h3>Guarding the Gates</h3><p>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.</p><h3>Shielding Your Secrets</h3><p>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.</p><h3>Be prepared for Disaster<br></h3><p>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.</p><h3>Vigilantly Monitor <br></h3><p> 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. </p><p>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.</p><p><em>By Ellie Najewicz</em></p>

Data Security Must Haves

<p>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.</p><h3>Increased Flexibility</h3><p>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.</p><h3>Closer collaboration across database and software development</h3><p>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.</p><h3>Beyond tuple-structured data<br></h3><p>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.</p><h3>Will NoSQL replace SQL?<br></h3><p>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.</p><p>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.</p><p><em>By Ellie Najewicz</em></p><p><br></p>

Change how you think with NoSQL

<p>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 <strong>Large Objects&nbsp; - </strong>commonly referred to as LOBs.<br></p><h3>What is it?</h3><p>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;</p><h3>Why use them? <br></h3><p><strong>Data Flexibility</strong> - 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.</p><p><strong>Storge for Large Volumes - r</strong>egular data types in relational databases cannot store such large volumes of data. Therefore, LOBs are the best format for large data size.</p><p><strong>Data Aggregation</strong> - by keeping unstructured data along with the relational data makes it very convenient to work with both sets of data together. <br></p><h3>What's the Catch?</h3><p><br>Are there any downsides to storing LOB data? LOBs of course have drawbacks. <br></p><p><strong>Performance -&nbsp; </strong>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.</p><p><strong>Volumes</strong> - 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.</p><p><strong>Complex to manage</strong> - 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: </p><p>&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. </p><p>&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.</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - Cannot enforce check constraints, primary key, unique, or foreign keys on LOBs.</p><p><strong>Better Alternatives for Unstructured Databases - </strong>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. </p><p>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.</p><h3>What's the Verdict?</h3><p>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. </p><p><em>By Ellie Najewicz</em></p>

BLOBs, CLOBs, & LOBs - Oh My!

orange metal railing white concrete stairs

<p>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;</p><h3>The Tale of Two Choices: Complexity vs. Simplicity</h3><p>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;</p><p>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;</p><p>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.</p><h3>Building Better</h3><p>How do we avoid complex solutions? In my experience I try to stay true to the following:&nbsp;</p><p>- 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;</p><p>- 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.</p><p>- 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;</p><h3>The Elegance of Understandability</h3><p>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.</p><p>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;</p><p><em>By Ellie Najewicz</em></p>