Lakebase Best Practices: Postgres Schema Design, Autoscaling, Branching and Sync

Summary

Managed Postgres database: Lakebase is a managed Postgres database on Databricks with stateless compute separated from shared storage, enabling fast branching, autoscaling and point-in-time recovery.
Production best practices: Size the working set against the local file cache, use pgvector with HNSW indexes for agent memory and embeddings, and tune autovacuum configuration per table.
Fast branching and scaling: Atomic copy-on-write database branches create in about two seconds regardless of size, allowing testing and recovery without panic, plus scale-to-zero capability for cost optimization.

Lakebase Best Practices: Postgres Schema Design, Autoscaling, Branching and Sync

Talk by Som Natarajan, Solution Architect, Databricks
Lakebase is the managed Postgres database on Databricks, and its architecture, stateless compute separated from shared multi-zone storage, is what makes branching, autoscaling and point-in-time recovery possible. This video covers production best practices: sizing the working set against the local file cache, using pgvector with HNSW indexes for agent memory and embeddings, choosing partitions, tuning autovacuum per table, understanding scale-to-zero cold starts, and creating atomic copy-on-write branches in about two seconds regardless of database size.
Learn how to size your working set against the local file cache (LFC), when to use pgvector with HNSW indexes for agent memory and embeddings, how to choose partitions, and how to tune autovacuum per table. Database branching creates an atomic copy-on-write branch in about two seconds regardless of database size, so you can test changes or recover from an accidental truncate without panic. Also covered: scale to zero and cold starts, branch ownership and permissions, snapshot versus continuous sync tables (each continuous pipeline can cost $18 to $40 per day), connection pooling, Private Link, and monitoring with Grafana.

Chapters

FAQs

Why is working set sizing important for Lakebase performance?

Working set sizing ensures your active data fits in the local file cache (LFC) of your Postgres compute nodes. When the working set fits in cache, queries perform significantly faster because data is accessed from memory instead of requiring I/O from remote storage.

How does pgvector with HNSW help with AI applications?

pgvector with HNSW indexes enables efficient similarity search for embeddings and agent memory. This is particularly useful for building AI applications that need fast vector similarity operations, such as semantic search or retrieval-augmented generation (RAG) systems.

How fast can you create a database branch in Lakebase?

Lakebase creates atomic copy-on-write database branches in about two seconds regardless of database size. This speed enables testing changes or recovering from accidental data modifications without extended downtime or complex recovery procedures.

What is scale-to-zero and why is it valuable?

Scale-to-zero means Lakebase compute can automatically scale down to zero when not in use, stopping charges for idle resources. This capability significantly reduces costs for development, testing, or production workloads with variable traffic patterns.

Full transcript

`00:00` Introduction

Good day. Show your hands, how many of you are using Postgres in your work environment today? Wow, a lot of people. So, which one of you would do this on a production server? Oh. OK. So, I just accidentally truncated the transaction table. It's quite large, and usually in your Postgres, self- managed Postgres, which I've dealt with before, this is the event that forces the resume to be updated , right? You delete data in a spreadsheet, panic, and then decide, "Oh well, it's not worth it.
I'll go do something else. Maybe I'll become a mountaineering instructor." Yes? But what sets Liquibase apart are our instincts, which we have developed over the years of working with Postgres. And I'll show you why some of those instincts are no longer relevant. Yes? Actually, this is the idea of today's session, which, by the way, will last 90 minutes. Yes? I would like to divide this into two parts, but unfortunately we have very limited time at the summit.
So we're trying to cram in as much information as possible so that you become responsible Lake-based professionals, right? This is the main idea of this session. So, I performed a quick point-in- time data recovery. So I didn't have to panic. I just found a way to restore my old working branch to a new one. And everything is okay, right? I no longer have to worry about rewriting my resume. So, Lake-based architecture in 60 seconds.

`02:27` Lakebase architecture: separating compute from storage

The fundamental difference from previous architectures is the separation of computing resources from storage. I mean managed scalable computing that starts, shuts down, scales up or down, even to zero, and offers instant branching. This is the exact trick I used to save myself just now. And this is a reliable, managed shared storage that is multi-zonal, i. e. automatically geo-redundant, to which Lake-based computing simply connects as needed.
This is the second part. So everything: branching, autoscaling, point-in-time recovery, snapshots—it all comes from this single entity of separation, right? This is what makes Lake-based so different from what we typically encounter in the case of self- managed and hosted Postgres. So, this is the architecture that I'm going to be covering for these 90 minutes . No more architectures or anything related to them. Welcome to the Lake Base Best Practices session, where I'll try to highlight typical recommended architectural principles , pitfalls, and hidden issues where your Postgres habits may be costing you too much .
Why? Because the Lake Base architecture is designed differently. My friend Yannick was supposed to be here. He was detained by family circumstances. May he be lucky. So I will try to convey his thoughts. So, today you will only listen to me. And, by the way, the door is closed. So you won't be able to just walk out or anything. I hope these 90 minutes will fly by. Typical statements about future plans. I'll probably mention some aspects of previous versions if I use them in my demos, but otherwise it's standard stuff.
Don't worry about it. So what will this give you? Why should you know this? Well, apparently it's after lunch . You had a rich keynote speech. And you came here hoping it would be helpful. We will do our best to make this useful for you, but in general it is for architects, platform administrators, and anyone looking to use or already using Lake Base after the POC stage. Why? This is a very new service. It's like a baby, right?
It is only taking its first steps in the real world, so I expect many of you to become future Lake Base experts. If you don't know what Lake Base is, it's a little awkward, isn't it? I have no other plan. I had a trick with truncate, but stay with us. There are probably many more tricks besides truncate, and I'll try to make this as useful as possible for you. So where are Lake Base operations typically used? There are three aspects here.

