Project ideas of pgwire

[sunng87]

pgwire aims to create a protocol layer that enables Rust applications to leverage Postgres' frontend/backend interactions. This library focuses on protocol implementation and API, designed for developers who may not even use SQL as their query language.

While I'm try to keep this library general, there are some potential project ideas the community can take and move on.

A Cool and Postgres-Native User Interface

This might not be a serious one. But it's like what Neon used to provide as an interesting on-boarding experience: use a postgres Notice for greeting.

❯ psql -h pg.neon.tech
NOTICE:  Welcome to Neon!
Authenticate by visiting:
    https://console.neon.tech/psql_session/77409f52d5cb1b2b

This is fully possible with pgwire.

Postgres-like Databases

Developers can build Postgres-compatible databases using pgwire. Examples include:

• Existing database backends greptimedb, sqlite and duckdb
• Database programming framework gluesql
• Query engine as library datafusion

Typically to build a Postgres-like database you will need:

• pgwire as protocol adapter
• data type conversion between postgres and your own
• a query engine with query parser
• an underlying storage engine

Postgres compatibility means more than just wire protocol. It is very simple and straight-forward to implement the simple query subprotocol, on which you can talk to the server using psql. However, to work with other database management tools, language drivers and visualization tools, there are several tiers of compatibility:

• The wire protocol: pgwire is focusing on this tier
• The Postgres SQL dialect: sqlparser-rs may do some help
• The Postgres data types: postgres-types
• The metadata layer: information_schema, pg_catalog schemas

Also it's worth to note that the extended subprotocol has some features that your database backend may not support. For example, postgres is able to inference data types of parameters in prepared statement, without parameter values provided, or executing the query statement. This step is called "Describe statment". Some of the language drivers like rust-postgres relies on this command to get parameter types. As far as I know, this is impossible with sqlite.

Middleware for PostgreSQL

There are requests from community to build a PostgreSQL proxy using this library. And I believe this is the other approachable direction. It's also the start point for a promising Postgres ecosystem that will be built on rust.

To archive this goal, there are several levels and abstractions developers can work on

• A frontend API abstraction within pgwire. This is like a low-level API for build database drivers. It's going to be general enough and only gives you callbacks for various Postgres wire protocol messages.
• An event based framework built on top of pgwire, the receives messages from frontend, and forward to backend. Developers will be able to interpret, manipulate those messages and build strategies for backend selection. This will be like Pingora but for Postgres.
• A real proxy application built on pgwire. It's like nginx for postgres. The application can be based from the framework above. It should be highly configurable with a modern configuration or scripting language. Or with best practices already built-in.

Conclusion

These are my ideas for developers who has a passionate to work on postgres protocol and rust. It's excited to see how we can built rust postgres ecosystem from the top tier, if you consider pgrx is doing it from the bottom tier.

Let me know if you have more ideas with the library.

[Xuanwo]

Please allow me to add opendal here so we can play with cloud storage nicely while needed.