Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

Postgres 18

Sep 26, 202556 minAudio

Nik and Michael discuss the newly released Postgres 18 - the bigger things it includes, some of their personal highlights, and some thoughts towards the future. Here are some links to things they mentioned: Postgres 18 announcement https://www.postgresql.org/about/news/postgresql-18-released-3142 Postgres 18 release notes https://www.postgresql.org/docs/18/release-18.html Skip scan episode with Peter Geoghegan https://postgres.fm/episodes/skip-scan Easier Postgres fine-tuning with online_advisor https://neon.com/blog/easier-postgres-fine-tuning-with-online_advisor pganalyze Index Advisor https://pganalyze.com/index-advisor BUFFERS by default https://postgres.fm/episodes/buffers-by-default Buffers II (the sequel) https://postgres.fm/episodes/buffers-ii-the-sequel Return of the BUFFERS https://postgres.fm/episodes/return-of-the-buffers UUID https://postgres.fm/episodes/uuid Partitioning by ULID https://postgres.fm/episodes/partitioning-by-ulid uuidv7 and uuid_extract_timestamp functions https://www.postgresql.org/docs/current/functions-uuid.html Add --no-policies option to pg_dump, pg_dumpall, and pg_restore https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cd3c45125 Add ONLY support for VACUUM and ANALYZE https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=62ddf7ee9 Make "vacuumdb --analyze-only" process partitioned tables (committed recently for Postgres 19) https://commitfest.postgresql.org/patch/5871/ NOT VALID constraints https://postgres.fm/episodes/not-valid-constraints The year of the Lock Manager’s Revenge (post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability Increase the number of fast-path lock slots https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c4d5cb71d "enable" parameters will work differently in Postgres 18 https://www.pgmustard.com/blog/enable-parameters-work-differently-in-postgres-18 logerrors https://github.com/munakoiso/logerrors ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

Nikolay: Hello, hello, Postgres.FM. My name is Nik, Postgres.AI, and as usual my co-host is Michael, pgMustard. Hi, Michael. Michael: Hi, Nik. How's it going? Nikolay: I'm very good. How are you? Michael: Yeah, very good also. Nikolay: It's again this time of the year, right? When it's obviously the right moment to discuss something. We make a mystery of it, but people already saw the title. And I'm pretty sure you won't hide it in the title, right? This elephant in the room. Michael: I was going to call the episode Christmas for Postgres

users. Nikolay: Big box of gifts. Michael: Exactly. Nikolay: Cool. Okay, let's talk about it. Michael: Well, I think for anybody brand new to Postgres or like relatively new to the show, we've done these episodes each year when the major releases come out and Postgres has got a really good track record for many many years now of doing a single major release each year around this time of year and that's the only release in the year that will include like new functionality, breaking changes, you know very semantic version style major

version. Nikolay: No it's not same there at all And we have many years we have a dispute inside my team. Same there or not same there. I noticed by the way, Tiger Data, Timescale, they appreciate Postgres approach, only 2 numbers, not 3, because original SemVar is 3 numbers. And 3 numbers, I always have, like I understand the logic behind 3 number versioning, But I like Postgres approach much more. It's much more simplified and so on.

Michael: Cool. Well, breaking changes will only ever go into a major version, but you're right, even the major version, we would still cut a new major version even if it was only new features, even if it didn't have breaking changes. So that's a good point. So yeah, you're right. Nikolay: Yeah, yeah, yeah. It's only 2 numbers, but yeah, major version, minor version. Simplicity. Michael: Yes. Nikolay: And major version changes every year. Michael: Major version every year, minor versions at least every quarter, sometimes more often if they need to do quick security

fixes. So yeah, this is the time of year where we get new features and the only time of the year that we get new features in core Postgres. So as such there tend to be a lot of them, so we've got a lot of things we could be choosing, no chance we're going to talk about them all today, but the idea is to talk about some of our favorites, some of the kind of categories of things that are coming, things that we've been kind of involved with or emotionally attached to or wanting for many years, all sorts. Where would you like to start?

Nikolay: Well, first of all, maybe we should just mention the features which we already discussed in depth. And we had the whole episode sometimes for some features which are going to be out. Right, so for example, skip scans in B-tree, right? Michael: Yeah, that's a huge one. And we had a great chat with Peter Geoghegan, didn't we? All about them. Nikolay: Right, right. And the TLDR version of it, it's sometimes index on two columns, for example. It will be able to support searches, for example, only on the

second column. So this rule, we got used to it over time that we must put the second column on the first place if we need searches or create additional index only on that column, second column. Now it's sometimes not true and things become more complicated when we make decisions on indexing, right? This is my perception, oh, like it's even more complicated now. Michael: Yeah, I would add a little bit that it's about the efficiency

of scanning that index as well. So we could previously scan a full index for things that didn't have filters on the on the leading columns it just wasn't it had no chance of being efficient or it's very unlikely to be efficient depending on the exact case now we can efficiently do it or Postgres can efficiently scan skipping any equality like across the first column if the first column doesn't have high cardinality. Let's say I think you gave the example a while ago of a Boolean.

