I wish this article would have went more in-depth on how they're setting up backups. The great thing about sequel light is lightstream makes backup and restore something you don't really have to think about
Go ahead, self-host Postgres
51–60 of 407 posts
Re: Go ahead, self-host Postgres
#52And then there is the urge to Postgres everything. I was disappointed alloy doesn't support timescaledb as a metrics endpoint. Considering switching to telegraf just because I can store the metrics on Postgres.
I've always just Postgressed everything. I used MySQL a bit in the PHP3 days, but eventually moved onto Postgres. SQLite when prototyping, Postgres for production. If you need to power a lawnmower and all you have is a 500bhp Scania V8, you may as well just do it.
I have now switched to pglite for prototyping, because it lets me use all the postgres features.
Re: Go ahead, self-host Postgres
#53For a fascinating counterpoint (gist: cloud hosted Postgres on RDS aurora is not anything like the system you would host yourself, and other cloud deployments of databases should also not be done like our field is used to doing it when self-hosting) see this other front page article and discussion: https://news.ycombinator.com/item?id=46334990
Aurora is a closed-source fork of PostgreSQL. So it is indeed not possible to self-host it.
However a self-hosted PostgreSQL on a bare metal server with NVMe SSDs will much faster than what RDS is capable of. Especially at the same price points.
Re: Go ahead, self-host Postgres
#54The author brings up the point, but I have always found surprising how much more expensive managed databases are than a comparable VPS. I would expect a little bit more as a cost of the convenience, but in my experience it's generally multiple times the expense. It's wild. This has kept me away from managed databases in all but my largest projects.
Once they convince you that you can’t do it yourself, you end up relying on them, but didn’t develop the skills you would need to migrate to another provider when they start raising prices. And they keep raising prices because by then you have no choice.
There is plenty of provider markup, to be sure. But it is also very much not a given that the hosted version of a database is running software/configs that are equivalent to what you could do yourself. Many hosted databases are extremely different behind the scenes when it comes to durability, monitoring, failover, storage provisioning, compute provisioning, and more. Just because it acts like a connection hanging off a postmaster service running on a server doesn’t mean that’s what your “psql” is connected to on RDS Aurora (or many of the other cloud-Postgres offerings).
Re: Go ahead, self-host Postgres
#55Huh? Maybe I missed something, but...why should self-hosting a database server be hard or scary? Sure, you are then responsible for security backups, etc...but that's not really different in the cloud - if anything, the cloud makes it more complicated.
I'd say a managed dB, at minimum, should be handling upgrades and backups for you. If it doesn't, thats not a managed db, thats a self-service db. You're paying a premium to do the work yourself.
Re: Go ahead, self-host Postgres
#56Earlier quoted context omitted.
I've always just Postgressed everything. I used MySQL a bit in the PHP3 days, but eventually moved onto Postgres. SQLite when prototyping, Postgres for production. If you need to power a lawnmower and all you have is a 500bhp Scania V8, you may as well just do it.
I have now switched to pglite for prototyping, because it lets me use all the postgres features.
Oho, what is this pglite that I have never heard of? I already like the sound of it.
Re: Go ahead, self-host Postgres
#57So, yeah, I guess there's much confusion about what a 'managed database' actually is ? Because for me, the table stakes are: -Backups: the provider will push a full generic disaster-recovery backup of my database to an off-provider location at least daily, without the need for a maintenance window -Optimization: index maintenance and storage optimization are performed automatically and transparently -Multi-datacenter…
Yugabyte open source covers a lot of this
Re: Go ahead, self-host Postgres
#58Earlier quoted context omitted.
I've always just Postgressed everything. I used MySQL a bit in the PHP3 days, but eventually moved onto Postgres. SQLite when prototyping, Postgres for production. If you need to power a lawnmower and all you have is a 500bhp Scania V8, you may as well just do it.
Have you given thought to why you prototype with SQLite? I have switched to using postgres even for prototyping once I prepared some shell scripts for various setup. With hibernate (java) or knex (Javascript/NodeJS) and with unit tests (Test Driven Development approach) for code, I feel I have reduced the friction of using postgres from the beginning.
Because when I get tired of reconstructing the contents of the database between my various dev machines (at home, at work, on a remote server, on my laptop) I can just scp the sqlite db across.
Because it's "low effort" to just fire it into sqlite and if I have to do ridiculous things to the schema as I footer around working out exactly what I want the database to do.
I don't want to use nodejs if I can possibly avoid it and you literally could not pay me to even look at Java, there isn't enough money in the world.
Re: Go ahead, self-host Postgres
#59I still don't get how folks can hype Postgres with every second post on HN, yet there is no simple batteries-included way to run a HA Postgres cluster with automatic failover like you can do with MongoDB. I'm genuinely curious how people deal with this in production when they're self-hosting.
It's largely cultural. In the SQL world, people are used to accepting the absence of real HA (resilience to failure, where transactions continue without interruption) and instead rely on fast DR (stop the service, recover, check for data loss, start the service). In practice, this means that all connections are rolled back, clients must reconnect to a replica known to be in synchronous commit, and everything restarts with a cold cache.
Yet they still call it HA because there's nothing else. Even a planned shutdown of the primary to patch the OS results in downtime, as all connections are terminated. The situation is even worse for major database upgrades: stop the application, upgrade the database, deploy a new release of the app because some features are not compatible between versions, test, re-analyze the tables, reopen the database, and only then can users resume work.
Everything in SQL/RDBMS was thought for a single-node instance, not including replicas. It's not HA because there can be only one read-write instance at a time. They even claim to be more ACID than MongoDB, but the ACID properties are guaranteed only on a single node.
One exception is Oracle RAC, but PostgreSQL has nothing like that. Some forks, like YugabyteDB, provide real HA with most PostgreSQL features.
About the hype: many applications that run on PostgreSQL accept hours of downtime, planned or unplanned. Those who run larger, more critical applications on PostgreSQL are big companies with many expert DBAs who can handle the complexity of database automation. And use logical replication for upgrades. But no solution offers both low operational complexity and high availability that can be comparable to MongoDB
Re: Go ahead, self-host Postgres
#60Take a look at https://github.com/vitabaks/autobase In case you want to self host but also have something that takes care of all that extra work for you
Thank you, this looks awesome.