9 ms·

Lessons learned scaling a PostgreSQL database to 1.2bn records/month

259 points by willvarfar 8y ago

ggm 8y ago
I will never complain about my minor pg issues again. The beast that is running here says whatever else, my problems are small. I'm interested in learning more about sharded design, time series and json data if anyone else reading this or the OP wants to write more.
elken 8y ago
Very interesting read. I'm not convinced that Postgres should be used for every aspect of a data processing pipeline yet but the author has certainly proved it is possible. You also seem to get a lot of features for free that people using Kafka can only dream of. It will be interesting to see how people will take advantage of new Postgres features in the future.
gomezjdaniel 8y ago
Thanks for the read!, a couple of questions: > Now we are renting our own hardware and maintain the database. We have a lot better hardware than any of the cloud service providers could offer Which provider are you using? > That 30% we can use to hire a freelance DBA to check in on the servers once a day. Where did you find this freelancer?
- emddudley 8y ago
  > hire a freelance DBA to check in on the servers once a day Not sure whether you were just interested in the author's specific freelancer but-- The term for this is "Remote DBA" and there are lots of them. I suppose the difficult part is confirming trustworthiness and skills before giving them direct access to your database.
willvarfar 8y ago
And I have built a very similar, perhaps even slightly larger, system, only in MySQL with TokuDB :) These free old-fashioned RDBMSs really can go fairly big and stay really fast; much bigger and faster than the noSQL stuff I also use. One thing that pushes people towards noSQL is that it is easier to get started; but I think that I've saved buckets of time in the long run by going with a conventional relational database. ymmv but its always worth thinking 'is this problem too big for a classic free RDBMS?' and the answer is almost always 'hell, no!'
sandGorgon 8y ago
> The primary reason we migrated away from Google SQL for PostgreSQL is because we discovered a bug that was causing data corruption. This was a known bug that is fixed in newer PostgreSQL versions. However, Google SQL for PostgreSQL is several versions behind. The lack of response from the support acknowledging the issue was a big enough red-flag to move on. I am glad we did move on, because it has been 8 months since we have raised the issue, and the version of PostgreSQL has not been updated to-date We have had similar experiences on various DB related issues. AWS are miles ahead on anything to do around storage and databases.
- gralx 8y ago
  In PostgreSQL performance, Google Cloud Platform "beats AWS by a factor of 2 in all [Aiven.io] tests".
  - shoo 8y ago
    > A program that produces incorrect results twice as fast is infinitely slower. — John Osterhout
    - oskari 8y ago
      Not sure if this comment was about the data corruption bug in Cloud SQL's PostgreSQL version, but the benchmarks we ran at https://aiven.io/blog/postgresql-cloud-performance/ https://aiven.io/blog/postgresql-cloud-performance/ used Aiven's PostgreSQL service on top of VMs from the cloud infrastructure providers and weren't affected by Cloud SQL issues. The same Linux, PostgreSQL, etc versions were used on all platforms. We didn't use RDS, CloudSQL or other managed services from the infrastructure providers, but the PDF version of the presentation does include comparison of our managed PostgreSQL service with RDS PG and Aurora PG if you're interested.
    - gralx 8y ago
      The article doesn't say the GCP results were incorrect. What's your source?
datenhorst 8y ago
>Column order matters. We have tables with 60+ columns. Ordering columns to avoid padding saved 20%+ storage I need to look into this
- jihadjihad 8y ago
  This was an informative read: https://blog.2ndquadrant.com/on-rocks-and-sand/ https://blog.2ndquadrant.com/on-rocks-and-sand/
