PostgreSQL jsonb: ოპერატორები, GIN ინდექსები და შეზღუდვები

გამოიყენე jsonb, არა json, და მხოლოდ იმ მონაცემებისთვის, რომელთა ფორმა ნამდვილად იცვლება: webhook-ების payload-ები, თითოეული კლიენტის პარამეტრები, პროდუქტის ატრიბუტები, რომლებიც კატეგორიის მიხედვით განსხვავდება. ველს ტექსტად ->>-ით ამოიღებ, დოკუმენტი მოცემულ ფრაგმენტს შეიცავს თუ არა, @>-ით შეამოწმებ, ინდექსს კი GIN-ად ააგებ jsonb_path_ops-ით, თუ უმეტესად @>-ს იყენებ. გაკვეთილის დანარჩენი ნაწილი დეტალებია, რომლებიც განსაზღვრავს, კარგად დაძველდება თუ არა ეს გადაწყვეტილება, და შემთხვევები, როცა ჩვეულებრივი სვეტი უკეთესი იქნებოდა.

გაფრთხილება მოკლედ: jsonb კარგი მეორე ინსტრუმენტია და ცუდი პირველი. ყველაფერი, რის მიხედვითაც ფილტრავ, join-ს აკეთებ, ალაგებ ან რაც უნიკალური უნდა იყოს, ნამდვილ სვეტში უნდა იყოს. დოკუმენტის სვეტი იმ ველების გრძელი კუდისთვისაა, რომლებსაც მთლიანად კითხულობ და იშვიათად ეძებ.

json და jsonb: რით განსხვავდება#

PostgreSQL-ს ორი JSON ტიპი აქვს და ისინი სხვადასხვანაირად იქცევა. json ზუსტად იმ ტექსტს ინახავს, რომელიც მიეცი, და ყოველ წვდომაზე თავიდან პარსავს. jsonb ერთხელ პარსავს, დაშლილ ბინარულ ფორმას ინახავს და ინდექსებს უჭერს მხარს. თითქმის ყოველთვის jsonb გჭირდება.

რას კარგავ jsonb-ის არჩევით:

• ჰარები და გასაღებების რიგი არ ინახება. {"b": 1, "a": 2} დაბრუნდება როგორც {"a": 2, "b": 1}: გასაღებები ჯერ სიგრძით, შემდეგ ანბანით არის დალაგებული.
• განმეორებადი გასაღებები ერთდება. თუ გასაღები ორჯერ გვხვდება, ბოლო მნიშვნელობა იმარჯვებს.
• ჩასმა ოდნავ ნელია, გარდაქმნის გამო.

json-ის არჩევის მხოლოდ ორი მიზეზია: გჭირდება ორიგინალი ტექსტი ბაიტი-ბაიტ (აუდიტის ჟურნალი იმისა, რაც მესამე მხარემ გამოგზავნა) ან მნიშვნელობას საერთოდ არასდროს კითხულობ. მაშინაც კი, ნედლი ტექსტი ხშირად text სვეტში ჯობს, რადგან განზრახვა უფრო გასაგებია.

რიცხვებზეც უნდა ითქვას. jsonb რიცხვებს PostgreSQL-ის numeric ტიპად ინახავს, ამიტომ დიდი მთელი და ათწილადი რიცხვები უცვლელად გადარჩება, JavaScript-ზე უკეთ. ხაფანგი გამოსვლისას არის: კლიენტი, რომელიც შედეგს double-ად პარსავს, 2^53-ის ზემოთ სიზუსტეს კარგავს, და ეს კლიენტის ბრალია, არა ბაზის.

სვეტი ისევე ცხადდება, როგორც ნებისმიერი სხვა:

CREATE TABLE app.events (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  tenant_id   bigint      NOT NULL,  kind        text        NOT NULL,  created_at  timestamptz NOT NULL DEFAULT now(),  payload     jsonb       NOT NULL DEFAULT '{}');INSERT INTO app.events (tenant_id, kind, payload) VALUES  (7, 'signup',  '{"plan":"pro","source":"ad","tags":["beta","eu"],"user":{"id":41,"email":"a@example.com"}}'),  (7, 'invoice', '{"total":19.90,"currency":"USD","paid":false}');

