Skip to main content

Tips and Tricks for Postgres Observability at Scale

Summary

  • This video covers practical observability techniques for Postgres at scale on Lakebase—the Databricks Data and AI platform's managed Postgres service—including automatic DDL change logging, Unity Catalog integration, and database branching for instant point-in-time recovery.
  • The most common Postgres outage patterns identified across post-mortems involve untracked schema changes (DDL) that silently break application logic; correlating these DDL events with application incident timelines is the fastest path to root cause.
  • A live demo shows Genie diagnosing a Postgres performance problem and resolving a missing index through background execution, illustrating how AI-powered auto-resolution reduces the need for a dedicated Postgres DBA.

Tips and Tricks for Postgres Observability at Scale

Watch: Tips and Tricks for Postgres Observability at Scale
Running Postgres in production gets harder as your application grows — logs pile up, metrics live in one tool, schema changes live in another, and when something breaks, you are stuck stitching it all together under pressure. this video shares practical tips and tricks for getting ahead of common Postgres operational challenges — we will cover how to spot configuration issues before they hit production, how to unblock application updates when schema changes go sideways, and how to quickly connect application failures back to the database changes that caused them. Along the way, we will show how bringing Postgres operational data together with modern AI tooling can take a lot of the manual work out of diagnostics — so you spend less time hunting for root causes and more time shipping. Whether you are running a handful of Postgres instances or managing them at scale, you will leave with concrete techniques you can apply to your own environment.
Talk By: Anna Stepanyan, Staff Product Manager (Lakebase), Databricks ;

Chapters

FAQs

What are the most common causes of Postgres outages at scale?

Based on the post-mortems discussed in this video, the most common Postgres outage patterns involve DDL schema changes—such as adding a column or altering a data type—that break application queries in ways that are difficult to diagnose after the fact. Correlating the exact DDL change with the application incident timeline is typically the fastest path to root cause.

How does Lakebase help with Postgres observability compared to self-managed Postgres?

Lakebase automatically logs DDL changes and integrates with Unity Catalog, eliminating the manual setup of collection scripts that self-managed Postgres requires. It also provides live signals like active sessions, slow query logs, and execution plans out of the box, making observability accessible to teams on the Databricks Data and AI platform without a dedicated Postgres DBA.

What is database branching and why is it useful for Postgres operations?

Database branching in Lakebase creates an instant point-in-time copy of a Postgres database without requiring a full data dump or restore. This allows teams to quickly roll back after a problematic schema change or test a migration safely on a branch before applying it to production.

How does Genie assist with Postgres performance diagnostics?

In the demo shown in this video, Genie identifies a missing index as the root cause of a performance problem and executes the fix in the background. This AI-powered auto-resolution capability reduces reliance on expert DBA knowledge for common performance issues, making Postgres operations more accessible to application developers on Lakebase.

Full transcript