yeahitslikethat 8y ago
The article confirms the fact the best cost to performance ratio is colocated hardware you build. Next is managed server. Last: any of the cloud database providers. The key piece is knowing what you are doing. The clouds don't care about your tiny problems. They won't fix things for you. You must fix things for you.
- Heliosmaster 8y ago
  Exactly my experience. And not only about Databases. At a previous job I was able to cut our infrastructure expenses by a lot (70%) by essentially have our infra being 1 load-balancer, 2 beefy application servers and 1 (+1 failover) PostgreSQL database. Each of the application server costs 300~ Euros p/m , 24 cores and 256GB of RAM. I have not experienced the same cost to perf ratio anywhere else.
  - griffordson 8y ago
    What was your salary, and the salary of additional people to increase the bus factor to an acceptable level? There is a lot more than raw perf numbers that go into these calculations for any responsible business owner.
    - toomuchtodo 8y ago
      This misnomer is frequently brought up, but untrue. Whether you’re on prem or cloud, the skills are transferable and staff needed regardless unless you’re a dev who just knows API calls for cloud services (my condolences). Cloud doesn’t mean you don’t need to know the infrastructure, it’s workload management. You still need to know how the underlying fundamentals operate, or you get burned (either through excessive cost, unexpected performance degradation, or data loss). Disclaimer: 18 years in different ops roles
      - apple4ever 8y ago
        Absolutely. Its not like you will have one less person in the cloud. You'll still need the same amount of people (maybe more!), but they'll just be doing different things.
ngrilly 8y ago
Great example of a job queue correctly implemented inside a transactional relational database (with SKIP LOCKED and a table dedicated to the job queue), instead of using a specialized tool.
- ddorian43 8y ago
  When you don't need insane scalability, having a transactional queue is a godsend.
  - ngrilly 8y ago
    For the record, it's possible to have scalability and transactional enqueuing at the same time, with something like Google Cloud Datastore: https://cloud.google.com/appengine/docs/standard/python/datastore/transactions#transactional_task_enqueuing https://cloud.google.com/appengine/docs/standard/python/data...
    - ddorian43 8y ago
      Yeah, with sharding. And global transactions. Which are costly. As long as you pass the cost to the customer. But if you're doing a lot, then you can't use them.
- shoo 8y ago
  yes. the linked 2ndquadrant blog post explaining how to implement this using SKIP LOCKED is a good read: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/ https://blog.2ndquadrant.com/what-is-select-skip-locked-for-... i used this a few years ago to implement my own (toy-scale: 2 workers 1 db running on a potato-class thin client computer) idiosyncratic job queue in postgres. i have a number of recurring jobs that need to be run periodically, and the jobs are not independent -- different jobs depend upon shared resources -- so if lots of jobs that depend on resource R just ran, we might need to wait a while before the other jobs that also depend on R are deemed feasible to be scheduled and run. it was reasonably easy to cobble something together of the form: each worker process does the following: 0. within a transaction 1. select a shortlist of rows for jobs that are due and are currently feasible, subject constraints ; 2. attempt to acquire one of of those rows using "select ... for update skip locked limit 1" 3. do work, update job result and status in db, commit the transaction the only part of this that is slightly clever is that i don't actually have a hard requirement for mutual exclusion between worker processes to mediate access to shared resources, so that means each worker process can maintain its own state of its 'fair share' of resources that it is consuming, and throttle itself without needing to coordinate this with other workers.
hestefisk 8y ago
Very interesting read. Having worked myself on large Postgres databases, I would have loved to know about these tuning opportunities. It’s disappointing to see Google providing such poor support for Postgres though in Google Cloud. Even Microsoft provide first class support. They need to up their game.
threeseed 8y ago
> The takeaway here is that Google and Amazon prioritise their proprietary solutions (Google BigQuery, AWS Redshift) Except this is nonsense and there is no evidence to back it up. AWS Redshift is a replacement for an enterprise data warehouse. It is not suitable for a website, OLTP or for many analytics use cases and AWS has never claimed it to be as such. It's why they they offer a suite of different paradigm databases e.g. Aurora, DocumentDB, DynamoDB, Neptune and Redshift. Because no one database fits every use case. Also using a database as a job queue really is a strange thing to do when you are running on the cloud and there are dedicated services available specifically for this use case.
- lr4444lr 8y ago
  I don't think that's what he's implying. He means I think that the cloud providers are not very friendly to devs who will take the time to customize their RDBMS to squeeze more performance out instead of switching over to a more expensive solution capable of handling the kind of high volume OLAP he had to deal with.
