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

41–50 of 116 posts

Re: Our Journey to PostgreSQL 12

#41

Post by arcticfox »

jamesmishra wrote: post #16
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…

5.7 TB is small by database standards. I work at a much smaller company and deal with "proportionally" much more data. I don't know if it would be worth the engineering effort to try and archive old data in a way that still makes it transparently accessible to users that go looking for it--especially when modern databases ought to be able to scale up and out without manual archiving.

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.

Re: Our Journey to PostgreSQL 12

#42

Post by skunkworker »

paulryanrogers wrote: post #21
tommyzli wrote: post #13

Earlier quoted context omitted.

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?

Good questions. As logical replication matures it may someday be possible to replicate among versions.

Logical replication would be nice, but at the moment it's far too slow to replicate for heavy day-to-day use, other than doing a pg upgrade between primaries and replicas.

Re: Our Journey to PostgreSQL 12

#43

Post by tyingq »

justinclift wrote: post #37

Reading over this, it seems like there isn't an offsite backup done of the database? eg to have a copy of the data in a "safe place" off AWS infrastructure If something goes wrong with their relationship with AWS, that could be business ending. :(

The termination clauses in their T&C's say they will give you access, post "for cause" termination, so long as you've paid your bill. Though I'm mindful that pulling a lot of data could take a long time.

Re: Our Journey to PostgreSQL 12

#44

Post by hermanradtke »

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…

Agreed. I ran a 1 billion dollar GMV e-commerce company and our primary OLTP database was around 60 GB. Everything else was moved to an OLAP database.

Re: Our Journey to PostgreSQL 12

#45

Post by tommyzli »

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"

Nope.

Re: Our Journey to PostgreSQL 12

#46

Post by kall »

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…

Putting data into cold storage, spinning up multiple flexible access points with different datasets... Sounds like what Snowflake is doing right? I don‘t really use it but looks neat from the outside. May be nice to bring some of that to OLTP.

Re: Our Journey to PostgreSQL 12

#47

Post by tommyzli »

cwyers wrote: post #34
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.

Only if they evaluated RDS and found it wanting. They don't even mention testing it.

It's not in the post, but I answered this in a separate thread. RDS doesn't let us provision as many IOPS as we need.

Apparently Aurora behaves differently, but I wasn't aware of that when we specced out the project.

Re: Our Journey to PostgreSQL 12

#48

Post by derekperkins »

systems wrote: post #38
jamesmishra wrote: post #16

Earlier quoted context omitted.

5.7 TB is small by database standards. I work at a much smaller company and deal with "proportionally" much more data. I don't know if it would be worth the engineering effort to try and archive old data in a way that still makes it transparently accessible to users that go looking for it--especially when modern databases ought to be able to scale up and out without manual archiving.

its huge by database standard, i worked in large multinationals and dealt with some of the their largest databases 5.7 is enormous by database standard , there is no way you can get good query performance on a 5.7 tb database without solid physical partitioning and heavily optimized queries, and most normal companies even with 200-500 GB database use datamarts to have good performance without a super complex architec…

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.

Re: Our Journey to PostgreSQL 12

#49

Post by wejick »

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…

I think it's because they use Django and naturally it's a monolithic architecture. We will hardly find database this big in microservice world, instead there will be several smaller databases.

Now whether several smaller DBs is easier to manage compared to one big one, it would be debatable. However with that huge DB, I would prefer having several smaller one.

Re: Our Journey to PostgreSQL 12

#50

Post by craigkerstiens »

outworlder wrote: post #31

> As I mentioned earlier we run Postgres on i3.8xlarge instances in EC2, which come with about 7.6TB of NVMe storage. Wait a second. You run your production database on ephemeral storage? Wow. I see the replication setup and the S3 WAL archiving and whatnot but still... that's brave.

It's really a question of how many replicas you have, if you're running with sync rep or not, and what your DR story is like. We've tested it before a few times at previous employers and are exploring it rolling it out for Crunchy Bridge currently. The NVMe storage is really nice it's great performance and the price balance of it is good as well. But it does come with nuances... I wouldn't let a user provision without HA for example. In cases for a standard app without crazy uptime requirements having a standby or 2 is wasted cash. So it isn't for everyone, but can be for some people.
Post reply on HN