PgDog is funded and coming to a database near you | Hacker News Reader

PgDog is funded and coming to a database near you

pgdog.dev

548 pointslevkk261 comments

> The reason DBs like Mongo or Dynamo exist is because Postgres has a scaling problem.

I've used Postgres at a few places and the #1 problem was always high availability, not scaling. One Postgres cluster could easily handle 100000 transactions per minute, but when a primary node went down it was a page and manually failing over to the spare then manually replacing the spare. The manual tooling was very finicky but at least it worked, no automated solution came even close. Lack of a good HA story is why I avoid self-managed Postgres as much as possible.

Have you looked into things like CloudnativePG? https://cloudnative-pg.io/

CNPG is quite nice and robust but I'd still be a bit reluctant to stack PG on k8s for really big clusters just because k8s ecosystem moves quite quickly and there's lots of patching/maintenance/churn which means more PG failovers so depends on how well your workload handles that (they're normally only a few seconds)

Most K8s upgrades can happen independently of node reboots etc., you only need to update for OS updates really, but that would be true of anywhere you run PG, even RDS.

>but that would be true of anywhere you run PG, even RDS

It's a little easier to strip down userland if the machine is only running PG. Technically possible on k8s with distros like Talos, Bottlerocket, etc but you still have all the k8s deps on top of PG. It's also a little easier to do defense-in-depth on a dedicated PG machine which means you might have mitigating controls in place to skip security patches (minimal kernel modules, selinux)--possible on k8s but now you're fighting through a 2nd layer of configuration

RDS is a bit of a special case because you also have AWS curating and prioritizing updates. You can do that yourself but it's a bit of a time sink scrutinizing every upgrade to see if you _really_ need it. Our RDS instances tend to go 3+ months without restarts

Good thing we support HA as well: https://docs.pgdog.dev/features/load-balancer/

Load balancer with health checks and failover, works out of the box. :) Battle-tested at this point too, so could be worth a look.

That's great news! I'll bookmark this in case I'm forced to manage Postgres again.

What do you use instead?

I tend towards using key-value databases as I find them general purpose enough while being much more robust. I'm not married to any one in particular, depends on the requirements.

Is a load balancer HA?

Not by itself if it's naive, but if it's able to assess target health and avoid degraded instances then it becomes a component in HA, the other being integrating an orchestrator for gracious recovery.

from their docs:

> PgDog does not detect primary failure and will not call pg_promote(). It is expected that the databases are managed externally by another tool, like Patroni or AWS RDS, which handle replica promotion.

Why the snark comment? The PgDog project has been around for a while, it's not vibe coded.

okay, it does appear that the LLM didn't write any of this. i guess the simple answer is that it is not HA.

Not gp but I didn’t perceive any snark in the comment you are replying to

Combined with a replication strategy and automated health checks, a load balancer could direct traffic to a healthy instance automatically.

What happens when the load balancer fails?

HA has to be all the way through, in which case you might not need a load balancer because each client already connects to a separate server. If you do, then you can have one load balancer per client machine.

I've extensively used Dynamo (internally at Amazon and externally) and even founded a DB startup with it at it's core. Boiling down scalability of Postgres vs Dynamo as it's written in blog is a bit terse. Dynamo scales writes horizontally with the keyspace, forever. Postgres simply can't, and no number of layers between the machines and the developer changes that. Sharding, pooling, Citus are all layered on top of an engine where a given row's writes still land on one primary.