So if we had only true and false, we could jump to the true and check that for the thing we want to filter on, and then skip the rest of the true values and then jump to the false where we, and pick up again there. So it's a much, much more efficient way of being able to scan indexes that aren't perfect for our query. Nikolay: For performance it's excellent news. Excellent. I'm just thinking about the decision-making process when we have a lot of tables and complex workload and we need to choose proper

index set. And I'm already, as you know, I shifted my mind totally to full automation because of full self-driving idea, full self-driving Postgres. And I see several good attempts to create index advisors mechanisms. 1 more is from Konstantin Knizhnik, there is blog post and Neon published this week. So and there is also a pganalyze Index Advisor. I was just thinking how this new feature will affect those tools.

Michael: Interesting to see. The good news for users though, this is 1 of those features that if you upgrade you might just see certain queries get faster without having to add new indexes. If you haven't thoroughly gone through all of your queries, optimizing them with perfect indexes already, there's a good chance that some of your existing queries will better serve queries that you haven't optimized. Nikolay: So if in the past we could consider something like this as a mistake. Now, like, Postgres is more foggy in this area. Michael: Much more.

Yeah. Nikolay: Good, good. Michael: So yeah, this is 1 of those cool features that I think loads of people are going to benefit without even necessarily knowing that they're going to, or... Nikolay: As usual, my advice remains to verify everything with real testing, with proper datasets and settings of Postgres Planner and work_mem, which is not Postgres Planner setting. And just verify everything and so on. Cool. Michael: Testing and Nikolay: so on. And maybe, maybe we will be able to drop some indexes additionally

because of this feature, who knows? It's yet to be discovered, because we don't like a lot of indexes due to many reasons, and now that 1, like 1 of them will be discussed later, I'm pretty sure. What else we already discussed? Michael: So the 1 we've discussed, I think we've had at least 2, maybe 3 episodes on, was where the BUFFERS should be on. Nikolay: Ah, okay, I didn't get, now I get. Yes, this is our favorite, and this is big news for this podcast actually.

Right. This is why in January I said this is I think it was committed very long ago in January or it was already obvious that it's going to be committed. So we spent 2 years, actually, this is my position, my point of view, we spent 2 years advertising for using BUFFERS inside EXPLAIN when you do EXPLAIN ANALYZE, and finally, I feel like, I feel slightly being sad because we don't need this anymore.

I actually had t-shirt with this, EXPLAIN (ANALYZE, BUFFERS) with BUFFERS written in bold. So yeah, BUFFERS after Postgres 18 hitting your Postgres servers. Yeah, it's not needed anymore. You just say EXPLAIN ANALYZE and you get BUFFERS. Yeah, I had some slight shift. I know pgMustard website has a recommendation how to properly collect plans, execution plans.

And I shifted my mind already, focus shifted to SETTINGS, WAL, and VERBOSE. Great. Yeah. So now, for older Postgres versions, we write EXPLAIN ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE. Then it will be minus 1 word. But it feels still like there is a room for improvement here. And we had this discussion. So let's just refer to it and move on. I think what I've got confused, because we also had a couple

of episodes about UUID. Maybe 1 of them was without you actually. Michael: It was. Nikolay: Yeah, this work uuidv7 started a couple of years ago when Kirk Wolak joined Postgres hacking sessions where Andrey Borodin and I tried to cook something online on YouTube channel, Postgres TV YouTube channel. And he joined with idea, let's implement ULID originally. Then it shifted to uuidv7, but then we needed to wait.

I mean, Postgres project needed to wait for quite some time because RFC was not polished and Peter Eisentraut's position was like, we should release only when it's polished because who knows, maybe something is changed, is going to change. So I remember like, yeah, very conservative. Everyone already has it. Various NPM modules, Python, Google projects, they all already have uuidv7 support, even though RFC is not finalized. But Postgres, I think, took very conservative position.

Maybe it's good for Database because it should be conservative, right? Michael: Well, and we need to support it for 5 years, right? Like if whatever we implement, we're saying we're supporting this version for 5 years, even if you automatically, even if you manage to deprecate it the next version, which is also nearly impossible. Normally you need to deprecate, give warnings that it's being deprecated, then actually remove it in a later version. So I really understand that and I think also Postgres did support, like you've said this multiple times, Postgres does support uuidv7

in older versions of Postgres. You just have to generate them either outside of the database or with your own function. You've done it even within Postgres. Nikolay: Yeah, this is what we do already for a couple of years. And we have how to do it in PL/pgSQL or just with pure SQL. It was part of my how-tos. Yeah, it's already aged. And you can combine it with partitioning, TimescaleDB or whatever. But this time it's just, actually it's like basically synthetic

sugar, honestly. Michael: It's a nice function, right? Nikolay: Since it's official we can expect it will be more widely used. Yes, and it was discussed a couple of times on Hacker News and people say there is like concerns about security ability to guess. I don't get those concerns because it's really hard to guess anything from those values. You can extract timestamp and unlike recommendations from original RFC authors, Postgres decided to present a function uuid_extract_timestamp