`05:50` What is Lakebase used for: feature serving, apps and agent memory

The first one— you are all familiar with the Delta Unity Catalog. You have information stored there. You want to bring it into a structure with very low latency and guaranteed transactionality, right? So, this is the first data. Consider this as feature serving. You have online scoring models. You want to do feature engineering, stream them in real time, your models will use that, and that's what you'll be using it for.
Applications—this is a huge area, and I always joke that 10-15 years ago, everyone used spreadsheets for everything, right? In October, I started doing wipe coding, which I knew nothing about , and then I realized that everyone was doing it. So I developed a fear of missing out (FOMO) when I started doing codeless programming. And I didn't even notice when everyone started doing it. I usually see apps becoming the new spreadsheets.
Do you have a business process? I have an app. Do you have an error monitoring system that you want to create for multiple users ? Let's make an app. You want to engage in financial monitoring or risk monitoring in financial organizations. I want to create an app. So everyone, even kids, is creating apps. When you build applications, of course you need a fast backend because transactional guarantees are required. And you also need experience with low latency.
You don't want to click something in the app, go have a cup of tea, and then come back. This is a very bad user experience. So you need a fast backend, and that's why LakeBase becomes the automatic choice for anyone building apps. And the last one is the circular data economy. You had something in Delta, you brought it out to LakeBase, and let's say it's used for agent memory or conversational agents use it as cheap vector storage.
And agents create many interesting memories in your app. And you want those memories to come back to understand how your agents are working and perhaps evaluate their performance. You could evaluate the agents' responses. These are the datasets that should go back into Delta because Delta or your lake house should be the agreed upon source of truth for everything. This does not change with the advent of LakeBase. So those are the three aspects I wanted to make sure you knew about.
This is a great 4x4 that you will take home with you. If after 90 minutes you don't get anything out of today, yes, I deserve all the curses. But you can still remember these four things: you need to look at schema design differently because we have disaggregated storage, so you need to design schemas to be copy-on -write friendly. We'll be talking a lot about copy-on-write anyway. Don't worry about it. Um, branching.
There is a lot of confusion: should you create branches from the production environment to the development environment? Will these branches be like Git, where I can take data and then merge it, like in GitHub? There are many myths surrounding this. We will try to debunk most of them, if not all. Um , service principals . So, one of the best practices in Databricks is not to make your friends owners. Let's say you have a development team, you like someone sitting next to you, but don't make them the owners of your database instance or even the tables.
Of course? Because if you have disagreements with this person or they decide: "Okay, I want to move to another organization." You will be left with abandoned ownership and " orphaned" objects. Therefore, the fact that it is worth using service principals or a group owned by a service principal does not change with Lakehouse. You still have to stick to these basics. You still have to do it very, very well. Um, "it's impossible."
First of all , it is a managed service. So if you expect, "I'll get regular superuser privileges to go in and say, ' Hey, I want to change work_mem globally'." or "I want to increase my shared buffer pool." Such things are not possible with Lakehouse. Why? Because we made sure to properly consider all these aspects and decided that this is exactly what you will get. So, there are certain GUCs that we support, but only locally.
We will touch on some of these GUCs and where they will be useful as well. These are five key topics that I picked at random and decided, "Okay, I'm going to really dive into this and try to make our conversation exciting." I don't know if it will work, but it's a very dry topic, right? Databases, you know, roles, access rights, table synchronization. I don't know how to make this interesting. So what I'm going to do is this: I'm going to divide this into two parts.
Maybe the first 45-50 minutes we'll talk about the first three aspects of this. And then we'll take a short break, about 5 minutes, so you can rest. And maybe do a quick 5-minute Q& A session. And then we will continue our journey for the next 45-50 minutes. The last 5-10 minutes are reserved for questions in any case. I don't think this hall is going anywhere. And I'm not going anywhere. I'm here at least until Friday .
So we can discuss all your detailed questions as much as we want, right ? This is how I would like to spend it. I was hoping that 5-10 people would come and I would just have a question and answer session and we would have a good time, but I don't think I can do that now. I have to stick to the slides I have. So let's go into a little more detail, because most of this is very relevant as we touch on each of the aspects I've talked about.
So, we've already set up the magic of, you know, branching, PITR, snapshots. Now let's look at the system. So you have stateless computing running on Kubernetes and completely managed by us. Um, this is the Postgres engine you know and have come to love. He fulfills requests. Every now and then he complains that you're running something you shouldn't. This is the computational part. And it also has two aspects of caching.

`12:57` Postgres compute, local file cache, page servers and safekeepers

