Making Postgres scale
pgdog.dev
pgdog.dev
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?
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. :)
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...
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.
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.
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.
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.
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!
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.
Silly question but is this at the same time, regular daily numbers or is that what you've benchmarked?
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...
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...
Thanks for the kind words!
That being said, the setups I typically see don't even go that far. Most companies don't mitigate for the database going down in the first place. If the db goes down they just eat the downtime and fix it.
https://wiki.postgresql.org/images/2/28/Moskva_DB_Tools.v3.p...
https://s3.amazonaws.com/apsalar_docs/presentations/Apsalar_...
That presentation starts with hard violence.
Being 100% hard and fast on that rule seems like a bad idea though.
[0]: https://www.postgresql.org/docs/current/server-programming.h...
[0]: https://www.postgresql.org/docs/current/extend-pgxs.html
Sorry for snapping. I’m exhausted with devs complaining that some older and well-established piece of tech (Postgres, HAProxy, nginx to name a few) isn’t easy enough to use, and then using something demonstrably worse, or writing their own terrible version of it. Work trauma.
One of the benefits of stored procedures they don't mention is SECURITY DEFINER, which is like setuid.
You can for instance have a user table with login and hashed password, have a stored procedure that can verify login and password, without giving SELECT access to the user table to the database user your application use.
Stored procedures also block SQL injection attacks.
The data migration was a pain, but it was still less painful than manually sharding the data or dealing with 3rd party extensions. Since then, we’ve had a few hiccups with autogenerated migration scripts, but overall, the experience has been quite seamless. We weren’t using any advanced PostgreSQL features, so CockroachDB has worked well.
The site makes it seems as if I can install CockroachDB on Mac, Linux, or Windows and try it out for as long as I like. https://www.cockroachlabs.com/docs/v25.1/install-cockroachdb... Additionally, they claim CockroachDB Cloud is free for use "up to 10 GiB of storage and 50M RUs per organization per month".
Therare limitations in terms of licenses [0].
[0]: https://www.cockroachlabs.com/docs/v25.1/licensing-faqs
And those free-licenses have this dirty little clause that you are not entitled to a license, they need to APPROVE a free-license. Now that is even more scary.
They pull all this because people kept using the free-core version and people simply never upgrade/wanted more. That is why all these changed happened. Coincidentally, the buzz around CRDB has died down to the point that most talks about CRDB are these rare mentions here (even reddit is as good as dead). 98% of CRDB mentioning how great it is, is all origination from CRDBLabs. Do a google and limit in time range, and then go page by page, ... They are a enterprise only company at this point.
Take in account, that the constant CPU/Mem/Query monitoring that CRDB does, eats up around 20 a 30M RUs per month. There are some people that complained as to why there free instances lost so much capacity. And those RU are not 1:1, like, you do a insert, its 1RU, oooo, no ... Its like 9 to 12RU or something.
Its very easy to eat all those RUs on a simply website. Let alone something that needs to scale. Trust me, your better of self deploying but then you enjoy the issue of the new licenses / forced telemetric / forced phone home, or spend 125$+ / vcpu (good luck finding out the price, we only know these numbers from people breaking nda offers). They are very aggressive in sales.
Its not worth it to tie your company to a product, that can chance licenses on a whim, that charges Oracle prices (and uses the same tactics). I am very sure that some of their sales staff is ex-Oracle employees. ;)
It was a pain to get that work with Cockroach since it doesn’t optimize cross schema queries and suggests one DB per customer. This was a deal breaker for us and we had to duplicate data to avoid cross schema queries.
Being able to live within Postgres has its advantages.
Just beware that CockroachDB is not a drop-in replacement for PostgreSQL.
Last time I looked it was missing basic stuff. Like stored functions. I don't call stored functions an "advanced feature".
O, its way worse then that... A ton of small functionality tend to be missing or work differently. Sometimes even small stuff like column[1:2] does not even exist in CRDB and other times its things like ROW LEVEL SECURITY ... you know, something you may want on a mass distributed database then will be use for tenant setups (i hear they are finally going to implement it this year).
The main issue is the performance... You are spending close to ~4x the resources on a similar performing setup. And this is a 2x Pgres (sync) vs 3x CRDB (where every node is just a replica). Its not the replication itself but the massive overhead on the raft protocol + the way less optimized query planner.
To match Pgres, your required to deploy around 10 a 12 nodes, on hardware that needs about twice the resources. That is the point where CRDB performance on a similar level.
The issue is, well, you increase the resources to your Postgresql server and the gap is back. You can scale Postgresql to insane levels with 64, 96 CPUs ... Or imagine, you know, just planning your app in advance in such a way, that you spread your load over multiple Postgresql instances. Rocket science folks ;)
CRDB is really fun to watch, the build in GUI, the replication being very visual, but its a resource hog. The storage system (peble) eats a ton of resources to compact the data, when you can simply solve that with a PostgreSQL instance with zfs (with ironically, often better compression).
I do not joke when i say, that seeing those step cpu spikes in the night hours of the compacter working, is painful. Even a basic empty CRDB instance, just logging its own usage, runs between 7 to 50% on a quad ARM N1, constantly.
PostgreSQL? You do not even know its running. Barely any CPU usage, what memory usage?
And we have not talked license/payment issues ... Of "free enterprise" version with FORCED telemetric on, AND your not allowed to hide the server (aka, if it can not call home, it goes into restricted mode in 7 days, with like 50 queries / second ... aka, the same as just shutting down your DB ). By the way, some people reported that its 125+ dollar/vcore payment. Given that even the most basic CRDB gets you 3x instances, with minimum 4 cores ... Do the math. Yea, after 10M income but they chance their licenses every few years, so who knows next year or the year after that.
Interesting product, shitty sales company behind it. I am more interested to see when the postgresql storage extension orioledb comes out, so it solve the main issue that prevents postgresql scaling even more, namely the write/vacuum issue. And ofcourse a better solution to upgrade postgresql versions, there CRDB is leaps and bound better.
Another (battle tested * ) solution is to deploy the (open source) Postgres distribution created by Citus (subsidiary of Microsoft) on nodes running on Ubuntu, Debian or Red Hat and you are pretty much done: https://www.citusdata.com/product/community
Slap good old trusty PgBounce in front of it if you want/need (and you probably do) connection pooling: https://www.citusdata.com/blog/2017/05/10/scaling-connection...
*) Citus was purchased by Microsoft more or less solely to provide easy scale out on Azure through Cosmos DB for PostgreSQL
https://www.citusdata.com/blog/2015/07/15/scaling-postgres-r... (note that this is a old blog post -- pg_shard has been succeeded by citus, but the architecture diagram still applies)
And me saying "Apparently" because I have no experience dealing with large databases on AWS.
Personally had no issues with Citus too, both on bare metal/VMs and as SaaS on Azure...
As with partitioning, in my experience something like a common key (identifying data sets), tenant id and/or partial date (yyyy-mm) work pretty great
For other use cases, there can be big gains from cross-shard queries that you can't really match with partitioning, but that's super use case dependent and not a guaranteed result.
Currently, I’m using Postgres FDWs to import the tables from those databases. I then create views that UNION ALL the relevant tables, adding a column to indicate the source database for each row.
This works, but I’m wondering if there’s a better way — ideally something that can query multiple databases in parallel and merge the results with a source database column included.
Would tools like pgdog, pgcat, pganimal be a good fit for this? I’m open to suggestions for more efficient approaches.
Thanks!
Postgres is a fantastic workhorse, but it was also released in the late 80s. Who, who among you will create the database of the future... And not lock it behind bizarro licenses which force me to use telemetry.
Are there specific things you’d want from a modern database?
While they come up with some other tricks here, that's ultimately what's scaling postgres means.
If I imagine a better database, it would have native support for scaling, a postgres compatible data layer as well as first party support for NoSQL( JSONB columns don't cut it since if you have simultaneous writes unpredictable behavior tends to occur).
It needs to also have a permissible license
I've never personally encountered this, but I've seen other HN contributors mention it. https://news.ycombinator.com/item?id=43189535
From what I can tell, unlike mongo, some postgres queries will try to update the entire JSONB data object vs a single field. This can lead to race conditions.
Compared to Postgres, Oracle DB:
• Scales horizontally with full SQL and transactional consistency. That means both write and read masters, not replicas - you can use database nodes with storage smaller than your database, or with no storage, and they are fully ACID.
• Has full transactional MQ support, along with many other features.
• Can scale elastically.
• Doesn't require vacuuming or have problems with XID wraparound. These are all Postgresisms that don't affect Oracle due to its better MVCC engine design.
• Has first party support for NoSQL that resolves your concern (see SODA and JSON duality views).
I should note that I have a COI because I work part time at Oracle Labs (and this post is my own yadda yadda), but you're asking why does no such database exist and whether anyone can make one. The database you're asking for not only exists but is one of the world's most popular databases. It's also actually quite cheap thanks to the elastic scaling. Spec out a managed Oracle DB in Oracle's cloud using the default elastic scaling option and you'll find it's cheaper than an Amazon Postgres RDS for similar max specs!
Can you get me some Oracle cloud credits ?
First positive thing I’ve ever heard about an oracle project !
https://docs.oracle.com/en/cloud/paas/autonomous-database/se...
There's also some sort of startup credits program with a brochure here, apparently you can just fill out a form and get some credits with an option to apply for more. But I don't know much about that. I've used the always-free programme for some personal stuff and it worked fine so I never needed to think about credits.
https://www.oracle.com/a/ocom/docs/free-cloud.pdf
I have to admit I'm not really familiar with Firebase, I thought that was some managed service for mobile apps, but Oracle DB comes with some stuff that sounds similar. And Oracle Cloud is an AWS-style cloud, it has a ton of high level services for things.
ORDS is a REST binding layer that lets you export tables, views, stored procedures and NoSQL JSON document stores over HTTP without writing a middleman server yourself. You can drive it directly from the browser. ORDS supports OAuth2 or can be integrated with custom auth schemes from what I understand. I've not used ORDS myself yet but probably will in the near future.
https://www.oracle.com/database/technologies/appdev/rest.htm...
Firebase IIRC when it first launched was known for push streaming of changes. Oracle DB lets you subscribe to the results of SQL queries and get push notifications when they change, either directly via driver callbacks or into a message queue for async processing later. It's pretty easy to hook such notifications up to web sockets or SSE or similar, in fact I've done that in my current project.
There's also a thing called APEX which is a bundled visual low-code app builder. I've never used it but I've used apps built with it, and it must be quite flexible as they all had a lot of features and looked very different. You can tell you're using an APEX app because they have a lot of colons in the URLs for some reason. Here's a random example of one from outside of Oracle that exports a database of dubious scientific research papers:
https://dbrech.irit.fr/pls/apex/f?p=9999:1::::::
I'm not holding it up as a great example, there are probably better examples out there, it's just a one that came to mind that's public and I used before.
I'm working on a fully open source game right now, and I can't ethically tell people to hook into a closed source service. But I must admit, having to use supabase instead of firebase has made this much harder than it needs to be.
supabase team here - can you share more about the challenges? We'd love your feedback so that we can fix any difficulties for the future
For example, Firebase doesn't care about captchas for anonymous authentication. Supabase explicitly warns you to enable this before allowing anonymous authentication.
The problem here is not every client is going to be able to use captchas. You can't just enable it for new signups, but disable it for signing in.
If user XYZ used a captcha to register, they probably aren't a bot when they sign in later. Say they sign in using a game engine client. Unless I want to open up a webview a captcha won't work.
Honestly I might not be using Supabase correctly. Basically I'm working on a small open source card game. I created a very similar game in Firebase previously. I used the database to manage state and then wrote some basic logic in Firebase functions.
I'm not sure exactly why, but this has been much harder in Supabase. I will admit my SQL isn't the best, so maybe I'm just used to NoSQL...
It’s a Postgres facade. But everything beyond that is a complete reimagining and a rewrite to scale independently.
I personally think it’s going to eat a lot of market share when it solves some remaining limitations.
1. count
2. max, min, sum
3. avg (needs a query rewrite to include count)
Eventually, we'll do all of these: https://www.postgresql.org/docs/current/functions-aggregate..... If you got a specific use case, reach out and we'll prioritize.
I think you probably need some documentation to the effect of the current state of affairs, as well as prescriptions as to how to work around it. _Most_ live workloads, even if the total dataset is huge, have a pretty small working set. So limiting DB operations to simple fetches and doing any complex operations in memory is viable, but should be prescribed as the solution or people will consider it's omission as a fault instead of a choice.
- Lev
I had it in the back of my mind for a while, nice to have it in code. Works pretty well, as long as columns in GROUP BY are present in the result set. Otherwise, we would need to rewrite the query to include them, and remove them once we're done.
> It’s funny to write this. The Internet contains at least 1 (or maybe 2) meaty blog posts about how this is done
It would’ve been great to link those here. I’m guessing one refers to StackOverflow which has/had one of the more famous examples of scaled Postgres.
1. Does the schema have an obvious column to use for distribution? You'll probably want to fit one of the 2 following cases, but these aren't exclusive:
1a. A use case where most traffic is scoped to a subset of data. (e.g. a multitenant system). This is the easiest use case- just make sure most of your queries contain the column (most likely tenant ID or equivalent), and partially denormalize to have it in tables where it's implicit to make your life easier. Do not use a timestamp.
1b. A rollup/analytics based use case that needs heavy parallelism (e.g. a large IoT system where you want to do analytics across a fleet). For this, you're looking for a column that has high cardinality witout too many major hot spots- in the IoT use case mentioned, this would probably be a device ID or similar
3. When do you want to migrate? Logical replication in should work these days (haven't tested myself), but the higher the update rate and larger the database, the more painful this gets. There's not a lot of tools that are very useful for the more difficult scenarios here, but the landscape has changed since I've last had to do this
4. Do you want to run this yourself? Azure does offer a managed service, and Crunchy offers Citus on any cloud, so you have options.
5. If you're running this yourself, how are you managing HA? pg_auto_failover has some Citus support, but can be a bit tricky to get started with.
I did get my Citus cluster over 1 PB at my previous job, and that's not the biggest out out there, so there's definitely room to scale, but the migration can be tricky.
Disclaimer: former Citus employee
There are definitely advantages of not running inside the system you wish to orchestrate.
Better keep up with the parser though!
If not is supabase the most painless way to get started?
If you're designing from scratch and make it worth with Citus then (specifically for a multi-tenant/SaaS sharded app) it can make scaling seem a bit magical.
That being said, this does seem to handle replicas better than Citus ever really did, and most of the features it's lacking aren't relevant for the sort of multitenant use case this blog is describing, so it's not a bad tradeoff. This also avoids the coordinator as a central point of failure for both outages and connection count limitations, but we never really saw those be a problem often in practice.
For everything else, and until we cover what's left, postgres_fdw can be a fallback. It actually works pretty well.
`id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY`
neat piece of tech! excited to try it out.
Postgres to pg-compatible DBs are never as smooth as they advertise it to be.
For a more scientific answer, there is this project: https://pgscorecard.com/
Note that Aurora scores 93% while Cockroach scores only 40%.
There isn't even pricing on it...