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

Schema design checklist

Apr 17, 202645 minAudio

Nik and Michael discuss a list of things to check when designing new schema in Postgres. Here are some links to things they mentioned: Use BIGINT in Postgres (blog post by Ryan Lambert) https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default Postgres 18 and UUIDv7 (blog post by Gwen Shapira) https://www.thenile.dev/blog/uuidv7 How to use UUID (how-to guide by Nik) https://postgres.ai/docs/postgres-howtos/schema-design/data-types/how-to-use-uuid Our episode on constraints https://postgres.fm/episodes/constraints Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown MultiXact member space exhaustion episode (with Metronome) https://postgres.fm/episodes/multixact-member-space-exhaustion Our Column Tetris episode https://postgres.fm/episodes/column-tetris Saving Space Basically for Free (blog post by James Coleman from Braintree) https://medium.com/paypal-tech/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9a Over-indexing episode https://postgres.fm/episodes/over-indexing Under-indexing episode https://postgres.fm/episodes/under-indexing Gadget's use of Postgres https://postgres.fm/episodes/gadgets-use-of-postgres Partitioning episode https://postgres.fm/episodes/partitioning RLS vs performance episode https://postgres.fm/episodes/rls-vs-performance ~~~ 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, this is PostgresFM. My name is Nik, PostgresAI, and as usual with me, Michael, pgMustard. Hi, Michael. Michael: Hi, Nik. How are you doing? Nikolay: So the topic... I'm doing great, how are you? Michael: I'm good, Thank you. What's the topic? Nikolay: What's the topic? The topic I chose, and everyone already saw it on cover you created, right? So the topic is 5 or so, maybe 6 things you should check when you start new schema or you already have some schema and your

project is growing and you want to be better prepared for bigger scale and performance wise. So what, especially if you use AI to design and improve your schema and your project is growing. So what should you look at to check your schema health? Michael: Makes sense. Just on the topic in general, I do think there are some things that are better suited to asking for help from AI and not?

Do you think like schema design is 1 of them or actually is it 1 of those ones that maybe we should still do it a little bit more ourselves at the moment? Nikolay: Well as you know I'm very very very intensive user of AI. Yeah. Honestly, like I do everything through AI right now. And I'm 100% schema design is a topic where AI can help a lot. But it also matters which questions you ask. If you just say, I want this and don't pay attention, you will

have low quality schema in many cases still. That's why I said, let's have this episode because it might be quite basic for many advanced users of Postgres, But this checklist we are going to present today, it can be good thing to keep in mind always when you design schema, just to ask right questions and revisit. Also, I'm not sure how exactly you use AI. I use it always like I work with people, developers.

When you just ask something and think those engineers, humans or AI, will solve all your problems and make all the decisions, you're offloading full responsibility to their shoulders, so then you have big negative surprises always, right? So it's better to keep requests as detailed as possible when you work with AI, and also make some iterations to review and improve. And when making iterations, like first, like I want some schema,

like, I don't know, like LinkedIn social media schema or something, right? It will give you immediately something, but then you want some details to be checked. And this is exactly when this checklist can be very helpful. So you can revisit and think and imagine when you will have gigabytes and terabytes of data, what will happen. So to avoid painful refactoring in the future and outages maybe or performance degradation, the price you can pay in the beginning

is much lower than the price you pay later. So I never work with AI in 1 shot, I always make iterations to improve in any question and to revisit and sometimes combine multiple LLMs. This is exactly when you can use this list of questions. So first topic is the data type of primary key. Michael: Yeah, and I feel like the answer to this has changed, or my opinion on what the best default for this has changed,

actually, in the last few years, just off the back of learning more about timestamp ordered UUIDs, right? This used to be an age-old debate, right, UUID versus integer or big integer really. And most of the kind of blog posts I would see are like trying to just encourage people to use BIGINT over int. But now that we have first-class support for time-ordered UUIDs, I don't see many reasons not to go with that.

What do you think? Nikolay: Well, the size, 8, is double. Yeah. Yeah, your ID is 16 bytes, so it's quite a big price to pay. Each row, you need to have 8 more bytes. It's not that much, is it? In many cases, it's fine. Well, indexes will be also bigger. Indexes double, yeah. It might make sense if volumes are huge. But anyway, to keep things simple, a rule to remember, red flags, If you are prepared for really large volumes of data, it must