Most of those who raised their hands when I asked who uses Postgres in production know what shared buffers are. You know, the PG buffer pool, everyone knows about that . But this is the least used part in Lake Base. So our shared buffers are a nearly stable, maybe 1GB shared buffer, regardless of the size of your compute units for autoscale instances. The vast majority of our caching is done using what we call LFC. So, the local file cache is usually about 75% of the available RAM.
This is our caching level. The local file cache is based on NVMe drives. It's expensive, so customers often say, "Hey, my Deltas are really cheap, and you say you 're using object storage." " Why do I have to pay extra for storage? " This is because of LFC. So, LFC is the foundation that computing relies heavily on to provide sub- millisecond latencies for your transactional operations. And of course, the ultimate repository is your object repository .
This does not change. And when we talk about storage, it's about making sure it's reliable and highly available. So, you have your page servers . Consider page servers as the source of truth for all PG pages for your calculations. So, page servers are the ones that asynchronously query the state from the WAL and reconstruct pages for your computations to serve requests . That's what page servers are. And safe keepers, we have three of them.
We use a two-out-of-three quorum commit scheme. Each of them is located in separate availability zones. So, our safekeepers are also redundant in their architecture. Isn't that right ? This is why we can ensure high availability. We can perform instant branch creation. We can do point-in-time recovery . All this is possible because we have this architecture. That's actually what I wanted to talk about, the driving force behind this .
I've already told you about the reading path: the first place to go is shared buffers. If the pages are not there, it accesses the local cache. If not, you have to make a network request to the page service, which is expensive. We'll talk a lot about how to design Postgres databases in Lake Base and why you should even bother with LFC, right? So I won't go into detail right now , but we have a lot of material on this.
So, what is circuit design? And , by the way, I didn't choose this . I was assigned this report on Lake Base. They told me, “Hey, this is a brief that we already published. "You have to tell me about the design of the scheme." And I had to rack my brains a lot: what should I talk about in this topic? And then I realized that there were a lot of topics, because when I started talking to clients, since I'm on the Lake Base promotion team, I saw a lot of people doing different things, thinking, "Yeah, this is a database.
I should be able to do this." Or: “This is a relational storage. I know what I'm doing, right? So there's a lot of nuance to this conversation, which is why I think it's important. Where do you encounter this? The first question, of course, is: "How do I choose the right size?" You know, in autoscaling there is a minimum number of CUs and a maximum number of CUs. I don't understand what CU is. What should I do about this?
And then you ask someone like ChatGPT, and they say, "Okay, have you used Aurora before?" And they have something similar. So you don't get a clear answer on how to design it. And, to be honest, sometimes it's an empirical approach because there's no hard definition of what a mine is. CU, and this is the max. CU. And that's why understanding your work set becomes crucial. So what do I mean by working set? Let's say you have a database that's, I don't know, 10 terabytes in size.

`17:53` How to size the working set and the local file cache

Would you keep 10 terabytes in memory all the time? In that case, you shouldn't use relational storage at all, right? This is not the purpose of relational storage. You should look into something like Redis. Upload 10 terabytes there and you'll probably have to rewrite your resume, right? It will be too expensive. So, from an architectural perspective, you have to think about what it means for the working set if I'm serving a certain workload.
So, 10 terabytes is not my working set. My working set is, by definition, the pages that I have to keep " hot." What I mean is that the amount of what I can fit into my LFCs determines my work set or vice versa. How much of my LFC memory is available to accommodate my working set. So you have to be very, very careful about understanding this concept, because it's important to think about working sets in this way . Why?
Because you will get your min- max CUs, and as we scale the compute units, the amount of RAM increases as well , which means more LFC. Let's say you configure autoscaling from two to four CUs. And I know that two CUs will probably have four gigabytes of RAM. Then 75% of four gigabytes is your entire LFC capacity. So, if the size of your working set I'm not very strong in math. How much is 75% of four gigabytes? In any case, this is the size of the working set you should focus on.
And if your work kit fits in there, great. You've done your first size assessment with Link base, and everyone will be happy. In most of our real-life cases, things are not that simple. Why? Because usage is dynamic. You don't know how many users there will be. I mean , you can guess at QPS a little bit, but understanding the working set will be influenced by many other factors. Therefore, it usually comes down to an empirical approach.
You start with something small and then someone complains, "Hey, you designed a new system." I'm not very happy with it. I click on something and wait forever. It doesn't work for me. So you have this difficult conversation, right ? This is how you arrive at determining working set size and computing power. Branch-friendly schemas—what does this mean? This means that you should consider branches as your ephemeral CI units.
So, if you're using CI/CD, this is how you should think about it . You shouldn't think of it as " hey, I have a branch and I developed something amazing there." Now, can I merge this with the main repository? Well, it's usually a database, for goodness sake. You can't just arbitrarily merge data schemas into something else. What happens if you break something in your working branch and there's a serious problem, right?
So we have to be very careful about what we allow in some of these cases. Yes, there are some drawbacks because, um …Databricks customers typically have a workspace for development, testing, and production. And if I tell my client, "Hey, there's a working LakeBase instance, you can create a development branch from it." They will just laugh at me. I mean, it's called a workspace . This is called a working instance. Why should I create a development branch from a workspace?
This is a valid question. We are working on a solution. Not as fast as you might expect, but these are some of the responses we received, and they seemed pretty valid, right? This is a really pertinent question for us. But for now, you should consider branches exactly as I explain . When it comes to time series, a lot of customers ask, "Hey, TimescaleDB, I love it." Unfortunately, it does not have open source code. And if we go down the path of implementing third-party software developed by someone else, we will no longer be in sync with the community version, or we will no longer be able to simply say that it is Postgres.
This is why TimescaleDB is not part of LakeBase. Most likely, it never will be. I'm just being honest about it. We will have an option for working with time series using an Apache extension, but you have to think about TTL (time to live) when it comes to time series. You should think about partitioning, right? Because you have to consider your business needs and then decide what makes sense for your time series data.
You should think of your hot data path as being in LakeBase and your cold partitions as being in Delta. What does this mean? This means you can perform incredible time series queries with LakeBase with millisecond latency. And if someone wants to know about something that happened 5 years ago, you say, " Okay, contact Delta." We have something that allows us to work with LakeHouse in real time. Maybe it's worth using this engine, right?
This is the hybrid architecture you should be thinking about now. This is no longer the world where there is one driver that does everything. Rather, we have to choose the tools for the task. And wide rows are a real problem in Postgres. All of you, if you work with Postgres, know that now you need to think about TOAST. You must be thinking, " Okay, how do I ensure that my query doesn't run slowly because I arbitrarily did a select star from without knowing that there 's a huge JSONB column there that's killing your latency?" .
These are the nuances you should think about. And we also talked about the impact of latency on cold pages. I said that every request is a challenge if your working set is not within the LFC. And RAM optimization: I touched on allocating LFC from the RAM of your existing CU units. So this is very, very helpful. So when you think about migration, right? You shouldn't think: okay, I'm running this server . I want to make an identical server on Lake Base.
Think about it in context: if I have 100 databases, how do I distribute them by working set size and move on. That would be my approach to all of this . Indexing, because I've seen a lot of hands raised, I know you're very, very familiar with indexing. But for those migrating from Delta, some of my clients say, "Hey, I have clustering in Delta. I'm using Z-order, liquid, can Lake Base somehow automatically work with that?"
No, it ca n't. You will still have to think about indexing separately. You still have to think about index coverage in both directions. If you have a predicate that says where A, B equals something , and let's say this is a graph use case, and you want to do a self join with B, A, you have to have indexes that span both directions, A, B and B, A. This is very, very important to understand. Indexing determines whether your heap seek will be small, large, or will simply perform a parallel sequential scan.
The moment you see it, you know you did something wrong. PG vector is a very useful extension if you want to have a single operating surface for your agents. So, let's say I was talking about agent memory, I was talking about OLTP. Now, if you want to do small or medium corpus embeddings for your agents, consider PG Vector. Why? Because vector indexes are expensive. Um, including our service. You know, I don't hide it.

