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
test-> from reviews_jsonb
test-> where review
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
from reviews_jsonb
where review
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, .
, .