not be int4, it should be int8. And in terms of UUID path, data type is always UUID for both version 4 and version 7. But you can check default how it's generated. And in modern Postgres, there is a function, I think in 18, only in the latest version. I think Michael: so. It didn't quite make 17, did it? Nikolay: So if you're on Postgres 18, so it's better to use UUIDv7

but also remembering trade-offs, because there is timestamp there, and the values will be ordered, like with integer or big integer. So there is a nuance here. But performance-wise it's much better than UUIDv4. There are many articles about it and you just need to check, okay, are we going to go with integer? Then it's int8 or BIGINT, right? Are we going to go with UUID? Then default generation, default should be version 7 for Postgres

18. For older version, it's also not an excuse to stick with version 4. You can write your own function. And you ask AI to write your own function. Michael: Yes, or generate them outside of the database. Nikolay: I slightly prefer this option less because if you have multiple application nodes, Who knows what will happen with their clocks? And since UUIDv7 includes timestamp inside it, order might be not, I don't know, like I prefer to leave it on database shoulders

to have like on the primary single source of true. On the other side, UUID by design is generated, it serves purpose to work on multiple, like on distributed systems, right? So maybe it's not a big deal. Generate on client side. Michael: All I mean is if you're on an old Postgres and it saves you creating your own function or whatever. Nikolay: Postgres waited a couple of years to bring UUIDv7 officially. Although again, you can create even SQL function, even not PL/pgSQL function, simple SQL function.

And if you ask your AI to search for example, probably it will find my page how to do it, but or other people's pages. But in other libraries from Google, from Node.js library, everything, they have it for a couple of years already, so for longer. They implemented before standard was finalized. Michael: Yeah, so huge red flags to look out for if it's using the integer data type, which is the same as int4, or serial. I think AI sometimes uses, it will define it as serial.

So if you just see that word without bigserial, you might hit that limit at 2000000000 or whatever the, I can't remember the exact upper limit. Nikolay: Yeah, and avoid UUID version 4 if you want good performance on large volumes of data. Yeah, that's it actually. Let's keep it simple because we could discuss more advanced situations. Michael: I think we should do a whole episode someday on migrations, like for people that are getting close to that 2 billion Nikolay: number, that Michael: would be a good topic. Nikolay: I can share, I had a lot of experience with different

approaches. Let's do that. Good. So how to define primary key? I can actually have an article about it, how to... Cool. Michael: Let's do that. Nikolay: Covering multiple paths so we could talk about this. Number 2 is constraints. Topic is constraints. Michael: Other constraints. Nikolay: Yeah, yeah. Yeah, other. Beyond primary key. Yeah. So I would just ask, first thing, are we using constraints well? Like to myself or AI and so on.

And because Postgres has 6 types of constraints and it's quite rich tooling, and it's much more convenient to have them earlier, not because adding them later is a big pain. Right now we have quite good support of two-step constraint definitions. I think in Postgres 19 it will be possible also if not null constraints, which is interesting. But anyway, check constraints, foreign keys, unique constraints,

it's possible to create first in almost finished state and then finalize it to avoid the long-lasting locks. But this is not the reason why I prefer to think about it in the very beginning. The main reason is the quality of data. This is why constraints exist in the first place, right? So if you introduce constraints early in your project, data quality is good. Introducing them Michael: later probably will... Yeah, and database side as well.

I mean, it just... Nikolay: Oh yeah, yeah. I don't trust constraints outside. With age, I became less radical. My points of view became less radical with it. So I just observe without fighting, like with backend engineers who try to implement constraints on application side. I just have a note, like they are not reliable because who knows which other layers of types of applications, especially these days with AI, it's so easy to add something else, implement it

in a different framework or even a language, it's super easy right now. And you will need to reimplement those constraints, or you will just forget and miss them and data quality will suffer. Michael: So specifically, something I think I'd like to see more of or at least I see a lot of people not doing is columns that you're not expecting to put any null values in, or you don't want to have any null values in, actually explicitly making them not null. Like, if you don't specify, then columns are nullable.

