Metis

Joins in #sql #database may cause may issues. See our new infographics explaining what can go wrong.

𝗟𝗼𝗮𝗱 𝘁𝗲𝘀𝘁𝘀 𝗮𝗿𝗲 𝗷𝘂𝘀𝘁 𝗮 𝘄𝗮𝘀𝘁𝗲 𝗼𝗳 𝘁𝗶𝗺𝗲. Let’s read on to understand why. Load tests should answer if the service and the database are fast enough to be deployed to production. They do this job well but have so many drawbacks that ultimately they should be avoided. 𝗙𝗶𝗿𝘀𝘁, 𝗹𝗼𝗮𝗱 𝘁𝗲𝘀𝘁𝘀 𝘁𝗮𝗸𝗲 𝘁𝗼𝗼 𝗺𝘂𝗰𝗵 𝘁𝗶𝗺𝗲. We must run thousands of transactions for many hours to get meaningful results. We need to fill caches, drives, and networks, and keep it running for a long time. 𝗦𝗲𝗰𝗼𝗻𝗱, 𝗹𝗼𝗮𝗱 𝘁𝗲𝘀𝘁𝘀 𝗮𝗿𝗲 𝗵𝗮𝗿𝗱 𝘁𝗼 𝘄𝗿𝗶𝘁𝗲. We can’t simply shoot the service with random requests. We need to mimic the production data distribution and send the data that just makes sense. 𝗧𝗵𝗶𝗿𝗱, 𝗹𝗼𝗮𝗱 𝘁𝗲𝘀𝘁𝘀 𝗮𝗿𝗲 𝗱𝗶𝗳𝗳𝗶𝗰𝘂𝗹𝘁 𝘁𝗼 𝗺𝗮𝗶𝗻𝘁𝗮𝗶𝗻. Data changes over time, services change their implementation, and we need to keep our testing data up to date. 𝗙𝗼𝘂𝗿𝘁𝗵, 𝘄𝗲 𝗻𝗲𝗲𝗱 𝘁𝗼 𝗱𝗲𝗮𝗹 𝘄𝗶𝘁𝗵 𝗚𝗗𝗣𝗥, 𝗖𝗖𝗣𝗔, 𝗮𝗻𝗱 𝘀𝗶𝗺𝗶𝗹𝗮𝗿. We can’t simply take the production data and play it in the non-production environment. 𝗙𝗶𝗳𝘁𝗵, 𝗿𝗮𝗻𝗱𝗼𝗺𝗻𝗲𝘀𝘀. It is perfectly fine to get a different response for the same request due to randomness, variations in the timestamps, random identifiers, and other stuff. 𝗦𝗶𝘅𝘁𝗵, 𝘁𝗵𝗲𝘆 𝗵𝗮𝗽𝗽𝗲𝗻 𝗳𝗮𝗿 𝘁𝗼𝗼 𝗹𝗮𝘁𝗲. Even if we find issues with load tests, it’s already after we wrote the code, reviewed it, merged it, and deployed it to some environments. 𝗜𝗻𝘀𝘁𝗲𝗮𝗱 𝗼𝗳 𝗿𝘂𝗻𝗻𝗶𝗻𝗴 𝗹𝗼𝗮𝗱 𝘁𝗲𝘀𝘁𝘀, 𝘄𝗲 𝘀𝗵𝗼𝘂𝗹𝗱 𝗰𝗮𝘁𝗰𝗵 𝘁𝗵𝗲 𝗶𝘀𝘀𝘂𝗲𝘀 𝗲𝗮𝗿𝗹𝘆. We can employ many observability techniques to see things behind the scenes and identify the issues in the developers’ environments. We can use telemetry to capture the queries sent to the database. Once we have these queries, we can project them onto the production database and extract the execution plans. This way we can immediately tell if the queries are going to be fast enough when deployed to production. In the same way, we can verify schemas and configurations. We can check if the indexes are configured properly, and if the queries use indexes well. We can utilize observability to do much more. Since we know what is being changed and in what way, we can easily pinpoint issues in production and correlate them to the actual code changes. This way, we can submit automated pull requests that fix the issues. We can tune configurations, schemas, indexes, extensions, and much more.

