PostgreSQL ਵਿੱਚ JSONB ਨਾਲ EAV ਨੂੰ ਬਦਲਣਾ

TL; DR: JSONB ਪੁੱਛਗਿੱਛ ਦੀ ਕਾਰਗੁਜ਼ਾਰੀ ਨੂੰ ਕੁਰਬਾਨ ਕੀਤੇ ਬਿਨਾਂ ਡਾਟਾਬੇਸ ਸਕੀਮਾ ਵਿਕਾਸ ਨੂੰ ਬਹੁਤ ਸਰਲ ਬਣਾ ਸਕਦਾ ਹੈ।

ਜਾਣ ਪਛਾਣ

ਆਉ ਰਿਲੇਸ਼ਨਲ ਡੇਟਾਬੇਸ (ਡੇਟਾਬੇਸ) ਦੀ ਦੁਨੀਆ ਵਿੱਚ ਸ਼ਾਇਦ ਸਭ ਤੋਂ ਪੁਰਾਣੇ ਵਰਤੋਂ ਦੇ ਕੇਸਾਂ ਵਿੱਚੋਂ ਇੱਕ ਦੀ ਇੱਕ ਸ਼ਾਨਦਾਰ ਉਦਾਹਰਣ ਦੇਈਏ: ਸਾਡੇ ਕੋਲ ਇੱਕ ਇਕਾਈ ਹੈ, ਅਤੇ ਸਾਨੂੰ ਇਸ ਇਕਾਈ ਦੀਆਂ ਕੁਝ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ (ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ) ਨੂੰ ਬਚਾਉਣ ਦੀ ਲੋੜ ਹੈ। ਪਰ ਸਾਰੀਆਂ ਉਦਾਹਰਣਾਂ ਵਿੱਚ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਦਾ ਇੱਕੋ ਸੈੱਟ ਨਹੀਂ ਹੋ ਸਕਦਾ ਹੈ, ਅਤੇ ਭਵਿੱਖ ਵਿੱਚ ਹੋਰ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਸ਼ਾਮਲ ਕੀਤੀਆਂ ਜਾ ਸਕਦੀਆਂ ਹਨ।

ਇਸ ਸਮੱਸਿਆ ਨੂੰ ਹੱਲ ਕਰਨ ਦਾ ਸਭ ਤੋਂ ਆਸਾਨ ਤਰੀਕਾ ਹੈ ਕਿ ਹਰੇਕ ਸੰਪੱਤੀ ਮੁੱਲ ਲਈ ਡੇਟਾਬੇਸ ਸਾਰਣੀ ਵਿੱਚ ਇੱਕ ਕਾਲਮ ਬਣਾਉਣਾ, ਅਤੇ ਸਿਰਫ਼ ਉਹਨਾਂ ਨੂੰ ਭਰਨਾ ਜੋ ਕਿਸੇ ਖਾਸ ਇਕਾਈ ਦੇ ਉਦਾਹਰਣ ਲਈ ਲੋੜੀਂਦੇ ਹਨ। ਬਹੁਤ ਵਧੀਆ! ਸਮੱਸਿਆ ਹੱਲ ਹੋ ਗਈ... ਜਦੋਂ ਤੱਕ ਤੁਹਾਡੀ ਸਾਰਣੀ ਵਿੱਚ ਲੱਖਾਂ ਰਿਕਾਰਡ ਸ਼ਾਮਲ ਨਹੀਂ ਹੁੰਦੇ ਅਤੇ ਤੁਹਾਨੂੰ ਇੱਕ ਨਵਾਂ ਰਿਕਾਰਡ ਜੋੜਨ ਦੀ ਲੋੜ ਹੁੰਦੀ ਹੈ।

EAV ਪੈਟਰਨ 'ਤੇ ਵਿਚਾਰ ਕਰੋ (ਹਸਤੀ-ਗੁਣ-ਮੁੱਲ), ਇਹ ਅਕਸਰ ਵਾਪਰਦਾ ਹੈ। ਇੱਕ ਸਾਰਣੀ ਵਿੱਚ ਇਕਾਈਆਂ (ਰਿਕਾਰਡ), ਦੂਜੀ ਸਾਰਣੀ ਵਿੱਚ ਸੰਪੱਤੀ ਦੇ ਨਾਮ (ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ) ਸ਼ਾਮਲ ਹੁੰਦੇ ਹਨ, ਅਤੇ ਇੱਕ ਤੀਜੀ ਸਾਰਣੀ ਵਿੱਚ ਇਕਾਈਆਂ ਨੂੰ ਉਹਨਾਂ ਦੇ ਗੁਣਾਂ ਨਾਲ ਜੋੜਦਾ ਹੈ ਅਤੇ ਮੌਜੂਦਾ ਹਸਤੀ ਲਈ ਉਹਨਾਂ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਦਾ ਮੁੱਲ ਰੱਖਦਾ ਹੈ। ਇਹ ਤੁਹਾਨੂੰ ਵੱਖ-ਵੱਖ ਵਸਤੂਆਂ ਲਈ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਦੇ ਵੱਖੋ-ਵੱਖਰੇ ਸੈੱਟ ਰੱਖਣ ਦੀ ਯੋਗਤਾ ਦਿੰਦਾ ਹੈ, ਅਤੇ ਡਾਟਾਬੇਸ ਢਾਂਚੇ ਨੂੰ ਬਦਲੇ ਬਿਨਾਂ ਫਲਾਈ 'ਤੇ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਵੀ ਜੋੜਦਾ ਹੈ।