manigandham 8y ago
What is Aiven supposed to do about a bug in Timescale that is still open? Aiven is great if you don't need the superuser access. They will run some bigger plans on local SSDs and allow you to create replicas and migrate across clouds and regions easily. It's a great way to run multi-cloud without shipping the data yourself. True that they could use more alerting features on resource usage. GCP managed services of open products are highly unrecommended for this exact reason of being very behind or even obsolete with very limited customization and control. Stick with BigTable, BigQuery, Dataflow and run the rest yourselfs on GCE/GKE.
- akulkarni 8y ago
  Just FYI - a fix for this bug was merged relatively quickly (within 4 days), and was released just this morning. https://github.com/timescale/timescaledb/issues/468#issuecomment-457654923 https://github.com/timescale/timescaledb/issues/468#issuecom...
  - manigandham 8y ago
    Congrats on 1.2, the analytical functions are a good addition.
    - akulkarni 8y ago
      Thank you :)
- oskari 8y ago
  Unfortunately we were not able to offer a concrete workaround in this case, but as mentioned to the author of the post, we were and are in contact with our friends at Timescale to resolve this, and an update with the fix will be available in Aiven shortly. We are not the upstream for any of the engines running in Aiven, so we work with the upstream developers and communities to resolve any issues found. Often our involvement in fixing the issues is through providing logs, stack traces and other debugging information to the original authors of the code, but in some cases we also fix the issues ourselves and always contribute such fixes back upstream, see e.g. this commit for a PostgreSQL bug that was found by our customers and fixed by us: https://github.com/postgres/postgres/commit/07ef035129586ca26a713c4cd15e550dfe35e643 https://github.com/postgres/postgres/commit/07ef035129586ca2...
patwolf 8y ago
> The primary reason we migrated away from Google SQL for PostgreSQL is because we discovered a bug that was causing data corruption Is anyone familiar with the details of the data corruption bug? I'm using Cloud SQL and would like to understand the risk.
- bluecheese 8y ago
  My Cloud SQL instance is currently on 9.6.6, I just got an email from GCP saying they are upgrading to 9.6.11 next week. There is a data corruption bug mentioned in the 9.6.7 release notes, not sure if it's the one the author is referring to. https://www.postgresql.org/docs/9.6/release-9-6-7.html https://www.postgresql.org/docs/9.6/release-9-6-7.html "Fix vacuuming of tuples that were updated while key-share locked (Andres Freund, Álvaro Herrera) In some cases VACUUM would fail to remove such tuples even though they are now dead, leading to assorted data corruption scenarios."
eatonphil 8y ago
I'm confused why hosting PostgreSQL themselves on GCP or AWS was not considered an option?
- shoo 8y ago
  my understanding was that they were trying to outsource as much of the ops & infrastructure side of things as possible. hence trying a third service provider after GCP & AWS before giving up and doing it themselves.
  - eatonphil 8y ago
    I got that. :) But they went from managed-db, managed-host to unmanaged-db, unmanaged-host when the clear middle ground is unmanaged-db, managed host. Maybe I missed it.
    - gajus 8y ago
      Hi, author here scanning through the comments. There are quite a few things that I had to leave out as the post was getting really long. I am writing a follow up article now titled "How Kubernetes and twelve-factor methodology saved us USD 300k in 24 months" where I am going to touch on this. The short version is that we never intended to stay with a cloud provider due to the costs of operating a large cluster (we continue to use GCP for critical components of our infrastructure, such as monitoring and alerting). It was convenient while we could afford it thanks to their startup programs, but migrating our data aggregation workers from cloud providers to dedicated servers cut costs by 40% (which adds up to a lot, as we are using ±0.5k vCPUs and _a lot_ of RAM).
      - justinclift 8y ago
        > Hi, author here scanning through the comments. Small note, there's a typo in the heading of "Google Cloud SQL for PosetgreSQL". ;) PosetgreSQL -> PostgreSQL
