9 ms·

Our Journey to PostgreSQL 12

215 points by tommyzli 6y ago

mooreds 6y ago
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.
- hans_castorp 6y ago
  > 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)
  - foxhill 6y ago
    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.
    - m_st 6y ago
      The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? And if you didn't adapt: Would your code still running after upgrading (backwards compatible)? I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.
      - wiredfool 6y ago
        I've done PG upgrades on a code base from ~7.x now through 11. For the most part it's pretty seamless, though there are occasionally backwards incompatible changes that pop up, but they're called out in the release notes. The biggest bites have been changing the database driver from pypgsql to psycopg2 and there was a binary column default wire format change at the 9/9.1 upgrade. In another job, I've got essentially the same code running against 9.5 and 12, and I don't notice the difference. Performance improvements for a feature are almost always 'free', but obviously your code isn't just going to start using CTEs if you haven't been using them.
      - dijit 6y ago
        > The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? The changes were made "for-free" as in no code needed to be changed, but when changing query planners caution should be heeded.. I remember a time where a mysql 5.6->5.7 upgrade crippled our entire monitoring stack: https://bugs.mysql.com/bug.php?id=87164 https://bugs.mysql.com/bug.php?id=87164 https://dba.stackexchange.com/questions/121841/mysql-5-7-innodb-is-extremely-slow-on-spatial-data-compared-to-mysql-5-6 https://dba.stackexchange.com/questions/121841/mysql-5-7-inn...
      - foxhill 6y ago
        whilst "free" in principle, there is obvious cost/risk in adopting features like those (CTE demat and llvm), if nothing else, in even trying them out. testing the llvm-based optimiser (in postgres 11) showed little benefit, and in a few circumstances slowed things down. had i had more time to poke around, i'd have probably got it to a place where it represented an improvement, but, up until i left that role, it was a feature that remained switched off - postgres doesn't generally introduce features that represents "breaks" in code. having not had _that_ much experience with MS-SQL, i'd still recommend the switch. looking through the things in postgres that i take for granted vs. what's in MS-SQL, i don't think i could cope with not having postgres :)
temp667 6y ago
Very nice. Did they migrate into Amazon RDS while doing this? For smaller projects I've stopped doing the self managed postgresql thing. The pricing is higher (75%?) for RDS for some use cases but can be worth it. Going to try RDS Proxy next.
- tommyzli 6y ago
  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
  - orf 6y ago
    That limit doesn’t apply to Aurora - did you consider that?
    - tommyzli 6y ago
      Apparently I'm living in the twilight zone because I have a vivid memory of reading the Aurora docs and seeing the same limit. Oh well, it's something to consider for the next upgrade.
  - throwdbaaway 6y ago
    That's an impressive amount of IOPS. I have always been a EBS / pd-ssd guy, and mostly rely on memory to reduce the IOPS requirement. But as cloud providers typically charge a ridiculous amount of money for memory, a setup like yours with instance storage / local-ssd is an intriguing option.
  - sk5t 6y ago
    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?
    - 6y ago
u678u 6y ago
I love RDBMS over NoSql but the whole upgrade and schema change always is stressful. I miss the days when we could ask our DBA to deal with it. :)
- jabo 6y ago
  At least with RDBMS, the database engine takes care of the actual data movement for you, after you issue a SQL command. With NoSQL, when you need to update your document format, you now need to handle the data migration yourself.
haltingproblem 6y ago
Love your app, easily the best experience of all dating apps. However, stability and notifications are atrocious. App notifications lead the data showing up in the app. Sometimes notifications fail all together. You guys can dominate this space if you can fix these issues.
0xbadcafebee 6y ago
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 has just been how the most popular products have done it, and they largely just never changed their designs in 40 years. I like the language, and the general organization of the data is handy. But everything else about it is archaic. Why fumble around with synchronization? 99% of the data in big datasets doesn't change. This doesn't even have to be "log-based", we just need to be able to ship the old, stable data and treat it almost like "cold storage". Why is there a single point of entry into the data? You have to use the one database cluster to access the one database and the one set of tables. Why can't we expose that same data in multiple ways, using multiple pieces of software, on multiple endpoints? Other protocols and languages have ways of dealing with these kinds of things. LDAP can refer you to a different endpoint to process what you need. Web servers can store, process, and retrieve the same content across many different endpoints in a variety of ways. Lots of technology exists that can easily replicate, snapshot, version-control, etc arbitrary pieces of data and expose them to any application using standard interfaces. Why haven't we created a database yet which works more like the Unix operating system?
- paulryanrogers 6y ago
  Why are we still using ASCII or Unicode character interfaces in shells? Because like SQL they work and are moderately well understood. There are many query languages and having one common one as a base is useful to transfer skills. Think of it as an on ramp to more specific dialects or technologies.