from value, from uuidv7 value, which I find useful, for example, for partitioning implementation. So anyway, this is a great feature. We discussed it in depth. Let's not lose time. But it's not like we have a data type, we just have a couple of functions to generate and to extract timestamp, that's it. But data type remains just UUID, it's version agnostic. Michael: Yeah, we already, we do have a data type, we already have the perfect data type, which is we've had it for many years, now we have nice functions around it too.

Nikolay: Now it's just in Query and everything in Query is great. Just use it, use it. It's better than version 4 for performance reasons and also convenient. I still find it helpful to keep timestamp like created at separately, although you lose additional bytes, but it's a matter of like, you need to compare what's better for you. Just rely on ID value, if it's uuidv7, or have it separate. I think it's worth a separate blog post to compare pros and cons

for this, actually. It's an interesting topic for developers, I think. What else? We also discussed 1 feature a few times, which I'm listed as author, right? But I'm like, I wipe coded it originally. And it was --no-policies support for pg_dump and pg_restore. I know there is an issue which some other folks were fixing because

it didn't eliminate comments. Policies can have comments, you know. So if you specify --no-policies, I saw there was an additional work following up this patch. And yeah, it's a pity that I missed it. But in my defense, I must admit that other know something in pg_dump and pg_restore also forgot about comments. And this work was done in Claude 3.7 in January, I think. Was committed later by Tom Lane with some improvements.

But when I say VibeCoded, it was originally VibeCoded, but eventually before sending patch, I always review line by line everything and polish myself and we're using multiple LLMs. So I think it's not strictly speaking vibe-coding. We call it vibe-hacking, but it's just good for prototyping to use some help of LLM if you don't write C code every day. And I think it unlocks the power for people who are not C coders.

They can implement things and present patches. But obviously, if you do it without looking into code and send some vibe-coded, purely vibe-coded patch, it's a terrible idea. And inside my company, everyone is using LLM AI to code, but it's like, it's prohibited to right now to start coding on your own, not thinking about LLM, but it's also prohibited, not, not taking responsibility for final result. If it's some BS code, it's your, on your shoulders, not like

you cannot blame AI. Right. And if you, if you send something, some proposal, open pull request, merge request, or sending patches, obviously you are responsible for what you are sending right Michael: yeah well I think it's quite rude as well. Isn't it because you're asking when you send a proposal PR merge request Anything you're asking other people to review it and I think the minimum you owe them is that you've reviewed it yourself yeah, when you when you ask other people to review something.

And that didn't used to be an issue because by writing something, you are reviewing it in a way. But now that people have options that involve them not even writing it, I think it is a step worth being insistent on. Nikolay: If you like not writing C code all the time, but you want to implement something, go take AI to help you, but then take another AI to review it and explain every line, explain every line to you so you fully understand what you, like the

final result and you are happy with it and you have tests and documentation. By the way, small tip, start with writing documentation first. And like usually people like, it's boring to think about documentation and tests with AI. It's slightly more like productive and Postgres test system is it's Perl. So it's really like not easy to deal with if you've like never wrote Perl or already forgot it after 20 years, for example, or something.

Anyway, I think AI is great. It's like it's unlocking creativity here, but the final result is on your shoulders fully. And I also, before this episode, I checked a couple of articles about Postgres 18 and I found terrible articles. Terrible articles. I can, like, I already, I'm building the set list of companies who produce BS blog posts, obviously LLM generated. And this is like not okay, in my opinion.

Do you mind if I name the companies? Michael: Not at all. I find it quite frustrating. And I think again, it's rude, right? You're taking a shortcut and publishing something that you hope will be read by many, many people, taking many, many more hours to read it than you took to write it. I just think it's... Nikolay: Yeah. In the past, I saw a lot of BS articles about Postgres on the website called MinervaDB.xyz, something like this. This time stormatics.tech blog post has a lot of wrong information.

Michael: Well yeah I guess this goes back to your your age-old thing of always verify right but sadly now we need to make sure we're also verifying things from what we considered maybe in the past to be reputable companies. Nikolay: Actually let me correct myself this stormatics.tech article was not I'm in the context of you know like building some police bot for blog posts about Postgres. This blog post was about optimization of moving pg_wal to different disk and it had a lot of mistakes.

For example, mentioning undo in WAL and a few others. But this time about Postgres 18, we have blog post published on dev.to, devtips, and it has a lot of, like it's LLM generated because it says logical application gets full DDL support. Well, no, it's not. This is 1 of the pains we still have. Obviously, LLM generated. So I think before we consider problems with patches, wipe coded, purely wipe coded, not wipe hacked, as I said, but wipe coded.

We have a bigger problem of misinformation being spread on in blog posts So yeah, and what do you think it would it would help to have some tool to quickly verify if I to do some fact-checking So we I checking AI and so on. Michael: The challenge is false positives and false negatives right? Like if an AI is capable of coming up with this stuff, well who's to say an AI wouldn't also rubber stamp it? So that's the kind of, there's the, there's the not catching

the ones that are AI written. And well, and then there's the issue of catching or flagging blog posts that were human written, but it thinks AI and accusing those of being AI written. I think this is an unsolved problem, isn't it? Don't education have this exact same issue with coursework and things? I think Nikolay: you are right. Humans also hallucinate. I hallucinated on this very podcast sometimes. I think we should say, not AI checking AI, we should say, I have this information. It can be a blog post or some report.

