This is our final blog post highlighting Michael Stonebraker's presentation at PostgresBuild 2021, called "Where the DBMS Market is Going." If you have not watched it, you can do so here.
The founder of what we, today, know as Postgres offers guidelines for the future of the cloud. Not only from a historical perspective, but very much forward-looking—soberingly clear recommendations to help cut through all the haze of cloud hype, but also the many misconceptions surrounding today's application development and operation practices!
Everything creates delay
What is the annoying part about an application? The fact that users use it…
While this may seem like a nonsense statement, because you build your applications so that user can use them, application usage—and especially if it's successful in generating uptake—is the cause of most problems. This is simply because everything that you do in that application causes the entire stack to have to do work and that takes time, resulting in delay.
In our previous post about Dr. Stonebraker’s insights, we focused on Maximizing Database Application Performance, looking at what impacts application performance. There, we focused on the delay that was created by interacting with the database. In this post, we’ll consider some of the aspects that actually cost time inside the database.
Where time is spent (and why)
There are general experiences of where database energy is spent when processing applications.
.png)
Understanding these various states is a very good way of getting to grips with what is actually happening when you run your application, and where you might start to run into bottlenecks.
If you want to dive deeper into this, these categories are broken down into wait events in Postgres.
Really understanding these wait events, what causes them and which of these have a true effect on your application's performance puts you in the league of folks who are into the dark arts of application performance tuning. Many of these events are interconnected, and many have more than one cause or resolution.
What can you do about it?
Open doors are so trivial, right? However, like clichés, they have become just that because of the truth that lies within.
When a database is spending its time working for an application, critical things are happening behind the scenes. When you have a real need for speed, this is something that you have to take into account when you design and build your application, but also when you design your data model—both logically (how individual data elements link together or how they are related to each other) as well as physically (where data is stored relative to other pieces of data in the system).
Making a serious application that is geared to supporting thousands of users, doing tens of thousands of transactions per second demonstrates the tremendous impact small things have on such massive data movement and data processing actions.
Dr. Stonebraker’s following suggestions—which he terms “surgery”—might seem radical, but they’re explicitly designed to satisfy your need for speed, and help you find the best new angle to get ahead of the pack.
Main memory DBMS
The closer you can get your precious data to where the action is, the faster things will go.
If all the data that your application works with does not reside on disk, but gets loaded into memory at startup, you eliminate a lot of traffic. Even if the traffic inside a server is fast, if there is a lot of action going on, such as hundreds of thousands of transactions per second, then you really do need a lot of really fast data movement.
Remove Latching
This is very important! If you do not have to fight over or wait on a specific resource, that is a clear thing.
Getting more connections to your database server is logical when your application gets used more. Effectively, this will just make the system increasingly crowded, causing fights and waits to increase.
Additionally, make sure that users access the data you are processing in a smart way. Carefully looking at the dataset your application works with and how you can create a logical partitioning in that can help reduce latching.
Here’s a simple example: if your users all work for different departments, it might make sense to have mechanisms that allow data operations to only access data for that specific department, by means of an identifying column, for instance.
Concurrency Control
Now, we’re stepping into trickier territory. Dr. Stonebraker advises: do not mess with this unless you are absolutely, positively certain that you know what you are doing!
As we have seen in the table above, Concurrency Control, in this case Postgres' Multiversion Concurrency Control (MVCC), is a vital mechanism for relational database management. As with most things that are worth doing, it has a cost, and as we have learned so far is that cost translates into delay.
There are multiple levels of transaction isolation possible with Postgres. Selecting lower levels will put you at more risk; however, this might help you gain some of that much desired speed.
Run in no-log mode
This is just outright dangerous!
However, if you were to separate part of your system from the rest of the system—a part that would be volatile enough to simply rebuild, such that it wouldn’t matter if it were lost in a crash—you could run it in no-log mode and experience blazing speed.
If you are able to bypass the system that writes transaction logs which are needed for recovery, you could gain a lot of speed. You would not be able to crash-recover that system or even create a replica database from it, but it would be blindingly fast.
EDB wait states
Everything starts with understanding. To this end, we have created EDB wait states as part of the Performance Analysis and Tuning function.
By investigating the actual wait state data from your application, running under a real world workload, you gain that critical insight that will allow you to figure out which measures will benefit you the most.
Just remember, it might not be the most obvious thing that brings you the most benefit!
Smart, smarter, smartest
The need for speed is bound to the basic rule of our world.
.png)
Those last few micro or even nano seconds of speed you want to take off your query executing time, require a lot more effort than the first half of a minute. We all know this as the 80/20 rule.
Another nugget of wisdom is: the more you think about your need for speed up front, the stronger your system's foundation will be, the less hardship you will have in trying to find that last stretch to meet your requirements.
Crossing the finish line
In a way, this is much like Formula 1 racing. If you want to go really fast, everything needs to add up, and every single piece of the engine needs to function at 100% of capacity, 100% of the time.
Across our Dr. Stonebraker series we have looked at various aspects of this, ranging from cloud adoption to in which tier to run which bits of your application.
In application creation there is a lot of emphasis on the visible parts, which makes sense. Nevertheless, the elements that have been somewhat lost over the years are the mechanics that largely contribute to the final success of the application—its ability to respond blindingly fast!
Building applications is an art, and your data management platform is a vital part of the process.
Use Postgres!