Heroku database at capacity

[wgwz]

The production indexer database is at capacity, we're on standard-2: https://devcenter.heroku.com/articles/heroku-postgres-plans#standard-tier

Standard-2 price is $200/month for 256 GB max storage: https://elements.heroku.com/addons/heroku-postgresql#standard-2

The next bump in the standard plan is standard-3, $400/month for 512 GB max storage: https://elements.heroku.com/addons/heroku-postgresql#standard-3

This task is to research alternative options that may be cheaper as the database grows. One discussed previously is AWS RDS, but there are other options as well. I.e. https://www.crunchydata.com/products/crunchy-bridge (among many others) crunchydata makes db management easier than aws, but still offers a competitive price point.

We need to decide where the database goes, how we migrate the database. I.e. do we manually stop the indexer process, and do a manual migration to the new db using pgdump and pgrestore? and restart the indexer pointed at the new database once it's complete?

Alternatively, this task could choose the cost, and just bump to the $400/month plan. Alternatively or additionally, we could look to filter out some data the indexer is capturing. I.e. there's some kind of network health check ping that we are storing, and there's a lot of that and i doubt we care about indexing those.

[wgwz]

Also relevant to this and why it's important: #2046

[aaronc]

What would be the price with crunchy data @wgwz ?

[wgwz]

For 512GB (double our current), and similar CPU/mem characteristics to our current heroku database, $191/month

https://www.crunchydata.com/pricing/calculator?provider=aws&region=us-east-1&tier=standard&plan=standard-8&storage=512

[aaronc]

How complex would a migration be?

[wgwz]

Crunchydata has docs for migrating to them from heroku postgres: https://docs.crunchybridge.com/how-to/heroku_dump_restore

There's the pg_dump/pg_restore migration method. Crunchydata mentions though that for larger databases there is a better method.

Either way, there would need to be downtime for the indexer itself, probably several hours. I guess we could still allow regen-server to have it's usual access, since it's read only. So most parts of the marketplace app would still be functional, aside from stale data during the downtime.

It's potentially a bit tricky but it doesn't look too bad.

[wgwz]

TODO: confirm that crunchydata has backups available

[blushi]

Hey team! Please add your planning poker estimate with Zenhub @blushi @wgwz

[wgwz]

I spoke with the crunchydata customer service team today and they answered some of our questions:

• backups

  • they showed me the feature and how it works, we get daily backups for free, for up to 10-days
  • there's a way you can create your own manual backups
  • you can restore from your backups

• what are the migration steps for the larger databases?

  • this doc was shared: https://crunchydata.notion.site/Setup-Replica-from-Heroku-Customer-Overview-96fcac45388a45cca693186377f35233
  • migration steps for smaller db's here (main difference is speed):
    • https://docs.crunchybridge.com/how-to/heroku_dump_restore#steps-to-migrate-via-logical-backup

I also asked them these two questions, which they'll get back to me on:

• for both migration methods, rough estimates of how long it takes for 256gb?
• for both migration methods, can we leave the database up and running for reads, while the migration is taking place? (up to the point of cutting over)

i also got a brief tour of the UI and it has some nice features.

• notes on changing the size of a crunchydata DB in the future:
  • they have maintance windows for databases.
  • suppose we get to a point where we want to increase the size of the DB again.
  • we basically just use a slider to select the increase we want, and then it's taken care of in the next maintenance window
  • BUT there is also an auto-resize feature: https://docs.crunchybridge.com/concepts/autoresize

[wgwz]

I got replies from the cruncydata team on these items:

for both migration methods, rough estimates of how long it takes for 256gb?

Standard caveats apply for replica-method. It could be 5 minutes if they don't have any indexes that need rebuilding. pg_dump is variable due to network consideration so it's hard to estimate but as a total guess I would have them plan for at least an hour, more or (maybe) less depending on the type of instance they select on Crunchy

for both migration methods, can we leave the database up and running for reads, while the migration is taking place? (up to the point of cutting over)

Yes, but that's entirely on the customer. If they have writes sneaking in they will be lost.

[wgwz]

Upon deployment for us we need to update:

• DATABASE_URL in the indexer
• INDEXER_DATABASE_URL in regen-server

[wgwz]

@blushi i forgot to add a vote yesterday, i added my vote but it's a bit of cheat so it's already done now :-)