We actually, internally, we have such tools already because we do a lot of consulting We write some reports and we need to verify we do we need to do fact-checking because sometimes you think this is this is it this is how Postgres works, but it worked like that 10 years ago and it already changed. So we need to dig deeper and do some fact-checking all the time. Because for example, I was wrong recently when I said changing your primary key from int4 to int8 is going to produce

a lot of bloat. I was completely wrong. And in that case, I was wrong very deeply. So it turned out there is a table rewrite mechanism similar to VACUUM FULL. Nice. And table comes out from it, like alter table, alter column. Table in the end is actually fresh and all indexes are freshly rebuilt. And sometimes it's acceptable to have this downtime maintenance window. Michael: Yeah of course completely offline operation but no bloat. Nikolay: So yeah I sometimes don't trust myself and this tool

like checking, fact checking we could use it yeah we use it internally again Like I am thinking to present it as an external tool so we could check blog posts we suspect are not accurate. Michael: I think that well accuracy is slightly different to LLM generated and I think it depends what you want to like. Do you want accurate posts or do you want human written posts? And that those are subtly different. Nikolay: I don't care about who wrote it, I want accurate posts. Michael: Yes, so I wasn't even talking about inaccuracies.

I was talking about inaccuracies in the checking. So like, even if you assume the article is completely accurate and human-written an LLM checker could still say we think this was this has some hallucinations in it you know hallucinating the hallucination so I don't have much hope in this, other than trust-based. You know, if you as a company start publishing posts that you haven't checked, whether with humans or if it has inaccuracies

in it, I'll forgive a few. But if you're doing it consistently and there's loads of made up stuff, I'm gonna stop reading your stuff and I'm gonna stop sharing it and you know there's... Nikolay: So LLM can be considered like amplifier of your like state. If you are not accurate, you produce a lot of bad pieces of advice, wrong things. It just amplifies it, right? So if you don't check information. To connect bits a little bit and finalize this AI discussion,

I find it Also interesting that sometimes hallucinations are really helpful. I had several hallucinations which led to the idea, actually this missing feature would be great to have. Well, this is great hallucination. Let's have DDL in Postgres 18. Already too late, right? There is a work in progress, unfortunately. And by the way, I recently checked it, DDL and logical, support of DDL and logical replication. It's not like we discussed it, right? I have low expectations that it will be implemented very soon,

unfortunately. And it's Michael: a lot of work, isn't it? And there's probably lower hanging fruit like sequences and things. Nikolay: Yes, this is also a work in progress. And I think, is it already going to post this 19 next year? I'm not sure. But sometimes I like just working, writing some ideas, I'm getting some great ideas like we had the case with corruption and we needed to fix it with all backups and so on. So we needed to apply a pg_resetwal.

The idea was we need to recover from some backups and they were broken, so LLM suggested to use pg_resetwal with a system identifier option which doesn't exist. So I ended up implementing that option and proposing a patch. Unfortunately, it was not finished before Postgres 18, but maybe it will be finished before Postgres 19 and we will have this option. Yeah, so there is discussion about this. Michael: That's funny. Nikolay: Yeah, and a few things like this. So sometimes, so this is like, interesting thing to have LLMs,

but you must fact check and sometimes you think, oh, this is a good idea and go implement it. So why not? Michael: Yeah. All right. Well, while we're on topic of corruption, another thing we've discussed a few times is checksums and encouraging people to enable them. And we're getting them, they're going to be on by default now from 18 onwards. Nikolay: I think it's long overdue. Michael: Yeah, it was but great. Nikolay: Yeah, it's a great great thing Minus 1 recommendation

we will have in the future for our clients and RDS had it for ages, right? Checksums are enabled there by default. Google Cloud also yeah yeah just great thing Michael: yeah I think quite a few of the hosting providers do but each of them have to it's another 1 that any new hosting provider that comes along they have to then know to do that or their customers don't get it by default. Now they don't even have to know and they'll get it on by default which I think is great. Yeah. Another 1 we've discussed a few times is pg_upgrade getting or

preserving the statistics, the optimized statistics across a version upgrade? Nikolay: This is a huge pain, and especially because, unlike previous topics, this is not solved in managed Postgres providers. I didn't see any of them, I mean, major providers like RDS, Cloud SQL, others. They always put it on shoulders on users to run, Analyze after major upgrade. And we had outages because of that.

So now I think in the future, not now, in the future. You cannot use it before your old cluster is already on 18. So it's only like first time we'll be able to benefit from it in 1 or 2 years only, when you upgrade from 18 to newer version. But anyway, this is great news, so I feel really happy that this is solved finally. And this is solved in a very elegant way because now you can dump statistics, So it's not only for upgrades, you can use it

for other, in other areas because you know, like, pg_restore also doesn't care about it. You restore and you need to run, Analyze manually, also vacuum actually, you need to run vacuum. Now, recommendation will be after pg_restore, after restoring from a logical backups, aka dumps, like logical backups is fine to name dumps, like, but not just backups. So when you restore from it, we always said you need to run a