Dynamo is a fundamentally different DB to Postgres. If your problem fits into the dynamo approach (I'd argue that more problems do), then you should be using it. No all problems fit, though.

Agreed, my critique was about how the article frames scalability. I've yet to see an OLTP problem that can't live in something like Dynamo. KV can model anything if you put in the work, the question is how much modeling discipline you trade for the scale, and in my experience the up front work is always worth it. Most of the time operational issues are swept under the rug and not consider tech debt.

Take for example AuroraDB: the sheer engineering it took to make SQL do scalable OLTP at all tells you how much that flexibility actually costs to keep.

Upfront modeling work is always worth it, but that only holds if you actually know your access patterns upfront. Most teams don’t, especially early on.

Curious how the DB startup with Dynamo at its core went. We use it heavily. The primary tricky thing for us at the moment is aligning pricing with workload value.

We obsessed over optimizations and pushing the apis to the limits of how we could pack it.

So much so, we re-wrote the DynamoSDK to squeeze out more optimizations so we could be the same cost even though we were a layer in front of dynamo. We used key encoding and other various technique as well as managed capacity (on demand vs reserved) to transparently optimize workloads for price. In our experience we saw dramatic gains vs just vanilla SDK usage.

If you're curious, here was the marketing website, but we're now part of Databricks: https://stately.cloud/

Interesting! We interact with the low-level APIs too vs the SDK, also: an IO scheduler for request batching and conn management, request hedging, full MVCC transactions, etc. We store raw bytes in DDB and manage schema/etc elsewhere. Curious if there is other low-hanging fruit, or not so low, you found that we haven't discovered yet.

Except that dynamo is still just glorified mysql? https://news.ycombinator.com/item?id=18871661

I don’t think the backend matters. It’s the frontend wrapper that makes or breaks HA.

If Dynamo is glorified MySQL then Hacker News is also glorified MySQL. The system is the whole system, not just one part of it.

Dynamo DB isn’t even good at being a KV store. Almost every time we have to also back it with S3 because of size limitations.

did you use single table design?

and yeah you have to spend a lot of upfront time designing your data models

If you know your access patterns really well and they are non-relational, then you can design the best possible tables for dyanmoDB. In such a case DynamoDB works and scales amazingly. Ofc, you cannot do multi table relationships etc shoehorning a relational scheme onto DynamoDB does not work.

Patroni serves this niche pretty well at this point.

Patroni 1.0 was released in 2016, i.e ~10 years ago.

https://github.com/patroni/patroni

Yup Patroni handles automatic failures and cluster management quite well

Noted. If I ever have to administer a Postgres setup again I'll take a look. Thanks.

~1600 TPS is not 'high scale'.

Pretty good for 98% of projects though.

Have you tried cnpg? Worked amazingly well for my usecases

I have mentioned this before, but here it goes again:

I'm really happy that there's more options for Postgres sharding and I applaud Pgdog and the team's efforts and energy.

Having said that, this makes it a no-go for me:

> shard_number = hash(data) % num_shards

https://docs.pgdog.dev/features/sharding/basics/#terminology

Most sharding solutions distribute the hash value over linear ranges, that then split across "virtual shards", that are then placed on the physical shards or worker. This allows for shard replacement when needed. For example, Citus works this way, and even adds convenience functions for shard migration (using logical migration) in an automated way. That's all I'd need.

Operationally, it's worlds apart. With modulo distribution the only way to replace data is to reshard everything --something you don't want to do however fast the operation may be.

Yeah good callout. We'll add rendezvous soon enough. Until then, being compatible with Postgres partitions has been advantageous -- while we build everything out, people were able to migrate to PgDog for the query routing layer while doing the resharding in Postgres.

Adding a sharding function in our architecture is relatively straightforward. We also support plugins which can control the flow (and direction) for queries, so our users can add their own (and they do!).

TBH I don't think it's that straightforward, I see it more of a notable architectural change. At a very high level, this means:

* Adding a sharding function, as you say.

* Developing an external service for metadata (shard placement) or alternatively have that metadata in one place and replicate (consistently!) to every query router.

* Implementing functions/catalogs for the users to understand the placement and configure/alter it.

* Implementing shard migration / rebalancing capabilities, possibly using Postgres logical replication (plus notable automation).

Here's one idea if you follow this path, something that Citus doesn't have: make the sharding function pluggable and pick one by default which is well-known and available in many languages (e.g. xxhash). If you do so, and guarantee stability of those functions, they could be used externally (applications) to route queries / inserts especially to the appropriate shard. While it makes application more complex, it may allow (combined with access to the metadata service) for faster ingestion paths (this is often known as application assisted sharding), and its not exclusive of the query routers.

Edit: formatting

"Why Us" => "I ran Postgres at Instacart, where we scaled the company 5x in April of 2020. The biggest problem we had was making Postgres serve 100,000s of grocery delivery orders per minute"

Couldn't be a better why us :)

Legends

Is 100k order per minute a lot? Even a single Postgres instance should serve that fine?

One assumes they mean 100,000s (plural) concurrent users actively building carts

Is that still a lot? Feels like a single 64-core, 256GB RDS instance with some caching should handle that fine. RDS has instances up to 192-core and 768GB.

Keep in mind they’re doing real-time logistics and messaging, as well as type-ahead search and managing ads and promotions

I think the real-time logistics is likely the thing taxes a Postgres database.

Everything else seems normal DB CRUD that a single beefy instance with a few replicas should handle easily. Type ahead search is no doubt using a different service and not directly querying Postgres.

Average throughput is one thing, tail latency, quite another.

100k(s) orders per minute is several orders of magnitude more than realistic. Amazon does 20k orders per minute.

Instacart doesn't need "100,000s of grocery delivery orders per minute".

There must be some 0s added for the sake of the story.

According their 2026 Q1 filing they do about 90 million orders per quarter which is about 12 orders per second, 720 orders per minute.

It might make 100k row level changes per minute, but that’s a different metric.

https://www.sec.gov/Archives/edgar/data/1579091/000157909126...

Instacard have released a public dataset[1] on their orders, so it should be even easier to verify this claim. From what I could find in some analysis[2] of this dataset around 100k orders per day and not per minute seems accurate.

I assume they are referring to how many database requests they have due to customers orders or a similar metric and just worded it poorly.

[1] https://www.kaggle.com/datasets/psparks/instacart-market-bas... [2] https://rstudio-pubs-static.s3.amazonaws.com/284199_5c498037...

This data set was released years before the Covid hypergrowth phase which they are referring to.

That's fair as the Kaggle dataset[1] is from 2017. Even assuming orders scaled with revenue (which grew to $1.5B in 2020[2]), you'd only reach a few hundred orders/minute at the pandemic peak (which lines up with the calculation above via a different method).

So I still assume the original comment isn't referring to actual orders placed.

[1] https://www.kaggle.com/datasets/psparks/instacart-market-bas... [2] https://fortune.com/2022/05/18/what-to-know-instacart-ipo/

it could be peak orders per second

I'd wager on this.

Going from 720 average to 100,000s peak still doesn't sound realistic. especially as they operate in many timezones.

And just like that you’ve done more due diligence than the VCs who just threw money at this.

Nope. Completely flawed logic that assumes equal distribution. Dunning Kruger

I thought not assuming uniform distribution was table stakes for senior engineers.

I can't say what the curve looks like, but 100,000 orders per second would consume reach official quarterly count in 15 minutes.

Since that's unlikely, this at least gives us some degree of bounds to guess what the curve looks like.

i think this assumes that those orders are distributed evenly over time

Amazon does 20k peak, or 20k average? Website visitor peaks could easily be two orders of magnitude higher traffic than average for a few minutes.

I worked at a company that had billions of views per year on a single big Postgres instance. Extremely read heavy with many queries needed for a page load. You can cache a lot of things.

Yes, but that's not a shopping cart, or a checkout workflow, nor a web store with heavy analytics.

It was one of the top real estate portals in the world. A lot of geolocation searches. New search every time someone moves the map. A ton of data sent to the client. Analytics in every page view.

No clue how a shopping cart or checkout flow would drastically increase database load. It should just be basic CRUD. Building a shopping cart is something every student makes. Pages in a web store can be cached relatively easily since items won't change often.

A primary DB with a few replicas and caching can go a really long way.

The composition of the average transaction will be different in a shopping cart (lots of writes and updates) compared to your use case which sounds like it skewed read heavy. With Postgres it’s generally easier to scale reads because it doesn’t really matter which replica the query hits, as long as it contains the data it needs. Whereas write-heavy workloads route through a single-writer bottleneck.

There’s challenges scaling read-heavy workloads, for sure — but they’re generally more straight forward than scaling write-heavy workloads. You can get away with more dumb horizontal scaling than with writes.

You don't see how adding functionality that requires writing to the database rather than just reading from a cache could "drastically increase database load"?

Even if it's writing to the DB, I doubt basic queries like adding an item to a cart is going to need DB sharding.

I think one piece that someone else mentioned could require DB sharding and that is all the live data needed for tracking deliveries.

The actual website/app should not need more than one beefy Postgres instance.

Scaling (asynchronous) reads is much easier than scaling writes.

That doesn't necessarily mean _new_ orders per minute. Their app or website could poll for updates every 15 seconds

Could just be looking at the "orders" endpoint in their app which might also include incremental updates as shoppers get items from the store. It's a fairly ambiguous statement

It's orders, not queries. Who knows how many requests that actually takes.

why did we switch to per minute? A modern quality enterprise SSD can do 35K +/- legit fsyncs per second.

Gives bigger numbers. But I agree per second is more honest.

I’ve always found Instacart to be extremely slow with giant latencies. Of course I don’t know if that’s due to Postgres or some other design flaw…

I am trying to gain a basic understanding of this: Right now I have a 4TB DB on one large box. Is the idea that using a proxy tool like PGDog I could spin up 8 smaller boxes handling ~500GB each and then one medium box for the proxy?

Right now I have a project that has very heavy write traffic from multiple services and a web app that reads from this. We are starting to hit the point where no amount of indexing, query optimisation, caching or box upgrades is helping us. We are looking at maybe moving the bulk of the static data to clickhouse to reduce the DB size but I would love to hear if PgDog or other kind of sharding could be useful for this use case.

> 8 smaller boxes handling ~500GB each and then one medium box for the proxy?

That's exactly right. Get in touch (lev@pgdog.dev), happy to help or at the very least tell you what current works (or doesn't) so you know what your options are.

