Ask HN: Do you self-host your database?
Now that there are so many options like
AWS
GCP
Azure
Digital Ocean
Heroku
Render
Firebase
Supabase
etc..
I was wondering does anyone these days Self-host databases on their infra?
I was wondering does anyone these days Self-host databases on their infra?
234 comments
[ 2.1 ms ] story [ 254 ms ] threadIn my experience, it is rare to have 100+ TPS applications. As for FAANG scale infra - YANGNI!
How are you managing a. monitoring b. backups for self-managed?
what's the scale of the application(s) that uses these data stores? How do you monitor these clusters?
The main reason being that the Azure postgres offering was not fit for (our) purpose.
We did start with the Azure offering, and our default is generally to use the cloud offering.
I am currently evaluating the same choice - Azure Postgres vs self-hosting in my k8s cluster.
I elaborated a litle on why here: https://news.ycombinator.com/item?id=24607664
but boils down to; - enormous performance difference - advanced/fine grained user roles (+default permissions, not settable without superuser) - use of extensions (productivity boosts for us come from; Oracle FDW, TDS FDS, TimescaleDB) - private network endpoints - lower cost
Also single managed server doesn’t scale well.
Do you have automated failover set up?
Also, didn't know about Aurora's per query pricing. That is crazy.
I tend to leave the decision to switch masters to humans, and just automate the process. Unless you have bulletproof conflict resolution/reconciliation, automating failover is inviting a conflict that leads to major pain and a large effort to fix. Better to have a few minutes of write downtime while waiting for someone to make the decision (which includes making sure the dead host is truely dead and not just split brained)
As traffic grew we'd encounter evermore outages - it seemed that some percentage of requests between nodes would be dropped (despite AWS's insistence to the contrary) and the nodes couldn't seem to gracefully recover. With the rapid growth, and tiny team we didn't have enough time to fully get to the bottom of it sadly.
The final straw for Galera for us was when we started exploring multi-region replication (us-east-1 and Singapore). The bandwidth between regions was just not high enough to support a cluster. Aurora offered all that and more (though MariaDB to MySQL was a bit fiddly). Since migrating life (and uptime) has improved immeasurably.
Might consider researching using pg_bouncer later (but really not needed right now).
Pricing and support for vendor solutions are too bad for small scale app.
In-line with my experience too.
Where are some of the hard things that you learnt self hosting these many services?
So difficult that I think it's worth writing an article for it: https://dev.to/pikachuexe/postgresql-logical-replication-for...
But staying update with latest version is possible and relatively easy with Docker: update the Dockerfile, build and push, run deploy via Ansible playbook
How do you handle firewall? Just open up the source IP with the IP of wherever the Application is hosted?
> you are only listening on a private network anyway what if i host my app in say some where else than the DB?
> better yet only listening on loopback or a local socket. For that app and db needs to run on the same server. Isn't it?
Postgres hot hot with 2 servers.
For clients I always recommend PaaS
I do this for cost reasons, self hosting the database is so much cheaper than the managed options that for the same cost I can run the entire app stack, load balanced web servers, etc.
No containers anywhere but maybe in the future I'll add some.
It's only reachable from the app servers, backed up to tarsnap.
It's really not a lot to learn and hasn't been a chore to operate.
But for me uptime isn't critical, I can happily be down for a few days if needed. It hasn't happened once yet but I can tolerate it were it to happen.
Plus this is how I learn. By doing it I learn how to do it and what should be considered. When it goes wrong I learn how to avoid that in future and what I should do to minimise risk of it recurring.
I find the database to be the easiest past to manage, it's file storage and availability of that which I outsource to AWS S3. The volume of that is harder for me to trivially solve. Self hosting Ceph seems more intimidating. Postgres is easy though.
As the project started to fade out, I moved to Hetzner Cloud to reduce the cost of standby infrastructure.
It was down for 4 hours in a row several times over these 18 years, but otherwise the uptime was close to six sigma (under 3 hours per year).
It's surprising how rock solid hosting providers are. Can't imagine hosting anything substantial on AWS after all these years.
Also they tend to underestimate the amount of work required when using managed solutions. For example, you'll certainly want to do secondary backups and test restores even for managed options.
If your database fits one server, you should consider it.
[0] https://gist.github.com/strzibny/4f38345317a4d0866a35ede5aba...
Additionally, running a managed DB involves a lot of upkeep as well, it’s just a different set of tasks.
Someone else managing that DB doesn't prevent me (or someone malicious) from destroying the data, various infra screwups still are a thing (though perhaps less common). The only difference is if doing the same things through the managed DB admin panel takes meaningfully less effort than ssh on a self-hosted box; and it seems a wash - in the managed DB is easier to use complicated features than when self-hosted, but they tend to be more complex for simple things.
Oracle, on the other hand, requires a lot of babysitting and you'll probably be better off using their cloud (and still it will be more of a burden than Postgresql).
You have control over the version.
You have control over features.
You have control over performance.
It’s tons cheaper for greater performance - especially when you go over a few hundred gigs.
Yes, the hosted ones have built in replication - but my data is far too valuable to put in the hands of a third party. If they lost it - they could only shrug and say sorry and that’s it. The TOS indemnifies them.
I think it’s kind of honestly lazy — to not take the management of your data in your hands if you run a database of any significant size.
That being said, we do replicate and backup 9 ways to Sunday.
> my data is far too valuable to put in the hands of a third party
Confused, you self-host "in the cloud" without a third party involved? What does that mean? Are you not counting the party hosting your machine?
Whereas you can get a machine on AWS and setup the database yourself.
A startup, where you'd rather have your engineers working on adding features to your product instead of "managing and operating" a database? Have a lot of money to throw at this problem, being aware that you are paying for convenience? then using RDS and such would probably be a better choice.
An established company/product with a team of people working at a non-crazy pace, and catering to steady enterprise customers? Would like to build a sustainable long-time infrastructure setup that is optimised for efficiency and cost? running your own Database chosen well for your needs, backed up and tested rigorously, and managed by people who cares for the company's infrastructure would be a better idea.
It is weird when large companies with a dedicated platform team choose to run so many databases for their teams/services on a PaaS. That clearly is an optimisation opportunity.
Applying migrations? initial setup and configuration?
I haven't used a cloud offering before, but OP was asking about managing and operating a database. And that list is what we do at work or at home.
If you think there is nothing more to maintaining a database than initial setup and configuration, you are in for a rude awakening. Shit happens.
I felt like generally you have nothing to do, except sometimes you gotta do something like respond to disk quota alert and stuff
That's the typical sales pitch for cloud offering, but I really fails to see how it applies to DB. Which part of “managing and operating” your database do you save when using such a service? And how big of a load did it put on your engineers in the first place?
1. Keeping your database software up-to-date. Applying security patches without a huge downtime.
2. Managing the installation so that it is operating correctly - it's Systemd init files are okay and that it will come back to life should the server restart, logs are being stored correctly at an appropriate disk partition and that they don't fill up your data directory,
3. OS configuration aligns with your and your database software's needs. Which sysenvs did you set? were they changed as part of your OS updates?
All of the above are worth it in many cases. Spending a few days optimising the installation for your workload can make a huge difference and also empower you to use custom extensions like Timescale which is not so easy to do in some managed database services. Also, cost savings because you take that effort on yourself.
this perception of having to be special or only focus on sysadmin stuff for self hosting is incredible. cloud providers have managed diamond cartel levels of consumer propaganda in the tech sphere, its pretty amazing once you notice it.
At the stage where you need to spend a few hours a month dealing with it, you can afford to hire a DBA.
Would a hosted service be a better Idea, in your opinion? (That machine also has a LAMP stack for some of my web stuff).
In general, this is implemented as two main flows:
- Data collected from "the edge": Web servers that serve ads, or receive form fill-outs for lead generation, that do nothing but record this information in a logfile
- Configuration and Reporting pushed from "the hub": A central processing node (usually in a hot/warm configuration, but I've been known to use things like raft here) that receives information from flow 1, makes a decision and writes the result to flow 2.
Because the "data" I want to materialise to clients always either fits into ram, or is in a coherent stream ready to deliver to a client exactly, my applications are very fast: I can always just add another node (in the right geographic place), however I also noticed another really interesting benefit: It is much easier to develop for than using an external database.
I have a general purpose tool I wrote decades ago that moves logfiles around reliably (and at low latency), merges them together, implements failover, etc, so I really don't have very much to write to make a new application. Sometimes I experiment with the encoding of the payload, but sometimes not.
For example, I did a "sandwich store" app that would list orders in JSON. These were written to a log file via a PHP script. The replication tool would copy them to the "home base" (the store itself). When the kitchen would print an order, it would record a log line saying the order was started, and this would be replicated via a different logfile to the two web servers. The user might submit an order and not see it right away, but I hid this using a cookie so you might only notice if you were using two web browsers logged in with the same user. The receiver on the edge would write out files for each order containing the most recently loaded status, so collecting the status just involved asking the servers for the contents of this file. When the order was scheduled to be delivered, another log line would update those files. And so on. Nothing really resembling a "database" here at all -- just files and memory.
Most recently, my ad server (erlang) has a ets table (cached in dets to speed recovery) that contains all of the publisher+targeting details as a key, and the list of matching advertisers. This table gets updated when home-base records a configuration change (say, because an operator adds a new site or advertiser). Configuration changes look something like this:
They're actually stored as binary terms in a disk_log. A process reads the disk_log and materialises the in-memory configuration for the web services and rebuilds the ets table. Erlang has a lot of tools that (perhaps unobviously) make this very easy.And so on.
And i am not the only one hosting it by my own, many people are using self-hosting PaaS like dokku, flynn or caprover. And all these solutions have a common problem, they all need to reinvent the database as a service layer. What is currently really missing is a good open-source PostgreSQL as a service appliance, something "simple" like Heroku. There have been attempts like Elephant Shed, but they all try to do too much and therefore fail in adoption as they never reach stability. Or people are forced to use complex solutions like patroni which is doing many things, but if something fails you have no clue what to do.
So what is really missing?
1. A simple old-school PostgreSQL vm image 2. Built on a copy-on-write file system to clone production environments like https://postgres.ai/ and Heroku for development reasons which will not really need any storage space. 3. A built-in backup solution like pgBackRest storing the files encrypted in some cloud storage (and restore options!) 4. It does not need a complex ui or inspection/monitoring software, there are many solutions you can run on a different machine. 5. Replication, Auto-Failover etc. are hard just make it a single server which you scale vertically, if you need horizontal scaling you have very specific needs, there can't be a one size fits all solution, so don't even try it. And a real bare-metal server with nvme disks has a lot of power it's insane how fast it is compared to cloud hosted databases.
Depends on who you go with. Google's managed postgres thing lets you install supported extensions.
https://cloud.google.com/sql/docs/postgres/extensions
for example, timescaledb isn't supported anywhere, so the options are managing it yourself or using their own hosted Postgres version.
IMHO, managed solution ins't that much reliable than running your own with the right planning. Failover strategy of AWS RDS is just absurb. It's literally just promote other instance, switch DNS over. And I do see issue where the new master are behind a few transaction compare with the replica...
Managed database is just to move the responsibility when the database is down to somebody else.
Self host gives you a lot of flexibility to mix and experiment with new technologies and tooling.
I have seen tutorials where folks go over the most essential settings, but I can't seem to find the ones I have used now, so me just doing a search for 'postgres tuning' is no better than you doing the search yourself.
Maybe someone else can chime in with a good guide?
On larger projects (typically once k8s gets involved) I'm running on a cloud provider anyway and I might as well use a hosted version like RDS for the main database.
It comes down to the importance/budget of the project. I'm not a Postgres expert by any means but I'm confident enough that for simple use cases I can manage selfhosted. And if I need more, hosted versions are available at a cost.
However, any hosted DB product I use has to be open source and in theory easily replaceable with a selfhosted version. After the Parse.com fiasco I'm averse to closed/proprietary components in my infrastructure.
And then security patches are rolled out accordingly to be updated automatically by the OS service. Not sure how good with docker container is on this part.
If you host something serious and you don't need special builds of databases it is probably better not to use docker. But it's a bit more effort.
I don't know how you wan to enforce that all developers and all production machines use the exact same environment. Maybe your production still runs RHEL 7, so all the developers need to run their desktop on CentOS 7?
Or take open source projects. Isn't it easier to put in the readme: "To start developing install docker and run 'docker-compose up'" vs. "Please install Ubuntu 18.04 into a VM, those 100 packages, but make sure this 50 packages are not installed."
In my experience virtualized desktop environments are very painful to work on, even with fast connections.
Docker (or containerization in general) is very useful for development. Much more useful than for deployment/production.
If that is not available, then it depends on how complex the dependency graph is. If it is simple and I can just `apt install` all dependencies then I'll wrap the install in an Ansible Playbook and use systemd. If not I create my own docker-compose.yml for deployment.
edit: when multiplexing multiple projects on the same host (happens with very small projects) I always use docker.
Hosting is not only about speed and price. We use atlas (=hosted mongodb), because we get that nice dashboard with all those statistics and performance tuning hints. Also, if we screw something up, mongo engineers are available immediately to help us. That was nice when we broke production because of index creation on the foreground. (Which pymongo defaults to, even though mongodb itself defaultst to backgound creation). We consulted them for tuning a frequently running “slow” query and that helped.
In short: hosted solution for support and less maintenance. Backups are arranged. We can choose which version to run on and upgrades are also arranged.
Question: is there a comparable service for postgresql like mongodb atlas?
> Question: is there a comparable service for postgresql like mongodb atlas?
There is no company behind Postgres. So, the big public cloud providers are providing their hosted solution.
So many options. Amazon RDS and Google Cloud SQL both offer PostgreSQL. Heroku PostgreSQL has been a solid option for years. Crunchy Data's Crunchy Bridge is a recent offering with serious talent behind it.
On the plus side we can do ad hoc tests and experiments by creating a new Aurora cluster with a recent snapshot and try things out.
We're doing this for multiple reasons: a) As our DB grew the service became very expensive, one of the biggest items in our AWS invoice; b) Keeping the PG servers up to date is a pain, we simply don't have time for this; c) We wanted to be able to migrate to other clouds and even be able to offer a self-hosted version of our platform.
Where do the SQLite files live? Are you using some kind of NFS or EFS or similar for them? Sharding them across many machines?
How are you handling backups and high availability? Are you using Litestream?
How many SQLite files do your application servers have open at any one time?
Also, how do you handle migrations, both schema as well as data?
SQLite is crazy fast in WAL mode, but it's because they use a memory-mapped file to coordinate consistency across multiple processes/threads. You can't mmap a remote file.
(Source: hacking in a DB replica workaround to PhotoStructure so people can store their library on arbitrary filesystems... BTW, I'd love to be wrong here: if someone's figured out how to do this, please share!)
I was wondering if maybe the cute hack that was posted recentlyish (https://news.ycombinator.com/item?id=27016630) to enable SQLite3 to access large remote databases over HTTP might be relevant, but that approach requires a small server to be sitting at the remote, and only implements read-only access in any case.
Now I'm wondering what an NFS/SMB translation of the above idea (which uses SQLite3's VFS hooks) might look like: ramp internal buffering to the max, use separate transient files (with eg date-based unique names) for each in-flight request, etc. Append-only journal files also come to mind...
It's crazy how flaky network filesystems are at the lower levels. Like, I use NFS at home every day, without Kerberos (which lets me enable encryption, which IIUC provides AEAD), all while knowing my data integrity entirely depends on the IP checksum computed by my 15 year old ISP router. :D
(I already know I need to upgrade. Shhh.)
I'm currently wrestling with a 35TB Aurora DB and considering options for future growth. The system is multi-tenant and one of the options on the table is breaking out the tenants and hosting them on either their own system or alongside smaller subset of tenants.
Thanks for the suggestion of TiDB, haven't previously considered this but it looks interesting, will take a closer look.
(And oh wow I just realized the billing must be amazing...)
Can't say too much, but it's Financial Services related.
I guess this cues the stereotypical "have you looked at kdb+" question, then, which you've probably already fielded :)
(https://kx.com)
IIUC, kdb+ does in fact have a real DB hiding in it, and it's not all just "weird programming language" - but it does seem to require/prefer a reasonable amount of buy-in to the way it does things to be valuable. I guess the lateral question is whether its performance obsession extends to smaller on-disk footprint, and whether the savings would be worth throwing out all the the existing Aurora integration work. (Answer: very probably not.)
Two disadvantages that stand out for me are:
1. You must be able to scale the database up to your biggest customer and down to the your smallest this can be increasingly difficult to provision.
2. You are stuck with customers being isolated and makes it much harder to develop features that cut across a group of customers.
If you have a dynamic set of databases or even tables, be ready to invest into tooling to recreate features you take for granted in a normal database.
IIRC, Slack had a lot of scaling issues when they won a deal with IBM. Jumping from medium-size customers to such a behemoth will obviously be a challenge, but a lot of the difficulties they faced was non-technical and more business-related (“What do you mean, you want our product but not our monthly plan charged per seat?”). Anyway, that's a nice problem to have for a start-up ;).
4. By splitting, you limit the exposure in case of SQL injection.
5. By splitting, you can have good performance for the customer who has 1M records, and good performance for the 100K customers with 10 records.
I have no affiliation with the product, but I have been comparing alternatives and it seems like that's where it shines.
I solved the problem by handling all the DB operations in a separate thread that handled write and read queues. It certainly was not as easy as using mysql/postgresql where server does everything for you. Nowadays there are probably plenty of libraries that handle that for you.
I've never used AWS RDS (or similar), but I had been thinking that part of the advantage of RDS was you didn't need to "keep the PG server up to date".
But apparently I was wrong! What does this look like on RDS?
Minor versions you can tell it a day and rough timeslot (few hours) for it to apply them. Major version upgrades always require you to start them, and the docs lay out prep work to do before hand (check for incompatibilities, setup any custom config before hand, take a backup/snapshot, do dry run)
One of the things I love with RDS is that last bit, doing a dry run with a clone of the database is fairly easy (can't say what it's like at the TB level of data, probably not as easy/quick) to test.
I remember a discussion here a few years ago about it, and there were so many voices chiming up to say 'we did this, terrible idea'.
You can use a cheap virtual server + attached storage. In my mind, it's not as difficult as people let you believe (until you hit a scaling issue and want horizontal scaling for example).
I also teach how to do it in my upcoming book[0] where I even have a scripted demo to create your first cluster including SSL, SELinux, attached storage,...[1].
For work stuff, I would just use managed offering in the cloud the company already has. So far, that was AWS and Azure.
[0] https://gumroad.com/l/deploymentfromscratch [1] https://gist.github.com/strzibny/4f38345317a4d0866a35ede5aba...