Postgres FM - Managed services vs. DIY

Episode Date: July 14, 2022

A well as discussing pros and cons, we mentioned a LOT of different providers and tools, and a few good articles/videos too. 😅Here are links to most of them, roughly in the order they came up: How Auto Trader migrated its on-prem databases to Cloud SQLPostgreSQL Community Panel: UpgradabilityPostgres TV Open TalksPostgreSQL Conference EuropeHannu Krosing — excellent vacuum talkpg_docs_bot — browser extension for getting to the current docsAmazon RDS for PostgreSQLGoogle Cloud SQL for PostgreSQLHeroku PostgresCrunchy BridgeSpilo: HA PostgreSQL Clusters with DockerAiven for PostgreSQLAlloyDB for PostgreSQLNeonYugabyteScaleGrid PostgreSQL HostingStackGresTimescaleOrioleDBCitusSupabasePlanetScalepg_stat_kcachepg_wait_samplingEDB BigAnimalAzure Database for PostgreSQL------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Hello and welcome to Postgres FM, a new weekly show about all things Postgres QR. I am Michael, I'm from PG Mustard and this is my co-host Nikolai, founder of Postgres AI. Hello Nikolai, how are you doing? Hello, hello, hello. Doing great. How are you? I'm still traveling right now in Prague. Prague is beautiful. So let's talk about some Postgres stuff. Oh, so good. Very jealous, but also I'm in the UK and it's about 28 degrees at the moment, so it feels like summer finally. Yes, so today I was keen to talk about hosting of Postgres.

So specifically, when should people choose a managed Postgres service versus DIY or do-it-yourself? Should we even be considering doing it ourselves these days? If they opt for managed, what are the best options out there? That kind of thing. So yeah, I'm really keen to hear your thoughts on this. What are you seeing at the moment? My thoughts are we should always go DIY path. We should always compile kernel of Linux and so on.

I'm joking, of course. But the opinion is changing, actually, right? Because if you are DBA back in 2005 to 2010, and you see something which replaces part of your work, probably you will be very skeptical, right? What do you think? Well, yeah. So I deal probably with more smaller companies startups

generally and i can't think of the last time i saw somebody starting a new project diy but i do hear and see about it a lot more in larger companies and some of those startups that getting big they're starting to consider maybe for cost reasons maybe for a little bit more control they're starting to consider taking things if not completely DIY at least you know still on Amazon but not RDS for example. Have you seen ever like a company who started like 10 or 15 years ago and they started with self-managed Postgres and then moved to RDS or something? Yes, so I think I actually saw them blog about it recently, so I think it's okay to mention that the team at Autotrader, which is a big second-hand car online dealership in the UK, I think they're

in the process of, or maybe now have completed their migration to Google Cloud SQL from what I believe were, if not completely self-hosted then self-managed databases. I saw a couple of examples as well, including some very large companies, and I've noticed that they just understand that managing Postgres probably is very difficult. Also, I think it depends on how big your databases and workload. If you manage to split for example to micro services and you have dozens of databases it's easier for you to go to RDS because limits are quite far so but if you have very heavily loaded huge database probably it will be challenging because for example disks on RDS regular RDS they are like

network attached to EBS volumes so if they're not local and so latency is a bit higher of course so it's tricky but I saw examples specifically microservice architecture and very big company they decided by purpose to get rid of any self-managed but as usual it takes many years so it's a combination usually. So that's a good, like, is that almost a rule of thumb that if somebody's got either one or a lot of smaller databases, there's probably not a good,

are there any good reasons if I've got one or lots of smaller databases to not use a managed service? Well, I think if you consider some already grown company, you have many databases. Probably like two big reasons I see. First is move smaller databases to managed services and just not to think about backing up replication after a while.