That's the idea of sharding. If you read the pgdog docs, you'll notice you need to tell it which shard server to route your request to - it doesn't just magically work. It's still providing value by reusing connections, which are particularly expensive in postgres.

Because it's not magic, you do still have to know what's going on under the hood, e.g. no cross-shard transactions.

I'd see if my application can benefit from read replicas before doing sharding, because sharding is difficult (if you care about data consistency). With replicas, each replica does have a full copy of the data and you only write to the master - you have to decide which transactions are suitable for running against replicas, which can lag slightly behind realtime. E.g. reading data to build a webpage is probably safe to do from a replica - any read-modify-write is not.

fwiw, we support cross-shard transactions. They are not magic though, just good old 2pc and a bit of coordination.

2pc is only safe if every part of the system has guaranteed uptime, which it never does. Assume that cross-shard transactions only work in the happy case and may result in inconsistent data otherwise.

They also reduce the benefit of sharding, possibly down to worse performance than a non-sharded DB.

For sure. They should be used for "metadata"-style tables only. High throuput writes should be direct-to-shard.

I'm curious how this might help with our biggest downtime-causer with postgres, which is major version upgrades. Poolers do a great job for failover and load balancing, but we consistently need ~10-20 minutes of downtime once or twice a year to do upgrades. Logical replication between old->new versions could probably help, but it would still require flipping everything over to the new cluster without partial writes or anything silly. Anybody have experience with this?

Seconded. Coming from MySQL this is a huge regression that makes Postgres look like something from the 80s. I still wonder why this isn't seen as the absolutely highest priority.

Probably because it's an open source project and apparently none of its users cared about this feature enough to develop it or fund it.

It is also a bit tricky tradeoff. You do not want to be stuck with the same data format forever. So databases like MySQL and PostgreSQL need a downtime when doing a major version upgrade. They both try to keep it short, usually seconds, but minutes can happen in either database.

I have not ran MySQL for some years but it at least used to have exactly the same issue. Upgrading a database with MySQL can take a long time if you have many tables. The main difference is only really that PostgreSQL does it with a separate tool, pg_upgrade, while MySQL does it as part of the main binary.

For both MySQL and PostgreSQL you will need to use some kind of logical upgrades if you want no downtime.

MySQL has advocated for decades spinning up a replica with the upgraded version, waiting for it to catch up to master before promoting it to the new master. You can do the same thing with Postgres.

Exactly, MySQL and PostgreSQL are the same here. Maybe one is a bit faster than the other at doing major version upgrades but the behaviours are quite similar.

They don't change the on-disk structure all the time though...

Mostly because MySQL development is slower.

Even when MySQL development velocity was more rapid, they maintained binary table format compatibility across major version upgrades the vast majority of the time. Literally the only exception I can think of, which necessitated a table rebuild, was the fractional timestamp storage change when going from MySQL 5.5 (2010) to 5.6 (2013).

No, the main difference is that MySQL bundles the code needed to interact with the old db version in the newer server binaries (effectively by not changing the on-disk binary format!) while pg_upgrade requires you to have both old and new installs living side-by-side to reuse logic/code from old binaries. It is a more bulletproof method and less susceptible to bugs and (upstream) developer errors, but is (or at least can be) harder for the sysadmin+dbadmin.

(For example, ports under FreeBSD doesn’t let you install multiple Postgres versions as they are marked as conflicting packages so installing one necessarily uninstalls the other. The saving grace here is that most (virtually all) FreeBSD installations have root on ZFS and you can employ ZFS snapshots (via the hidden .zfs folder) to access the old binaries after upgrading to the new postgres version, but not many people know this trick!)

We use logical replication and a pause / swap in pgbouncer for ~5s of paused (but not failed) writes.

This is for DBs that are ~1-1.5TB but doesnt have a huge amount of churn/qps

Effectively what is described here https://www.pgedge.com/blog/always-online-or-bust-zero-downt...

Logical replication is how this is typically done. If you have some infra-as-code setup, you create a new cluster with identical settings except for the major version, import the schema, start copying data from a read-replica running the old version, stop accepting writes from the old version (downtime starts), sync the sequence numbers, and point your services to the new cluster (downtime ends).

If you use something like CloudNativePG they automate parts of the process with cli tools and declarative syntax. Otherwise you take the time to figure it out by hand. It might sound complicated, but just practice on your staging DB, and if all goes well you do the same procedure in prod.