`26:12` pgvector and HNSW for agent memory and embeddings

If you don't really want a massive vector index running 24/7, you might want to consider: is there a low-cost way to do this? And PG Vector can definitely help here. But you will run into performance issues if you do a lot of embeddings. So it is not designed for ultra-low latency. Hey, I can run this on a large scale. I can do whatever I want with it . It's more important to make sure you're mindful of some of the performance issues involved.
Ultimately, HNSW wins over IVF flat because HNSW is a graphical index and you don't need to pre- populate data if you use HNSW. So, if you want to go down this path, you should start thinking about these points. So I'll quickly show you what I'm going to show you? This is where I'm going to switch things up, because if I talk too long, people will either run away or start using their phones. So I try to take that into account as well .
So I'll try to liven it all up a bit. So, in this case, we want to demonstrate how you usually understand where to start, because starting is very, very difficult. I want to understand: okay, I know the query is very slow, but I don't know how to do it. I don't understand anything at all, do I ? That's where I started. So, the interface is a little different. The SQL editor remained the same . You'll still get all the usual features, but you still need to understand where and what to look for.
So, for this particular " select count star from transactions" I got a parallel sequential scan. What we all fear, right? Once you see that, if I go to buffers, you can see the number of pages that are being read. This, this is not good. You need to start thinking, "Hey, what do I do with this?" You could do an index creation. As in this case, I took a very simple example. So you can say, " Okay, this, this, this, even my son can understand."
I don't really need to sit for 90 minutes to figure this out. But this is just an introduction to how you should think about indexes. And where to start when the query is running slowly ? And that can be a huge request. I once optimized a huge query for my client. It had about 450 lines. I'm starting here, right? There is nothing special here. No matter the situation, with some things you just start simple, figure something out, and then say, "Okay, now that I've done something, what does it look like?"
That's how you start thinking about these things. This is an index-only scan. Once you see index-only scanning, you've done well. You don't need to complicate anything beyond that. Coming back to this point, the next important element that I will touch on a little bit is partitioning. Why? At Delta we say don't partition at all , right? We don't like partitioning in Delta . Why? Because many customers end up partitioning things they shouldn't, and then start complaining : "Hey, it doesn't work for me ."

`29:58` When and how to partition tables in Postgres

