Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Our Journey to PostgreSQL 12

tech.coffeemeetsbagel.com

Post reply on HN
Print view View original

71–80 of 116 posts

Re: Our Journey to PostgreSQL 12

#71

Post by cconstantine »

tommyzli wrote: post #13
ants_a wrote: post #10

pg_upgrade would have worked fine given your requirements. Just using normal streaming replication to move database over to new systems and then performing an in place pg_upgrade there would be doable with most likely a couple of minutes of downtime and a much quicker and more robust process.

How would that have worked with multiple replicas cascading from the new primary? Streaming replication doesn't work across versions, so would we have had to build out a tree of new instances, then pg_upgrade them all at the same time?

I've done a few postgres upgrades, the first using pg_upgrade, and the last doing effectively what you did (it was even 9.x -> 12).

My experience was that it needs to rewrite all the data for some tables/indexes under some circumstances, and the db won't be available while that happens. So, unless your db can be down for the time it takes to rewrite all that data it isn't really an option. After having done the upgrade through streaming logical replication I'm not sure I would try pg_upgrade again.

I did the pg_upgrade style update a long time ago, so most details are fuzzy, but I remember setting up a string of replicas something like:

primary -> [read_replica, backups_replica]

read_replica -> [upgrade_replica]

upgrade_replica -> [read_replica_upgraded, backups_replica_upgraded]

This allowed us to do multiple practice rounds without putting any unnecessary load on the primary. I think we needed to re-initiate replication off 'upgrade_replica' after the upgrade, but we did the live update during low-load so the extra read load wasn't an issue.

Re: Our Journey to PostgreSQL 12

#72

Post by phonon »

throwdbaaway wrote: post #67
tommyzli wrote: post #40

Earlier quoted context omitted.

We are living life on the edge to an extent, but we have 5 hot standbys across AZs and regular backups + WAL archives to S3. May not be as durable as EBS, but it's enough for me to sleep soundly at night. And with a highly concurrent WAL-G download, it takes like an hour to catch up a new replica from scratch.

Fine, with enough replicas, you can sleep well at night. But how about the 3 years uptime without reboot? Can you really enjoy your morning coffee without thinking about it? :) Netflix went full ephemeral storage for their Cassandra clusters since the beginning, at the time when they were just spinning disks. Years later, they still insist on doing this, and had to come up with creative solution to fix the uptime iss…

Why can't you reboot? NVMe storage is local, not ephemeral.

Re: Our Journey to PostgreSQL 12

#73

Post by throwdbaaway »

phonon wrote: post #72
throwdbaaway wrote: post #67

Earlier quoted context omitted.

Fine, with enough replicas, you can sleep well at night. But how about the 3 years uptime without reboot? Can you really enjoy your morning coffee without thinking about it? :) Netflix went full ephemeral storage for their Cassandra clusters since the beginning, at the time when they were just spinning disks. Years later, they still insist on doing this, and had to come up with creative solution to fix the uptime iss…

Why can't you reboot? NVMe storage is local, not ephemeral.

Yes, you can reboot, but you have to update in-place, instead of rolling out a new OS image. Or you adopt the Netflix approach. There are also some additional restrictions, e.g. you can't change the machine type.

Anyway, from the other comment here, I think tommyzli might not have realized that a reboot is still possible, which would partially explain the 3 years uptime.

Re: Our Journey to PostgreSQL 12

#74

Post by ramraj07 »

philipwhiuk2 wrote: post #32

The fact that the answer isn't "move to RDS where Amazon solves the problem for us, which isn't our core business as a relationships app" seems to me to be a massive failing of the RDS offering and cloud services in general.

RDS can be.. expensive? Like by a lot?

Re: Our Journey to PostgreSQL 12

#75

Post by throwdbaaway »

tommyzli wrote: post #6
temp667 wrote: post #3

Very nice. Did they migrate into Amazon RDS while doing this? For smaller projects I've stopped doing the self managed postgresql thing. The pricing is higher (75%?) for RDS for some use cases but can be worth it. Going to try RDS Proxy next.

Thanks! We stuck with plain EC2. RDS has a limit of 80,000 provisioned IOPS and our read replicas on Postgres 9.6 would regularly hit near double that during peak

