Nik [00:00:13]: Hello, hello. This is Postgres.FM. I'm Nik, as usual, PostgresAI. And as not usual, today with me is Kirk, KiraSoft. Hi, Kirk.

Kirk [00:00:26]: Hey, Nik.

Nik [00:00:27]: So Michael couldn't join today, and I asked Kirk to join me, because the topic is interesting not only for users, but also, I think there is potential to improve something in Postgres, right? And as some our listeners know, we have hacking sessions online with Andrey and Kirk, which lasts, I think, three years, right, on YouTube. (Kirk: Yep.) So, and you recently visited us already on Postgres.FM, to discuss Google Summer of Code project, right?

So the topic is let's form it in the form of question. If you performed dump restore or just restore from a backup, is it enough? (Kirk: Yeah.) Is it enough to continue working with this data which is just loaded to your Postgres? For me, obvious answer is no, and actually, you know, this podcast, we touched it multiple times in the past. But today, I just, like, I have a lot of interesting things to discuss because, with our new AI tooling we built at PostgresAI, it's called DBLab Forge.

We explored and found so many interesting things, so many nuances, which I thought, would be interesting for wider audience to discuss today. How do you find this topic? Like, what do you think about it?

Kirk [00:01:54]: Well, Michael sent me a text message and said I have to say it sounds boring. No, but it's... (Nik: Yeah.) No, this is actually a great topic.

Nik [00:02:03]: It's a... I agree with you, actually. It's a boring topic. Yeah, it should be boring.

Kirk [00:02:06]: No. No, this—

Nik [00:02:07]: But it's entertaining sometimes when you just loaded your data and have very big surprises with performance.

Kirk [00:02:15]: There you go. This is what makes it interesting. And you have the data to back some things up, like out of 28 sites, companies you've reviewed that give advice of what to do after, only eight of them cover what, that touched on this out of 28. Three do it really well. So this is gonna be good stuff for everyone to see. And the question overall, the restore finished, is your database ready, maybe isn't the right question. The real question is your database ready to be thrown into production mode? And the answer there is no.

Nik [00:02:50]: No.

Kirk [00:02:50]: All your data's restored. Your indexes are restored.

Nik [00:02:53]: Well—

Kirk [00:02:53]: If you throw it into production, crazy.

Nik [00:02:56]: Yeah, we start arguing right away. This is why I really, like, looked forward to do it with you. So my question wasn't, is your database ready? I said, "Is it enough?" (Kirk: Right.) It's much—

Kirk [00:03:11]: And it depends on your goal.

Nik [00:03:12]: Yeah, yeah. It depends on your goal.

Kirk [00:03:13]: If all you are doing is validating your backup, it's probably enough.

Nik [00:03:18]: No.

Kirk [00:03:18]: If you're making sure you have enough space—

Nik [00:03:19]: No, this is another topic. This is another topic.

Kirk [00:03:21]: Oh, no, you're right. Look at that.

Nik [00:03:23]: For validation of backup, it's also not enough, and let's talk about it another time, how— (Kirk: Right.) properly validate dumps. And another thing I must admit, dumps are logical backups. I will always argue with statement, dumps are backups. Dumps are not backups. They are logical backups. We had a separate episode. It is just maybe, like, a preference of terminology. It... Of course, some people say, like, have their own opinions. But if we return to the central topic, like performance, my answer obvious, like, no, it's not enough, right? (Kirk: Yeah.)

For OLTP, right? Right?

Kirk [00:04:04]: Well, actually, it doesn't even matter for what. Let's talk about, we can talk about why. And also, just for the audience getting used to me, my motto is simple: be wrong out loud, you'll learn faster. So I love getting corrected, okay? (Nik: Right.) I... You know, and I just said something, and I got corrected, and boom, right on. So let's turn this topic around a little bit and say, well, what could be missing? All my data's there, all my indexes are there, and Postgres recently added that they'll bring over statistics if you tell it to.

Nik [00:04:39]: It's,

Kirk [00:04:39]: That's fabulous

Nik [00:04:39]: It happened in Postgres 18, right? Yeah.

Kirk [00:04:42]: Right. That's fabulous. So that must be everything. (Nik: Right.) And then you look under the hood a little bit and you find— (Nik: Yeah.) no, you need things like the visibility map. Oh, but when does that get written? Oh, when you do— (Nik: Yeah.) your first query on that table. Oh, you mean I can do a read—

Nik [00:04:59]: Ah

Kirk [00:04:59]: And—

Nik [00:05:00]: Hold on. Let, let's untangle this. You started to... Like, okay, you are very right except one thing. Visibility map is not written at first read until Postgres 19. Let's start untangling this and like, presenting this maybe in a more structured way because it's really complex topic. I chose a simple topic for our first podcast together, Like, when I launched our DBLab Forge for a couple of nights and I, I'm amazed about how many details it brought, you know? So let's start maybe thinking about what we are missing after a simple restore. First of all, maybe, like, what dump already includes by default, forgetting about Postgres 18 edition.

Dump includes some set commands usually, right? Then DDL to create tables and everything, but not indexes. Indexes, is not... It's not wise to create indexes in the beginning because they will slow down data load. And then it includes a bunch of COPY commands to load data to those tables, and then, indexes and constraints like foreign keys, unique keys and so on. First thing is quite IO-intensive usually, so it loads disk a lot. But indexes are both IO but a lot of also CPU intensive, so you will see CPUs involved a lot. And like, there is pg_dump, there is pg_restore. Looks simple, but something is missing. And you started jumping to visibility map.

