Show HN: BSON Extension for Postgres (opens in new tab)

(github.com)

115 pointsbuzzm2y ago27 comments

JSON support in postgres is superb but sometimes you really want decimal, date, and binary types, "carefree" UTF8 string handling (i.e. no escaping), and robust roundtrippability. So I made an extension for BSON.

27 comments

ccleve2y ago

The code here is really well designed. This project can serve as a tutorial on how to build a Postgres extension. Have a look at this:

https://github.com/buzzm/postgresbson/blob/main/pgbson--2.0....

and this:

https://github.com/buzzm/postgresbson/blob/main/pgbson.c

Really nice stuff.

hnto_pics2y ago

That's the first time I hear of BSON. How does this compare to cbor and messagepack?

Joel_Mckay2y ago

For cross-platform transactional Queues we found bson rather reliable as a message body format in AMQP over SSL, and tag the protocol handler with the user UUID and API session key. The uptime on that system is nearing 4 years, and its the one part of the system that has been low drama.

It is not meant for large reporting formats like live charts etc. These parsers can be unstable if they ignore type checks, precedence, and nesting depth.

The original libbson author wouldn't give me a donation link on inquiry, as they probably thought it was some sort of scam. The library merged into mongoDB years ago. Would not touch mongoDB now days given its current license, and propensity to implode key structures on upgrades. =)

miohtama2y ago

It started as internal MongoDb format, so it has a different history and context. But today comparison might be interesting.

lxe2y ago

Why BSON and not JSONB, which is already supported in Postgres?

lolinder2y ago

They have similar names but solve completely different problems. JSONB is just a binary representation of JSON, with the same limited set of data types. It doesn't provide "decimal, date, and binary types", which OP identified as part of the draw to BSON.

cryptonector2y ago

JSONB is optimized for traversal and compressibility. Is BSON so-optimized too?

3 more replies

mattashii2y ago

> It doesn't provide "decimal [...] types", which OP identified as part of the draw to BSON.

This part is misinformation, as json[b] works perfectly fine with decimals (well, Numeric, which is an arbitrary precision decimal type)

2 more replies

boomskats2y ago

Blast from the past. https://news.ycombinator.com/item?id=7457645

robertlagrant2y ago

It says in the posted comment.

eddd-ddde2y ago

This is really cool. A common use case for me is building JSON objects directly in my query, for example to return a list of json objects. Usually this means date columns lose their type, is there a way of returning bson like with jsonb_build_object that keeps this types?

vvpan2y ago

Perhaps this is an opportunity to ask somebody who might know about BSON performance. As a POC/stress test for work I added two multi-GB datasets to Postgres (as JSONB) and to Mongo (BSON). While trying to query approximately a hundred megabytes of data (a few hundred documents) from each I found that Postgres executed the query and decoded the JSON data in under a second, while it took Mongo a few seconds. Does this mean that BSON is slow to deserialize? Or perhaps it is not related to serialization? I was quite confused.

SahAssar2y ago

Did you do a EXPLAIN ANALYZE on the postgresql query (and perhaps mongodb if it has something similar)?

It might help to find if it was the actual query or the de-serialization that was the bottleneck.

dsizzle2y ago

Listed status is "experimental" but only two relatively minor commits in the last two years. Maybe it's more stable than that implies or the author is looking to pick it back up?

hanszarkov2y ago

I've used the BSON Java ObjectId for distributed primary key generation for a long time. Really useful for distributed systems.

salil9992y ago

It's specifically advised NOT to use ObjectId for distribution. This data type has a notion of time embedded in it which results in monotonically increasing ObjectIds. Use UUID or a hashed version of the ObjectId

pella2y ago

As I understand it, it was 'Tested using Postgres 14.4'.

I'm wondering if there are any plans to support Postgres versions 15 and 16?

buzzmOP2y ago

Retested on OS X 13.2 for both 15.5.3 and 16.1; bug discovered thanks to 16! Fixed and new version pushed.

buzzmOP2y ago

Considering a random brew install bumped my postgres to 14.10.1 and I didn't even notice until 45 mins ago, maybe I should.

j / k navigate · click thread line to collapse

27 comments

ccleve2y ago

The code here is really well designed. This project can serve as a tutorial on how to build a Postgres extension. Have a look at this:

https://github.com/buzzm/postgresbson/blob/main/pgbson--2.0....

and this:

https://github.com/buzzm/postgresbson/blob/main/pgbson.c

Really nice stuff.

hnto_pics2y ago

That's the first time I hear of BSON. How does this compare to cbor and messagepack?

Joel_Mckay2y ago

It is not meant for large reporting formats like live charts etc. These parsers can be unstable if they ignore type checks, precedence, and nesting depth.

miohtama2y ago

It started as internal MongoDb format, so it has a different history and context. But today comparison might be interesting.

lxe2y ago

Why BSON and not JSONB, which is already supported in Postgres?

lolinder2y ago

cryptonector2y ago

JSONB is optimized for traversal and compressibility. Is BSON so-optimized too?

3 more replies

mattashii2y ago

> It doesn't provide "decimal [...] types", which OP identified as part of the draw to BSON.

This part is misinformation, as json[b] works perfectly fine with decimals (well, Numeric, which is an arbitrary precision decimal type)

2 more replies

boomskats2y ago

Blast from the past. https://news.ycombinator.com/item?id=7457645

robertlagrant2y ago

It says in the posted comment.

eddd-ddde2y ago

vvpan2y ago

SahAssar2y ago

Did you do a EXPLAIN ANALYZE on the postgresql query (and perhaps mongodb if it has something similar)?

It might help to find if it was the actual query or the de-serialization that was the bottleneck.

dsizzle2y ago

Listed status is "experimental" but only two relatively minor commits in the last two years. Maybe it's more stable than that implies or the author is looking to pick it back up?

hanszarkov2y ago

I've used the BSON Java ObjectId for distributed primary key generation for a long time. Really useful for distributed systems.

salil9992y ago

pella2y ago

As I understand it, it was 'Tested using Postgres 14.4'.

I'm wondering if there are any plans to support Postgres versions 15 and 16?

buzzmOP2y ago

Retested on OS X 13.2 for both 15.5.3 and 16.1; bug discovered thanks to 16! Fixed and new version pushed.

buzzmOP2y ago

Considering a random brew install bumped my postgres to 14.10.1 and I didn't even notice until 45 mins ago, maybe I should.

j / k navigate · click thread line to collapse