And I think most people, or at least most AIs I see, are creating, like by default, they don't put NOT NULL on every column. Nikolay: Yeah, there's an opinion that NOT NULL should be default. Anyway, it's hard to have a default, but inside your project you can say, okay, unless it's explicitly specified, let's make columns NOT NULL. Exactly. And avoid troubles with NULL we talked about in a special episode about NULLs, right? Michael: Yeah, well and I guess in this specific case there is

also, well with some constraints, mostly we care about, we're doing it for quality reasons, but there is, there is like a tiny amount of performance optimization that comes out of them. I guess a bit more for unique constraints, like if you, if the database knows for sure this is going to be, this is unique, it can do some optimizations. So yeah, anyway, but yeah, good point about, I like that about NOT NULL. Nikolay: Yeah, yeah, unique is interesting stuff. And also, I don't think foreign keys are underused in Postgres

ecosystem. Days when everyone suggested that foreign keys are super bad, for me it's 20 years ago, I remember these topics, let's avoid foreign keys completely. Although we know that foreign keys can bite badly, we had an episode with Metronome company about issues basically with MultiXact related issues which can be caused when you use foreign keys a lot. But anyway, I still think foreign keys is a good thing to have, data quality. But I think what is usually underused is check constraints.

They are great. They could be used much more often. And for example, a classic example, when I say, like, when I see in schema ENUM data type, I think, okay, later, If we want to change this list of possible values, how will our migration? Michael: Especially removing any. Yeah, Nikolay: so check constraints are slightly more flexible. Well, Also removing is a topic, right?

But check constraint you can introduce in 2 stages. Again, you can drop the old 1 and you can first create a new 1 in NOT VALID state and then validate it in a separate transaction. But I just see it like quite useful, quite convenient tool for me and you see it in schema right away like this this column should be like for example in this range or it should be this list or something. So I wish it was used more often.

Michael: Yeah. What do you see in... Going back to foreign keys quickly, do you see a lot of the LLMs doing a lot of things on update cascade, on delete set NULL, things like that. Do you see any cascade stuff popping in there by default or not when it should be or when you think it Nikolay: should be? It's a good question. I actually don't remember having issues with this question. I know deferred constraints can might be an issue. I remember a case when pg_repack, it was a company called Miro,

maybe you know it, right? They had issues with pg_repack and deferred constraints. There is an article, old article from them about this. But I don't remember particular issues here, and I think this leads us actually to the question for our particular projects, what's better? And If I had any doubts with AI, I would just make some experiments. Based on the product we are developing, I would say, okay, for example, in 1 year, how many of entities in which entity table

we expect? All other tables, let's fill it with fake data, run vacuum Analyze to have good state, and then just explore plans with pgMustard, for example. Let's think what kind of workload we might have according to user stories in our specification of the product and so on. And then collect plans and see, right? And then include also modifying queries and collect those plans and think how it will work.

It's so easy these days to make these experiments before we finalize all the decisions and then you will see it in action and it's great. So collect all the plans, find like weak spots where we have suboptimal plans and how we can deal with this. If, for example, we have entity and another, and 1 too many, and it's many, so many, right? And then we want Delete to be supported in UI, where people, as we know, expect 200 milliseconds, less than 1 second, let's

say, right? And when this Delete of 1 row triggers propagating according to foreign key triggers, deletion of 1000000 rows, it cannot meet our requirements, right? Now we need to think about asynchronous deletion, maybe using some event queue system or something, right? Yeah. And this will pop up if you prepare a good experiment here, right? Without experiment, it's hard to say. You need to answer questions. How big are the tables and how this relationship between tables

on foreign keys will be in the worst case, for example. Can user create like million of entities and then we need to delete this user, for example. This is a great question and with AI you can iterate so fast these days. So great. Collect plans and then think and decide. Okay, it's a good question, but I don't see it as a simple question. Michael: No, no, no. It was more, I just asked in case you'd seen there was a chance that LLMs were throwing it in on every foreign key and That was