That is definitely missing. But before that, statistics, right? Statistics is missing, visibility map is missing, and also hint bits are missing. Unless you... Like, this is nuance, actually. Hint bits are missing only if you don't create indexes, because indexes will read tuples. Index creation will read tuples, and it should build, hint bits. But what are... Like, what kind of statistics? What are... What is visibility map, and what is a hint bit? What are hint bits, right? Let's maybe walk through all of them, right?

So statistics— (Kirk: Okay.) statistics is maybe, like, it's, like, easier to understand, like, because we all know Postgres planner is statistics-based. Without statistics, it might choose wrong, wrong, plans. And usually, what I usually see, like, manually doing something, or with agents actually, right? You just load data. You have indexes, all good. You think you're good to go, but you see the plan, you check the plan, EXPLAIN ANALYZE BUFFERS, and you see Bitmap index scan, bitmap heap scan, used. A pair of bitmap scans is used instead of index scan which you expected, or even worse, you expected index only scan. For example, select count, right?

But you see bitmap scan is used, which is quite slower, That raises questions why, and obvious answer why, like, you don't have statistics.

Kirk [00:08:19]: So let's— (Nik: Yeah.) let's define a few things, okay? So statistics, the easiest one for me, to explain this to somebody who's new is if you have a primary key index, the database will automatically keep track of the most minimal value and the highest value that's currently in the system, and this allows a very fast way. So if you select something that's where, where ID is less than the minimum value, it knows there's no rows, okay? The optimizer can just look at that. Same thing if you try going above that ID, the maximum ID value. So those are little tricks that you can play. Now, after that, you get into the more complex things like it's a very dense packing, which means if you're 10% below the highest number on an ID column, it can estimate the number of rows it's gonna bump into.

Now, it's an estimate, but it understands this based on collecting some of these statistics. That versus the visibility map, and the visibility map issue is, like, funny because after a pg_restore, everything's visible. All records are visible.

Nik [00:09:30]: Right. They don't know about—

Kirk [00:09:31]: But the map still has to be written—

Nik [00:09:33]: Well—

Kirk [00:09:33]: So for the system to use it. Go ahead.

Nik [00:09:36]: Yeah, so... Oh, how to explain this better? So statistics is for planner. Visibility map is also for planner as well, and for executor as well because indexes don't have version data. So every time we have index scan, we need to inspect heap. And index only scan, despite the name, can also inspect heap if it doesn't know anything about this tuple, status. Is it live or dead? We don't know from index itself, right?

So visibility map, it's a map, which stores two bits for every page. One is all frozen and other is all visible, which means this page contains tuples which are all visible for all transactions, right? And you are right. After restore, we have a lot of new pages. We probably have some tuples which went to pages with other tuples, you know, like, not from our loading transaction. It might happen, but most of the pages will be, if it's bulk load a lot of data. Like, or maybe the table is just created. We talk about fresh dump restore, right? Table is just created, so all the, those pages are, are, are ours, and we just finished this work.

Everything should be visible to all, but it's like by default it's not, because this is just how Postgres works. Those pages are just created and other sessions not know. They must inspect. The visibility map is not created yet. That's a problem, right? So, and also there are hint bits, the third piece, because we talked about this multiple times in this, on this podcast, that you can select, columns xmin, xmax from any table. It's like date of birth and date of death.

And those columns are always present. They contain transaction ID of transaction which created this tuple or attempted to delete this tuple, attempted to create this tuple as well. But like, I mean, because insert or copy can be canceled as well, right? And a lot of the tuples will be produced by a failed attempt. So those columns are like, are hidden columns. And the interesting piece is that, for example, if we delete some tuple It just marks xmin, puts it to the row.

This row goes to WAL and to the page itself. And then later, autovacuum needs to come and actually delete this tuple if it's actually dead. But this xmin value originally, when another session tries to read it, originally it doesn't know if this xmin, belongs to a transaction which succeeded or failed. It doesn't know. So it must go to, clog, transaction log, to inspect the status of that transaction. It takes a little bit of extra, there is overhead of this operation.

And this is, like, not super efficient to inspect for every tuple to inspect transaction ID status for all those transactions which are present in xmin, xmax. That's why there is a so-called, there is a mechanism, hint bits, which basically in the tuple header, it just caches the status of those transactions, xmin and then xmax, right? So because sometimes you have a live tuple, you see xmax is filled with some number, but it's just that transaction was roll— like, got rolled back, right, at the end. Means the tuple is, tuple is still alive.

Of course, some transactions also complicate things additionally, right? So just to avoid this real time, exploration of transaction IDs, hint bits cache it right in the header of the tuple, and it happens when? At the first read, actually, after load. I remember this is old, scary story. It's good to have it, before Halloween, right? Like— oh, we see a lot of writes on replicas, on standbys, and those writes are not coming from temporary file generation. It's just actually Postgres decided to update some data. What, what's happening? What's happening, it just updates those hint bits because it's fresh. Like, we have a lot of fresh tuples. Maybe we had copy, bulk load, or pg, pg_dump, pg_restore used.