vacuum Analyze. Now it's just a vacuum to have visibility maps created sooner. Michael: Your index only scans, yeah. Nikolay: Well if you forget vacuum, it's not a big deal. Maybe you will like, and of course, autovacuum will take care of it, but lacking statistics, it's a big danger. Now you can dump statistics, restore statistics. This is great I think from this we can benefit sooner than from

having it in upgrades because once you're already running Postgres 18, you can start using it Michael: By the way, I think I think we might owe at least 1 cloud provider an apology. Do you know who implemented this feature? 3 guys, all from AWS. So I think at least 1 cloud provider does deserve credit for implementing this feature, and they've actually implemented it in a way that everybody benefits which is cool. Nikolay: Right, but why do we need to apologize?

Michael: Well because you said all of the cloud providers have just shoved this onto users and didn't do anything about it. Nikolay: I think yes, we can apologize easily, not a problem. And this is great. I knew this feature is from RDS team. This is great. But still, if you run upgrade on RDS, it has 16 points, 16 steps. Michael: Yeah, yeah, yeah. Nikolay: How to upgrade using pg_upgrade official documentation has 19 steps. RDS has 16 steps.

And 1 of them, last 1, 1 of couple of last ones, is you don't need to run analyze. And people tend to forget or just say, okay, maybe it's not super important. And it happens. People, it's manual. And so I stand my ground. They don't automate it, they put it on shoulders on users. But of course, now they need to wait until 18 and then 19, and

then it will be fully automated, which is great. Yeah, I heard reasons why they don't automate it, because it can take time, prolonging downtime window. And there is an official approach, analyzing stages, which I think is a mistake, honestly. Because in OLTP cases, we better to get final statistics sooner and do it inside maintenance window. Right? Michael: Yeah. I just think if you care about the downtime, you should probably

be working out how to do a 0 downtime upgrade in some way, shape, or form, and then you don't have to worry about this problem anyway. So it's more, I think if you can take the downtime, why not just do the full analyze? Nikolay: Exactly. And also in this area, pg_upgrade, I think, did you see that it got parallelization --drops option for... Michael: Yes, I did, yeah. Nikolay: This is also great news for those who care about upgrades.

Everyone should care about upgrades, right? Michael: Yeah, especially when we're talking about a new major version. Nikolay: But anyway, like, am I right that statistics can be dumped and restored, even not in the context of upgrades? Michael: If it's if If they can, then actually people can already benefit from this because you can use... Nikolay: Yeah, this is a dump option statistics. So once we are... Because this is discussed in the context of upgrades only, but again, it's not about only upgrades.

Once your server is running Postgres 18, you can already start benefiting and adjust your dump scripts and all these dump statistics. And then you can just restore it and that's it. I also noticed that vacuumdb has now, in Postgres 18, received the option missing stats only, to compute only missing optimizer statistics. This is interesting, because vacuumdb has also --jobs, so to move faster if you have many many cores?

Right, you can say... Michael: I think, yeah, I think we need the missing statistics because currently extended statistics aren't dumped and aren't preserved. So If you create, you know, we talked about correlations between 2 columns. If you create those, those are not preserved. So I suspect that got added to analyze so that we don't have to run full analyze still. Otherwise there's, Well, if we've used create statistics, if we've used the extended statistics.

Nikolay: Yeah, that's interesting. But also, as like, to be careful with vacuumdb --drops, I love it. And because if, if you have increased default statistics target, or like a lot of statistics to a lot of tables and so on, it's great to have parallelized processing here, right? But unfortunately, until version 19, so next year only, partition tables will still be in trouble because vacuumdb doesn't take

proper care of them, it only cares about partitions themselves but root table is not processed and if you process it with analyze it will process partitions once again anyway so there is a problem here. Michael: We have a new feature for that too in 18. Nikolay: No it should be in 19. Michael: We have an analyze only for the parent partition. Nikolay: No no no Analyze only for parent partition is in work. I think it's not like it's, it's, maybe it's committed already,

but it's not in Postgres 18. Laurenz Albe did it. I noticed because I also wanted to do it but it was only recently committed I think. There is allow vacuum analyze to process partition tables without processing their children. This is great but this is not vacuumdb stuff. Michael: Yeah okay but we can do analyze only and then the table, the parent name, you're right it's a separate part. Nikolay: Analyze only we had always, I mean for ages.

vacuumdb --analyze-only, this is what we do after upgrade, I know it because This was our recipe until we hit this very problem when partition tables lacked, root table lacked statistics and some queries suffered from it. Michael: No, but if you did it on the parent partition, it would also gather statistics on all of the child notations as well. Nikolay: There are many things here. There is a single threaded SQL command, right? Analyze, or vacuum analyze, doesn't matter.

They are single Michael: thread, Nikolay: right? And there is also vacuumdb, which has a -j, aka --drops option, which helps you parallelize. The problem is when you run vacuumdb -j like 60, I'll analyze only. Root tables in partition tables will lack statistics after it. And this is solved after Postgres 18 beta 1 was out. So we need to wait until 19. Michael: So that would be in 19,