But also there is another big reason. This isn't understood usually by upper management not by dbas because dbas usually think like let's do everything yourself like let's keep it in our hand or let's have access to pg data and so on or like process list and so on but upper management thinks like thinks like it's better to rely on Amazon or Google engineers and their infrastructure in terms of backups and related like RPO, RTO, related SLOs, service level objectives. Instead of trying to hire a lot of good experts it's very hard to find good experts and scale the process of their work so this second reason I think it's

for beer companies it's very important that's why like like RDS and others are growing I think I don't I don't have numbers, unfortunately, but I think from, as you said, like, everyone is on managed already, but not everyone, actually, not everyone. And sometimes people go back, actually, sometimes, but not often. Yeah. Well, could you give us, do you know any examples or the reasons why they went backwards? Well, the price is a big reason, of course. If you manage Postgres yourself, for example, on EC2 instances, and you can have one-year, three-year contracts, there are ways to optimize the price, and it will be definitely cheaper than RDS. Then you can do additional infrastructure optimization in terms of backups

and various clones for non-production use and so on so it's it's of course like much cheaper like i would say roughly like up to two times cheaper but cost is sometimes not a big driver i think it will change by the way we are in the beginning of some crisis coming so maybe reasons will like reasoning will start changing but also moving back sometimes like a weaker reason is pure technical so to troubleshoot some weird things it's not a fun when you go to rds and say something like i have an issue and their engineers say it's a postgres issue you go to postgres experts even even sometimes to Postgres mailing list or some various chats, and they say, RDS is not Postgres. Work with RDS engineers. It's not fun to be

between if very, very difficult incidents happen and you don't have full access. So I've been there a couple of times with my clients. It's not fun. So this is a technical reason to move back to take full control, but you need to have very strong experts. I think that's a really good point about the expertise. Third reason. There is a third reason. There's various extensions and capabilities that RDS doesn't offer. This is the one I wanted to bring up.

I think this is more of a reason to start on a DIY, or maybe one of the only reasons to start on DIY is if you really want an extension that isn't supplied by the managed service private that you want to go with. I'm seeing some of the newer ones really aggressively go after installing lots of the extensions.

But until a few years ago, a lot of them really didn't have even some of the contrib modules. I think, as an example, I think Heroku still doesn't let people install AutoExplain, which comes with Postgres. Heroku, unfortunately, there were recently big discussions on Hacker News why Heroku has not been developed. And it's a pity because it was a pioneer of managed Postgres many years ago. It was so great. But then they started to, after acquisition by Salesforce, I guess, they started to lag in terms of pricing. I remember helping several companies at least to migrate to RDS

just because of pricing and also capabilities. I have currently three clients who want to migrate to RDS just because of pricing and also capabilities. I have currently three clients who want to migrate and they say, let's postpone a little bit implementation of cloning stuff we are developing because we are in the process of migrating off Heroku. So I don't see anyone who is migrating to Heroku, unfortunately. But Heroku was great back to like 10 years ago. Well, I still think they have some features that others don't.

So I think they come with better defaults. So once you upgrade off the smallest instance, they keep changing your various settings to keep up with your instance size in a better way, I think, than some of the bigger ones like RDS. I had a friend recently move from Heroku to Crunchy Bridge, which is one of the bigger ones, like RDS. I had a friend recently move from Heroku to Crunchy Bridge, which is one of the newer ones, a lot of the team behind Heroku. I was really surprised by some of the defaults still there, and there was not as good documentation,

but everything else looks amazing. But he ended up migrating back to Heroku and upping his instance size. I think he thought some of the reliability was on the Heroku side side but it turned out it might have been something he was doing instead so it was interesting to see somebody that does rely on some of the heroku features really valuing those and ending up migrating back and the other thing i think they do that most of the others don't do nicely yet at least is major version upgrades mostly for you and now i think they require a little bit of downtime but i only just recently saw that added to google cloud sequel so i thought that was quite a nice thing that they still do that others don't well good thing here is