And then replicas don't know. Like, they don't have, those hint bits, and they need to take care of it by themselves, each replica actually, which is crazy. But if you enable wal_log_hints or you have data checksums enabled, which is default if you create cluster recently, you probably have data checksums enabled, then it's already done on the primary and propagated to all standbys, so everything should be fine. Hint bits are there. There are some nuances there still. Some writes might happen, but not as, as before. So the interesting part about this, like after dump restore, we don't have statistics, we don't have visibility maps, and we don't have hint bits, which is, I mean, like—

Kirk [00:15:00]: Well,

Nik [00:15:00]: Very interesting situation, right?

Kirk [00:15:03]: So to be clear, we have all the data we need for our queries to give us the correct answer. The problem is it's not optimal, and now we're... this is we're spilling into you want this to be optimal. And I kind of want to cut to the chase a little bit and say the best thing you can do right after your restore is a VACUUM ANALYZE. If you VACUUM ANALYZE all your tables— (Nik: Yeah.) it'll create all of these things. (Nik: Yeah.) And then everything works out. And that begs one question: Why doesn't the restore do that automatically?

Nik [00:15:39]: Which is a big question. I... here I'm, I must mention that we have a robot here which promised to follow our discussion and warn us, not to exceed, 45, 50 minutes. This is our goal for episodes. Because I know Kirk and myself, we can easily spend hours discussing all the nuances, especially if, as decided now, we will bring a little bit of hacking, pieces to the discussion because we don't... Like, we have a two-sided goal.

One, like, we want, to understand how to operate Postgres these days. And I agree with you. The... if, one thing our listeners should remember is just never forget VACUUM ANALYZE after loading bulk load, for example, restoring from dumps. And we have another goal, slightly touch, possible improvements in Postgres itself, maybe for our Wednesday sessions, which we irregularly have, right? Irregularly. I cannot pronounce it. (Kirk: Right.) So that's interesting. Yeah, let's talk about this. Like, why those tools? Like, why isn't automated by pg_restore?

Why it's not present, for example, in form of explicit VACUUM ANALYZE lines inside dumps? Why pg_restore doesn't have hyphen, hyphen, vacuum, hyphen, analyze option? Well, this is one hacking question, right? Like, why is this not implemented in Postgres? And another operational, like DBRE question, tooling question is our system is explore 28 documentation pages. Most popular platforms managed by Postgres platforms, 28. Only 3 of them mention vacuum. I think only 5 or 6 of them mention analyze.

Most of them, including RDS, Neon, Cloud SQL, they don't mention neither. No analyze, no vacuum. Which means, like I'm very curious how many folks, humans, and agents bumped into this problem and we are, like— amazed how performance is better right after restore, right?

Kirk [00:18:02]: And we hit this—

Nik [00:18:03]: Let—

Kirk [00:18:04]: In our production.

Nik [00:18:05]: Yeah. Let me mention three more names, because Supabase, Dalibo, and Aiven, only three documentation pieces, we explored, mentioned VACUUM ANALYZE. That was great. So three winners in this small contest. Right. Well, actually, before we talk into these two pieces, we need also to raise the question, maybe, maybe we are wrong. Maybe, you know, there is autovacuum. It will come and take care of all these pieces, right? Maybe. And as, as I said, hint bits, if you, if your dump, has index creation for your table, hint bits are solved, so only two topics left, right? So we need statistics and we need visibility maps for good performance. Without visibility maps, all index only scans will be having huge heap fetches, which is bad.

But what about autovacuum? It will come soon, like just wait a few seconds, it will catch up, and you will have both visibility maps, which is job for vacuum, and statistics, which is a job for analyze, but autovacuum covers both of them. Isn't it so?

Kirk [00:19:16]: So that's a great question. Here's... There's a few challenges, though. First off, does an autovacuum have a limited window of time it runs? So if one of your tables is exceedingly large, will autovacuum actually go through all of it to generate the whole visibility map when it hits that table, all right? That's the first question I have. The other thing is maybe, okay, autovacuum's gotta catch up. How many tables did you just restore, and how much time is that? So now you get the question of, I finished my restore, can I use my database? Well, it depends. Do you have 40 autovacuums back there running trying to catch up? (Nik: Yeah, yeah.)

Did you let all the autovacuums settle down?

Nik [00:19:57]: Most, most databases I observe, even huge ones which come to us, they usually come with three workers, even if they have, like, 96 cores. The, people, are afraid of raising number of workers. And you are right, autovacuum is throttled. So it takes a lot of time for it to process the tables. Plus also it can be blocked, it can be busy with other stuff, right? Plus also it can be a problem, with partition tables because autovacuum doesn't take care.

vacuumdb doesn't take care, and I think autovacuum doesn't take care of root tables in partition, parent tables of partition tables, which is improved in Postgres 19, but not fully. So which means that if you have a partition table, if, and you forgot VACUUM ANALYZE manually, This is a big operational gap Postgres right now has, and obviously, like, it's not enough, and I don't want to wait sometimes, right? Another point is that I just recovered from dump. I'm checking some query right away, and I see bad performance because autovacuum is asynchronous by nature. So yeah, and it's like opportunistic, approach, asynchronous, and in many cases it's not enough.

Thus, we should have VACUUM ANALYZE in dumps or pg_restore.