ਹਾਲਾਂਕਿ, ਮੈਂ ਇਹ ਪੋਸਟ ਨਹੀਂ ਲਿਖਾਂਗਾ ਜੇਕਰ ਈਵੀਏ ਪਹੁੰਚ ਵਿੱਚ ਕੁਝ ਕਮੀਆਂ ਨਾ ਹੁੰਦੀਆਂ। ਇਸ ਲਈ, ਉਦਾਹਰਨ ਲਈ, ਇੱਕ ਜਾਂ ਇੱਕ ਤੋਂ ਵੱਧ ਇਕਾਈਆਂ ਨੂੰ ਪ੍ਰਾਪਤ ਕਰਨ ਲਈ ਜਿਨ੍ਹਾਂ ਵਿੱਚ ਹਰੇਕ ਵਿੱਚ 1 ਵਿਸ਼ੇਸ਼ਤਾ ਹੈ, ਪੁੱਛਗਿੱਛ ਵਿੱਚ 2 ਜੋੜਾਂ ਦੀ ਲੋੜ ਹੈ: ਪਹਿਲਾ ਗੁਣ ਸਾਰਣੀ ਨਾਲ ਜੋੜਨਾ ਹੈ, ਦੂਜਾ ਮੁੱਲ ਸਾਰਣੀ ਨਾਲ ਜੋੜਨਾ ਹੈ। ਜੇਕਰ ਕਿਸੇ ਇਕਾਈ ਦੇ 2 ਗੁਣ ਹਨ, ਤਾਂ 4 ਜੋੜਾਂ ਦੀ ਲੋੜ ਹੈ! ਇਸ ਤੋਂ ਇਲਾਵਾ, ਸਾਰੀਆਂ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਨੂੰ ਆਮ ਤੌਰ 'ਤੇ ਸਤਰ ਦੇ ਤੌਰ 'ਤੇ ਸਟੋਰ ਕੀਤਾ ਜਾਂਦਾ ਹੈ, ਜਿਸ ਦੇ ਨਤੀਜੇ ਵਜੋਂ ਨਤੀਜਾ ਅਤੇ WHERE ਧਾਰਾ ਦੋਵਾਂ ਲਈ ਕਿਸਮ ਕਾਸਟਿੰਗ ਹੁੰਦੀ ਹੈ। ਜੇ ਤੁਸੀਂ ਬਹੁਤ ਸਾਰੇ ਸਵਾਲ ਲਿਖਦੇ ਹੋ, ਤਾਂ ਇਹ ਸਰੋਤ ਵਰਤੋਂ ਦੇ ਮਾਮਲੇ ਵਿੱਚ ਕਾਫ਼ੀ ਫਾਲਤੂ ਹੈ।

ਇਹਨਾਂ ਸਪੱਸ਼ਟ ਕਮੀਆਂ ਦੇ ਬਾਵਜੂਦ, EAV ਦੀ ਵਰਤੋਂ ਇਸ ਕਿਸਮ ਦੀਆਂ ਸਮੱਸਿਆਵਾਂ ਨੂੰ ਹੱਲ ਕਰਨ ਲਈ ਲੰਬੇ ਸਮੇਂ ਤੋਂ ਕੀਤੀ ਗਈ ਹੈ। ਇਹ ਅਟੱਲ ਕਮੀਆਂ ਸਨ, ਅਤੇ ਕੋਈ ਬਿਹਤਰ ਵਿਕਲਪ ਨਹੀਂ ਸੀ।
ਪਰ ਫਿਰ PostgreSQL ਵਿੱਚ ਇੱਕ ਨਵੀਂ "ਤਕਨਾਲੋਜੀ" ਪ੍ਰਗਟ ਹੋਈ ...

PostgreSQL 9.4 ਨਾਲ ਸ਼ੁਰੂ ਕਰਦੇ ਹੋਏ, JSONB ਡੇਟਾ ਕਿਸਮ ਨੂੰ JSON ਬਾਈਨਰੀ ਡੇਟਾ ਸਟੋਰ ਕਰਨ ਲਈ ਜੋੜਿਆ ਗਿਆ ਸੀ। ਹਾਲਾਂਕਿ JSON ਨੂੰ ਇਸ ਫਾਰਮੈਟ ਵਿੱਚ ਸਟੋਰ ਕਰਨ ਲਈ ਆਮ ਤੌਰ 'ਤੇ ਪਲੇਨ ਟੈਕਸਟ JSON ਨਾਲੋਂ ਥੋੜਾ ਹੋਰ ਸਪੇਸ ਅਤੇ ਸਮਾਂ ਲੱਗਦਾ ਹੈ, ਇਸ 'ਤੇ ਓਪਰੇਸ਼ਨ ਕਰਨਾ ਬਹੁਤ ਤੇਜ਼ ਹੈ। JSONB ਇੰਡੈਕਸਿੰਗ ਦਾ ਵੀ ਸਮਰਥਨ ਕਰਦਾ ਹੈ, ਜੋ ਕਿ ਪੁੱਛਗਿੱਛ ਨੂੰ ਹੋਰ ਤੇਜ਼ ਬਣਾਉਂਦਾ ਹੈ।

