LWN: Comments on "Highlights from the PostgreSQL 9.2 beta"

Awesome!

ringerc — Tue, 22 May 2012 05:34:50 +0000

Portability is an issue for Pg, particularly where intrinsics or inline asm are used. Pg is built with gcc, llvm/clang, icc, mingw/gcc-win32, and many other compilers. It runs on x86, x64, ppc32, ppc64, sparc64, mips, s390, and more. It is expected to support a wide set of platforms as per the buildfarm (http://buildfarm.postgresql.org/).

Of course, not all those need fancy fast asm/intrinsics based speedy parsers. A portable, safe implementation in plain old C does the job fine for most platforms; Pg's build system is designed to selectively build files depending on target platform so optimised versions can be used where available and a general fallback implementation where not.

A big concern to me with using a new JSON optimised/complex implementation would be the need to maintain it, ensuring it was secure, reliable, and stable. Especially in "fast" code it's hard to ensure that everything is checked and safe. Pg users expect bad input to cause a polite error message not a backend crash, so any kind of horrible corrupt JSON you can imagine must be coped with cleanly.

Of course, Pg already has a roll-your-own JSON implementation, so it'd be cool to have something faster so long as it still focused on safety first.

excellent

wingo — Thu, 17 May 2012 08:09:12 +0000

Just echoing my kudos to the postgresql team. Also, a fine article; thanks!

Awesome!

Cyberax — Wed, 16 May 2012 12:23:40 +0000

Right now it's "no way in hell I'm going to release it in this state". It'll be BSD once I refactor it into something generally usable.

Highlights from the PostgreSQL 9.2 beta

jberkus — Wed, 16 May 2012 02:26:13 +0000

It's always cool to find out what's really the "killer feature" for specific people. Thanks!

Highlights from the PostgreSQL 9.2 beta

ringerc — Wed, 16 May 2012 02:15:22 +0000

A huge and important change isn't mentioned in the article, but will make a massive difference to lots of Pg users, eliminate a really common mailing list FAQ, and greatly improve performance in several very common use cases. From the relnotes:

Improve the ability of the planner to choose parameterized plans (Tom Lane)

A prepared statement is now parsed, analyzed, and rewritten, but not necessarily planned. When the prepared plan is executed with parameters, the planner might replan it for every constant, or it might execute a generic plan if its cost is close to that of a constant-specific plan. CLARIFY"

If you're wondering why this is important: Say you have a table with a very common value and an uncommon value. You run queries that filter on the uncommon value, relying on an index to avoid scanning the rest of the table. Everything goes well until you convert it to a parameterised prepared statement - perhaps even one created automatically behind the scenes by PgJDBC or similar. Suddenly queries take hundreds of times as long and you don't know why. Eventually, using auto_explain, or an explicit PREPARE followed by ANALYZE EXECUTE, you find out that the prepared statement version of your query isn't using the index. WTF?

Pg was planning prepared statments at PREPARE time. If the value for a parameter wasn't known, it couldn't use the stats for the column to find out if it was a very common value or an uncommon one. It'd often land up choosing a conservative approach that left it using a seqscan instead of an index scan, especially if the server was tuned with the default weights for seq_page_cost and random_page_cost. Much wailing, gnashing of teeth resulted, along with aggressively unrealistic random_page_cost settings and pleading for query hints.

Now, all that should be gone! This is awesome, and a huge usability/performance win. Thanks so much Tom.

Awesome!

jberkus — Tue, 15 May 2012 15:03:08 +0000

Cyberax,

What's the license of the parser?

Awesome!

nteon — Tue, 15 May 2012 14:42:56 +0000

is the source for that online somewhere? I'd love to see it :)

Awesome!

Cyberax — Tue, 15 May 2012 13:54:56 +0000

Right now I probably have the world's fastest JSON parser. It uses SSE instructions and bitslicing and is ridiculously fast - on the order of strlen speed. Of course, it's totally ridiculous but it was fun to write.

I was thinking about binary serializing JSON (I definitely don't like BSON). Simply tagging strings with length and not bothering about escaping helps greatly and simplifies parsing greatly. It also can be easily plugged into existing parsers. Type-specific tagging (int4, int8, bigint, float, string, map) would also be welcomed, but there are some fine points with it.

Awesome!

Cyberax — Tue, 15 May 2012 13:48:58 +0000

Adding custom types to JPA is not hard. It's just implementation-specific so you have to do it for Hibernate (EclipseLink, whatever) separately.

Basically, for the application programmer it'd look like:
>@Column(type='json')
>public JSON getColumn() {...}

Awesome!

jberkus — Tue, 15 May 2012 13:17:05 +0000

The current PostgreSQL JSON type is a varlena rather than a binary type. So you'd need to go through a conversion anyway.

We discussed having a binary JSON type as well, but without a protocol to transmit binary values (BSON isn't at all a standard, and has some serious glitches), there didn't seem to be any point.

If you're interested in working on binary JSON support for PostgreSQL, we'd be interested in having you help out ...

Awesome!

ringerc — Tue, 15 May 2012 13:03:29 +0000

The trouble with that is that custom Hibernate types seem to be difficult to use through JPA. Or am I just misinformed? (Please be so).

Hacking the driver to lie about the type and auto cast to/from text is ugly, but preserves the ability to use server validation etc with no changes higher in the client.

I would really love to be able to use JSON to store a serialized tree that got unpacked into an object graph using JAXB and jaxxon or similar, so instead of a 'json' entity field in the mapping you could use the root of (or a collection of) your custom jaxb annotated objects. That would take a lot more brains on the ORM though.

Awesome!

Cyberax — Tue, 15 May 2012 12:33:06 +0000

That'd be great! I'm going to add JSON custom types to Hibernate.

Awesome!

ringerc — Tue, 15 May 2012 12:25:19 +0000

Covering indexes will be a real plus, especially at a relatively low write cost. They will really help with some long standing Pg pain points.

Very glad to see JSON types too. Iwill have to clean up and submit my JDBC driver patch that lets you remap types in the cocnnection properties so the driver can be told to report 'xml', 'json' etc as 'text' and avoid confusing front ends that don't understand them.

Highlights from the PostgreSQL 9.2 beta

fuhchee — Tue, 15 May 2012 00:55:33 +0000

Good work by the postgresql team.