Blog

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

The Key to Order in your Database

To View or Not to View

Rightsizing your Database