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

44

u/agentoutlier Jan 16 '23

IMO this is the best post of the year so far!

Also thank you for not using Medium or Substack and making a very easy to read post!

EDIT also thanks for introducing that online flow chart service. Looks pretty cool and I might use it in the future.

19

u/GavinRayDev Jan 16 '23

Aw heck, you're gonna make me blush 😳

Also thank you for not using Medium or Substack and making a very easy to read post!

I have... less than stellar opinions of people who post technical content on these sorts of sites. If there are paywalls or ads, and it's meant to be educational, it shouldn't go there IMO.

1

u/mike_hearn Jan 17 '23

You can write Medium and Substack posts without paywalls. It's a bummer to read that because my blog is on Medium (blog.plan99.net) and there are no ads, and no paywalls (as far as I know!). It's convenient not to self host this, so I never got around to moving it. But I see this viewpoint more often these days.

1

u/GavinRayDev Jan 17 '23

If there are no paywalls, no problem from me!

3

u/bytedonor Jan 16 '23 edited Jan 16 '23

Why not just use ‘ServerSocket’, accept connections on main and pass the open socket to a VT executor task? I did something like this recently and was able to pump data across 500K web sockets in my test

7

u/GavinRayDev Jan 16 '23

No particular reason, outside of the fact that I tried to write it originally with an async socket for performance, couldn't find any good examples online:

There's one article which shows some super basic usage and then a bunch of regurgitated articles with questionable code patterns

So I did some digging in Github for usages of the API and tried to put something together that was a bit more full-fledged. Now there exists a better reference article for it.

I'm not familiar enough with the internals of Loom to know whether the Virtual Threads make a huge impact in this async context (maybe /u/pron98 can comment) but I had thought that since it's used for the channel group executor service it should be alright.

9

u/pron98 Jan 17 '23

Using an ordinary ServerSocket polled in a loop on a virtual thread should be about the same if not better. All sockets used on virtual threads automatically become effectively asynchronous from the OS's perspective anyway.

2

u/GavinRayDev Jan 17 '23

Neat, that would have significantly simplified the design. The callback loop in the async socket isn't the prettiest thing, now I know.

Thanks for commenting, Ron!

4

u/lurker_in_spirit Jan 17 '23

There's a recent Helidon Nima podcast floating around on YouTube where they mention that in their experience VT + non-blocking IO is slower than VT + blocking IO: https://www.youtube.com/watch?v=AkMSEjkfldk (minute 15 or so)

1

u/kaperni Jan 17 '23

Would it make sense to run ServerSocker::accept in a dedicated platform thread? Or is it same-same?

1

u/rbygrave Jan 18 '23

Just noting that with Nima yes it does use a platform thread for that. To me that makes sense - dedicate a platform thread to that task.

1

u/sandys1 Jan 17 '23

Could you elaborate on this ? Really keen to learn this.

2

u/bytedonor Jan 17 '23

Nothing much to see - it's a stupidly simple blocking socket server which looks exactly the same like the one you would use with traditional threads, but without scaling problems:

    public void serveForever() throws IOException {
        try (var executor = Executors.newVirtualThreadPerTaskExecutor();
             var serverSocket = new ServerSocket(8080, 16384)) {
            while (true) {
                var sock = serverSocket.accept();
                sock.setSoTimeout(5000);
                executor.execute(() -> {
                    try {
                        protocolHandler(sock);
                    } catch (Exception e) {
                        try {
                            sock.close();
                        } catch (IOException ioe) {
                            // Ignore
                        }
                    }
                });
            }
        }
    }

1

u/sandys1 Jan 17 '23

Trust me. This is not stupid for us. And thanks for this!

Where do u do the actual work and processing ?

2

u/GavinRayDev Jan 17 '23

(In the protocolHandler(sock) function, your business logic would go there)

1

u/rbygrave Jan 18 '23

This is how Helidon Nima does it as well.

12

u/lukaseder Jan 17 '23

I'm a simple man. I see PostgreSQL, I upvote.

9

u/kharkivdev Jan 16 '23

Now. I am hyped for Java 21.

2

u/persism2 Jan 16 '23

Very cool! What's the advantage over just regular jdbc connection? Performance?

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

1

u/skippingstone Jan 17 '23

Where is the source code?

4

u/persicsb Jan 17 '23

directly in the article

2

u/GavinRayDev Jan 17 '23

HA!

A good question though, I probably should put the finished thing up on Github. I'll do it today and post a link here.

I'll probably not make it use Java 21 because it's kind of a pain to set up to use with Gradle/Maven though.