That's an impressive amount of IOPS. I have always been a EBS / pd-ssd guy, and mostly rely on memory to reduce the IOPS requirement. But as cloud providers typically charge a ridiculous amount of money for memory, a setup like yours with instance storage / local-ssd is an intriguing option.

Re: Our Journey to PostgreSQL 12

#76

Post by sk5t »

tommyzli wrote: post #70
rubiquity wrote: post #55

Earlier quoted context omitted.

Disclaimer: I work at AWS, not on EC2. Locally attached disks are not ephemeral to instance reboots/power failures. However, the disks are wiped after instance terminations. On the official EC2 product pages this is called "instance storage" not "ephemeral storage."

TIL, this is really good to know! Do you know offhand if this is a new feature, or have I just always been wrong

It's been that way for a long time - years at least. You can reboot but not 'stop' and of course not 'terminate.'

Re: Our Journey to PostgreSQL 12

#77

Post by sk5t »

tommyzli wrote: post #6
temp667 wrote: post #3

Very nice. Did they migrate into Amazon RDS while doing this? For smaller projects I've stopped doing the self managed postgresql thing. The pricing is higher (75%?) for RDS for some use cases but can be worth it. Going to try RDS Proxy next.

Thanks! We stuck with plain EC2. RDS has a limit of 80,000 provisioned IOPS and our read replicas on Postgres 9.6 would regularly hit near double that during peak

Did you consider lowering those IOPS with application-level and/or distributed in-memory cache and/or pub-sub notifications to let your app nodes not pester the database so much? Reasonably performant hand-written SQL (no ORM!), review of query plans, maybe shift the hot path into functions/procs?

Re: Our Journey to PostgreSQL 12

#78

Post by matharmin »

throwdbaaway wrote: post #67
tommyzli wrote: post #40

Earlier quoted context omitted.

We are living life on the edge to an extent, but we have 5 hot standbys across AZs and regular backups + WAL archives to S3. May not be as durable as EBS, but it's enough for me to sleep soundly at night. And with a highly concurrent WAL-G download, it takes like an hour to catch up a new replica from scratch.

Fine, with enough replicas, you can sleep well at night. But how about the 3 years uptime without reboot? Can you really enjoy your morning coffee without thinking about it? :) Netflix went full ephemeral storage for their Cassandra clusters since the beginning, at the time when they were just spinning disks. Years later, they still insist on doing this, and had to come up with creative solution to fix the uptime iss…

From the parent comment:

> it takes like an hour to catch up a new replica from scratch

That means it should be pretty easy to replace an instance - just create a new replica from scratch, then fail over (if you're replacing the current master/primary instance), and remove the old one.

Re: Our Journey to PostgreSQL 12

#79

Post by sk5t »

0xbadcafebee wrote: post #8

Am I the only one who thinks it's bizarre that a structured query language defines so much of how we choose to architect and operate our systems? Think about it for a sec: SQL is literally just a language to query and manipulate data. There's no reason that schema changes and data changes have to happen only through the one language, and only through one interface on one piece of software. For whatever reason, this h…

Are you kidding, LDAP referrals as a model of how to do it? I mean, I did a lot of LDAP work back in the day, and that's not a feature that saw a lot of action outside academia. Just write your own thing on top of HTTP, that's got referrals too!

There are practically many ways of talking to database systems, if it isn't too troubling that some SQL is often happening somewhere. Like, there's Hasura, postgrest, etc.; or Mongo has a variety of drivers that support different inputs.

One might consider the most unix'y database to be Berkeley DB/Sleepycat, but that is probably not what you wanted. ;)

Re: Our Journey to PostgreSQL 12

#80

Post by lopatin »

throwdbaaway wrote: post #63
derekperkins wrote: post #48

Earlier quoted context omitted.

5TB is not that large and it's not that difficult to get good performance. We operated a 50TB single instance of MySQL for 5 years with a tiny team before migrating to Vitess, and it was basically zero maintenance. We did partition our largest table, which just requires a little extra SQL, but is otherwise transparent.

You guys are talking past each other, as your workloads appear to be different. With a traditional RDBMS, size usually wouldn't be a bottleneck, as long as you can add enough disks to a single server. Write operations per second, that's the metric I would care about. A 50TB instance with low amount of write operations can be zero maintenance, while a 500GB instance with high amount of write operations can be a real p…

Thank you for validating my troubles with a write-heavy ~500GB db in the midst of a TB-measuring contest :)
Post reply on HN