Kirk [00:21:21]: Absolutely. And I think it, for me, I think it should be a restore option, okay? Just because the person restoring it knows what they want it to do and why that restore exists. So for me, it kind of makes more sense in the restore than telling the dump. Because also if it's only in the dump, then you can't do it in the restore. And if you're restoring something else that didn't do that, it doesn't give control to the person who is what I consider the last user, the last decision maker. If it's in the restore, the last decision maker has that opportunity. (Nik: Right.) And that's probably the person who needs it the most.

Nik [00:22:00]: Should it be on by default? Imagine it existed. Should it be on or no?

Kirk [00:22:07]: Ooh, that's always a tough one. I would say the hackers would argue no, because it would create a lot more work and demand that never existed before and make things slower, and that would be their big push. Personally, I think it should be on by default. You know, it's such an important piece to have all of the rest of that data and the visibility maps and everything else.

Not having it just seems like it's lacking. (Nik: Yeah.) It's begging for problems.

Nik [00:22:38]: Right. I have another tough question. What if we use a single transaction option, during pg_restore? Vacuum cannot be included inside a transaction block.

Kirk [00:22:50]: Ah, yeah. (Nik: Yeah.) But I'll be honest, the vacuum concept, even if you did the single transaction for your entire restore, once that commits, that's when you would vacuum, only if it succeeded without error.

Nik [00:23:06]: After. (Kirk: Right.) Yeah. Yeah, but this makes—

Kirk [00:23:08]: Yeah, the vacuum—

Nik [00:23:09]: Well—

Kirk [00:23:09]: Wouldn't be part of the commit.

Nik [00:23:10]: Right. I think, this option could just be, like, incompatible with single transaction. If you chose single transaction, pg_restore could fail saying, like, you chose two incompatible options. That's it. Why not? And we know, like, by default, pg_restore is not, ACID basically. It's not a single transaction. It can fail and continue and so on. That's all, like, there is also a stop on error, option, but it's different.

Kirk [00:23:35]: Well—

Nik [00:23:35]: But yeah.

Kirk [00:23:36]: Yeah, and the minute you use -j option in pg_restore, you introduce a problem where the creation order— All right? Can create a problem, because you can be in the middle of creating the four dependencies a materialized view needs, for example, and you're running in five threads, and the four are running the restore, and now it looks and says, "Oh, the next thing to do is create the materialized view," but none of the four tables you need have actually finished being created.

So then your materialized view at the end blows up because it doesn't have that blocking mechanism of knowing, hey, I gotta wait till these things finish before I go. So it's, pg_restore's not perfect, okay? And running into some of these things was very interesting for me the first time.

Nik [00:24:25]: Right. I also think, hackers might argue that there is already another CLI tool, vacuumdb, which, supports vacuuming and analyzing, and it can do... Like, by default, it's vacuuming, but you can say hyphen, hyphen analyze, so it will do both. And also you can say analyze only, it will do analyze only. And they can say, "Okay, we have already CLI tool and so on." But the problem is for me right now in a agentic, era, is that having these, like, in two separate tools requires, like, memory, or you need to memorize. And we see how platforms,

Kirk [00:25:04]: That's the takeaway (Nik: Yeah.) Out of 28 platforms, only eight total mentioned either— (Nik: Yeah.) analyze, gather statistics— (Nik: Yeah.) whatever. (Nik: Yeah.) So 20 of them clearly never said anything. (Nik: Right.) So, and this was exactly our experience.

Nik [00:25:18]: And vacuum only in three. Vacuum only in three.

Kirk [00:25:20]: And vacuum only in three.

Nik [00:25:21]: 25 lack it.

Kirk [00:25:23]: And to me, it made no sense I had to vacuum them— (Nik: Mm.) because they were just freshly restored.

Nik [00:25:29]: Yeah, it's counterintuitive.

Kirk [00:25:31]: It's a clean copy. (Nik: Yeah.) Like, the fact that I had to vacuum evaded my thought process.

Nik [00:25:37]: If you don't know about visibility maps, I agree. I remember, like, it took time. I, for my mind, it took years to settle and have, like, muscle memory to type a VACUUM ANALYZE. And I must admit, I still sometimes, forget about it. And I know agents, some agents will do forget about it. So I think, like, I, I'm feeling we are preparing already to raise this again in hackers. And I think we must, take into account agentic behavior, because if it's separate CLI tools, it's easy to forget these things. If there is an option in CLI, clear option in pg_restore, it's easy to, oh, there is a such option.

Even if it's not default, it would be great to have already, right? Because it will be, like, easy to, easy to use, right? Now you vacuumdb, oh, something. However, as we know, some dumps are restored through psql.

Kirk [00:26:30]: Yes. Absolutely. And guess what? If you're restoring those, they're, some of that's causing some of these things to actually happen— (Nik: Right.) because the transactions are different. (Nik: Right.) So it's— (Nik: Yeah.) it's a little different. But the flip side, too, is, and this is a key point, you mentioned using agents. (Nik: Right.) If you are doing agents and you're actually trying to capture statistics or speed benchmarking, and they forget to do this— (Nik: Yeah.) it totally blows up your benchmarking concepts.

Nik [00:27:02]: Yeah. You know what would help? Fully sitting inside an agentic use context. So I would love to have VACUUM ANALYZE option for pg_restore, even if it's not default. Although I, I'm, I personally would, like, argue to making, for making it default. And additionally, in psql, if we restore from dump, write in dumps, pg_dump, if it included just line, like raise notice— (Kirk: Yeah.) or something like this for agent. Raise, raise info, raise, raise something. Raise warning at least if you don't want, notice—