Nikolay: sure. We cannot move fast and take care of partitions, unfortunately, partition tables properly. Yeah. Michael: We need to set, then you need to do a separate task. Nikolay: Right. So in Postgres 18, first of all, statistics can be dumped, which is great. pg_upgrade is not in trouble anymore. Okay. But sometimes we still need to rebuild statistics. In Postgres 18, also, vacuumdb received missing stats only. And vacuum, single-threaded SQL command, received ability to

take care of partition tables properly. This is what happened. Michael: Not just vacuum, but also analyze. Nikolay: Yeah. Vacuum, well, yeah, vacuum analyze and analyze. Michael: So vacuum's a bit pointless. I don't think there's any point vacuuming a parent partition because it's not going to have any data in it. Nikolay: But it needs statistics. Vacuum, analyze, vacuum. Michael: Exactly, it does need statistics separately from its children. But in the past, in 17 for example, in version 17, 16, 15, if

I, to get statistics on the parent partition, I'd have to run analyze on the parent table, which would also gather, regather statistics for all of the child tables, which is a lot, like that, There's so much more work. Nikolay: So what happened, we say, vacuumdb -j is great because we have 96 cores, let's go. Hyphen-j 96, let's go. But then, oh, actually, root tables and partition case, they lack statistics. We need to run analyze on them.

But when you run analyze on root table, it recalculates statistics for all partitions once again. Now in 18, we have ability to skip that and say, okay, vacuumdb -j, move really fast. And then additionally run analyze only on partition tables, only for root partitions, skipping partitions themselves, because vacuumdb already did it. So now we have full recipe for Postgres 18, how to recalculate statistics in all database and move really fast.

When you're like inside maintenance window, for example, you need vacuumdb -j, and then separately you need to take care of root tables for partition tables, skipping partitions themselves. Right? This will be the fastest. In Postgres 19, vacuumdb will take care of this and we will need to get rid of this second step. Michael: Yeah, I didn't check the commit, but it's possible. I said missing stats only might be for create statistics, it might be for extended statistics, but it might also be for parent

partitions. That could be another place you're missing stats. Yeah. I haven't checked. Nikolay: Anyway, 19 will be excellent and we will have all the pieces of the puzzle ready and we can move fast. I like, I honestly think like Vacuum and Analyze could have some option, I mean SQL commands, they could have some option to tell like I want additional workers. Give me like, you know, like vacuumdb is good, But if you in managed Postgres case like RDS, you cannot easily run vacuumdb

because you need this to instance running in the same region or something. So you like it's terrible idea to run it from laptop, right? Because connection issues and so on, you will lose it. So you need an EC2 instance next to it, like some maintenance host, and from there you can... It's not convenient, right? It would be great to have autovacuum and say, I want like 16 drops or 96 drops and let's go full speed, we are inside maintenance window. Makes sense, right? Michael: I think so, yeah.

Yeah I'm thinking also you might always want to have more, if you've got, you know, regularly scheduled jobs, you might always be able to give it more. You might even want a configuration parameters like always give my vacuums at least 3, you know up to 3 workers or something. Anyway, getting into autovacuum territory here. Nikolay: Yeah, yeah, yeah. Let's maybe jump in between some other pieces. There are actually so many great things. I like the release overall, a lot of stuff.

And for example, I like the idea that now regular check, not check, not null constraints can be defined as not valid. As we discussed a few times, it's terrible naming not valid because they will be checked for new writes, but they are not checked for old writes. I think it's like all the pieces we had before already, because not null, I think in Postgres 12, since Postgres 12, it can rely on check constraints, check not null constraints.

There is not null and there is check constraint. For primary keys, we needed only regular not nulls, not null constraints, right? But implicitly we could have already used like regular check constraints with not null and then do some dance around it. Now it's like becoming easier to redefine primary keys without thinking about all these nuances. I like this. Things are polished now in many areas.

Like we just discussed it with statistics and this, not now. I like this. Maturity of many pieces, many functionalities. Michael: It feels like a release chock full of things that have come up for real. For real users have hit these issues and people have come up with fixes for them. It's not like, I know that's the almost a definition of a maturing product but it doesn't feel like there's that bigger divide between

hackers working on things that hackers want to work on versus users hitting problems and actually wanting to solve problems you know that it feels very very tightly done which is nice. Nikolay: Yeah yeah another thing I wanted to mention is ability is infamous number of fast path slots for Lock Manager, right? We had 16 only. Yeah, we had issues in multiple customer databases.

Several companies suffered. And I remember Jeremy Schneider, who did it? Like this was year of Lock Manager. Michael: Yes, that was his post. Nikolay: Yeah, it was maybe a couple of years ago, right? Or maybe, yeah, 2023, I think. And now, yeah, I raised this last year in hackers that we need to make this probably adjustable, like expose it as a setting. And Postgres 18 finally has this solved, but in a different way.

I think Tomas Vondra, who worked on it mainly, right? And instead of adding 1 more GUC setting, we already have almost 300, right? Too many. Instead of that, now it takes into account max locks per transaction setting, right? I think. And automatically adjusts based on that. So if you have a lot of cores, you likely, if you tune your Postgres,