"It's very slow." So, we don't want partitioning in Delta , but Lake Base is completely different. Delta is a columnar storage. We don't need partitions. We need clusters. That's why we do clustering there. We use liquid clusters. We do Z-ordering. All this is familiar to you guys . But Postgres is a string store. I can't survive without partitions. I need partitions. So, how do I choose partitions? It depends on my business needs.
This is based on the predicates of my queries. And it depends on the data's time to live. You have to think about the lifetime of the data. What do I mean by lifetime? This means that I cannot store information in Lake Base indefinitely. I want to say, "Okay, this is what I'm going to keep, and here's the reason why." So, if you have partitions, you can use a sliding window, right? You create a new partition, the old partition just disappears.
This is an atomic replacement. Very, very quickly. So, that's what you should be thinking about as you start looking at some of these lifecycle events around administering your Lake Base. I was talking about PG Vector. I won't go into much detail about this , but this is a case where you want to provide moderate scale co-location with your operating environment if you're running a lot of AI agents that are not only very chatty, recording a lot of conversational memory, episodic memory, etc. , but also want to understand more context.
You want to choose a specific RAG and create a vector storage around it, this can be very, very useful. So, we've already covered many of these points. Automatic vacuum. We run the autovacuum ourselves. So you don't have to think about "hey, dead tuples—that's why my query is running like a turtle." I know I solved this issue . You still need to have some GUCs that can be configured, but this is done at the individual table level.
So , in this case, you can set the zoom factor. Usually the default is 20%. That is, I need 20% of my table to be dead tuples so that autovacuum can take this table into work and clean up the garbage. But you can adjust this at the individual table level. Globally, this is impossible. As I said, these GUCs are blocked. For each table, you can change the scaling factor and say, "Hey, I want to reduce it to 2%." This means that the autovacuum will still work on schedule, but it will take this table into operation much faster.
Where might you need this? This will be needed when there is a lot of bloat due to upsert operations. So you do a lot of upserts and realize that because Postgres records history, it creates versions of those pages. It doesn't actually do a complete rewrite. So, this is where it becomes very, very useful. One of my customers asked me, "Hey, can I run the vacuum manually?" I am very paranoid about car vacuums. I don't like anything automatic.
I don't even drive a car with an automatic transmission. I drive a car with a manual transmission. So I want to run the vacuum manually. When you run the vacuum manually, do so without the full command. That is, don't do a vacuum full. This means you are greatly inflating your storage. And you might think that this will help you with performance, but eventually your storage costs will suddenly go up, and you'll say, "Hey, where did these storage costs come from ?"
So be sure to consider these aspects. Normally I would n't change any of this , you know? I'm not doing any of this . In a pinch, I'll just change the scaling factor for specific tables under certain conditions, okay? That's the main thing. Don't do this for every table. Do this under certain conditions, such as when loading an upsert, where you are constantly updating data, and the system will automatically handle everything.
If you already need to run a vacuum, run a regular vacuum, but since we don't have PG cron, many of you are disappointed, I know. PG cron will appear in Lake Base, but for now you will probably have to schedule a job outside of Lake Base for this. So yes, it's uncomfortable. Don't do this. Yes, we've already discussed all this. And one thing I want to demonstrate next that is very, very useful is the toast part. So if you look at this particular query, I think it will execute very quickly.
So, the main heap is very, very small. It takes up 4 MB. And the JSONB column in this table contains the rest of your data. Many customers tend to add JSON to Postgres. Lake Base is no different. It will process your JSON data exactly as it should—as a set of information that it doesn't know what to do with, right? And this is where you need to be careful about how it works, because you will encounter things like this very quickly, especially when, for example, you are receiving IoT data.
Typically, these are JSON streams or clickstream data. And you want to upload them directly to Lake Base. You shouldn't do this, but if you do, there are some nuances to be aware of. So start checking your buffer reads and understand what you're actually wasting time on, then move on. Usually when you have JSONB, you need to, you know, take it seriously. You have to think: okay, of all the nested columns I have in JSON, do I really need all of them, or are just five enough?
And in that case, you know, you single them out. You break them into the appropriate columns and add them instead. Then you won't lose speed when executing regular select queries because the JSONB column slows down performance. So, here's where I would start. I do n't think it can be demonstrated, but I would still look at my buffers to understand where the congestion is coming from and how to deal with it. Usually, if you feel like there is no other way out and don't know what to do with it, split it into two tables.
Keep all regular columns in one—this is a very rare pattern. I do n't often see this in practice, but you can still do it. You can have a regular table and a JSONB-only table with the same primary keys. If you want to ensure data integrity, add a foreign key (FK) to the parent table and, if necessary, configure cascading deletes. It's entirely possible to do this, but take it more seriously when you're receiving JSON structures.
I think we discussed many important points. So, going back . This is where it gets interesting, right? We talked about auto-scaling. Scaling to zero is very useful. When you have many development instances running, developers don't work after 5:00 PM. At least they don't work anymore, but still . If you have a good reason why an instance shouldn't be running all the time, scaling to zero is an effective tactic for controlling costs, right?

`37:46` Autoscaling, scale to zero and cold starts

You can run a ton of instances, and as long as you configure them to scale aggressively to zero, you won't lose much money unless other features like point-in-time recovery are used. Point-in-time recovery usually significantly increases storage capacity. So if you set up, say, 30-day recovery for a large database, it's going to cost you a lot, right? Do not set a long recovery period for a specific point in time unless business needs require it.
If your business really needs it, or someone frequently does truncate in production, then you'll have to consider it, right? There's nothing you can do about it, but unless you have an urgent need, set the storage window for PITR to a minimum. Let's say 3 or 5 days is something acceptable. So that your expenses are also manageable. We looked at autoscaling and scaling to zero. The suspension time can range from 60 seconds to 7 days.
We usually enable scaling to zero by default for all branches. So, if you really want to turn it off, you can. And one of the cases where scaling to zero is disabled is in high availability settings. Is n't that right? Because if your instance needs to be highly available, it's a bit contradictory to simultaneously say, " Hey, I want to scale to zero and still have high availability." So when you configure high availability for your instances, scaling to zero is automatically disabled.
The " cold start" path is very, very important when you have scaling set to zero. And it will still come out sideways when you have feature design workloads. Because your online traffic is, you know, unpredictable and jumpy, if you set up scaling to zero saying, "Hey, I'll save on costs between 12:00 and 1:00," and suddenly there's a spike, then scaling to zero will give you a "cold start" problem. This means that usually when our instance goes completely into a waiting state and we want to get it back to working, say, establishing a connection, we have to perform a lot of background operations .
We have to connect the computing resources, set up the connection, run the extension, make the foreground, and then contact the page servers, right? Because LFC is completely cleared. We have a background automatic ability to restore LFCs from previous compute resources to new ones when we perform restarts, minor fixes, and resource replacements; we do this so you don't experience "cold start" penalties during regular maintenance at Lake Base.
But if you do, then use PG prewarm . Of course? PG prewarm is an extension available in Lake Base. Just run it so your LFC doesn't suffer as a result. So, this is something you should think about. We already talked about a branch for each PR. Think of it as an ephemeral initiative when you have, say, a current work problem and you want to figure out exactly what the complexity of a particular bug is. You want to correct a mistake.