Let's see 4 hot trends in database observability that modern companies are chasing now. We spent years improving our monitoring solutions. We can now capture metrics nearly automatically thanks to solutions like OpenTelemetry. We can visualize data points easily with open-source solutions. Finally, we can easily measure our applications' performance. And yet, there are still more things to incorporate to make observability even better. 𝗧𝗵𝗲 𝗳𝗶𝗿𝘀𝘁 𝘁𝗵𝗶𝗻𝗴 𝗶𝘀 𝗶𝗻𝘁𝗲𝗴𝗿𝗮𝘁𝗶𝗼𝗻 𝘄𝗶𝘁𝗵 𝗱𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿𝘀’ 𝗲𝗻𝘃𝗶𝗿𝗼𝗻𝗺𝗲𝗻𝘁𝘀 𝘁𝗼 𝗰𝗮𝗽𝘁𝘂𝗿𝗲 𝗵𝗼𝘄 𝘁𝗵𝗲 𝘀𝗼𝗳𝘁𝘄𝗮𝗿𝗲 𝗲𝘃𝗼𝗹𝘃𝗲𝘀 𝗼𝘃𝗲𝗿 𝘁𝗶𝗺𝗲. This gives us the following: - We can analyze the changes much earlier in the pipeline which shortens the feedback loop and lets developers iterate faster - We can see how individual changes affect the production database because we see every little detail - We can easily troubleshoot issues since we know exactly what’s happening across our pipelines 𝗡𝗲𝘅𝘁, 𝗺𝗼𝗱𝗲𝗿𝗻 𝗼𝗯𝘀𝗲𝗿𝘃𝗮𝗯𝗶𝗹𝗶𝘁𝘆 𝘀𝗼𝗹𝘂𝘁𝗶𝗼𝗻𝘀 𝘀𝗵𝗼𝘂𝗹𝗱 𝘄𝗮𝗿𝗻 𝘂𝘀 𝗮𝗯𝗼𝘂𝘁 𝗶𝘀𝘀𝘂𝗲𝘀 𝗯𝗲𝗳𝗼𝗿𝗲 𝘁𝗵𝗲𝘆 𝗮𝗰𝘁𝘂𝗮𝗹𝗹𝘆 𝗮𝗽𝗽𝗲𝗮𝗿. This brings many benefits: - Developers feel confident that their changes are safe to be deployed and any issues are identified early - Managers can plan their roadmaps reliably as their teams avoid expensive rollbacks and lengthy troubleshooting - All DORA metrics are improved as teams move faster and face fewer errors - Velocity increases and MTTR goes down 𝗧𝗵𝗶𝗿𝗱, 𝗴𝗼𝗼𝗱 𝗼𝗯𝘀𝗲𝗿𝘃𝗮𝗯𝗶𝗹𝗶𝘁𝘆 𝘀𝘆𝘀𝘁𝗲𝗺𝘀 𝗮𝘃𝗼𝗶𝗱 𝘀𝘄𝗮𝗺𝗽𝗶𝗻𝗴 𝘂𝘀𝗲𝗿𝘀 𝘄𝗶𝘁𝗵 𝗱𝗮𝘁𝗮 𝗽𝗼𝗶𝗻𝘁𝘀. This unblocks the teams in the following ways: - They need lower working knowledge about the system to keep it in shape and maintain it over time - Junior developers can be as effective as senior developers in troubleshooting issues - Teams can work independently as they don’t need help from other teams (like DBAs) to debug the problems - They don’t need to spend time on troubleshooting and can apply the solutions immediately There is no need to slice and dice metrics anymore. World-class observability solutions are now giving guides that developers can follow mechanically to solve the issues. 𝗟𝗮𝘀𝘁 𝗯𝘂𝘁 𝗻𝗼𝘁 𝗹𝗲𝗮𝘀𝘁, 𝗼𝗯𝘀𝗲𝗿𝘃𝗮𝗯𝗶𝗹𝗶𝘁𝘆 𝘀𝗼𝗹𝘂𝘁𝗶𝗼𝗻𝘀 𝗰𝗮𝗻 𝗻𝗼𝘄 𝘃𝗲𝗿𝗶𝗳𝘆 𝗺𝗮𝗻𝘆 𝗵𝘆𝗽𝗼𝘁𝗵𝗲𝘀𝗲𝘀 𝗮𝗻𝗱 𝗽𝗿𝗼𝘃𝗶𝗱𝗲 𝗮𝗻𝘀𝘄𝗲𝗿𝘀. This gives the following: - Observability tools can confirm what will work and what will not bring any improvements - Developers don’t need to try things out. They just come and immediately see which things are effective - MTTR is greatly reduced as many solutions are tested at once - Developers are sure improvement suggestions will work in their production systems and are not only theoretically valid Say goodbye to old monitoring solutions that swamp your developers with raw data points.

Caches in SQL are important for improving the performance and efficiency of database operations. By storing frequently accessed data in memory, caches reduce the need to retrieve information from disk, which is a slower process. See our new infographics explaining how it works. #sql #database #observability