Kirk [00:27:42]: Actually, but if you... Yeah

Nik [00:27:44]: Just—

Kirk [00:27:44]: If you create the dump—

Nik [00:27:44]: Don't forget VACUUM ANALYZE

Kirk [00:27:46]: With the option—

Nik [00:27:46]: Just one line

Kirk [00:27:46]: Add the line.

Nik [00:27:48]: That's it. Imagine, like, dump, it just includes this line. It's easy, cheap. (Kirk: Yeah.) And agents would, restoring, it would notice it. It would be in context, and that's it, right?

Kirk [00:28:01]: Well, and not only that, here's what's cool about Postgres. I give it points. If I'm logged into a specific database and I type VACUUM ANALYZE semicolon, it understands I wanna VACUUM ANALYZE that entire database. (Nik: Right.) That one database that I'm logged into, it'll do every table in there.

Nik [00:28:21]: In single process—

Kirk [00:28:22]: That's,

Nik [00:28:23]: Single threaded

Kirk [00:28:23]: I don't even have to load the table

Nik [00:28:23]: Single threaded.

Kirk [00:28:25]: Is that?

Nik [00:28:25]: A separate,

Kirk [00:28:26]: Yeah, single-threaded, of course (Nik: Yeah.) Yeah

Nik [00:28:27]: I wish—

Kirk [00:28:27]: Which is horrible

Nik [00:28:28]: I wish would be control over, parallelization level, right, at SQL level, but this is another idea for hacking. You know, like— (Kirk: Right.) I now we can use vacuumdb, but until Postgres 19, vacuumdb doesn't take care of parent, partition tables, of parents of partition tables as we discussed, and it's a headache actually, right?

Kirk [00:28:51]: Well, and again, I wanna point out that when we moved to Postgres for the first time, we do daily restores in multiple systems.

Nik [00:28:58]: From Oracle

Kirk [00:28:58]: Just from—

Nik [00:28:58]: You moved from Oracle.

Kirk [00:28:59]: From... Well, well, yeah, we moved over from Oracle to Postgres, and we have an environment set up that auto restore. We have snapshot pg_dumps. We restore these into multiple systems used by different people for different reasons, okay? And the challenge for us was the performance went to shit. For, oh, forgive the French, but on these systems, it was horrible, and it literally took me a while to figure out that it was, I was missing the, you know, the stuff that goes along with it, not just statistics. I was missing the visibility maps. And the finding out vacuum analyzed fixed the problem. That was great. And then the next thing we ran into was if you issue VACUUM ANALYZE semicolon, it's single-threaded.

Nik [00:29:48]: Yeah. Exactly.

Kirk [00:29:49]: So five large tables just takes forever. (Nik: Yeah, yeah.) Yeah. So, and then you end up doing vacuumdb with multiple threads.

Nik [00:29:58]: Which has issues with partition tables.

Kirk [00:30:00]: Right. Luckily, we weren't using partitions— (Nik: Okay.) so we were okay. (Nik: Okay. Okay.) Yeah, yeah, yeah. But—

Nik [00:30:05]: But

Kirk [00:30:05]: It fixed our problems. But I— (Nik: Yeah.) blindsided by it, and apparently so are 80% of the people out there that even talk about this.

Nik [00:30:13]: Yeah. I remember actually a couple of years ago when I was writing every day about Postgres, how, like how to Postgres Marathon it was called. I had recipes how to dump, restore, like at the highest speed, like utilizing all resources, bumping, to either CPU or disk, saturating them, right? If you want to just fresh machine, you want dump, restore as fast as possible. And vacuumdb is of course there, but with a note about partition tables. And it was, I think Laurenz Albe who fixed it for, fixed it for Postgres 19, so looking forward for upcoming release in November, hopefully, right?

And yeah, (Kirk: Nice.) so yeah, so obviously there are some ideas in terms of hacking here, but again, how do you find the, this idea of simple raise info or something which would go to stderr, not stdout, just to signal to humans or agents, like don't forget that, right? Which, like obvious, right? Just include it in dumps. (Kirk: Right.) Yeah. Yeah,

Kirk [00:31:24]: Right. Interesting

Nik [00:31:24]: Let's maybe accumulate some ideas and raise this discussion. I know it was discussed in 2002, 2014. I just... Our research came out and it this is not a new topic, but it wasn't, properly revisited. I think it's time to revisit in agentic context, right? Yeah.

Kirk [00:31:44]: Exactly. And as these, because the, as the agents are doing these restores, they this is a nuanced issue. (Nik: Right.) And they don't see it. (Nik: Right.) And it's not popular. (Nik: Right.) So.

Nik [00:31:55]: Let's slightly maybe touch, visibility maps and hint bits, because there are changes. Oh, actually, let's touch statistics additionally, because as you mentioned— (Kirk: Yeah.) Postgres 18, implemented a statistics dump restore, but it's not on by default, right? So you need, an option. An option, by the way, it was called in a weird way for pg_dump. All such options start with hyphen, hyphen, with, with. But this— (Kirk: Oh.) is without. It's just statistics.

Just hyphen, hyphen, statistics. Okay. And it's not on by default, which means that you need to set it. It has some limitations, so extended statistics won't be dumped. But good news for partition tables, partition tables, root tables, for them, statistics will be dumped, and that's great. And then we restore it and kind of it's fine, right? However, well, extended statistics is missing. And there is an interesting piece, our system DBLab Forge found.