JSONB ਡਾਟਾ ਕਿਸਮ ਸਾਨੂੰ ਸਾਡੇ ਇਕਾਈ ਸਾਰਣੀ ਵਿੱਚ ਸਿਰਫ਼ ਇੱਕ JSONB ਕਾਲਮ ਜੋੜ ਕੇ ਬੋਝਲ EAV ਪੈਟਰਨ ਨੂੰ ਬਦਲਣ ਦੀ ਇਜਾਜ਼ਤ ਦਿੰਦਾ ਹੈ, ਡਾਟਾਬੇਸ ਡਿਜ਼ਾਈਨ ਨੂੰ ਬਹੁਤ ਸਰਲ ਬਣਾਉਂਦਾ ਹੈ। ਪਰ ਬਹੁਤ ਸਾਰੇ ਲੋਕ ਦਲੀਲ ਦਿੰਦੇ ਹਨ ਕਿ ਇਹ ਉਤਪਾਦਕਤਾ ਵਿੱਚ ਕਮੀ ਦੇ ਨਾਲ ਹੋਣਾ ਚਾਹੀਦਾ ਹੈ... ਇਸ ਲਈ ਮੈਂ ਇਹ ਲੇਖ ਲਿਖਿਆ ਹੈ।

ਇੱਕ ਟੈਸਟ ਡੇਟਾਬੇਸ ਸਥਾਪਤ ਕਰਨਾ

ਇਸ ਤੁਲਨਾ ਲਈ, ਮੈਂ $9.5 ਬਿਲਡ 'ਤੇ PostgreSQL 80 ਦੀ ਇੱਕ ਤਾਜ਼ਾ ਸਥਾਪਨਾ 'ਤੇ ਡਾਟਾਬੇਸ ਬਣਾਇਆ ਹੈ। DigitalOcean ਉਬੰਟੂ 14.04. postgresql.conf ਵਿੱਚ ਕੁਝ ਪੈਰਾਮੀਟਰ ਸੈੱਟ ਕਰਨ ਤੋਂ ਬਾਅਦ ਮੈਂ ਦੌੜਿਆ ਇਹ psql ਦੀ ਵਰਤੋਂ ਕਰਕੇ ਸਕ੍ਰਿਪਟ. EAV ਰੂਪ ਵਿੱਚ ਡੇਟਾ ਨੂੰ ਪੇਸ਼ ਕਰਨ ਲਈ ਹੇਠ ਲਿਖੀਆਂ ਸਾਰਣੀਆਂ ਬਣਾਈਆਂ ਗਈਆਂ ਸਨ:

CREATE TABLE entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENCES entity(id), 
  entity_attribute_id INT    REFERENCES entity_attribute(id), 
  value               TEXT
);

ਹੇਠਾਂ ਇੱਕ ਸਾਰਣੀ ਹੈ ਜਿੱਥੇ ਉਹੀ ਡੇਟਾ ਸਟੋਰ ਕੀਤਾ ਜਾਵੇਗਾ, ਪਰ ਇੱਕ JSONB ਕਿਸਮ ਦੇ ਕਾਲਮ ਵਿੱਚ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਦੇ ਨਾਲ - ਵਿਸ਼ੇਸ਼ਤਾ.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

ਬਹੁਤ ਸਰਲ ਲੱਗਦਾ ਹੈ, ਹੈ ਨਾ? ਫਿਰ ਇਸਨੂੰ ਇਕਾਈ ਟੇਬਲ ਵਿੱਚ ਜੋੜਿਆ ਗਿਆ ਸੀ (ਹਸਤੀ & entity_jsonb) 10 ਮਿਲੀਅਨ ਰਿਕਾਰਡ, ਅਤੇ ਇਸਦੇ ਅਨੁਸਾਰ, ਸਾਰਣੀ ਨੂੰ EAV ਪੈਟਰਨ ਅਤੇ JSONB ਕਾਲਮ ਦੇ ਨਾਲ ਪਹੁੰਚ ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹੋਏ ਉਸੇ ਡੇਟਾ ਨਾਲ ਭਰਿਆ ਗਿਆ ਸੀ - entity_jsonb.properties. ਇਸ ਤਰ੍ਹਾਂ, ਸਾਨੂੰ ਸੰਪਤੀਆਂ ਦੇ ਪੂਰੇ ਸਮੂਹ ਵਿੱਚ ਕਈ ਵੱਖ-ਵੱਖ ਡਾਟਾ ਕਿਸਮਾਂ ਪ੍ਰਾਪਤ ਹੋਈਆਂ ਹਨ। ਉਦਾਹਰਨ ਡੇਟਾ:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

ਇਸ ਲਈ ਹੁਣ ਸਾਡੇ ਕੋਲ ਦੋਵਾਂ ਵਿਕਲਪਾਂ ਲਈ ਇੱਕੋ ਜਿਹਾ ਡੇਟਾ ਹੈ। ਆਓ ਕੰਮ 'ਤੇ ਲਾਗੂਕਰਨਾਂ ਦੀ ਤੁਲਨਾ ਕਰਨਾ ਸ਼ੁਰੂ ਕਰੀਏ!

