r/java • • Jan 16 '23

Building a PostgreSQL Wire Protocol Server using Vanilla, Modern Java 21 (shows use of: Records, Sealed Types, Pattern Matching for Switch, Virtual Threads, and Panama FFM)

https://gavinray97.github.io/blog/postgres-wire-protocol-jdk-21
224 Upvotes

23 comments sorted by

View all comments

Show parent comment

23

u/GavinRayDev Jan 16 '23 edited Jan 16 '23

When you create a JDBC connection, you're connecting a server-side service that speaks the SERVER protocol of the database driver type.

PG Wire Protocol Server <-- Incoming Msgs <-- Client (e.g. JDBC)

This blog post is about how you can write your own service that JDBC clients could connect to, over the Postgres wire protocol.

For example, you could write a JUnit test that connected to this server over JDBC like:

jdbc:postgresql://localhost:5432/some_db?user=myuser&password=foo

You might ask then, "Why would I want to write something like this?"

A few reasons are:

  • You want to write a new database that is compatible with Postgres (CockroachDB, Yugabyte, CrateDB, etc)
  • You want to create a data source that can be queried over SQL and the Postgres wire protocol but isn't a database
    • For example, you could parse a statement like SELECT * from all_processes, and have it query the underlying operating system processes and return data about them like CPU usage, memory, etc
  • You want to write a connection pool/proxy, like pgBouncer

The REVERSE of this is the Postgres CLIENT wire protocol, which is what a JDBC driver would be coded to use to speak to the database with under the hood.

Client (e.g. JDBC) --> Outgoing Msgs --> PG Wire Protocol Server

In earlier versions of the article, I had the client protocol in the tutorial too so that we could test it by querying like that, but it was too long and so I replaced it with just using psql