maybe a bad like bad deal Oh, but it sounds like they're not Nikolay: maybe a wrong person to ask about this because I don't just I design sometimes these days I have a few applications designed Yeah created from scratch with AI but I mostly deal with things which other people already created, and I just see how AI can help. So I'm not sure will this propagation of deletion, for example, will be supported in how exactly it will be implemented. But it's definitely worth thinking in the context of latencies

and what should be offloaded and what tools we'll be using to offload in this, to background jobs, for example. It's just a thing that you need to, instead of thinking, looking at code, I would think looking at dynamic results, experiments. Michael: Yeah, and I'm thinking, like if we're talking specifically about schema design when you expect to hit scale, I might default

to not using ON X CASCADE just by default because of those, because at scale it can become problematic so that would probably be, if you had to come up with a best practice for if you're designing at scale, maybe lean in that direction. Nikolay: That's interesting, that's interesting because This was my position for a long time, but it's not about AI building or something. This was my position. Let's, like, it's dangerous because who knows how many rows we will need to delete.

Like, it's like it's unbounded delete, right? Unlimited delete, This is how it feels. Let's design without it. But then I see in my consulting practice, I see cases where really large projects have it and it's just not 1 project. Many projects I see and they keep it and somehow live with it. They at some point implement non-cascaded, like asynchronous deletion, but by default they still rely on cascaded deletes somehow and they are fine. This is interesting.

So reality shows it's not that bad. But I agree with your way of thinking and this is my default way of thinking as well. Postgres can survive very terrible things these days much better than 20 years ago. Including the revolution of 100 years... Michael: The only time I see that being a horrendous issue, even in small to medium-sized projects, is if people haven't indexed the Nikolay: second part of foreign key. Michael: Exactly, the referencing column. Nikolay: There are 2 ends of foreign key.

1 is always indexed because it's primary key, the other 1, it's your job to index it, right? To not forget. Yeah, yeah, yeah. We have now a checkup, old checkup, actually. We have a special report just for this case, yeah. Good, but it's all good questions, but I feel we can spend a lot in the Constraints area. I think we had Constraints episodes. We did Michael: have an episode, yeah. Let's link that up and move on. Nikolay: Yeah, let's move on. Some entertaining topic, Column Tetris, which is sometimes it

might bring some quite interesting and good savings. In many cases it won't, but I just see people enjoy this topic, and I decided to include it. So the idea is, when you design a new Schema, the price to pay to reorder Columns is very low. You can reorder it easily. The problem is that for Postgres, physically, the Column order matters. Due to padding alignment, if int2, for example, is followed

by int8, you will see a gap of 6 bytes of zeros in every row. And this is bad. It's like a waste of storage and also memory, which is not only disk, it will pollute with those zeros, it will pollute memory. And sometimes you can be so unlucky, I saw cases like 30% of each row is like zeros. It's, And Postgres doesn't have automatic reordering of Columns. It could, actually, but it doesn't have it.

Michael: So the order you define it in, let's say you do create table, the order you put those Columns in, that's the order they end up on disk. And every new Column you add goes at the end. That makes sense, right? But even if it could be slotted in, let's say you add a new Boolean type, and it could, I don't actually know if that would work, but it always goes on the end. Nikolay: And there's no way to sum up. Boolean is 1 byte. Michael: Yeah, but I wonder if that would work. Nikolay: In Boolean, 7 bits are wasted already. If you zoom in like under my

Michael: let's put brilliant brilliant brilliant next to each other right Nikolay: no no no no no Boolean Michael: really Nikolay: is 1 bit. Michael: Oh bit. Yeah, good point good point, so yeah, Nikolay: But it's stored as a 1 byte. 7 bits already wasted. But if you have Boolean and then timestamp, for example. For example, first column, as we all agree, should be id, the primary key. I'm joking, it's not always, but it's very popular. Id is the first column, int8, occupies the whole 8-byte

word. And then we have Boolean and then timestamp. Not only you wasted 7 bits inside Boolean, it's already by design, like Postgres doesn't have less than 1 byte. But then you waste 7 bytes, so 7 bits first and 7 bytes because of this alignment padding. And your Boolean, which is like, by sense, it's only 1 bit, suddenly occupies 8 bytes. It's a huge waste of resources, right?