that yeah variety increases a lot and you touched crunchy bridge so we should already add one more set of players here which are based on kubernetes operators right and like i know alexander kukushkin the maintainer of patrony not long ago developed major upgrade automation for the building block of their operator it's called spilo so it's like a container image for a very ready to use in aws first of all but not only i think and he managed to achieve a good level of major create automation with a small downtime so it's improving but But I want to blame managed services for one thing. They usually don't provide access to look at PIDG data and at least have physical replication connection, right? I remember like four years ago or so,

I met the founder of Ivan, who is now big, very big. They raised a lot and they grow very fast. And I asked him, like, okay, very good. You are building a similar offering to RDS, but with more flexibility and more features probably and so on. Can you provide a replication connection and access to PIDG data and so on? He said, by default, no, but if you contact support, it's possible. Guess what recently their support answered? Of if you contact support it's possible guess what uh recently

their support answered of course no it's not possible so this this hole is closed also and lack of physical replication connection means some kind of vendor lock-in so if you want to migrate off managed service you must use only logical replication connection, which is quite tricky and complex and has a lot of issues if you have a very heavy workload. And we're talking about big instances, right? Because smaller ones, we still have the option of dumping the store, I guess. Right. But, well, what does small mean today?

Small today for me, it's less than one terabyte already. Okay, great. So I would not dump, like, of course, like when you do logical, actually initialization is dump restore basically, right?

So we need to do it anyway. Even for 10 terabyte database, we need to do it and there are ways to speed it anyway even for 10 terabyte database we need to do it and there are ways to speed it up but it's not fun i would rather prefer physical copy of files awesome let's talk about upgrades another day maybe um it feels like a whole good topic and actually i'll put it in the show notes but you did a really good panel with a few people i think it was a postgres vision recently. I enjoyed that.

Yeah, it's interesting. Yesterday, they invited me to Postgres Europe conference to talk about upgrades again. And they also accepted database branching talk. But I want to say once again, it's my public position that actually, I already booked a hotel to Berlin in late October. I'm going to come. It's very good. But I asked them, do they record talks? Do they have plans to publish it later? If they don't, I probably will not come because this is my position. Everything should be recorded and published later. I understand this is a huge conference, 500 people. Some of them will probably come to my talk. But I want to have a recorded session as well and to be able to share and refer later to my materials

because for me it's a huge trip. So that's why we talked today, and this is good because if someone is interested in the comparison of managed versus non-managed and Kubernetes, we can send the link right so i must say all postgres conference organizers please record and publish share and uh full disclosure i'm not sure you knew this nikolai but i'm actually on the panel for the pgconf eu yes yeah i knew it but i forgot yes okay okay so yeah i'm excited to see those, hopefully, if you do come.

So you also can pass my words to the organizers, right? Yeah, absolutely. And it's possibly worth giving a quick shout out to some of the work you've been doing on the Postgres TV channel to publish some of these talks. So some of these talks that were given that were not recorded in the past at different conferences, you've picked some of the better ones and recorded them and put them on. Remind me of the name of the series? It's called Postgres Open Talks because talks should be open as well. But the idea of that series conflicts with my position

to come to conferences that are only recorded, of course. So if every conference starts to record and publish later, probably this series will be over, but I will be happy, actually. That's a sign you really care about solving the problem, isn't it? We're taking a definite tangent here, but I had the same experience with my browser extension for redirecting people on the Postgres documentation.

I use it. Nowadays, yeah, well, yeah, but hopefully you won't need to soon. Right. Actually, I should stop using it. Right. There you go. So, yeah, but I'm happy because I don't I didn't want this extension to exist I would much rather it worked first time yeah google search

results are much better I mean we we know the problem but maybe our audience doesn't know the problem is that like until very recently if you search for something related to postgres you saw some versions up to 7.4 like 7.3 and so on. Like very, very old and it was not fun. And people shared these links very old thinking it's not old and so on, like a lot of confusion, but it was recently solved. And I recently saw some example of not good occurrence, maybe I should post it to the mailing list, but like 99% it works very well right now. Sadly, I don't think there's much we can do on the Postgres side for specific bad examples other than let time play out.