lettergram 8y ago
So my PostgreSQL deployment processes quite a bit more than 1.2bn records / month (closer to 10bn / month). What I have to say for AWS (which I use), the back up is why I use AWS. The ease of backing up, rolling back, and connecting additional services, and scaling make it killer. That being said, I’ve seen around 5x - 10x speed improvement for the same exact cost utilizing bare metal servers. I’ve been using online.net/scaleway and similar services. However the real value save is limited as you need to do a lot more work and often pay more for backing up. Overall, real savings might be 50% cost reduction, with some additional risk. Need more space? Launch a new server, copy, check, switch over, pray. With AWS it’s one click The real question is what do you want to manage the database or not and how much time you have.
- rattray 8y ago
  The author mentioned using https://www.pgbarman.org https://www.pgbarman.org for this, with a freelance DBA. Does anyone else have experience between the two options?
  - jsiepkes 8y ago
    I can attest that pgbarman works great. cough I accidentally nuked a production server once. However thanks to barman the downtime was only tens of minutes. It works by making a full backup at fixed intervals (say weekly) and then "pretending" to be a replica which makes it receive all the changelogs in realtime. (Basically it receives and archives WAL's). This makes point in time recovery possible. Highly recommended.
  - mmckelvy 8y ago
    I've used Barman for physical backups (I used the streaming backup option). Works well, though it's a bit of a slog to get set up the first time (the docs can be confusing in some areas) but once it's up it's great.
- griffordson 8y ago
  Add replication across regions into the mix and things swing even further in favor of AWS. Dealing with cross datacenter replication of MySQL was eating our lunch and preventing us from getting anything else done before we moved to an early version of RDS.
LaserToy 8y ago
Very interesting design. It looks like they are capturing events, and trying to predict things. I wonder why they went with Uber database and didn’t use cheaper S3 got storage, some processing for prediction and something simple and cheap for serving.
hstaab 8y ago
GCP beats out AWS by a factor of 2? Interesting
ziont 8y ago
1.2b/s try that its far moerchallenging
- emddudley 8y ago
  What kind of database are you writing 1+ GB/s to...
  - ziont 8y ago
    i mean 1.2 billion records per second. not month. now that was a tough problem back when I was sane.
- reallydude 8y ago
  thats the kind of problem i work on (millions per sec at peak). everything is a queue and we stream from servers via kafka and into processors into redshift, more or less. Its free once it's moving around in AWS, but redshift and processors get expensive and im interested in reliable ad hoc solutions... but havent seen it.
  - scapecast 8y ago
    can you elaborate on the process of copying data into Redshift? How often do you run COPY commands? On a continuous basis? Asking since Redshift as an OLAP isn't really optimized for heavy writes, so if you've figured out a way to do it, please bottle that magic!
Rafuino 8y ago
You ended up co-locating your own hardware, saying "We have a lot better hardware than any of the cloud service providers could offer." Can you clarify then what kind of hardware you're using? All SSDs, HDDs, how much capacity, are you going for all in-memory transactions, etc., etc.?
garyclarke27 8y ago
Thanks for writeup. Yes PostgreSQL is an incredible database except for it’s Mat Views, IMO this is their biggest weakness vs Oracle, SQL Server mat views are also poor with umpteen restrictions. With PG any view can be a Mat View, problwm is they don’t have incremental refresh or auto refresh. So not v useful, I roll my own, using custom tables and transaction id’s, not too difficult since all are auto generated but would be nice if Postgres added incremental refresh. I think this their most urgent missing capability that should be top of the list for v12.
deleted 8y ago
[deleted]
agnivade 8y ago
For the large tables, what about using timestamp based table partitioning to split data into smaller units ? And then materialize each of them one by one ? So you get to keep materialized data and a single logical table.

259 points by willvarfar 8y ago

ggm 8y ago

I will never complain about my minor pg issues again. The beast that is running here says whatever else, my problems are small. I'm interested in learning more about sharded design, time series and json data if anyone else reading this or the OP wants to write more.

elken 8y ago

Very interesting read. I'm not convinced that Postgres should be used for every aspect of a data processing pipeline yet but the author has certainly proved it is possible. You also seem to get a lot of features for free that people using Kafka can only dream of. It will be interesting to see how people will take advantage of new Postgres features in the future.

gomezjdaniel 8y ago

Thanks for the read!, a couple of questions: > Now we are renting our own hardware and maintain the database. We have a lot better hardware than any of the cloud service providers could offer Which provider are you using? > That 30% we can use to hire a freelance DBA to check in on the servers once a day. Where did you find this freelancer?

emddudley 8y ago
> hire a freelance DBA to check in on the servers once a day Not sure whether you were just interested in the author's specific freelancer but-- The term for this is "Remote DBA" and there are lots of them. I suppose the difficult part is confirming trustworthiness and skills before giving them direct access to your database.

willvarfar 8y ago

And I have built a very similar, perhaps even slightly larger, system, only in MySQL with TokuDB :) These free old-fashioned RDBMSs really can go fairly big and stay really fast; much bigger and faster than the noSQL stuff I also use. One thing that pushes people towards noSQL is that it is easier to get started; but I think that I've saved buckets of time in the long run by going with a conventional relational database. ymmv but its always worth thinking 'is this problem too big for a classic free RDBMS?' and the answer is almost always 'hell, no!'