But the bigger picture here is that people usually realize that waste is significant only when it's a huge table, like 100 million rows, right? And then, oh, so many spaces wasted because we didn't play this column Tetris initially. But this is like chicken or egg problem, maybe inverted. Anyway, so you realize it later, but you could do it earlier, but you didn't think about it because when you have only 1,000 rows, for example, it costs you nothing.

But again, with AI, it's so easy. You just say, let's play Column Tetris in Postgres. AI should know it. I haven't tried, but I'm pretty sure it should know it, because there are many articles about it. Michael: Oh, and we did an episode. Nikolay: Yeah, starting from Stack Overflow, where this topic was very well described. So AI should know it, this topic. When you create a new table, you think many rows will be stored, just let's apply column Tetris to this. Boom. It will reorder columns.

Never try it again. Michael: Yeah, what do you think, like I feel like there's a... I don't know if I'd still want to have, I'd still want to do it myself just because I feel like there's also a natural order for columns. So I would want to put all the 8-byte ones together and 16-byte ones in to make sure they're all with similar and then all of the... Yeah, exactly. Nikolay: Ah, similar. Michael: But equally, like, imagine if it's like a users table

and I could put the I could put certain information about that user that makes sense to go together together and then information about something like maybe like there all the IDs that maybe the foreign keys like their team ID or their organization, all those kind of like referencing columns, put those together. So I know the order doesn't matter. I know you could just specify column order, but you know when you do SELECT * or you just... Nikolay: This is what I was trying to say, like, are you trying

to tell me that you're using SELECT *? And here we go. So I'm joking, actually. I'm using SELECT * all the time. Also all the time I see articles how bad it is to use SELECT *. Right? It's convenient, I know, yeah, but also if you manually work, it's inconvenient to have order and SELECT * doesn't work well. But applications shouldn't use SELECT *. Michael: You're right.

And I'm even thinking about examples like the system views in Postgres. Like you do SELECT * FROM pg_stat_statements or something. But that's not even the table, right? That's the view. And then they can define the order. So it doesn't actually matter. Nikolay: Yeah, it's an interesting topic. I played Column Tetris a few times heavily, and then it was really inconvenient. Oh, this is an ugly table. It looks ugly. Like, this order is ugly. But at the same time, it's your problem that because you use SELECT *, which is considered like not good practice.

It's practice for exploring and so on. Okay, explore with AI, ask to provide better order or something. I don't know. But anyway, if you talk about, if you focus on performance, again I saw cases where it was significant waste. Michael: 1 of the first articles I saw and it was by Braintree and I think they reported an average of about 10% on some of their largest tables. Nikolay: It depends. Michael: Obviously it completely depends but that was quite surprising to me. I know you mentioned an extreme case where you saw 30%, but I was quite surprised that in an actual production case, it was

as high as 10%. And when you're talking about hundreds of gigabytes or terabytes, That's actually a decent amount. Nikolay: Yeah, yeah, exactly. The problem also, of course, in existing projects which were developed during many years, usually it's not so simple because the schema is evolving during many years and you cannot insert your new column in the middle. It's always, as you said, at the end. So sometimes we do need to, for example, to change the order using some redesign thing.

By the way, have you heard that Postgres 19 will have repack and also repack concurrently, which is basically vacuum concurrently. We had an episode with author of pg_squeeze. It's a vacuum full concurrently. Exactly, exactly. Not vacuum concurrently. But the decision was made to name it Repack concurrently. Michael: I think that's sensible, getting away from people thinking it's vacuum related, yeah. Nikolay: I haven't looked into details, but naturally this is

the point where you could reorder columns, because it's based on logical replication. Michael: Yeah, I suspect if they do that, it will be a different keyword again. Nikolay: Who knows? Again, I haven't looked into details. There are long discussions on pgsql-hackers mailing list. It just feels naturally, because it's based on logical replication. This is where you can reorder columns and play Tetris for existing large tables. I hope it will be implemented at some point. Maybe it's already partially implemented or something.

I don't know. You basically, before you start filling the new table, you can reorder it, because it's logical, right? It should be possible. Anyway, this is content, it's an entertaining topic. Let's move on to a more serious topic called indexes, index chaos. If you just use AI and if you don't spend enough time for things as I said like experimenting with real data benchmarks you might end up with under-indexed situation or over-indexed situation