But yeah, so the worst thing for me was sometimes I'd forget that it was an old version, read it and then not realize about a feature. Anyway, sorry, probably should get back to hosting. We've got off-topic upgrades, off-topic conference, and off-topic search results in Google. Yeah, okay, a lot of off-topic topics. Yeah, so let's assume we don't need an extension that we can't get on a provider.

We don't have a really experienced DBA team, and we're not expecting to have more than a terabyte of data anytime soon. What are you thinking in terms of which managed services would you tend to lean towards in what circumstances? Well, it depends on which cloud you use. Maybe you are not on cloud. In cloud, on cloud. Sometimes, like very rarely, but sometimes people don't use clouds. But if you're on cloud, you probably should just choose the offering they provide. But sometimes it's awful. Like a few years ago, I would not recommend Google Cloud SQL to anyone.

They had only eight knobs to tune in from from almost 300 setting parameters it was not flexible not ready to to you cannot tune it you like it's not good but i don't remember some other problems they're offering head but right now it's improving a lot so very quickly right since since we mentioned yeah since we mentioned uhgres TV, we had Ilya Kosmodemyansky and I. We had a great guest, Hannu Crossing, who in the past was a Skype database architect, developed a lot of things in Skype.

And he talked about vacuum issues, and it was a great talk. So if you're interested in vacuum issues in postgres go check this presentation on postgres.tv and he works at google right now so cloud sql has very very strong experts and it's improving and they also recently released alloy db right so it's like it's very also interesting like among all postgres offering you can choose in cloud they are like almost pure postgres like rds postgres almost pure nobody except amazon engineers can say there are no patches. Of course, there are some patches there. But there are also very different databases, like Aurora, which has different storage,

or recent NeonDB, which is like open source Aurora, also with different storage. So we cannot say it's already Postgres. It looks like Postgres, it feels like Postgres, but not always. But there are also much more different, like AlloyDB, it's already Postgres. It looks like Postgres. It feels like Postgres, but not always. But they're also much more different. Like AlloyDB, it's quite different. They have interesting idea to have column store write and memory

in addition to row store. So row store converted to column store to have very fast aggregates for analytical workloads. Or we have also YugaByte, which is not Postgres at all, but kind of looks like Postgres. So among all these options, it's hard to choose, right? Right. I guess it depends so much on the use case.

And I think you're right. I think if you don't need anything special and you're fully committed to a single cloud provider already it makes sense by default to go with theirs unless you know you read the fine print and there's a really good reason not to or go on if you for example on google or azure or i don't and you want like and you're committed to be on this cloud. You still may think to use not their own Postgres managed service, but also offering from companies like Ivan or ScaleGrid because they work on all.

So you will have API and UI ready to work on any cloud if you move or expand to other cloud providers. So it's also interesting. But you know what we should choose we should choose kubernetes the only question is i mean kubernetes operators or products like like staggres why like because this is the same like managed but more power you have access and everything if you need to diagnose and so on. The only question is when. Maybe not today, maybe tomorrow, right? Because still they started a few years ago and they're still very young.

But I'm sure in five years, a lot of users will prefer Kubernetes under their full control compared to fully managed by cloud provider services? Well, I think for people that are using Kubernetes themselves, which is a growing number for their application side, I think those people, once they've built up that muscle and they understand it and they understand the sharp edges, then yeah, I think it does make a lot of sense. I think there was a lot of fear what is it fud fear uncertainty i don't know is it denial uh but i didn't see any good arguments against it and it does seem to really help in a few very specific ways but equally i