you raise that setting already. So number of these slots for fast path checks in the Lock Manager mechanism, it will automatically raise. And we checked in 1 case, we had 16 hitting replicas in that case. Sometimes it's hitting your primary workloads, sometimes replicas workloads. In that case, we had 16 by default, right, before 18, but now we will have, due to that setting, it's already adjusted, we

will have 64. And honestly, in half an hour, there's a scheduled benchmark I need to do, I need to conduct. But unfortunately, I remember early implementations of the idea of raising this threshold were not super successful in terms of helping with this performance cliff. So I'm not sure. This should be very carefully tested in each particular case, because there are several variants of this cliff.

Michael: Yeah, well if I trust anybody around performance cliff, I do trust Tomas to have looked into it. Nikolay: So I'm optimistic. As we learned from our podcast episode, we have different definitions of performance cliff. Michael: True. There is 1 huge feature in Postgres 18 we haven't talked about yet, which is asynchronous I/O. Nikolay: Yeah, well, this is elephant in the room, honestly. And I must admit, I lack experience. So I cannot tell a lot about this.

I know this is a big work led by Andres Freund, right? Yeah. So yeah, I remember following this work. It's a lot. I hope we will find good results in some benchmarks in like actual systems we help manage But I don't have such experience yet. Looking forward to it, honestly, so Michael: Yeah, it'll be good to benchmark it because I think a lot of the normal benchmark type workloads, you know, pgbench,

sysbench, that kind of thing, won't see benefits from this work. There are like lots and lots of small queries and this is much better at large scans, right? Like if you're benefiting from gathering a lot at once, you're gonna see bigger differences when you're getting more blocks than 1 block, for example. So yeah, I think it'll be interesting in real world workloads to see how big a difference. I think it's another 1 of those things that you could see significant improvements to how fast vacuums run on some of your largest

tables or how fast, I don't actually don't know exactly which things have made it in, but I think bitmap heap scans definitely have and vacuum definitely has and there's a few others but as we see more and more of those get used in future versions of Postgres I think we're going to see the benefits of this without having to change anything again it's another 1 of those features that has managed to be committed on by default and with quite high, like not, maybe not high is not necessarily the right word, but it hasn't been shipped with really conservative defaults,

I don't think. That's how I'd phrase it. Nikolay: Yeah. I would also pay attention to additional CLI tools this version is bringing. Because in Postgres 17 somehow I've overlooked and we in our discussion didn't touch it, but later I noticed, not I noticed, somebody pointed out to this big new tool appeared, a pg_create_subscription, converting physical standby to logical replica,

which is great, like absolutely, like this is automation of, like we can throw out a lot of automation, which is great. I always love to do it. It's official and it's working. I'm not sure about managed Postgres setups yet because I think they must expose it via their APIs and CLIs because it needs to be run on the server and we don't have access to it. But in self-managed setups, it's already available since last

year. I mean, since your production is running Postgres 17. Yeah. But also, this release Postgres 18 gets extension pg_logicalinspect. Well, it's not CLI, but some extension. I'm not sure what's inside in detail, but I would like to explore it, because logical replication is quite complex, and inspecting logical snapshots and sounds is a really good idea. And also there is extension pg_overexplain.

I'm jumping between CLI tools and some extensions, I know. But this is a good thing, I think, to understand. I remember how it was born. Robert Haas did it, right? Yeah. And I think maybe you can tell more about this because it's closer to your fields, dealing with EXPLAIN plans. Michael: Yeah, I think, firstly, kudos for an awesome name. How good a name is pg_overexplain Nikolay: but over tune

Michael: Yeah But yeah, so I think it does 2 things that are quite important 1 is it's the first customer of the ability to extend EXPLAIN in an extension, So I think it's a good example to people if they want to add more features to EXPLAIN in the future which has been, you know, over the last 4 or 5 versions we've had quite a few new parameters added to EXPLAIN and I suspect there's some amount of belief that maybe some of those could have been extensions for a while first and seen how popular they

were before adding them for everybody. So that first and foremost Robert made it EXPLAIN extensible and then overexplain is an example of that but also is more information than a user would normally want from EXPLAIN that would generally be useful for hackers like people working on the Postgres source code or optimizer trying to understand why it's making certain decisions while they're working on a new feature. So for example the behavior around disabled nodes.

So you know when you can you know when you're testing why isn't Postgres using my index and you want to do enable_seqscan = off so that you completely disable, well you try and discourage sequential scans so that if it could possibly use your index it will. In the past that used to work by adding a massive cost constant to sequential scans so that they'd be discouraged but not impossible That's changed in I know Robert actually was happy to be the

person working on that 1 as well and it's now done on a on a base of counting the number of disabled nodes as the first tiebreak and then cost as the second tiebreak which is a really neat implementation I've actually blogged about that but the overexplain instead of telling you which nodes are disabled within the EXPLAIN plan it will give you a counter of the number of disabled nodes so far so that's useful for hackers but it's really confusing as a user because if you're using it you can see disabled nodes

counter being 1 all the way up and you have to kind of look back to see what the first 1 was and luckily I think it was David Rowley who kind of backed that out and made it more user-friendly but the the point is EXPLAIN is designed for users. The primary user of EXPLAIN and EXPLAIN ANALYZE are people trying to speed up slow queries. Nikolay: But- Do you mean human users or do you Michael: mean- I Nikolay: mean human users. Michael: I mean human users and what I mean is not Postgres hackers,