And usually people are scared to have under-indexed. In my practice, I see over-indexed very often. Sometimes to extreme. Michael: I guess for new projects, I would be surprised to see them over-indexed. But for- it's Nikolay: possible, it's possible still. Michael: Is that like as a result of AI or something else? Nikolay: Well, the problem here is that, again, everything depends on your prompts, depends on angles. If you say we will need to order by arbitrary column, right?

Remember our episode with CTO Gadget, Harry, right? So any column we can reorder. Naturally, AI might decide to put index on every column, right? But is it really Michael: what you want? But that's what Gadget decided to do as well, right? Nikolay: Right, but with understanding consequences, right? But in reality, will it be used If 1 year later you see that 90% of those indexes are unused and the gadget has special case

They have many applications to support if you design your own only 1 application This is where I would just stop and think with AI again with some experiments I would ask questions like this all depends on workload, right? Which queries we will have, right? And I would just think, which queries we really need and which we won't need, and then choose a proper minimal set of indexes.

Michael: I like that. I think there's also like some rules of thumb we can give people in terms of like how many is where it starts to get dangerous for other reasons. But the kind of 1 place where it's not even and it depends is overlapping and I see more and more where people are adding indexes based on AI suggestions. They're adding indexes they've effectively already got or they're adding ones that overlap with indexes they've already got, and therefore only 1 of them is needed.

And by the way, it won't always show up in your stats, it won't always show up as an unused index, because it is still using the smaller index. Nikolay: It will show up in PostgreSQL stats because it's called in our case redundant index in Postgres monitoring systems and CheckUp. I never heard the term overlapping. I see people talk about duplicate indexes, which is like trivial case, exact same definition. But we call it redundant indexes. It's not an easy topic as 1 might think, but we solved it in

many parts. Over years, we have quite a good detection approach, how to reliably detect redundant indexes. And as you say, this is true, in many cases all of those indexes are used. And it's quite like, there is a big fear, oh, I'm going to drop this, it's used, should I drop it? Yeah, but the reports we have quite reliable and of course I wish we had very native standard algorithm to disable indexes

for quite for some time like without involving HypoPG and hypothetical indexes, which currently supports it. Michael: Our last episode was on what's missing in Postgres, and I think we got a nice comment, I've forgotten who from, but saying 1 of their features would be make this Nikolay: invisible. Temporally hide index from the planner. There are extensions for it, HypoPG supports it, but we want some mechanism beyond index setting, and index is valid to false.

We want this. Yeah, yeah, yeah, I agree. And this is a good thing to probably work on for those who want to start hacking Postgres so it's a good thing to officially implement something. Anyway, questions to ask is, they all start here, should start from understanding workload. So we cannot think about indexes without understanding workload. So first we need to generate some fake data and then start thinking about usage patterns.

And without AI It's really hard work. It's a lot of time. With AI it becomes easier. If you have user stories defined already, you understand what kind of patterns we will have, and you can start imagining. It will not be 100% accurate, but it will be good enough, much better than without it or with manual work. So you can iterate here and see, and then with queries in hand and some like lab already developed, I see it should take 1 or

2 hours for like medium-sized project I mean in terms of complexity and then you can start iterating collecting plans and think which indexes you can have playing Michael: But also this whilst this might be the most important in terms of performance that we've talked about so far it's also the the easiest to change later right we can yeah yeah we can set up a certain set of indexes and later decide we want a slightly different set of indexes and that's not super painful in a lot in most cases especially if there's no partitioning involved

so that for me feels like yeah it's important it's really important But if we don't get it right on day 1, it's much, much easier to fix than the primary key being the wrong data type or not having the right constraints in place, that kind of thing. Or even the column order, yeah. Nikolay: So the point is you should build like wind tunnel for your project, right? Like, you know, like, Test lab, yeah. Database lab. And then you should put this workload like wind and see like

how your profile of your database behaves under this wind. Michael: Yeah, a lot of people do that with production. They just do it with their slow queries. Nikolay: But with AI it's easier to have massive experiments before you finalize all the decisions. So my point is do it. And then questions to ask, are there any indexes we don't need in terms of they are redundant or unused or what indexes are missing because we have bad plans and this again like pgMustard will visualize it and so on and you have API already, right?