შეხედე, როგორ არის აგებული ცხრილი: tenant_id, kind და created_at სვეტებია, რადგან ყველა მოთხოვნა მათ იყენებს, ხოლო payload-ში მხოლოდ ცვალებადი ნაწილია. მთელი დიზაინი ამ გაყოფაზე დგას.

ოპერატორები, რომლებსაც ყოველდღე გამოიყენებ#

ორი ოჯახია: ოპერატორები, რომლებიც მნიშვნელობას ამოიღებს, და ოპერატორები, რომლებიც ამოწმებს. ადვილად ერევა, რადგან ერთმანეთს ჰგავს.

ერთპირიანი ისარი მნიშვნელობას jsonb-ად ტოვებს, ამიტომ ჯაჭვის გაგრძელება შეგიძლია. ორპირიანი ისარი ჩვეულებრივ text-ს გაძლევს, რაც ჯაჭვის ბოლოს ან WHERE პირობაში გჭირდება. მარჯვნივ მთელი რიცხვი მასივის ელემენტს ირჩევს, ათვლა ნულიდან იწყება, უარყოფითი კი ბოლოდან ითვლის.

SELECT payload -> 'user' ->> 'email'      AS email,       payload -> 'tags' ->> 0            AS first_tag,       (payload #>> '{user,id}')::bigint  AS user_idFROM app.eventsWHERE kind = 'signup';

ორი ქცევა ხალხს აბნევს. პირველი, არარსებული გასაღები SQL NULL-ს იძლევა და არა შეცდომას: ეს მოსახერხებელია, მაგრამ ბეჭდურ შეცდომებს მალავს - payload ->> 'plna' ჩუმად არაფერს დააბრუნებს. მეორე, JSON null და არარსებული გასაღები სხვადასხვა რამეა. '{"a": null}'::jsonb -> 'a' აბრუნებს jsonb მნიშვნელობას null, რაც SQL NULL არ არის; მასზე ->> კი SQL NULL-ს აბრუნებს. თუ შენი ლოგიკისთვის განსხვავება მნიშვნელოვანია, შეამოწმე ქვემოთ ცხრილში მოცემული ? ოპერატორით.

PostgreSQL 14-მა subscripting დაამატა, ამიტომ payload['user']['email'] ასევე მუშაობს და UPDATE-ში უფრო ბუნებრივად იკითხება. ის jsonb-ს აბრუნებს, ->-ის მსგავსად.

შემამოწმებელი ოპერატორები

ისინი boolean-ს აბრუნებს და WHERE-ში გამოიყენება.

შემცველობა (containment) კარგად ღირს შესასწავლად, რადგან ინდექსები სწორედ ამ ოპერატორის გარშემოა აგებული:

-- Events whose payload includes this fragmentSELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';-- Nested: the fragment must have the same structureSELECT id FROM app.events WHERE payload @> '{"user":{"id":41}}';-- Arrays: contains the element, in any positionSELECT id FROM app.events WHERE payload @> '{"tags":["beta"]}';-- A key exists, whatever its valueSELECT id FROM app.events WHERE payload ? 'currency';

შემცველობა სტრუქტურას ადარებს და არა ტექსტს. ფრაგმენტი {"tags":["beta"]} ემთხვევა დოკუმენტს ["eu","beta","x"]-ით, რადგან მასივის რიგი და ზედმეტი ელემენტები მნიშვნელობას არ აქვს. მაგრამ არ ემთხვევა დოკუმენტს, სადაც "beta" ერთი დონით ღრმაა ჩადგმული. ? ოპერატორი კი მხოლოდ ზედა დონის გასაღებებს ეძებს; user-ის შიგნით email-ს ვერ იპოვის. კითხვის ნიშანი ზოგ დრაივერში პარამეტრის ადგილმჭერებთან შეიძლება დაეჯახოს - ცნობილი შემთხვევა Java-ს JDBC დრაივერია - და ამ დროს ფუნქციები jsonb_exists, jsonb_exists_any და jsonb_exists_all გიშველის.

დოკუმენტის შეცვლა#

ერთი ველის შეცვლა ის ადგილია, სადაც jsonb ყველაზე ნაკლებად ჰგავს ჩვეულებრივ სვეტს. ადგილზე შეცვლა არ არსებობს; ახალ მნიშვნელობას აგებ.

-- Add or overwrite top-level keys (right side wins)UPDATE app.events SET payload = payload || '{"paid":true}' WHERE id = 2;-- Set a nested path; the last argument creates the key if it is missingUPDATE app.eventsSET payload = jsonb_set(payload, '{user,email}', '"new@example.com"', true)WHERE id = 1;-- Remove a key, or a pathUPDATE app.events SET payload = payload - 'source' WHERE id = 1;UPDATE app.events SET payload = payload #- '{user,email}' WHERE id = 1;

jsonb_set-ში ახალი მნიშვნელობა თავადაც ვალიდური jsonb უნდა იყოს, ამიტომ ზემოთ სტრიქონს ბრჭყალების ორი ფენა აქვს: შიდა ბრჭყალები მას JSON სტრიქონად აქცევს. ამის დავიწყება ამ ფუნქციის ყველაზე ხშირი შეცდომაა. || ზედაპირულია - ჩადგმულ ობიექტს მთლიანად ცვლის და არა ერწყმის - ამიტომ ღრმა შერწყმისთვის jsonb_set ან აპლიკაციის კოდი გჭირდება.

დოკუმენტები სტრიქონებიდან jsonb_build_object-ით და jsonb_agg-ით აიგება, საპირისპირო მიმართულებით კი სტრიქონების სიმრავლეს დამბრუნებელი ფუნქციებით იშლება:

-- One row per tagSELECT e.id, t.tagFROM app.events e,     jsonb_array_elements_text(e.payload -> 'tags') AS t(tag)WHERE e.kind = 'signup';-- One row per top-level keySELECT key, value FROM app.events e, jsonb_each(e.payload) WHERE e.id = 2;-- Build a document from a querySELECT jsonb_build_object('tenant', tenant_id, 'events', jsonb_agg(kind))FROM app.events GROUP BY tenant_id;

უფრო რთული სტრუქტურებისთვის jsonpath (jsonb_path_query, @?, @@) ფილტრს, მაგალითად $.items[*] ? (@.qty > 5), ერთი გამოსახულებით წერს. ძლიერი ინსტრუმენტია და იმის ცოდნა, რომ არსებობს, ღირს, მაგრამ მხოლოდ მათთვისაა იოლად წასაკითხი, ვინც მასთან უმუშავია, ამიტომ მაშინ მიმართე, როცა -> ჯაჭვები მძიმდება და არა ნაგულისხმევად. PostgreSQL 17-მა ასევე დაამატა SQL-ის სტანდარტული JSON_TABLE, რომელიც დოკუმენტს ერთი კლაუზით სტრიქონებად აქცევს; დაყრდნობამდე სერვერის ვერსია შეამოწმე.

jsonb-ის ინდექსაცია GIN-ით#

ინდექსის გარეშე ყოველი payload @> ... ფილტრი მთელ ცხრილს კითხულობს. GIN ინდექსი (generalised inverted) ამას ხსნის: დოკუმენტის შიგნით გასაღებებსა და მნიშვნელობებს აინდექსებს, ისევე, როგორც წიგნის საძიებელი სიტყვებს გვერდებს უკავშირებს. თუ ჯერ ზოგადი სურათი გინდა, PostgreSQL indexes: types, order and cost აჩვენებს, ინდექსის ტიპებს შორის სად დგას GIN.

ოპერატორების ორი კლასია და არჩევანს მნიშვნელობა აქვს:

-- Default: also answers key-existence queriesCREATE INDEX events_payload_gin ON app.events USING gin (payload);-- Smaller and quicker, containment and jsonpath onlyCREATE INDEX events_payload_path ON app.events USING gin (payload jsonb_path_ops);

პრაქტიკული წესი: თუ შენი ყველა მოთხოვნა @>-ია, აიღე jsonb_path_ops. ის გასაღებებსა და მნიშვნელობებს ცალ-ცალკე კი არ აინდექსებს, არამედ მნიშვნელობამდე მთელ გზას ჰეშავს, ამიტომ შესამჩნევად პატარაა და ხშირ გასაღებზე შემცველობას უკეთ უმკლავდება. თუ ? გჭირდება იმის გასაგებად, გასაღები არსებობს თუ არა, ნაგულისხმევი კლასი გჭირდება. ორივეს ერთად ყოლა შესაძლებელია, მაგრამ ერთ სვეტზე ორი GIN ინდექსი კომფორტისთვის მძიმე საფასურია.

შეამოწმე, რომ დამგეგმავი მას ნამდვილად იყენებს:

EXPLAIN (ANALYZE, BUFFERS)SELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';

გინდა ნახო Bitmap Index Scan GIN ინდექსზე, რომელიც Bitmap Heap Scan-ს კვებავს. GIN ყოველთვის bitmap სკანირებას იძლევა, ჩვეულებრივ index scan-ს არასდროს. EXPLAIN ANALYZE on a slow query ასეთ გეგმებს ხაზ-ხაზ კითხულობს.

რას არ აკეთებს GIN ინდექსი

აქედან მოდის იმედგაცრუების უმეტესობა.

• `->>` შედარებებს არ ეხმარება. WHERE payload ->> 'plan' = 'pro' ზემოთ მოცემულ GIN ინდექსს ვერ გამოიყენებს, რადგან ეს შემცველობის შემოწმება არ არის. გადაწერე როგორც payload @> '{"plan":"pro"}' ან ააგე გამოხატულების ინდექსი (შემდეგი ნაწილი).
• დიაპაზონებს და დალაგებას არ ეხმარება. GIN ინდექსს ვერ ჰკითხავ "total 100-ზე მეტია" და ველის მიხედვით ORDER BY-საც ვერ გააკეთებ.
• ნელა იწერება. GIN მოლოდინის სიას (fastupdate) იყენებს ჩასმების პაკეტებად დასამუშავებლად: საშუალოდ ეს დატვირთვას ასწორებს, მაგრამ ზოგი ჩასმა ცარიელებისთვის იხდის და vacuum-ს მეტი სამუშაო აქვს. ცხრილი ინტენსიური ჩასმებითა და დიდი GIN ინდექსით ამას ჩაწერის შეყოვნებაზე აჩვენებს.
• დიდი შეიძლება იყოს. ექვივალენტური B-tree-ზე რამდენჯერმე დიდი ჩვეულებრივი ამბავია. პატარა სერვერზე ის მეხსიერებისთვის იბრძვის შენს ცხელ მონაცემებთან.
• დამგეგმავს დოკუმენტის შიგნეულის სტატისტიკა არ აქვს. @>-ზე სელექტიურობას ხვდება, ამიტომ შეფასებები ხშირად მცდარია, ცუდმა შეფასებამ კი ცუდი გეგმა შეიძლება აირჩიოს. ეს ცნობილი შეზღუდვაა და არა კონფიგურაციის შეცდომა.

GIN ინდექსი jsonb-ზე ზოგადი დანიშნულების ინსტრუმენტია, რომელიც კითხვას "იპოვე დოკუმენტები, რომლებიც X-ს შეიცავს" კარგად პასუხობს. ველისთვის, რომლის მიხედვითაც მუდმივად ფილტრავ ან ალაგებ, უფრო ზუსტი რამ გჭირდება.

გამოხატულების ინდექსები ველებისთვის, რომლებსაც რეალურად ეძებ#

როცა ერთი-ორი ველი მთავარ დატვირთვას იღებს, პირდაპირ დააინდექსე ისინი B-tree-ით გამოხატულებაზე. ის უფრო პატარაა, შესანახად იაფია, დიაპაზონებსა და დალაგებას უჭერს მხარს და დამგეგმავს ნამდვილ სტატისტიკას აძლევს.

-- Equality on a text fieldCREATE INDEX events_plan ON app.events ((payload ->> 'plan'));-- Range on a numeric field: cast inside the indexCREATE INDEX events_total ON app.events (((payload ->> 'total')::numeric))  WHERE kind = 'invoice';-- Unique within a tenantCREATE UNIQUE INDEX events_user_once ON app.events (tenant_id, (payload #>> '{user,id}'))  WHERE kind = 'signup';

ორმაგი ფრჩხილები სავალდებულოა: გარე წყვილი ინდექსის სვეტების სიაა, შიდა კი გამოხატულებას აღნიშნავს. მოთხოვნამ გამოხატულება ზუსტად უნდა გაიმეოროს, ტიპის გადაყვანის ჩათვლით. თუ ინდექსი ((payload ->> 'total')::numeric)-ზეა, მაშინ WHERE payload ->> 'total' > '100' ტექსტს ადარებს და არაფერს იყენებს. ეს იგივე ხაფანგია, რომელიც ჩვეულებრივი ფუნქციებისთვის ინდექსების გზამკვლევშია აღწერილი, ხოლო ნაწილობრივი პირობა WHERE kind = 'invoice' ინდექსს მხოლოდ იმ სტრიქონებამდე ამცირებს, რომლებსაც ეს ველი საერთოდ აქვთ.

გამოხატულების ინდექსები კარგად ერწყმის CHECK შეზღუდვას, რომ ტიპი, რომელზეც გადაგყავს, ნამდვილად იყოს გარანტირებული:

ALTER TABLE app.events  ADD CONSTRAINT invoice_total_numeric  CHECK (kind <> 'invoice' OR jsonb_typeof(payload -> 'total') = 'number');

მის გარეშე ერთი სტრიქონი "total": "n/a"-ით გადაყვანას ტეხავს, ისიც მოთხოვნის შუაგულში და არა ჩასმისას. შეზღუდვები დოკუმენტის შიგნით სქემასთან ყველაზე ახლოს მდგომი რამაა და იაფია.

ველის ნამდვილ სვეტად აყვანა#

როცა ველი ყველა მოთხოვნაშია, მისი ამოღება შეწყვიტე. PostgreSQL 12-ს და უფრო ახალს შეუძლია ნამდვილი სვეტი დოკუმენტთან შეთანხმებულად ინახოს:

ALTER TABLE app.events  ADD COLUMN plan text GENERATED ALWAYS AS (payload ->> 'plan') STORED;CREATE INDEX events_plan_col ON app.events (plan);

სვეტი ჩაწერისას გამოითვლება, დისკზე ინახება, ისევე ინდექსდება, როგორც ნებისმიერი სხვა და SELECT *-ში ჩანს. პირდაპირ ვერ ჩაწერ; დოკუმენტს ცვლი და ის მიჰყვება. არსებულ დიდ ცხრილზე შენახული გენერირებული სვეტის დამატება ცხრილს თავიდან წერს, ამიტომ ეს მიიჩნიე ბლოკით მიგრაციად და არა ჩვეულებრივ ბრძანებად - უსაფრთხო თანმიმდევრობა აღწერილია schema migrations without downtime-ში.

ალტერნატივა ჩვეულებრივი სვეტია, რომელსაც აპლიკაცია ან backfill ავსებს, ხოლო დოკუმენტი ასლს ინახავს, სანამ არ ამოიღებ. მეტი სამუშაოა და მეტი მოქნილობა: ტიპების შეცვლა, NOT NULL-ის და გარე გასაღების დამატება შეგიძლია. გენერირებული სვეტები გარე გასაღები ვერ იქნება.

MySQL-ის მომხმარებლები ამ ხერხს გაიცნობენ - MySQL JSON-ს მხოლოდ ასე აინდექსებს - და MySQL JSON columns მის ანალოგს განიხილავს. PostgreSQL-ში ეს არასავალდებულოა, რადგან გამოხატულების ინდექსები არსებობს, მაგრამ გენერირებული სვეტი სქემას გულწრფელს ხდის იმაში, რომელი ველებია მნიშვნელოვანი.

რა ჯდება jsonb წარმადობაში#

jsonb-ს აქვს თვისება, რომელიც დატვირთული ცხრილის პირველ განახლებაზე ხალხს ბუნებს: დოკუმენტის ნებისმიერი ცვლილება მთელ მნიშვნელობას თავიდან წერს. PostgreSQL jsonb-ს ნაწილ-ნაწილ არასდროს ცვლის. 40 KB დოკუმენტში ერთი ლოგიკური მნიშვნელობის შეცვლა სტრიქონის ახალ 40 KB ვერსიას წერს, ძველი კი vacuum-ს ელოდება.

საცავი ასე მუშაობს:

• დაახლოებით 2 KB-ზე დიდი მნიშვნელობები იკუმშება და, თუ მაინც დიდია, სტრიქონიდან TOAST ცხრილში გადაიტანება. მოთხოვნა, რომელიც დიდი დოკუმენტის ერთ ველს კითხულობს, მაინც ვალდებულია მთლიანად წამოიღოს და გახსნას.
• TOAST-ში მყოფი jsonb-ის განახლება TOAST-ში ახალ მნიშვნელობას წერს. ძველი რჩება, სანამ vacuum არ მოაცილებს.
• სტრიქონის ნებისმიერი სვეტის განახლებამ, დოკუმენტთან დაუკავშირებელმაც კი, სტრიქონი შეიძლება თავიდან ჩაწეროს; heap-only განახლებები მხოლოდ მაშინაა შესაძლებელი, როცა არც ერთი დაინდექსებული სვეტი არ შეცვლილა.

შედეგები, ტკივილის სიხშირის მიხედვით:

1. დოკუმენტის შიგნით ცხელი მრიცხველები ცუდი იდეაა. jsonb_set(payload, '{views}', ...) გვერდის ყოველ ნახვაზე ინახული ინფორმაციის შეუსაბამო bloat-ს ქმნის. მრიცხველები ცალკე მთელი რიცხვის სვეტში ან ცხრილში შეინახე.
2. დიდი დოკუმენტები, რომლებიც ხშირად იცვლება, რამდენიმე სტრიქონში უნდა იყოს. 200 KB blob, რომელშიც მოთხოვნაზე ერთი ველი იცვლება, ქვეერთეულების მიხედვით სტრიქონებად დაყავი.
3. ფართო დოკუმენტები აზიანებს კითხვას, რომელსაც ერთი ველი სჭირდება. ვიწრო ცხრილი, სადაც ხშირად წაკითხული ველები სვეტებია, დანარჩენი კი payload-ში, მთლიანად payload-ისგან შემდგარ ცხრილს სჯობს.
4. vacuum-ს მეტი სამუშაო აქვს. vacuum and bloat-ში წერია, როგორ დაინახო, რომ მკვდარი ტუპლები autovacuum-ის დასუფთავებაზე სწრაფად გროვდება; ხშირად განახლებადი jsonb ცხრილები ამის გავრცელებული წყაროა.

მკაცრი ზღვარი 1 GB-ია მნიშვნელობაზე, რომელსაც არ უნდა მიუახლოვდე. პრაქტიკული ზღვარი ბევრად დაბალია: რამდენიმე ასეული კილობაიტის შემდეგ დოკუმენტი ფაილია, ფაილისთვის სწორი სახლი კი ფაილების საცავია.

როდის არ ვარგა jsonb#

გულწრფელი ტესტი: თუ შეგიძლია ველები, მათი ტიპები და სავალდებულოები ჩამოწერო, შენ სქემა გაქვს, ხოლო PostgreSQL-ის ცხრილები სქემისთვის საუკეთესო ინსტრუმენტია. jsonb არ ვარგა ამ შემთხვევებში.

• რელაციური მონაცემები მიმალული სახით. orders-ის შიგნით შეკვეთის ხაზების jsonb მასივს პროდუქტებზე გარე გასაღები ვერ ექნება, გაშლის გარეშე join-ს ვერ გაუკეთებ და პროდუქტის არსებობას ვერ უზრუნველყოფ. გამოიყენე შვილობილი ცხრილი.
• ველები, რომელთა მიხედვითაც ფილტრავ, ალაგებ ან აგრეგირებ. სვეტს აქვს სტატისტიკა, B-tree, ტიპი და შეზღუდვები. დოკუმენტის შიგნით გზას ამათგან არაფერი აქვს დამატებითი სამუშაოს გარეშე, SUM((payload ->> 'total')::numeric) მილიონობით სტრიქონზე კი SUM(total)-ზე ნელია.
• ყველაფერი, რაც უნიკალური ან სავალდებულო უნდა იყოს. სვეტზე NOT NULL, UNIQUE, REFERENCES და CHECK ერთი ხაზია. დოკუმენტის ველზე ეს გამოხატულების ინდექსები და ხელით დაწერილი შემოწმებებია, აპლიკაცია კი ერთ-ერთს დაივიწყებს.
• ხშირად განახლებადი პატარა მნიშვნელობები. მრიცხველები, დროშები, დროის ნიშნულები და სტატუსები, რომლებიც თითქმის ყოველ მოთხოვნაზე იცვლება, ზემოთ აღწერილ გადაწერას ყოველგვარი სარგებლის გარეშე იხდის.
• დიდი ბინარული შიგთავსი. base64 სურათები JSON-ში მეგაბაიტის სამი მეოთხედია უსარგებლოდ დახარჯული პარსინგი. ფაილი ობიექტების საცავში ჩადე, URL კი სტრიქონში.
• მთელი აპლიკაცია სქემის გარეშე. თუ ცხრილში ერთი id და ერთი data სვეტია, რელაციურის შიგნით დოკუმენტური ბაზა ააგე, დოკუმენტურზე უარესი ინსტრუმენტებით და რელაციურის გარანტიების გარეშე. ეს შეიძლება გააზრებული არჩევანი იყოს; PostgreSQL or MongoDB განიხილავს, როდის ჯობს მეორე ძრავა, MongoDB indexes and schema design კი აჩვენებს, როგორ გამოიყურება ალტერნატივა.

და შემთხვევები, როცა ის სწორი ინსტრუმენტია:

• მესამე მხარეების payload-ები, რომელთა ფორმას ვერ აკონტროლებ: ინახება როგორც მიიღე და უმეტესად მთლიანად იკითხება.
• მომხმარებლის მიერ განსაზღვრული ველები ან თითოეული tenant-ის პარამეტრები, სადაც ყოველ სტრიქონს თავისი გასაღებები აქვს.
• იშვიათი ატრიბუტები: კატალოგი, სადაც თითოეულ კატეგორიას ათიოდე თვისება აქვს და ორ კატეგორიას საერთო არ აქვს.
• მოვლენებისა და აუდიტის ჩანაწერები, ერთხელ დაწერილი და გამართვისას წაკითხული, სტაბილური ველებით (tenant_id, kind, created_at) ჩვეულებრივი სვეტების სახით.
• პროტოტიპები, გეგმით, რომ გადარჩენილი ველები სვეტებად აიყვანო.

ჩვევა, რომელიც მუშაობს: დაიწყე სვეტებით ყველაფრისთვის, რაც იცი, დაამატე ერთი jsonb სვეტი შიგთავსის მიხედვით დასახელებული (attributes, metadata, raw_payload) და ველი მისგან იმ დღეს გამოიტანე, როცა WHERE-ში მეორედ გამოჩნდება.

jsonb პატარა სერვერზე#

პატარა ინსტანციაზე ზემოთ ჩამოთვლილი ხარჯები მეხსიერებასა და დისკზე ჩნდება და არა CPU-ზე. GIN ინდექსმა, TOAST-მა და განახლებებიდან მიღებულმა bloat-მა ცხრილის სიდიდე შეიძლება გააორმაგოს, ზედმეტი გვერდები კი შენს სამუშაო სიმრავლეს shared_buffers-იდან განდევნის. ინდექსის დამატებამდე და მის შემდეგ ზომები შეამოწმე:

SELECT pg_size_pretty(pg_table_size('app.events'))   AS table_and_toast,       pg_size_pretty(pg_indexes_size('app.events')) AS indexes;SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scanFROM pg_stat_user_indexes WHERE relid = 'app.events'::regclass;

შევსებულ ცხრილზე ინდექსები CREATE INDEX CONCURRENTLY-ით ააგე, რომ ჩაწერა არ დაიბლოკოს, და სესიისთვის ჯერ maintenance_work_mem გაზარდე - GIN-ის აგებას ბევრი მეხსიერება სჭირდება. თუ ნაგულისხმევი GIN ინდექსიც გაქვს და გამოხატულების ინდექსებიც, ერთი კვირის შემდეგ idx_scan შეამოწმე და ის წაშალე, რომელსაც დამგეგმავი უგულებელყოფს. PostgreSQL tuning for small servers აღწერს მეხსიერების პარამეტრებს, რომლებზეც დამოკიდებულია, ეს ყველაფერი cache-ში დარჩება თუ არა, PostgreSQL with pg_stat_statements კი გაჩვენებს, რომელი jsonb მოთხოვნები ღირს რეალურად ძვირი, სანამ ინდექსს არასწორ ადგილას დაამატებ.

RE:NODE-ზე PostgreSQL სერვერი ცალკე ტარიფია საკუთარი მონაცემებით შესასვლელად, ამიტომ ინდექსის აგება და გამოცდა ასლზე პატარა ტარიფი ჯდება და არა სამუშაო ბაზის რისკი. დიდი ინდექსის აგებამდე ან ცხრილის გადამწერ მიგრაციამდე backup გააკეთე; სლოტები ტარიფშია, ნაბიჯები კი მონაცემთა ბაზებისა და backup-ების გზამკვლევშია ნაჩვენები.

FAQ#

PostgreSQL-ში json გამოვიყენო თუ jsonb?

თითქმის ყოველთვის jsonb. ის ინდექსირებადია, უფრო სწრაფად მოითხოვება და განმეორებად გასაღებებს აშორებს. json გამოიყენე მხოლოდ მაშინ, თუ ორიგინალი ტექსტი, ჰარებისა და გასაღებების რიგის ჩათვლით, უნდა შეინარჩუნო.

რატომ არ იყენებს ჩემი jsonb მოთხოვნა GIN ინდექსს?

ყველაზე ხშირად იმიტომ, რომ მოთხოვნა @>-ის ნაცვლად =-თან ->>-ს ან ->-ს იყენებს. GIN ინდექსი შემცველობისა და გასაღების არსებობის შემოწმებებს პასუხობს და არა ამოღებული მნიშვნელობების შედარებებს. გადაწერე მოთხოვნა @>-ზე ან შექმენი B-tree ინდექსი გამოხატულებაზე, რომელიც მოთხოვნის გამოხატულებას ზუსტად ემთხვევა.

jsonb_ops თუ jsonb_path_ops?

jsonb_path_ops, თუ მხოლოდ @>-ს და jsonpath ოპერატორებს იყენებ: ის უფრო პატარაა და ჩვეულებრივ უფრო სწრაფი. ნაგულისხმევი jsonb_ops აირჩიე, თუ გასაღებების შესამოწმებლად ?, ?| ან ?& გჭირდება.

jsonb ჩვეულებრივ სვეტებზე ნელია?

ველებზე, რომლებზეც ფილტრავ და აგრეგირებ, დიახ: ველების სტატისტიკა არ არის, სტრიქონები უფრო ფართოა და ყოველი განახლება მთელ მნიშვნელობას თავიდან წერს. დოკუმენტის მთლიანად შესანახად და დასაბრუნებლად განსხვავება მცირეა. ფასი იმაშია, რას მოითხოვ მისგან.

შეიძლება jsonb-ის შიგნით ველზე გარე გასაღები ან უნიკალურობის შეზღუდვა დავადო?

პირდაპირ არა. უნიკალურობას უნიკალური ინდექსი გამოხატულებაზე იძლევა, დოკუმენტის ფორმას კი CHECK შეზღუდვა ამოწმებს. გარე გასაღებს ნამდვილი სვეტი სჭირდება, ამიტომ ველი გენერირებული სვეტით ამოიღე ან თავად ცხრილში გადაიტანე.

რა ზომის შეიძლება იყოს jsonb მნიშვნელობა?

ზღვარი 1 GB-ია, მაგრამ წარმადობა მასზე ბევრად ადრე ეცემა. დაახლოებით 2 KB-ზე დიდი მნიშვნელობები იკუმშება ან TOAST საცავში გადადის, ნებისმიერი განახლება კი მთელ მნიშვნელობას თავიდან წერს. ხშირად განახლებადი დოკუმენტები პატარა გქონდეს, ფაილები კი ობიექტების საცავში შეინახე.