`41:30` Database branching, snapshots and point-in-time recovery

Then, instead of running bug fixing workflows in a production environment, I can quickly create a branch, regardless of the size of the database. This is a very simple, atomic, 2-second action on which I can calmly perform my bug fixing or impact assessment tasks. So, that's how I would start thinking about creating branches. Um, yes, I already said that . Snapshots, you can configure snapshots for your databases. I would recommend this if you have a need to be able to restore them.
You can take a snapshot and restore it on another instance, or you can think of it as PG dump, PG restore. You do it and can even restore it to another Postgres database . All this is quite possible. And usually, you know, it becomes useful when you have archives for compliance, or you're doing a migration and you want to have a snapshot of the data, or you've done something terrible and you want to cover your tracks.
These are the reasons why you should use snapshots. Another very useful element is property rights. I've touched on the topic of ownership a little bit before, and there 's a nuance to this model. Namely, when I create a branch, we not only copy the data for you , but also all the roles associated with your main branch. So , when I made a fast branch, it gives me all the settings in full. Imagine getting an identical car that you can drive, thinking it's yours, but it's not.
Yes? That's what creating branches gives me. But the nuance is that if, let's say, this is my grocery branch. And if, let's say, I want to run this. I blocked the product branch , right? I said that no one can truncate it. I mean, the first video was just for effect, and you can easily get around that, right? You can be lenient with me , but that's the point , right? I have completely locked down my grocery branch, so it's not really possible.
And if I go to my development branch and if I look at what it looks like from the perspective of the next developer—the role I'm looking at—it doesn't have the inheritance option set. So, there is no inheritance option. But I can provide some option in my branch. Why? Because in production I don't want this person to delete or clean up data, but in my branch I can do whatever I want. So, I can set inherit to true, and if I run this, it will work.
Truth? So, this is quite powerful, and it's only possible because I have a group that belongs to a service entity that has primary ownership. So all I do is add more subjects to this group. So once you think about this paradigm of ownership, everything becomes possible, everything becomes easy and simple, right? So when you need to grant a special, very high level of access, such as truncate, you can quickly create a branch of your production system and grant those permissions to that branch, so that anyone who wants to do something reckless can do it to the branch.
And even if I run it, my main resource will remain intact, right? Um, if you... if you look at what the role looks like here, my main resource remains intact. She doesn't have an inheritance option yet . So it can't really do what my service entity can . Here, in a nutshell, is how I would like you to start thinking about information ownership. So, one of the things I'd like to quickly cover is the semantics of synchronization.

`46:17` Snapshot vs continuous sync between Delta and Lakebase

There is a lot of confusion about when to use synchronization. And given some of the announcements you saw during the speech, the situation becomes even more complicated, right? We don't know when to use synchronization. Even if I use the synchronization mechanism, there are so many modes. What should I do with all this ? That's why I felt it would be useful. Um, we have something called " snapshot mode ," right? By the way, regarding synchronization, I didn't cover the basics.
Synchronization is when you have information in your lakehouse and want to move it to lakebase in a managed way. That's what synchronization is, and nothing more. So, if you have feature maintenance workloads and want to synchronize information on a regular basis, snapshot mode works like this: it opens 10 parallel copy pipelines , copies the information to Postgres, and then we perform an atomic partition swap. So it doesn't write data record by record.
Continuous mode, on the other hand, is based on a change data feed. It picks up all the changes since the last stream, and then just constantly passes that information on. So , it really does insert line by line. But, because it does this with a very small amount of data, you don't really feel the difference. It works, but the cost can be very high when you have, I don't know, 100 continuous conveyors. Each continuous conveyor can cost you between $18 and $40 per day.
So, if you have a very large spreadsheet that you constantly synchronize, think about the costs. Consider whether this makes any business sense. So use a continuous pipeline when there is a real business need, but think of it as something that should only be used when necessary. Snapshot mode, always use it. I generally recommend using snapshot mode for the vast majority of use cases. We have a feature called accelerated sync, it's in private preview right now .
This is a direct write to storage approach. We are not going through the computational power of Postgres. We write directly to S3 or ADLS, which allows us to achieve a 2–10x speed increase when writing an entire table to Lake Base. The change data feed, again, is in beta on AWS. It's quite unique: let's say you sync information to Lake Base, then it shows up in your app, and the app writes the data back to Lake Base, and you're thinking, "How do I get this information back to my lakehouse?"
Should I create my own pipeline? Should I do anything else? This is where a change data stream helps. It uses logical replication slots and we do two things. We are temporarily unloading everything that has been there since the beginning of time. If the table has, say, 100 records, we unload all of them using one slot. And then there's the streaming slot for everything non-stop . So, if information comes into this table, we translate it into the delta table about every 15 seconds.
You even get op codes. So, is this select? Is this an insert? Is this an update? Sorry, not select. Insert, update or delete. You get these transaction codes and then you decide what to do with that data. Or just dump them into another table? Should I perform the merge operation ? Should I perform an upsert on the parent table ? All possibilities are available to you . When do you perform JDBC synchronization in Lake Base ?
A few weeks ago, my client asked me, "Hey, I've got about 80-85 continuous conveyors running. It's costing me a lot of money per day, and I don't know what to do." And I have a streaming pipeline that I need for my real-time fraud detection scenario. So, real-time fraud detection in financial services: they have to figure out who's being dishonest in real time, right? They want this information, but at the same time they face the costs of having to route the streaming pipeline to Delta and then from there to lake base.
So, this is a two- step strategy. But in this case, I think you shouldn't use synchronization tables. Instead, you should write to Delta asynchronously, and to lake base via JDBC. Why? I don't really need to think about the discrepancy between offline and online data in this particular case. Some of you might ask, "Okay, what if Lake Base gets the information first and Delta gets it, I don't know, 5 minutes later?" There may be a discrepancy between my offline spreadsheet and my online spreadsheet.
This is a valid and justified question, but for the vast majority of your scenarios, I don't think the offline-online data discrepancy will matter. If it's important, don't do it. If not, sometimes it is very useful to transfer data directly to lake base via JDBC. UC management is available for all synchronization tables. You, I think, if you've used Unity Catalog and sync tables before, you understand that we create an automatic managed Postgres connection in UC, you can see all the metadata, your Postgres schemas, etc.
And then we can control who can do what with all these tables in Unity Catalog. We're working on it, a lot of effort has been put into understanding how to unify the behavior of Postgres and Unity Catalog, because you know how it all works, and you're having a hard time understanding how to reconcile Unity management with role management in lake base, right? This is a big problem for many customers. We are working on finding the best way to implement this, but currently your Databricks principals and their permissions are not automatically converted to lake base.
So you have to manage access rights separately. UC has its own rights, and Postgres has its own roles. So, watch this video and tell me what's different while it plays. This is a 1- minute video. This is very fast. Oh, and this is a record, of course, because LFC is always 100. Normally you should see LFC between 99 and 100%. This means that your design is very reliable. Your table is perfect. Your work set is simply wonderful.
But look at what he does. Good. So, what's going on? So if I go back to the first set and pause it, you'll see that there's like a mountain there, right? And there is a red zone there. I'll show you how to do it first , because it's not part of the product. But I'll quickly tell you how to do it. There's a mountain there, a red zone, then it drops off, and you saw some errors. So, one of the biggest problems I see in app development is that they don't really care about connectivity, right?