- outworlder 6y ago
  > 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.
  - 6y ago
shoo 6y ago
> We then made the following changes to the subscriber database in order to speed up the synchronization: [...] Set fsync to off I'm curious how much risk of data loss this added. I guess the baseline is "we need to migrate before we run out of disk" I.e. you're either going to have data loss or a long period of unavailability if the migration cannot be carried out fast enough.
- ants_a 6y ago
  If fsync is turned back on and a manual sync call is issued before considering the replica valid there will be no risk from this.
- tommyzli 6y ago
  ants_a is correct. Also, our NVMe storage is ephemeral so you aren't recovering from a power loss anyways :)
  - rubiquity 6y ago
    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."
    - tommyzli 6y ago
      TIL, this is really good to know! Do you know offhand if this is a new feature, or have I just always been wrong
      - sk5t 6y ago
        It's been that way for a long time - years at least. You can reboot but not 'stop' and of course not 'terminate.'
ants_a 6y ago
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.
- tommyzli 6y ago
  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?
  - paulryanrogers 6y ago
    Good questions. As logical replication matures it may someday be possible to replicate among versions.
    - sharadov 6y ago
      I've replicated across different PG versions, there is an extension called mimeo, which is fantastic for logical replication https://pgxn.org/dist/mimeo/1.5.1/doc/howto_mimeo.html https://pgxn.org/dist/mimeo/1.5.1/doc/howto_mimeo.html
    - skunkworker 6y ago
      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.
  - craigkerstiens 6y ago
    I believe it is possible to repoint the replicas with re-wind and then repoint to a new timeline. This is something we looked at at Heroku a long time back. It wasn't trivial to fix, but Heroku eventually improved some of this. This post drills into some of that - https://blog.keikooda.net/2017/10/18/battle-with-a-phantom-wal-segment/ https://blog.keikooda.net/2017/10/18/battle-with-a-phantom-w...
lmarcos 6y ago
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, which is fair to assume I believe)
- diziet 6y ago
  Imagine there are 10m users. That's 600kb per user.
  - mbyio 6y ago
    And you have to account for indexes, temporary tables used for data analysis, etc. And most of it is probably not compressed. So with that perspective it isn't that much data at all.
  - stickfigure 6y ago
    That's an incredibly large amount per user? I have worked on a couple online dating sites, including one that was fairly popular (Let's Date - which stiffed me for my last invoice before they went belly up grrr). Unless you're storing images in the database, it's really hard to generate 600k for a dating workload - even with indexes. The only thing I can imagine generating 600k per user is putting something like "hit tracking" in the database. Which I've done - yes it adds up - but it's also relatively easy to move to some other kind of store.
    - curryst 6y ago
      If messages between users go into their main database, then that would be a pretty reasonable amount.
      - stickfigure 6y ago
        600k is a sizable book. The Adventures of Huckleberry Finn is 600k. And that's the average per-user; most users will never send or receive messages. The only thing I can imagine is that they do an incredible amount of activity tracking.