don't see most small companies needing it like i think there's like a scale where it becomes really useful when you need to provide a certain service level um maybe you have a really really low tolerance for downtime or absolutely zero data loss requirement and that's really a hard you know that kind of uh really high level thing that everyone thinks they need or want but not everyone actually is willing to pay for maybe is the main criteria but yeah i don't see any reason why not but i do think it won't be self-managed i think a lot of people will then pay for somebody else to manage that for them still to not need that kubernetes expertise in-house at least in the early days of a startup maybe Maybe, but with this approach, Kubernetes-based, maybe you can hire

a vendor of this Kubernetes product for Postgres. But with this approach, you can install anything. Postgres is very well known for its extensibility, but fully managed offering always

limits this. For example, Timescale extension is available only on timescale cloud. But that's due to licensing. I mean, among managed Postgres, it's not available on RDS. And it will never be available there. Because timescale thinks it's a reason for users to go to their cloud offering instead of RDS. By the way, what do you think, how successful will be this approach? Like, we have some very good extensions, very popular.

We will have it only on our managed service. So I saw, I think the founder of AureoDB gave a really good talk recently where he described some extensions are kind of normal extensions they add a little bit of function yeah so they add a little bit of functionality to postgres that makes it a little bit better in a certain way and he then also added a category of yeah i think he did call them super extensions so situs timescale i think he included aureole um basically extensions extensions are doing so much. They're changing things really fundamentally at certain levels.

And actually they change it so much that they can almost be considered, they're not a fork because they are an extension, but they have some characteristics of a fork where do they play nicely together? How easily can you migrate between them? It's like not fork, but different database, right, already. Almost, yeah, exactly, almost a different database. So, yeah, I think I can see why it would work for timescale because if you really want their expertise,

they are the best at supporting it and going to their cloud makes a lot of sense. But that's a really good reason for picking DIY as well. If you really want the timescale functionality, some of it it seems incredibly good even for non-time series workloads like I think did you see they did their skip scan implementation that's super useful in loads of workloads so there's there's reasons to want that and if you want it but you aren't ready to go to timescale as your managed service provider for some reason, you have to DIY. So yeah, I do think you're right.

But I'm seeing actually a little bit more of an excitement around these services that abstract even more away from you. And they come with, I think, limitations at the moment. And they're very, very new. But services like Superbase or Neon looks exciting. But I know it's not Postgres, but on the MySQL side, what's it called? PlanetScale. These services, I can see the excitement in the early startup CTO level.

They're promising them that you start with this today and we'll scale with you. We can scale up, we can scale out, and you don't have to handle any of that yourself you just keep talking to us like we're your database there might be some hidden dark secrets that are yet to come out of the you know the reality of that but it seems super compelling to me as a as a simple option i don't need super complex things what do you think about security issues with those services because for example, if you are a customer of Amazon, you have trust with them. They don't access your data in RDS, although they could. There is some trust. But if it's a smaller player and they run your databases

with your data inside their AWS account, for example. Like how to achieve good trust here? Well, I think security is a really good point. I think reliability as well. You know, AWS, there's two angles. One, they've got a track record of good uptime, good reliability. And two, if you go down or if they go down and therefore your service goes down, half the internet's down as well.

So customers aren't that surprised that your service isn't running because nothing's, you know, their Spotify isn't working or their Netflix isn't working. So I think there's a few things you get with these big players that people underestimate a little bit. these new ones they sometimes they're built on some really complex um infrastructure to handle some of this stuff that that does that does fail and sometimes in very spectacular ways and could can result in a lot more downtime than the normal few minutes so i think complexity does come at a cost or sorry they're they're taking away the complexity from you but they're adding it on their side and i don't And I don't know that I would trust something that needed that myself. For me, as a database performance expert, they add complexity because if I manage Postgres

myself, I can install very useful extensions in addition to pg-sus statements. Let's talk about performance, right? Yeah. pg-stat kcache and pgWeight sampling, these extensions add two important dimensions to such statements. One is physical, so you can see disk I.O., PgStatK cache, disk I.O. and CPU, even system CPU, user CPU, context switches and so on.