`54:39` Connection pooling and application connectivity

There was a specific problem with my client where they said, "I'm using an asynchronous checkpoint." This is a linear graph primitive for creating short-term and long-term memory using Link base. And then they said, "Hey, I just installed this, ran it for a bit, and now Link base is working fine, and I'm getting a bunch of errors." The problem is that the asynchronous checkpoint they set up worked for every request. Yes?
And for each request, the minimum number of connections was set to 1, the maximum was set to 10. And as soon as I saw that, I realized: even if we had the capacity for 4,000 connections, which is a huge scale, and it was running autoscaling from two to four, that meant it had about 1,000 connections. Yes? And for each request, 20 connections are opened . So, within minutes he was facing a connection problem, right? Which is what this actually signaled to you.
So I faked it. How do I show it to you in a way that I can exaggerate for effect? Actually, what I did was " bombard" the instance with a large number of open connections , and then pushed it almost to the limit; This is a very small instance with autoscaling, that's why the red zone is so small, right? If you're running 32 CUs, your connections probably won't be that small. There will be thousands of them. And then I did something else , right?
This is the moment where there is a slight lull , and then everything remains in place. This is where I use connection pools. So, you have two options for configuring connection pooling. The first is the PG bouncer. Um, it's a simple switch that you use in your connection. So this is a very, very simple and straightforward thing for you. So if you go here and if I click " connect," that's it. Once you enable this, you will get a puller-based endpoint.
That's exactly what I did the second time, just addressing the same thing . I'm overloading the instance, but since I'm accessing the puller endpoint, it's very, very stable. It does not open new connections with each new request. That's why you can use something like a PG bouncer. Some customers say, “Hey, I don’t want to deal with database management. What else can I do? Use connection pooling on the application side.
So, if you have an existing application that opens a huge number of connections and people are constantly complaining, “Hey, it’s not working or it’s timing out ; "A gateway timeout error occurs in the web application ," this is the reason. So any application design should start with connection pools. Liquibase is no different, it's just Postgres, so use a PG bouncer or application-side connection. Yes. So, the next part.
We talked about connection pools. We talked about query plan analysis. I've given you a lot of information about what to look out for . OAuth and service principles—this is very important. If you don't use OAuth , sometimes you might have long-running tasks, right? For example, the transaction is open for over an hour because OAuth tokens have a lifetime of approximately 53–60 minutes , give or take. It's not exactly 60 minutes.
So have a plan to refresh OAuth tokens without breaking the connection or disrupting the application. Also LFC and preload , I was talking about that. If you have predictable working sets, consider the cache hit or miss ratio as this will give you an instant understanding of where problems are occurring . So when something goes wrong, it's not necessarily just a problem on the database side, right? This doesn't necessarily mean, "Hey, there's a problem with my query."
Your request may be a problem, but start on the left and work your way up. Sorry. Start on the right and move to the left, where, um, look at application pooling, at connections, then at your requests, look at what's there, look at your prefetch. It's like you have a predictable pattern of where to start and where to go. Don't immediately jump into my example execution plan thinking, "Okay, if I solve the database problem, then I'll solve everything."
Because your app will still go through the RTT network, right? Your round trip time (RTT ) is not just database latency. You still have a huge network in the middle. You still have your laptop. There are many nodes that the request has to go through back and forth. So your latency is a function of not just your database, but also your overall end-to-end latency. Therefore, it is important to think about this here. Another useful addition is the Lake-based Data API.
I don't know how many people have used the Lakes-based Data API, but if you have, say, a stateless backend on FastAPI that doesn't require a dedicated connection to Postgres or PGWire. Data APIs are your friend. And it is based on HTTP. This is REST, which is very, very useful when you have external application integrations that you need to manage, right? So, for serverless applications, there are a lot of things you can do with data APIs, and you should start considering them for such cases.
And regarding native Postgres— most of you raised your hands when I asked which of you uses Postgres in production. So you know where to use the native PGWire protocol, and all the session states, all the GUCs, everything I was talking about, you have almost all of that set up already. So there's no need to talk much about where and what to use . I talked about PG bouncer and application pooling. Um, it's important to understand why and where you're using connections.
And remember, the maximum number of connections isn't just your client connections. You still have some reserved connections. You still have, say, a surveillance tool that uses the connection. So just because you have connections on the application side and you have accurately determined the number of connections your application opens, doesn't mean they won't expire. Isn't that right ? So implement connection discipline into your app design.
We have reviewed OAUTH tokens. Service principals. Yes, I covered the basics of query plans. Hmm, file cache—that's a useful thing to pay attention to. So, this will come in handy when you analyze cold start performance. So where do you usually use it? As I said, in feature provisioning workloads or during the first call, when your application starts up and you want to understand where the cold start losses are the greatest.
Is this my database, which happens most often, or somewhere else ? So you understand all these aspects. And then remember: if it's a cache hit—that's great. This means fetching pages from either the Postgres buffer pool or our LFCs. If it's a cache miss, it's a network request to the page server. And at Lake Base, this is especially noticeable because of our architecture. So indexing, partitioning, and all those basics are important to you in Lake Base more than anywhere else.
Yes, we talked about working set misses, missing indexes, all these specific things. And the final topic of today's conversation will be two things. The first is HADR. I think you've heard talk about HADR across regions and clouds for Lake Base. This is definitely being implemented, which will increase the SLA level beyond four nines, etc. Regarding all the system tables, remember the video I showed? It was actually broadcast from the Grafana dashboard.
So, now we have the ability to use OpenTelemetry logs for Lake Base. This is a 5-minute setup where I can integrate Grafana with Lake Base, collecting all the information every 5 seconds, and effectively use all that data. And that's what many customers will do . If not Grafana , then probably Data Dog. There may be other tools, but there is a good way to start visualizing your logs and everything else to keep up with the analysis of important statistics.