So with statistics, for each relation, each table, a flag will be... There is a, like a relpages flag, right? There is also— (Kirk: Right.) it's not a flag, it's a number. It's an estimate how many pages, how many, eight kilobyte pages this relation has for a planner to take into account. And there is also another estimate which is called a relallvisible, which means how many pages are marked all visible in visibility maps.

So the thing is that this mechanism in Postgres 18 dumps that number, and our restore will catch it and restore it. And the planner will think that, okay, we have like, say like 100,000 pages, and all of them are visible, it will say. For example, ideal case, or half of them are, are visible. But visibility maps are not yet built. So the planner will choose, for example, index only scan, and will get the worst possible heap fetches number. It will inspect every heap, page, like behaving like index scan, which is very unexpected, and it's like skewed result for a planner, right?

So I actually talked about this to Andrey Borodin this morning, which is I usually go to Andrey Borodin, to talk about planner behavior. And he said, "Well, yes, somebody needs to implement, like to split and distinguish different kinds of statistics," right? And then I thought, this is terrible. This is just a bug. This is a mistake they did. I found a discussion in Hackers where it was decided to keep this number and in like, to preserve it in the dumps, but I thought like, why? It's obviously a mistake. You have all visible number, but you don't have visibility map.

You fool yourself basically, right? You fool your own planner. But then I realized that this feature was not developed for dumping and restoring statistics for regular dumps, logical, logical backups, right? It was developed specifically for pg_upgrade. (Kirk: Right.) Where visibility map is preserved.

Kirk [00:35:26]: Right. Yeah.

Nik [00:35:27]: Yeah, that's, that's what... Like this was insight which was provoked by our forge system. I just love how exploring Postgres these days looks like. You throw some questions, you have experiments. Our forge, system, it conducts experiments on dedicated machines like Explore the different kinds of Postgres versions and also the master branch, everything. And then you actually... I always have, after first wave, I always have even more, more questions. (Kirk: Right.)

And I just love how, like, I can throw as many questions as possible, because I always had those questions, but it took a lot of time. I didn't have time. Now I do. I just throw it and return in a few hours. I have more answers and more questions until I'm fully satisfied. I cannot say I'm fully satisfied on this topic. It's enough for our discussion, but I think this is important operational topic, right? It basically concerns every Postgres setup, because dump restore— (Kirk: Yeah.) are important tools, right? And also copy. You just copy data from somewhere, and you do need the statistics and visibility map. So, okay, that's why actually it's disabled by default.

Don't use it. Actually, my recommendation, don't use dump restore of statistics except for pg_upgrade. I mean—

Kirk [00:36:53]: I completely agree with that.

Nik [00:36:54]: Yeah. Well, it should—

Kirk [00:36:56]: In part—

Nik [00:36:57]: It should, yeah

Kirk [00:36:57]: Partially because of the number you just pointed out, but also keep in mind that after you did a pg_restore, you got fresh, raw data all visible, okay? Like, this just came in, and it, it's... it filled the blocks, and it loaded this stuff. Everything should feel really clean at this point, okay? Even the toast tables that you started out empty, right? So as it created every single one of these rows, and it used a pretty optimized way to do—

Nik [00:37:29]: Well—

Kirk [00:37:30]: It grew it nicely. (Nik: Yeah.) But that is completely different than whatever you were seeing on your old production system.

Nik [00:37:37]: Well, this is an interesting topic. So you say, like, for copy itself should take care of it, not a separate VACUUM ANALYZE at the end of restore operation, but copy itself could take care of it, right?

Kirk [00:37:49]: Well, this mode of copy, yes.

Nik [00:37:52]: So there is such mode.

Kirk [00:37:53]: I wouldn't want copy if you just added records by using copy.

Nik [00:37:57]: Yeah. Right.

Kirk [00:37:57]: I wouldn't want it to do this. But the pg_restore construct, right? The... If you're... And they do it in a single transaction, by the way. I dug into the code. (Nik: Yeah.) They truncate the table. They begin a transaction, they truncate the table, and then they do the copy data— (Nik: Right.) on the restore, on the pg_restore side, so that if it fails, it rolls back to empty— (Nik: Right. Right.) which is actually a faster fail.

Nik [00:38:22]: So my glasses—

Kirk [00:38:22]: And so it's very happy

Nik [00:38:23]: Also suggests something. There is COPY FREEZE. I actually learned... I forgot about it. I learned about it some long, like, time ago, because it's an old thing, COPY FREEZE. So copy— (Kirk: Yeah.) can have freeze option, and it'll run vacuum freeze of building visibility map, for both bits. As I mentioned, there are two bits for every page. All visible mean, means that all tuples in this page are visible to all transactions, and all frozen, which means that all transaction IDs, xmin, xmax, xmin, right? They are in the past. Even if they might look like from the future, they are in the past. This is freezing. Important job because... for vacuum because we have four-byte transaction IDs.

So vacuum from time to time needs to process tuples and pages and freeze them. Like, meaning that don't look at this number in terms of deciding is it... Like, it looks like big one, but it's from, not from future. It's from the past. It's to avoid transaction ID wraparound. (Kirk: Right. Right.) copy freeze—

Kirk [00:39:29]: So that's interesting, though. (Nik: Yeah.) It's not implemented in pg_restore—

Nik [00:39:33]: Well—