𝗗𝗲𝘃𝗢𝗽𝘀 𝗲𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝘀 𝘄𝗮𝗻𝘁 𝘁𝗼 𝗮𝘂𝘁𝗼𝗺𝗮𝘁𝗲 𝗲𝘃𝗲𝗿𝘆𝘁𝗵𝗶𝗻𝗴. This gives them many benefits - they don’t need to do any manual steps, they don’t need to speak to other teams and can prioritize their important tasks better. 𝗛𝗼𝘄𝗲𝘃𝗲𝗿, 𝗺𝗮𝗻𝘆 𝗼𝗿𝗴𝗮𝗻𝗶𝘇𝗮𝘁𝗶𝗼𝗻𝘀 𝗺𝗮𝗸𝗲 𝗮𝘂𝘁𝗼𝗺𝗮𝘁𝗶𝗻𝗴 𝘁𝗵𝗶𝗻𝗴𝘀 𝗵𝗮𝗿𝗱𝗲𝗿. They require synchronous communication between the teams, they limit access permissions, and they scatter ownership throughout various squads. In turn, DevOps engineers don’t own their databases and struggle to move fast with automation since they need to maintain the databases manually. 𝗪𝗵𝗮𝘁 𝗶𝗳 𝘁𝗵𝗲𝘆 𝗰𝗼𝘂𝗹𝗱 𝗼𝘄𝗻 𝘁𝗵𝗲𝗶𝗿 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲? 𝗪𝗵𝗮𝘁 𝗶𝗳 𝘁𝗵𝗲𝘆 𝗱𝗶𝗱𝗻’𝘁 𝗻𝗲𝗲𝗱 𝘁𝗼 𝘀𝗽𝗲𝗮𝗸 𝘁𝗼 𝗼𝘁𝗵𝗲𝗿 𝘁𝗲𝗮𝗺𝘀? We need to bring tools that automate everything around databases. To make DevOps engineers true database owners, they need to be able to optimize all the queries that are executed in the database. They need tools that can show them what’s going on, analyze the source code, and indicate how to optimize it to make it faster. These tools need to bring confidence and reliability. There is nothing worse than finding bugs very late in the pipeline because it requires the DevOps engineers to start the implementation process from scratch. They need solutions that can analyze if their designs are correct, if the queries are fast enough, and if the schema changes won’t cause a data loss. The next part of the work is achieving the highest database reliability and understanding whether the database works correctly. DevOps engineers need to have live database-oriented metrics showing the status of the databases. However, just bringing the data is not enough. We can’t expect the DevOps engineers to do the hard work of connecting the dots when these things can be easily automated. We need tools that can help by reasoning about all parts of the SLDLC to explain how to make things better. Tools that can let the DevOps engineers focus on the hard parts. 𝗧𝗵𝗲 𝗯𝗶𝗴𝗴𝗲𝘀𝘁 𝘁𝗶𝗺𝗲-𝘄𝗮𝘀𝘁𝗲𝗿𝘀 𝗮𝗿𝗲 𝗺𝗲𝗲𝘁𝗶𝗻𝗴𝘀 𝗮𝗻𝗱 𝘀𝘆𝗻𝗰𝗵𝗿𝗼𝗻𝗼𝘂𝘀 𝗰𝗼𝗺𝗺𝘂𝗻𝗶𝗰𝗮𝘁𝗶𝗼𝗻. DevOps engineers automate their tasks and they can’t do that when many parties are involved. They need to work independently and own all the parts of their databases. They need to have tools that can analyze database designs, query performance, and suggest improvements. These tools need to automate the reasoning and provide actionable insights instead of raw data. Metis handles all of that and lets the DevOps engineers own their databases and talk less with other teams. Use Metis and unleash the power of your DevOps teams.