Michael: Yeah Yeah, it's quite nice Nikolay: You can connect your AI to pgMustard for example and ask to use pgMustard to explain what's happening and find bad problems and missing indexes with this lab environment. Great? Michael: Yeah, OK. Going just like to the extreme, How many indexes on a table would you automatically just think, oh wow, that's like a lot? Maybe it's justified, but I'm already thinking that's too many.

Nikolay: These rule of thumbs are quite weak in my opinion, but we have them anyway. Like for example, if the volume of indexes exceeds the volume of data, it's already some bell is ringing. Or before Postgres 18 with this nasty lightweight lock manager problem means I don't want more than 15 indexes per table. Michael: Because of the 16 relation limit. Nikolay: So simple, primary key lookup which quite likely will be very, will have quite high QPS will suffer, and because of

locking. So there are a couple of kind of these rules, right, but they are not strict and again I say that I consider them weak, but maybe... Michael: But I think it's still helpful, right, if you've got a schema designed by AI and it's got 20 indexes on 1 table, maybe consider is that smart? Nikolay: But it happens, index data exceeding heap data it happens. Michael: Especially with some, especially like if you're doing

gin indexes. Nikolay: Oh yes. Michael: If it's just B-trees, yeah. Nikolay: Yeah, okay, great. So the final topic, we couldn't decide on which 1 to choose. We had 2 ideas. 1 is RLS and another is partitioning. Let's touch both maybe. Michael: Yeah I think so. So from an AI perspective, like when you're asking it for a new schema, do you see partitioning come up? Does it over partition? Nikolay: It won't come up Unless you start saying I'm going to

have a lot of data here and I want good performance, I want billions of rows stored in this table, it should be maintainable. It will naturally come to the idea of having partitioning. Partitioning will be interesting to join with UUID version 7 or 8, it doesn't matter. It's possible, again, I have a recipe for this in my set of how-tos. And it can be also the question, like partitioning you cannot develop without understanding workload again.

It's similar to indexes, like it will be something, but how it will behave, who knows? Maybe you will end up, your queries will need to scan all partitions, which is terrible in most cases. And then plan behavior. Anyway, the question is to ask, like you said requirements, I want a lot of rows and I want well maintainability of this. Help me. Michael: Like deleting old data, are you thinking? Nikolay: No, creating indexes, for example, or reindexing. Michael: OK, yeah, sure. Vacuum. Nikolay: Vacuum, yeah, yeah.

These are problems biting us much more often and badly than just, I don't know, like, we talk about this a lot, as well, right? Michael: I know, I know. Nikolay: Yeah. Direct performance benefits from partitioning are good, but also the problem when creation of index or full table vacuum, it will take hours or half a day, it's already so painful. Especially if it's to prevent transaction wraparound, or if it's,

again, indexing means that xmin horizon... Like anyway, let's not go too deep. The question to ask, will we survive n number of rows because we expect all the data. I want- Michael: And just out of interest, is it pretty much always doing range partitioning based on timestamp type? Is that Nikolay: like- Again, I'm the wrong person to ask. Michael: Okay, yeah, fair Nikolay: enough. In many cases, I just use to still use TimescaleDB if it's time-based. Michael: Makes sense.

Nikolay: And I'm happy because there is compression there, it's great. But I also saw cases when it was decided, not AI, but it was decided, like this partitioning and full control over partitions, special table created. It was also working well and served needs, but on surface, you should think about workload and then push your AI or something to think about how it will behave if you have that much data. Partitioning is inevitable there. And then experiments again. Even experiment with 1000000000 rows, it won't take a lot, just

allocate machine and do it. It's like just so easy, right? Half an hour of wait to generate it, or more like even if it's 1 hour why I don't understand why people don't do it all the time They come to they come to us with questions, which I'm grateful. Thank you with money and so on But it's so easy just to experiment more often these days Michael: Yeah, even when it even leave it running overnight Nikolay: Yeah Michael: Yeah, I'm still I'm still in the fence I love partitioning