Kirk [00:39:33]: To have it auto use— (Nik: Right.) vacuum freeze.

Nik [00:39:36]: I think if you're—

Kirk [00:39:36]: And, or the COPY FREEZE, and it could.

Nik [00:39:39]: Well, it, maybe it could be implemented in pg_dump because it's a copy option. So pg_dump could generate— (Kirk: Right.) COPY FREEZE, or maybe you are right. Maybe we could just, substitute COPY commands with COPY FREEZE. There are limitations for COPY FREEZE. It should be, like, table should be created right here in this transaction, or truncate should happen this transaction. So I think, yeah, COPY FREEZE could be used if it's pg_restore with single transaction option maybe.

Kirk [00:40:07]: Not even with your single transaction. (Nik: Yeah.) Here's the deal. Because it allows for the, according to you, the truncate version, and we know pg_restore does truncate first in the same transaction, then the copy, I think it, I think you could get it to fly.

Nik [00:40:23]: Yeah. As I said, I forgot about this option. Relearned it this morning. So I cannot say if I see improvements. I feel there should be improvements for modern Postgres to provide more options maybe to reconsider defaults, but this is what you wanted. Like, to right after restore, we already in very, like, clean state, all visible, all frozen. We are good. Performance is great.

Kirk [00:40:48]: Oh, yeah, yeah, yeah. So maybe it's COPY FREEZE space VM for visibility map because again, everything's visible.

Nik [00:40:58]: Yeah, but... And also don't forget statistics.

Kirk [00:41:01]: Well— (Nik: Yeah, yeah.) yeah. Well, yeah, yeah. That would have to come after.

Nik [00:41:04]: That asynchronous.

Kirk [00:41:05]: And what's cool is then you don't actually need the vacuum portion of the VACUUM ANALYZE. You really only need the analyze portion.

Nik [00:41:13]: Right, but statistics actually for many index only scans, it's not super, impor— like, it's important, but it's not that important as having visibility maps, because this is a performance killer for index only scans. (Kirk: Yeah.) Slow count, slow, like, slow index only scans basically, right? When, when you need just— (Kirk: Right. Right.) find—

Kirk [00:41:32]: And since—

Nik [00:41:33]: You have some join. Yeah.

Kirk [00:41:35]: And since some of that gets created when the index gets created, right? 'Cause it does some of the analysis while it's doing the index, then—

Nik [00:41:43]: When it's doing index, index... Well, yeah. It, as we've said, it's, building, hint bits This—

Kirk [00:41:52]: Yeah. Okay

Nik [00:41:52]: Tuple headers. It, it's not building, statistics

Kirk [00:41:55]: Ah, but now additional statistics.

Nik [00:41:56]: Right. And in Postgres 19, there is addition. By the way, interesting thing is that hint bits, if you don't have data checksums and wal_log_hints enabled, it's old state. I hope nobody... Like, I hope everyone has data checksums enabled these days. It's not so, I know it's not so, but still, modern states should be like data checksums aren't enabled, so you don't think about that. But as I said, in the past, we had situations when every standby needs to build, mark those hint bits for all tuples, separately.

Agent [00:42:29]: Excuse me, Kirk and Nik. We are at 45 minutes. Time to start wrapping up with your closing takeaway.

Nik [00:42:38]: Thank you, agent. Every standby should build, needs to build, separately, and causing writes. It's unexpected sometimes, and select is slow. And also, if you do it now, if you don't have indexes, indexes who are, will, will revisit tuples, read them, and cause hint bits to be created. Pages for heap are dirted, and go, they go to WAL, they flush. This is modern state, right? But if you don't have indexes and you for example, read from the table with sequential scan, first read is terribly slow because of those if in hint bits being built, I mean, this is caching, so first it's very slow. Subsequent, sequential scans will be fast.

And in Postgres 19, there is optimization. Optimization says that not only hint bits can be created by select, by reading at read time, but also visibility maps. (Kirk: Oh.) Yes.

Kirk [00:43:44]: But that's a lot of writing to do a query.

Nik [00:43:47]: It's not a lot of writing. It's just one bit for one page. (Kirk: Right.) Eight kilobyte page, so roughly like if you have eight gigabytes, okay, it's a million bits. But that's not a lot, very roughly. The limitation, unlike, hint bits may happen on standby. May happen only on the primary, right? Postgres 19 feature. And I was thinking about this. Is it helpful? Is it... Like, in our very context, can we say we need less this need of VACUUM ANALYZE after restore or, or no? Maybe yes, but maybe no. The problem is it, it's only allowed on the primary. So imagine you have something you loaded, but your reads are on read replicas, on standbys.

They won't do it, right? (Kirk: Mm.) So it's some, some improvement, but it's like limited. And also it will make your first read slower, like I agree. It's maybe not that much slower as hint bits, but still slower. It will, cause additional writes. Yeah. And index only scan first time will be slow still, even if it's on the primary.

Kirk [00:45:02]: Well, that's... Right. All right. Good. So let's work on wrapping this up. I think the key takeaways are, first thing I learned, by the way, only turn on data checksums if your data is important to you. Okay?

Nik [00:45:17]: I can... Yeah

Kirk [00:45:20]: If you honestly don't care about database corruption—

Nik [00:45:24]: Fsync off

Kirk [00:45:24]: That got out there and nobody noticed for years—

Nik [00:45:27]: Fsync off. Fsync off. Unlock tables. No WAL.

