Postgres improvements

[dchoi27]

We have Eric from Trigram working on our current postgres setup including schema, config, migrations.

Tasks:

• Repo setup
• Baseline metrics (heroku dashboard)
• Baseline benchmarks with local setup mirroring heroku
  • POST /upload, POST /pins, GET /check
• Test out table partition to enable better scaling
• Analise postgres config
• Analise current postgres indexes
• Cloudflare sync for the new db connectors
• Setup https://pganalyze.com/ for our dbs
• Add migration system
• Split out read to read replica

[hugomrdias]

@yocontra can you please review the list above and give some feedback if it makes sense ?

feel free to add remove etc

[yocontra]

Here's my current TODO list for my first PR of DB changes:

• Basic analysis of production index usage, heroku stats to figure out where out issues are

• Create partition schemas for pin, upload, and content tables

  • Ended up going with hash partitioning into 100 tables by CID - this should speed up reads/writes by isolating IO into smaller tables with ~20K rows. You will be able to grow these tables up to 2M rows before you need to revisit and consider more partitions, so this should be acceptable for a long while.

• Optimize the create_upload function

• Optimize basic DB configuration flags (specifically when autovacuum should run, table statistics, etc. to keep indexes cleaner than they are now since this is write-heavy workload)

• Write migrations for production/staging to move data from the existing single tables into the new partitioned model

• Create benchmark system in-repo and create a report comparing before vs. after the changes

TODO for future PRs:

• Postgres index analysis + make changes based on unused indexes
• Move all read queries to the read replica
• Use a connection pooler like pgbouncer or https://github.com/yandex/odyssey
• Set up pganalyze (or pgbadger) to get better statistics on the production DB
• Set up a migration system for production and staging DB changes
  • sqitch suggested by @hugomrdias
  • Also looking at liquibase, and flyway
• New cloudflare worker DB connection work if needed (factor out PostgREST #829)
• Facilitate switching DB providers if needed

[yocontra]

One thing that surfaced is PostgREST isn't able to deal with partitioned tables correctly - after implementing partitioning the query generator blows up (ends up doing a no-op and running no query, with no error reported) doing a basic relational query on the table after the create_upload function succeeds. Issue was already reported and the PR to fix it was merged over the weekend, so I'll go ahead and update to latest and see if it resolves the query generator issue.

UPDATE: Need to wait for a new PostgREST release - fix is not available in any docker images yet, and I don't want to roll our own custom image by pulling binaries out of their GitHub Actions which seems hacky.

[yocontra]

Latest PostgREST (9.0.0) fixes the partition issue so unblocked there. Tests are all passing fine with the new partition changes.

On the DB configuration front, Heroku only lets you set basic flags so most of that will be skipped except what can be tuned in the database itself. If you want to tune the database more (and generally have more control, make it cheaper) I would recommend using a service like Aiven for managed PG. If we were able to set flags, this is what the configuration would mostly look like:

[JeffLowe]

Closing this as complete for nft.storage. Optimizations are live.
The new Uploads V2 architecture is going to remove the bottleneck on the db so most of the remaining optimizations of the current architecture are not necessary.
Remaining step on this is to port changes over to web3 (captured here web3-storage/web3.storage#615