Developers often want to own the stuff they work with. This gives them many benefits - they don’t need to speak to other teams, they can prioritize their work, and they can optimize the things they work on by introducing better tools and processes. It’s hard for them to do their work when they aren’t the owners. Unfortunately, this is common with databases. 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿𝘀 𝗱𝗼𝗻’𝘁 𝗼𝘄𝗻 𝘁𝗵𝗲𝗶𝗿 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗲𝗻𝗴𝗶𝗻𝗲𝘀 𝗮𝗻𝗱 𝘀𝘁𝗿𝘂𝗴𝗴𝗹𝗲 𝘄𝗶𝘁𝗵 𝗺𝗼𝘃𝗶𝗻𝗴 𝗳𝗮𝘀𝘁 𝘄𝗶𝘁𝗵 𝗮𝗹𝗹 𝘁𝗵𝗲 𝘄𝗼𝗿𝗸 𝗼𝗻 𝘁𝗵𝗲𝗶𝗿 𝗽𝗹𝗮𝘁𝗲𝘀. What if they could own their database? What if they didn’t need to speak to other teams 𝗧𝗼 𝗺𝗮𝗸𝗲 𝗱𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿𝘀 𝗼𝘄𝗻𝗲𝗿𝘀, 𝘁𝗵𝗲𝘆 𝗻𝗲𝗲𝗱 𝘁𝗼 𝘂𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱 𝗵𝗼𝘄 𝘁𝗼 𝗼𝗽𝘁𝗶𝗺𝗶𝘇𝗲 𝘁𝗵𝗲 𝗾𝘂𝗲𝗿𝗶𝗲𝘀 𝘁𝗵𝗲𝘆 𝗱𝗲𝗮𝗹 𝘄𝗶𝘁𝗵. They need to have the tools that can let them change the code with confidence. They need solutions that can analyze if their designs are correct, if the queries are fast enough, and if the schema changes won’t cause a data loss. This requires integration with developers’ environments, CI/CD pipelines, and all the environments developers work on. 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿𝘀 𝗻𝗲𝗲𝗱 𝘁𝗼 𝘂𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱 𝗶𝗳 𝘁𝗵𝗲𝗶𝗿 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲𝘀 𝗮𝗿𝗲 𝘄𝗼𝗿𝗸𝗶𝗻𝗴 𝗰𝗼𝗿𝗿𝗲𝗰𝘁𝗹𝘆. They need to have live database-oriented metrics showing the status of the databases. Just bringing the data is not enough. Tools need to reason automatically and provide answers instead of raw data. Those tools need to do all of that by connecting dots from all parts of the SLDLC to explain how to make things better, hypothesize about indexes, query rewrites, and configuration changes, and verify these ideas automatically. 𝗧𝗵𝗲 𝗯𝗶𝗴𝗴𝗲𝘀𝘁 𝘁𝗶𝗺𝗲-𝘄𝗮𝘀𝘁𝗲𝗿𝘀 𝗮𝗿𝗲 𝗺𝗲𝗲𝘁𝗶𝗻𝗴𝘀 𝗮𝗻𝗱 𝘀𝘆𝗻𝗰𝗵𝗿𝗼𝗻𝗼𝘂𝘀 𝗰𝗼𝗺𝗺𝘂𝗻𝗶𝗰𝗮𝘁𝗶𝗼𝗻. Developers need to be able to work independently on all the parts of their databases. Developers can deal with the issues entirely on their own if they don’t need to talk to other teams or ask for details or metrics. They can fix everything independently. Developers can own their databases. They can solve all the issues and deploy things fast. They need to have tools that can analyze database designs, query performance, and suggest improvements. These tools need to automate the reasoning and provide actionable insights instead of raw data. Metis handles all of that and lets the developers own their databases and talk less with other teams. Use Metis and unleash the power of your developers.

Learn about #window functions in #sql with our new infographics!

