[ANN] shadow-pgsql: PostgreSQL without JDBC

[Thomas Heller]

Hey Clojure Folk,

I'm close to releasing the first alpha version of https://github.com/thheller/shadow-pgsql a "native" interface to PostgreSQL I wrote.

Its an implementation of the native binary protocol without any intent to ever support JDBC. Mostly because that provides a bunch of features I never use, but no support for features I wanted. It is mostly Java but I will probably only use it from Clojure so that is my primary goal going forward. I think the Java bits are close to stable.

I'm looking for interested beta testers and feedback. I'm bad at writing docs cause I never know where to start since there are so many features and differences to JDBC.

I have an example repl session here to get started:

https://github.com/thheller/shadow-pgsql/blob/master/src/examples/repl.clj

I would not recommend using it on "important" data yet cause it might very well eat it all. My goal is to move my projects to shadow-pgsql over the next few months, so a 1.0 should not be very far off.

Anyways, I'm happy to answer questions if anyone is interested.

Cheers,

/thomas

[Kyle Cordes]

On Thursday, August 21, 2014 at 1:00 PM, Thomas Heller wrote:
> Hey Clojure Folk,
>
> I'm close to releasing the first alpha version of https://github.com/thheller/shadow-pgsql a "native" interface to PostgreSQL I wrote.
>
> Its an implementation of the native binary protocol without any intent to ever support JDBC. Mostly because that provides a bunch of features I never use, but no support for features I wanted. It is mostly Java but I will probably only use it from Clojure so that is my primary goal going forward. I think the Java bits are close to stable.
>
> I'm looking for interested beta testers and feedback. I'm bad at writing docs cause I never know where to start since there are so many features and differences to JDBC.
>

As a user of both Postgres and Clojure, I find this very interesting. It’s helps with a couple of pain points around JDBC, such the fact that any nonstandard feature ends up hidden behind a untyped interface passing strings around. But I also have a couple of bits of feedback that are a little more skeptical:

First, the amount of work it will take to get this to a complete enough state that large projects could safely switch to it, could be substantial. It makes me wonder if, instead, this could be built as a layer up on top of the Postgres JDBC driver. This would not be as elegant because it would not strip out as much unnecessary code, but it may be quite a lot less work.

Second, it seems to most effectively target people who are both very type oriented, yet are using Java or Clojure. It seems to me that folks who are so concerned with types that they would step away from the standard way of talking to databases generically, might be found over in the community of people using more rigidly typed languages like Haskell etc.

Third, although I like the idea of leveraging the features of the tool you are using (like Postgres), at the same time experiences taught me that, the more firmly a project seems destined to never switch to a different brand of database, the more likely some future unexpected opportunity will come up where that is exactly what is needed. I suppose this is just Murphy’s Law.

I don’t want to sound discouraging though, I really like this idea.

--
Kyle Cordes
http://kylecordes.com

[Thomas Heller]

Hey Kyle,

thanks for the Feedback. Appreciate it.

I think you misunderstood the meaning of a "type" in shadow-psql. A "type" is merely the format of how a given value is represented on the wire since the backend needs to understand what we send it. Postgres supports 2 different Wire Formats: Text and Binary. While Text is considered the "default", binary is usually a lot more efficient. pgjdbc for example only supports the text format. I try to be binary first, which works for most types so far. (Numeric is giving me trouble, but I'll eventually figure that out). I allow overwriting the "types" cause not everything I store in postgres is understood by it (EDN, Keywords, ...). By hooking directly into the encode/decode code I can efficiently do the transformation on-the-fly. In my completely unscientific preliminary benchmark I cut the query time from pgjdbc 650ms to shadow-pgsql 200ms and that is for very simple types (50k rows) with no optimizations done yet. I expect the difference to be much larger if you use a timestamp, timestamptz or bytea for example, as the text format for those types carries a bit more overhead. But once everything is stable I will do some real benchmarks. Better performance was not the reason I wrote this, just a pleasant side-effect.

As for the amount of work: its pretty much done. Some more exotic features need to be implemented, but those were never available via JDBC anyways (eg. COPY). I think its stable enough that I will begin moving my projects "soon", when I release everything to production I'll probably release a 1.0.0-RC. shadow-pgsql can not be layered on top of JDBC, well technically thats what I did for the last 2 years (https://gist.github.com/thheller/de7ecce0db58130ae6b7) BUT it required some ugly reflection calls since the PGJDBC does not expose all the information I needed. In the end I decided that I'd feel better to rewrite everything from scratch as the documentation is quite good and the protocol is simple.

Since the non-Java world does just fine without JDBC, I think we can do too. ;) Also, the "Illusion" JDBC provides that you can just switch databases if you feel like it only holds until you start using postgres-specific features. Not all databases have arrays, hstore or json types.

