A few years ago, I used to run and maintain a block explorer that indexed Bitcoin mainnet, in the end, this turned out to be a ~2 TB database which was too expensive to host on any managed database, hence, I ended up running it on the same server that exposed the API.
I remember when I had to run this for the first time, I faced a few challenges, for example:
- I usually don't care about optimizing the db schema because postgres can handle most projects without much effort, this wasn't the case anymore, there were indexes that I had to drop because these were causing inserts to become considerably slower and they took a few GB to store them.
- Column types started to matter, I had some columns that were stored as hex-strings but I ended up switching to BYTEA to save some space.
- The reads were slow until I updated the postgres settings, postgres default settings are very conservative and while they can work for many projects, those won't work when you have TB of data.
- While this isn't database specific, offset-pagination does not work anymore and I switched all of these queries to scroll-based pagination.
- Applying database migrations isn't trivial anymore because the some operations could lock the database for hours, for example, updating a column type from TEXT to BYTEA isn't an option if you want to avoid many hours of downtime, instead, you have to create a new column and migrate the rows in the background, once the migration is ready, drop the old column.
Overall, it was a fun journey that required many tries to get a decent performance. There are some other details to consider but it's been a few years since I did this.