Become a Creator today!Start creating today - Share your story with the world!
Start for free
00:00:00
00:00:01
IBM Killed Our Database: How 5 Engineers Migrated to Postgres | Ep. 13
24 Plays6 months ago
IBM was dropping RethinkDB support. No migration path. No alternatives. Just an end date.
Andy Monroe's team at LaborChart had five engineers and an entire construction scheduling platform built on a database that was already dead. They'd watched every other RethinkDB hosting provider shut down, leaving only Compose.io.
When IBM acquired Compose, they knew it was just a matter of time.
Andy's air conditioning had died two hours before our interview. It was 90 degrees in Kansas, and as we talked about this moment, the irony wasn't lost on either of us.
The company he'd helped build solved scheduling problems for construction workers—the very people who might have fixed his AC. Now he had a different kind of emergency to handle.
Get Tern Stories in your inbox: https://tern.sh/youtube
Connect with Andy! - andymonroe.dev
Recommended
Transcript
End of RethinkDB Support
00:00:00
Speaker
We finally got the notification we were dreading. We are dropping support for RethinkDB. The way that we use RethinkDB is really fundamentally relational. We have a concept essentially of like these foreign keys between records. We do effectively like a lot of joins and filtering on joins. Has your opinion about how to build software changed with the rise of AI tools?
00:00:22
Speaker
We mostly use Claude at my current company and what's good for humans is good for Claude or vice versa. What's good for Claude is good for
Transition to Postgres
00:00:28
Speaker
humans. I kind of just decided that Like, we'll just let Postgres lead the way. What do you do when IBM kills your database? That's exactly what happened to Andy Munro and his five-person team at LaborChart.
00:00:37
Speaker
They built their entire construction scheduling app on RethinkDB, a technology that had already been defunct for two years by the time Andy got there. They were running on the last hosting provider, which had been acquired by IBM.
00:00:48
Speaker
And finally, they got the email. IBM was dropping support. No migration path, no alternatives, just an end date. So Andy had to lead a complete database migration to Postgres while keeping the product running with zero downtime in the wake of an acquisition.
00:01:03
Speaker
What he built wasn't just a migration script, though. It was an entirely new way of thinking about data transformations that would later shape how he approaches building
Andy Munro's Introduction
00:01:11
Speaker
with AI. This is that story. ah Today i on the show, I've got Andy Munro.
00:01:17
Speaker
Welcome to the show, Andy. Glad to be here. ah Yeah, how's it going with you? Things are great. It's hot here. So we are we are all experiencing but a heat wave that has come far too early, but we're going to make it through anyway.
00:01:32
Speaker
Yeah, yeah, we were just talking before our recording started. My AC just went out like, I don't know, probably two hours ago, and it's like 90 degrees where I am. So
LaborChart's Evolution
00:01:42
Speaker
if I sound sweaty in audio, that's why.
00:01:46
Speaker
We're going to get this done before you have to go downstairs and put ice packs over your bat. It's going to be great. Exactly. Cool. So today, we wanted to talk about... we talked we were chatting a little bit before the show. um And you had said that you'd worked on a migration of Rethink to Postgres when you were at LaborChart, which required the whole the whole thing.
00:02:07
Speaker
um Before we get into that that story, tell me a little bit about... like Set the stage for me on LaborChart. What kind of What kind of work were you doing there? um And what was the the sort of journey of the company that led to that moment?
00:02:22
Speaker
Yeah, so LaborChart was an interesting company. I say was because they were acquired a few years ago. I think it was like 2022, something like that.
00:02:34
Speaker
But ah yeah, LaborChart was um based in Kansas City, and it was founded actually by a third-generation electrical contractor, was our founder CEO. His name's Ben Schultz.
00:02:46
Speaker
Cool. Yeah. And it was really built to solve the like specific needs of like um especially specialty contractors in like construction.
00:02:58
Speaker
um But it generalized pretty well as well. And the whole premise of it is it's like it's a labor scheduling software. So like the core workflow that you think of for a tool like LaborChart is like so the like way that construction works like pre-LaborChart is like You know, you've got like the foreman on these job sites and they might say, like, I need like, you know, three guys with X skill set for these dates.
00:03:28
Speaker
ah Find me someone. And then so they put that request in with the scheduler. And the scheduler is literally like some guy who's got some like bespoke whatever system. And he just calls everybody until he can like get the scheduling worked out.
00:03:43
Speaker
And like that's the way the whole thing works. It's scheduling is mostly like sort of listed out on a whiteboard somewhere. So like the people who actually are being scheduled, like would physically go somewhere
RethinkDB's Features and Challenges
00:03:53
Speaker
and look at a whiteboard. And that's how they know where to go.
00:03:56
Speaker
So LaborChart was basically like sassifying all of that. So now it's like the foreman or whatever can just like on like an app or on their the web app can just um they can like put in a request again with all the like parameters that they need for the person.
00:04:15
Speaker
And then, um yeah, a scheduler or it could be multiple people now that it's like a shared system, like not like one guy's Excel spreadsheet or something. um They can just go and they can assign people who are free to that particular project for those particular dates. And it like just works. And it automatically sends like texts to people get the automated text and can say, yes, I will be there or no, I won't. And.
00:04:43
Speaker
Yeah. So helps make managing that kind of stuff a lot easier. So the like core concept of this system is like you have people, you have projects, you have an assignment, which is like a person to a project for some dates.
00:04:56
Speaker
And you have a request, which is like basically a pre-assignment. It's like before you have a person. um Yeah. And that's like the core. Yeah, that makes... sense i I love this. I actually listened to a podcast um from the CEO of Procore recently.
00:05:12
Speaker
And it's just... We were acquired by Procore. Yeah, exactly. yeah And it was... like It is fascinating because construction broadly and like... is just a far more logistically complex business than even people who know a little bit about construction or trades or or whatever think that it is There's a lot of people like the founder of LaborChart, I imagine, who sit around and and like They thought they were going to be out there like working with people and doing this job that they learned how to do. And then they end up in a scheduler role where they sit in a room and they call people and push paper around.
00:05:49
Speaker
And it kind of sucks. And they're like, why? is Is this really the job? and i it it It has a lot to do with just like how efficient construction has gotten.
00:06:01
Speaker
um And it's amazing that it all works on on whiteboards um or did until recently, I guess. Yeah, and I would also say like an interesting part too is like,
00:06:15
Speaker
as I have seen it, there's like not very much like Venn diagram overlap between like tech people and like construction people. Right. And so you don't end up with like a lot of really great tech in support of construction. Cause they'll like share like the, the people that have context on both of those things is like pretty small.
00:06:34
Speaker
Um, and so, yeah, I like continue actually to think that there is probably a lot of opportunity in software for construction. Um, that is like basically untapped.
00:06:47
Speaker
But yeah, yeah, it makes a ton of sense. they just
00:06:52
Speaker
Yeah, absolutely. um So tell me a little bit about the app itself. That's, you know you have, you have what sounds like a fairly straightforward data model. You're matching people to assignments. How did you, how'd you model that out? What was the tech stack when you got there?
00:07:05
Speaker
Yeah, so the tech stack when I got there, which didn't change too much over the course of my time there. I was only there for, think it was like,
00:07:15
Speaker
including pre and post acquisition. was probably a little over two years, um something like that. But it was Node.js backend and ah like single page application front end, actually using a tool no one uses anymore called Knockout.
00:07:31
Speaker
um For those of you that are familiar, um which actually worked pretty well. ah We didn't have like a huge amount of um like,
00:07:44
Speaker
I don't know, like feature change velocity, but it it kind of didn't really matter. We had really strong product product market fit. And so mostly it was like ah iterative features that we shipped while we were there, but we did do a pretty major um refactor of like the main API service.
00:08:02
Speaker
um The code used to be all CoffeeScript when I started. And the new API was sort of like a gradual rewrite TypeScript.
00:08:14
Speaker
um Some of the endpoints were like
Post-acquisition Migration Strategies
00:08:16
Speaker
basically straight rewritten and then like any new features that we built that needed a new endpoint obviously went in this like new API. So we had like three Node.js backends um which were basically the legacy like API backend and then there was like a public API sort of legacy backend and then there was this like what we called core API which was the new TypeScript thing that could serve both of those legacy purposes. Yeah.
00:08:42
Speaker
Oh, cool. Interesting. Yeah. So it was ah designed ah from the beginning to be able to support um both ah internal and external like API use cases.
00:08:55
Speaker
Cool. That makes sense. Yeah. And then, our our of course, the content... and Main topic of this conversation, the primary database that we used was a database called RethinkDB, which is a like JSON document database.
00:09:11
Speaker
um And I actually thought RethinkDB had a really great API. I thought they had some really great ideas. But um they ultimately went defunct.
00:09:22
Speaker
My understanding is they like more or less got outcompeted by MongoDB. yeah it was, it feels like a sort of like a classic story of like, I think rethink DB maybe had a more interesting, like theoretical foundation, like a more thoughtful start, but they weren't as good at distribution as Mongo was.
00:09:45
Speaker
Mongo was way better about actually getting people to use the software and figure out how to turn that into a business. um From my perspective, then rethink DB was. And I think that's why they ultimately went under, but Ultimately, I thought that the API was actually really great to use in a lot of ways.
00:10:01
Speaker
It was very easy to like ship pretty quickly with it. It had support for like you know custom like secondary indexes and like not quite foreign keys, but like you could do effectively joins pretty easily with Rethink. um Even though you didn't actually have foreign key validations, it was still like relatively easy to make those things work.
Overcoming Migration Challenges
00:10:27
Speaker
um Which was ah a big complaint for the longest time about Mongo. that You stuff all your data in there and then all of a sudden you're pulling half of your data set into memory to do anything with it.
00:10:38
Speaker
Right, right. They added much of this over time because they had the luxury of doing so. Right, exactly. Um, but yeah. And one of the like cool things, one of the things that I thought was pretty nice about rethink DB is it had this, like you, it didn't have a separate, like sort of string based query language, like you would have with like Postgres, you know, the way that you interact with rethink DB is they had, um,
00:11:06
Speaker
ah basically these like query builders um that you could write in like a couple different languages. Definitely JavaScript had support. um I know there was a Ruby, there is like a Ruby syntax that was supported as well. And maybe also Python or Java. I can't quite remember, but I know Ruby had support and definitely JavaScript did because that's what we used. to And you could just write um in JavaScript directly, like these sort of, um,
00:11:34
Speaker
yeah, these like built queries. So you could, you know, have a bunch of if statements to add on like another like where clause or like things like that. And we use that pretty extensively in the code base, which came into play when we yeah ultimately had to move, like trying to figure out something that could be as dynamic as what RethinkDB was as far as like actually doing the queries.
00:11:59
Speaker
That's really interesting. I want to come back to that because I i i feel like
00:12:05
Speaker
interacting with this it with something like a SQL database, like so many people fall back into an ORM or a query query builder or something like that. Being able to just say like, this is the native interaction with the the language seems great. And also a terrible thing to give up when you actually have to go backwards to, well, not backwards, but you know you you have to move to a technology that's not borderline defunct.
00:12:26
Speaker
Yeah. Yeah. You get to that like I feel like I've heard people call it like a sort of impeded impedance mismatch or yeah that kind of thing where it's like you're going between, you know, SQL has its way it understands data.
00:12:40
Speaker
And then also your programming language of choice has its way that it understands data. And that mapping is not usually one-to-one and you have to figure out how do you deal with that, which RethinkDB, it wasn't, that wasn't an issue.
00:12:54
Speaker
Mm-hmm. Yeah, that makes sense. um And I think that's... I've heard that story a lot for NoSQL databases. Like, it makes sense. It's very easy. Go get me a thing. Here's the thing. Here's the associated things with my thing.
00:13:05
Speaker
That's very straightforward. Mapping the full breadth of SQL into it and to add Otherwise, you know, a node something like Ruby or Node is not necessarily quite as straightforward.
00:13:16
Speaker
Mm-hmm. So what's... What was the what was the timeline on RethinkDB? I should have gone and looked this up, but, you know, it was... um Your impetus for moving was ah a moment in the RethinkDB ecosystem.
00:13:32
Speaker
Yeah, so ah let's see if I can remember the name. So it was like already, like the business, I think, had already shuttered by the time I'd started at LaborChart.
00:13:44
Speaker
But, you know, it was open source, like you could still use it you could self-host it if you wanted to. um But ultimately, our impetus to leaving, this was post-acquisition that we actually decided to do this, was we were using...
00:14:00
Speaker
ah let's see, it was a company called Compose or compose.io, something like that, um for a hosted version of RethinkDB. And um they were like the main player in hosting RethinkDB if you wanted like a managed instance of it.
00:14:18
Speaker
um And at some point they got bought by IBM. And eventually we finally got the notification we were dreading which is uh we are dropping support for rethink db and at this point there was really no one left that was you know looking to run managed instances of rethink db for us and there was not an interest um at the organizational level of self-hosting rethink db and just managing that forever um
00:14:53
Speaker
So yeah, the decision got made that we'll put in the effort and we'll migrate to Postgres. Procore uses Postgres um for its main app as well. So it's ah they're like mostly a Rails monolith or they were at the time.
00:15:08
Speaker
And so they had a lot of people who really understood Postgres. And so it was also desirable for them to have ah us be using the same database that they mainly use and had all the expertise in and obviously Postgres is Postgres.
00:15:21
Speaker
It's a great database. It's hard to go wrong. um Yeah, i would I would advise anyone to like, you know, if you're just starting out on a project these days, you should just use Postgres until you really need to not use Postgres for some reason, which is like increasingly a smaller and smaller subset of use cases ah can't get by with Postgres.
00:15:45
Speaker
Yeah. Uh, yeah, absolutely. It's great software. Tell me a little bit more about actually making this decision. Did you see, I mean, you knew like rethink DB, the company was acquired by Stripe in like 2016.
00:15:57
Speaker
Um, so like been a minute. Uh, Did you, it sounded like you saw this coming to a certain extent. Did you have, did like pre-rolled that discussion about what you, about what you were going to do?
00:16:11
Speaker
There was conversation about it for some time. um I think even pre-acquisition there was like, So the other co-founder of LaborChart, the CTO, um who built a lot of the app in the first place was named Hunter, Hunter Browning. um And Hunter and I, every once a while, would just talk about like, he's like looking at like other things we could move to. So we talked about things like,
00:16:38
Speaker
You know, do we move to, like, Couchbase? Do we move to Mongo? Do we move to, like, AWS, like, DynamoDB?
Ensuring Data Consistency
00:16:47
Speaker
Like, you know, there's these others, like, document DBs out there.
00:16:55
Speaker
And i looked I remember looking around at them a little bit, and I was like, our the way that we use RethinkDB is really fundamentally relational. like We have a concept, essentially, of like these foreign keys between records and like um
00:17:13
Speaker
And we do effectively like a lot of joins and filtering on joins and all that kind of stuff. And looking around at these other document databases is none of them had support for that kind of thing to the extent that RethinkDB did.
00:17:30
Speaker
You know, as far as like the ergonomics, like, of course, you know, you can do anything if you try hard enough, but like. It's a computer, just write more code. Yeah, but really it was like, i remember talking to Hunter for the longest time, just thinking saying like, we're going to end up moving to Postgres. Like Postgres is really, it's like the right solution for the data that we have and the way that we use it.
00:17:51
Speaker
Yeah. So that, that conversation happened for a while. And then, um, obviously post acquisition, when that conversation started to come back up, there was more serious investigation that I, again, I think mostly happened between Hunter and some of the like more senior technical people at Procore. And, um, while I wasn't fully involved in all those conversations, uh,
00:18:17
Speaker
Ultimately, yeah, Postgres ended up being picked, and I would still that was i would still say is the right answer. Obviously, the the migration eventually worked. ah Maybe spoiler alert, but...
00:18:29
Speaker
um it did It did work. Procore continues to let everyone use post make everyone use Postgres. And it was the right decision. So that's great. um Tell me a little bit about... i you You mentioned that because Procore used Postgres, and I'm sure there was some pressure to standardize technologies, I think there's this like base instinct of a lot of...
00:18:50
Speaker
engineering orgs of like, we want to simplify our tech stack. Did they help you with the modeling or the the thought process behind why it was best for labor charts data?
00:19:02
Speaker
Or was that something that sort of all came from internal discussions?
00:19:10
Speaker
Yeah, we did get some support from internal to Procore, but honestly, it was it was mostly just done by ah team that we had,
00:19:21
Speaker
um which was a pretty small engineering team. Like, we had... I don't know on, on the neighborhood of like five engineers when we were acquired. Um, Oh, wild. It was a really small team. Like I was, I was running the engineering org and like, I don't know I did not have like a lot of like professional software engineering experience at that point. Like I was, i don't know, like I had some leadership experiences from like college orgs and like I got software pretty well, but yeah, it was a small team and, um,
00:19:57
Speaker
But yeah, mostly we ah were allowed to pretty much run with it as long as it seemed like what we were doing was making sense. um You know, I think someone would have raised the flag if it seemed like we were doing something that was like fundamentally not going to work or like we were just wasting time or whatever. But um no one ever really did that.
00:20:17
Speaker
um
00:20:20
Speaker
Yeah. And the data modeling was mostly done by me um as far as like converting what we had in RethinkDB to Postgres, which ah one of the harder parts there is RethinkDB does not have schemas. It's a document database. So it's like...
00:20:39
Speaker
um
00:20:41
Speaker
Fortunately, in my history of adding TypeScript to a formerly untyped code base, I had written a lot of the TypeScript schemas for like most of our like tables. um So I knew basically the shape of everything. And I kind of like i knew sort of.
00:20:58
Speaker
where the bodies were buried, where things had bad names, where there were like typos. and And I actually used ah this like big database migration as an excuse to fix a lot of that stuff, which was like,
00:21:13
Speaker
cool to be able to do. you know Nothing complicated, but just like, so I mentioned like those requests, you know these unfilled sort of like, I need a percent of these skills for this project for these dates.
00:21:26
Speaker
ah That was not the original name for that data model. It was originally called placeholders. And so sure ah in the product and like in our public API and everything, it was called requests, but in the database, it was actually still called placeholders.
00:21:43
Speaker
So you'd see like placeholder ID and like things like that. um So this was an excuse to like fix all of those kinds of things. In like one or two cases, there were like, you know, embedded like array of objects that, um,
00:21:57
Speaker
we were able to convert to actually be like a proper like join table. ah Effectively that way it was. That's what it was.
00:22:07
Speaker
But there were also a few cases too, where it was just like, this is we way easier to just move into a JSON B column. And so we did that too. ah Yeah. put I mean, Postgres support for JSON these days is great.
00:22:19
Speaker
Yeah. Yeah. Yeah, absolutely. That's interesting. Were there any, ah one of my favorite bits of and any like tech debt arcana, were there any typos you got to fix where someone had spelled something wrong and like, now that's the name of the key, I guess.
00:22:34
Speaker
Yeah, so somewhere in the DB, I think there was a field called like recipient emails or something like that, or like recipients. And the I and the E were flipped.
00:22:47
Speaker
And that's the way it was added in the first place. That typo got into the public API. Oh, no. And so we actually, like, we had to implement, once that was fixed, the public API actually had to continue supporting the typo in order to not break our customers. So I don't know if that ever got fixed. I would not be surprised if you still have That's part of the API.
00:23:10
Speaker
That looks forever. We might have added ability for you to, like, write to either of them. That would obviously be the most nice solution, but... um But yeah, so like recipients was one that we got to fix, uh,
00:23:25
Speaker
Yeah, because that was back in the days when it was like just Hunter. And, you know, and like this isn't on Hunter. It's like, yeah, if you're one developer just doing everything yourself, like you will make a typo and miss it eventually.
00:23:40
Speaker
Like that's why we have like PR reviews. Right. but Pray for you that it only appears in like internal variable names. the Yeah. Yeah. Hopefully ends up in the database and lives forever.
Reflecting on Migration Success
00:23:54
Speaker
but that's yeah. So we did have at least that one case. I think that was the only like straight up typo. um Everything else was just like, there's like a, this, this changed names in the product over time. So we should actually just use the like,
00:24:10
Speaker
the stable new name for it rather than what it was named, you know, years ago or whatever. Yeah. Yeah, absolutely. That's, that stuff's inevitable. Like names drift and rot. And yeah, we were already at at turn. We already renamed one of the database objects. We have exactly one of these things. We're six months old.
00:24:28
Speaker
Yeah. Oh, well. Yeah. Well, but and you you accumulate a ton of those in the beginnings too. Like you just inherently get the wrong, like you get things wrong the first time.
00:24:39
Speaker
My like rule of thumb is you really never get the like design of a new thing. Right. Until like you've done it the third time. Like, The first time you literally just don't know what you're doing and you just you learn by making mistakes.
00:24:53
Speaker
And the second time you like mostly know what you're doing and you get most of it right. But there's still just like one or two things where you're like, that is a problem. I wish I had done that differently. Third time, you can usually run with it for the long term um without have you majorly being upset about it.
00:25:13
Speaker
i love it. ah Yeah, that feels right, is that you you definitely are not going to get it right the first two times. um think If you're good, third time. Yeah, if you're if you're good, if you're lucky, um combination of everything, but...
00:25:29
Speaker
yeah Absolutely. So coming back to the modeling, that you had you had been in there. you kind of loaded all the context into your brain. you You didn't know it, but that was ah that's what you were doing with the the TypeScript migration.
00:25:42
Speaker
How did you... Did you just like sit down and and like write out what the new schema was going to be based on that? Or was there like an incremental approach you took or something something else? Yeah. I...
00:25:54
Speaker
yeah i um
00:25:59
Speaker
It was something that we mostly did by doing
AI and Software Development
00:26:02
Speaker
it. um However, we did kind of get to cheat a little bit to start with. So ultimately, the library that I ended up picking for us to go for for querying Postgres was a library called K'nex, K-N-E-X.
00:26:21
Speaker
Ooh. um It's been around for a while. It's like sort of the like boring software version of a query builder for JavaScript and Postgres. um And yeah, it had a concept of like migrations built into it, which we used.
00:26:38
Speaker
um But one thing we got to do to start with is before we were really actually doing real queries in production, um, we were doing these sort of like, ah we had this like fake, or not fake, it worked in development.
00:26:57
Speaker
And so as long as we weren't doing these migrations on a real production Postgres DB, ah we had the philosophy of just like, we'll just rewrite the migration files whenever we realized there's things we didn't do right.
00:27:09
Speaker
So that way it's not like we have these like, you know, 300 migration files where it's just like, we're like churning on the design or whatever. Like, we would just we had basically the like 0001 migration file that we did a lot of churning on um and you would just like nuke and rebuild your postgres database um whenever ah like uh some recent pr had changed that um which everyone was used to doing it was easy to do like we had a command for it so
00:27:43
Speaker
And that was most of how we did that. A lot of it was like me, like manually mapping back and forth. Actually, one thing that we did do. um
00:27:56
Speaker
I'm trying to remember how we did it now. I want to say I created some like.
00:28:05
Speaker
I think to get the TypeScript types back out of Postgres and making sure that they were right when we're like you know type annotating our queries in Kinex was I'd created some script at some point that for every type of like common field, it like maps that to a TypeScript type.
00:28:25
Speaker
And for more uncommon field types, so like for example, certain JSONBs, there was like a hard-coded lookup that it would do to say like, oh, if it's this table and this field, it gets this type, like as a special case.
00:28:39
Speaker
And so we also had a script that I built to make it a little bit easier to work through some of these changes and be able to get like, you know, types to break ah in a good way um in your code base. So it's like you would rebuild your Postgres DB from whatever is the latest migration.
00:29:00
Speaker
And then you would also, and it might have been in the same command that we had to run, um it would like basically just extract the whole schema from Postgres using a query and then convert that into these like TypeScript files to annotate every table.
00:29:16
Speaker
So that way, even when we did make changes, um you didn't get like caught out by, like oh, we changed this from like an int to a float because we didn't realize there's actually a case where it's a decimal or whatever.
00:29:31
Speaker
um Not that it matters for TypeScript. It's always number, but like you know. Yeah. um but that's cool So you ah
Future Plans and Contact Information
00:29:38
Speaker
you had you sort of like started with, like what are we're trying to get the modeling right. You're trying to you're trying to get the ah schema right. But you're pushing that back up into the queries.
00:29:47
Speaker
almost automatically because of the power of adding types. Yeah. Yeah. Cause mean, really the philosophy that I had, and I still kind of, I have the philosophy that I think like, this is a good way to build your software is like Postgres is harder to change than the rest of your code.
00:30:08
Speaker
And so really it's like, you really care about how does Postgres think about this data more than how the code thinks about it. um And so like, you know, you don't want to be in a situation where it's like, you know, your type annotations are becoming out of sync with Postgres and it's like, how do you make sure, ah yeah, this is, this is exactly it.
00:30:27
Speaker
The problem was, was like, am I, are we building a model where a developer is going to have to remember to update these fields in two places every time? That sounds like a huge pain.
00:30:38
Speaker
Um, yeah. And so we just decided, or again, we were a small team. I kind of just decided that like, we'll just let Postgres lead the way. And, um, we will write the migrations that we need to to ah get Postgres in good shape. And then ah to actually update the um the type definitions, yeah, we would just extract from Postgres and process it.
00:31:05
Speaker
Interesting. That's super cool. i mean, it makes a ton of sense, right? That you were spending all of this time on getting the Postgres bi migration right. You're only going to do the migration, you know, once. Fingers crossed.
00:31:17
Speaker
Right, right. um And we got to benefit from it down the line, too. Like, if we added new fields, like, they got types. And that was free. Unless you had a really, like, weird field, then you might have a couple manual things to do. But...
00:31:31
Speaker
If you were doing like a UUID, you just got that it was a UUID and that was done. Yeah. Yeah. that's That's straightforward. that's That's cool. um So how did that... All right. So you spent all your time thinking about the postgres the Postgres layer, auto-generated types for it. I love that approach.
00:31:47
Speaker
how How did that like intersect with the queries? I'm particularly interested in how did that intersect with the queries that like you had this totally separate language for RethinkDB with the Query Builder What were you, how did you, did you like move though over those incrementally as you churned or or how do you think about that?
00:32:06
Speaker
Yeah. So there were, uh, there was a different pattern for reads versus writes. Um, and one of the, my like proudest pieces of this migration was ah coming up with this tools these tools that we ended up calling PG writers.
00:32:29
Speaker
um So earlier on um in my time at LaborChart, back when it was still LaborChart, I had ended up building a tool um for saving our different models called SaveManager.
00:32:47
Speaker
And what SaveManager's job was is it was like a shared way of like writing records to the DB. And managing these like sort of what was previously like tricky use case where um we had like basically an event system, we called them activities.
00:33:03
Speaker
And it used to be you just have to manually know what activity to save with whatever write you were doing. So like say like a person's name was changed or something, you want an activity for that.
00:33:16
Speaker
Used to be you'd have to just like know to also save that in that spot. And what Save Manager did was it created this layer where RethinkDB supported when you made a write, you could get the old and the new JSON um back.
00:33:32
Speaker
And so almost all of our activities could be computed based off of the delta between the before and after JSON schema. So Save Manager would actually just do it for you.
00:33:43
Speaker
um If the delta... at hand had, you know, the right kind of change, um, you would just automatically log whatever activities and you did not have to think about it.
00:33:55
Speaker
Cool. So one of the cool things about all of our saves going through this save manager was, um, uh, So again, so like all of these rights, like we're just writing a JSON object, which is pretty straightforward.
00:34:14
Speaker
So one of the insights that I had at some point is there was someone else from the Procore side who was like, his job was to do, like figure out how to do the database migrations, like migrating our old data into our new data.
00:34:29
Speaker
And like, he clearly seemed to be struggling. I think he just didn't have as much context on um how to work with that kind of code base. there And I was working on like, how do we do dual writes, where it's like, if we write a request and rethink, we also write it to Postgres.
00:34:46
Speaker
Yeah. um And I ended up coming up with this tool called PG writers that basically you could just take these JSON blobs that would have been ah like row, so to speak, in rethink.
00:34:58
Speaker
And it would just automatically do all of the appropriate rights and Postgres, which kind of sounds obvious. I love it. But like we just built a PG writer for each table.
00:35:11
Speaker
And it could pretty deterministically, like, just always write the correct thing. And what that also meant is, like, now this, like, difficult migration script where it's, like, you're trying to figure out how to map each table into Postgres. It's like, wait, we can just use the PG writers.
00:35:26
Speaker
And you just go through and, like... that's it so you guarantee that you have the same logic in your migration code as you do in your dual write code so there won't ever be any divergences where it's like oh the migration script actually like misses this case that like dual writes account for or vice versa it all used the same code for writing to postgres based off of a right to rethink um That is super cool.
00:35:54
Speaker
was going to ask, there's no out-of-band thing here. We like wrote a separate script to so like go get the Rethink data and put it in Postgres. So you just ran it through the PG Writers?
00:36:07
Speaker
Pretty much. So it was a separate script. Um, but yeah, like I heavily used these PG writers where it was like, you know, effectively just like in an appropriate order, like for looping over all of the records in each table and just like writing them, which in practice like worked pretty well. Um,
00:36:27
Speaker
Yeah, we we never really had issues with the PG writers, you know, and they were tested pretty well and everything too, but um it ran fast enough on our ah production DB and we were able to even do it where like there wasn't even downtime.
00:36:43
Speaker
We would just write, like we could migrate all of the data just while the app was running, being sensitive not to overwhelm the RethinkDB instance because, yeah,
00:36:54
Speaker
uh reading db is great but it does not perform like postgres does um we could overwhelm it if we weren't careful uh and yeah we would just do that while the app is running and then um we could rely on the app also like doing dual rights at the same time and we would generally just uh and we would check for like created at uh or like updated at um timestamp deltas to make sure we didn't actually do an out of date right or something like that yeah that makes sense but yeah that that worked great um so that was how we did writes is we just shimmed in these pg writers and to save manager and it just that was it um and then the the read queries were harder um which was uh
00:37:41
Speaker
that's It's why we ended up picking Connex over like any of the other libraries that existed out there at the time. yeah ah For those who are unfamiliar with Connex, Connex is not an ORM, but it is more than just a ah like SQL client wrapper to the DB.
00:38:01
Speaker
is Specifically, it's like a query builder, which was important because RethinkDB's native syntax was um was also like fundamentally a query builder syntax.
00:38:12
Speaker
And so we needed something that was going to be similar to that if we were going to have high odds of getting the same um read queries from A to B. And being able to work with our code where it was like, like we had a lot of situations where it was like, you know, maybe you're on a list page and there's like a bunch of filters that you could choose to add or not add.
00:38:35
Speaker
And we had a lot of dynamicism in our code for like constructing those queries based off of whatever filters you passed. Yeah. So, you know, we didn't want to do like string concats or anything like that to do that. um But also like ORMs, like didn't really feel like the right answer either. That felt like also a refactor from the rethink code. um Yeah, you had a query builder already.
00:39:03
Speaker
but Right, exactly. Find a query builder. Yeah. um And in hindsight, I had no regrets about picking Kinex. I think it was absolutely the the closest thing that we could have found to what RethinkDB had. And I still think, even though Kinex is not an, I would say, a ah a querying tool that gets very much attention in the JavaScript community. It's not very sexy, um but it was very stable for us and I had good experiences with it.
00:39:36
Speaker
That's cool. So if if you had a magic wand, would you have still chosen Kinex? um I guess I'm trying to get at it made the migration easy, but then did it put you in a good spot going forward?
00:39:52
Speaker
I feel like it did. i mean, so fundamentally, like, there were definitely still use cases where we had more complicated queries than the Kinect's query builder syntax could do really well.
00:40:05
Speaker
Particularly, like, we had a couple of use cases where... um you know I mentioned we still had some JSONB columns that we had to bring over just for pragmatic purposes. And there were a couple of cases where we had to like filter off of content in there.
00:40:18
Speaker
And Connex is still fairly generic. like It's not only for Postgres. And so it's missing features for things like JSONB selectors and things like that.
00:40:33
Speaker
So there were some cases where we had to drop into um more ah like rock rearing. um But it was fine. it it like You could do it and the code was like a little bit scary looking, but you could understand it if you had a good understanding of Kinex.
00:40:53
Speaker
And for the most part, like easy things were still easy. um And so, yeah I would say there is no situation that I was like, I ever really was concerned with it. Um,
00:41:06
Speaker
you know And it's as a query builder, its scope was limited enough that you didn't run into cases where it was like, oh, this particular situation, if you use Kinex for it, it actually like produces this like really poor query that has terrible performance. like You're really working very close to the like raw um whatever the SQL is going to be with Kinex.
00:41:32
Speaker
And that was, ah worked well for us. And I also didn't feel like it was like too low level to like onboard other people to it. Like it was still a fairly straightforward syntax, especially again, for anyone that understands SQL, I think the ramp to learning connects was not that bad.
00:41:50
Speaker
um Yeah. Yeah. And at that point, like, this app runs on top of a SQL database, it's not unreasonable to ask your developers to learn SQL. Right. To your point of like the data is fundamentally modeled in SQL, it's going to be hard to understand what you should do if you don't understand how the data is modeled and how to interact with the the data as it exists on disk.
00:42:13
Speaker
Right. Yeah, and of course, with Connex being a, like, a SQL query builder, ah it uses SQL terminology for everywhere it could in its API. So it was like, you could just guess what it was going to produce and you'd be right almost all the time. That's cool.
00:42:30
Speaker
I love it. See, um... SQL alchemy is non ORM mode. Like the query and builder there has always looked like, I've loved it because it does that. Right. you're like, I want to select this thing. I want to join on this thing. I want, and so like, I, I know what SQL this is going to produce.
00:42:47
Speaker
And that's, yeah, that's very straightforward. Yeah. um Yeah. Honestly, like the experience made me a big fan of Korea builders in general. ah Like at my current job where like Django based shop and,
00:43:03
Speaker
i I want to love Django, but I have to say I'm like... i live in the hometown of Django right now. Oh, I didn't realize that. Yeah.
00:43:14
Speaker
That's awesome. Yeah. Invented for the local newspaper, which is hilarious. Like a town of like... College town of like not quite 100,000 people or something like that. But anyways, ah I want to love Django, but i joke that I'm Django's biggest hater because it's ORM is like...
00:43:32
Speaker
I think it's it's really great for like an old style Python, like pre type annotations and like all that kind of stuff. Like it it is super usable. And for the like, like straightforward, um, use cases that Django was really built to serve. Well, I think it's like, yeah, it's, it's still great for that, but we're becoming an increasingly sophisticated company and I'm, uh,
00:43:58
Speaker
I find myself disagreeing with the way the ORM works and it's an unfortunate emotional situation to find yourself in. Yeah. Well, it's time to tend move to a query builder, I guess.
00:44:11
Speaker
Yeah. um Cool. I want to come back and talk a little bit about what you're what you're doing now and and sort of what what you think of in the future. But I guess to put a to put a pin in this story,
00:44:26
Speaker
um so You've got your out-of-band rights. You've got your dual rights. did you As you um figured out how to roll out the reads, when did you how did you interleave that with the the changes to the rights as well? Because you mentioned that was the more complicated part.
00:44:43
Speaker
Yeah, so um the way that we sequenced everything was, you know, we kind of mostly stabilized um the um the database schema before we, like, really started actually writing data in production.
00:45:01
Speaker
um And we also sequenced, like, the tables that we cared about previously. based off of the relationship mapping. So we didn't really have like circular relations between models, which is great.
00:45:14
Speaker
So we mostly said that like we started with, um, believe it was companies was our like sort of like core accounts model, um, you know, for like multi-tenant system.
00:45:27
Speaker
And so that one was the one that goes first because it doesn't really point at anything and everything else points at it. So, um, um When we moved to ah Postgres, obviously we wanted those foreign key relationships to be enforced there. So that means for that to work, you need the table that's being pointed to to already be there. um So we sequenced like that.
00:45:51
Speaker
And um once we'd mostly stabilized the schema, except for maybe some of those like very, like, you're going to do them last kind of tables, um yeah we started doing the dual writes.
00:46:03
Speaker
And once we had the dual writes like running for a bit, we'd done the migrations, we did some sort of validation checks, make sure that everything looked good. And um then we would go through and we used like feature flags for everything. And so we'd feature flag for like one whole table. Again, maybe I'll use the company's table as an example.
00:46:24
Speaker
We'd have a feature flag for like, you know, if we should be reading from the company's table or the rethink table. And so um once we got to the point where one endpoint was like only reading from tables that had support for Postgres, like we felt the data was good, we validated and everything, we would flip a flag over. And now instead of reading from the rethink table, everything would read from Postgres for that one endpoint.
00:46:52
Speaker
And because we always wrote to both tables every time we did a write, um we could safely assume that all of those tables were directly in sync with each other.
00:47:04
Speaker
And so we would assume it would not matter if you ah were reading from one or reading from the other. So we would just flip it over. Yeah. And that also worked really well. Like, I remember those first few tables where we like turned on rights for it Like, okay, like now's the big moment. Like, ah yeah, are we, are like customers about to crash or what's going to happen? And um yeah, pretty much every time we like had nothing, ah nothing came to the century, like ah radio silence, which is like,
00:47:37
Speaker
you know, exactly what you want to have happen in that situation. yeah absolutely. You don't want to hear a peep. Yeah. Which also one thing we did, I forgot there was one, one of the ways that we did validation that I didn't mention was, um, prior to actually returning the Postgres data, we, uh,
00:47:56
Speaker
We actually had a ah stage where it was like it would read from both databases and sort of convert both of them to the like JSON that you the JavaScript would actually operate on.
00:48:07
Speaker
And um if there was a difference, we'd log an error. Cool. And cruel we got to see ahead of time anywhere where we were making any mistakes in production before we actually started returning anything.
00:48:19
Speaker
Yeah. And so like we really, we had uh, a ah ah very high like safety model for making sure that this worked. And in practice it it worked really well.
00:48:33
Speaker
Yeah. Yeah. You knew ahead of time, like you had done the comparison, you'd been dual reading, but just dumping the logs out. Right. So yeah, that's, you can't make it much lower risk than that. Honestly. yeah Yeah.
00:48:45
Speaker
That's cool. That's very cool. Um,
00:48:50
Speaker
and and And now they run Postgres. but Yeah, now they run Postgres. Yeah, I actually ended up ah leaving from Procore before it was all finished, but um but I've kept up with, you know, former coworkers and they got it done after I left, which is also like, you know, I was a really big driver for like the design of the project and a lot of the implementation because I had just like,
00:49:20
Speaker
I don't know. I really wanted to see it happen. I wanted it to work. I was like excited and energized by the project in general. um um And so you always hope that like if you actually leave, will people or really be able to like take up the mantle without...
00:49:37
Speaker
me to like provide context and guidance and things like that. And ah fortunately it it all worked out. They, they did eventually get through it, um which is great. That's awesome.
00:49:48
Speaker
Yeah. I think that's, it's, it's one of the hardest things of these like larger projects where it seems like you have a plan and a way to go forward, but actually it's all on your shoulders. And if you're not there, it doesn't get done.
00:49:59
Speaker
It feels bad to leave a project yeah in a state, in that state. Right. Yeah. So it's cool that you imbued it with enough momentum that it actually finished after you left. Yeah.
00:50:11
Speaker
yeah Very cool. um All right, we're coming up on time. So I i just want to ask a couple of ah quicker questions. Go for it. So you mentioned earlier that the your view of building software is like start with what's durable, start at the database and kind of build your way out from there.
00:50:30
Speaker
Has your opinion about how to build software changed with the rise of AI tools? Great question. So that aspect, i would still lean the same way.
00:50:44
Speaker
um and maybe with AI tools in general, I kind of have this philosophy of like... We mostly use Claude at my current company. And I started saying people like, what's good for humans is good for Claude or vice versa. What's good for Claude is good for humans. So I think most of the time, like when I think about AI tools, I really like think about like as a human, like what would be the best context to give?
00:51:11
Speaker
And I think that ends up being the same thing that's good for these AI tools most of the time. And so I would say like, yeah, like what is, what AI tools really need to be able to succeed is like, they need to know what the database is. They need to know how it's structured and ah be able to understand whatever form of like conceptualizing that that you have. So like in our case, it's like Django models, but um obviously different in other cases. so Cool.
00:51:42
Speaker
Yeah, I think that like that resonates. That makes a ton of sense. there's I've talked with a bunch of people where it's almost like the calculus of what you should do for both the humans and the machines has changed because doing it just for the humans, in some cases, didn't feel worthwhile.
00:52:01
Speaker
But the machines are kind of dumb and they're kind of bullheaded and they're very productive. Right. um So maybe you need to do it for the machines now as well and the humans still get benefit from it. Yeah, exactly.
00:52:12
Speaker
Like, yeah. But I love that. What's good for the humans, good good for the machine and vice versa. I think that's that's absolutely true. um What was the weirdest bug you hit through this migration?
00:52:26
Speaker
The weirdest bug? Good question. Yeah.
00:52:34
Speaker
in hindsight, I, I don't think we really had that many but bugs. We were, again, we were really, really on the like, uh, safety side of it, like trying hard not to have them.
00:52:47
Speaker
Um,
00:52:53
Speaker
Yeah, I think a lot of the bugs would have just been earlier on where it was like before we had the PG writers in place and it was like migrations and dual rights like could get out of sync from each other. And then it's like, oh, like, why isn't this working quite right? It's like, oh, like you assumed that and I assumed this. And so like this table is in a sort of incorrect state between the two. um Yeah, that makes sense. so Yeah.
00:53:20
Speaker
Not super exciting bug stories, I guess. ah You know, that's the best. Which, yeah, you want to be able to say that.
00:53:29
Speaker
um And last one. um What can what can folks do to help you out and where can they find you on the Internet? Yeah, great question. I am working on getting my like ah blog back up and running.
00:53:47
Speaker
i forget what exact sub URL I'll put it on. I'll probably make everything go to the right place. But my website andymunroe.dev. um I have a Twitter, but I don't post that much on it.
00:54:01
Speaker
But that's Yeah, andymunroe.dev is probably the best place to find me right now. I am technically on LinkedIn, but your messages probably won't get to me. You can also just email me at ah mail at me.andymunroe.dev.
00:54:16
Speaker
That will also work. or We'll put links in the description. Sweet. Cool. Well, it's been a pleasure talking to you, TR. This has been great. Thanks very much for coming on.
00:54:27
Speaker
ah appreciate your time. Absolutely. Thanks for having me.