`1:04:27` Monitoring Lakebase with Grafana and OpenTelemetry

So, what to look out for: We talked a lot about getting into the LFC. Keep an eye on the number of active connections, because as soon as you see the " red zone", it means something is wrong. And the LFC hit-miss ratio gives a good idea of the working set size, the CU size, and consequently the entire architecture underlying this behavior. Going back, maybe we'll get in 5 minutes, and then I'll answer questions. Multi-AZ high availability, I already talked about quorum sensing.
Then I talked about storage redundancy. It's important to understand here that we are not actually replicating the data. When it comes to Lake Base, it's not about cloning data. It's about branching out. So, think of it as something you do in an environment where you don't have to think, " Okay, how do I move all this information now?" Do I need to think about data replication? Do I need to think about storage? Here it is important to understand that data movement is completely absent.
And with cross-region and cross-cloud disaster recovery (DR), this will change, because we will need secondary regions, we will have to replicate the storage to the secondary region. All of that is fair, but it would change the semantics of how we support some of the disaster recovery capabilities that customers need, right? So that's something you have to keep in mind. A huge area of confusion is Private Link. And it works differently for Lake Base and to some extent for Zero Bus, because Lake Base and ZeroBus are high-performance or intensive services.
What do I mean? This means they will generate significantly more traffic than, say, a typical Databricks workspace API call . True? So we created something called Service Direct Private Link. This is simply a regional login for your entire Databricks account. This is an account-level construct for each region. So if you're working in, let's say, a specific region like Azure West Europe, because I'm from London and I don't really understand the regions here yet.
So, Azure West Europe, think about it. You will have only one regional login for the entire region, and you will configure one Service Direct Private Link for all your Lake Base instances. So this is not a per-instance setting. This is not a per- workspace setup like your previous Private Link frontend. This is for your entire set of workspaces in that particular region. So you set it up once and forget it. Does it work without it?
Yes, if your workspace is also configured to receive public or publicly routed traffic. If you configure your workspace as private and do not configure Service Direct Private Link, autoscaling will not work. The Data API, on the other hand, has a different input because it's 443. This is REST, right? This is HTTPS. It does not come in through Service Direct. So, your data API can use your existing Private Link interface.
So this will continue to work, but if you want the PG protocol to work with Private Link, you need to configure Service Direct Private Link. Um, in another—OTel, I was talking about that . Very, very fast connection. It took me about 5 minutes. I mean, I'm not very good at these things , but even I managed to do it in 5 minutes. So it's a very fast and useful tool for understanding, visualizing, and analyzing your logs.
And we're constantly improving it, aren't we ? We are also announcing many observability-related releases at the summit. If you saw the keynote or some of the specific speeches on this. So be sure to use OTel and PG system tables. Like PG stat statements, PG stat activity, and figure out what's working and what's not, get ahead of problems quickly and act accordingly. In another, I think we talked about the fundamental foundation, i. e. disaggregated storage and compute, and then talked a lot about SP ownership, usage patterns, OpenTelemetry , sync tables, what and when to use .
Where do you usually make mistakes? What are the pitfalls? I think I covered a lot of these aspects, but fundamentally it all comes down to how you move forward, how you build architectural solutions with Lake base and what problems you ultimately solve, right? So I'll leave that for last, and it seems there's a survey about the session that you could kindly fill out. Otherwise, I think we have enough time for me to answer your questions, if any.
I'll happily spend the next 10 minutes answering into the microphone, and then I can go outside and we can chat there. 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.