ਆਪਣੇ ਡਿਜ਼ਾਈਨ ਨੂੰ ਸਰਲ ਬਣਾਓ

ਇਹ ਪਹਿਲਾਂ ਦੱਸਿਆ ਗਿਆ ਸੀ ਕਿ ਡੇਟਾਬੇਸ ਡਿਜ਼ਾਈਨ ਨੂੰ ਬਹੁਤ ਸਰਲ ਬਣਾਇਆ ਗਿਆ ਸੀ: EAV ਲਈ ਤਿੰਨ ਟੇਬਲਾਂ ਦੀ ਵਰਤੋਂ ਕਰਨ ਦੀ ਬਜਾਏ, ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਲਈ ਇੱਕ JSONB ਕਾਲਮ ਦੀ ਵਰਤੋਂ ਕਰਕੇ ਇੱਕ ਟੇਬਲ। ਪਰ ਇਹ ਬੇਨਤੀਆਂ ਵਿੱਚ ਕਿਵੇਂ ਪ੍ਰਤੀਬਿੰਬਤ ਹੁੰਦਾ ਹੈ? ਇੱਕ ਇਕਾਈ ਦੀ ਵਿਸ਼ੇਸ਼ਤਾ ਨੂੰ ਅੱਪਡੇਟ ਕਰਨਾ ਇਸ ਤਰ੍ਹਾਂ ਦਿਖਾਈ ਦਿੰਦਾ ਹੈ:

-- EAV
UPDATE entity_attribute_value 
SET value = 'blue' 
WHERE entity_attribute_id = 1 
  AND entity_id = 120;

-- JSONB
UPDATE entity_jsonb 
SET properties = jsonb_set(properties, '{"color"}', '"blue"') 
WHERE id = 120;

ਜਿਵੇਂ ਕਿ ਤੁਸੀਂ ਦੇਖ ਸਕਦੇ ਹੋ, ਆਖਰੀ ਬੇਨਤੀ ਸਧਾਰਨ ਨਹੀਂ ਲੱਗਦੀ. JSONB ਆਬਜੈਕਟ ਵਿੱਚ ਕਿਸੇ ਪ੍ਰਾਪਰਟੀ ਦੇ ਮੁੱਲ ਨੂੰ ਅਪਡੇਟ ਕਰਨ ਲਈ ਸਾਨੂੰ ਫੰਕਸ਼ਨ ਦੀ ਵਰਤੋਂ ਕਰਨੀ ਪਵੇਗੀ jsonb_set(), ਅਤੇ ਇੱਕ JSONB ਵਸਤੂ ਦੇ ਰੂਪ ਵਿੱਚ ਸਾਡੇ ਨਵੇਂ ਮੁੱਲ ਨੂੰ ਪਾਸ ਕਰਨਾ ਚਾਹੀਦਾ ਹੈ। ਹਾਲਾਂਕਿ, ਸਾਨੂੰ ਪਹਿਲਾਂ ਤੋਂ ਕਿਸੇ ਪਛਾਣਕਰਤਾ ਨੂੰ ਜਾਣਨ ਦੀ ਲੋੜ ਨਹੀਂ ਹੈ। EAV ਉਦਾਹਰਨ ਨੂੰ ਦੇਖਦੇ ਹੋਏ, ਸਾਨੂੰ ਅੱਪਡੇਟ ਕਰਨ ਲਈ entity_id ਅਤੇ entity_attribute_id ਦੋਵਾਂ ਨੂੰ ਜਾਣਨ ਦੀ ਲੋੜ ਹੈ। ਜੇਕਰ ਤੁਸੀਂ ਆਬਜੈਕਟ ਨਾਮ ਦੇ ਆਧਾਰ 'ਤੇ JSONB ਕਾਲਮ ਵਿੱਚ ਕਿਸੇ ਪ੍ਰਾਪਰਟੀ ਨੂੰ ਅਪਡੇਟ ਕਰਨਾ ਚਾਹੁੰਦੇ ਹੋ, ਤਾਂ ਇਹ ਸਭ ਇੱਕ ਸਧਾਰਨ ਲਾਈਨ ਵਿੱਚ ਕੀਤਾ ਗਿਆ ਹੈ।

ਹੁਣ ਆਉ ਉਸ ਹਸਤੀ ਦੀ ਚੋਣ ਕਰੀਏ ਜੋ ਅਸੀਂ ਹੁਣੇ ਇਸਦੇ ਨਵੇਂ ਰੰਗ ਦੇ ਅਧਾਰ ਤੇ ਅਪਡੇਟ ਕੀਤੀ ਹੈ:

-- EAV
SELECT e.name 
FROM entity e 
  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';

-- JSONB
SELECT name 
FROM entity_jsonb 
WHERE properties ->> 'color' = 'blue';