Edit: Apparently Postgres 19 has a patch for one-shot logical replication of sequences! https://www.depesz.com/2025/11/11/waiting-for-postgresql-19-...

RDS has blue green deployments that can help. It was rough at first, though seems they worked out the kinks.

Logical replication solves this. You roll the cluster, downtime is minimal. like 60s maybe.

Logical replication needs a special 'upgrade' use case that will automate most of its pain points away. I understand why DDL does not replicate, and that you may want to replicate to a data warehouse that only needs some columns, etc, but there should be a case just for upgrading that handles all DDL, sequences all existing everything, and just works...

It's weird that PostgreSQL still doesn't have a proper, open source, general multi-master implementation.

At this point i wonder if i'll ever see that.

Do other RDBMSs have this? I genuinely have no clue. I've been fortunate enough to be able to get away with one primary and multiple secondaries at my largest usage of Postgres. Multi-master is the kind of thing I am fully out of my depth on, so I'm curious if there's a well defined path for implementation here or what.

MySQL has Galera cluster for that.

And Group Replication

And percona xtradb cluster

More accurately, MariaDB has Galera for that. MySQL Galera is EOL in a few months [1], which is understandable given the change in ownership.

[1] https://mariadb.com/resources/blog/upgrade-now-announcing-my...

Commercial RDBMS (oracle/mssql) have had it in some form for awhile, with pluses and minuses. Open source DBs have had bolt-ons, including BDR for pgsql.

Multi-master is hard. The main issue is what to do with commit/replication lag. It's far "easier" if support for eventual consistency is ok with your use case. In some cases it's not. Also, the problems related to read-only lag can happen on multi-master instances. If somebody does a giant long running query on one of the masters, the target instance needs to hold the data state for the query, even if the underlying DB is getting updates. It also needs to still keep up with other masters. This means the whole cluster can slow down if the multi-master replication is synchronous. Depending on a variety of factors, that can chew up disk space, memory, etc.

There are ways of dealing with these issues (and others), but it comes with tradeoffs with performance, etc.

It has been tried many times. Good luck to pgdog, but there’s a reason these projects don’t stick.

Multi master, from even a conceptual perspective, is incredibly complicated. Databases, transactions, consistency, parallelism are all very complicated.

It’s something that always seems promising at the start but as soon as maintenance and long term improvements enter the picture(ie integrating new Postgres versions), the complexity becomes too much.

Well, not officially, but there are solutions for that. Like BDR (or Postgres Distributed nowadays) by EDB.

> Like BDR (or Postgres Distributed nowadays) by EDB.

which is not open source afaik

What about Multigres[0]? It builds on top of Postgres and adds HA (based on Flexible Paxos[1]), sharding, etc. They're still not production-ready, but I'm highly optimistic they will solve a lot of the problems Postgres have.

For example, with Multigres, you should be able to achieve true zero downtime major version upgrade by simply resharding [2]. With vanilla Postgres + pgBouncer, you can only achieve near-zero downtime (few seconds at most), though it's probably good enough for most use cases.

[0] https://multigres.com/

[1] https://fpaxos.github.io/

[2] https://multigres.com/docs#migrate-across-postgres-versions

> What about Multigres[0]?

