3 ms·

"Use PostGres until you have an engineering - data driven rationale not to" is my standard answer for non-blob data storage when a project starts. Why? because

"Use PostGres until you have an engineering - data driven rationale not to" is my standard answer for non-blob data storage when a project starts. Why? because when `n` is small (tables, rows, connections), postgres works well enough, and if `n` should ever become large, we'll have interest in funding the work to do a migration, if that's appropriate - and we'll be able to evaluate the system at scale with real data.

jmalicki 6y ago
postgres works great for `n` in ranges in excess of (10k, 10B, 1k) at least, which not many would consider small. The only real justification I've heard is postgres' story on replication still lags mysql, and the write amplification bit which they've done a lot of work on for pg13 (see [0]) Even for many very large companies I've seen, noSQL databases are mostly used as caches that can afford to be delayed or a bit out of sync, not as the database of record. [0] https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/
- pnathan 6y ago
  I mean, you can go a loooong way on Postgres. Things that probably are going to start causing issues: - the story of replication - ultra-high volume write contention - high volume timeseries data streams For #3 there, I was consulting on a project a few years ago where there was about 1 gigabyte of timeseries data in Postgres/RDS. Some ad hoc queries were really running into slowness - COTS RDS isn't great at speed. I want to say 1 billion rows, but that seems a off. Anyway, we were talking data sharding, etc. Didn't go anywhere, because the project was canceled, etc. But we could have done a nice job if we wanted to. Point being, there are design limits on postgres, particularly regarding distributed systems. Which is fine. I just have this allergy to designing for scale on Very Small Project. Also worth noting that the anti-monolith dogma plays out here, a few well built monoliths probably are better for a SQL database than "one bajillion microservices".
  - jmalicki 6y ago
    I'm totally with you that those three are reasonable limits (hell, I named two in my rebuttal)... Just that scale in itself is not the issue, it has highly specific issues. And for things like high update time series databases, those in my experience are usually metrics databases that say Prometheus serves well, and pg extensions like timescaledb do a stellar job for when you truly need ACID semantics.
  - jmalicki 6y ago
    Also most webscale use of nosql databases I've seen basically boils down to "eventually consistent materialized views"

jmalicki 6y ago

postgres works great for `n` in ranges in excess of (10k, 10B, 1k) at least, which not many would consider small. The only real justification I've heard is postgres' story on replication still lags mysql, and the write amplification bit which they've done a lot of work on for pg13 (see [0]) Even for many very large companies I've seen, noSQL databases are mostly used as caches that can afford to be delayed or a bit out of sync, not as the database of record. [0] https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/

pnathan 6y ago
I mean, you can go a loooong way on Postgres. Things that probably are going to start causing issues: - the story of replication - ultra-high volume write contention - high volume timeseries data streams For #3 there, I was consulting on a project a few years ago where there was about 1 gigabyte of timeseries data in Postgres/RDS. Some ad hoc queries were really running into slowness - COTS RDS isn't great at speed. I want to say 1 billion rows, but that seems a off. Anyway, we were talking data sharding, etc. Didn't go anywhere, because the project was canceled, etc. But we could have done a nice job if we wanted to. Point being, there are design limits on postgres, particularly regarding distributed systems. Which is fine. I just have this allergy to designing for scale on Very Small Project. Also worth noting that the anti-monolith dogma plays out here, a few well built monoliths probably are better for a SQL database than "one bajillion microservices".
- jmalicki 6y ago
  I'm totally with you that those three are reasonable limits (hell, I named two in my rebuttal)... Just that scale in itself is not the issue, it has highly specific issues. And for things like high update time series databases, those in my experience are usually metrics databases that say Prometheus serves well, and pg extensions like timescaledb do a stellar job for when you truly need ACID semantics.
- jmalicki 6y ago
  Also most webscale use of nosql databases I've seen basically boils down to "eventually consistent materialized views"