I'm managing a few databases for living, from pg dbs of few hundreds of GBs on-prem, up to few TBs on rds, and almost 1PB cluster of 20 warehouses on Snowflake. Which of those count? (doing data architect/eng for living)
I have more experience running large MySQL databases. Both at SurveyMonkey and Zapier our primary databases were MySQL and were massive as you can imagine from massively scaled consumer products. I won't list the data here since this is about postgres but wanted to provide the context.
The largest postgres I've personally administered is on RDS:
* postgres 13.4
* db.m6g.2xlarge (8vCPU, 32 GB RAM)
* 1.3tb storage
* Largest tables: 317GB, 144GB, and 142GB
I effectively treat this database as an analytics database and write massive queries with joins against those largest tables and postgres handles it just fine.
I have had random bad query plans when using CTEs that get cached and take down the entire DB for a bit but usually been able to fix it with `vacuum analyze` and the customizing the query to convince postgres to choose a better plan.
Overall I think postgres can do a lot of work with very little.
For me, your numbers underline the "premature optimization is the root of all evil" saying. Maybe misunderstanding the performance implications of different orders of mangitudes is similar to the lack of understanding in statistics. I often encounter colleagues or customers who worry about some data structure or table when there are 10s of thousands of entries which is actually not enough to spend that much time worrying about.
Out of curiosity, were those CTE issues on the current PG version (13.4)? I know they changed some materialization stuff with them in 12 or so but was wondering if that issue was from back then or if you're running into some stuff that currently still happens.
When you say 'analytics database', what kind of performance are you implying? Massive queries that respond in 10min? How tuned were things for the queries you were running?
I'm currently working through an analytics architecture and I'm having to defend against "why aren't you using postgres" when I'm talking about olap dbs.
I have a +1tb Postgres database at home that has market data in it. Moving it over to another Postgres db that has timeseries extensions to compact and increase performance. Also thinking about just partitioning everything up into parquet files. Query performance isn’t the best and am currently trying to improve it.
I know we're talking about postgres but have you looked at clickhouse? it seems pretty promising with big data (and also timeseries data with its log engines)
Check out Hydra for Postgres, its an extension to create columnar tables for data warehouse type of analytics. Disclaimer: I’m associated with them as they sell managed services, but the extension is open source.
Yes, but a usual pattern for smaller DBs is, for example, to run a full backup weekly and then daily incremental backups. Also, SQL dumps makes easier to do partial recovery, like for a single table.
On this size class (~1TB) I see PITR backups using something like Barman as the only viable alternative. (Of course you can also copy the whole VM/Disk etc)
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.
17 comments
[ 3.2 ms ] story [ 51.9 ms ] threadThe largest postgres I've personally administered is on RDS:
I effectively treat this database as an analytics database and write massive queries with joins against those largest tables and postgres handles it just fine.I have had random bad query plans when using CTEs that get cached and take down the entire DB for a bit but usually been able to fix it with `vacuum analyze` and the customizing the query to convince postgres to choose a better plan.
Overall I think postgres can do a lot of work with very little.
I'm currently working through an analytics architecture and I'm having to defend against "why aren't you using postgres" when I'm talking about olap dbs.
I said "postgres" because I didn't want to say "SQL" and include crazy systems with SQL query interfaces.
On this size class (~1TB) I see PITR backups using something like Barman as the only viable alternative. (Of course you can also copy the whole VM/Disk etc)
And that was in _2018_. I wonder how big it is now.
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.