not people working on the optimizer. So overexplain gives them a bit more information about exactly what the optimizer is doing at each stage. And I just don't think it's going to be useful for users. Nikolay: Yeah. Let's also mention moving to macro level. We mentioned that pg_stat_statements have a couple of improvements. We always suggest that especially for Java applications or some applications which tend to set application_name to some like session ID, blah, blah, blah. And set comment could pollute pg_stat_statements.

We always said set pg_stat_statements.track_utility to off, so you don't track set commands. Now in Postgres 18 it's not a problem anymore. Parameter is just like, I think $1, right? Should be. Yeah. So yeah, you basically it's all, everything is just 1 entry set application_name to something. So normalization is implemented for set commands. And also something is improved in the area of tracking parallel activity, right? Michael: Yes, there's like some extra columns.

Nikolay: Not enough columns in pg_stat_statements. Michael: I have actually seen a conversation since then discouraging more columns. Interesting that that 1 got in. I wouldn't have thought that would be the 1 that snuck in last. Talking of new columns, there's also some new ones on pg_stat_all_tables and similar for vacuum tracking, vacuum and analyze time spent which looks really useful. Nikolay: Yeah yeah I like this and I think it's worth working on moving information from logs to SQL yeah It's easier to monitor

it than dealing with logs. I can't wait until errors will be tracked properly in Postgres. There is work in progress in this area. I hope it will end up being added to 19. Not yet, I just like, maybe it won't work, but I think errors should be tracked as well, properly. Counters at least, like something. Yeah, because now we need to go to logs all the time. So yeah, although I actually, I learn all the time.

Last 20 years I learned Postgres and sometimes I feel like I'm lagging. Development is faster than my learning curve. Yeah. So I just learned that there is a pg_stat_database_conflicts or something, view, which exposes some types of errors. And for example, it can tell you that query was canceled because tablespace was moved. It also can tell you that it was canceled because of certain

timeouts, but not statement timeout and not transaction timeout, not idle transaction timeout. Yeah, due to lock timeouts, for example. Lock timeouts, yes, but not statement timeout, not transaction, not, I don't know. And a few more like replication conflicts. So it tracks some counters, but choice is really strange. And query cancellation due to very common reasons like statement timeout. Unfortunately, you need to parse logs right now or use an extension

called logerrors. Not super popular, but very convenient. Unfortunately, available nowhere, almost. So only in self-managed case, I guess. But I hope this idea of logerrors will be finally implemented in the future in Postgres itself and yeah. Okay, good. We touched some future a little bit as well. Michael: I had 1 more favorite I wanted to give a shout out to. Obviously it's EXPLAIN related being me.

Nikolay: Right. Michael: I think there's a really big improvement that has gone a bit under the radar for many, which is actual row counts are going to be reported as decimals now so this is most important when you have loops and lots of loops and it will report you've got 100,000 loops. Pardon me? Nikolay: 1.5 rows, right? Michael: 1.5 per loop is way more useful than 1 or 2 rounded

to, but it's even more important when you are around the 0.5 rows per loop or 0 like anything 0 to 1 is particularly interesting below 0.5 is very very important because it will round to 0 and no matter what you multiply 0 by you're gonna think that that's returning 0 rows and that's really Really not true when you've got a hundred thousand loops. Nikolay: Yeah Yeah, the mistake accumulates and multiplies.

Michael: Yeah, exactly Okay, the next like once you get past the looping operation, you will see the actual number of rows, but you don't know what's happened in between. Now you don't have to worry about that. It will report it as a decimal and you can multiply that. Nikolay: Let me add 1 more because this is great actually because I feel connection to so many features and 1 more yeah this is where Andrey Borodin worked like and others many others actually. He worked for quite some years and finally amcheck is getting

support for GIN indexes. Yeah. This is big because we do need it. We do need it because we need to use amcheck often. Michael: I actually missed that in the release notes as well. Nikolay: It always worked for B-tree to check indexes for corruption when you, for example, perform OS upgrade or other stuff. You need to check for corruption from time to time. But GIN indexes, like, we couldn't do it. Well, we usually, in self-managed cases, yeah, only in self-managed

cases, I think we used patched version. So we, we tested that patch. We, it, it took several years for, for the patch to mature and get rid of false positives, false negatives, I think. Especially false negatives when it's missing. I always get confused. But it was false positives as well. Yeah, but now it's great. It's there. So it's time to use it. Extend your amcheck actions to a GIN. Yeah. And if you don't have amcheck actions, you must.

I mean, you should, not must, you should. Because amcheck is a great tool. I think maybe it should be renamed because we use it mostly to check indexes for corruption. But yeah, anyway, maybe it's a good name actually because it's unique. But I think for large Postgres databases, everyone should check for corruption from time to time for various types of corruption and amcheck is a great tool for it. Michael: Is it access method?

Nikolay: Access method check, yeah. Michael: Yeah, makes sense. All right, yeah, good one. Thanks so much, Nikolai. Catch you next week. Nikolay: Thank you, enjoy it.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode