[00:00:00] Before we jump into the show, I wanted to let you know that in the U.S. black Friday is November 28th, Cyber Monday is December 1st and during that time a lot of sales typically go on. Well, I am putting my course on sale for a dramatic discount so it is 75% cheaper this week compared to last week for example. So the normal price of the course was $900, but the price of it from now until the end of Cyber Monday is $225. Now you don't get the same things in the $225 course as you got in the $900 course. So for example, I've removed the Q and A sessions and you no longer get a 2 terabyte database. Although the videos I record are with a 2 terabyte database, the discounted version you have a 200 gigabyte database you can use. It's essentially a self study version of the $900 course. And as I mentioned previously, I do intend to re record the course for Postgres18 sometime in 2026 and anyone who has ever bought the course including this discount will get that new recording for free. So if you want to learn more about the course it will be the first link in the show
[email protected] or you can just visit scalingpostgrads.com courses ludicrous speed postgres and this page will tell you all about the course.
[00:01:30] And it's omitted some of the differences that used to exist for the $900 version. Like for example the $900 version had multiple bonuses whereas this one just has the unlocking AI with PGvector. But if you're wanting to learn more about postgres or even if you've contacted me asking for a discount at some point, I historically have never discounted it but I am doing it for this limited time until essentially the end of the day on December 1, which is cyber Monday.
[00:01:59] So feel free to check this out to see if you're interested, but I hope you, your friends, family and co workers continue to do well. Our first piece of content is why Application developers using AI is great for DBA job security.
[00:02:16] This is from drunkdba Medium. Com and he basically has this thought if programmers of the future are AI agents, Companies will need 100 times more human DBAs to clean up the mess in production. And the main reason why is AI hallucinations because still in a lot of ways LLMs are sophisticated autocomplete and I particularly like this quote from the MIT Technology Review.
[00:02:42] It's all hallucination, but we just call it that when we notice.
[00:02:47] So essentially it's hallucinating everything it's outputting, it's just some percentage is right and some percentage is wrong. So he says, you know, when something's good enough, we call it AI magic, but when it's wrong we just say, oh, it's a hallucination.
[00:03:00] And you know, you can minimize the hallucinations, but you can't eliminate them, at least with the current technology of where LLMs are at now. And this causes a problem because LLMs tell programmers or users that postgres has particular features that don't exist, like an adaptive optimizer. And he mentions that now Oracle has one and it just happens to assume postgres has one as well. And I actually have encountered a case where someone thought postgres had a particular feature, but no, it was conflating a feature of MySQL with it and then answers failing, discussing the difference between empty strings and nulls, it mentioning the fact that postgres has time to live indexes, which it doesn't discussion about tweaking background workers and prod, which is not really a thing. LLMs adding extra work like suggesting re indexing all your indexes, setting up a 2no Petroni high availability with failover which essentially ensures you're going to eventually hit a split brain problem and then it's just going to keep getting worse because as AI consumes data on the Internet and AI is generating its own data, it's going to be feeding itself itself its own slop and its accuracy definitely won't get better and it may even get worse. Now he does say there are some areas where AI can be beneficial, like for example giving suggestions for DB parameter tuning, AI providing index suggestions. But as long as it's truly an advisor and it's just not automatically creating indexes or different types of ops automation like handling alerting on backup restore checks or TXID wraparounds or replication lag spikes. I don't know if I would want it automatically growing storage before something fills. I think I'd still prefer an alert on that case. But there are so many developers that don't know much about postgres, so it's quite easy for them to ask an LLM a question and then expect that is the correct answer when sadly, as we've seen some examples here, it's not. And here are some examples. AI generates weird schemas, terrible queries, and unsafe designs. AI hallucinates postgres features that don't exist. It amplifies bad advice from old mailing list threads. Startups implement this stuff directly in production and systems break in more creative ways than ever before. And then as a result, some of these companies are going to have to hire more senior DBAs, pay more per hour, prioritize performance and reliability and and buy support contracts they previously refused. So he sees this as a positive for job security for DBAs. And then he even includes some AI content about who can AI really replace. And he mentioned CEOs and politicians. The CEO is appropriate because AI can make stuff up and the politician is appropriate because LLMs lie constantly. Now you can change the CEO and politician's picture to whomever you choose, but I thought it was particularly appropriate. But if you want some non AI generated education, you can definitely try the course I mentioned at the start of the show and Next piece of content storing products, prices and orders in PostgreSQL. This is from cyberdeckhive and postgresql.com and I thought this was appropriate because he's imagining an AI generated data model was created from some LLM and let's say you have a product table here and then you have a sales table here. But the reference to the product Number in the first table has onUpdateCascade and on delete cascade. So that basically means that in case the product number is changed, it changes in all tables referencing the data to and if somebody happens to delete a product, all tables will be automatically cleaned. So basically when you drop a product you're actually going to be deleting sales history.
[00:06:57] Changing of prices will affect historical sales. Changing of product numbers will affect historic invoices and sales prices might not reflect the list prices. So this could put you in a really bad situation. And the recommendation for handling it is to do a little bit of denormalization. So when you need to collect the history of a particular sale, you would want to include how many units, what the price was, what the product name was, and what the product number was at that time without necessarily tying it too strictly to the product table. But check out this blog post if you want to learn more. Next piece of content last Thursday PostgreSQL 18.1, 17.7, 16.11, 15.15, 14.20 and 13.23 are released. This is from PostgreSQL.org and officially as of last Thursday, PostgreSQL 13 is end of life. So if you're still on that version, definitely upgrade as soon as you can. I know I helped a number of companies do that transition in advance of this date. Now there are a lot of bug fixes that have happened for 18 and they list many of them down here. But there are also two security issues that impact all the versions that have been resolved. The first one is PostgreSQL Create Statistics does not check for schema create privilege. So that's good. This was corrected. And then also PostgreSQL LibPQ undersizes allocations via an integer wraparound. So this can essentially cause a segmentation fault for your application. So I would say this is the higher priority one to update your client libraries when you can with these updated versions. But check this out if you want to see all the detail.
[00:08:38] Next piece of content you should shard your database. This is from pgdog.dev and he has this quote here. The best time to shard your postgres database was last year. The next best time might be this quarter. Now of course he's a bit biased because pgdog is a sharding solution. Let's kind of check out the blog post and this is kind of the story here where an organization runs into a database problem due to the statistics not being updated. And basically their system should have been optimized better given the size of their database. So they should have been running autovacuum more frequently. They maybe should have changed the default state statistics target or adjusted statistics on individual columns to make sure that things were up to date. And then if you do that, if you do update how many statistics are collected, you actually increase the planning time of queries because it has to sort through more data to be able to give you the ultimate answer. And he says there is an alternative because smaller is easier. So if your database was smaller, he's proposing 1/12 of the size and maybe you wouldn't have to increase the number of statistics or adjust the autovacuum parameters for a smaller database. It may be fine at that level. Andy says, for example, you know table writes are reduced by a factor of 12. Autovacuum has to maintain 12 times less data, queries have to search 12 times fewer rows, and making schema changes essentially blocks a lot less traffic compared to an instance size that's over 10 times as large and has 10 times the traffic. Now this may be a viable path to go, but I wouldn't consider this until my database was over a terabyte in size, frankly, because you are trading one set of work for another set of work. So for example, if you are not going to be optimizing a larger instance size then. Now you have to manage multiple server instances.
[00:10:36] So you need the automation to handle changes to 12 different database instances and potentially their replicas too. So is that 24 instances or 36? So it's just a different class of problems to deal with. You still have the same amount of data, but I guess you choose what to work on. Do you want to operationally run 24 to 36 servers versus knowing more about how to optimize Postgres for running at larger data sizes. But nonetheless I found this an interesting blog post.
[00:11:07] Next Piece of Content There was another episode of postgres FM last week. This one was on tens of terabytes per hour. And this is talking about the blog post that Nick did at Postgres AI where he was talking about using PG backrest to set up a large replica as quickly as possible. And he basically achieved tens of terabytes per hour in transfer rate by using PG backrest and multiple workers or multiple jobs to do it. So this episode discussed that they also had some discussion of running postgres on a network based storage and having access to snapshots, which is great versus NVMe storage where you get an incredible performance but you have to deal with how you want to back up your data.
[00:11:51] But if you want to learn more about that, you can definitely listen to the episode here or watch the YouTube video down here.
[00:11:57] Next piece of content Postgres 18 skip scan breaking free from the left most index limitation. This is from pgedge.com and this is talking about the new Skip scan feature. And near the bottom he actually has a performance check he did on 17 versus 18. Although what was weird about this performance comparison? In Postgres 17 he's doing a sequential scan, but then in Postgres 18 he's doing an index scan. So naturally it's going to be faster.
[00:12:26] So I wonder how much of sequential scan versus index scan for the skip scan. But if you want to learn more, you can check out this blog post.
[00:12:33] Next Piece of content Data Retention Policy Implementation how and why so this is talking about the importance of data retention and how you should have a plan in place for when you want to expire your data, because there's a lot of regulatory and legal implications for how long you can store data.
[00:12:51] And then is this data really going to be used anymore anyway? Now you can just purge the data data or delete it, or you could archive it and anonymize it to retain historical data, but just anonymize particular records. And in terms of the implementation, the easiest way to do it is of course do table partitioning. Or you can also just do deletes, although that does have an extra burden on the system. But if you want to learn more about this, you can definitely check out this blog post. Next Piece of Content Performing Standby Data Center Promotions of a Patroni Cluster this is from percona.com and usually patrony does automated failover for a given cluster, but that is in one data center you can actually define another replica cluster in another data center. But in order to promote that particular data center's cluster, you do need to do it via a command and they talk about that and how to set it up. So if you're interested in that, you can check out this blog post and Last piece of Content RegressQL I think regression testing for PostgreSQL queries this is from boringsql.com and this is a tool that lets you do regression testing of your SQL queries.
[00:13:59] It basically lets you write tests, point it to an endpoint, and then tell you if all those queries ran.
[00:14:06] For example, you could do a schema change and ensure that you don't have any regressions. I don't think this is a tool I would necessarily use because I think I have other sufficient testing in my application. But if you want it specifically for SQL, you can definitely check out this piece of content and new tool.
[00:14:23] I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content mentioned, as well as sign up to receive weekly notifications of each episode there. You can also find an audio version of the show, as well as a full transcript. Thanks and I'll see you next week.
