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

81–90 of 116 posts

Re: Our Journey to PostgreSQL 12

#81

Post by kevas »

philipwhiuk2 wrote: post #33
lmarcos wrote: post #12

Silly question: they have 5.7TB in their database... How come? It's a dating app founded in 2012, I can understand that one can accumulate such much data in 11 years, but sure you can periodically archive "unused" data and move it out of your primary database, right? I mean, are the 5.7TB of data actually needed in a daily basis by their app? (I assume data for analytical purposes is not stored in their primary DB, w…

Purely at a guess, people's images are stored in the app as blobs because it's "easier"

What was the strategy for moving things over? By age? Monitoring queries and determining what data isn’t being queried? Something else?

Re: Our Journey to PostgreSQL 12

#82

Post by tommyzli »

sk5t wrote: post #77
tommyzli wrote: post #6

Earlier quoted context omitted.

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?

Believe it or not these numbers are actually from _after_ me and some others spent a few weeks cleaning up our heavier queries

Re: Our Journey to PostgreSQL 12

#83

Post by hans_castorp »

mooreds wrote: post #2

Amazing that the process went so smoothly and that there were so many resources for them to draw from. Jumping from 9 to 12 is quite a few major versions! Also liked the couple of gotchas which go to show no matter how smooth a data migration is, there'll be some bumps.

> Jumping from 9 to 12 is quite a few major versions!

Just a little side note.

They were jumping from 9.6 to 12, not from 9.0 to 12.

Before Postgres 10 was released, the first two digits defined a "major" version). So from 9.6 to 12 it's three major releases (9.6 -> 10, 10 -> 11, 11 -> 12)

Re: Our Journey to PostgreSQL 12

#84

Post by mrweasel »

diziet wrote: post #15
lmarcos wrote: post #12

Silly question: they have 5.7TB in their database... How come? It's a dating app founded in 2012, I can understand that one can accumulate such much data in 11 years, but sure you can periodically archive "unused" data and move it out of your primary database, right? I mean, are the 5.7TB of data actually needed in a daily basis by their app? (I assume data for analytical purposes is not stored in their primary DB, w…

Imagine there are 10m users. That's 600kb per user.

That's really a good way of looking at it. I though it sounded like a lot of data, well, 600kb is a lot of textual data, but who knows what they have stuffed into the database.

I worked for an e-commerce site, with a few million customers, even more orders, data-duplication all over the place, and still we where using a perhaps a 200GB of database storage.

Re: Our Journey to PostgreSQL 12

#85

Post by ddorian43 »

namibj wrote: post #68
paulryanrogers wrote: post #18

Earlier quoted context omitted.

Distributed systems are hard. Multi master is particularly sticky, especially if the data doesn't have natural boundaries. Once solved though horizontal is nice, if more involved to maintain.

CockroachDB is pretty good at encapsulating the complexity of multi-master. You'll have to accept that transactions can fail due to conflicts, so if they are interactive, you'll have to retry manually. Edit: I'd like hear criticism, instead of just seeing disapproval.

(as a downvoter) Distributed transactions don't scale, they are NOT efficient. You can't co-partition data in Cockroachdb, so the only way is the slow way. Interleaved-data don't count.

Re: Our Journey to PostgreSQL 12

#86

Post by sa46 »

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.

We tried pg_upgrade going from Postgres 10 to Postgres 12 and it didn't work. An individual instance was about 8 TiB in size. We left it running for over a day to see if it would complete. Instead, we used an approach similar to logical replication described in the article.

To be fair, our use case was probably close to pathological for pg_upgrade. We had lots of TOAST data and dozens to hundreds of indexes per table.

Re: Our Journey to PostgreSQL 12

#87

Post by rossmohax »

lumost wrote: post #66
arcticfox wrote: post #41

Earlier quoted context omitted.

5.7 TB for an OLTP database is small?! I must be living in a different world. Obviously I know you can go that big, but I thought the number of use-cases would be limited.

Why does my browser routinely eat 8GB while it used to only require 32MB 25 years ago? because it can. Web services likewise come up with features and data to fill databases. For $8/hr you can rent a DB with 500 GB of memory and 64 cores, complete with redundancy, automated backups, and failover. For the hourly rate of an oracle consultant you can rent a DB with 2TB of memory for the day. Bear in mind that many of th…

If data is sharable, it doesn't mean that it is trivial. In your example with shards by user, simple message sent between users in app becomes are very non trivial dance to be done reliably.

Re: Our Journey to PostgreSQL 12

#89

Post by ants_a »

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?

In pg_upgrade documentation there is documented a way to use rsync to quickly replicate the upgraded contents of the new primary to replicas. So you would first move to upgraded base VMs running the old version streaming from old primary, which can be done one host at a time if need be.

The new cluster can then be pg_upgraded and rsynced all at once.

Re: Our Journey to PostgreSQL 12

#90

Post by foxhill »

hans_castorp wrote: post #83
mooreds wrote: post #2

Amazing that the process went so smoothly and that there were so many resources for them to draw from. Jumping from 9 to 12 is quite a few major versions! Also liked the couple of gotchas which go to show no matter how smooth a data migration is, there'll be some bumps.

> Jumping from 9 to 12 is quite a few major versions! Just a little side note. They were jumping from 9.6 to 12, not from 9.0 to 12. Before Postgres 10 was released, the first two digits defined a "major" version). So from 9.6 to 12 it's three major releases (9.6 -> 10, 10 -> 11, 11 -> 12)

still, changes between 9.6 and 12 are _numerous_, both in features and performance: llvm based query compilation, CTE de-materialisation, proper procedures, and that's just off the top of my head.

i wish the process for upgrading postgres were easier/more dynamic. i'm sure plenty of people are still using versions 9.6 or earlier.

Post reply on HN