19 ms·

Updating a 50 terabyte PostgreSQL database (2018)

168 points by Maksadbek 5y ago

throwaway29303 5y ago
(2018)
- wiz21c 5y ago
  was wondering why they were using old PG's :-)
abagheri43 5y ago
abagheri43 5y ago
lelandbatey 5y ago
This article mentions that a key piece of software they use in all this is Slony[0], software which allows replicating one primary/master to many secondaries/slaves, even when those replicas have different versions. That's pretty cool, but I'd like to instead draw your attention (dear reader) to the phenomenal writing happening in Slonys description of itself. It describes, in plain but technical language what Slony is, then immediately jumps into addressing "why should you use this?" and does so very directly. It reiterates the problem it solves, mentions other potential solutions by name to contextualize the current solution space, then explains in a nice bulleted list exactly the situations and niches that Slony is tailored for. Oh my heavens, I wish every piece of technology described itself this way! [0] - Slony -- https://www.slony.info/ https://www.slony.info/
- xblau 5y ago
  Archived link, since it appears to be down at the moment: https://web.archive.org/web/20211031124351/https://www.slony.info/ https://web.archive.org/web/20211031124351/https://www.slony...
LoungeFlyZ 5y ago
Irony. I think the HN post took down the Slony website.
- mulmen 5y ago
  http://howfuckedismydatabase.com/postgres/slony.php http://howfuckedismydatabase.com/postgres/slony.php
  - arlattimore 5y ago
    I've not seen that website before, going back to the home page and clicking through the database options literally had me laughing out loud - thank you :-)
jfbaro 5y ago
Thanks for sharing this. Really interesting. But a basic question, why not upgrading to PG 13 instead? I am curious about the reasons for staying on an older version of PostgreSQL.
- jfbaro 5y ago
  I see it now that it's a 2018 article. Makes sense!
- hawk_ 5y ago
  Interesting. Does PG13 let then do this without downtime?
  - CubsFan1060 5y ago
    Not from 9.4, but now there is a path using logical replication between versions: https://www.percona.com/blog/2019/04/04/replication-between-postgresql-versions-using-logical-replication/ https://www.percona.com/blog/2019/04/04/replication-between-...
simonw 5y ago
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?
- vonnieda 5y ago
  > One other detail to note is that we built our software architecture in such a way that we can stop traffic to our PostgreSQL databases, queue the transactions, and run a PostgreSQL update without affecting payments acceptance.
  - foobiekr 5y ago
    That’s downtime by any reasonable measure depending on the exact specifics. Is writing to a journal while the DB is down “downtime”? Site wise no but DB-wise yes.