Regards,

/thomas

[Jason Jackson]

This makes me wonder if it would make sense to create a Postgresql
plugin that adds a keyword type and other similar types, that would
allow for a more precise roundtrip between Clojure and Postgres.

Jason

> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@googlegroups.com
> Note that posts from new members are moderated - please be patient with your
> first post.
> To unsubscribe from this group, send email to
> clojure+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en
> ---
> You received this message because you are subscribed to the Google Groups
> "Clojure" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to clojure+u...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Thomas Heller]

EDN/Transit on the backend might be nice, but a little much for me. I'll take it if you feel like a lot of C. jsonb in 9.4 will have to do for now.

Transparent encoding/decoding is already built-in to the client though, even with array support to encode a clojure set to postgres text[] and back.

See:

https://github.com/thheller/shadow-pgsql/blob/master/src/examples/repl.clj

https://github.com/thheller/shadow-pgsql/blob/90639eabc9f70ab9f8898b47088e7c07baa4f9d7/test/shadow/pgsql_test.clj#L45-L63

edn-type is in there too, just need to start writing proper tests and documentation.

Cheers,

/thomas 

[Shashy Dass]

What does the library do for connection pooling?

Thanks,

Shashy

[Thomas Heller]

http://commons.apache.org/proper/commons-pool/ 2.2

The clojure end should manage connection handling for you, if you need to keep a connection open (or transactions) there are

(shadow.pgsql/with-connection db
  ...)

and

(shadow.pgsql/with-transaction db

  ...)

macros, you should never actually need to touch the Connection object yourself.

For Java the intended interface is shadow.pgsql.DatabasePool.withConnection(DatabaseTask ...).

HTH,

/thomas

[Ludwik Grodzki]

Hi.

Have you tried it with Amazon's redshift?

Regards.

Ludwik.

[Thomas Heller]

Hey,

No. I was not aware that redshift is PostgreSQL under the hood.

Based on the docs it is based on PostgreSQL 8.0.2 and might not support some features I use to get the type thing working. It might be possible to make it work but you'll probably lose most of the features I implemented shadow-pgsql for in the first place. Will give it a try just for kicks when I find the time. But my primary Goal is good old PostgreSQL.

Cheers,

/thomas

--

You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clo...@googlegroups.com
Note that posts from new members are moderated - please be patient with your first post.
To unsubscribe from this group, send email to
clojure+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---

You received this message because you are subscribed to a topic in the Google Groups "Clojure" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/clojure/9uxhrXmufU0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to clojure+u...@googlegroups.com.

[Thomas Heller]

I meant to do a proper release announcement for quite some time now but simply don't have the time to do it properly.

That being said I have now been using shadow-pgsql [1] in production for well over a month and it has been working as expected and "stable". A couple million Queries were executed successfully and no major issues have come up. There are still some features missing but if you only need basic SQL features it should all work out.

Happy to talk about it if anyone is interested.

Hope to find some time next month to write some docs and cut a proper release.

Cheers,

/thomas

[1] https://github.com/thheller/shadow-pgsql