levkk

It sounds like we just need to add a config:

     cross_shard_queries = off

What else am I missing?

Echoing the comment below (above), since we can fingerprint queries using the Postgres parser, we can create an allow list and a more fine-grained ruleset.

Everything is fine. You never have to release your code for anything that's not PgDog itself. If you're running PgDog as a service, anyone who connects to it can ask you for the source code of PgDog. That's what AGPL says.

In practice, if you use it internally, your teammates already have access to the code. Requirement fulfilled.

Your databases run on a private net typically, so your customers can't connect to PgDog directly: AGPL doesn't apply. That's internal use.

If you're running a DB cloud and using PgDog as a proxy, anyone who connects to it, i.e. your customers can ask for the code to PgDog. If you didn't modify it, just point them to my repo. Requirement fulfilled.

If you did modify it, e.g. added some cool feature or fixed a bug, you'll have to show your clients what you did. That's real open source. That's how GPL was supposed to work. Code is free, and so is knowledge and our ability as humans (and now machines, lol!) to learn from it. GPL stopped working because we stopped shipping software on CDs. AGPL is it's modern version.

I love open source and that's why I'm using AGPL. GPL and FSF is the reason why I can do what I do today: I learned from open code written by thousands of brilliant people who chose to share that with the world. Now it's my turn to give back.

Thank you! We used Makara for this at Instacart, but eventually grew to using other languages, e.g. Python, Go, etc. and reinventing the wheel every time was pretty tedious.

Hey Jake!

I would love to collaborate. Email me: lev@pgdog.dev. Config management is a solved problem, we can use K8s or any number of CD tools. PgDog config reloading can be synchronized.

Best effort transactions for schema changes across shards are working today. Ideally, schema changes are idempotent so it's safe to retry in case of failure. Otherwise, we can try 2-phase commit. It'll need a bit of management to make sure they are not left uncommitted (they block vacuum).

Shard splitting can be done with logical replication. I've done this at Instacart with 10TB+ databases. At that scale, you need to snapshot it with a replication slot open, restore to N instances, delete whatever data doesn't match the shard #, and re-sync with logical replication. Another thing I wanted to try was using Pg 17 logical replication from streaming replicas. I feel like it's possible to parallelize resharding with like 16 replicas, without affecting the primary. In that situation, it might be feasible to just COPY tables through foreign tables with postgres_fdw or PgDog (my choice of sharding function was very intentional). Something to consider.

pg_query.rs seems to be mutable now, as far as I can tell. I've been rewriting and generating brand new queries. I like that it's 100% Postgres parser. That's super important. They have the "deparse" method (struct -> SQL) on pretty much every NodeEnum, so I think it's good to go for doing more complex things.

Lev

Small consolation prize is we can generate unique primary keys: https://docs.pgdog.dev/features/sharding/primary-keys/.

I would like to implement cross-shard unique indexes, but they are expensive to check for every query. Open to ideas!

Check this out and let me know what you think: https://pgdog.dev/blog/multi-tenant-pg-can-be-easy

I think there are a few good solutions for multi-tenant safety. We just need ergonomic wrappers at the DB layer to make them easy to use.

Thanks! I never considered this. What would that look like?

I've been thinking about other algorithms as well, like range-based. We can definitely override the algorithm for hot shards. Ideally, we match what Postgres does with partitions: range, hash, and list. That way, we can shard both inside Postgres (e.g. with postgres_fdw) and at the proxy.

I think the first step is to add as much monitoring as possible to catch the problem early. There is also the dry-run mode [1] that you can try before sharding at all to make sure your traffic would be evenly split given a choice of sharding key.

[1] https://pgdog.dev/blog/sharding-a-real-rails-app#dry-run-mod...

Thanks! I'm curious:

> I’d want friction on breaking this boundary

Why do you want friction?

> implications of joining across shards are not the same

That's usually well understood and can be tracked with real time metrics. Ultimately, both are necessary and alternative solutions, like joining in the app code, are not great.

Thank you! Good feedback. The query parser is cached, so if you're using prepared statements or just the extended protocol with placeholders, it's almost free: cost of a mutex + hash lookup.

Cross-shard joins will be interesting. I suspect the biggest cost there will be executing suboptimal queries without a good join filter: that's what it takes to compute a correct result sometimes. Also, the result set could get quite large, so we may need to page to disk.

I'm focusing on OLTP use cases first with joins pushed down as much as possible. I suspect cross-shard joins will be requested soon afterwards.

It's config driven, so no split brain. All proxies have the same config and deployments are synchronized:

1. pause traffic 2. reload config 3. resume traffic

This can be done in under a second.

Restarts without downtime can be handled with blue/green using and a TCP load balancer or DNS.

No issues. PgDog is a company, email me if you want to use it internally and we'll work it out.

lev@pgdog.dev

Absolutely. Join our Discord, if you'd like: https://discord.com/invite/CcBZkjSJdd

No. If you're using it internally though, there are no issues. You don't have to share anything. If you're using it externally, e.g. building a PgDog cloud service, you'll need to share any changes you make to PgDog _only_.

I don't think Supavisor actually does sharding.

Thanks! Will do. 15 year journey starts now.

Thank you. Years in the making.

Thanks!

Business plan is managed deployments and support, pretty standard for an infra product I believe.

That's exactly what I'm saying!

There are lots of things that neither the code nor the docs cover, so I suspect that's not quite possible, yet.

For example, if you're deploying a Postgres proxy, it will have a TCP timeout setting that you can tweak. Neither the docs nor the code will tell you what the value should be set to though.

Your engineers might know, because they have seen your internal network fail dozens of times and have a good intuition about it.

Software complexity has a wide range. If you're thinking of simple things like Sendgrid, Twilio or Stripe APIs, sure, an agent can easily write some boilerplate. But I think in certain sectors, we would need to attach some more inputs to the model that we currently don't have to get it to a good spot.

I can run it for you! Email me: lev@pgdog.dev

There is a bit documentation about configuring foreign keys here: https://docs.pgdog.dev/configuration/pgdog.toml/sharded_tabl...

Medium term I can see detecting foreign keys automatically (if constraints exist).

It can but it's not the primary goal at the moment. If you want to restrict the number of rows returned, you can rewrite the query to add a LIMIT clause. To control which rows your users can see, you can use row-level security.

One thing I was thinking of doing is generating query plans asynchronously and blocking/cancelling queries that would otherwise be expensive and cause downtime. That's on the roadmap.

Will do. You're not the first one to recommend this, it's about time I listen.

Paginating over millions of rows isn't really done in OLTP use cases that PgDog is targeting, as far as I know. Would be great to learn about yours though. Feel free to reach out!

Thanks!

Re: pgcat and longevity, it's actually the opposite. Pgcat is/was an open source project that I worked on in my spare time. Thankfully I found a couple engineers (and the awesome community contributions) at large corps to push it over the line.

PgDog is a startup, so this is now my full time job.

I'm guessing so it's never zero and can't be confused with something else, like a bunch of NULs. There are a few messages that's don't have a payload, e.g. ParseComplete.

Exactly :)

Yup.