- rattray 5y ago
  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… doesn't make me excited to upgrade big postgres db's in production.
  - rattray 5y ago
    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/ https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ (submitted to HN here: https://news.ycombinator.com/item?id=29926316 https://news.ycombinator.com/item?id=29926316). They included a video of the whole thing which is pretty incredible.
paozac 5y ago
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?
- GordonS 5y ago
  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"...
  - Groxx 5y ago
    it's more than big enough to cause big problems / risk days of downtime to change, yea. 50GB is not big. 50TB is at least touching big - you can do it on one physical machine if needed, but it's the sort of scale that benefits from bigger-system architecture. 50PB would be world-class big, hitting exciting new problems every time they do something.
  - mritun 5y ago
    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.
  - aantix 5y ago
    With 50TB, and if you were doing a full text search, wouldn't the entirety of the index have to be held in memory?
    - duskwuff 5y ago
      No. Full-text indexes exist.
      - Groxx 5y ago
        You can also do an incremental/streaming search. Lots of ways to avoid loading it all into memory at once, yeah.
leifg 5y ago
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?
- mulmen 5y ago
  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?
  - deleted 5y ago
    [deleted]
  - Kranar 5y ago
    Normalization is pretty independent of optimization strategy and generally that's not presented as one of its advantages. Normalizing data can improve write performance but will make reads slower, so if you have a write intensive database then you will see some performance gains.
    - mulmen 5y ago
      There are performance benefits to normalization at scale. Take a look at the 2005 schema changes at Wikipedia [1] for a real-world example. > Normalizing data can improve write performance but will make reads slower, so if you have a write intensive database then you will see some performance gains. In OLTP this might be true, based on access pattern. Normalization may improve both read and write performance. It could also make it worse if you take it too far. Domain Key normal form could kill you in unique indices alone. In OLAP different levels of normalization actually helps both read and write performance. Take a look at the star schema to see how that works. In general dimensions as they relate to fact tables are normalized but dimensions themselves are denormalized. Normalization is a tool like anything else. You can’t make absolute statements about it. The appropriate application will depend on your use case and may change over time. [1]: https://en.m.wikipedia.org/wiki/MediaWiki https://en.m.wikipedia.org/wiki/MediaWiki
deleted 5y ago
[deleted]
wruza 5y ago
While others saying 50T DB is relatively normal, personally I can't imagine myself dealing with that sort of data which is always online. If I were them, I'd ask myself, could we archive this data into split chunks? Is this archive even required to be online? What is the size of a subset that has actuality? And so on. Of course they have answers to that and they are generally "no", but my imagination just stops here. Some time ago when I studied bitcoin ledger structure, I was confused about how it does sum up all transactions to get the balance so quickly, like we in accounting usually do, with the help of some period caching, which is another point of failure and maintenance. Bitcoin is a large enough database to not do that easily. Few docs later I realized that it doesn't do this: mining -> A 1 A -> B 0.2 (sum(A) == 0.8) And instead it does this: mining -> A 1 A -> (B 0.2; A 0.8) (last(A) == 0.8) No sums required, all balances are checked by essentially "lookup the latest appearance in a db", where lookup also involves some merkle trees optimization, which I didn't understand enough to remember.
- samhw 5y ago
  > I was confused about how it does sum up all transactions to get the balance so quickly, like we in accounting usually do For what it's worth, in a system design context, this is called event sourcing (in more general terms it's just a fold, though the idea of event sourcing doesn't preclude caching). I worked at a bank for a few years and this was how we calculated balances too. > And instead it does this: I don't quite follow you here. It does something distinct from either a fold or caching the current total? (Do you mean that each transaction encodes the resultant balance?)
  - wruza 5y ago
    Not the entire balance, because a single wallet may have many "inputs", but that's just an implementation detail, as far as I understand it. https://en.bitcoin.it/wiki/Transaction https://en.bitcoin.it/wiki/Transaction If the input is worth 50 BTC but you only want to send 25 BTC, Bitcoin will create two outputs worth 25 BTC: one to the destination, and one back to you (known as "change", though you send it to yourself). Any input bitcoins not redeemed in an output is considered a transaction fee; whoever generates the block can claim it by inserting it into the coinbase transaction of that block. I.e. any "input" is spent completely by a transaction and this makes it irrelevant to future calculations. See also: https://bitcoin.stackexchange.com/questions/13069/how-does-the-network-know-my-current-balance https://bitcoin.stackexchange.com/questions/13069/how-does-t...
barrkel 5y ago
Upgrading, not updating.

168 points by Maksadbek 5y ago

throwaway29303 5y ago

(2018)

wiz21c 5y ago
was wondering why they were using old PG's :-)

abagheri43 5y ago

lelandbatey 5y ago

This article mentions that a key piece of software they use in all this is Slony[0], software which allows replicating one primary/master to many secondaries/slaves, even when those replicas have different versions. That's pretty cool, but I'd like to instead draw your attention (dear reader) to the phenomenal writing happening in Slonys description of itself. It describes, in plain but technical language what Slony is, then immediately jumps into addressing "why should you use this?" and does so very directly. It reiterates the problem it solves, mentions other potential solutions by name to contextualize the current solution space, then explains in a nice bulleted list exactly the situations and niches that Slony is tailored for. Oh my heavens, I wish every piece of technology described itself this way! [0] - Slony -- https://www.slony.info/ https://www.slony.info/

xblau 5y ago
Archived link, since it appears to be down at the moment: https://web.archive.org/web/20211031124351/https://www.slony.info/ https://web.archive.org/web/20211031124351/https://www.slony...

LoungeFlyZ 5y ago

Irony. I think the HN post took down the Slony website.

mulmen 5y ago
http://howfuckedismydatabase.com/postgres/slony.php http://howfuckedismydatabase.com/postgres/slony.php
- arlattimore 5y ago
  I've not seen that website before, going back to the home page and clicking through the database options literally had me laughing out loud - thank you :-)

jfbaro 5y ago

Thanks for sharing this. Really interesting. But a basic question, why not upgrading to PG 13 instead? I am curious about the reasons for staying on an older version of PostgreSQL.

jfbaro 5y ago
I see it now that it's a 2018 article. Makes sense!
hawk_ 5y ago
Interesting. Does PG13 let then do this without downtime?
- CubsFan1060 5y ago
  Not from 9.4, but now there is a path using logical replication between versions: https://www.percona.com/blog/2019/04/04/replication-between-postgresql-versions-using-logical-replication/ https://www.percona.com/blog/2019/04/04/replication-between-...

simonw 5y ago

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?

vonnieda 5y ago
> One other detail to note is that we built our software architecture in such a way that we can stop traffic to our PostgreSQL databases, queue the transactions, and run a PostgreSQL update without affecting payments acceptance.
- foobiekr 5y ago
  That’s downtime by any reasonable measure depending on the exact specifics. Is writing to a journal while the DB is down “downtime”? Site wise no but DB-wise yes.
rattray 5y ago
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… doesn't make me excited to upgrade big postgres db's in production.
- rattray 5y ago
  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/ https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ (submitted to HN here: https://news.ycombinator.com/item?id=29926316 https://news.ycombinator.com/item?id=29926316). They included a video of the whole thing which is pretty incredible.

paozac 5y ago

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?

GordonS 5y ago
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"...
- Groxx 5y ago
  it's more than big enough to cause big problems / risk days of downtime to change, yea. 50GB is not big. 50TB is at least touching big - you can do it on one physical machine if needed, but it's the sort of scale that benefits from bigger-system architecture. 50PB would be world-class big, hitting exciting new problems every time they do something.
- mritun 5y ago
  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.
- aantix 5y ago
  With 50TB, and if you were doing a full text search, wouldn't the entirety of the index have to be held in memory?
  - duskwuff 5y ago
    No. Full-text indexes exist.
    - Groxx 5y ago
      You can also do an incremental/streaming search. Lots of ways to avoid loading it all into memory at once, yeah.

leifg 5y ago

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?

mulmen 5y ago
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?
- deleted 5y ago
  [deleted]
- Kranar 5y ago
  Normalization is pretty independent of optimization strategy and generally that's not presented as one of its advantages. Normalizing data can improve write performance but will make reads slower, so if you have a write intensive database then you will see some performance gains.
  - mulmen 5y ago
    There are performance benefits to normalization at scale. Take a look at the 2005 schema changes at Wikipedia [1] for a real-world example. > Normalizing data can improve write performance but will make reads slower, so if you have a write intensive database then you will see some performance gains. In OLTP this might be true, based on access pattern. Normalization may improve both read and write performance. It could also make it worse if you take it too far. Domain Key normal form could kill you in unique indices alone. In OLAP different levels of normalization actually helps both read and write performance. Take a look at the star schema to see how that works. In general dimensions as they relate to fact tables are normalized but dimensions themselves are denormalized. Normalization is a tool like anything else. You can’t make absolute statements about it. The appropriate application will depend on your use case and may change over time. [1]: https://en.m.wikipedia.org/wiki/MediaWiki https://en.m.wikipedia.org/wiki/MediaWiki

deleted 5y ago

[deleted]

wruza 5y ago

While others saying 50T DB is relatively normal, personally I can't imagine myself dealing with that sort of data which is always online. If I were them, I'd ask myself, could we archive this data into split chunks? Is this archive even required to be online? What is the size of a subset that has actuality? And so on. Of course they have answers to that and they are generally "no", but my imagination just stops here. Some time ago when I studied bitcoin ledger structure, I was confused about how it does sum up all transactions to get the balance so quickly, like we in accounting usually do, with the help of some period caching, which is another point of failure and maintenance. Bitcoin is a large enough database to not do that easily. Few docs later I realized that it doesn't do this: mining -> A 1 A -> B 0.2 (sum(A) == 0.8) And instead it does this: mining -> A 1 A -> (B 0.2; A 0.8) (last(A) == 0.8) No sums required, all balances are checked by essentially "lookup the latest appearance in a db", where lookup also involves some merkle trees optimization, which I didn't understand enough to remember.

samhw 5y ago
> I was confused about how it does sum up all transactions to get the balance so quickly, like we in accounting usually do For what it's worth, in a system design context, this is called event sourcing (in more general terms it's just a fold, though the idea of event sourcing doesn't preclude caching). I worked at a bank for a few years and this was how we calculated balances too. > And instead it does this: I don't quite follow you here. It does something distinct from either a fold or caching the current total? (Do you mean that each transaction encodes the resultant balance?)
- wruza 5y ago
  Not the entire balance, because a single wallet may have many "inputs", but that's just an implementation detail, as far as I understand it. https://en.bitcoin.it/wiki/Transaction https://en.bitcoin.it/wiki/Transaction If the input is worth 50 BTC but you only want to send 25 BTC, Bitcoin will create two outputs worth 25 BTC: one to the destination, and one back to you (known as "change", though you send it to yourself). Any input bitcoins not redeemed in an output is considered a transaction fee; whoever generates the block can claim it by inserting it into the coinbase transaction of that block. I.e. any "input" is spent completely by a transaction and this makes it irrelevant to future calculations. See also: https://bitcoin.stackexchange.com/questions/13069/how-does-the-network-know-my-current-balance https://bitcoin.stackexchange.com/questions/13069/how-does-t...

barrkel 5y ago

Upgrading, not updating.