Contents
postgresbson
BSON support for PostgreSQL
Introduction
This PostgreSQL extension realizes the BSON data type, together with functions to create and inspect BSON objects for the purposes of expressive and performant querying.
BSON (http://bsonspec.org/) is a high-performance, richly-typed data carrier similar to JSON but offers a number of attractive features including:
- Datetimes, decimal (numeric), and byte[] are first class types. In pure
JSON these must all be represented as a string, requiring conversion,
potentially introducing lossiness, and impairing native operations
like
>and<=. - Performance. Moving binary BSON in and out of the database under some
conditions is almost 10x faster than using native
jsonborjsonbecause it avoids to- and from-string and to-dictionary conversion. - Roundtrip ability. BSON is binary spec, not a string. There is no whitespace, quoting rules, etc. BSON that goes into Postgres comes out exactly the same way, each time, every time.
- Standard SDK implementations in upwards of 20 languages
Roundtripping and many available language SDKs enables seamless creation, manipulation, transmission, and querying of data in a distributed system without coordinating IDLs, compile-time dependencies, nuances in platform type representation, etc. Here is an example of a typical processing chain: Java program -> message bus -> python util -> save to database -> other Java program wakes up on insert trigger:
- Java program constructs an
org.bson.Document(which honorsjava.util.Mapinterface), e.g.doc.put("name", "Steve"),doc.put("balance", new BigDecimal("143.99"))etc. - Java program encodes
org.bson.Documentusing Java BSON SDK to a BSONbyte[]. - Java program publishes
byte[]to a Kafka topic. - python listener wakes up on topic and receives
byte[]. - python listener decodes
byte[]using python BSON SDK intodict, not a string. It is a fully reconstituteddictobject with substructures,datetime.datetimefor date fieldss,Decimalfor penny-precise fields, etc. The listener prints some things but does not change anything in the dict. - python listener encodes
dictto back tobyte[]andINSERTs it to a BSON column in Postgres using thepsycopg2module. - A different Java program wakes up on a Postgres insert trigger and
SELECTs the BSON column as abyte[](e.g.select bson_column::bytea where ...) - This
byte[]is identical to the one created in step 2.
Another example of roundtrip ability involves hashes and digital signatures. BSON offers an easy, robust, and precise means to associate arbitrarily complex data with hashes thereof in a way that is basically not possible or certainly not reliable using JSON or JSONB:
# Create complex data. Note array g is polymorphic:
amt = bson.decimal128.Decimal128(Decimal("-10267.01"))
data = {"dt":datetime.datetime.utcnow(),"amt":amt,"msg":"Hello!","g":["2",2,2.0,{'v':2}]}
# Probably in a util function, take data and associate it with metadata.
# Get the SHA2 of this data:
raw = bson.encode(data)
h2 = hashlib.sha256(raw).hexdigest()
meta = {'security':{'hash':{'algo':'SHA256','v':h2}}}
doc = {'d': data,'m': meta}
# Encode the *whole* doc (data + meta) and insert into BSON column:
rb2 = bson.encode(doc)
curs.execute('insert into foo (bson_data) values (%s);', (rb2,))
conn.commit()
# -- L A T E R --
# Pull the material back out of the database. Be sure to cast to
# bytea so the psycopg2 does not try to turn it into an EJSON string!
curs.execute("select bson_data::bytea from foo;")
all_recs = curs.fetchall()
# bytea comes in as type 'memoryview' for efficiency; get the bytes
# from row 0, column 0:
r = bytes(all_recs[0][0])
doc2 = bson.decode(r, codec_options=CodecOptions(document_class=collections.OrderedDict))
# Extract the stored hash value:
h3 = doc2['m']['security']['hash']['v']
# REHASH the data portion:
r2 = bson.encode(doc2['d'])
h4 = hashlib.sha256(r2).hexdigest()
if h3 == h4:
print("rehash of data OK")
else:
print("WARN: rehash of data does not match associated hash")
The extension offers two kinds of accessor suites:
- Typesafe high performance accessor functions that take a dotpath notation
to get to a field e.g.
select bson_get_datetime(bson_column, 'msg.header.event.ts') from table; - Arrow and double arrow operators similar to those found in the JSON type e.g.
select (bson_column->'msg'->'header'->'event'->>'ts')::timestamp from table;
The BSON type is castable to JSON in so-called EJSON format to preserve type fidelity. Thus, the wealth of functions and operations and even other extensions built around the JSON type can be used on BSON. These of course can be combined:
-- Use dotpath to quickly get to event substructure, then cast to jsonb and
-- use ?& operator to ask if both `id` and `type` are present as top level tags:
select (bson_get_bson(bson_column, 'msg.header.event')::jsonb) ?& array['id','type'] from table;
Why dotpath accessors are better in BSON (and native json and jsonb, too)
In general, the dotpath functions will be much faster and memory efficient
especially for larger and/or deeper structures. This is because the dotpath
implementation in the underlying C library itself will “walk” the BSON structure
and only vend allocated material at the terminal of the path. The arrow operators
necessitate the construction of a fully inflated substructure at each step in
the path, which is exactly what happens with arrow operators and the native
json and jsonb types. For example, consider this instance with approx. 3K of
data, from which we want to query where id is AAA:
{
header: { event: {id: "E123"} },
data: {
payload: {
product: {
definition: {
id: "AAA",
... another 1K of data ...
},
constraints: {
... another 1K of data ...
},
approvals: {
... another 1K of data ...
}
}
}
}
}
The dotpath method will make postgres pass the 3K structure to the BSON extension (internally of course, not back to the client!), which will only have to examine roughly 64 bytes of data to dig down to the id field to return a string which is then examined for equality to AAA:
select * from table where bson_get_string(bson_column, 'data.payload.product.definition.id') = 'AAA';
With arrow operators, at each arrow, almost 3K of data has to be processed:
Remember double arrow operator yields text type, which fortunately is easily
compared to a literal string; no casting necessary, but the journey there is
tough because each single arrow forces construction of a whole new object to
pass to the next stage. This happens internal to the engine but still...
select * from table where
bson_column->'data'->'payload'->'product'->'definition'->>'id' = 'AAA';
^ ^ ^ ^ ^ ^
| | | | | |
+- initial pull of a little more than 3K | |
| | | | |
+- almost 3K reprocessed | |
| | | |
+- another 3K reprocessed| |
| | |
+- another 3K | |
| |
+- about 1K here
|
+- a handful of bytes
Total: about 13K of data in 4 separate pass **and** construct chunks of 3K processed to extract 3 bytes.
Again, this is exactly the same situation that occurs with native json and jsonb
types using the arrow operators; it is not particular to BSON. This is why postgres
provides the #> operator and the corresponding json_extract_path and jsonb_extract_path functions for these native types.
Arrays, BSON, and JSON
Unlike JSON, BSON can only be created from a key:value object (which can be empty); it cannot be created from a simple scalar or in particular an array. In the course of descending into substructure, it is of course possible to encounter an array and have that returned. The BSON spec gets around this problem by returning a string indexed object where the keys are the integer representation of the location in the array starting at offset zero. From the CLI, this will be seen as:
select bson_column->'d'->'payload'->'vector' from ...
returns {"0":"some value", "1":"another value", "2":{fldx:"a rich shape"}}
not ["some value", "another value", {fldx:"a rich shape"}]
The postgres array type does not help here because postgres requires a homogenous type and BSON allows for heterogenous types.
There are three ways to deal with this:
-
If you don’t need the whole array, access individual items using strings; note the double arrow and the quotes around ‘0’:
select bson_column->'d'->'payload'->'vector'->>'0' from ... -
“Back up one level” in the arrow chain and cast to
jsonb:mydb=# select (bson_column->'d'->'payload')::jsonb->'vector' from ... ?column? -------------- [21, 17, 19] Note no quotes around 0 because now it is jsonb: mydb=# select (bson_column->'d'->'payload')::jsonb->'vector'->>0 from ... ?column? -------------- 21 -
Use the
bson_get_jsonb_arrayfunction:mydb=# select bson_get_jsonb_array(bson_column,'d.payload.vector') from ... ?column? -------------- [21, 17, 19] mydb=# select bson_get_jsonb_array(bson_column,'d.payload.vector')->0 from ... ?column? -------------- 21The subtle but at times very important benefit of
bson_get_jsonb_arrayover approach #2 above is that the dotpath will quickly and efficiently navigate to just thevector. The issue with “backing up one level” is that ifpayloadis very big, the whole structure must be converted tojsonbonly to pull out what could be a smallvector.Remember: For objects, if you wish to use the
jsonbfunctions, simply cast tojsonb:mydb=# select (bson_column,'d.payload')::jsonb ...
Status
Experimental. All contribs / PRs / comments / issues welcome.
Example
CREATE EXTENSION pgbson;
CREATE TABLE data_collection ( data BSON );
-- Programmatic insert of material through postgres SDK e.g. psycopg2
-- can use native types directly; this is this high-fidelity sweet spot
-- for the BSON extension:
import bson
import psycopg2
import datetime
conn = psycopg2.connect(DSN) # 'host=machine port=5432 dname=foo .... '
sdata = {
"header": {
"ts": datetime.datetime(2022,5,5,12,13,14,456),
"evId":"E23234"
},
"data": {
"id":"ID0",
"notIndexed":"N0",
# BSON decimal128 adheres more strongly to IEEE spec than native
# python Decimal esp. wrt NaN so we must use that. We are
# encoding to BSON later anyway so no extra dependencies here.
"amt": bson.decimal128.Decimal128("107.78"),
"txDate": datetime.datetime(2022,12,31),
"userPrefs": [
{"type": "DEP", "updated":datetime.datetime(2021,4,4,12,13,14),"u":{
"favoriteCar":"Bugatti",
"thumbnail": bson.binary.Binary(bytes("Pretend this is a JPEG", 'utf-8'))
}},
{"type": "X2A", "updated":datetime.datetime(2021,3,3,12,13,14),"u":{
"listOfPrimes":[2,3,5,7,11,13,17,19],
"atomsInBowlOfSoup": 283572834759209881,
"pi": 3.1415926
}}
]
}
}
# raw_bson is byte[]. BSON is castable to/from bytea type in PG.
# See pgbson--2.0.sql about byte[] validation to ensure that the bytea
# being stored is real BSON not malformed junk. Note that null handling is
# the same as for regular types and the to/from JSON and validation machinery
# is NOT invoked. In other words:
#
# mydb=# insert into bsontest (bdata,marker) values (null,'hello!');
# INSERT 0 1
# mydb=# select bdata,marker from bsontest where bdata is null;
# bdata | marker
# -------+--------
# | hello!
# (1 row)
# mydb=# select bdata->'d'->'payload',marker from bsontest where bdata is null;
# OR mydb=# select bdata::bytea,marker from bsontest where bdata is null;
# OR mydb=# select bdata::json,marker from bsontest where bdata is null;
# ?column? | marker
# ----------+--------
# | hello!
# Following a path from a NULL BSON still produces NULL.
raw_bson = bson.encode(sdata)
curs = conn.cursor()
curs.execute("INSERT INTO bsontest (data) VALUES (%s)",(raw_bson,))
conn.commit()
curs.execute("""
SELECT
2 * bson_get_decimal128(data, 'data.amt'),
4 + bson_get_datetime(data, 'data.txDate')::date
from bsontest
""")
rr = curs.fetchall()[0] # one row
print("amt: ", rr[0], type(rr[0])) # amt: 215.56 <class 'decimal.Decimal'> fetched as relaxed native Decimal type
print("txDate: ", rr[1], type(rr[1])) # txDate: 2023-01-04 <class 'datetime.date'>
-- EJSON is recognized upon insertion. For example, the $date substructure
-- "ts": {"$date":"2022-03-03T12:13:14.789Z"}
-- is converted upon parse to a single scalar of type timestamp:
-- "ts": timestamp
INSERT INTO data_collection (data) values (
'{"d":{
"recordId":"R1",
"notIndexed":"N1",
"baz":27,
"bigint":{"$numberLong":"88888888888888888"},
"dbl":3.1415,
"ts": {"$date":"2022-03-03T12:13:14.789Z"},
"amt": {"$numberDecimal":"77777809838.97"},
"payload": {
"fun":"scootering",
"val":13,
"vector":[21,17,19],
"image" : { "$binary" : { "base64" : "VGhpcyBpcyBhIHRlc3Q=", "subType" : "00" } }
}
}
}'
);
-- Functional indexes work as well, enabling high performance queries
-- into any part of the structure; note the dotpath in the example below:
CREATE INDEX ON data_collection( bson_get_string(data, 'd.recordId'));
-- ... and those indexes help. Queries on peer fields in a substructure,
-- one with an index, one without, yields nearly a 10000x improvement in
-- performance.
select count(*) from data_collection;
count
---------
1000000
explain analyze select count(*) from btest where bson_get_string(data,'d.recordId') = 'R31';
---- QUERY PLAN
Aggregate (cost=8.45..8.46 rows=1 width=8) (actual time=0.076..0.077 rows=1 loops=1)
-> Index Scan using btest_bson_get_string_idx on btest (cost=0.42..8.44 rows=1 width=0) (actual time=0.070..0.071 rows=1 loops=1)
Index Cond: (bson_get_string(data, 'd.recordId'::cstring) = 'R31'::text)
Planning Time: 0.296 ms
Execution Time: 0.108 ms
explain analyze select count(*) from btest where bson_get_string(data,'d.notIndexed') = 'N31';
Aggregate (cost=215012.50..215012.51 rows=1 width=8) (actual time=993.471..993.472 rows=1 loops=1)
-> Seq Scan on btest (cost=0.00..215000.00 rows=5000 width=0) (actual time=0.124..993.464 rows=1 loops=1)
Filter: (bson_get_string(data, 'd.notIndexed'::cstring) = 'N31'::text)
Rows Removed by Filter: 999999
Planning Time: 0.079 ms
Execution Time: 993.505 ms
-- These are equivalent queries but the dotpath accessors will be
-- significantly faster (and much more memory efficient) if the path depth > 3.
-- Note of course that the BSON accessor functions can be used in
-- a selection set (select), a predicate (where), or essentially anywhere else.
select bson_get_bson(data, 'd.payload') from btest where bson_get_string(data,'d.recordId') = 'R1';
select data->'d'->'payload' from btest where bson_get_string(data,'d.recordId') = 'R1';
?column?
---------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------
{ "fun" : "scootering", "val" : 13, "vector" : [ 1, 2, 3 ], "fancy" : [ { "A" : 1 }, { "B" : [ "X", "Z" ] } ], "image" : { "$binary" : { "base64" : "VGhpcyBpcyBhIHRlc3Q=", "subType" : "00" } } }
-- The ->> operator, like JSON, yields a text type, which you can cast
-- whatever you believe is appropriate
select (data->'d'->>'amt')::numeric as my_amount from btest where bson_get_string(data,'d.recordId') = 'R1';
my_amount
----------------
77777809838.97
Building
Requires:
- postgres development SDK (mostly for
.hfiles in.../postgresql/server). On OS X you can usebrew. On RH 8 it is a little trickier because many repos do not have version 14.x. Here is a a good step-by-step install of 14.4 for RH 8 Note you will need both server and development (e.g.postgresql14-devel) packages because you need.hfiles. It is not necessary to do a complete build of postgres. pg_config(which comes with postgres) and is used as part of the Makefile. Note that some earlier versions of postgres did not not include thepg_configexec and thepgxsenvironment.libbson.soand BSON C SDK.hfiles. You can make or install these separately and there is plenty of material on this topic. There are some quick tips at the top of theMakefile- C compiler. No C++ used. The compiler arguments are driven by the
environment set up by
pg_config.
Then:
git clone https://github.com/buzzm/postgresbson.git
# edit the Makefile to point at the BSON includes and dynamic lib; then:
make PGUSER=postgres # compiles pgbson.c to pgbson.so
make PGUSER=postgres install # copies .so, .sql, and .control files into target dirs in postgres environment
There are too many build directory permissions, install directory permissions,
and other local oddments to document here, but
neither postgres nor root privilege is required to compile and link the shared
lib but installation in your particular environment will vary. In general,
on OS X using brew you won’t need root because root does not own /opt/homebrew
but on Linux, lots of things are done with sudo yum and resources end up
owned as root in /usr/pgsql-nn and /usr/lib64.
In addition, on RHEL 9, there appears to be an oddment around the compilation
target in make trying to create a .bc LLVM file using clang in addition to
the regular .so shlib using gcc. On many platforms, clang may not be
installed and even if it is, the pg_config modified Makefile may use the
wrong path to it e.g. /usr/lib64/ccache/clang instead of /usr/bin/clang or
just clang. The good news it appears the .bc LLVM output is not necessary
for the postgres extension. As long as the .so is installed into the proper
path the extension will work. At your discretion you can “manually make” the
.bc file by editing the command line and then running make PGUSER=postgres install again.
Make sure you install and then restart your postgres server to properly pick up the new BSON extension.
Testing
Tested on
-
PG 16.3:
- OS X 14.3.1 Sonoma; Apple clang version 15.0.0 (clang-1500.1.0.2.5)
- RHEL 9.3
-
PG 16.1.3:
- OS X 14.3.1 Sonoma; Apple clang version 15.0.0 (clang-1500.1.0.2.5)
- OS X 13.2 Ventura
- RHEL 9.3
-
PG 15.5.3: OS X 13.2 Ventura, RHEL 9.3
-
PG 14.4: OS X 10.15.7, OS X 13.2 Ventura, and RHEL 8.6.
# Make sure postgresql16-devel (or 14 or 15) is installed *first*.
# pip3 install psycopg2 uses pg_config; thus the PATH must also be set!
PATH=/path/to/pgsql-16/bin/pg_config:$PATH pip3 install psycopg2
pip3 install pymongo # for bson only; we won't be using the mongo driver
python3 pgbson_test.py
See excuse at top of file regarding the non-standard test driver approach.
Quick reference
The module defines BSON data type with operator families defined for B-TREE and HASH indexes.
Field access (supports dot notation):
-
bson_get_string(bson_column, dotpath) RETURNS text
-
bson_get_int32(bson_column, dotpath) RETURNS int4
-
bson_get_int64(bson_column, dotpath) RETURNS int8
-
bson_get_double(bson_column, dotpath) RETURNS float8
-
bson_get_decimal(bson_column, dotpath) RETURNS numeric
-
bson_get_datetime(bson_column, dotpath) RETURNS timestamp without time zone
-
bson_get_binary(bson_column, dotpath) RETURNS bytea
-
bson_get_boolean(bson_column, dotpath) RETURNS boolean
-
bson_get_bson(bson_column, dotpath) RETURNS bson
-
bson_get_jsonb_array(bson_column, dotpath) RETURNS jsonb
-
bson_as_text(bson_column, dotpath) RETURNS text
Operators and comparison:
- Operators: =, <>, <=, <, >=, >, == (binary equality), <<>> (binary inequality)
- bson_hash(bson) RETURNS INT4
TO DO
- Significantly tidy up test driver
- Need something better when extracting arrays.
- Need something better when bson_get_bson() resolves to a scalar because simple scalars like a string are not BSON. Currently, it just returns NULL which is “technically correct” but unsatisfying
- Need additional safety checks when doing BSON compare and other operations because corrupted BSON has a tendency to segfault the PG process.
- Need more docs or a more clever solution for when calling
bson_get_{type}points to a field that exists but the type is wrong. Currently it just returns null because that is “safest.”
See also
- PostgreSQL - http://www.postgresql.org/
- BSON - http://bsonspec.org/
- MongoDB - http://mongodb.org