mbyio 6y ago
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 your architecture, so I'm curious why you didn't go that route.
- paulryanrogers 6y ago
  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.
  - namibj 6y ago
    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.
    - ddorian43 6y ago
      (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.
      - namibj 6y ago
        Are you suggesting it hits scaling boundaries earlier than that single-node postgres? The TPC-C benchmark suggests fairly strong scaling up to medium~high double digit node counts, and my understanding is that it's decently conflict prone and very much relying on distributed transactions. Of course a distributed architecture costs efficiency, but as long as it still scales further (and after some point, cheaper) than single-node alternatives, the efficiency loss is tolerable. You can affect partitioning, but forcing it requires the payed version.
deleted 6y ago
[deleted]
latchkey 6y ago
I read this and all I can think about is all that private information on some unmaintained database server.
sharadov 6y ago
Given the limitations that you had ( move to larger instance, downtime restrictions), you took the most optimal path. Fantastic work! I am in the process of what you did, but across couple hundred instances ( am using pg_upgrade for most but will be using an approach similar to yours where we can't afford downtime).
outworlder 6y ago
> 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.
- eropple 6y ago
  It's funny--we used to run Vertica on ephemeral nodes and actually found a performance improvement going to EBS, but that was pre-NVMe in AWS. I wonder how big the delta was for CMB between EBS and ephemeral?
- tommyzli 6y ago
  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.
  - throwdbaaway 6y ago
    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-187f1e4ef859 https://netflixtechblog.medium.com/datastore-flash-upgrades-...
    - phonon 6y ago
      Why can't you reboot? NVMe storage is local, not ephemeral.
      - throwdbaaway 6y ago
philipwhiuk2 6y ago
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.
- cwyers 6y ago
  Only if they evaluated RDS and found it wanting. They don't even mention testing it.
  - tommyzli 6y ago
    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.
    - phonon 6y ago
      Aurora charges $0.20 per 1 million requests...your IO would have gotten expensive. It's also still stuck on PostgreSQL 11.9.
    - darkr 6y ago
      RDS can do up to 64K PIOPS these days. If you need more than that, I would definitely advocate for re-architecture to split things out and/or shard. We're up to 40k PIOPS in one of our larger databases and fast approaching the time to make that jump ourselves..
- tyingq 6y ago
  I suspect the RDS limitations are left there to push you to Aurora. They control that and would be better equipped to make the most of their infrastructure and margins with it.
- ramraj07 6y ago
  RDS can be.. expensive? Like by a lot?
justinclift 6y ago
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. :(
- tyingq 6y ago
  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.
  - Dylan16807 6y ago
    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.
rubiquity 6y ago
Uisng async replication and read replicas in a relational DB is a great way to play reverse Wheel of Fortune and go from ACID to just C. You must get some fun bug reports. A poster below mentions doing actions in the app and their side effects vanishing. At the end of the day it's a business decision but that would not be fun to program against, though maybe some of it can be handled with app/client-side with caching and causal consistency. edit: For more on the nuances of Postgres tradeoffs for replication and transaction isolation: https://www.postgresql.org/docs/9.1/high-availability.html https://www.postgresql.org/docs/9.1/high-availability.html
esseti 6y ago
if pglogical better than a min downtime with pgdump/psql? it seems a lot of work to setup pglogical to migrate versions (or am i missing anything?)
- darkr 6y ago
  With 5.7 TB data, you're probably looking at something like 24 hours for a dump/restore including index rebuilds

215 points by tommyzli 6y ago

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.

hans_castorp 6y ago
> 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)
- foxhill 6y ago
  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.
  - m_st 6y ago
    The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? And if you didn't adapt: Would your code still running after upgrading (backwards compatible)? I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.
    - wiredfool 6y ago
      I've done PG upgrades on a code base from ~7.x now through 11. For the most part it's pretty seamless, though there are occasionally backwards incompatible changes that pop up, but they're called out in the release notes. The biggest bites have been changing the database driver from pypgsql to psycopg2 and there was a binary column default wire format change at the 9/9.1 upgrade. In another job, I've got essentially the same code running against 9.5 and 12, and I don't notice the difference. Performance improvements for a feature are almost always 'free', but obviously your code isn't just going to start using CTEs if you haven't been using them.
    - dijit 6y ago
      > The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code? The changes were made "for-free" as in no code needed to be changed, but when changing query planners caution should be heeded.. I remember a time where a mysql 5.6->5.7 upgrade crippled our entire monitoring stack: https://bugs.mysql.com/bug.php?id=87164 https://bugs.mysql.com/bug.php?id=87164 https://dba.stackexchange.com/questions/121841/mysql-5-7-innodb-is-extremely-slow-on-spatial-data-compared-to-mysql-5-6 https://dba.stackexchange.com/questions/121841/mysql-5-7-inn...
    - foxhill 6y ago
      whilst "free" in principle, there is obvious cost/risk in adopting features like those (CTE demat and llvm), if nothing else, in even trying them out. testing the llvm-based optimiser (in postgres 11) showed little benefit, and in a few circumstances slowed things down. had i had more time to poke around, i'd have probably got it to a place where it represented an improvement, but, up until i left that role, it was a feature that remained switched off - postgres doesn't generally introduce features that represents "breaks" in code. having not had _that_ much experience with MS-SQL, i'd still recommend the switch. looking through the things in postgres that i take for granted vs. what's in MS-SQL, i don't think i could cope with not having postgres :)

temp667 6y ago

Very nice. Did they migrate into Amazon RDS while doing this? For smaller projects I've stopped doing the self managed postgresql thing. The pricing is higher (75%?) for RDS for some use cases but can be worth it. Going to try RDS Proxy next.

tommyzli 6y ago
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
- orf 6y ago
  That limit doesn’t apply to Aurora - did you consider that?
  - tommyzli 6y ago
    Apparently I'm living in the twilight zone because I have a vivid memory of reading the Aurora docs and seeing the same limit. Oh well, it's something to consider for the next upgrade.
- throwdbaaway 6y ago
  That's an impressive amount of IOPS. I have always been a EBS / pd-ssd guy, and mostly rely on memory to reduce the IOPS requirement. But as cloud providers typically charge a ridiculous amount of money for memory, a setup like yours with instance storage / local-ssd is an intriguing option.
- sk5t 6y ago
  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?
  - 6y ago

u678u 6y ago

I love RDBMS over NoSql but the whole upgrade and schema change always is stressful. I miss the days when we could ask our DBA to deal with it. :)

jabo 6y ago
At least with RDBMS, the database engine takes care of the actual data movement for you, after you issue a SQL command. With NoSQL, when you need to update your document format, you now need to handle the data migration yourself.

haltingproblem 6y ago

Love your app, easily the best experience of all dating apps. However, stability and notifications are atrocious. App notifications lead the data showing up in the app. Sometimes notifications fail all together. You guys can dominate this space if you can fix these issues.

0xbadcafebee 6y ago

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 has just been how the most popular products have done it, and they largely just never changed their designs in 40 years. I like the language, and the general organization of the data is handy. But everything else about it is archaic. Why fumble around with synchronization? 99% of the data in big datasets doesn't change. This doesn't even have to be "log-based", we just need to be able to ship the old, stable data and treat it almost like "cold storage". Why is there a single point of entry into the data? You have to use the one database cluster to access the one database and the one set of tables. Why can't we expose that same data in multiple ways, using multiple pieces of software, on multiple endpoints? Other protocols and languages have ways of dealing with these kinds of things. LDAP can refer you to a different endpoint to process what you need. Web servers can store, process, and retrieve the same content across many different endpoints in a variety of ways. Lots of technology exists that can easily replicate, snapshot, version-control, etc arbitrary pieces of data and expose them to any application using standard interfaces. Why haven't we created a database yet which works more like the Unix operating system?

paulryanrogers 6y ago
Why are we still using ASCII or Unicode character interfaces in shells? Because like SQL they work and are moderately well understood. There are many query languages and having one common one as a base is useful to transfer skills. Think of it as an on ramp to more specific dialects or technologies.
outworlder 6y ago
> 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.
- 6y ago

shoo 6y ago

> We then made the following changes to the subscriber database in order to speed up the synchronization: [...] Set fsync to off I'm curious how much risk of data loss this added. I guess the baseline is "we need to migrate before we run out of disk" I.e. you're either going to have data loss or a long period of unavailability if the migration cannot be carried out fast enough.

ants_a 6y ago
If fsync is turned back on and a manual sync call is issued before considering the replica valid there will be no risk from this.
tommyzli 6y ago
ants_a is correct. Also, our NVMe storage is ephemeral so you aren't recovering from a power loss anyways :)
- rubiquity 6y ago
  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."
  - tommyzli 6y ago
    TIL, this is really good to know! Do you know offhand if this is a new feature, or have I just always been wrong
    - sk5t 6y ago
      It's been that way for a long time - years at least. You can reboot but not 'stop' and of course not 'terminate.'

ants_a 6y ago

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.

tommyzli 6y ago
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?
- paulryanrogers 6y ago
  Good questions. As logical replication matures it may someday be possible to replicate among versions.
  - sharadov 6y ago
    I've replicated across different PG versions, there is an extension called mimeo, which is fantastic for logical replication https://pgxn.org/dist/mimeo/1.5.1/doc/howto_mimeo.html https://pgxn.org/dist/mimeo/1.5.1/doc/howto_mimeo.html
  - skunkworker 6y ago
    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.
- craigkerstiens 6y ago
  I believe it is possible to repoint the replicas with re-wind and then repoint to a new timeline. This is something we looked at at Heroku a long time back. It wasn't trivial to fix, but Heroku eventually improved some of this. This post drills into some of that - https://blog.keikooda.net/2017/10/18/battle-with-a-phantom-wal-segment/ https://blog.keikooda.net/2017/10/18/battle-with-a-phantom-w...

lmarcos 6y ago

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, which is fair to assume I believe)

