Leveraging PostgreSQL 17's JSONB for Enhanced Data Modeling and Performance

JSONB has been part of PostgreSQL since the ancient version 9.4. PostgreSQL 17's lates JSONB improvements provides a flexible and efficient way to store and query semi-structured data. Its indexing capabilities and optimized storage mechanisms make it ideal for handling large volumes of complex data, such as configuration settings, user profiles, and log data. While NoSQL databases like MongoDB have gained popularity, recent breaking changes and performance issues have raised concerns about their long-term suitability as a primary data storage solution. PostgreSQL's mature ecosystem, strong community support and proven reliability offer a compelling alternative for handling complex data workloads. The audience will learn about the JSONB functionalities available in PostgreSQL and the latest improvements making it a powerful allied to write applications at scale.

Transcript

1. Leveraging jsonb in PostgreSQL 17 for max performance Incontro Devops Italia 2025 Federico Campoli Kamedata 2025-03-14 Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 1 / 55
2. Few words about the speaker Born in 1972 Passionate about IT since 1985 In love with PostgreSQL since 2006 PostgreSQL and FreeBSD tattoo on the right shoulder Proud member of PostgreSQL Europe https://www.postgresql.eu Freelance DBA at Kamedata Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 2 / 55
3. Kamedata DBA are an endangered species but we are still very needed. Please reach out if you need any help with... PostgreSQL (of course) Resque from THE CLOUD aka SOMEBODY ELSE’S COMPUTER Database check Security Performance tuning Migration (aka resque) from another RDBMS Anything else related with PostgreSQL Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 3 / 55
4. Getting in touch Linkedin: https://www.linkedin.com/in/federicocampoli/ Codeberg: https://codeberg.com/the4thdoctor Bluesky: https://bsky.app/profile/pgdba.org Mastodon: https://fosstodon.org/@4thdoctor scarf Blog: https://pgdba.org Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 4 / 55
5. Feedback please Please scan the QR code and leave a feedback. https://grusp.org/agenda Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 5 / 55
6. Table of contents 1 The advent of JSON and JSONB 2 A quick look to JSONB 3 Considerations about MongoDB 4 Wrap up Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 6 / 55
7. History The land of the SQL A long time ago the database was called by that name Gintama by Hideaki Sorachi, source https://gintama.fandom.com/ Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 7 / 55
8. History With the arrival of the NOSQL database... And the STRUCTURE BAN the SQL language fell into decline Gintama by Hideaki Sorachi, source https://gintama.fandom.com/ Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 8 / 55
9. History In such hard times there was only one RDBMS left with the spirit of the SQL His name is PostgreSQL, a reckless Jack of All Trades with a sweet trunk Gintama by Hideaki Sorachi, source https://gintama.fandom.com/ Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance 9 / 55
10. Yorozuya Gintama by Hideaki Sorachi, source https://gintama.fandom.com/ Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 10 / 55
11. What on earth is JSON JSON stands for JavaScript Object Notation Open standard file format and data interchange JSON uses human-readable text to store Born out of a need for a real-time server-to-browser session without Flash(tm) or Java(tm) Nowadays the de facto standard for data interchange and document storage Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 11 / 55
12. PostgreSQL’s JSON and JSONB PostgreSQL 9.2: JSON added as native data type PostgreSQL 9.3: JSON helper functions added PostgreSQL 9.4: JSONB data type along with the helper functions PostgreSQL 12: json path added PostgreSQL 16: SQL/JSON constructors and identity functions PostgreSQL 17: New SQL/JSON capabilities, and the JSON TABLE() function, which converts JSON data into a table representation Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 12 / 55
13. PostgreSQL’s JSON and JSONB Essentially, JSON and JSONB are two ways to store JSON data within a PostgreSQL database. However they store the same data in different ways. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 13 / 55
14. PostgreSQL’s JSON and JSONB JSON stores the data as plain text. Use it when: You primarily need to store and retrieve the entire JSON document as-is, without frequently querying its contents. Storage space is a primary concern, and the slight overhead of JSONB’s binary format is undesirable. You don’t need indexing or complex querying within the JSON data. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 14 / 55
15. PostgreSQL’s JSON and JSONB JSONB stores the data as binary. Use it when: You need to query or index the data within the JSON document. Its binary format and indexing capabilities are designed for efficient data retrieval. You expect to perform frequent searches, filtering, or data extraction within the JSON. Data validation is important, as JSONB enforces basic structural integrity. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 15 / 55
16. Advantages of JSONB Binary storage for efficient querying and indexing. Indexable for fast data retrieval. Rich query operators and functions. Automatic data validation. Flexible schema for complex data. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 16 / 55
17. Disadvantages of JSONB Larger storage than plain JSON in some cases. Index overhead impacting writes. Complex querying of nested data. Weak schema enforcement. Performance trade-offs for certain operations. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance The advent of JSON and JSONB 17 / 55
18. The Dragon of Katsurahama Gintama by Hideaki Sorachi, source https://gintama.fandom.com/ Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 18 / 55
19. Loading significant amount of data To load some significant data I wrote a very simple script using the python library faker https://codeberg.org/the4thdoctor/pg faker Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 19 / 55
20. The test tables idi =# \d js_tab Table "public.js_tab" Column | Type | Collation | Nullable | Default -- ------+---------+-----------+----------+------------------------------------ id | integer | | not null | nextval(’js_tab_id_seq ’:: regclass) data | json | | | idi =# \d jsb_tab Table "public.jsb_tab" Column | Type | Collation | Nullable | Default -- ------+---------+-----------+----------+------------------------------------- id | integer | | not null | nextval(’jsb_tab_id_seq ’:: regclass) data | jsonb | | | Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 20 / 55
21. The test data The tool inserts 5,000,000 rows of data having a fixed structure and using fake data. For performance reason the faker library is called every 1000 rows meaning that we have groups of 1000 rows with the same data. e.g. { "name": "Karen Snyder", "date": "1994 -03 -20" , "address": "541 Wilson Inlet Apt. 017\ nEast Tammy , MI 15868" , "city": "North Dennis", " phone_number ": "(658)496 -2106 x5781", " passport_full ": "Brandi\nCastillo\nF\n17 Dec 1962\ n12 Sep 2015\ n12 Sep 2025\ nV23724877\n" } Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 21 / 55
22. The test tables Interestingly with a JSON object so small the JSONB storage is slightly more efficient than plain JSON. idi =# \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description -- ------+---------+-------+-----------+-------------+---------------+---------+------------- public | js_tab | table | thedoctor | permanent | heap | 1450 MB | public | jsb_tab | table | thedoctor | permanent | heap | 1431 MB | Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 22 / 55
23. Selecting from js tab All tests are from cached data. idi =# EXPLAIN ANALYZE SELECT data FROM js_tab WHERE data ->>’name ’=’Thomas Greene ’ ; QUERY PLAN -- ---------------------------------------------------- Gather (cost =1000.00..220303.85 rows =25001 width =261) (actual time =2852.591..2858.493 rows =1000 loops =1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on js_tab (cost =0.00..216803.75 rows =10417 width =261) (actual time =1110.635..1981.631 rows =333 loops =3) Filter: (( data ->> ’name ’:: text) = ’Thomas Greene ’:: text) Rows Removed by Filter: 1666333 Planning Time: 0.075 ms Execution Time: 2858.550 ms (8 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 23 / 55
24. Selecting from jsb tab idi =# EXPLAIN ANALYZE SELECT data FROM jsb_tab WHERE data ->>’name ’=’Thomas Greene ’ ; QUERY PLAN -- --------------------------------------------------- Gather (cost =1000.00..217785.06 rows =24996 width =257) (actual time =458.331..459.646 rows =1000 loops =1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on jsb_tab (cost =0.00..214285.46 rows =10415 width =257) (actual time =455.134..455.251 rows =333 loops =3) Filter: (( data ->> ’name ’:: text) = ’Thomas Greene ’:: text) Rows Removed by Filter: 1666333 Planning Time: 0.063 ms Execution Time: 459.721 ms (8 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 24 / 55
25. Selecting from text using like idi =# CREATE TABLE txt_tab AS SELECT id ,data :: text FROM jsb_tab; SELECT 5000000 idi =# EXPLAIN ANALYZE SELECT data FROM txt_tab WHERE data like ’%" name ": "Thomas Greene "%’ ; QUERY PLAN -- --------------------------------------------------- Gather (cost =1000.00..212504.10 rows =475 width =261) (actual time =1842.801..1844.386 rows =1000 loops =1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on txt_tab (cost =0.00..211456.60 rows =198 width =261) (actual time =1839.577..1839.757 rows =333 loops =3) Filter: (data ~~ ’%" name ": "Thomas Greene "%’:: text) Rows Removed by Filter: 1666333 Planning Time: 0.399 ms Execution Time: 1844.449 ms (8 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 25 / 55
26. Focus on JSONB Inclusion operator idi =# EXPLAIN ANALYZE SELECT data FROM jsb_tab WHERE ’{" name ":" Thomas Greene "}’ <@ data ; QUERY PLAN -- ------------------------------------------------------------- Gather (cost =1000.00..210125.29 rows =474 width =257) (actual time =565.642..567.093 rows =1000 loops =1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on jsb_tab (cost =0.00..209077.89 rows =198 width =257) (actual time =561.064..561.200 rows =333 loops =3) Filter: (’{" name ": "Thomas Greene "}’:: jsonb <@ data) Rows Removed by Filter: 1666333 Planning Time: 0.279 ms Execution Time: 567.154 ms (8 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 26 / 55
27. Indexing JSONB Inclusion operator idi =# CREATE INDEX idxdata ON jsb_tab USING gin(data); idi =# SELECT pg_size_pretty ( pg_table_size (oid)),relname FROM pg_class WHERE relname=’idxdata ’; pg_size_pretty | relname -- --------------+--------- 80 MB | idxdata (1 row) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 27 / 55
28. Indexing JSONB Inclusion operator idi =# EXPLAIN ANALYZE SELECT data FROM jsb_tab WHERE ’{" name ":" Thomas Greene "}’ <@ data ; QUERY PLAN -- ---------------------------------------- Bitmap Heap Scan on jsb_tab (cost =41.02..1870.58 rows =474 width =257) (actual time =0.161..0.437 rows =1000 loops =1) Recheck Cond: (’{" name ": "Thomas Greene "}’:: jsonb <@ data) Heap Blocks: exact =38 -> Bitmap Index Scan on idxdata (cost =0.00..40.90 rows =474 width =0) (actual time =0.148..0.148 rows =1000 loops =1) Index Cond: (data @> ’{" name ": "Thomas Greene "}’:: jsonb) Planning Time: 0.366 ms Execution Time: 0.476 ms (7 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 28 / 55
29. Beware of the update overhead Updating a GIN index tends to be slow because of the intrinsic nature of inverted indexes: inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed item). GIN is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. When using GIN always consider sizing correctly the gin pending list limit. ATTENTION Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 29 / 55
30. Update without index JSONB idi =# DROP INDEX idxdata; DROP INDEX idi =# BEGIN; BEGIN idi =*# EXPLAIN ANALYZE UPDATE jsb_tab SET data= jsonb_set(data , ’{name}’, ’"Justin Davis"’, true) WHERE ’{" name ":" Thomas Greene "}’ <@ data ; QUERY PLAN -- ------------------------------------------ Update on jsb_tab (cost =0.00..245541.18 rows =0 width =0) (actual time =1126.619..1126.620 rows =0 loops =1) -> Seq Scan on jsb_tab (cost =0.00..245541.18 rows =474 width =38) (actual time =1123.805..1124.769 rows =1000 loops =1) Filter: (’{" name ": "Thomas Greene "}’:: jsonb <@ data) Rows Removed by Filter: 4999000 Planning Time: 0.193 ms Execution Time: 1126.655 ms (6 rows) idi =*# ROLLBACK; ROLLBACK Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 30 / 55
31. Update with GIN and fastupdate JSONB idi =# CREATE INDEX idxdata ON jsb_tab USING gin(data); CREATE INDEX idi =# BEGIN; BEGIN idi =*# EXPLAIN ANALYZE UPDATE jsb_tab SET data= jsonb_set(data , ’{name}’, ’"Justin Davis"’, true) WHERE ’{" name ":" Thomas Greene "}’ <@ data ; QUERY PLAN -- ---------------------------------------------- Update on jsb_tab (cost =41.02..1871.77 rows =0 width =0) (actual time =9.042..9.043 rows =0 loops =1) -> Bitmap Heap Scan on jsb_tab (cost =41.02..1871.77 rows =474 width =38) (actual time =0.218..1.527 rows =1000 loops =1) Recheck Cond: (’{" name ": "Thomas Greene "}’:: jsonb <@ data) Heap Blocks: exact =38 -> Bitmap Index Scan on idxdata (cost =0.00..40.90 rows =474 width =0) (actual time =0.198..0.198 rows =1000 loops =1) Index Cond: (data @> ’{" name ": "Thomas Greene "}’:: jsonb) Planning Time: 0.389 ms Execution Time: 9.072 ms (8 rows) idi =*# ROLLBACK; ROLLBACK Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 31 / 55
32. Update with GIN without fastupdate JSONB idi =# DROP INDEX idxdata; DROP INDEX idi =# CREATE INDEX idxdata ON jsb_tab USING gin(data) WITH (fastupdate = off); CREATE INDEX idi =# BEGIN; BEGIN idi =*# EXPLAIN ANALYZE UPDATE jsb_tab SET data= jsonb_set(data , ’{name}’, ’"Justin Davis"’, true) WHERE ’{" name ":" Thomas Greene "}’ <@ data ; QUERY PLAN -- ---------------------------------------------- Update on jsb_tab (cost =41.02..1871.78 rows =0 width =0) (actual time =77.474..77.475 rows =0 loops =1) -> Bitmap Heap Scan on jsb_tab (cost =41.02..1871.78 rows =474 width =38) (actual time =0.177..1.618 rows =1000 loops =1) Recheck Cond: (’{" name ": "Thomas Greene "}’:: jsonb <@ data) Heap Blocks: exact =38 -> Bitmap Index Scan on idxdata (cost =0.00..40.90 rows =474 width =0) (actual time =0.158..0.159 rows =1000 loops =1) Index Cond: (data @> ’{" name ": "Thomas Greene "}’:: jsonb) Planning Time: 0.251 ms Execution Time: 77.504 ms (8 rows) idi =*# ROLLBACK; ROLLBACK Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 32 / 55
33. json path idi =# UPDATE jsb_tab SET data= jsonb_set(data , ’{childs}’, ’[" Mary","Lucy","John "]’, true) WHERE ’{" name ":" Thomas Greene "}’ <@ data ; UPDATE 1000 Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 33 / 55
34. json path idi =# SELECT DISTINCT jsonb_path_query (data , ’$.childs ’) as childs , jsonb_path_query (data ,’$.name ’) as parent FROM jsb_tab WHERE data ? ’childs ’; childs | parent -- ------------------------+----------------- ["Mary", "Lucy", "John"] | "Thomas Greene" (1 row) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 34 / 55
35. json path idi =# EXPLAIN ANALYZE SELECT DISTINCT jsonb_path_query (data , ’$.childs ’) as childs , jsonb_path_query (data ,’$.name ’) as parent FROM jsb_tab WHERE data ? ’childs ’; QUERY PLAN -- ----------------------------------------------- Unique (cost =66735.38..70290.38 rows =452000 width =64) (actual time =2.321..2.776 rows =1 loops =1) -> Sort (cost =66735.38..67920.38 rows =474000 width =64) (actual time =2.321..2.361 rows =1000 loops =1) Sort Key: ( jsonb_path_query (data , ’$." childs"’:: jsonpath , ’{}’::jsonb , false)), ( jsonb_path_query (data , ’$." name"’:: jsonpath , ’{}’::jsonb , false)) Sort Method: quicksort Memory: 95kB -> ProjectSet (cost =23.82..4228.15 rows =474000 width =64) (actual time =0.144..1.854 rows =1000 loops =1) -> Bitmap Heap Scan on jsb_tab (cost =23.82..1853.41 rows =474 width =257) (actual time =0.138..0.459 rows =1000 loops =1) Recheck Cond: (data ? ’childs ’:: text) Heap Blocks: exact =42 -> Bitmap Index Scan on idxdata (cost =0.00..23.70 rows =474 width =0) (actual time =0.122..0.123 rows =1000 loops =1) Index Cond: (data ? ’childs ’:: text) Planning Time: 0.247 ms Execution Time: 2.798 ms (12 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 35 / 55
36. json path Beware of the where condition idi =# EXPLAIN ANALYZE SELECT DISTINCT jsonb_path_query (data , ’$.childs ’) as childs , jsonb_path_query (data ,’$.name ’) as parent FROM jsb_tab WHERE jsonb_exists (data ,’childs ’) ; QUERY PLAN -- ----------------------------------------------- HashAggregate (cost =4042604.29..4103437.46 rows =1667376 width =64) (actual time =315.686..316.815 rows =1 loops =1) Group Key: ( jsonb_path_query (data , ’$." childs"’:: jsonpath , ’{}’::jsonb , false)), ( jsonb_path_query (data , ’$ ." name"’:: jsonpath , ’{}’::jsonb , fals e)) Planned Partitions: 64 Batches: 1 Memory Usage: 1561 kB -> Gather (cost =1000.00..3857629.76 rows =1667376 width =64) (actual time =0.222..315.428 rows =1000 loops =1) Workers Planned: 2 Workers Launched: 2 -> ProjectSet (cost =0.00..3689892.16 rows =694740000 width =64) (actual time =206.875..311.481 rows =333 loops =3) -> Parallel Seq Scan on jsb_tab (cost =0.00..209244.76 rows =694740 width =257) (actual time =206.872..310.993 rows =333 loops =3) Filter: jsonb_exists (data , ’childs ’:: text) Rows Removed by Filter: 1666333 Planning Time: 0.103 ms Execution Time: 316.907 ms (12 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 36 / 55
37. json constructors idi =# SELECT json_array (select relname from pg_class where relkind=’r’ limit 5); json_array -- ------------------- ["address", "city", "company", " pg_statistic ", "pg_type"] (1 row) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 37 / 55
38. json test functions idi =# SELECT data IS JSON as is_json , data IS JSON ARRAY AS is_json_array , data IS JSON WITH UNIQUE KEYS FROM jsb_tab LIMIT 1; is_json | is_json_array | ?column? -- -------+---------------+---------- t | f | t (1 row) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 38 / 55
39. json table idi =# SELECT jt.* FROM jsb_tab , json_table ( data , ’$.childs [*] ’ COLUMNS ( id FOR ORDINALITY , child_name text PATH ’$’ ) ) as jt WHERE JSON_EXISTS( data , ’$.childs ’) LIMIT 10; id | child_name -- --+------------ 1 | Mary 2 | Lucy 3 | John 1 | Mary 2 | Lucy 3 | John 1 | Mary 2 | Lucy 3 | John 1 | Mary (10 rows) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 39 / 55
40. json table idi =# SELECT DISTINCT jt.* FROM jsb_tab , json_table ( data , ’$’ COLUMNS ( parent_name text PATH ’$.name ’, address text PATH ’$.address ’, childs text PATH ’$.childs [*]’ WITH WRAPPER ) ) as jt WHERE JSON_EXISTS( data , ’$.childs ’) LIMIT 10; parent_name | address | childs -- -------------+----------------------+-------------------------- Thomas Greene | 64476 Patel Squares +| ["Mary", "Lucy", "John"] | Brownville , CA 71810 | (1 row) Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance A quick look to JSONB 40 / 55
41. Sadaharu Gintama by Hideaki Sorachi, source https://gintama.fandom.com/ Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Considerations about MongoDB 41 / 55
42. License In October 2018, the MongoDB database was relicensed under the Server Side Public License (SSPL). Debian, Red Hat Enterprise Linux, and Fedora subsequently dropped MongoDB, citing concerns about the SSPL. Source Wikipedia Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Considerations about MongoDB 42 / 55
43. License In 2018, MongoDB submitted the license to the Open Source Initiative (OSI) for approval. The company withdrew its submission in 2019. In January 2021, following the re-licensing move by Elastic, OSI released a statement declaring that the SSPL does not comply with its Open Source Definition because it discriminates against specific fields of endeavor, describing it as a ”fauxpen” source license. Source https://en.wikipedia.org/wiki/Server Side Public License Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Considerations about MongoDB 43 / 55
44. Deprecations Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Considerations about MongoDB 44 / 55
45. Deprecations The 30th September 2024 MongoDB removed the Atlas Device SDKs. Suddenly all the mobile apps kept in in sync with MongoDB databases, even when users were offline, lost their go-to tools. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Considerations about MongoDB 45 / 55
46. Syntax #PostgreSQL get users with age between 30 and 45 SELECT * FROM users WHERE (data ->> ’age ’):: integer BETWEEN 30 AND 45; #MongoDB get users with age between 30 and 45 db. your_collection .find ({ "age": { $gte: 30, $lte: 45 } }); Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Considerations about MongoDB 46 / 55
47. Wrap up There are interesting projects based and extending PostgreSQL’s capabilities. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 47 / 55
48. Wrap up FerretDB https://www.ferretdb.com/ FerretDB allows you to use MongoDB drivers seamlessly with PostgreSQL as the database backend. Use all tools, drivers, UIs, and the same query language and stay open-source. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 48 / 55
49. Wrap up Microsoft DocumentDB https://opensource.microsoft.com/blog/2025/01/23/documentdb-open-source- announcement/ DocumentDB is an open-source document database platform and the engine powering the vCore-based Azure Cosmos DB for MongoDB, built on PostgreSQL. Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 49 / 55
50. Wrap up PostgreSQL offers a constantly improving ecosystem on JSONB with excellent performance. The license is super permissive as it’s a BSD license derivative There are interesting projects to use PostgreSQL capabilities and transform it to a document db And remember that... Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 50 / 55
51. Disclaimer Y.M.M.V. Translation: no airbags. we die as heroes Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 51 / 55
52. PGDay Napoli 2025 https://2025.pgdayna.org Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 52 / 55
53. Feedback please Please scan the QR code and leave a feedback. https://grusp.org/agenda Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 53 / 55
54. Thank you for listening! Any questions? Copyright by dan232323 http://dan232323.deviantart.com/art/Pinkie-Pie-Thats-All-Folks-454693000 Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 54 / 55
55. Leveraging jsonb in PostgreSQL 17 for max performance Incontro Devops Italia 2025 Federico Campoli Kamedata 2025-03-14 Federico Campoli (Kamedata) Leveraging jsonb in PostgreSQL 17 for max performance Wrap up 55 / 55