When Postgres is Enough: Postgres as a Coordinator - Michael Cooper

So um I am Michael Cooper. Uh I am founding a company called AG Ledger. Uh and uh today's conversation we're going to walk through my learnings on this journey. Um just a quick word of of who we are. Um and so I build a or I'm building um a a tool or system that signs and change every signs on a chain every AI agent action. it's in into into an appendon ledger that can be verified offline. Uh what we're trying to solve for is pretty simple. Uh if agents are going to do work for you, then that record of what happened can't just be a log because agents can one uh uh change the log and two if you ask an agent what happened after the fact, it will just make things up. So you have to capture things ahead of time and you have to uh validate the difference between the intent ahead intent at first and the and the uh action that happened as well as the results. And so it is at the end of the day a log uh in all practical purposes but we have to treat it as a leisure because agents are just different and they work with systems in a different way. Um, and so what we're going to talk about today or what I'm going to talk about today is, you know, the learnings I've I I've um had on this journey of of building out this product and how at the end of the day I've ended up uh building what is in in essence a a an entire solution built on Postgress and Postgress only and that is so this is also me just giving back to the community because as Postcrest is is a pretty incred incredible product and so I want to kind of thank everybody here for um for building building this thing out. And so uh just to explain where I started, I started building out a a conventional SAS with the usual uh distributed stack. Uh it was it was on AWS uh staple services. Um all the things you'd expect from a SAS perspective. Um and then once we started once I started building it out, I I kind of went through um two different ways and two different reasons for simplifying the stack. And I'm going to talk about the processes of simplifying the stack and how the processes of simplifying the stack ended up at me just being on Postgress. And so this is is a journey of how we got there, the learnings um many of which were self-inflicted from my own ignorance. Um but the learnings of how I got here and what's working for me in terms of uh how it relates uh to uh hosting Postgress and and I'm happy to stop along the way and answer uh questions as they come in. And so as I'm moving from a conventional SAS, we simplified in two ways. Uh we expected some some serious tradeoffs. And so I was doing this expecting a lot of bloat in my code. And I was doing this expecting a lot of a significant decrease in performance. And my at the end of the day, what's kind of fun and so to to lead with the findings up front, I ended up with a smaller codebase, uh less moving parts, easier to manage and configure, and my stack is slightly faster. And so it's a fun journey. It was a fun journey for me to be on. Uh had I known these answers ahead of time, but it was it would have saved about four months of my life. But um let me walk kind through what that what that does. And so the first thing that matters is if you're looking at um if you're looking at my use of Postgress and my use as Postgress as a coordinator, it's incredibly important to understand uh the data that I'm using, the shape of that data and the requirements of that data. So there are there are three there's three kinds of uh state come out of this product. The ledger itself which is assigned record record hash chained events now is append only uh immutability triggers no updates nothing ever deleted uh uh ID7 keys so inserts land in the right uh hand edge uh of the index instead of scared through it. I do support other versions of Postgress. So there is some wrapping I do sometimes on those keys. So I'm always I'm always on V7, but uh on on the older versions I'll I'll have a a wrapper on it. And if if anyone has any questions on that one, let me know. But I here I'm just going to assume that we're on uh everything that I'm running is native in terms of the that that the the talks and learning that I'm going to give. Um and the Q is for for me then. So issue one is my ledger and the needs of the ledger as it relates to Postgress and the second and then is the is the queue which is the exact opposite. So every row is created work completed and deleted basically in an hour and nothing in it outlives the the work it describes. So it generates nearly all of my vacuum work and so a lot some of the learnings we'll have below and and data I can share if anyone's interested ping me uh tuning of the vacuum and testing of the vacuum um because there was some very serious concerns that I was going to hit that and we'll we'll talk about what that means as we go. Um and so from a rebuildables cash cache invalidation and rate limit counters uh lossable and rebuildable from postcress at any time. So they they get the weake weakest durability that we could accept. Uh batch works we do run uh checkpointing and integrity sampling and is scheduled incremental and and off the hot path. So I do have a slightly different data set than others do and it's it's worth noting that along the way because I'm I'm not saying at the end of the day my choices and my architecture is going to work for everybody but it does work for me and it does mean that I do have some especially as it relates to uh coordination of of tasks and things um some some unique needs and I do uh stress a system in in other ways. One thing that is incredibly important if anyone else is looking at designing a service uh uh based on on Postgress you do have to realize it in the era of AI everyone talks about AI development coding and you know AI assisted in terms of building it out that's interesting but it's not as interesting as AI as your customer and so in in this particular case my service is entire is a deterministic service there is nothing in my codebase that is uh probabilistic. So I I'm a deterministic service, but my customers are probabilistic agents. And so uh they are ridiculously bursty. Uh they are really um uh they're they're know-it-alls at times and go down weird odd paths. And so there's a lot of what I have to do to to build my service in a deterministic way to kind of predict where the agents are going to go off the rails, bring them back on the rails, and also uh keep those agents and the very bursty nature of those agents from um affecting my overall service. And so we'll we'll talk about that as we go as well in terms of the architectural choices you have to make when you're when you're building out a service when agents are your customers. Um which I don't think is a topic that gets as much it gets as much conversation as it should. Uh we all kind of talk about you know using agents to build our services. Um that's kind of overdone but uh use having agents use our service is is kind of the new fun and interesting space that's changing uh every day. And so again just to recap and where I ended up um the conventional SAS the stack you would reach for uh ECS Fargate API worker uh ALB with WFT. Um uh my first introduction to Postgress in this stack was actually just Postgress within RDS. And so it was just going to uh hold um uh certain sets the the the ledger workload itself and everything around it. Uh especially caching and things like that. Um and um my data SKs and things along those lines were not going to be in Postgress itself. they they were all in uh Reddus eventually and then once I moved to I need to be able to provide the service as a self-hosting service to my customers. Um Reddus made less sense and then we switched to Valky. Um so Elastic and Valky and so with Bull MQ all the usual suspects the the the process worked fine. It was just really really complex. And if I'm going to send this as a self-hosted service into a customer environment and need that needed to work in an offline mode where I don't have um you know instant access to what's going on in the environment. I can't be patching things myself. I have to wait I have to ship code to a customer for them to patch. Um then having all of these dependencies suddenly becomes a liability and adds to the complexity of my life quite a bit. And so the first thing I dropped, and I know this isn't a Postgress conversation, but I do think we as a community should start talking about it. The first thing I dropped is was the OM and and and and Drizzle. Um I know this is going to be fairly controversial. Uh I get to say it because I I'm an infro guy at heart. I'm a networking guy. I I go old school networking. I know if you look at my LinkedIn, you'll see that, you know, I had two stops at at open source data companies. one at at Confluent which uh we went public with Jay and and with Kafka and loved loved everything that Kafka was doing really cool and astronomer with Airflow and so that might lead you to believe I'm more of a data guy than I am. I'm not. I'm an infrastructure guy at heart in training and I'm newer to the Postgress uh world and again I'm I'm a a recent advocate and a huge fan. But with that said, um all the things that we were using uh that I was using Drizzle for, which was to help me understand SQL, um wasn't actually helping because what I ended up having is uh more places for bugs to hide. And um using an AI agent to help me structure my SQL rights were a heck of a lot easier for me than using an AI agent to help me structure my Drizzle and then help me structure and uh debug my SQL that the Drizzle was was pointing out and then help me structure and and look through the the JSON. And so at the end of the day, the the the from my perspective, the primary value Drizzle was providing providing was the abstraction or or making SQL easier to understand for humans. Um the AI assisted coding was already making SQL easier to understand for humans and was already um uh was doing a lot of double-checking for me. And so it was it was a level I didn't need in the first thing that came out. Not part of this conversation, but I do think it is going to be I do think it's a valid conversation that more and more people are going to have. Um but then the second thing that kind of definitely shifted the skew is if I'm selling something that has uh agents doing work against real systems of records that and I'm asking the agent to document what it's doing why it's doing it when it's doing it then everything in that d everything in that agent's record is incredibly proprietary to the customer. There's no way that customer is going to trust that data to leave their environment. the agents going to be working next to data and next to systems of record in their environment and if that's the case then the agent records have to stay in their environment too and therefore that's that customer self host hosted and the requirement that everything happens within the customer's environment from from a data data integrity point of view and a data sovereignty point of view just means that I now needed to look at the entire stack and so uh RDS Postgress is is still something I I I expect most of my customers are going to use or or probably Aurora, but it's going to be within their tenant, not mine. And so that service is going to say they can they can be running a native version of Postgress in my for my service or they can be running a hosted, but it's theirs, not mine is the is kind of the issue. Elastic Cache and Valky was kind of the first thing that we move moved uh to. Um but then now I've got two staple services I have to manage and that started to become problematic and scary for me and I'll and I'll talk that through here in a second. Um, and then all of the rest of that scaffolding I just can't ship with uh with my service because my customer is going to have their own. And so I have to build all the infrastructure necessary to interact with what I'm expecting to be their load balancers and the rest. And I need to be able to ship very clear APIs, agent friendly APIs to be able to interact with this and and clear provide clear uh hints in the rest and and very good documentation. Um, but I can't ship my own service here because there's no way a large enterprise shop is going to, you know, take a allow me to to ship ship those services. And so the the the conversation for me did not become, you know, what's the best service? What's the best solution for any of these stacks? It was it was never a conversation of is Postgress better than Valkyrie for this particular function. it was what's the least number of uh dependencies I can take in my product and if I take these dependencies do I do I see a a trade-off for performance somewhere else and you know starting you know starting with valky which we'll talk about in a second there there was you know a lot of things can do better than postgress but there's a lot of things that can't do period and so I if if that's the case hey postgress does these 12 things valky does these four things it might do one of those four things better than my 12 guess what the answer there is postgress and so that's exactly what we started so it was BMQ and Valky uh and it's it was a hard it was it was a hard lesson for me to do because I spent a lot of turn time learning Valky I became a huge Valky advocate overnight I was I loved what these guys were doing I I I love bowling Q spent a lot of time reading those things and and learning about it and and then halfway through when you realize Yeah, I uh while as cool as this thing is, I don't actually need it that it was really painful to to strip that out. And so it's it's not a jab against Valky or BMQ or or for any of the purists on the phone saying on on the call saying that they hey you know they can do things especially with you know managing Q that um that uh Postgress doesn't do nearly as well. I totally agree. I just it it's more dependencies means more complexity. more complexity means more bugs, more issues, more downtime, um more patches. Uh the the trade-off wasn't worth it. And so, uh Bowling Q back in cross instant cache and rate limit counters. Rate limit counters being my probably the biggest problem for me to solve and and it spent a lot of time testing and tweaking that. Um those issues uh those were the things three things bal is can I move into into Postcrest? Uh the answer is with PG boss yes I could and so it was Postgress plus P pg Boss meant that everything Valky was doing got stripped out. Um there are some uh I've got some demos here if anyone asks questions in terms of wanting to look at some of the um uh stats but uh I'll skip that for this and uh we can go back to that if there's there's questions that are specific to it. Um, and so that PG boss is kind of the unsung hero here. And I know Postgress gets a lot of a lot lot than I'm a huge fan and and and as everyone else here, but um based on well my web traffic is I get far more hits on my on log on lessons learned log uh blogs I post about my my PG boss learnings than I do on my own product which is a little bit uh embarrassing for me. Um there's there's many of you actually kind of uh running into to the love of of PG Boss as well. And so uh in moving PG Boss or in moving uh Valky out PG Boss in here's some of the things that you know kind of uh here's here's a here's what's working for me. Um I've got one PG boss. I've got two processes uh for my API that's send only um and then uh with with API it so API runs in a supervision and schedule with supervision is scheduling off so it is affect it is functionally uh send only uh worker runs as a full instance it supervises owns every cron schedule uh registers every handler PG bus keeps its own schema um which it installs on the first boot which is a migration baseline kind of deliberately excludes and so it for the most part it it's working for me uh out of the box. Um there are uh six wrappers I've had to build around it because of uh issues I was consistently hitting in my own code. Um I I've got those listed here. So uh register batch worker unique in Q insure Q and pool adapter. Um, I've spent a lot of time on those rappers. And so, um, happy to to to send those over to anyone if you if you want to see what I'm doing on those. Happy to share that out um or or uh or build a blog around them uh so that you can see the the code I'm doing the code I'm doing here to kind of handle some of those situations. And so, um, one of the biggest, uh, things I'm I having to, um, uh, adjust, um, or the kind of the first place I I hit problems because I wasn't using dribble was or and or in general, uh, actually was, uh, uh, having SQL uh, native SQL um, hitting uh, PG boss. that's not something that that seems to be as common as I think it's probably going to be. And so some of the rappers I'm write I I'm putting um are entirely around it. Um, one of the other dependencies I took or one of the other things I I did with uh PG Boss, which you know, looking back probably cost me a lot more time than I needed to is, uh, I built I wrapped PG Boss Boss and a replace assuming that I might want to replace it later. Uh, especially for maybe, uh, PG Bounce for for um, uh, some of the connection work. Uh, and the more I get into PG Boss, the more I just love it. And so I don't think it's coming out anytime soon, but I did spend a lot of time uh wrapping um PG Boss so that it wasn't a it was something I could I could take back out. Um but with that said, huge fan of PG Boss. If you guys aren't using it, you should you should think about it. Um and if there's anything in my learnings here on PG Boss or any of the the things that any of the rappers I've built, um happy to share. Just ping me. Um and so couple of uh issues on trial. Um uh kind of the one thing I was expecting uh most people to the I was expecting uh the more research I did on what I was doing that you know vacuum was going to cause issues and you know if you if your vacuum is set at you know 6 hours or 12 hours or whatever then you know everything I'm saying would run just great and then when vacuum hits 12 hours later your your system comes to its knees and so I spent a lot of time with the autovacuum tune uh on on my different job t tables. And one of the first things I did is actually just uh I moved vacuum up to a 1 hour retention um on my hot cues because again I'm building a a stateful ledger. Uh things are writing to the ledger. So I don't actually need the logs to reconstruct my ledger is if my ledger isn't accurate at in real time then u then I've got a fundamental problem with my product. And so I I do have a different need here because the the I don't need those logs for to to play back uh to play back things. Um and so I can I can kill the logs after an hour. I can also then autovacuum after an hour. And so uh I did a lot of the different um uh performance modeling on that. And if we have time, I actually have some uh test testing I can show in terms of what happens when I um change things because of skip lock or change scenes because of um uh when I the different um tweaks I've made to see whether or not um the performance holds up. But uh to to to skip to the chase, the the honest answer is is it didn't change much. Um, and so yes, I moved to the issue I was going to have a 12 hours until 1 hour, but I didn't I don't actually see big long cues building up. I don't I don't see things run away. Um, I see my performance slightly degrade at times and, um, and life and life moves on. And so, um, kind of the biggest core issue as expecting in moving this over and taking the the Postgress and PG boss dependency, um, we just haven't seen. Um, again another demo we can we can come back to and and I'll post these demos because uh this it's mostly just how my product works versus uh how it impacts uh uh Postgress and and PG boss just to kind of share my learnings. Um but they they kind of they they're better suited to specific questions and so I'll I'll post them uh I'll include them in the in the post follow-up so you can you can watch them if you if you like. um at at another time. And so uh where I'm at so where my product is today is and it's a huge benefit to me as a as a founder and as a startup is I literally have one stateful thing left and it this is not by choice. This was completely by accident. I was not I I was not part of the just use uh Postgress debates. Um I was struggling through the which part which tool should I use here uh at the same time as you you all you smart people were having the debate I wasn't smart enough to know that was a valid question and so I I have one thing left and it's it's Postgress and so uh Postgress holds the state the chain the queue the locks the schedule uh the invalidation bus as well as where my my rate limit counters um one thing I learned far later than I should have is uh I was putting a lot of my schema in code. Um which as I as I have a a a tool that is built to work with AI agents that means that schema changes fast and you know what was working for uh uh codecs yesterday may not work for codeex tomorrow. Codex might be an agent based on a codeex model might go in a different way. And so I was changing my schema via rebuilds on a really regular basis. And um that was that was a learning and a fascination that I once I realized I can just start moving a lot of that schema into Postgress as well. And then measure the impact in performance and there are some there are some performance concerns in doing that. So moving schema out of code to uh Postgress does impact but not nearly as much as I thought and the flexibility of allowing then my customers to be able to modify their own schema and shape the data uh in somewhat real time while still locking things down for for the agent. So they, you know, they can adjust their the shape of the data or the shape of of what they're responding to an AI agent with in near real time if they choose to or they can hold the line uh and the agent kind of does um follow. That is an amazing capability I only got by um moving a lot of my schema and a lot of things that were code to start with just into data in Postgress. And so, um, another, um, you know, I I know all the old school Postgress people on the on the call are kind of are are rolling their eyes because, you know, the rookie didn't kind of know that, but, um, it's also just it's a tremendous, uh, capability, uh, especially in the area era of AI because it allows me to go it allows me to change allows my customers to change the shape of the data as fast as our agents change. And so, um, I didn't mention it ahead of time, you guys probably could have guessed. Um I'm I'm a Fastify uh is by my front door and so um so all my writers come through there and that that was never in doubt. That's kind of the only piece of my stack that's kind of always been there. Um and really happy with that choice. Um uh I've had to write a lot of rappers and and and had a lot of learnings on that front too, but uh no regrets there. And then my the the PG boss lives as a library throughout my post P post P post P post P post P post P post P post P post P post P postgress and as I mentioned earlier when I was putting it in I was you know the first question is is you know can I live without Valky and the rest of these things and the second question question is is you know is postrest or is pgbos the only thing I'm going to need here or is is it pg balance and and are there other libraries I'm going to have to add as I've gone down this path I'm more and more comfortable that um I'm staying on PG Boss and so I spend a lot of time wrapping it so I can pull it out if I need to, but I just I really don't think I'm going to. Um and then I do have a a one service that pretends to be S3 and it just allows the anchor for the for my uh uh my chain. Um and so that's the extent of my service. And so with this with this shift to Postgress, I now have a very um understandable very small um I have a lot less code. It's more performant in many ways than my previous stack. Um it's a fraction of the cost to run than my previous stack. And I can ship that to an enterprise customer. uh they can run that uh off uh completely um uh disconnected from me um and they can just take patches from me on a on a fairly regular basis. And so for the most part my you know my managing SAS switched to me shipping tarballs. And so it it's a it's a very different um solution that I've ended up with. But that's prime that's because the world changed. Customers expectations of where data goes has changed and customers expectations of well why would I just why would I buy your SAS service for you and take that dependency? I'll just write it myself. And so u because of that you know that third issue where you know many companies are just going to write these services themselves instead of taking a dependency from somebody else you my feeling is I just had to make myself ridiculously easy simple and clear to understand and also make myself cheap enough that there there's no benefit for them to write it themselves they might as well just use mine and you can't do that with a 12 dependency stack you can do that with um with Postgress and Postgress only and so um a huge fan of of what Postgress plus PG boss has has has given me and and really happy with the solution as it exists today. And so um one of the things I mentioned before is uh you know we had a lot of concerns and one of the first ones when I when I reached out and asked my friends and said hey you know you know what is so from an honest downside the perspective they said yeah you can do this but you're going to pay for it in performance you're going to have to run much bigger boxes than the rest and that hasn't been true. Um, one thing that is true and if you're going this route, you have to spend a lot of time thinking about um, which I have spent a lot of time thinking about and we we tweak and we allow our customers to tweak. Um, we have not paid for it in performance. The, uh, uptick in some of the things has been measurable but not meaningful. Um, the connections is and so uh, you do have to plan, you do have to pay a lot of attention to your connections. you do have to um spend a lot of time uh managing those. But that's not overly unique to any data service and that's was probably something I was going to have to spend just as much time on before. And there's a case to be made that I you know I only have to manage connections in one spot versus in 12. And so there's a case to be made this is simpler to do but the number of knobs I have are are smaller. And so, um, the one real Bill, you you can't overlook it is connections. For me, we I'm comfortable in with my with our performance and our capability of getting around it. Uh, as long as you plan for it. Um, but it it is real and it it was uh um and it can't be overlooked and um it can't be overlooked and and it shouldn't be overlooked and it should it should be called out. And so the uh kind of the last thing I want to say uh uh on this you on my journey and where I'm at and I'm happy to double click on any of the additional concept content. I mean I think we originally p pitched this as a as a three-part series. This kind of being the overlay of how we got here, what we did and then we could kind of double click into others. And so if there's interest there we can do it. If not then I can just write me and I'll and I'll and I'll provide my learnings in an email to you. Um it is incredibly important to note I am not claiming that Postgress won these bake offs. I am not claiming that you know I put Postgress against Valky and Postgress one. I am not claiming that um you know that PG boss means I don't need BM Q. Um what I am saying is in the era that we are in today good enough is sometimes much better than um any other solution and in our particular case um in my particular case um Postgress was was the good enough solution and it helped I'm far my my final solution is much better than um what I started out with uh by choosing again the path of good enough and the path of good enough for me um ended with postcross and so uh two things first uh if you're interested in kind of my learnings the easiest way to do it is uh I have a free version of my product it takes about five minutes to set up on a uh on on a docker node and uh so you can point your you can point your uh you can either do it manually or point agent at AG Ledger and have a a local instance of what I'm doing running on your box which would get which would spin up um uh my version of or my modifications to um uh see to Postgress as well as my configuration of PG boss and you can see my entire service run you can do uh performance against it and the rest uh if you're interested in uh my learnings or or some of my testings because you're you're building out your own service and kind of want to learn um the ping me at michael@agledger.com or just you from sorry my agedger.ai or you can uh ping from the agedger um website um and you you'll reach me um life of a founder. This is pretty much all I do and I would be um insanely grateful for anyone to talk to me about it. Um and so mainly because my wife and kids are sick of hearing about it and so um it's a really fun journey. I'm having a blast and if any of you guys are on the same journey, uh ping me and I'm happy to help. Um the one kind of uh one of my favorite stats to point out is uh I have done um scale testing on my solution. Um I have done it over uh days at a time um with you know very adversarial um chaotic uh testing environments. And for the most part, my my records were never processing in in less in less than I mean I think the longest I ever got my cues out to was about 2 minutes, which is way too long. I agree. But even when they were c 2 minutes, it didn't mean anything stopped processing. It just means some of them were 2 minutes late. And so um it to me that means that the infrastructure is solid and I can trust these things and even when they get behind it doesn't fall down. And but my favorite part about that testing wasn't that it can happen. Of course it can happen. Um I did all of that testing and all of my max scale testing on AWS VMs that cost 65 cents an hour to run. So these are very small VMs. This is not what my customer is going to be running their infrastructure on. Again, 65 cents a minute and and that or 65 cents an hour that included the compute, the storage, and the database. And so for 65 cents an hour um my infrastructure was was for the most part bulletproof for 90% of the customer workloads I was expecting my customers to hit. And so to me again it it does not speak to um you know that's the correct tuning or hey look how much you can save with postgress. Those are not those aren't actually what I'm testing. I was just testing whether or not the code failed out and the answer is no. And so Postcross with PG Boss um under chaotic conditions with you know lots of things breaking and under artificially constrained um environments I'm getting the performance I need to be comfortable shipping the solution um on and and to that um again it's a it's a testament to not what I've built um it's a testament to what this community has built in in Postgress and and and in PG Boss and so If anyone listening uh knows the people contributing to PG Boss, tell them I owe them many beers. Uh I love what they're doing. Um, and obviously um, huge fan of the Postgress community and I I'll continue to look for ways to give back in my learnings and my lessons because what this community is built and what this product what this product is is is pretty impressive.