There are a few tricks that make it run well (PostgreSQL compiled with a non-standard block size, ZFS, careful VACUUM planning). But nothing too out of the ordinary.
ATM, I insert about 150,000 rows a second, run 40,000 transactions a second, and read 4 million rows a second.
Isn't "Postgres does not scale" a strawman?
Well, that’s pretty much what I am doing.
Do you think this could become less important for your use case the new PG17 "I/O combining" stuff?
https://medium.com/@hnasr/combining-i-os-in-postgresql-17-39...
Can it be performant in high load situations? Certainly. Can is elastically scale up and down based on demand? As far as I'm aware it cannot.
What I'm most interested in is how operations are handled. For example, if it's deployed in a cloud environment and you need more CPU and/or memory, you have to eat the downtime to scale it up. What if it's deployed to bare metal and it cannot handle the increasing load anymore? How costly (in terms of both time and money) is it to migrate it to bigger hardware?
like you're more likely to encounter two phases (building the DB in heavy growth mode, and using the DB in light growth heavy read mode).
A business that doesn't quite yet know what size the DB needs to be has a frightening RDS bill incoming.
Being elastic is nice, but not always needed. In most cases of database usage, downsizing never happens, or expected to happen: logically, data are only added, and any packaging and archiving only exists to keep the size manageable.
By the way, really cool website.
> Damn, that’s a chonky database. Have you written anything about the setup? I’d love to know more— is it running on a single machine? How many reader and writer DBs? What does the replication look like? What are the machine specs? Is it self-hosted or on AWS?
It's self-hosted on bare metal, with standby replication, normal settings, nothing "weird" there.
6 NVMe drives in raidz-1, 1024GB of memory, a 96 core AMD EPYC cpu.
A single database with no partitioning (I avoid PostgreSQL partitioning as it complicates queries and weakens constraint enforcement, and IHMO is not providing much benefits outside of niche use-cases).
> By the way, really cool website.
Thank you!
I can build scalable data storage without a flexible scalable redundant resilient fault-tolerant available distributed containerized serverless microservice cloud-native managed k8-orchestrated virtualized load balanced auto-scaled multi-region pubsub event-based stateless quantum-ready vectorized private cloud center? I won't believe it.
Please do.
> It’s self-hosted on bare metal, with standby replication, normal settings, nothing “weird” there.
16TB without nothing weird is pretty impressive. Our devops team reached for Aurora way before that.
> 6 NVMe drives in raidz-1, 1024GB of memory, a 96-core AMD EPYC CPU.
Since you’re self hosted, I’m you aren’t on AWS. How much is this setup costing you now if you don’t mind sharing.
> A single database with no partitioning (I avoid PostgreSQL partitioning as it complicates queries and weakens constraint enforcement, and IMHO does not provide many benefits outside of niche use cases).
Beautiful!
About 28K euros of hardware per replica IIRC + colo costs.
Damn. I hope you make enough revenue to continue. This is pretty impressive.
https://calculator.aws/#/estimate?id=cfc9b9e8207961f777766e1...
Seems like it would be 160k USD a month.
I could not input my actual IO stats there, I was getting:
Baseline IO rate can't be more than 1000000000 per hour.
You can just… own servers. I have five in a rack in my house. I could pay a colo a relatively small fee per month for a higher guarantee of power and connectivity. This idea also scales.
Probably depends on the usage patterns too. Our developers commit atrocities in their 'microservices' (which are not micro, or services, but that's another discussion).
“Are you… are you storing images in BLOBS?”
“Yes. Is that bad?”
Did you use any particular guide for setting up replication? Also, how do you handle failover/fallback to/from standby please?
At least for pgbackrest, set up a spool directory which allows async wal push / fetch.
That's kind of where I'm at now... you can vertically scale a server so much now (compared to even a decade ago) that there's really no need to bring a lot of complexity in IMO for Databases. Simple read replicas or hot spare should be sufficient for the vast majority of use cases and the hardware is way cheaper than a few years ago, relatively speaking.
I spent a large part of the past decade and a half using and understanding all the no-sql options (including sharding with pg) and where they're better or not. At this point my advice is start with PG, grow that DB as far as real hardware will let you... if you grow to the point you need more, then you have the money to deal with your use case properly.
So few applications have the need for beyond a few million simultaneous users, and avoiding certain pitfalls, it's not that hard. Especially if you're flexible enough to leverage JSONB and a bit of denormalization for fewer joins, you'll go a very, very long way.
And you often don't really need to.
Just last week for some small application and checking the performance of some queries I add to get random data on a dev setup. Which is a dockerized postgres (with no tuning at all) in a VM on a basic windows laptop. I inserted enough data to represent what could maybe be there in 20 years (like some tables got half a billion rows, small internal app). Still no problem chugging along.
It is crazy when you compare what you can do with databases now on modern hardware with how other software do not feel as having benefited as much. Especially on the frontend side.
Only if my favorite websites in the late 90s was 15 seconds make because that's how long people would wait for a webpage to load at the time. Things have improved dramatically.
I'd like to see those accessible frontends. The majority is not usable keyboard-only.
Only tab and shift-tab. Arrow keys are a bust. And the only visible shortcut is ctrl-K for the search input and I think it's because it comes as an algolia default.
For something better I only have to watch around the page at the browser itself: underlined letters in the menu tells me what alt+letter will open said menu. Then I can navigate using arrow keys and most menu items are shown with a key combination shortcut.
One thing that was crazy was having to go through verification for blind usability, when the core function (validating scanned documents) requires a well sighted user.
I won't say MUI is perfect... it isn't... but you can definitely go a lot farther in a browser than you can with what's in the box with most ui component libraries is the only real point.
Question - what is your peak utilization % like? How close are you to saturating these boxes in terms of CPU etc?
> Your replies are really valuable and informative. Thank you so much.
Thank you!
Did you benchmark io rate with different ZFS layouts?
6 NVMe drives in mirrored pairs would probably be substantially higher latency and throughput
Though you'd probably need more pairs of drives to match your current storage size. Or get higher capacity NVMe drives. :)
But most of the time, an RDBMS is the right tool for the job anyway, you just have to deal with it.
Well, it’s only true for writes.
If you insert 150K rows per second, that’s roughly 13 Billion rows per day.
So you’re inserting 10%+ of your database size every day?
That seems weird to me. Are you pruning somewhere? If not, is your database less than a month old? I’m confused.
Why does it do that? I thought only revocations need to be published?
If you want to hide what subdomains you have you can use a wildcard certificate, though it can be a bit harder to set up.
[1]: https://developer.mozilla.org/en-US/docs/Web/Security/Certif...
Silly question but is this at the same time, regular daily numbers or is that what you've benchmarked?
Any tricks you used for those parts?
Zfs send / recv or replication.
> I used to run a bunch of Postgres nodes at a similar scale. The most painful parts (by far) were restoring to a new node and major version upgrades. Any tricks you used for those parts?
Replication makes this pretty painless :)
Basically, yes.
https://www.postgresql.org/docs/current/runtime-config-repli...
Thanks for the kind words!
I use Postgres with 32K BLKSZ.
I am actually using default 128K zfs recordsize, in a mixed workload, I found overall performance nicer than matching at 32K, and compression is way better.
> Thanks for sharing - and cool website!
Thank you!
That's amazing - I would love to know if you have done careful data modeling, indexing, etc that allows you to get to this and what kind of data is being insert ed?
The schema is nicely normalized.
I had troubles with hash indexes requiring hundreds of gigabytes of memory to rebuild.
Postgres B-Trees are painless and (very) fast.
Eg. querying one table by id (redacted):
EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE];
Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1)
Index Cond: (id = '[ID_VALUE]'::bigint)
Planning Time: 0.056 ms
Execution Time: 0.052 ms
# zpool iostat 1
operations bandwidth
read write read write
----- ----- ----- -----
148K 183K 1.23G 2.43G
151K 180K 1.25G 2.36G
151K 177K 1.25G 2.33G
148K 153K 1.23G 2.13GIf you’re doing insert only, you might benefit from copying directly to disk rather than going through the typical query interface.
I ran into
multixact "members" limit exceeded
- autovacuum_max_workers to my number of tables (Only do so if you have enough IO capacity and CPU...).
- autovacuum_naptime 10s
- autovacuum_vacuum_cost_delay 1ms
- autovacuum_vacuum_cost_limit 2000
You probably should read https://www.postgresql.org/docs/current/routine-vacuuming.ht... it's pretty well written and easy to parse!
I only “need” that because one of my table requires batch deletion, and I want to reclaim the space. I need to refactor that part. Otherwise nothing like that would be required.