And another PgWeight sampling, it provides similar analysis like performance insights and addables, RDS. Without these two extensions, it's very hard to understand, for example, which query consumes a lot of CPU, but not very data intensive. Sometimes it happens. Or similar things. Producer's statements sometimes cannot answer some questions, right?

And if you don't have performance insights like RDS has, sometimes you are very blind. So they add complexity in terms of query analysis and performance optimization to me. Because with these two extensions and proper monitoring, it makes it very simple to perform top-down analysis of workload and find the worst queries. So, agree or no? Yeah, well, I think we're talking about different ends of the spectrum. I think maybe you're right that that's the kind of thing that the CTO at a startup picking, let's say, let's pick on PlanetScale because it's not even Postgres.

Let's say they pick PlanetScale today. Maybe they're not aware of that kind of limitation all the way down the line, or that they're completely reliant on PlanetScale's tooling to get that. I think PlanetScale is not a good example because people choose PlanetScale, my SQL users, because they are also developers of FITES, and if you need to build a very big system that requires sharding it's

actually almost standard de facto so you probably will go there because of that right or use Vitesse and that's it it's interesting that it's very specific example and I'm interested in that example but it will move us away from our main topic right now. Well, how about Superbase then, in terms of their promising- Superbase is for smaller projects also very specific. They do very great thing, but for smaller projects mostly.

Like I think- At the moment. At the moment, right. But their main offering is like, we provide you out of the box API authentication capabilities and this real-time Firebase-like thing. But the question is like what do you offer if my database is 30 terabytes and I have 30,000 TPS? I need specific performance analysis tools. I need to be sure that you will survive, for example, five terabytes of wall per day backed up properly and so on.

And this question is interesting. I'm not sure SuperBase is ready for this scale yet. No, but who it like? I'm thinking, looking through the ones I've got, I think EDB, for example, came out with a managed service recently and Crunchy, of course, both of those are Postgres consultancies turned into managed service providers. Are they, would you think about those? Do you think they might have some answers to this kind of thing?

I'm very negative today for edb i wish they they provide better consulting still because i have couple of clients over the last few years not couple several clients who suffered from their consulting but it's good for me because they went to our small shop and we fixed problems so I haven't checked their managed service I don't know no no no no comments here maybe it's good but again like it's it's very difficult choice right so yeah many many but RDS is growing like Google is interesting. Microsoft has the power of Citus. So it's a very interesting time we live in.

But again, in a few years, most people will choose Kubernetes-based, I think. You heard it here first. Say again? I said you heard it here first. Probably not first. People have been talking about this for a while. Awesome. I know we could probably talk about this topic for quite a long time.

Is there any last things you wanted to add before we wrap up? Well, I think physical connection is a big limiting factor and every company who wants to deal with managed Postgres should think about this. Do they need it? And also low level and diagnostics capabilities so if they're okay without it it's a go for for this managed service i have many clients who work on rds and other managed services and i like helping them as well because sometimes it's fun to deal with their support engineers it's sometimes it's left, but it's a specific kind of fun, I would say. But in general, I think we have...

Like EDB called it big animal, right? Yes. We have a huge zoo of animals of various kinds, right? And I definitely like the breed that is associated with Kubernetes. So let's probably talk about this area sometime soon as well. Sounds good. Wonderful.

Well, thank you everyone for joining us. I'm going to put a bunch of these links in our show notes. We welcome ideas, suggestions for future topics. And yeah, thank you so much Nikolai hope you have a good week thank you I also want to thank everyone who provided feedback to Michael and I wanted to say you can provide feedback to me as well so like but for Michael is also good and don't forget to subscribe and also share please share this channel this post

to the fam link and and links to specific episodes this will help us to grow wonderful thank you michael take care speak soon bye bye