According to they githyb (https://github.com/multigres/multigres) as of today (June 12th, 2026):

> Multigres is a Vitess adaptation for Postgres. The project is currently in the early stages of development.

Maybe it works, maybe it doesn't. I would start looking into it when it gets released as stable. Otherwise it's unfair.

pssst... we're 100% open source under the PostgreSQL license, with active-active multi-master replication for any topology from single-region HA to write-anywhere global. :-) try it out on the Downloads page on our site https://www.pgedge.com/download/enterprise-postgres for secure downloads, or check out Spock on GitHub (https://github.com/pgEdge/spock) and the Active Consistency Engine (https://github.com/pgedge/ace) to integrate the extension & tool yourself. Answers to common questions in our FAQ: https://www.pgedge.com/resources/faq#pgedge-distributed-post...

PgDog, Neki, multigres, awesome to see. And yes this is the main issue with postgres. Well this and not having index hints, looking forward to 19

Don't forget the original PgBouncer. Hard to setup, but with the help of AI these days it's easier to configure.

The pg_hint_plan extension isn't in core, yet is pretty competent when you need to override planner.

  We sharded over 20 TB that we know about.

This is probably a typo, right? 20TB isn't that big. I would imagine they've sharded a lot more than that

For a vast majority of use cases 20TB is positively enormous.

that article seems to suggest 20TB total over the dozen deployments in prod.

Sure, but 20TB in “the only database you need” is mere hours or minutes worth of data for many workflows.

This product is for Postgres deployments that are so large they need to be sharded. For these use cases, I think 20TB is about normal.

Yes. But for most workloads it is not much for PostgreSQL. You often will not have to shard at all.

RDS caps out at 64 TB unless you use Aurora, so 20 TB is totally manageable without sharding.

You are correct. As a point of comparison: almost ten years ago at Segment we had a single Aurora PostgreSQL instance with ~50T of data, it was used to index potential identity data in a much larger corpus of files stored in S3.

If your working set is 20 TB, then it's pretty big. Each database has its own mix of hot/cold data, so it's impossible to compare without more information. A better measure might be IOPS. RDS has fairly low maximum IOPS unless you spend a lot more for provisioned IOPS or use Aurora.

If you think 20TB "isn't that big" I want to know what size of DBs you're working with 0_0

It's big but it's not so big it wouldn't fit on SSD on one particularly beefy server (two for redundancy). Sharding this would be more about the transaction rate. Actually, sharding would always be about the transaction rate.

It doesn’t even remotely need to fit on one SSD with logical volume management (or RAID).

I mean yes, for a single DB it's large, but if you're thinking about sharding you're probably in the tens of TBs, and if you're a company offering sharding you've prob sharded larger workloads.

It's really not that big for a postgres db in a lot of places, honestly.

I notice there is an Enterprise Edition, can you please specify which features are not open source? Do you predict new features you add will be ee licensed as a way to pay back your VC funders?

Two big ones:

1. Control plane to manage multi-node deployments; "works out of the box" experience to make PgDog easy to deploy and use

2. QoS (quality of service): automatically block bad queries from taking down the database

Last but not least, you get SLA-backed support from us (up to P0).

New features are broken down into two categories:

1. Sharding / running Postgres at scale: always open source.

2. Infra management / making it easy to run PgDog at scale: enterprise.

Thank you for the clear response!

This is a remarkably open-source friendly business model. I hope it works out similarly remarkably well for you!

Cool work, thanks.

Wrt. the pooler, how do you compare with pgbouncer?

I'm interested because I have a postgres instance, low-traffic but still like ... tens of r(eads)ps. I was not running anything close to the machine limits but still added pgbouncer to improve performance and didn't see a noticeable difference. I was stress-testing the machine obv., I'm not talking about the 10 rps, lol.

For context, my numbers were something like 10k rps +/- 1k vanilla postgres and like 9k rps +/- 1k with pgbouncer in front of it. So ... slightly slower but big error bars so I wouldn't say for sure. I ended up not using pgbouncer as the benefit was immaterial.

Also yeah, in case you want to check it out, it's the db that backs this project: https://httpstate.com.

Old benchmark, but still good: https://pgdog.dev/blog/pgbouncer-vs-pgdog

We used `pgdog` as a proxy during a recent database backend migration (Heroku -> EC2 -> RDS) and it was much smoother than PgBouncer. Really nice seeing more things in this space, and having the team's work recognized.

Awesome, glad it worked!

Love PgDog. I don't need it honestly, but using it in my on-prem k8s because I heard about you in Postgres FM podcast randomly when I had nothing to listen to on a hike in the woods and it picked up my interest.

https://open.spotify.com/episode/6qgpfiW68KcvRASs6649Fb

Thanks!

I've moved from pgbouncer to pgdog a few months ago without issue. Huge fan.

I tried out PgDog a while ago, but couldn't find a good way of handling the config except for having this users / pgdog toml file, which makes it a bit awkward to handle in kubernetes where we often do multi-tenancy in postgres - or rather having many databases on the same instance(s), and have them come and go at will.

Also had an issue with it because it cached authentication requests when doing passthrough it seems, I'd changed the roles password, but it kept using the old one, which was no bueno ;).

PgDog seems to make more sense when you really care about a few databases that need massive scale, rather than a simple proxy in front of postgres. I'll keep following the development though, it is much needed in this space, postgres can use all the investment it can get to get it past the single machine scale that it excels at currently.

Happy to chat about this, but we use the AWS secrets manager flowing into External Secrets Operator to generate a pgdog_users.toml. We then kick off a workflow to refresh things, but our rate of change here is much smaller than a super dynamic multi-tenant system.

You could also build a watcher side car that watches for changes of the pgdog_users.toml and have pgdog refresh itself then too with this combination. We thought about that but prefer to control the reloads for our needs.

We successfully did this with pgdog at $JOB using our own "controller" -- the same service that handles deploying new instances of our application (instancing an argoCD Application that fires Crossplane DB creation, making new Deployments of bricks, etc) will also, at the end of that process, scan the cluster for Database CRDs, use those to generate a new pgdog.toml + users.toml, update the Secrets in the cluster, enable maintenance mode on all pgdog pods, do a live config reload on each of them, then disable maintenance mode (this is to make the change atomic between all the pgdog instances). Downtime there is about 2-3 seconds and all it does is make new SQL requests from existing clients wait, it doesn't break the connection or anything.

Not the place and not the time, but we are building an enterprise edition that "just works" out of the box. Not saying that the open source experience cannot be better - it always can and we'll keep improving. What you've experienced is definitely a known issue with our specific implementation of passthrough auth. Scram made things a bit harder, since we can't validate user's passwords at login time anymore (that's what makes scram secure fwiw).

We'll get there.

Good stuff, although I’m not quite sure about the fast OLAP use case.

If you’re already sharding by tenant for other reasons, OK… But I see CDC to a true OLAP system as more scalable.

PostgreSQL still needs real columnar tables in the core, hopefully one day

OLAP means different things to different people. For us, it's just making sure your admin dashboard keeps working basically:

  SELECT tenant_id, COUNT(clicks)
  FROM users
  GROUP BY tenant_id
  ORDER BY 2 DESC
  LIMIT 25;

Performance is a side effect - definitely needed and we'll do everything we can, but we are not competing with ClickHouse or Snowflake - just trying to make sharded Postgres work with your app.

Re OLAP: It's probably ~good enough~ for a lean team that's trying to keep the tech stack standard and/or doesn't have a dedicated data person to take advantage of a columnar store.

Tomas Vondra, a major Postgres contributor recently revived a thread on using Bloom filters - https://www.postgresql.org/message-id/flat/5cd8c20c-14b5-4b0...

So there is more core work happening on support OLAP but I do think it will take some time.

In the meantime, I think we have all the pieces (storage, query engine, table format) to set up a true OLAP. For instance, I created https://github.com/viggy28/streambed to pressure test this idea.

I've loved using pgdog for the last 6 months. It's been incredibly stable. It's nifty how they've solved the LISTEN/NOTIFY on a transaction pooler problem.

Is there an explainer for people who are broadly familiar with the DB space? It sounds like you're building an equivalent to Vitesse for Postgres, but it's not super clear from the article (which I know is not the point of this, but still :) ).

Edit: It also might be interesting to point out how your solution differs from what the folks at Planetscale are building https://planetscale.com/neki

There's multiple solutions coming up in this space:

1. Neki as you mentioned 2. PgDog 3. Multigres, headed by original creator of Vitesse

Citus is an older one that does something like this, right? But it's an extension, not a proxy.

I could be wrong, but with Citus, for most use cases, you can only have one co-ordinator node which fans out requests. So theoretically, you still can run into bottle necks at some point if 1 coordinator node is not enough.

With proxies like pgdog, multigres, and eventually Neki, these can scale out horizontally, so you get true unlimited scale.

Doesn't PgDog only have one proxy, or can you have multiple? I imagine they'd need to coordinate on sharding rules somehow.

You can have multiple. All sharding is config-based, so no real-time synchronization is required.

Reminds me of long ago, before Postgres even had things like parallel scan to utilize multiple CPU cores on a single machine, I used to have Python helpers to split up queries by ranges of IDs. If a query was complicated, I'd EXPLAIN it first then pick either the innermost or outermost index scan, and often get a linear speedup. But it was quite manual, required using temp tables for SELECTs, and ofc had no consistency.

Three real-world issues I've run into recently with PgBouncer + Postgres are:

1. pool exhaustion from idle connections inside open long-running transactions

2. SQLAlchemy's client-side pool using dead connections that PgBouncer had already killed, causing periodic request errors

3. Some tasks have to bypass PgBouncer when they use SET or prepared statements

I've already sharded large datasets at the application layer, but looks like PgDog solves the above problems for any future work?

SQLA async is a bit of a struggle with pgbouncer.

I had to disable application pooling as it was causing read only transactions I could couldnt pin down the cause.

#1 is a problem with the client's code, I don't know any easy workaround. Usually a long-running transaction means you're accidentally waiting on stuff like RPCs in the middle, or maybe doing something that doesn't really need to be in a xact.

#2, shouldn't the client<->PgBouncer connections stay open?

#3 is why I just use client-side pools instead of PgBouncer, but that gets annoying when you have a replicated service so you have to think about the sum of connections across all pools, so I get why people use PgBouncer.

Congrats on the funding Lev!

Just to say we're happy pgdog users here! One feature we quite like (of the proxy) is the handling of different connection settings per connection (i.e. statement_timeout). When we investigated RDS proxy (ages ago) it wasn't supported, I think the same was true for pgbouncer so it required a bunch of application changes. With pgdog, it just works transparently.

Thanks! Glad we made it relatively easy to migrate!

>PgDog is a sharder, connection pooler and load balancer for PostgreSQL. Written in Rust, PgDog is fast, reliable and scales databases horizontally without requiring changes to application code.

Still trying to figure out how this works technically, is the performance gain really just re-write in rust?

Not quite. The performance gain is to bring those features to Postgres!

Edit:

Performance gains are from having the ability to load balance reads (horizontal scaling for read queries) and scale out writes (with sharding). Once instance bottleneck in Postgres has many faces:

1. Behind schedule vacuums because of too many dead tuples (too many writes)

2. The WALWriter is single-threaded and IO-bound - Postgres can only do about 200-300MB/sec in writes per instance (real prod numbers on EC2 with NVMes and ZFS, basically best case scenario).

3. Bulkheading: single primary is a single point of failure. With 12 primaries, if one fails, 91% of your customers don't notice.

The list goes on. Rust is just a side effect. We love it because it's fast and correct - the perfect match for a database product.

Oh thanks for clearing that up.

Sorry, out walking the dog (not a pun). I'll post more details in a few.

So to oversimplify, is the idea to bring an AWS Aurora-style storage mechanism natively to Postgres?

Yes, except it doesn't have any cross-dependencies on the same volume, so the uptime here should be higher.

Aurora has a completely different storage backend. PgDog is a front end proxy - each server in the cluster is still using standard Postgres right?

Yup!

Aurora is one big database, isn't it? PgDog is just a proxy where you tell it which shard to access.

> With $5.5M from Basis Set, YC, Pioneer Fund and other great investors, we have years of runway,

This is years of product development with a three person team. If Enterprise sales and support are a big part of your business plan it will suck up a lot more than that.

Presumably enterprise sales will bring in revenue on its own

Yes but the sales process for enterprise is often 9 months or more, and you have to have support coverage before it pays for itself.

Suggestion: have more than just helm and Docker in your quickstart documentation. I'd like to try this out just to see what it can do, but not quite enough to fire up one of those systems for it.

Is there a binary I can run directly?

We should add it to brew/apt/etc for sure. Also, we could add it to crates.io so you could do something like `cargo install pgdog`. Distribution, distribution, distribution.

I also appreciate GitHub releases with pre-compiled binaries for different platforms. The more options the better!

In addition - the docker compose example doesn't set up any data volumes for the postgres instances - that might be considered a bug?

Then again, sharding on a single host probably isn't very useful anyway - but it might work with docker in swarm mode?

The docker compose example is just a demo. I don't know anyone who runs Postgres with docker compose / swarm in prod :) But yes, happy to add volumes so it seems more real.

I'm a big PGDog fan! It really helped us scale our connection proxy needs pretty substantially and it has great features like auto mode to support Aurora failovers neatly. It's infra that just works.

I've seen a couple of these "distributed" postgres extensions.

My question is, has any of them been talked about being upstreamed to postgres itself? Or, adding a custom built in feature to postgres itself?

This is not an extension, it's a proxy! Very different. You can deploy it anywhere already without having to wait for upstreaming or your cloud provider adding support for it. It's one of the two reasons why we built it this way, the other being performance (it's much faster to do this in the proxy than inside Postgres).

It doesn't actually distribute postgres. It lets you use one connection to talk to multiple postgres databases by switching between them and if you're very careful you can sort of see it like a single database, ht it's not really.

It’s surprising they don’t mention advantages over other sharding systems like Citus. Maybe it’s just the fact that it’s only a proxy and not core extensions? But that could limit capabilities.

We do, just buried deep in our blog: https://pgdog.dev/blog/pgdog-vs-citus

The same old processes vs. threads debate, plus having the ability to scale the coordinator past a single machine. So, if you're OLTP, definitely consider PgDog. OLAP - Citus still wins because of its advanced query engine. We'll get there.

Excellent article, this makes a lot of sense!

TLDR: Tokio concurrency > Process concurrency in OLTP.

> having the ability to scale the coordinator past a single machine

Since Citus v11 (released 4 years ago), any worker node can also work as a "query router" (a node that you can query against [1], and works from this perspective as a pure coordinator:

> for very demanding applications, you now have the option to load balance distributed queries across the workers

You can also setup such query routers as dedicated nodes by setting the `shouldhaveshards` to `false`, becoming an effective coordinator (for querying; not for metadata operations).

So with Citus you can absolutely have as many query routers (coordinators if you wish) as you want.

[1]: https://www.citusdata.com/updates/v11-0/#metadata-sync

Edit: formatting, typo

We've run into so many issues with PgBouncer and Postgres that I wish we didn't have to deal with as a new growing company. Nice to see more options out there!

Like what? I've never had problems related to PgBouncer, but apparently we have different use cases. I'd love to hear where the rough edges are so I can avoid them, or at least plan for them in advance.

Is this comparable to Supabase's just announced multigres?

the reason mongo is a joy to use in scaled env is because no additional setup/software needed and all drivers natively support secondary/primary writes/reads and topological changes. so it's end to end, and adding is as a new proxy in frontend of postgres leads to all clients being incompatible or the code itself has no control anymore about when to use a secondary and what allowed stall is acceptable for a particular query. Any solutions to this by pgdog?

> all drivers natively support secondary/primary writes/reads and topological changes.

Expanding on that a bit, mongo drivers even have a shared specification of the state machine for monitoring topology changes[1] and algorithm for selecting the server to send an operation to[2] (along with various declarative test cases that the drivers use to validate them alongside the specs in the repo). I think people sometimes underestimate how important the client-side work is to this sort of experience; for all of the faults mongo has had over the years, the amount of investment that they put into the client libraries is something I've never seen anywhere else (although having spent several years working on some of these libraries, my take is likely very biased).

[1]: https://github.com/mongodb/specifications/blob/master/source... [2]: https://github.com/mongodb/specifications/blob/master/source...

once mongo rewrote their engine - it's performant, scales & easy to run. seems a lot of devs got burnt by the early issues don't consider it all together.

its probably the easiest database to run at scale. run & forget. you just have to do a little more work on the data modeling part before you write your application i.e consider your query patterns.

I really wish they'd acknowledge the prior art and name that they've taken inspiration from - https://github.com/postgresml/pgcat

Don't pay a startup for your DB proxy, you should own that layer yourself inside of your infrastructure.

The creator of pgdog is also the creator of pgcat, so I think they probably don't need to do this.

This reminds me of college. We had to cite our own papers from prior semesters or risk getting kicked out for plagiarism. I don't miss those days :)

I disagree, because now I am suspicious as to why there's a glaring omission like that. Never the mind looking at contribution timelines.

"it's not that deep" as the kids say.

In fact postgresML took naming heat because Postgres is right there in the name and they weren't affiliated with the brand. "pg" is just two letters. like WP-engine (literally the name as they say it is "double U P engine").

And a cat and a dog is fun.

don't think they're trying to get one over on you.

I don't think they're trying to get one over me, nor do I think it's _that deep_. One should simply acknowledge the prior projects that led to where you are today, even if they are your own. I 100% stand by my original statement and think that pgDog should mention that they're affiliated or a paid product on top of pgCat!

I only just now realized

pg cat

pg dog

What's he going to name the next version?

pg emu ?

pg mouse

pg_pachy ?

The founder is the author of both...

> you should own that layer yourself inside of your infrastructure

Unless you have millions of users, you don't really need this. It would be nice to have but its not a pressing need. So why invest into developing something that you only need once you are at massive scale? At this point you might as well switch away from Postgres because you'll surely have the manpower to do it.

Even with a proxy like PgDog the Postgres sharding story isn't solved. Resharding with logical replication is unlikely to work with databases which are already TBs in size. I never got it to catch up, I had to sync data at the filesystem level which is terrible. Tools like pg_repack also fall apart at scale.

For those that get to a point where a sharding proxy is required, switching databases is a very appealing solution.

And for those that are almost there, application side sharding is more flexible than building a query routing proxy.

Doesn’t PgDog also handle the sharding by proxying the writes? Maybe I missed something but I thought this is their value prop. It’s not just another PgBouncer.

From the docs you have to tell it which shard to access - it doesn't automagically rewrite your statements.

We also do that! But it's not well documented at the moment.

I do tenant per PG schema, most are smallish some are bigger (not much, can do all in a single box) but moving forward eventually will need something like this. Also plan to provide "get your own VPS" for more enterprise customers.

This kind of tool will help in this case?

Yup. We support schema-based sharding: https://docs.pgdog.dev/configuration/pgdog.toml/sharded_sche...

I think sharding is the wrong approach; who wants to mess about with sharding logic? Distributed key-value stores are the way to go. But cockroach already offers that so I suppose you can try the other way.

Does making it "just work" here come with any caveats vs standard PG?

Getting there! Cross-shard writes do because of 2pc. Reads are eventually consistent.

Given that they implement connection pooling and sharding, I'm going to say "not at all".

You _could_ make that ACID, but it's not going to be faster than a single machine.

congrats, lev! brings back fond memories of database fires.

i'm sure you'll get 100x comments about "why not just have one fast SSD? it can do 2000 trillion writes/s"

Thanks! Yup...to be expected. If you know, you know, and have the scars to prove it :)

I'd love to advocate for PgDog if there were more than 2 managed service providers. Adding a single company with no substitute in your supply chain feels hard

I didn't realize there are _any_ managed providers of PgDog out there...do tell!

I see the top diagram on the frontpage shows 'dsql' support but it isn't mentioned in the documentation, is that correct?

Let's go. Very bullish on PgDog. Lev understands this space better than anyone else. If you are sharding Postgres, you should talk to him.

We are still using Pgpool-II and it's been very solid, but would be interested in moving to PgDog.

Would love to hear the advantages of moving to PgDog.

I wish them all the best. Supabase, Timescale, etc etc. there's a whole cottage industry of extending postgres to whatever you need.

How does this compare to Aurora Serverless?

This is exciting. INSERT (SELECT ...) doesn't work though, right? The docs only mention VALUES inserts.

Not yet, but actively working on this as we speak.

2M qps in production is legit. Curious how much RAM and CPU that takes on average per deployment though

Depends. Only pooling, very little. Load balancing/sharding needs to parse queries, so a bit more. Could go up to a GB per pod, sometimes more if you have a lot of unique SQL queries (unique by text, not by parameters). We cache query ASTs to avoid parsing them on each request - that's the bulk of memory usage.

Semi related question - I have always wondered, how do you tackle OOM issues at the proxy layer, i.e. let's say a particular SQL query requires proxy to fan out the query to multiple shards, which return a pretty large dataset. I'm assuming you would need to load this dataset in the ram to perform certain operations. What happens if the resulting dataset causes the proxy pod to go OOM?

Two schools of thought:

1. Let it crash. Increase the RAM, try again.

2. Page to disk (swap), make it slow but ultimately work.

Both have their trade-offs. There is no free lunch here.

Scratching my head. Wondering why I would reach for this over just running a Yugabyte cluster.

This commit looks... odd.

https://github.com/pgdogdev/pgdog/commit/36434f93f03dec1d7d4...

I want to have as much fun as the next developer, but that makes me worry, what with supply chain attacks in the news and all.

I see you met Sage, our newest founding engineer :) If you're not having fun at your job...

In all seriousness, we review every single line of code that goes in and only people who work for PgDog Inc are allowed to merge.

I am odd, yes. I also care deeply about supply chain security and focused on it when I led the crates.io team as well as during my time at the Rust Foundation. You can rest assured that my occasional shitposts are not opening an attack avenue for your supply chain.

- Sage

I will not stop shitposting on main though

How is this different from Citus?

I hope people pronounce this as „pig-dog” and has a mascot that looks like „man-bear-pig”

Crap! Missed opportunity.

let's say i have a primary with 100M rows of addresses and indexes on things like city, state, zip code (all in memory). I also have 3 read replicas that struggle to do 1000 lookups per minute each. Does PgDog help?

I us pg. not that I know much about database internals, besides the 'b-tree' stuff we learned in college.

I don't know how the pg scaling story gets fixed unless certain things are rewritten. that's my fear of going all in pg.

mysql has vitess etc & even upgrades are easier. though pg is more extensible.

Any strongly consistent database is going to be limited by a single machine's throughput. That's just what you trade for strong consistency. You can shard it yourself but then the DBMS isn't giving you consistency so you'd better be very careful. You can use a tool like PgDog to aid with sharding but it's not doing magic, you still have to be aware how it works and the limitations of sharding.

However 95% of projects are going to be fine with a normal single-machine database and another 4% are going to be well served by upgrading the hell out of that machine. Only the absolute busiest projects actually need a distributed database and you can cross that bridge when you actually get to it.

They say Amazon processes 20k orders per second. That seems not unachievable for postgres with fast SSDs and careful query optimization, though they don't choose do it that way. You're not Amazon, you have at most 20 orders per second and that's nothing.

What's the difference between pgdog and vitesse?

i am not using any tool like pgbouncer and have not run into any issues so far. Is it even required these days? Have you guys tested your setup without these connection poolers/multiplexers?

Each connection is a process on the server, that takes up both CPU and RAM, it will run out.

This solves the thousands of clients case for read in a way that is transparent to the clients.

Yes it's required at large scale, especially if you want to distribute reads or shard to a particular geographical area.

Nit-Pick: It might be anti-marketing, still it would be helpful if the use cases can be articulated in a way where it would make sense to use this Vs any other type of database. Honesty goes a long way with the more technical folks for anything related to infrastructure.

Surfacing where and how PG is better than Dynamo or any other database is probably a good starting point instead of calling out PG a silver bullet for everything. At the end of the day its all a trade-off.

Always is. Marketing is not our strong suit (only engineers here). We'll get better at it.

Crap, I'm supposed to be an engineer?

How are 3 developers going to QA this properly ?

How are 3 developers going to sell that to any company? Procurement will have a field day.

They have funding. That's what it will be for. I wish them well and appreciate that people are still doing FOSS.

As long as they don't get undercut by the equivalent of AWS https://aws.amazon.com/rds/proxy/ which is a managed pgbouncer.

The issue is if the DB layer fails your product is going to completely stop working.

You’d need a ton of faith in these 3 people.

Feels more like it would work better inside of a bigger organization.

The QA tester in me is kinda risk adverse.

The source code is available for inspection as is the biographies of the people involved.

They rely on the libraries that are part of Postgres itself to ensure they are parsing the SQL etc "correctly" (where "correct" means "the same as Postgres itself).

Bigger organizations do not necessarily mean higher quality.

What bigger organization is testing PostgreSQL itself?

What are the relative quality measurements of Postgres vs MariaDB vs Oracle vs SQL Server?

MariaDB has over 200 employees.

https://mariadb.com/about-us/careers/

Oracle's department that handles DBs probably has at least a few hundred.

3 people would be an ultra lean QA department for a product like this.

I'd have a hard time convincing my boss to go with PGDog over a more stable and tested solution.

This doesn't mean it's bad, just not ready yet

They are not just some random 3 have decades of real db experience behind them. They also just got funded which gives them the ability to expand and stay longer in the game.

> we don’t think you would use anything else.

This just seems like fanboyism to me. At the very least, you need to qualify what scenarios you think it's useful for.

I don't doubt that Postgres is good for all the projects you've ever worked on. Generalizing from that, though, is hubristic.

we are using PG bouncer in production. Interesting, I will follow the evolution of this project

Is this like on prem RDS?

This is just awsome

Fix the bad license.

Is this vibe-coded?

how does it compare to PlanetScale ?

PgDog is GalaxyScale </joke>

> The reason DBs like Mongo or Dynamo exist is because

Not quite. The reason "DBs" like those exist is purely due to fashion. Lets not kid ourselves into thinking they do anything better, save the exception of making data hard to access, which might be a project goal in some cases.

Dynamo definitely scales better than anything else at the tradeoff of not guaranteeing durability in the case of enough node failures and (like most distributed databases) not allowing interaction between different pieces of data.

> Tradeoff of not being able to query your data

Fixed it.

Dynamo is not write-only.

I mean neither is /dev/null

Reply on news.ycombinator.com