Use JSONB for postgres columns other than primary and foreign keys

I just opened JSONB for PostgreSQL and wondered what could go wrong if I used it for the columns of all tables?

That is, all my tables will have primary and foreign keys in the form of columns and a column of fieldtype JSONB for any other data.

Besides the extra space due to JSONB overhead and loss of input on the “columns”, what would I skip?

+4
source share
1 answer

It turns out you're here here.

Highlights of using a relational database.

  • Well defined relationship.
  • Well defined and detailed outline.
  • .

. . - . , .

... , JSONB . SQL. , , JSONB , , SQL. , , - .

JSONB SQL...

create table customers (
    id text primary key
);

create table products (
    id text primary key,
    title text,
    sales_rank integer,
    "group" text,
    category text,
    subcategory text,
    similar_ids text[]
);

create table reviews (
    customer_id text references customers(id),
    product_id text references products(id),
    "date" timestamp,
    rating integer,
    votes integer,
    helpful_votes integer
);

, SQL, JSONB ...

create table customers (
    id text primary key
);

create table products_jb (
    id text primary key,
    fields jsonb
);

create table reviews_jb (
    customer_id text references customers(id),
    product_id text references products_jb(id),
    fields jsonb
);

JSONB.

create table reviews_jsonb (
    review jsonb
);

, script. 589859, 93319 , 98761 .

, JSONB, . -, .

SQL: 138

test=> select round(avg(r.rating), 2)
from reviews r
join products p on p.id = r.product_id
where p.category = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 138.631 ms

JSONB: 380

test=> select round(avg((review#>>'{review,rating}')::numeric),2)
test-> from reviews_jsonb
test-> where review #>>'{product,category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 380.697 ms

JSONB: 190

test=> select round(avg((r.fields#>>'{rating}')::numeric),2)
from reviews_jb r
join products_jb p on p.id = r.product_id
where p.fields#>>'{category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 192.333 ms

, . , JSONB, 50% SQL. ?

SQL: 130 (+500 )

test=> create index products_category on products(category);
CREATE INDEX
Time: 491.969 ms

test=> select round(avg(r.rating), 2)
from reviews r
join products p on p.id = r.product_id
where p.category = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 128.212 ms

JSONB: 360 (+ 25000 )

test=> create index on reviews_jsonb using gin(review);
CREATE INDEX
Time: 25253.348 ms
test=> select round(avg((review#>>'{review,rating}')::numeric),2)
from reviews_jsonb
where review #>>'{product,category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 363.222 ms

JSONB: 185 (+6900 )

test=> create index on products_jb using gin(fields);
CREATE INDEX
Time: 3654.894 ms
test=> create index on reviews_jb using gin(fields);
CREATE INDEX
Time: 3237.534 ms
test=> select round(avg((r.fields#>>'{rating}')::numeric),2)
from reviews_jb r
join products_jb p on p.id = r.product_id
where p.fields#>>'{category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 183.679 ms

, .

, , Hybrid JSONB , Full SQL, , Full JSONB. . , , .

: SQL , , , , JSONB, .

, .

+3

Source: https://habr.com/ru/post/1669865/


All Articles