sandGorgon 8y ago

> The primary reason we migrated away from Google SQL for PostgreSQL is because we discovered a bug that was causing data corruption. This was a known bug that is fixed in newer PostgreSQL versions. However, Google SQL for PostgreSQL is several versions behind. The lack of response from the support acknowledging the issue was a big enough red-flag to move on. I am glad we did move on, because it has been 8 months since we have raised the issue, and the version of PostgreSQL has not been updated to-date We have had similar experiences on various DB related issues. AWS are miles ahead on anything to do around storage and databases.

gralx 8y ago
In PostgreSQL performance, Google Cloud Platform "beats AWS by a factor of 2 in all [Aiven.io] tests".
- shoo 8y ago
  > A program that produces incorrect results twice as fast is infinitely slower. — John Osterhout
  - oskari 8y ago
    Not sure if this comment was about the data corruption bug in Cloud SQL's PostgreSQL version, but the benchmarks we ran at https://aiven.io/blog/postgresql-cloud-performance/ https://aiven.io/blog/postgresql-cloud-performance/ used Aiven's PostgreSQL service on top of VMs from the cloud infrastructure providers and weren't affected by Cloud SQL issues. The same Linux, PostgreSQL, etc versions were used on all platforms. We didn't use RDS, CloudSQL or other managed services from the infrastructure providers, but the PDF version of the presentation does include comparison of our managed PostgreSQL service with RDS PG and Aurora PG if you're interested.
  - gralx 8y ago
    The article doesn't say the GCP results were incorrect. What's your source?

datenhorst 8y ago

>Column order matters. We have tables with 60+ columns. Ordering columns to avoid padding saved 20%+ storage I need to look into this

jihadjihad 8y ago
This was an informative read: https://blog.2ndquadrant.com/on-rocks-and-sand/ https://blog.2ndquadrant.com/on-rocks-and-sand/

yeahitslikethat 8y ago

The article confirms the fact the best cost to performance ratio is colocated hardware you build. Next is managed server. Last: any of the cloud database providers. The key piece is knowing what you are doing. The clouds don't care about your tiny problems. They won't fix things for you. You must fix things for you.