Reliability is often misunderstood and teams miss multiple things when making sure their databases work well. They focus on improving stability metrics without building the proper processes around them. Let’s see 3 hot trends in database reliability that every team should incorporate into their organization. 𝗙𝗶𝗿𝘀𝘁 - 𝗢𝗯𝘀𝗲𝗿𝘃𝗮𝗯𝗶𝗹𝗶𝘁𝘆 𝗜𝗻𝘀𝘁𝗲𝗮𝗱 𝗼𝗳 𝗠𝗼𝗻𝗶𝘁𝗼𝗿𝗶𝗻𝗴 We need to change the way how we monitor our ecosystems. To make sure our database is reliable, we need to prevent any bad changes from reaching production. We need to find the issues before they get through pipelines and fix them as early as possible. Monitoring is not enough to do that. We can’t just capture metrics from various software layers and sources in the production, and call it a day. 𝗪𝗲 𝗻𝗲𝗲𝗱 𝘁𝗼 𝗺𝗼𝘃𝗲 𝗳𝗿𝗼𝗺 𝗺𝗲𝗿𝗲𝗹𝘆 𝗺𝗼𝗻𝗶𝘁𝗼𝗿𝗶𝗻𝗴 𝘁𝗵𝗲 𝗽𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 𝘁𝗼 𝘀𝗲𝗲𝗶𝗻𝗴 𝗲𝘃𝗲𝗿𝘆𝘁𝗵𝗶𝗻𝗴 𝗶𝗻 𝗼𝘂𝗿 𝗲𝗰𝗼𝘀𝘆𝘀𝘁𝗲𝗺. This way we can identify slow queries before they hit the database, invalid schema migrations that may take the database down, and wrong scripts that may lead to data loss. 𝗦𝗲𝗰𝗼𝗻𝗱 - 𝗨𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱𝗶𝗻𝗴 𝗜𝗻𝘀𝘁𝗲𝗮𝗱 𝗼𝗳 𝗦𝗲𝗲𝗶𝗻𝗴 Our current monitoring solutions are great at showing us where something happens. However, just seeing that the metrics increased is not enough when we’re deploying many times a day and we deal with many heterogeneous applications. 𝗪𝗵𝗮𝘁 𝘄𝗲 𝗻𝗲𝗲𝗱 𝗶𝗻𝘀𝘁𝗲𝗮𝗱 𝗶𝘀 𝘂𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱𝗶𝗻𝗴. We need our monitoring solutions to explain what happens, why metrics change, what exactly contributes to the issues we observe, and how things are evolving. Instead of getting the alarms that metrics spiked, we should get a consistent history explaining what happened and where we are now. 𝗧𝗵𝗶𝗿𝗱 - 𝗔𝘂𝘁𝗼𝗺𝗮𝘁𝗲𝗱 𝗧𝗿𝗼𝘂𝗯𝗹𝗲𝘀𝗵𝗼𝗼𝘁𝗶𝗻𝗴 𝗜𝗻𝘀𝘁𝗲𝗮𝗱 𝗼𝗳 𝗠𝗮𝗻𝘂𝗮𝗹 𝗪𝗼𝗿𝗸 Once we get comprehensive explanations of what changed and why the system doesn’t behave as expected, we should expect the tooling to troubleshoot the issues automatically. The system can add hypothetical indexes and check the execution plans, verify the cost of different queries, and suggest improvements. We can submit pull requests to fix the code and configurations automatically. Monitoring the metrics is not enough. We need to move on to prevent the bad code changes from being deployed to production. We can get all of that automated and forget that our databases exist.

There are two important tasks that each developer faces when using databases - how to make queries faster and how to improve the schemas. There are many ways to tackle these problems and we need to find the balance between how much time we spend and how much we improve the performance. 𝗠𝗮𝗻𝘆 𝘁𝗵𝗶𝗻𝗴𝘀 𝗮𝗳𝗳𝗲𝗰𝘁 𝘁𝗵𝗲 𝗽𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲 𝗼𝗳 𝘁𝗵𝗲 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲. We can use CTEs that may be slower than the less readable code with duplications. We may go with many joins that can exponentially increase the number of rows we extract from the database. We may use cursors that let us achieve nearly anything but are terrible for the performance. 𝗧𝗵𝗲 𝗻𝗲𝘅𝘁 𝘁𝗵𝗶𝗻𝗴 𝗶𝘀 𝘄𝗵𝗮𝘁 𝗶𝗻𝗱𝗲𝘅𝗲𝘀 𝘄𝗲 𝗵𝗮𝘃𝗲 𝗶𝗻 𝗼𝘂𝗿 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲. We may go with popular B-tree-based indexes, but we may also have text-oriented indexes with trigrams or things supporting ML and vector databases. 𝗧𝗵𝗲𝗻 𝗰𝗼𝗺𝗲 𝗮𝗹𝗹 𝘁𝗵𝗲 𝘁𝗵𝗶𝗻𝗴𝘀 𝘁𝗵𝗮𝘁 𝗺𝗮𝗸𝗲 𝘁𝗵𝗲 𝗼𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻 𝗲𝗻𝗴𝗶𝗻𝗲 𝗽𝗿𝗼𝗱𝘂𝗰𝗲 𝗳𝗮𝘀𝘁𝗲𝗿 𝗽𝗹𝗮𝗻𝘀 No matter what we choose to do, we need to have tools that can help us along the way. Just like we have compilers and static code analysis protecting us from silly mistakes, we need to have tools that can analyze our queries, schemas, and configurations, and provide automated insights. 𝗧𝗵𝗲𝗿𝗲 𝗶𝘀 𝗷𝘂𝘀𝘁 𝗼𝗻𝗲 𝘁𝗼𝗼𝗹 𝗼𝘂𝘁 𝘁𝗵𝗲𝗿𝗲 𝘁𝗵𝗮𝘁 𝗱𝗼𝗲𝘀 𝗮𝗹𝗹 𝗼𝗳 𝘁𝗵𝗮𝘁 - 𝗠𝗲𝘁𝗶𝘀. Metis covers all you need to make your queries faster and schemas better. You get actionable code improvements, schema analysis, index coverage, and AI-based suggestions on how to optimize your queries. Metis is the only tool that walks you through the optimization process and leads you along the way. Use it today and make your databases faster.