diziet 6y ago
Imagine there are 10m users. That's 600kb per user.
- mbyio 6y ago
  And you have to account for indexes, temporary tables used for data analysis, etc. And most of it is probably not compressed. So with that perspective it isn't that much data at all.
- stickfigure 6y ago
  That's an incredibly large amount per user? I have worked on a couple online dating sites, including one that was fairly popular (Let's Date - which stiffed me for my last invoice before they went belly up grrr). Unless you're storing images in the database, it's really hard to generate 600k for a dating workload - even with indexes. The only thing I can imagine generating 600k per user is putting something like "hit tracking" in the database. Which I've done - yes it adds up - but it's also relatively easy to move to some other kind of store.
  - curryst 6y ago
    If messages between users go into their main database, then that would be a pretty reasonable amount.
    - stickfigure 6y ago
      600k is a sizable book. The Adventures of Huckleberry Finn is 600k. And that's the average per-user; most users will never send or receive messages. The only thing I can imagine is that they do an incredible amount of activity tracking.

mbyio 6y ago

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 your architecture, so I'm curious why you didn't go that route.

paulryanrogers 6y ago
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.
- namibj 6y ago
  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.
  - ddorian43 6y ago
    (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.
    - namibj 6y ago
      Are you suggesting it hits scaling boundaries earlier than that single-node postgres? The TPC-C benchmark suggests fairly strong scaling up to medium~high double digit node counts, and my understanding is that it's decently conflict prone and very much relying on distributed transactions. Of course a distributed architecture costs efficiency, but as long as it still scales further (and after some point, cheaper) than single-node alternatives, the efficiency loss is tolerable. You can affect partitioning, but forcing it requires the payed version.

deleted 6y ago

[deleted]

latchkey 6y ago

I read this and all I can think about is all that private information on some unmaintained database server.

sharadov 6y ago

Given the limitations that you had ( move to larger instance, downtime restrictions), you took the most optimal path. Fantastic work! I am in the process of what you did, but across couple hundred instances ( am using pg_upgrade for most but will be using an approach similar to yours where we can't afford downtime).

outworlder 6y ago

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

eropple 6y ago
It's funny--we used to run Vertica on ephemeral nodes and actually found a performance improvement going to EBS, but that was pre-NVMe in AWS. I wonder how big the delta was for CMB between EBS and ephemeral?
tommyzli 6y ago
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.
- throwdbaaway 6y ago
  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-187f1e4ef859 https://netflixtechblog.medium.com/datastore-flash-upgrades-...
  - phonon 6y ago
    Why can't you reboot? NVMe storage is local, not ephemeral.
    - throwdbaaway 6y ago

philipwhiuk2 6y ago

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.

cwyers 6y ago
Only if they evaluated RDS and found it wanting. They don't even mention testing it.
- tommyzli 6y ago
  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.
  - phonon 6y ago
    Aurora charges $0.20 per 1 million requests...your IO would have gotten expensive. It's also still stuck on PostgreSQL 11.9.
  - darkr 6y ago
    RDS can do up to 64K PIOPS these days. If you need more than that, I would definitely advocate for re-architecture to split things out and/or shard. We're up to 40k PIOPS in one of our larger databases and fast approaching the time to make that jump ourselves..
tyingq 6y ago
I suspect the RDS limitations are left there to push you to Aurora. They control that and would be better equipped to make the most of their infrastructure and margins with it.
ramraj07 6y ago
RDS can be.. expensive? Like by a lot?

justinclift 6y ago

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. :(

tyingq 6y ago
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.
- Dylan16807 6y ago
  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.

rubiquity 6y ago

Uisng async replication and read replicas in a relational DB is a great way to play reverse Wheel of Fortune and go from ACID to just C. You must get some fun bug reports. A poster below mentions doing actions in the app and their side effects vanishing. At the end of the day it's a business decision but that would not be fun to program against, though maybe some of it can be handled with app/client-side with caching and causal consistency. edit: For more on the nuances of Postgres tradeoffs for replication and transaction isolation: https://www.postgresql.org/docs/9.1/high-availability.html https://www.postgresql.org/docs/9.1/high-availability.html

esseti 6y ago

if pglogical better than a min downtime with pgdump/psql? it seems a lot of work to setup pglogical to migrate versions (or am i missing anything?)

darkr 6y ago
With 5.7 TB data, you're probably looking at something like 24 hours for a dump/restore including index rebuilds