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

61–70 of 116 posts

Re: Our Journey to PostgreSQL 12

#61

Post by Dylan16807 »

tyingq wrote: post #43
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.

That's still a lot of trust that nothing else wipes the account.

But this post wasn't about backups, so there might be a whole lot excluded from the diagram.

Re: Our Journey to PostgreSQL 12

#62

Post by Dylan16807 »

outworlder wrote: post #30
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…

> Why haven't we created a database yet which works more like the Unix operating system? Not to be overly snarky, but have you tried? Database design is full of trade-offs.

You're basically telling them to put in months of work to find out. Even if it's not too snarky, it's a ridiculous way to learn something that could be conveyed pretty well in a blog post or a chapter of a book.

Re: Our Journey to PostgreSQL 12

#63

Post by throwdbaaway »

derekperkins wrote: post #48
systems wrote: post #38

Earlier quoted context omitted.

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.

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 pain.

Re: Our Journey to PostgreSQL 12

#64

Post by phonon »

tommyzli wrote: post #47
cwyers wrote: post #34

Earlier quoted context omitted.

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.

Aurora charges $0.20 per 1 million requests...your IO would have gotten expensive. It's also still stuck on PostgreSQL 11.9.

Re: Our Journey to PostgreSQL 12

#65

Post by bcrosby95 »

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.

This was pretty common in AWS back in the late 00s. Performance usually sucked too much otherwise.

Re: Our Journey to PostgreSQL 12

#66

Post by lumost »

arcticfox wrote: post #41
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.

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 these workloads are trivially shardable (e.g. any table keyed off customer ID) and can be scaled across hundreds of DBs as required.

Re: Our Journey to PostgreSQL 12

#67

Post by throwdbaaway »

tommyzli wrote: post #40
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.

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 issue: https://netflixtechblog.medium.com/datastore-flash-upgrades-...

Re: Our Journey to PostgreSQL 12

#68

Post by namibj »

paulryanrogers wrote: post #18
mbyio wrote: post #14

This is a very difficult thing to do. Very impressive. I have so many questions but my number one is: were you able to evaluate alternatives to your existing vertical scaling based setup? For example, cockroachdb, multi-master postgres, using sharding instead of a single DB, etc. At that database size, you are well past the point in which a more advanced DB technology would theoretically help you scale and simplify y…

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.

Re: Our Journey to PostgreSQL 12

#69

Post by snissn »

craigkerstiens wrote: post #50
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 withou…

hi! Can you share how you do HA on postgres? Master/slave with monitoring and manual fall over or is that automatic? If so reliable? What tooling do you use? Thanks!

Re: Our Journey to PostgreSQL 12

#70

Post by tommyzli »

rubiquity wrote: post #55
tommyzli wrote: post #22

Earlier quoted context omitted.

ants_a is correct. Also, our NVMe storage is ephemeral so you aren't recovering from a power loss anyways :)

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
Post reply on HN