2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Liaqat's PlanetPostgreSQL3 / Webinar: Introduction to JSON data types in PostgreSQL [Follow Up]
Liaqat Andrabi

Webinar: Introduction to JSON data types in PostgreSQL [Follow Up]

December 19, 2017/0 Comments/in Liaqat's PlanetPostgreSQL, Webinars /by Liaqat Andrabi

On 12-Dec-2017, 2ndQuadrant held the first in a series of PostgreSQL webinars. The session was conducted by Andrew Dunstan, Principal Contributor of JSON functionality to PostgreSQL and Senior Developer & PostgreSQL Committer at 2ndQuadrant.

As promised, the recording of the Webinar is now available. Those who couldn’t make it to the live session, can now view Introduction to JSON data types in PostgreSQL here.

While Andrew was able to address many questions live, there were several queries that couldn’t be answered due to time restrictions. Andrew Dunstan has taken time to answer those questions below.

Q1: Since PostgreSQL 9.4 JSONB supports GIN index, does that means that GIN index has a better performance on JSONB? What about JSON?

A:  There is no direct indexing support at all for the JSON data type, only for the JSONB data type. However, you can use expression indexes on the JSON type using the operators that return text. In general, if you want indexing, use JSONB.

 

Q2: Assuming that I have a following relationship:

 role -> permission

In relational database I would create a pivot table to hold all the combinations:

 Id, role_id, permission_id

Assuming that I could do the same with a jsonb field in the role table:

Role->permissions = jsonb[permission_id, permission_id, permission_id]

But if I remove or add a permission, it would always perform an update of the whole column instead of updating just the element in the permission JSON.

In addition, I am not sure if I can join the permission_ids with the permission table from a JSONB. I wonder if it would be best to stick to the relational model for this scenario or if this would be a valid scenario.

A: In general permissions, schemes are likely to be done best by fairly normalised tables, possibly with the addition of some array fields to avoid the need for join tables. Some specialised and highly dynamic permissions schemes might benefit from being done using JSON, but without knowing the precise details of the case it’s difficult to make a judgement.

 

Q3: Are there any maximum length data/keys?

A: Not practically. For example, the following works:

create table foo as select jsonb_build_object(repeat('x',10000),repeat('y',10000000))

Indexing might impose smaller limits.

 

Q4: What is the difference between JSON and JSONB? For example, JSONB stores structured data whereas JSON stores unstructured data. Do you have any example? It is hard to understand as I’m from SQL RDBMS background.

A: JSON and JSONB are both PostgreSQL data types. You can have a column of one type or the other. They store the data in different ways – JSON stores the data as text and JSONB stored the data in a decomposed format, which allows us to process it far more efficiently.

 

Q5: Is it more efficient to put some fields together in a JSON field and index them, compared to having multiple fields each with their own index?

A: It’s possible that it could be more efficient, but it’s very dependent on the data. This would be a very good case for doing thorough benchmarking.

 

Q6: If there are data points that will be queried more often and also sortable, is it still ok to store that data in JSONB?

A: In general, data that has a known data type and well understood values (e.g. Social Security Numbers) should not be stored in JSON. It would be far better to store such data in a regular text field and index it with a btree index.

 

Q7: I have a JSONB column, which has few 100’s of array elements. How do i update and what is the cost of this update?

A: PostgreSQL does not ever update a row or a field directly. Instead, it creates a new version of the row. That’s how Multi Version Concurrency Control works. It’s why PostgreSQL requires far fewer locks than many other database systems. We never update in place. So if you update a few values in a 600 key JSON field, it rewrites not just the whole field but the whole row. The cost is therefore the same as to cost of updating any field in the table.

 

Q8: I am working on ERP project which has all its business logic at Database layer. How could this JSON data type be useful for me? Till now, I haven’t used this in my application.

A: Not every application will benefit from storing data in a JSON format in the database. Many ERP applications are fairly fixed in structure, and these are not likely to benefit a great deal from such storage. Applications with more dynamic data structure requirements are likely to benefit more. However, static applications can still benefit from the JSON facilities in PostgreSQL by being able to import JSON data from elsewhere very easily, and by being able to export data to JSON very easily.

 

Q9: You said, Much better alternative to Entity-Attribute-Value..

Like for this example:

Product: id, name
Attribute: Id, name
Value: id, name

Attribute_value: id, attribute_id, value_id
Product_attribute_value: product_id, Attribute_value_id

Here I would have foreign keys as well. Assuming I want to allow only certain values for an attribute. In addition, if I want to re-arrange a value to another attribute I would need to perform an update to the whole database and change each JSON field, instead of just changing the relationships in the pivot.

How would this work in JSONB?

A: If you’re using EAV then you can’t use Foreign Keys with it, since different attributes will have different value ranges, and foreign keys don’t work like that. Apart from that I don’t understand the question.

For any questions, comments, or feedback regarding the 2ndQuadrant PostgreSQL Webinar series, please visit our website or send an email to [email protected]

Tags: andrew dunstan, JSON, JSONB, postgres, PostgreSQL, webinar
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
pglogical 2.1 and Logical Replication in PostgreSQL 10 PG Phriday: Postgres on ZFS
Scroll to top
×