ਮੈਨੂੰ ਲਗਦਾ ਹੈ ਕਿ ਅਸੀਂ ਸਹਿਮਤ ਹੋ ਸਕਦੇ ਹਾਂ ਕਿ ਦੂਜਾ ਛੋਟਾ ਹੈ (ਕੋਈ ਸ਼ਾਮਲ ਨਹੀਂ!), ਅਤੇ ਇਸ ਲਈ ਵਧੇਰੇ ਪੜ੍ਹਨਯੋਗ ਹੈ। JSONB ਇੱਥੇ ਜਿੱਤ ਗਿਆ! ਅਸੀਂ JSONB ਆਬਜੈਕਟ ਤੋਂ ਟੈਕਸਟ ਮੁੱਲ ਦੇ ਰੂਪ ਵਿੱਚ ਰੰਗ ਪ੍ਰਾਪਤ ਕਰਨ ਲਈ JSON ->> ਆਪਰੇਟਰ ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹਾਂ। @> ਆਪਰੇਟਰ ਦੀ ਵਰਤੋਂ ਕਰਕੇ JSONB ਮਾਡਲ ਵਿੱਚ ਉਹੀ ਨਤੀਜਾ ਪ੍ਰਾਪਤ ਕਰਨ ਦਾ ਦੂਜਾ ਤਰੀਕਾ ਵੀ ਹੈ:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

ਇਹ ਥੋੜਾ ਹੋਰ ਗੁੰਝਲਦਾਰ ਹੈ: ਅਸੀਂ ਇਹ ਦੇਖਣ ਲਈ ਜਾਂਚ ਕਰਦੇ ਹਾਂ ਕਿ ਕੀ ਇਸਦੇ ਵਿਸ਼ੇਸ਼ਤਾ ਕਾਲਮ ਵਿੱਚ JSON ਆਬਜੈਕਟ ਵਿੱਚ ਇੱਕ ਵਸਤੂ ਹੈ ਜੋ @> ਆਪਰੇਟਰ ਦੇ ਸੱਜੇ ਪਾਸੇ ਹੈ। ਘੱਟ ਪੜ੍ਹਨਯੋਗ, ਵਧੇਰੇ ਲਾਭਕਾਰੀ (ਹੇਠਾਂ ਦੇਖੋ)।

ਚਲੋ JSONB ਦੀ ਵਰਤੋਂ ਨੂੰ ਹੋਰ ਵੀ ਆਸਾਨ ਬਣਾਉ ਜਦੋਂ ਤੁਹਾਨੂੰ ਇੱਕ ਵਾਰ ਵਿੱਚ ਕਈ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਦੀ ਚੋਣ ਕਰਨ ਦੀ ਲੋੜ ਹੁੰਦੀ ਹੈ। ਇਹ ਉਹ ਥਾਂ ਹੈ ਜਿੱਥੇ JSONB ਪਹੁੰਚ ਅਸਲ ਵਿੱਚ ਆਉਂਦੀ ਹੈ: ਅਸੀਂ ਬਿਨਾਂ ਜੋੜਨ ਦੀ ਲੋੜ ਦੇ ਸਾਡੇ ਨਤੀਜੇ ਸੈੱਟ ਵਿੱਚ ਵਾਧੂ ਕਾਲਮਾਂ ਵਜੋਂ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਦੀ ਚੋਣ ਕਰਦੇ ਹਾਂ:

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

EAV ਨਾਲ ਤੁਹਾਨੂੰ ਹਰੇਕ ਸੰਪਤੀ ਲਈ 2 ਜੋੜਾਂ ਦੀ ਲੋੜ ਹੋਵੇਗੀ ਜਿਸ ਬਾਰੇ ਤੁਸੀਂ ਪੁੱਛਗਿੱਛ ਕਰਨਾ ਚਾਹੁੰਦੇ ਹੋ। ਮੇਰੀ ਰਾਏ ਵਿੱਚ, ਉਪਰੋਕਤ ਸਵਾਲ ਡੇਟਾਬੇਸ ਡਿਜ਼ਾਈਨ ਵਿੱਚ ਇੱਕ ਵਧੀਆ ਸਰਲਤਾ ਦਿਖਾਉਂਦੇ ਹਨ. JSONB ਸਵਾਲਾਂ ਨੂੰ ਕਿਵੇਂ ਲਿਖਣਾ ਹੈ, ਇਸ ਦੀਆਂ ਹੋਰ ਉਦਾਹਰਣਾਂ ਦੇਖੋ, ਇਸ ਵਿੱਚ ਵੀ ਇਹ ਪੋਸਟ.
ਹੁਣ ਪ੍ਰਦਰਸ਼ਨ ਬਾਰੇ ਗੱਲ ਕਰਨ ਦਾ ਸਮਾਂ ਹੈ.

ਉਤਪਾਦਕਤਾ