Kirk [00:45:30]: Ah. Yeah. There you go. Yes. Yes. Only, only for unimportant, only for unimportant databases should you, should you turn it off. So otherwise it should be on. And the,

Nik [00:45:42]: Means always should be on. (Kirk: Yes.) Right? (Kirk: Absolutely.) Because— my AI told me that some other AIs might be listening to our materials— (Kirk: Uh-huh.) including even podcasts. (Kirk: Right.) And I'm not sure if they understand humor.

Kirk [00:45:57]: Or sarcasm. (Nik: Right.) Right. (Nik: Right.) Right.

Nik [00:45:59]: So let's—

Kirk [00:46:00]: Yeah, yeah. That could still be sarcasm

Nik [00:46:01]: Clarify. Data checksums should be enabled.

Kirk [00:46:04]: Yes. Always. Always. Because you will get data corruption without them, and worse, you won't notice it until it gets bad. Okay? So now backing into our side of the conversation here. Everybody should know that when you do a pg_restore, that there's a, there's a need to do some kind of VACUUM ANALYZE to get everything brought up to date so that your system runs the way it should run once you turn it on and enable it. And that, first off, walk away with that. That's your key takeaway. Second takeaway I think we have is we have an idea to revisit this in the hackers and maybe add a, an option to pg_restore to do a VACUUM ANALYZE so that it does it automatically, and the agents start doing it automatically too.

And then we can put this to rest because we're gonna start up in a known state, and we can then do tests. (Nik: Mm-hmm.) So this is big. And then third, we had some secondary and tertiary ideas of maybe we do copy, Inside of the pg_restore, we can look to say, "Hey, can we do the version of copy that gets us, so that it includes... I'm sorry, I forgot the word. COPY FREEZE?

Nik [00:47:18]: COPY FREEZE. Yeah.

Kirk [00:47:19]: And potentially, you know, see if there's a way that, hey, maybe we can augment this so it writes a visibility map at the end of restoring the table. That would eliminate the vacuum portion of the VACUUM ANALYZE that we do at the end, and then we could just do an analyze on the database.

Nik [00:47:37]: Right. I see what you're saying.

Kirk [00:47:37]: And then again, vacuumdb is the better choice because the -j option allows you to run multiple concurrently.

Nik [00:47:44]: And you need to think about partition tables if you use vacuumdb before Postgres 19.

Kirk [00:47:50]: Yes. Yes. And that's... And let's add that too. Let's look at correcting vacuumdb so that it actually— (Nik: Right.) handles— (Nik: Right.) the partition tables the correct way.

Nik [00:48:01]: Well, it's already done by Laurenz Albe for Postgres 19. Yeah.

Kirk [00:48:05]: Oh, for vacuumdb? (Nik: Yeah.) Not just— (Nik: Yeah.) Okay. Perfect.

Nik [00:48:08]: vacuumdb analyze only and analyze option, they take care of parent tables, yeah, in 19.

Kirk [00:48:14]: Perfect.

Nik [00:48:14]: But before 19-

Kirk [00:48:15]: 19

Nik [00:48:15]: It's still a problem. Yeah.

Kirk [00:48:17]: So I'd like to give a hat tip to Laurenz. He was one of the first people to help train our team and work with us from CYBERTEC. He's a great guy. I'm glad to see him in the community, so.

Nik [00:48:29]: Yeah. That's great. One thing you forgot, I still think in hacking, hacking, conclusion, I think pg_dump could have just a single notice or warning at the end.

Kirk [00:48:43]: Yeah. Yeah, no, thank you for catching that.

Nik [00:48:45]: Reminder. (Kirk: Yeah.) Reminder.

Kirk [00:48:46]: pg_dump— should output something that at the end of the pg_restore shows up on the screen via the error, you know, standard error, that actually reminds you that, hey, if you didn't run this with, you know, VACUUM ANALYZE or whatever switch that was, you're missing this, and specifically for the SQL version— (Nik: Right.) of the restore.

Nik [00:49:08]: Right. It's—

Kirk [00:49:08]: Where it will—

Nik [00:49:09]: Not purely Linux way, because Linux way is just keep to be silent and don't talk too much, right? But for agents— (Kirk: Right.) I think it would be helpful. And finally, I think this is great summary. Thank you so much. I... Finally, I think we should say platforms should revisit their docs, right? Docs.

Kirk [00:49:27]: Platforms should revisit their docs.

Nik [00:49:29]: Docs. Yeah. (Kirk: Yeah. Yeah.) That's—

Kirk [00:49:33]: Kudos to the, kudos to the handful, like you mentioned. But the rest of you guys who are documenting this stuff—

Nik [00:49:39]: Including—

Kirk [00:49:40]: This should at least be raised as an issue.

Nik [00:49:42]: Yeah. RDS and Cloud SQL, which is a quite big, Postgres managed platforms, they don't have it at all. Like, they don't mention analyze and vacuum. Yeah. Okay. (Kirk: Right.) Finally, I must admit once again how I enjoy working, exploring Postgres these days with our new tooling we have. Which originally we started using only for our, like, work with customers, but now, like, I think I'm looking forward to more findings and new interesting topics.

So yeah. And thank you, Kirk, for joining. That was great, interesting discussion. See you next time.

Kirk [00:50:19]: Absolutely. Thank you, Nik. Goodbye, everyone.