Hacking the Postgres wire protocol

The best part I think is "pg_query is special. It doesn’t actually implement parsing SQL. It works by extracting C source code directly from Postgres and wraps it with a nice Rust interface. This allows PgDog to understand all queries that Postgres can."

One of the things that puts metaprogramming on wheels is being able to pull in grammars, schemas and such for external systems. Think how many cool tools we could have if we could do this in a more general way.

Having written several pg txn poolers, I like the implementation. I also love your simple branding and I think this could make a fantastic product for enterprise customers. However, my biggest concern as a buyer is longevity. PgCat (another popular pooler in rust) will likely lose funding shortly. Meanwhile, PgBouncer is battle tested, small in scope, and is starting to get consistent new contribution.

pgdog looks interesting, but I read the docs wondering how it handles foreign keys between tables, and the docs don't seem to cover it. It was the first question I had, and I'd assume would be an important question for most users. The project states "Data relationships are not sacrificed to go faster" and that it operates "transparently to the application", but it's not clear how it does that in regard to foreign keys.

Additionally, maybe this is just a pet peeve with Postgres documentation in general, but the docs use "database" to mean both a single database and a Postgres server interchangeably. On some pages this makes the content harder to understand. I think it's good practice to distinguish between databases and "clusters" (no, not those clusters, actually servers), and perhaps between clusters and groups of servers. While some of the naming is unfortunate for historical reasons, re-using the same names for different concepts just causes more confusion.

Wrapping up the actual Postgres code is a brilliant idea. Then you never have to worry about updating your code to keep up with their changing API.

And the fact that this works at all is another example of why Postgres is the default data store on the internet. It's battle tested and has a massive ecosystem of support tools, like this one.

>we need to ... understand SQL syntax using a parser, Rust ecosystem has a great library called pg_query

I'm impressed both that the Rust ecosystem had the right library available and that it was high enough quality code for the author's team to use it as a reference for building their own additional functionality.

I am long on this project and excited about it.

I am wondering, why it took so long for something like this to happen for Postgres, where as Vitess existed for MySQL more than a decade. I am not an expert, but I hear Citus is not really a fair comparison with Vitess.

It would be nice if they could go more into detail about how they decide whether a query is read or write. The casual mention of this makes it sound like they originally took a too-naive approach (looking at the first word) but even ignoring CTEs, you can still have an UPDATE within a SELECT subquery, which makes the problem very hard without parsing the whole query. Perhaps the C++ code extracted from pg handles this for them and returns some metadata that indicates whether there are any writes buried in the query?

I feel I am missing something with the approach of those routers. If I am doing cursor pagination over a couple million rows with some ordering this will pull in a huge number of data from each server and then perform it in memory at the router level if I understand correctly.

The neon approach of decoupling storage from processing but keeping the processing a query local to one server seems better to me, but I am maybe missing something.

> More complex examples, like IN (1, 2, 3) or id != 25 can be handled as well. For the former, we can hash all values and route the query to the matching shards. For the latter, we can do the opposite.

This makes it sound like the query would be routed to all shards except the one matching HASH(25). But wouldn't id != 25 need to go to all shards, since there could be values in the id set that hash to the same shard as 25?

Am I right to think this could be used to "inject" limits on the number of rows returned by a user query, or otherwise restrict what users see allowed to do?

I know it sounds silly/crazy but I have a use case where I would like to allow "mostly" trusted users to access the database directly and not through an API, but I need to apply some restrictions on their output.

> with 4 bytes added for itself

Why one would want to do that? Only reason I can think of is, so that values < 4 can be reserved for future users, like new versions of client-server that agree on 64 bit payload if the first 4 bytes are zero or alike. But there are better ways.

Has someone done this for MySQL?

Is there something like pg_query for Oracle ? I want to use it for making AST's out of Oracle SQL queries