Heliosmaster 8y ago
Exactly my experience. And not only about Databases. At a previous job I was able to cut our infrastructure expenses by a lot (70%) by essentially have our infra being 1 load-balancer, 2 beefy application servers and 1 (+1 failover) PostgreSQL database. Each of the application server costs 300~ Euros p/m , 24 cores and 256GB of RAM. I have not experienced the same cost to perf ratio anywhere else.
- griffordson 8y ago
  What was your salary, and the salary of additional people to increase the bus factor to an acceptable level? There is a lot more than raw perf numbers that go into these calculations for any responsible business owner.
  - toomuchtodo 8y ago
    This misnomer is frequently brought up, but untrue. Whether you’re on prem or cloud, the skills are transferable and staff needed regardless unless you’re a dev who just knows API calls for cloud services (my condolences). Cloud doesn’t mean you don’t need to know the infrastructure, it’s workload management. You still need to know how the underlying fundamentals operate, or you get burned (either through excessive cost, unexpected performance degradation, or data loss). Disclaimer: 18 years in different ops roles
    - apple4ever 8y ago
      Absolutely. Its not like you will have one less person in the cloud. You'll still need the same amount of people (maybe more!), but they'll just be doing different things.

ngrilly 8y ago

Great example of a job queue correctly implemented inside a transactional relational database (with SKIP LOCKED and a table dedicated to the job queue), instead of using a specialized tool.

ddorian43 8y ago
When you don't need insane scalability, having a transactional queue is a godsend.
- ngrilly 8y ago
  For the record, it's possible to have scalability and transactional enqueuing at the same time, with something like Google Cloud Datastore: https://cloud.google.com/appengine/docs/standard/python/datastore/transactions#transactional_task_enqueuing https://cloud.google.com/appengine/docs/standard/python/data...
  - ddorian43 8y ago
    Yeah, with sharding. And global transactions. Which are costly. As long as you pass the cost to the customer. But if you're doing a lot, then you can't use them.
shoo 8y ago
yes. the linked 2ndquadrant blog post explaining how to implement this using SKIP LOCKED is a good read: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/ https://blog.2ndquadrant.com/what-is-select-skip-locked-for-... i used this a few years ago to implement my own (toy-scale: 2 workers 1 db running on a potato-class thin client computer) idiosyncratic job queue in postgres. i have a number of recurring jobs that need to be run periodically, and the jobs are not independent -- different jobs depend upon shared resources -- so if lots of jobs that depend on resource R just ran, we might need to wait a while before the other jobs that also depend on R are deemed feasible to be scheduled and run. it was reasonably easy to cobble something together of the form: each worker process does the following: 0. within a transaction 1. select a shortlist of rows for jobs that are due and are currently feasible, subject constraints ; 2. attempt to acquire one of of those rows using "select ... for update skip locked limit 1" 3. do work, update job result and status in db, commit the transaction the only part of this that is slightly clever is that i don't actually have a hard requirement for mutual exclusion between worker processes to mediate access to shared resources, so that means each worker process can maintain its own state of its 'fair share' of resources that it is consuming, and throttle itself without needing to coordinate this with other workers.

hestefisk 8y ago

Very interesting read. Having worked myself on large Postgres databases, I would have loved to know about these tuning opportunities. It’s disappointing to see Google providing such poor support for Postgres though in Google Cloud. Even Microsoft provide first class support. They need to up their game.

threeseed 8y ago

> The takeaway here is that Google and Amazon prioritise their proprietary solutions (Google BigQuery, AWS Redshift) Except this is nonsense and there is no evidence to back it up. AWS Redshift is a replacement for an enterprise data warehouse. It is not suitable for a website, OLTP or for many analytics use cases and AWS has never claimed it to be as such. It's why they they offer a suite of different paradigm databases e.g. Aurora, DocumentDB, DynamoDB, Neptune and Redshift. Because no one database fits every use case. Also using a database as a job queue really is a strange thing to do when you are running on the cloud and there are dedicated services available specifically for this use case.