ਪ੍ਰਦਰਸ਼ਨ ਦੀ ਤੁਲਨਾ ਕਰਨ ਲਈ ਮੈਂ ਵਰਤਿਆ ਵਿਸ਼ਲੇਸ਼ਣ ਦੀ ਵਿਆਖਿਆ ਕਰੋ ਸਵਾਲਾਂ ਵਿੱਚ, ਐਗਜ਼ੀਕਿਊਸ਼ਨ ਟਾਈਮ ਦੀ ਗਣਨਾ ਕਰਨ ਲਈ। ਹਰੇਕ ਪੁੱਛਗਿੱਛ ਨੂੰ ਘੱਟੋ-ਘੱਟ ਤਿੰਨ ਵਾਰ ਚਲਾਇਆ ਗਿਆ ਸੀ ਕਿਉਂਕਿ ਪੁੱਛਗਿੱਛ ਯੋਜਨਾਕਾਰ ਨੂੰ ਪਹਿਲੀ ਵਾਰ ਜ਼ਿਆਦਾ ਸਮਾਂ ਲੱਗਦਾ ਹੈ। ਪਹਿਲਾਂ ਮੈਂ ਬਿਨਾਂ ਕਿਸੇ ਸੂਚਕਾਂਕ ਦੇ ਸਵਾਲਾਂ ਨੂੰ ਚਲਾਇਆ। ਸਪੱਸ਼ਟ ਤੌਰ 'ਤੇ, ਇਹ JSONB ਦਾ ਇੱਕ ਫਾਇਦਾ ਸੀ, ਕਿਉਂਕਿ EAV ਲਈ ਲੋੜੀਂਦੇ ਜੋੜ ਸੂਚਕਾਂਕ ਦੀ ਵਰਤੋਂ ਨਹੀਂ ਕਰ ਸਕਦੇ ਸਨ (ਵਿਦੇਸ਼ੀ ਕੁੰਜੀ ਖੇਤਰਾਂ ਨੂੰ ਸੂਚੀਬੱਧ ਨਹੀਂ ਕੀਤਾ ਗਿਆ ਸੀ)। ਇਸ ਤੋਂ ਬਾਅਦ ਮੈਂ EAV ਮੁੱਲ ਸਾਰਣੀ ਦੇ 2 ਵਿਦੇਸ਼ੀ ਕੁੰਜੀ ਕਾਲਮਾਂ 'ਤੇ ਇੱਕ ਸੂਚਕਾਂਕ ਬਣਾਇਆ, ਨਾਲ ਹੀ ਇੱਕ ਸੂਚਕਾਂਕ ਜਿਨ ਇੱਕ JSONB ਕਾਲਮ ਲਈ।

ਡੇਟਾ ਅੱਪਡੇਟ ਨੇ ਨਿਮਨਲਿਖਤ ਨਤੀਜੇ ਸਮੇਂ ਦੇ ਹਿਸਾਬ ਨਾਲ ਦਿਖਾਏ (ms ਵਿੱਚ)। ਨੋਟ ਕਰੋ ਕਿ ਪੈਮਾਨਾ ਲਘੂਗਣਕ ਹੈ:

ਅਸੀਂ ਦੇਖਦੇ ਹਾਂ ਕਿ JSONB EAV ਨਾਲੋਂ ਬਹੁਤ ਜ਼ਿਆਦਾ (> 50000-x) ਤੇਜ਼ ਹੈ ਜੇਕਰ ਤੁਸੀਂ ਉੱਪਰ ਦੱਸੇ ਕਾਰਨ ਕਰਕੇ, ਸੂਚਕਾਂਕ ਦੀ ਵਰਤੋਂ ਨਹੀਂ ਕਰਦੇ ਹੋ। ਜਦੋਂ ਅਸੀਂ ਪ੍ਰਾਇਮਰੀ ਕੁੰਜੀਆਂ ਨਾਲ ਕਾਲਮਾਂ ਨੂੰ ਸੂਚੀਬੱਧ ਕਰਦੇ ਹਾਂ, ਤਾਂ ਅੰਤਰ ਲਗਭਗ ਗਾਇਬ ਹੋ ਜਾਂਦਾ ਹੈ, ਪਰ JSONB ਅਜੇ ਵੀ EAV ਨਾਲੋਂ 1,3 ਗੁਣਾ ਤੇਜ਼ ਹੈ। ਨੋਟ ਕਰੋ ਕਿ JSONB ਕਾਲਮ 'ਤੇ ਸੂਚਕਾਂਕ ਦਾ ਇੱਥੇ ਕੋਈ ਪ੍ਰਭਾਵ ਨਹੀਂ ਹੈ ਕਿਉਂਕਿ ਅਸੀਂ ਮੁਲਾਂਕਣ ਦੇ ਮਾਪਦੰਡ ਵਿੱਚ ਪ੍ਰਾਪਰਟੀ ਕਾਲਮ ਦੀ ਵਰਤੋਂ ਨਹੀਂ ਕਰ ਰਹੇ ਹਾਂ।

ਸੰਪੱਤੀ ਮੁੱਲ ਦੇ ਆਧਾਰ 'ਤੇ ਡੇਟਾ ਦੀ ਚੋਣ ਕਰਨ ਲਈ, ਸਾਨੂੰ ਹੇਠਾਂ ਦਿੱਤੇ ਨਤੀਜੇ (ਆਮ ਪੈਮਾਨੇ) ਪ੍ਰਾਪਤ ਹੁੰਦੇ ਹਨ:

ਤੁਸੀਂ ਨੋਟ ਕਰ ਸਕਦੇ ਹੋ ਕਿ JSONB ਬਿਨਾਂ ਸੂਚਕਾਂਕ ਦੇ EAV ਨਾਲੋਂ ਤੇਜ਼ੀ ਨਾਲ ਕੰਮ ਕਰਦਾ ਹੈ, ਪਰ ਜਦੋਂ EAV ਸੂਚਕਾਂਕ ਦੇ ਨਾਲ, ਇਹ ਅਜੇ ਵੀ JSONB ਨਾਲੋਂ ਤੇਜ਼ੀ ਨਾਲ ਕੰਮ ਕਰਦਾ ਹੈ। ਪਰ ਫਿਰ ਮੈਂ ਦੇਖਿਆ ਕਿ JSONB ਪੁੱਛਗਿੱਛਾਂ ਲਈ ਸਮਾਂ ਇੱਕੋ ਜਿਹਾ ਸੀ, ਇਸ ਨੇ ਮੈਨੂੰ ਇਸ ਤੱਥ ਵੱਲ ਪ੍ਰੇਰਿਤ ਕੀਤਾ ਕਿ GIN ਸੂਚਕਾਂਕ ਕੰਮ ਨਹੀਂ ਕਰਦੇ ਹਨ. ਜ਼ਾਹਰ ਤੌਰ 'ਤੇ ਜਦੋਂ ਤੁਸੀਂ ਆਬਾਦੀ ਵਾਲੀਆਂ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਵਾਲੇ ਕਾਲਮ 'ਤੇ GIN ਸੂਚਕਾਂਕ ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹੋ, ਤਾਂ ਇਹ ਸਿਰਫ਼ ਉਦੋਂ ਹੀ ਪ੍ਰਭਾਵੀ ਹੁੰਦਾ ਹੈ ਜਦੋਂ ਸ਼ਾਮਲ ਓਪਰੇਟਰ @> ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹੋ। ਮੈਂ ਇਸਨੂੰ ਇੱਕ ਨਵੇਂ ਟੈਸਟ ਵਿੱਚ ਵਰਤਿਆ ਅਤੇ ਇਸਦਾ ਸਮੇਂ 'ਤੇ ਬਹੁਤ ਵੱਡਾ ਪ੍ਰਭਾਵ ਸੀ: ਸਿਰਫ 0,153ms! ਇਹ EAV ਨਾਲੋਂ 15000 ਗੁਣਾ ਤੇਜ਼ ਅਤੇ -->> ਆਪਰੇਟਰ ਨਾਲੋਂ 25000 ਗੁਣਾ ਤੇਜ਼ ਹੈ।

ਮੈਨੂੰ ਲਗਦਾ ਹੈ ਕਿ ਇਹ ਕਾਫ਼ੀ ਤੇਜ਼ ਸੀ!

ਡਾਟਾਬੇਸ ਸਾਰਣੀ ਦਾ ਆਕਾਰ

ਆਉ ਦੋਵਾਂ ਪਹੁੰਚਾਂ ਲਈ ਸਾਰਣੀ ਦੇ ਆਕਾਰ ਦੀ ਤੁਲਨਾ ਕਰੀਏ। psql ਵਿੱਚ ਅਸੀਂ ਕਮਾਂਡ ਦੀ ਵਰਤੋਂ ਕਰਕੇ ਸਾਰੇ ਟੇਬਲ ਅਤੇ ਇੰਡੈਕਸ ਦਾ ਆਕਾਰ ਦਿਖਾ ਸਕਦੇ ਹਾਂ dti+

EAV ਪਹੁੰਚ ਲਈ, ਸਾਰਣੀ ਦੇ ਆਕਾਰ ਲਗਭਗ 3068 MB ਹਨ ਅਤੇ ਕੁੱਲ 3427 GB ਲਈ 6,43 MB ਤੱਕ ਸੂਚਕਾਂਕ ਹਨ। JSONB ਪਹੁੰਚ ਸਾਰਣੀ ਲਈ 1817 MB ਅਤੇ ਸੂਚਕਾਂਕ ਲਈ 318 MB ਦੀ ਵਰਤੋਂ ਕਰਦੀ ਹੈ, ਜੋ ਕਿ 2,08 GB ਹੈ। ਇਹ 3 ਵਾਰ ਘੱਟ ਬਾਹਰ ਕਾਮੁਕ! ਇਸ ਤੱਥ ਨੇ ਮੈਨੂੰ ਥੋੜਾ ਹੈਰਾਨ ਕੀਤਾ ਕਿਉਂਕਿ ਅਸੀਂ ਹਰੇਕ JSONB ਵਸਤੂ ਵਿੱਚ ਜਾਇਦਾਦ ਦੇ ਨਾਮ ਸਟੋਰ ਕਰਦੇ ਹਾਂ।

