Hacker News Reader

Comment by benbjohnson | Hacker News Reader

Postlite author here. I'm glad to see the interest in this project. This project really only has one use case and that is being able to connect to a remote SQLite database using GUI database administration tools like DBeaver. The idea came out of this Twitter thread:

https://twitter.com/benbjohnson/status/1508927916561743872

It's a recurring theme with SQLite. Many developers are on-board with using it as a database but when you tell them that they also have to SSH in and use a CLI exclusively then they are turned off. Postlite is meant to alleviate that pain.

View on HN

Heh. When I saw this project, I wondered what it would be written in. Using jackc/pgproto3 almost feels like cheating... it's such an unreasonably great protocol library for Postgres.

I wrote a PgBouncer alternative awhile back using that library, and in the span of only about 1200 SLoC, I had a fully functional alternative that...

- benchmarked better than PgBouncer for me

- avoided the need for the annoying session, transaction, statement modes by just Doing The Right Thing. If you prepare an anonymous statement, it holds that connection for you until you execute the prepared statement. If you open a transaction, it holds that connection for you until you commit or rollback that transaction.

- offered control over whether to set application_name for the DB connection whenever one is acquired by a client connection, and whether to also clear application_name or not when the connection is released.

It's relatively straightforward to detect when a SELECT query comes through, so I had planned to add transparent read replica support to route SELECT queries to read replicas if you aren't in a transaction. I also never got around to implementing TLS support, but... that should be trivial in Go.

Longer term, I also thought it would be cool to implement some extensions to the Postgres wire protocol, like end to end compression with zstd. You might have an application in one datacenter querying a Postgres database in another, and depending on the size of datasets that you're getting back from the database, compression could make a huge difference. You could also imagine implementing a "double proxy" where a proxy is running both locally and in the remote datacenter, and it would implement the non-standard postgres extensions behind the scenes, presenting a purely standard wire protocol to any client that connects.

Since this was just a fun side project that was never proven in production, I've never gotten around to open sourcing it. I also wish it actually had some tests... but those haven't happened yet. I don't want to mislead people into thinking I consider this side project to be production ready, but I don't remember any obvious problems. If people were actually interested, I could open up the repo, but this comment is less about self-promotion and more about how impressed I've been with that particular wire protocol library, but I admittedly do also enjoy talking about side projects. If anyone needs to do something with Postgres's protocol, I would highly recommend it.

Yeah, jackc/pgproto3 is great. When I first started the project, I was digging through the protocol documentation and expecting to write message parsers. But pgproto3 handles all that. Almost all the real code in Postlite is in a 500 LOC file[1]. I was surprised how easy it was.

[1]: https://github.com/benbjohnson/postlite/blob/main/server.go

at least half the code is error-checking or error-handling

Please open source your project. It sounds like a lot of (designing, as well as coding) effort went into making it, and you don’t want to see all that work go to a waste.

Do not worry about it being buggy, or even it being bad (design, or code). Let others do that work for you :-)

I went ahead and made it public here: https://github.com/coder543/roundabout

It took a few minutes since I had to add a license and README, plus I did a quick test of it locally to make sure it still worked, which helped me discover that SASL authentication needed to be implemented, so I added that.

I agree other people could be interested in contributing, I'm just not sure how much interest there actually is for a PgBouncer alternative.

Thank you for opening it up to the public. Much appreciated!

Do you know of a package/application/library that can be used to validate just the FEBE protocal of Postgres, and possibly stress/performance test it, as well. Such a test-suite would be great to independently test the various implementations of Postgres wire-compatible projects and products, including your roundabout.

I’m not sure what exists as far as protocol validation goes, but I did benchmarking of mine versus PgBouncer using pg_bench, and it had no problems.

I think the following could be an interesting project:

A pair of programs. One to emulate Postgres server, another to emulate a Postgres client. These two programs would assume they are just talking to each other, and know exactly what to expect from the other side.

Then we can inject a protocol implementer (the system under test, or SUT) between these two programs and see if the SUT can make both these programs believe that they are still talking just with each other. This way we can validate the level of protocol support by the SUT. And if we can run this whole setup, multiple clients, a server, and the SUT, under controlled conditions, we can also evaluate the performance of each such protocol implementation.

The Postgres client emulator would send a predefined set of commands, and would know exactly what the response should be. The Postgres server emulator would know exactly what commands to expect, and the hard-coded responses to send for each incoming command.

The client emulator's knowledge of the responses would make it easy to catch any errors/bugs introduced by the SUT.

The Postgres server emulator would _not_ implement any server-side logic (command parsing, planning, etc.), to ensure the peak performance for each command it processes.

I was thinking of implementing such a Postgres server emulator back in around 2014, but for a different reason. IIRC, I was thinking of calling it Black Hole Postgres, to test the performance of my TPC-C implementation, DBYardstick [1].

[1]: https://github.com/DBYardstick/TPC-C

Ben, you have been one of most influential creators I have known. I have been following you since boltdb days. I used boltdb in project that helped scale backoffice system of many counties in tier 2 cities of US, creating huge impact. With litestream and now this, thanks for creating project that are literally changing the way new developers are building systems.

Thank you, Debarshi! That means a lot. I really appreciate it.

Do you think this technique can be adapter for integration testing? So you wouldn't need a running PostgreSQL server to run tests that connect to a PostgreSQL database, or maybe it would make tests easier to run in parallel by using different SQLite files.

Tests that run against a different relational database from production make me really nervous.

The Django ORM has provided the ability to test against SQLite and deploy against PostgreSQL with the same code base (and the same tests) for years - and while it works incredibly well, I still won't use that in any system that I build. How your database behaves is such a crucial component of your application!

These days I find spinning up a real PostgreSQL database to run the tests is so easy there's essentially no reason not to do it. I use GitHub Actions for my PostgreSQL testing insurance, and it ends up just being a few extra lines in the YAML file.

I agree with Simon. I think you could run tests against SQLite locally so they're quick but then run them against Postgres in CI to ensure it works against the database you're running on. There's a lot of subtle differences in how the two databases work even though they both support a lot of the same SQL syntax.

You could also run SQLite in production. Then you don't have to test against a different database locally. :)

Why not just use a separate schemas on the same postgres server?

I normally scp the db files down to my machine and use Table Plus locally.

Works well if you are not trying to watch real time processes as they write to the db.

Reply on news.ycombinator.com