Subscribe
Share
Share
Embed
Today, we’re joined by Benedikt Deicke, co-founder and CTO of Userlist. He talks to us about why naming things and cache invalidation are hard, why it’s a good idea to denormalize your data sometimes, and more.
- Userlist
- Real Time Analytics with PostgreSQL
- Follow Benedikt on Twitter
- Follow Userlist on Twitter and LinkedIn
- Visit Benedikt’s website
Chapters
- (01:28) - What is Userlist?
- (02:10) - Userlist’s tech stack
- (04:40) - You don’t always have to normalize your data
- (12:27) - Naming things is hard
- (14:01) - Cache invalidation is hard
- (18:28) - How Userlist maintains data accuracy
- (29:24) - How Benedikt developed their real-time reporting engine
- (32:49) - Database consistency
- (37:14) - Benedikt’s tall tale about deleting large data
Sponsor
This show is brought to you by Xata, the only serverless data platform for PostgreSQL. Develop applications faster, knowing your data layer is ready to evolve and scale with your needs.
About the Hosts
Queen Raae wrote her first HTML in 1997 after her Norwegian teachers encouraged her to take the new elective class.
Around the same time, Captain Ola bought a Macintosh SE for his high school with the proceeds from the school newspaper he started.
These days, you’ll find them building web apps live on stream and doing developer marketing work for clients. They are both passionate about the web as a platform and the joy of creating your own thing.
Visit queen.raae.codes to learn more.
Creators & Guests
Host
Host
Guest
What is Data in the Wild?
Learn from your favorite indie hackers as they share hard-earned lessons and tall tales from their data model journeys!
Brought to you by Xata — the only serverless data platform for PostgreSQL.
[00:00:00] Ola: Welcome to Data in the Wild. Discover data model tips and tricks used by our favorite indie hacker devs. With Queen Raae! I'm your co-host, Captain Ola Vea, and this podcast is brought to you by Xata, the serverless data platform for modern web apps.
[00:00:24] Benedicte: And today's guest is the great and powerful Benedikt Deicke, co-founder of Userlist. Welcome to the show, Benedikt.
[00:00:31] Benedikt: Hello. I'm glad to finally be on.
[00:00:36] Benedicte: And did I say your last name right? I just realized I've never said your last name even though I've known you for a while.
[00:00:41] Benedikt: It's fine. It was completely wrong, but it's fine. It's actually Deicke.
[00:00:46] Benedicte: Deicke! I knew it. It's just weird
[00:00:48] Benedikt: Deicke.
[00:00:50] Benedicte: I just never thought about your last name really since we have the first same first name. I've been focusing on that.
[00:00:56] Benedikt: Yeah. I know the feeling. I mean, especially with internet friends, right? We usually just read and write a name. You never think about how to pronounce it until you're on a podcast and want to mention that person you're like, "That guy I know."
[00:01:11] Benedicte: And then sometimes you only know their Twitter handle, which is like, "Oh, you should definitely talk to like, you know," and then it's @uibreakfast or something, you know, something like that.
[00:01:24] Well, that's, there's a name behind there somewhere.
[00:01:27] But anyway, let's get into it.
[00:01:28] Just to lay the groundwork for people, what problem does Userlist solve?
[00:01:35] Benedikt: Userlist is an email marketing platform for software as a service applications. So what we do really well is like integrate with your application and you send us data about your customers, your users, what they do inside your platform and obviously their email address and stuff like that.
[00:01:52] And then inside our tool, you can either send them a broadcast, like just announce a feature or something like that, or even set up like pretty flexible automations based on stuff they do in your application or don't do in your application. So that's the core value of Userlist.
[00:02:09] Ola: Okay.
[00:02:10] So before we get into your experience with data modeling, could you quickly run through your tech stack?
[00:02:17] Benedikt: Our tech stack is pretty simple. We are a Ruby on Rails application in the backend. Our database is PostgreSQL. We also use Sidekiq as our background queue job scheduler and our frontend is based on EmberJS. So the backend is a API only application and the frontend talks to the API, basically.
[00:02:46] Ola: Cool.
[00:02:47] Benedicte: So what data models has changed the least for you since launching Userlist?
[00:02:52] Benedikt: That's a good question. I feel like we don't have that much churn in our data models. Like in, in terms of that they change a lot. The thing I noticed is that the overall strategies on how we deal with data or like group data or data models together.
[00:03:10] That's something that's evolving constantly and has some things in there that I regret not doing early on. For example, like organizing data by customer account and stuff like that.
[00:03:23] Benedicte: You were saying this has changed?
[00:03:26] Benedikt: Yeah. Like this is the part of the data models that's changing.
[00:03:29] Like the data models themselves are pretty stable, but if you go into database modeling theory a little bit, like they always tell you, "Normalize your data as much as possible." Like just keep it in one place, have relations between them and stuff like that. So in the early days, that's a lot of what we did.
[00:03:53] Just like have an account model. And that refers to, I don't know, a user model. And if that then again refers to something else, you don't reference the account again, because it's implicitly there through the relationship to the user model and then to the account model. And that's what we did in the early days.
[00:04:13] But these days I learned that sometimes denormalizing stuff is actually a good idea.
[00:04:20] And you want to know that this, whatever resource over there belongs to this particular account, even though you implicitly don't through the relationships. But in reality, it's a nightmare to deal with. So yeah. That part is something I'd definitely change in the early days.
[00:04:40] Benedicte: How far into the journey did you go before you were like, "Oh, this denormalization or this normalization stuff is great in theory, but."
[00:04:52] Benedikt: Yeah, it was fine for the first couple of years. But the more data we have and the more the activity that is on our platform, the more I realized that normalized data is nice in theory, but not in reality. At least part of it.
[00:05:09] Benedicte: So what are the problems for the people who aren't so into it? What are the problems that you were seeing?
[00:05:14] Benedikt: Yeah, the basic problem or the biggest problem is just speed in terms of querying the data. Because we are a multi-tenet application, so we have one big application, but the data is basically organized per customer. That's like one criteria we filter by all the time. Like, of course, if you log into Userlist, you want to see the user list of your account and not of everyone's account.
[00:05:42] So we are constantly filtering whatever you see by customer ID or by account ID in our case. And by not having that account ID present in all the data models, it becomes a little bit challenging because then to figure out what data to display, you have to query that resource and like all the relationships that connect that resource back to the account.
[00:06:05] And again, it makes it unnecessarily complex to query. And it touches a lot more data than necessary. Does it make sense?
[00:06:16] Benedicte: It does make sense. And I think like it seems to be something that you experience when you have more data, because I'm guessing the speed it then takes longer to do these joins and finding what is connected.
[00:06:32] And it's easier to just do like, "I want account or I want, yeah, I want this row based on an ID," and then just have all that information available to you. Yeah.
[00:06:43] Benedikt: Yeah, exactly. It's fine for the days where there's not a lot of data, but by now we have like millions and millions and millions of records and that becomes a problem. Yes.
[00:06:54] Benedicte: So you said that most of them haven't really changed, but is there any model or kind of feature where you've needed to work on your data model over time or it has been changing?
[00:07:08] Benedikt: Yeah. So the part that we are currently working on changing is like kind of fixing that thing across all the data models where we add more data into the data models and organize them a little bit more by account ID. And that's an ongoing process.
[00:07:27] And in that process, we also want to pick up something that Monica mentioned in like one of the earliest episodes of like, "when in doubt, always have a list of things instead of just one thing."
[00:07:40] So the thing that's definitely missing in Userlist right now in our data models is we are currently, as I mentioned, organized by customer ID or by like account ID.
[00:07:52] And that's also where their billing is attached. So you have one account, you have one subscription attached to that, and then you have a couple of users or like thousands of users in that account.
[00:08:04] But what we realized into the journey is that that's unfortunately not how people want to use the product because, basically what's missing is like this concept of an environment or a workspace.
[00:08:19] People want to like have the production data in there and they also maybe want their staging data in there to be able to test stuff. So one thing we need to introduce, sometime soon, it's on the roadmap for this year, is like have like this concept of workspaces where we organize the users into workspaces or the data into workspaces. And then have multiple workspaces per account and therefore multiple workspaces per billing model.
[00:08:46] And that's something I definitely regret not doing from the start, because as you can imagine, everything's connected to an account or almost everything.
[00:08:55] And changing that means rewiring a lot of stuff. And honestly, I'm a little bit scared about that process.
[00:09:04] Benedicte: 'Cause the work around at the moment would be for me to like have an additional account, that would be my kind of testing and dev environment.
[00:09:12] Benedikt: Yes, exactly. That's how we do it right now.
[00:09:15] We said, like, it would be nice if people would just like pay for another account, obviously. But in reality, that's not what happens, right? In reality, uh,wet up test accounts for people that are like kind of marked as free. So they can like, they get a production account for the real world data and then like one free account for playing around with it.
[00:09:39] And for the most part, yes, it's fine. But like in the past, we have like a couple of incidents where people then secretly turned their test account into the production account for something else. And eventually we noticed, but it was never a pleasant conversation.
[00:10:00] Benedicte: No, that does not sound like the conversation you want to have.
[00:10:04] Ola: But that's the same kind of solution that Anna Maste used on her Boondockers Welcome thing. Because she had like, she had
[00:10:13] You could camp with your recreational vehicle. And then, but some of them had different locations. They had like the cabin and the house. So then they had to set up a separate accounts, but did she change it?
[00:10:30] I don't remember.
[00:10:32] Benedicte: She did not change it before it was sold because it wasn't that many. But yeah, she was saying that people would have a property and you would describe your property and the number of look and the number of spaces you had. But then some of the spaces are so far from other spaces that the amenities would be different.
[00:10:50] Like it would be like the dev environment where it was like nothing. And then there was, would be the production environment where there was like access to a bathroom and water and stuff like that. And then people would have to create another account for the other listings. But she said most people didn't, they just kind of wrote it in the description.
[00:11:10] So yeah, people wouldn't know exactly. They can like search for it.
[00:11:15] Ola: It didn't sound like it was a huge problem for her, actually.
[00:11:21] Benedicte: No, I think her customers weren't, there weren't that many with like large properties with like multiple spaces for vehicles. But I'm guessing for Benedikt where it's mostly devs using the product or dev setting up the product, they are used to having testing and staging and, or dev like having different environments.
[00:11:39] So they have that expectation.
[00:11:41] Benedikt: Yeah. It's honestly, it's not that bad yet. But we can see a path where this gets out of hand and it's starting to get a little bit out of hand because it's a nightmare to monitor and then stuff like that.
[00:11:57] So this is also one of the things that I'd rather change now than in a couple of years because while it's a nightmare already, it's going to be a worse nightmare in a couple of years.
[00:12:07] So, let's try and get ahead of this before it's unmanageable.
[00:12:12] Benedicte: But we were talking a little bit before the show as well, and you were saying that there are some other things that you're dealing with these days that is around
[00:12:20] counting. Which I'm going to let you talk about it. But it's funny cause in my head, it's like, you just count.
[00:12:27] Benedikt: Yeah. Yeah. It's interesting. And I've been thinking about like what to talk about earlier today. And I can't, I remember this, there's this joke in software development I feel like it's very true for what we do in Userlist.
[00:12:43] Basically, it goes like this: there are two hard things in software development, naming things, cache invalidation, and off by one errors. And that pretty much explains like the entire journey of building Userlist. Like we've come across all of these, especially related to our data models in the database.
[00:13:07] And like to touch on, like the least related thing to naming, to counting is like the naming part. But one side note or like funny story in Userlist some. As I mentioned, Userlist tracks users, but Userlist also has users, right? Like our customers are users of the system.
[00:13:26] So we ended up with two user models, because we store users like our customers' users, and we store our own users. So in the like first couple of weeks of building Userlist, we were constantly confused. Like when having conversations, like what user are we actually talking about?
[00:13:45] So at some point, we established a pattern of like having users, which are our customers and tracked users, which are our customers' customers.
[00:13:56] And that solved a little bit like of that pain. So naming things hard, confirmed.
[00:14:01] And then the other two are more related to counting, right? So and again, like in the early days, it's not a problem because you just store your data and if you want to count it, you just select count star from table and you get a result back and all's fine. And you can filter by it and like do whatever you need to do.
[00:14:25] But the problem that we ran into is with the more data we have, like counting becomes slower and slower and slower. And so because like, especially, I think it might be different in other databases, but because of the way Postgres is built and please don't ask me for the details, I'm not entirely sure. But the way Postgres works basically means that in order to get a accurate counting result, it has to actually go to the database table and count every single row. And of course, the more data you have, that gets a lot slower and slower. So that's the only thing the database can do.
[00:15:05] So counting is always a problem. And then yeah, at some point you start trying to figure out what to do about it. And because we are working with Ruby on Rails, the first thing we tried to use was counter caching. And that's basically the idea of instead of constantly querying the database and like actually counting all the rows, you basically on the over, like on the overarching model, like for example, the account, you have a column that is tracked user count. And whenever you create a new user, you increment that value by one. And instead of like counting all the rows in the database, you just look at this column. And that kind of works,
[00:15:48] Benedicte: But then people unsubscribe.
[00:15:49] Benedikt: Exactly. And then people, and then stuff happens, right? So in theory, it's a super nice solution. But then you get into like cache invalidation thing, right? Because that column is essentially a cache of the count of the number of rows. In that particular account, and as long as you're like super careful about how to create and how to delete data from your system, that cache should never get out of sync.
[00:16:23] But in Ruby on Rails, that basically means always going to your active record models, which like the wrapper models around the database tables, and always using the create and the destroy method to delete every record individually. And that would also then always fire a query that increases or decreases the value in that column.
[00:16:46] But because like doing stuff one by one is kind of inefficient, you often resolve to like just sending a delete query with the condition to the database. So for example, delete all the users that don't have an email address set up. And that's one query to the database. It just removes those rows, but what it doesn't do, at least not by default, it's like change the value in that column.
[00:17:12] So if you're not super, super careful about the code in your entire application, you're pretty, like you run into cache invalidation issues pretty fast. And that number you see in there is not accurate anymore because you did something that modified the actual count without updating the cache column.
[00:17:36] So number two, confirmed. Cache invalidation is hard.
[00:17:40] Benedicte: And I'm guessing your users want to see the number of active emails or active, you know, how many opened emails in the last seven days? And you know, those kinds of statistics. And for that you needed to be of this minute at least, or like, you know, not.
[00:18:00] Benedikt: Right. Yeah. That's another level even. Yeah. Like but for the longest time, we just didn't have any like aggregated statistics over time. And maybe we can talk about it a little later on but, let's just assume we're talking about like the total numbers of users in your account.
[00:18:19] And that's the thing we tried to solve with like counter caching for a while. And yeah, it kind of works to a certain degree, but it's hard.
[00:18:31] Benedicte: So you don't feel like you've solved it yet? Have you landed on the solution you think you're going to be living with for the next four years?
[00:18:38] Benedikt: We'll see. The next iteration of what we did is,
[00:18:43] Yeah, basically, so assuming like, for example, let's talk about broadcast. That's a good example, where the problems with that approach, like besides cache invalidation, become obvious. So like, for example, like a broadcast is a one off message to a list of users. And of course you want to know after the fact that like, after you send it, how many people did I send that email to?
[00:19:12] And the good thing is like you send that broadcast once and you send it to a thousand people and that number is not going to be decremented ever, like sort of cash invalidation issue is not a problem in this case because you said it once and that's the truth, like you send it to a thousand people, it's not going down ever, so we don't have a cash invalidation issue with this.
[00:19:34] But we get another problem with this. And that is when you start thinking about like how broadcast works is you click the button and you send a thousand emails and how do we track the number of emails that we send? Well, we increment the counter cache column by one every time we send one of those emails.
[00:19:55] And that's all nice until you realize that you're sending several thousand emails per minute, and they are all trying to increment that column. And you've basically got two options there. Oh, well, maybe a couple. But again, databases the way at least relational databases work, it's that they are always trying to make sure that your data is accurate.
[00:20:21] And like the classic example is like, imagine you're using this database for a bank account and you should withdraw or deposit money. You always want it to be accurate and like not get into a state where the data is not matching reality.
[00:20:37] Benedicte: Almost accurate.
[00:20:38] Benedikt: Oh yeah. Almost accurate would be a problem with a bank account.
[00:20:41] And I mean, same thing with the count in the broadcast. Sure, it's not as big of a problem if it's inaccurate, but we want it to be accurate, right? So the database makes sure that it's always displaying the right number or like storing the right number.
[00:21:02] And like you might wonder, like, if I'm always just increasing that column by one, that's just going up, right? But the problem is, unless your database is enforcing, like, I think it's atomicity, where like that operation is like just isolated from everything else. It becomes a problem because if you think about it, adding plus one is basically a multi step operation.
[00:21:29] You first read the row of the current value of the counter column. Let's say it's 10. Then you add one. So it's 11. And then you write it back to the database, right? So
[00:21:41] Benedicte: If everyone is reading 10 at the same time, everybody is saving 11.
[00:21:46] Benedikt: Exactly. And then like, if you just turn one in isolation or like one ever and nothing else happens, that's fine.
[00:21:53] Like you read it, you increment it by one, you store it back. But because we're sending a thousand broadcasts or a thousand emails, of course, we don't do those one by one. We do them in parallel. So each of those parallel processes reads the value increments by one. And then writes it back.
[00:22:11] So we had like 10 processes that read back 10, incremented by one and wrote back 11. Which isn't accurate because actually, it should be 15. So luckily, the database handles this case for us at least. I think it's the default setting and if it's not, you can at least force it to. But essentially, what it does it has this concept of row logs when you start reading from it, it says, "okay, I'm reading this row and I'm planning to update it. So no one else touches the row.
[00:22:45] Benedicte: Nobody else touch it. Nobody else. Get off my row.
[00:22:50] Benedikt: Exactly.
[00:22:50] Benedicte: This is my row.
[00:22:52] Benedikt: It's basically acquiring the log. It does its read operation. It does its increment operation, and then it's write operation, and then it releases the log. And that makes sure that the increment is always accurate.
[00:23:05] And over time, like all of those, I don't know, thousand increments happen after each other.
[00:23:11] But a problem with that again is it then happens after each other, right? And that means it doesn't run in parallel anymore. And that also means it takes forever. So while we might be able to send the emails in parallel, because there's no interconnectedness between them, keeping the stats or like count, like counting how many stuff we send.
[00:23:33] Again, it's a linear operation and takes forever. Like in our case, the sending took like a minute or two and then updating that stat column took an hour or two or maybe three.
[00:23:46] Benedicte: That long? I'm always amazed by, cause we talked to also, I think it was Monica about like delete, she was deleting something and it just like cost so much when you have a lot of data. But like, it takes like hours. In my mind, I was thinking like, "Oh, five minutes or like 10 minutes, but just like hours to update this row."
[00:24:05] Benedikt: Yeah. Like it took, I mean, it's been a while. I don't know the exact numbers.
[00:24:10] Benedicte: But like long, long.
[00:24:11] Benedikt: What feels like forever.
[00:24:13] And I mean, one of the problems was, we process all of our data.
[00:24:21] Like stuff like that we process, in the background. So we have like, basically like for a broadcast of a thousand emails, we have a thousand background jobs. And when one of them fails, they basically do a retry. So they wait, they go back to the queue, wait a little, and then try again. And when like one of those queries hits one of those, the role logs we just mentioned, it basically fails and goes to the end of the queue.
[00:24:50] So what was basically happening is that like the, that the queue would like be processed, but almost all of the jobs would fail because one other job was writing that column at a time. So instead of like doing things one by one, and maybe that would have been a little faster.
[00:25:11] I would say just ensuring that it's always just one writing stuff would constantly retry, fail and go to the back of the queue. And then we also have like incremental retries in terms of like they wait a little before they get retried again, and the more times they retry, the longer they wait.
[00:25:36] And combining that with like, what I just described that basically made the retrace to be there forever. So yeah, that's the other off by one errors.
[00:25:51] Benedicte: Did you solve that?
[00:25:53] Benedikt: Yes, we kind of solved that.
[00:25:56] And the solution to that is basically my sledgehammer these days for all sorts of like those problems.
[00:26:05] And the solution we came up with are like. Actually, yeah, we didn't invent it ourselves, but the solution out there for problems like this is instead of updating the column directly, you basically did basically two approaches. Like one of them is, instead of having just one column, you have like 10 columns in not the same row, but like different, well, basically, how do you describe this best, like, instead of having one row with one column, you have 10 rows with a counter column and you randomly update one of those.
[00:26:43] And then at some point, like every couple of minutes, sum those up and write them to the original column. So that original column would only get updated every so often. And the chances of the role logs would be decreased by yeah, basically a factor of 10. Because you have 10 of those columns, those rows that you spread the rights out. That's one approach.
[00:27:11] And the other approach that we took is we're basically maintaining another table that's a queue of the updates to that column. So instead of writing to that column directly, we insert a row into that queue table that just says something like one, because we want to increment this table by one or minus one, because we want to decrement it, or if it makes sense, like some other Delta number. Because the thing is updating that row needs the row log, but inserting a row into a table, that's not conflicting with anything.
[00:27:52] So you can always just like pump those out and be super fast about it. And then, every so often we'd also just sum up everything in that table and write it to that particular column. And when we query that data or like want to show the data in UI and make sure it's the real time value, we basically read the row and then look at the queue table, find all the related updates, sum those up and yeah, add them or remove them from the row number in the select query, where we initially query the data.
[00:28:34] Benedicte: So that's how you would get the real time how many emails are sent already? Like how many emails are sent? You then check that intermediate table that tells you how many rows of sent emails essentially. But it's rows with a count of plus one.
[00:28:50] Benedikt: Yeah, exactly. So we have again, in a way we're still like counting individual rows.
[00:28:55] But because we are flushing that queue table, I don't know, every thousand inserts or so it's always a small table. So that counting operation is super fast and combining that super fast counting operation or some operation actually with the already cached value in the original table. Yes, the query is kind of ugly and like convoluted, but like we get a real time value super fast.
[00:29:20] And that's currently the thing we are using almost everywhere.
[00:29:24] And that's also how we just recently built our reporting engine feature, where we are now able to keep track of counts over time and like, show you, I don't know, how your users grow over time. And it's basically a similar idea.
[00:29:44] Benedicte: So if we go back to the broadcasting, would that then be like a graph over how many people opened that specific email over time? So on like March 8th, five people open it the next day, 10.
[00:29:55] Benedikt: Yeah, exactly. So, for the reporting engine, we basically, like previously we would solve this, as I described in the beginning of the episode, by just like looking at all the messages sent and looking at when they were clicked or opened and group by date.
[00:30:15] Yeah. But again, this is also kind of slow, doesn't work with large amounts of data or doesn't work reliably or good enough for large amounts of data. So for that particular case, we're using concept of rollup tables which are basically a cached version of the group query.
[00:30:36] So instead of maintaining a table where we insert every open at the exact time, we basically create buckets, right? So in this particular minute, five people opened a message. And the minute after just one and so on and so on.
[00:30:59] And of course, that again has this problem of the row counter cache at the row log. So we also have like this queue table on top of it to make it fast in terms of writes again.
[00:31:11] As I mentioned, it's my sledgehammer for everything.
[00:31:16] Benedicte: Where did you learn that approach? Was that by talking to others, reading a book, blog post?
[00:31:21] Benedikt: That idea actually came from a talk on YouTube by Citus. Citus is basically a database engine on top of Postgres. I think they got acquired by Microsoft and they have like their YouTube channel as a goldmine of like knowledge.
[00:31:38] And when it comes to like large scale database modeling and architecture and solutions, and they've got a talk called, I think it's called real time analytics with PostgreSQL. And that's where they explained that idea of like having rollup tables with the queue table and stuff like that.
[00:31:59] And that's what initially inspired me to do the counter queue update table and ultimately also inspired the way we implemented our reporting engine.
[00:32:13] Ola: So how did you find that video?
[00:32:15] Benedikt: Real time analytics Postgres in YouTube.
[00:32:21] Ola: You just searched for it and it showed up? Yeah.
[00:32:24] Benedikt: Yeah. I just searched for it.
[00:32:26] And I mean, I've been reading and researching this for quite a while before we landed on this. And ultimately I think I first watched that video two years ago and we only got around to implementing all of it in the last couple of weeks. But yeah, like it doesn't hurt to be prepared for when you get those problems.
[00:32:49] Benedicte: But would another option for these kinds of things be to like add on, what are they called? Like, is it Redis or Elasticsearch or like any of these kind of solutions? Aren't they made for like counting and like or do you know?
[00:33:06] Benedikt: Yeah.
[00:33:06] Benedicte: But I've heard they're not good for real time because they're more like eventually consistent.
[00:33:10] Benedikt: Yeah, I mean there's multiple parts to it. I think they all have like upsides and downsides. And sure, there is an argument to building some of what I just described with a dedicated analytics database.
[00:33:25] For example, like Clickhouse or I don't know, one of those things. But a problem you run into with like, once you start spreading out your data across multiple databases, your database in itself can't ensure consistency anymore.
[00:33:44] Like what I described earlier with the role logs and transactions and managing access to the data that only works because all of the stuff happens in this one database and this one database has control over everything. Let's assume we split those out, like you start the counters in Redis.
[00:34:06] We as a developer or application developer have to make sure that the data between our Postgres database, which actually stores like the messages, like the individual messages sent and stuff like that, and the counts, like keeping those in sync is our responsibility at that point. And if something went wrong, for example, we failed to I don't know.
[00:34:30] Let's assume like for every message we send, we store a row in a database that just marks the state of it. That sets the state from queue to deliver it or whatever. And let's assume thathatrt of the operation fails. But we already updated the count in Redis.
[00:34:49] It's our responsibility to make sure to do that. Change that count back to the original value. And that's a burden on you as the application developer. That's not as easy because you have to anticipate all the problems ahead of time and also anticipate rollback strategies. And by just staying within one database system, that database system handles all of those for you.
[00:35:14] It's like you start a transaction, you do a million things and at the end you say commit and it makes sure that that operation in its entirety works. Or if one of the things in that transaction, like you do like 10 queries and the 9th query fails, it will make sure that the previous eight queries are successful have no effect on the database.
[00:35:35] And that's the thing you lose by spreading your data across different databases, because then yeah, Postgres can't know what's in your Redis and Redis doesn't know what's in your Postgres. And you have to figure out like, how do I make sure that the data I write actually ends up being there?
[00:35:52] And if it fails at some point in the process, how do I make sure that I roll it back, but only the parts that are actually executed, like you don't want to do rollback more of the stuff that hasn't yet happened yet.
[00:36:05] Benedicte: Or you have to do everything again and put that into Redis and with the amount of data that you have, that's going to be a long, long.
[00:36:11] Benedikt: Yeah, exactly.
[00:36:12] Like it's In my opinion, it's too big of a nightmare to ensure consistency to justify spreading it out across different databases. Your mileage may vary. Maybe it's a good solution for you, but I decided it wasn't a good solution for us. So that's why we're sticking with one.
[00:36:36] Benedicte: I really like your thoughtful response.
[00:36:38] Like you're, you know, it seems like you thought about it. You just didn't read a tweet and was like, this is the right way to do it.
[00:36:47] Benedikt: Basically, I tried those other ideas in the early days of my career and then had. As I mentioned, like the two hard things in software development and cache invalidation is one of them.
[00:36:59] And essentially spreading it out in a different system is again, a cache of sorts. And it's becoming a lot harder to manage those if the data is in different systems.
[00:37:11] Benedicte: I feel like Ola has a question he should ask.
[00:37:14] Ola: Oh yeah. Is it time for the tall tale now?
[00:37:17] Benedicte: I think so. I feel like it.
[00:37:20] Ola: Yeah.
[00:37:21] Benedikt: Yeah.
[00:37:21] I mean, it feels like I already touched on a lot of things that play into it.
[00:37:28] We recently, previously mentioned that deleting data is a problem at large scale. And yes, I can confirm it's a problem. And here's the story that happened a couple of weeks ago.
[00:37:43] So at night, we get a lot of incoming data. Like a lot of our customers just have a chron job set up at midnight that syncs their entire database into our system. So sometime between midnight UTC and early mornings, we get like a huge influx of data. We do a lot of writes to the database and because writes change user data, we also do a lot of segment evaluations, which is basically our way of grouping users into groups and then running automations based on leaving or accessing those groups.
[00:38:16] So when data changes, we also have to re evaluate that stuff. So during the nighttime, at least nighttime in Europe, we do a lot of read and write operations on the database. Usually that's not a problem. But recently, umonef our customers canceled. And of course when a customer cancels, we delete their data. Like we wipe everything they ever sent us from the system.
[00:38:43] And that's exactly what we did. Like, so we got the webhook from Stripe telling us, "Hey, this customer canceled and we enqueued a job that wipes out this entire account." And usually that works well. But in this particular case, this was a huge customer. They had sent us millions and millions and millions of events about their users.
[00:39:07] And we just like, we're stupid about deleting that data by just doing something like delete from tracked events where account ID is whatever. And I didn't think about it until the next morning when I woke up and saw that all the work that we're usually doing at night was still pending when I woke up and got to the computer at like 8am.
[00:39:32] Like we had queues backed up for hours and everything was grinding to a halt. Nothing was working anymore. And eventually we figured out it was because that delete query was looking at the data on the account level and that's kind of, we're getting back to what I mentioned earlier.
[00:39:52] So it was manually scanning the entire events table, which is by far the largest table in our system. And trying to delete all of the rows that had this particular account ID on them and what made things worse because we're usually querying those events by user ID, not by account ID.
[00:40:12] We didn't have an index on it. So it was literally going to the disk and reading every single row and trying to find the ones that should delete. And that basically used up all of our IO operations at our Postgres provider, and we weren't allowed to do more stuff on disk anymore. And that slowed everything down.
[00:40:36] And eventually the solution was to stop everything, kill that query and basically not delete that data for another day, but have everything normalize and then instead of using that delete from track events, where account ID equals whatever.
[00:40:57] We went a little bit different and started deleting, like querying all the users of that account ID and then one by one deleting the events for every single of those users. And that still took forever, but it didn't bring the database down because there was an index on user ID and that way, it only took a couple of hours.
[00:41:17] It didn't bring down the entire system. And things were fine. So yeah, that's it.
[00:41:27] Benedicte: For those who are not that into databases, what's an index?
[00:41:31] Benedikt: An index is basically, well, it's the same thing as you see in a book, right? Or let's say, like cookbook. You have a cookbook and you want to make pancakes today.
[00:41:45] You could open up the book and like look at every page until you find the recipe for pancakes or you go to the end of the book, look into the index, and look for P and then look for pancakes and then there's the page, right? That's like page 215. And that's essentially what a database does as well.
[00:42:12] You ask it for pancakes and it looks into the index and knows what, like what row exactly on this, that information lives in.
[00:42:21] Benedicte: And then you can make different indexes. So for a cookbook, that would be like, you have one that would be for the actual recipes and then you could have another index for ingredients.
[00:42:30] So like egg, and you can see all of the rows that had ingredient or all of the pages that have ingredient egg, and you could do that with a database. Yeah.
[00:42:39] Benedikt: Exactly, yeah. And if you have like a fancy cookbook, that's actually what happens in books as well, right? You can have like index by name or you can have index by ingredient and just increase the lookup time by that.
[00:42:52] Benedicte: And so what you were saying is that you didn't have that on account ID. So it couldn't go this account ID. I know it's on row 25. You had to look through all the rows and see if it was a match.
[00:43:05] Benedikt: Yeah, exactly. And I mean, you could argue, "Hey, you're stupid for not having that."
[00:43:10] But the problem is we actually considered it and decided to not have it because again, this is the largest row in our system. It's millions and millions and millions of rows. And we have indices on that on different criteria, the criteria we usually query the data with. And that's usually like user ID and a couple of other things.
[00:43:33] And even those indexes are like multiple gigabytes in size. So they're huge to maintain and take up a lot of space on disk. And that's why we decided against having one on account ID as well, because you barely using it and maintaining it is takes time and takes space. So that's why we never had one.
[00:43:59] But then of course, in hindsight, we shouldn't have had this query that looks up stuff by account ID to delete it. So, yeah.
[00:44:09] Benedicte: And that would be the same with the cookbook. Like if you're going to have an index for every possible way of finding a recipe in a cookbook, most of the cookbook would be indexes, indices, indexes.
[00:44:21] Benedikt: Indexes, indices, whatever.
[00:44:23] Benedicte: Indexes, indices.
[00:44:25] Ola: So that delete job was basically just starting on the first page in the book. I'm just looking through. Does that look like pancake? No. No, not pancake.
[00:44:36] Benedicte: Should I rip this page out? No. Should I rip this page out? No.
[00:44:40] Ola: And you know, like millions of pages in the book.
[00:44:43] So it was just like all night, just looking through the book. It was like, no, that's not a pancake. Oh, a pancake. Yeah?
[00:44:50] Benedikt: Yeah, exactly. That's exactly what happened.
[00:44:53] And again, what you hinted Benedicte is it was also like when it found a pancake, kinda, I don't know, not ripping it out already, but like kinda putting a finger on it so when it found all the pancakes, rip them out at the same time all together.
[00:45:11] So it was like just a ridiculous operation.
[00:45:14] Benedicte: I have a feeling that there will be a sketchnote chronicling this tall tale. It feels like a good candidate.
[00:45:22] Ola: So the Pirate Princess, she's doing that. I read for her at night in about an hour and then she goes to her bed and she's supposed to sleep there. But she has this little unicorn that she lights up with, and it's like a flashlight kind of thing.
[00:45:41] And then she looks through the books and she's like trying to not make a sound when she turns the page so that I shouldn't hear it. And then like after half an hour, she's been super quiet. I'm like, "Lillian, are you reading?" And she's like, "No. Well yes, actually."
[00:46:04] Benedicte: And for a while, her thing was reading cookbooks and bookmarking the pages that she wanted us to make, which is basically all the cakes and all the desserts. She was making her own index of Pirate Princess approved meals.
[00:46:22] Benedikt: Yeah, that's smart.
[00:46:24] Benedicte: But I guess we're at the Wayansende, which I suddenly couldn't say in English. At the end of the road, for this one.
[00:46:33] But where can folks find out more about you Userlist?
[00:46:38] Benedikt: Yeah, so Userlist is at userlist.Com and also at user list on Twitter and other social media. And I'm @BenedictDeicke on Twitter and BenedictDeicke.com on the web.
[00:46:52] Benedicte: So consistent.
[00:46:53] Benedikt: I'm trying to be. Yeah.
[00:46:56] Benedicte: Thank you so much for sharing your data model stories with us today, Benedikt.
[00:47:00] I really enjoyed that you referenced the good old naming, caching, and off by one folklore of development and showed us real world examples of when that's a problem.
[00:47:14] Benedikt: Yeah, you're welcome. This was fun.
[00:47:16] Ola: Welcome back to Data in the Wild next week and discover more data model tips and tricks.
[00:47:23] Ahoy!
[00:47:25] Benedicte: See you around the interwebs.
[00:47:26] Benedikt: Bye.