lr4444lr 8y ago
I don't think that's what he's implying. He means I think that the cloud providers are not very friendly to devs who will take the time to customize their RDBMS to squeeze more performance out instead of switching over to a more expensive solution capable of handling the kind of high volume OLAP he had to deal with.

manigandham 8y ago

What is Aiven supposed to do about a bug in Timescale that is still open? Aiven is great if you don't need the superuser access. They will run some bigger plans on local SSDs and allow you to create replicas and migrate across clouds and regions easily. It's a great way to run multi-cloud without shipping the data yourself. True that they could use more alerting features on resource usage. GCP managed services of open products are highly unrecommended for this exact reason of being very behind or even obsolete with very limited customization and control. Stick with BigTable, BigQuery, Dataflow and run the rest yourselfs on GCE/GKE.

akulkarni 8y ago
Just FYI - a fix for this bug was merged relatively quickly (within 4 days), and was released just this morning. https://github.com/timescale/timescaledb/issues/468#issuecomment-457654923 https://github.com/timescale/timescaledb/issues/468#issuecom...
- manigandham 8y ago
  Congrats on 1.2, the analytical functions are a good addition.
  - akulkarni 8y ago
    Thank you :)
oskari 8y ago
Unfortunately we were not able to offer a concrete workaround in this case, but as mentioned to the author of the post, we were and are in contact with our friends at Timescale to resolve this, and an update with the fix will be available in Aiven shortly. We are not the upstream for any of the engines running in Aiven, so we work with the upstream developers and communities to resolve any issues found. Often our involvement in fixing the issues is through providing logs, stack traces and other debugging information to the original authors of the code, but in some cases we also fix the issues ourselves and always contribute such fixes back upstream, see e.g. this commit for a PostgreSQL bug that was found by our customers and fixed by us: https://github.com/postgres/postgres/commit/07ef035129586ca26a713c4cd15e550dfe35e643 https://github.com/postgres/postgres/commit/07ef035129586ca2...

patwolf 8y ago

> The primary reason we migrated away from Google SQL for PostgreSQL is because we discovered a bug that was causing data corruption Is anyone familiar with the details of the data corruption bug? I'm using Cloud SQL and would like to understand the risk.

bluecheese 8y ago
My Cloud SQL instance is currently on 9.6.6, I just got an email from GCP saying they are upgrading to 9.6.11 next week. There is a data corruption bug mentioned in the 9.6.7 release notes, not sure if it's the one the author is referring to. https://www.postgresql.org/docs/9.6/release-9-6-7.html https://www.postgresql.org/docs/9.6/release-9-6-7.html "Fix vacuuming of tuples that were updated while key-share locked (Andres Freund, Álvaro Herrera) In some cases VACUUM would fail to remove such tuples even though they are now dead, leading to assorted data corruption scenarios."

eatonphil 8y ago

I'm confused why hosting PostgreSQL themselves on GCP or AWS was not considered an option?

shoo 8y ago
my understanding was that they were trying to outsource as much of the ops & infrastructure side of things as possible. hence trying a third service provider after GCP & AWS before giving up and doing it themselves.
- eatonphil 8y ago
  I got that. :) But they went from managed-db, managed-host to unmanaged-db, unmanaged-host when the clear middle ground is unmanaged-db, managed host. Maybe I missed it.
  - gajus 8y ago
    Hi, author here scanning through the comments. There are quite a few things that I had to leave out as the post was getting really long. I am writing a follow up article now titled "How Kubernetes and twelve-factor methodology saved us USD 300k in 24 months" where I am going to touch on this. The short version is that we never intended to stay with a cloud provider due to the costs of operating a large cluster (we continue to use GCP for critical components of our infrastructure, such as monitoring and alerting). It was convenient while we could afford it thanks to their startup programs, but migrating our data aggregation workers from cloud providers to dedicated servers cut costs by 40% (which adds up to a lot, as we are using ±0.5k vCPUs and _a lot_ of RAM).
    - justinclift 8y ago
      > Hi, author here scanning through the comments. Small note, there's a typo in the heading of "Google Cloud SQL for PosetgreSQL". ;) PosetgreSQL -> PostgreSQL

