[HN Gopher] Show HN: BSON Extension for Postgres
___________________________________________________________________
Show HN: BSON Extension for Postgres
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.
Author : buzzm
Score : 92 points
Date : 2024-01-15 13:46 UTC (1 days ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| hnto_pics wrote:
| That's the first time I hear of BSON. How does this compare to
| cbor and messagepack?
| miohtama wrote:
| It started as internal MongoDb format, so it has a different
| history and context. But today comparison might be interesting.
| Joel_Mckay wrote:
| 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 licensee, and propensity to implode
| key structures on upgrades. =)
| eddd-ddde wrote:
| 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?
| pella wrote:
| 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?
| lxe wrote:
| Why BSON and not JSONB, which is already supported in Postgres?
| robertlagrant wrote:
| It says in the posted comment.
| lolinder wrote:
| 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.
| cryptonector wrote:
| JSONB is optimized for traversal and compressibility. Is BSON
| so-optimized too?
| lolinder wrote:
| It originated with MongoDB as their primary storage and
| interchange format, so I'd assume it at least was _meant_
| to be optimized for that purpose.
|
| Either way, like I said, JSONB and BSON are solving
| slightly different problems--you probably would not choose
| BSON if you don't need the extra data types, and if you
| _do_ need the extra data types JSONB can 't help you any
| more than JSON can.
| halayli wrote:
| JSONB is not optimized for compression.
| mattashii wrote:
| > 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)
| lolinder wrote:
| Fair. A charitable interpretation would be that they're
| referring to the fact that in BSON you can specify the
| exact number type you want to use [0], whereas in JSONB you
| just have one shared number type for all number fields.
|
| [0] https://www.mongodb.com/docs/manual/reference/bson-
| types/
| boomskats wrote:
| Blast from the past.
| https://news.ycombinator.com/item?id=7457645
| ccleve wrote:
| 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.
| vvpan wrote:
| 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.
| SahAssar wrote:
| 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.
| dsizzle wrote:
| 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?
___________________________________________________________________
(page generated 2024-01-16 23:01 UTC)