for the reasons you mentioned, But I think so many projects won't ever need it that I understand not doing it by default with a new project unless you know, unless it's like a new feature for an existing system that you know is going to get heavy amounts of data really quickly. Just there are so many trade-offs like that it's still a little bit like... Nikolay: Which trade-offs? Michael: So for example indexes, being able to create indexes concurrently, delete, drop indexes concurrently. Nikolay: You can concurrently create index on each partition

and then yeah it exists on each partition you can create this these things can be automated and of course I like to be living much more better automated there is a big potential here but also every Every Postgres version gets a lot of improvements in this area, last maybe 10 years already. So it's not like frozen. Yeah, but also make like, Michael: just being extra careful that every high frequency query

you have contains the partition key and is getting pruned at planning time like there are a few gotchas Nikolay: that hit you worse. Michael: Yeah there are, Nikolay: I have a series of articles about this and I went quite deep there and it was crazy yeah. Michael: But that's my, they're my hesitations. Nikolay: Creation Michael: of foreign keys. Nikolay: That's why I'm hesitant for example working with timescale timescale DB something like often we just Abandoned idea of having foreign key because yeah, it's important If you do partitioning

yourself, you will deal with, if you have large tables, adding foreign key between them. If partitioning is involved, it's an art, I would say. And GitLab mastered it. They have Migration Helpers RB. This is a great source of experience of many people involved and I can't stop recommending how great it is because it's open source. It's a great place to look at and also they have great documentation about it. But anyway, it's a thing to decide. If you don't expect billions of rows, don't do it, of course.

At the same time, we have also very sad cases when people come to us for consulting. We identify problems, we say partitioning is needed. Sometimes we go and spend a lot of effort because the project is huge or there are many databases to take care of. And then finally it's implemented great. There are many hidden dangers to explore. Mid-journey, for example, they came to us, helped, forgot about lightweight lock, well, not forgot, we didn't know about that

at that time, and they hit it, lightweight LockManager problem was hit badly, and there are articles about it. But then some projects just say, okay, they evaluate how much effort it is to deal with partitioning when tables are huge, and start talking, okay, maybe we should migrate to my MongoDB. Michael: Yeah, Or even sharding, right? Like instead, I think when we talked to Notion, Arka, he mentioned that because they keep their shards relatively small, they've

opted to never partition at all. Nikolay: Yeah, the need for partitioning diminishes. I agree with this, but not vanishes completely. Michael: What, maybe? Nikolay: Not Michael: maybe. You mentioned, it depends how small you keep your shards, right? Nikolay: Yeah, yeah. Michael: Like Sugu, for example, Multigres, he talked about having lots of smaller databases being much easier. Nikolay: Yeah, yeah, yeah, yeah. Maybe you're right. Michael: Okay. What about RLS? You mentioned you wanted to touch on Nikolay: that. Yeah, finally, RLS, if you, for example, I just noticed it's

not like Supabase is very heavily promoting RLS. They have a lot of articles including how to avoid problems. But if you just use AI, I noticed it often adds RLS. Michael: Oh, really? Nikolay: Yeah, I just... Interesting. This is how it works. I just noticed I had a couple of cases where suddenly it brought like, we are going to use RLS. It's multi-tenancy here, let's do it. Okay, let's do it, but let's also pause and think about performance and avoid problems like current_setting inside a loop and you

SELECT count of million rows suddenly becomes super slow. So the question to ask AI, again, benchmark is ideal here. With RLS, without RLS, what is the overhead of having RLS in our particular case? Let's benchmark it. And if you see it's not okay, then let's optimize it, because there are tricks how to optimize it. So if I notice an AI-generated schema with RLS, I would ask question like, what will be performance impact?

Let's benchmark it, and if it's bad, let's optimize. That's it. Yeah. Great. Great. That's it, actually. I think Michael: that's a useful checklist, actually. Nikolay: 5 or 6 things to ask your AI when you design schema. Michael: Yeah or your colleague even. Nikolay: Yeah also or your own schema which you have already 10 years, why not. Michael: Yeah exactly or even a new table if you're doing a new feature it still makes sense that all of these make sense. Nikolay: I agree.

Cool, thank you for listening. Yeah, see you soon.

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