lettergram 8y ago

So my PostgreSQL deployment processes quite a bit more than 1.2bn records / month (closer to 10bn / month). What I have to say for AWS (which I use), the back up is why I use AWS. The ease of backing up, rolling back, and connecting additional services, and scaling make it killer. That being said, I’ve seen around 5x - 10x speed improvement for the same exact cost utilizing bare metal servers. I’ve been using online.net/scaleway and similar services. However the real value save is limited as you need to do a lot more work and often pay more for backing up. Overall, real savings might be 50% cost reduction, with some additional risk. Need more space? Launch a new server, copy, check, switch over, pray. With AWS it’s one click The real question is what do you want to manage the database or not and how much time you have.

rattray 8y ago
The author mentioned using https://www.pgbarman.org https://www.pgbarman.org for this, with a freelance DBA. Does anyone else have experience between the two options?
- jsiepkes 8y ago
  I can attest that pgbarman works great. cough I accidentally nuked a production server once. However thanks to barman the downtime was only tens of minutes. It works by making a full backup at fixed intervals (say weekly) and then "pretending" to be a replica which makes it receive all the changelogs in realtime. (Basically it receives and archives WAL's). This makes point in time recovery possible. Highly recommended.
- mmckelvy 8y ago
  I've used Barman for physical backups (I used the streaming backup option). Works well, though it's a bit of a slog to get set up the first time (the docs can be confusing in some areas) but once it's up it's great.
griffordson 8y ago
Add replication across regions into the mix and things swing even further in favor of AWS. Dealing with cross datacenter replication of MySQL was eating our lunch and preventing us from getting anything else done before we moved to an early version of RDS.

LaserToy 8y ago

Very interesting design. It looks like they are capturing events, and trying to predict things. I wonder why they went with Uber database and didn’t use cheaper S3 got storage, some processing for prediction and something simple and cheap for serving.

hstaab 8y ago

GCP beats out AWS by a factor of 2? Interesting

ziont 8y ago

1.2b/s try that its far moerchallenging

emddudley 8y ago
What kind of database are you writing 1+ GB/s to...
- ziont 8y ago
  i mean 1.2 billion records per second. not month. now that was a tough problem back when I was sane.
reallydude 8y ago
thats the kind of problem i work on (millions per sec at peak). everything is a queue and we stream from servers via kafka and into processors into redshift, more or less. Its free once it's moving around in AWS, but redshift and processors get expensive and im interested in reliable ad hoc solutions... but havent seen it.
- scapecast 8y ago
  can you elaborate on the process of copying data into Redshift? How often do you run COPY commands? On a continuous basis? Asking since Redshift as an OLAP isn't really optimized for heavy writes, so if you've figured out a way to do it, please bottle that magic!

Rafuino 8y ago

You ended up co-locating your own hardware, saying "We have a lot better hardware than any of the cloud service providers could offer." Can you clarify then what kind of hardware you're using? All SSDs, HDDs, how much capacity, are you going for all in-memory transactions, etc., etc.?

garyclarke27 8y ago

Thanks for writeup. Yes PostgreSQL is an incredible database except for it’s Mat Views, IMO this is their biggest weakness vs Oracle, SQL Server mat views are also poor with umpteen restrictions. With PG any view can be a Mat View, problwm is they don’t have incremental refresh or auto refresh. So not v useful, I roll my own, using custom tables and transaction id’s, not too difficult since all are auto generated but would be nice if Postgres added incremental refresh. I think this their most urgent missing capability that should be top of the list for v12.

deleted 8y ago

[deleted]

agnivade 8y ago

For the large tables, what about using timestamp based table partitioning to split data into smaller units ? And then materialize each of them one by one ? So you get to keep materialized data and a single logical table.