ਪਰ ਫਿਰ ਵੀ, ਸੰਖਿਆਵਾਂ ਆਪਣੇ ਲਈ ਬੋਲਦੀਆਂ ਹਨ: EAV ਵਿੱਚ ਅਸੀਂ ਪ੍ਰਤੀ ਗੁਣ ਮੁੱਲ 2 ਪੂਰਨ ਅੰਕ ਵਿਦੇਸ਼ੀ ਕੁੰਜੀਆਂ ਨੂੰ ਸਟੋਰ ਕਰਦੇ ਹਾਂ, ਨਤੀਜੇ ਵਜੋਂ 8 ਬਾਈਟ ਵਾਧੂ ਡਾਟਾ ਮਿਲਦਾ ਹੈ। ਇਸ ਤੋਂ ਇਲਾਵਾ, EAV ਸਾਰੇ ਸੰਪੱਤੀ ਮੁੱਲਾਂ ਨੂੰ ਟੈਕਸਟ ਦੇ ਰੂਪ ਵਿੱਚ ਸਟੋਰ ਕਰਦਾ ਹੈ, ਜਦੋਂ ਕਿ JSONB ਸੰਖਿਆਤਮਕ ਅਤੇ ਬੂਲੀਅਨ ਮੁੱਲਾਂ ਦੀ ਵਰਤੋਂ ਅੰਦਰੂਨੀ ਤੌਰ 'ਤੇ ਕਰੇਗਾ ਜਿੱਥੇ ਸੰਭਵ ਹੋਵੇ, ਨਤੀਜੇ ਵਜੋਂ ਇੱਕ ਛੋਟਾ ਫੁੱਟਪ੍ਰਿੰਟ ਹੋਵੇਗਾ।

ਨਤੀਜੇ

ਕੁੱਲ ਮਿਲਾ ਕੇ, ਮੈਨੂੰ ਲਗਦਾ ਹੈ ਕਿ JSONB ਫਾਰਮੈਟ ਵਿੱਚ ਇਕਾਈ ਦੀਆਂ ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਨੂੰ ਸੁਰੱਖਿਅਤ ਕਰਨਾ ਤੁਹਾਡੇ ਡੇਟਾਬੇਸ ਨੂੰ ਡਿਜ਼ਾਈਨ ਕਰਨਾ ਅਤੇ ਸੰਭਾਲਣਾ ਬਹੁਤ ਸੌਖਾ ਬਣਾ ਸਕਦਾ ਹੈ। ਜੇ ਤੁਸੀਂ ਬਹੁਤ ਸਾਰੀਆਂ ਪੁੱਛਗਿੱਛਾਂ ਚਲਾ ਰਹੇ ਹੋ, ਤਾਂ ਹਰ ਚੀਜ਼ ਨੂੰ ਉਸੇ ਸਾਰਣੀ ਵਿੱਚ ਰੱਖਣਾ ਅਸਲ ਵਿੱਚ ਵਧੇਰੇ ਕੁਸ਼ਲਤਾ ਨਾਲ ਕੰਮ ਕਰੇਗਾ। ਅਤੇ ਇਹ ਤੱਥ ਕਿ ਇਹ ਡੇਟਾ ਦੇ ਵਿਚਕਾਰ ਆਪਸੀ ਤਾਲਮੇਲ ਨੂੰ ਸਰਲ ਬਣਾਉਂਦਾ ਹੈ ਪਹਿਲਾਂ ਹੀ ਇੱਕ ਪਲੱਸ ਹੈ, ਪਰ ਨਤੀਜਾ ਡੇਟਾਬੇਸ ਵਾਲੀਅਮ ਵਿੱਚ 3 ਗੁਣਾ ਛੋਟਾ ਹੈ.

ਨਾਲ ਹੀ, ਕੀਤੇ ਗਏ ਟੈਸਟਾਂ ਦੇ ਅਧਾਰ ਤੇ, ਅਸੀਂ ਇਹ ਸਿੱਟਾ ਕੱਢ ਸਕਦੇ ਹਾਂ ਕਿ ਪ੍ਰਦਰਸ਼ਨ ਦੇ ਨੁਕਸਾਨ ਬਹੁਤ ਮਾਮੂਲੀ ਹਨ। ਕੁਝ ਮਾਮਲਿਆਂ ਵਿੱਚ, JSONB EAV ਨਾਲੋਂ ਵੀ ਤੇਜ਼ ਹੈ, ਇਸ ਨੂੰ ਹੋਰ ਵੀ ਬਿਹਤਰ ਬਣਾਉਂਦਾ ਹੈ। ਹਾਲਾਂਕਿ, ਬੇਸ਼ੱਕ ਇਹ ਬੈਂਚਮਾਰਕ ਸਾਰੇ ਪਹਿਲੂਆਂ ਨੂੰ ਕਵਰ ਨਹੀਂ ਕਰਦਾ ਹੈ (ਜਿਵੇਂ ਕਿ ਬਹੁਤ ਵੱਡੀ ਸੰਖਿਆ ਵਾਲੀਆਂ ਸੰਪਤੀਆਂ, ਮੌਜੂਦਾ ਡੇਟਾ ਦੀਆਂ ਸੰਪਤੀਆਂ ਦੀ ਸੰਖਿਆ ਵਿੱਚ ਮਹੱਤਵਪੂਰਨ ਵਾਧਾ,...), ਇਸ ਲਈ ਜੇਕਰ ਤੁਹਾਡੇ ਕੋਲ ਉਹਨਾਂ ਨੂੰ ਸੁਧਾਰਨ ਬਾਰੇ ਕੋਈ ਸੁਝਾਅ ਹਨ , ਕਿਰਪਾ ਕਰਕੇ ਟਿੱਪਣੀਆਂ ਵਿੱਚ ਛੱਡਣ ਲਈ ਸੁਤੰਤਰ ਮਹਿਸੂਸ ਕਰੋ!

ਸਰੋਤ: www.habr.com