[00:07] Hi folks, my name is Anna. I'm a product manager for from the lakebased team and lakebased is our Postgress is data bricks. I'm curious who is here a posgress DBA. Is there are any posgress DBAs here in the room by any chance? No.
[00:19] Raise of hands. I'm a Postgress DBA. No. No. I know Scott is okay. He's one. He's on my team. Anyone else? Understood. How about who has uh access to posgress DBA?
[00:31] Is there someone you can call in the middle of the night when things go south? Who knows posgress? Anyone? All right. No. Then my talk is exactly for you. Um the obvious tip here is to have good
[00:44] observability. Collect uh those signals that matter at scale. Your enterprise gold standard explaining showing what's happening right now. We are active sessions. What are the queries that are waiting to execute?
[00:57] What are the logs? Uh what's currently slow? The top slow SQL queries, the execution plans. You want to watch how they change, what changed recently? Was there any DDL change? The list goes on
[01:10] and on. And if you have a Postgress DBA on your team, they'll tell you collect this. And uh well, many uh serious enterprise tools will give you this information. AWS performance insights, Oracle, they all come with this uh lake
[01:23] base as well. Um many of these live signals are also available from posgress directly. Uh so if you're running posgress yourself, you could collect them but it uh but you need to set up the collection scripts. Uh it doesn't
[01:36] come for free. That's something that DBA will help you ensure. Some of these signals aren't available from posgress out of the box. So you'll need an extension a custom script and again a posgress DBA will help you there.
[01:49] uh collecting these signals is important but knowing how to read them uh is is a is a different thing. That's something that a DBA can help you with and we've just seen that not a lot here not a lot
[02:01] of people here have access to a body of posgress DBA. Uh where did they go? Did they disappear? Are they in the bar? Can we bring them back? In fact, what really changed is how many applications we have
[02:14] right now. It all started with monolithic applications that are huge huge databases but there was just one database that you cared about. You had five DBAs watching it. It was perfect. Then cloud and microservices came in and
[02:27] every dev got access to the AWS cloud console started creating the apps and there there's more apps and today every dev gets what three five agents I can run three agents in parallel. They'll be
[02:39] creating new software even more apps even more databases. So how do we cope? We don't have a posgress PhD. Uh we don't have a posgress DBA either. How do we survive?
[02:51] For this talk, we looked at 20 postmortems of public companies that relate to outages that touch a database. So in this analysis, we saw that there is not many classes of outages uh that
[03:04] uh uh that that are that are typically present. The biggest uh group here is uh schema changes. uh when applications uh change the schema that's that's the third of all outages. Uh so the typical
[03:18] story here schema change was applied we didn't realize we dropped some data and now we're trying to recover it takes forever to replay that while log on top of our snapshot and so we're down for hours and hours. Uh the other group here
[03:32] is connection storms. So each each posgress connection reserves some memory on posgress and hitting the ceiling is easy. Uh when queries uh slow down you have uh an application retrying as
[03:45] opening the new connections and here your your connection count cramps up uh silently uh and but then it hits hard when you uh can't establish the new connections anymore and start showing uh
[03:57] errors to your uh to your users. capacity is about 10% of the outages. That's the slice uh that's easiest to watch. Uh it creeps gradually uh and so
[04:09] if you watch it closely uh you can catch it. But who watches it closely? Um uh a common pattern here uh is that a lot of these uh outages that touch the database they are triggered in uh by a
[04:23] change a change in your application or uh a traffic pattern uh that you take in your app. So database and application are intrinsically connected. Your database isn't just a dependency for
[04:36] your app. Your database doesn't just take your app down. It works in reverse as well. You change your app that can kill your database. So you really need to be careful here. Uh the app and the database are part of the one one single
[04:49] life cycle. Um so one one tip I can give you capture your application telemetry. Don't just watch the database watch the your app closely and try to uh to have this uh
[05:01] data as one single correlated timeline. Perhaps you can lend them together in in one data store. Have a have a dashboard that shows side by side the metrics about your database and the app. uh lakebased lens uh applic your metrics
[05:14] from datab bricks apps and uh from from posgress in the same unity catalog they they are side by side um in your lakehouse. So capture these signals before you need them. Um this is a
[05:28] classic example of uh of schema change going wrong. Um they thought they were not using uh using the new tables. they decided to clean up some test data and then they uh they truncated the table
[05:41] and that took uh the real data down as well. Um for in this case uh the schema change was the start of the outage. So what can we do? We can watch all the schema changes, all those DDL statements
[05:54] that changed your database, whether they come from an app or whether they're manually executed by someone who connected to the database directly. Um, uh, collect them. Uh, on lakebase, we
[06:06] log every DDL statement and that's also available in your Unity catalog. Um the other tip uh that relates to those schema change outages is keeping enough history point in time history to
[06:20] recover your database. So in my opinion 4 days is the minimum uh you should get. Think of an outage that happened on Friday. On Monday that outage is 3 days old. Maybe it wasn't a critical uh
[06:32] problem that affected your proto. So you can recover it after the weekend, but you if you only have one day of history, you won't have that option. You'll have to be there until Friday night and then
[06:44] you'll be sick and tired and uh who knows if recovery will go right. So keep that history at least four days a week is better. If you if you can do 35 uh uh
[06:56] 35 days uh that would be ideal. um and uh rehearse uh the restore uh on schedule. Do that regularly. Know the tools uh you need to um you need to use
[07:08] for recovery. Uh GitLab learned this uh the the hard way. Uh back in 2017, they they had five different backup mechanisms for their main database and none of them was practiced and worked.
[07:20] So the outage took hours to resolve. Uh don't be in the same boat. On Lakebase, a restore is uh just a few clicks uh with a separated storage and compute. Uh we create uh uh a restored copy out of
[07:35] your snapshot or from point in time in less than a second. It's available there. You don't need to wait for the uh for the while to replay on top of the snapshot. A typical story uh with recovery on other databases.
[07:49] Then uh capacity. Um uh what I'd recommend here is to alert on the trend. You're running towards the cliff. So think of how long till you get to that cliff. Don't alert on those short-lived
[08:03] spikes. They might be normal for your application. Um uh so measure how build a projection uh time for to to hit the threshold and alert based on how long do
[08:15] you have better use elastic compute and bottomless storage. uh that's uh available on lakebase today. Uh then uh heavy analytical queries. We
[08:28] like to run analysis on our live production databases, don't we? And a heavy analytical query would evict your working set and drag everything else down with it. So watch your slow queries
[08:40] um uh find those who are running them uh evict them from your posgress and uh ideally run them on lakehouse. You could do that with Lakehouse RT. Uh sign up for a private preview. If you don't have
[08:53] Lakehouse, perhaps at least set up a replica so that these analytical queries don't hit your live database. Um the other tips and many of these
[09:05] transfer well from lakebase to any other posgress or even to other databases. My SQL is uh quite similar. Uh pageionate everything. Never return an unbounded
[09:17] result set. And please don't fake it. If your your page size is million rows, that's not really a page. You really need to constrain how much work you're doing. Um try to keep your transaction
[09:29] short. Uh the long ones will block vacuum and uh that's not great for posgress. You can lose uh rights to your database if vacuum doesn't run. So keep after autovacuum on if you're running
[09:41] posgress yourself. Legbase will take care of it. Uh if uh if uh if you're using lake base um t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t t tend to your indexes, prune them um and add the missing ones.
[09:56] Um isolate tenants. Uh that's uh an important one. Back in the day, we had uh servers there servers that they used to be scars. We'd purchase a server and then we'll try to cramp everything into
[10:08] it. All of our databases will live on the same hardware because we didn't have any other hardware. it was difficult to obtain and purchase. Today is a is is is a different world. We're in the world of the cloud of elastic compute. You don't
[10:22] have to overprovision. Getting a database is one click away. So, uh don't put all of your uh databases into one single instance. Uh give each app its own database, please. Noisy neighbors
[10:35] today is your choice. Uh so, choose uh not to be one. All right. I'm uh going back to where I started. Uh these signals they matter at scale and they're the your enterprise ba
[10:49] baseline uh I mentioned before you need to collect them and then once collected you really need to to give someone a task uh to to watch it. Um uh no human can actually watch every graph every
[11:02] single log line. Uh but uh we're now in the world of the agents and agents can they can continuously watch your database and make sure it's healthy. Um and they they have all of the vast
[11:14] knowledge of DBAs uh in their mind in their memory right now. Uh in Lakebase the agent that helps you watch uh your database is Genie. That's the assistance assistant many of you already have and
[11:27] are using in Lakehouse and soon we're bringing it to to Lakebase 2. Uh, let me show you this in a quick demo. Uh, all right. Is there a video here? H,
[11:48] okay, it it does play. Um, over here I have a database. That's a uh coffee shop. We're selling a lakebased autoscaling blend that has a bit of neon tint in it. Uh, you can purchase coffee, see past orders. There are no no past
[12:01] orders here right now. Uh but we can add some to the cart and um yeah uh that's a simple flow. We can also see the and the page of the past orders that rendered
[12:13] real quick. But suddenly an influencer posted around about our blend and uh lakeb autoscaling blend became popular and well CPU started to creep up. Um
[12:25] some other graphs are flat like nothing's happening. uh there's lots of queries being executed and query execution time is going up. Yeah, these symptoms are clear, but what do I do? How do I fix this? Do I have more data?
[12:39] Um, of course, there's uh let's let's just check what's actually going on in the app. Our query is now almost 30 times slower than it used to be before. 30 time times. That's a big uh big
[12:53] increase. So, other graphs uh let's check here. We have no idea what's going on. But tada, we found uh Genie here. So, let's ask Genie, why is my page so slow? Uh what what brought it down?
[13:08] The genie will start looking at the data in your Unity catalog, the those observability uh signals uh that we export. It realizes that it should look at PG start statements. Uh um it didn't
[13:21] figure out the schema right away, but on the second attempt, it understood the data that we're writing there. Uh it looks at the weight events now and active sessions history to understand what's going on. Um
[13:35] on and on uh looks at the query execution time. It correctly deduced that we are talking about the orders table. If uh if we mention the orders t page and now it realized and it's telling me there's no index on orders
[13:49] customer email. That's uh the root cause of our problem. All right. Great. uh happy to hear that there is no index but I hear that adding an index is a risky step like it can take forever sometimes
[14:01] to build that index and it can block uh rights or reads to my database. So can Genie help me create uh this index safely? Let's see.
[14:15] It tries to understand the scope to build the the index. It realizes that I want to do this safely. So uh we should create a test branch first uh and try to apply index
[14:28] there before running it in production. So test index test in that branch that it created.
[14:40] Now it applies on that branch. If we go to the branches, we'll see that there is a production and the index test one both are active right now. So indeed the the validation happened on a branch here.
[15:00] It tells me there's uh that the plan will with this index plan flips from a full sequential scan to an index scan and that will make my query way faster. Uh it asks me for a confirmation.
[15:20] I consent. And voila, our page is fast again. We're back to seven milliseconds here.
[15:32] All right. So, what just happened there? Um, it all started with uh with the lake base capturing your signals in uh your delta tables in Unity catalog. We're doing that continuously. Um, then I asked Genie for help with diagnosing uh
[15:46] uh diagnosing an issue. It looked at all of the data I had, found the right data points, and understood the root cause. Um, it applied a fix on a branch without affecting a prod to validate that this
[15:59] fix indeed addresses the issue. The branch is is a copy on write branch. So, it's a very quick to create within second that copy was available for me to to test the uh uh to to test the
[16:13] solution. We confirmed uh uh that we like the solution. We approved uh the actual change on production and finally uh the index uh was created on prod as
[16:25] well. Um so the lakebased collects table signal signals that an expert DBA would uh would ask for those active sessions and
[16:38] weights your top SQL and query plans. All of this is available here and we once sir charge for observability per feature. Uh you just pay for the storage in uh in delta and for compute for
[16:51] querying it. We track the full DDL change history and um and offer longer retention than anywhere else on the market. And finally uh secret source genie will not just uh read and diagnose
[17:04] the problem, it also will resolve the issue for you. And uh we offer background genie too. Uh that one will wa watch your database in the middle of the night. So if the issue starts while
[17:16] you're asleep, you'll uh land on uh um on the insights page that already informs you about the things that uh you might want to uh want to look at.
[17:28] All right. So one last thing uh what makes the recovery cheap to rehearse the separation of storage and compute. um that also let Genie apply that fix uh safely. The same capability powers uh
[17:43] other features um database branching um so uh database branching opens a lot more than just the recovery and uh later today at 12:20 on in that theater uh you
[17:57] can uh watch my talk about the dev workflows with uh with lakebased branching. Thank you.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.