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

Updating a 50 terabyte PostgreSQL database (2018)

adyen.com

Post reply on HN
Print view View original

21–30 of 67 posts

Re: Updating a 50 terabyte PostgreSQL database (2018)

#21

Post by rattray »

rattray wrote: post #17
simonw wrote: post #11

I didn't understand how they avoid downtime during the upgrade - they start with "Stop traffic to the database cluster" and then perform a bunch of steps that each take 5-10 minutes or longer before starting traffic again - so do they just accept the downtime and schedule it in advance, or did I miss a detail?

Yeah, they architect their application to accept DB downtime – but I'm sure their services are still degraded to some degree or another during this, and they aren't clear how much total DB downtime they need for this (and how that time scales across various axes). Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" which…

I did a little googling, and still find the answers are "logical replication, which is slow for large DB's" or "pg_upgrade, which requires nontrivial downtime".

Gitlab wrote a much more in-depth rundown in 2020 of a pg_upgrade, which in their case took 2h of downtime and 4h total time: https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ (submitted to HN here: https://news.ycombinator.com/item?id=29926316). They included a video of the whole thing which is pretty incredible.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#22

Post by mritun »

GordonS wrote: post #14
paozac wrote: post #13

50TB is not so big these days. I read that in 2008 (!) Yahoo had a 2+ PB PG database. What is the largest you know of, 14 years later?

50TB is big. Bigger is possible I'm sure, but I'd guess 99.something% of all PG databases are less than 50TB. If someone here commented they had a 2PB database, I guarantee someone else here would be like "pfft, that's not big"...

The OP message could have better said that 50TB databases are common these days when single metal or 24xl I3en or I4* instance on AWS can hold 60T raw.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#24

Post by aantix »

GordonS wrote: post #14
paozac wrote: post #13

50TB is not so big these days. I read that in 2008 (!) Yahoo had a 2+ PB PG database. What is the largest you know of, 14 years later?

50TB is big. Bigger is possible I'm sure, but I'd guess 99.something% of all PG databases are less than 50TB. If someone here commented they had a 2PB database, I guarantee someone else here would be like "pfft, that's not big"...

With 50TB, and if you were doing a full text search, wouldn't the entirety of the index have to be held in memory?

Re: Updating a 50 terabyte PostgreSQL database (2018)

#26

Post by ok_dad »

paozac wrote: post #13

50TB is not so big these days. I read that in 2008 (!) Yahoo had a 2+ PB PG database. What is the largest you know of, 14 years later?

Around ~2005 I took a tour of the [a well known government organization] and they were bragging about several-PB-sized databases at the time. Interestingly, there was a TON of server racks there in a bomb-proof building with tons of security, and they were all IBM servers (a supercomputer maybe?), if I remember correctly. Also, there was one small server rack that was painted differently from the rest (it looked like something made in-house), and we asked what it was, and the tour guide (a PhD computer scientist) said that technically it doesn't exist and he couldn't talk about it even though it was super cool. Now that I know what they were doing around that time (and probably still today) I am kinda scared at the implications of that tour guide's statement and what that one tiny rack was for. I'm glad I never went to work in their organization, since that tour was meant to recruit some of us a few years down the road.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#27

Post by mulmen »

leifg wrote: post #19

One of the things I always wonder with giant relational database is. How much of the "typical relational stuff" are they actually using? Do they have constraints on rows? Are they using views or do they just denormalize and duplicate? Do they use joins at all? Are they even doing more than 1 thing in a transaction?

My only experience with databases of that size is for data analysis so yeah, constraints are relaxed. But even at that point ideas like normalization are critical to extracting performance out of large datasets.

Normalization is a performance optimization. Denormalization is a development shortcut. Neither is right or wrong but I would be surprised if a 50TB OLTP database wasn’t already highly normalized.

If it isn’t then my next guess is that it could be made smaller or more performant if it was.

We used to be proud of server uptime, back when we gave them names. Today if you have a server up for 900 days you’re going to be accused of malpractice.

Similar for data. We used to be proud of being able to keep “big” data online, but I’m no longer impressed. You’re just spending money. Did you actually solve the business case as efficiently as possible given available resources? Do you need 50TB in the first place?

Re: Updating a 50 terabyte PostgreSQL database (2018)

#28

Post by gbrown_ »

simonw wrote: post #11

I didn't understand how they avoid downtime during the upgrade - they start with "Stop traffic to the database cluster" and then perform a bunch of steps that each take 5-10 minutes or longer before starting traffic again - so do they just accept the downtime and schedule it in advance, or did I miss a detail?

Whilst others have mentioned their application is architected to cope with this I wanted to echo the sentiment. This post feels very much like a “draw the rest of the owl” meme.

Re: Updating a 50 terabyte PostgreSQL database (2018)

#29

Post by znpy »

rattray wrote: post #17
simonw wrote: post #11

I didn't understand how they avoid downtime during the upgrade - they start with "Stop traffic to the database cluster" and then perform a bunch of steps that each take 5-10 minutes or longer before starting traffic again - so do they just accept the downtime and schedule it in advance, or did I miss a detail?

Yeah, they architect their application to accept DB downtime – but I'm sure their services are still degraded to some degree or another during this, and they aren't clear how much total DB downtime they need for this (and how that time scales across various axes). Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work" which…

> Overall my takeaway is basically "if you want to upgrade a large Postgres db, you'll need like an hour of planned downtime and a lot of careful work"

HA is one of those things where MySQL wins hands down, sadly. Sadly in the sense that PostgreSQL HA still looks like a couple of hacks held together with duct tape, at least when compared to MySQL solutions.

The Percona MySQL distribution has multi-master HA (Percona Xtradb Cluster) which is great for HA, and vanilla MySQL 8.x has group replication which iirc also is multi-master. MariaDB instead has multi-master based on Galera Cluster (although i'm not very knowledgeable about MariaDB).

In PostgreSQL world there are many HA solutions, none of which is both multi-master and open source (sadly).

Re: Updating a 50 terabyte PostgreSQL database (2018)

#30

Post by Gigachad »

paozac wrote: post #13

50TB is not so big these days. I read that in 2008 (!) Yahoo had a 2+ PB PG database. What is the largest you know of, 14 years later?

How are people dealing with databases this large? At work we have a mysql db with a table that has 130M records in it and a count(*) on the table takes 100 seconds. Anything but a simple look up by id is almost unworkable. I assumed this was normal because its too big. But am I missing something here? Are SQL databases capable of actually working fast at 50TB?
Post reply on HN