I used Postgres since 1995 ( [PG95] mailing list has less than 400 subscribers !) for my scientific projects and operating with numbers and english strings was smooth, but in 1996 I signed up to make a searchable database of electronic archive of "Uchitelskaya gazeta" ( the popular newspaper for teachers community). It was my moonlight work to help ( Read more... )

After ten years of working with postgres ( actually, I started with Ingres, then postgres95), I and Teodor got a chance to see live other developers.  We presented there GIN and FTS.  Pictures from the Anniversary Summit at at Ryerson University in Toronto can be  viewed on the flickr under name  PGCon-2006, I hope many of us will enjoy looking ( Read more... )

Andrew Dunstan committed SQL_JSON - one of the most awaited part of remaining part of SQL/JSON standard, which our team in Postgres Professional were working since early 2017, the first version was presented in our talk at PGConf.eu - 2017. Kudos, Andrew, you helped us with committing jsonb and help now ( Read more... )

( Read more... )

We have submitted TOAST API for PG15.

Below is a performance analysis of JSONB, which uses JSONB TOASTER implemented using TOAST API.

Each jsonb looks like: key1, looong key2[], key3, loong key4[]. Check slide #10 of our talk at pgvision 2021 for description of data and queries used in benchmarks ( Read more... )

It's interesting to know how big is your jsonb and it's not trivial as it looks, since jsonb is a binary format and also may be TOASTed (compressed).

Assume, that jb - is an attribute of type jsonb, than

CREATE TABLE test (jb jsonb ( Read more... )

Inspired by commit support for partial TOAST decompression"When asked for a slice of a TOAST entry, decompress enough to return the slice instead of decompressing the entire object ( Read more... )

Bloom index by default works for int4 and text, but other types with hash function and equality operator could be supported. Just use opclass interface, for example, for type bigint

CREATE OPERATOR CLASS bigint_ops
DEFAULT FOR TYPE bigint USING bloom AS
OPERATOR 1 =(bigint, bigint),
FU>CTION 1 hashint8(bigint);
Now, you can ( Read more... )

Updated 30-05-2019:
We committed jsonpath to PostgreSQL 12 and decided to postpone SQL/JSON functions for the next release. Check my talk at PGCon 2019 http://www.sai.msu.su/~megera/postgres/talks/jsonpath-pgcon-2019.pdf

Updated 21-03-2017:
Refresh data for MySQL ( 8.0.4 version).
SQL-commands, we used for checking conformanceUpdated 10- ( Read more... )

This is a technical post to illustrate the house data set example, which I use in sqljson documentation.


( Read more... )