Αντικατάσταση EAV με JSONB στο PostgreSQL

TL; DR: Το JSONB μπορεί να απλοποιήσει σημαντικά την ανάπτυξη σχημάτων βάσης δεδομένων χωρίς να θυσιάζει την απόδοση των ερωτημάτων.

Εισαγωγή

Ας δώσουμε ένα κλασικό παράδειγμα πιθανώς μιας από τις παλαιότερες περιπτώσεις χρήσης στον κόσμο μιας σχεσιακής βάσης δεδομένων (βάσης δεδομένων): έχουμε μια οντότητα και πρέπει να αποθηκεύσουμε ορισμένες ιδιότητες (χαρακτηριστικά) αυτής της οντότητας. Ωστόσο, ενδέχεται να μην έχουν όλες οι παρουσίες το ίδιο σύνολο ιδιοτήτων και ενδέχεται να προστεθούν περισσότερες ιδιότητες στο μέλλον.

Ο ευκολότερος τρόπος για να λύσετε αυτό το πρόβλημα είναι να δημιουργήσετε μια στήλη στον πίνακα της βάσης δεδομένων για κάθε τιμή ιδιότητας και απλώς να συμπληρώσετε αυτές που χρειάζονται για μια συγκεκριμένη παρουσία οντότητας. Εξαιρετική! Το πρόβλημα λύθηκε... έως ότου ο πίνακας σας περιέχει εκατομμύρια εγγραφές και πρέπει να προσθέσετε μια νέα εγγραφή.

Εξετάστε το μοτίβο EAV (Οντότητα-Χαρακτηριστικό-Τιμή), εμφανίζεται αρκετά συχνά. Ένας πίνακας περιέχει οντότητες (εγγραφές), ένας άλλος πίνακας περιέχει ονόματα ιδιοτήτων (χαρακτηριστικά) και ένας τρίτος πίνακας συσχετίζει οντότητες με τα χαρακτηριστικά τους και περιέχει την τιμή αυτών των χαρακτηριστικών για την τρέχουσα οντότητα. Αυτό σας δίνει τη δυνατότητα να έχετε διαφορετικά σύνολα ιδιοτήτων για διαφορετικά αντικείμενα και επίσης να προσθέτετε ιδιότητες αμέσως χωρίς να αλλάξετε τη δομή της βάσης δεδομένων.

Ωστόσο, δεν θα έγραφα αυτήν την ανάρτηση αν δεν υπήρχαν κάποια μειονεκτήματα στην προσέγγιση EVA. Έτσι, για παράδειγμα, για να αποκτήσετε μία ή περισσότερες οντότητες που έχουν 1 χαρακτηριστικό η καθεμία, απαιτούνται 2 ενώσεις στο ερώτημα: η πρώτη είναι μια ένωση με τον πίνακα χαρακτηριστικών, η δεύτερη είναι μια ένωση με τον πίνακα τιμών. Εάν μια οντότητα έχει 2 χαρακτηριστικά, τότε χρειάζονται 4 ενώσεις! Επιπλέον, όλα τα χαρακτηριστικά αποθηκεύονται συνήθως ως συμβολοσειρές, γεγονός που έχει ως αποτέλεσμα τη μετάδοση τύπων τόσο για το αποτέλεσμα όσο και για τον όρο WHERE. Εάν γράφετε πολλά ερωτήματα, τότε αυτό είναι αρκετά σπατάλη όσον αφορά τη χρήση των πόρων.

Παρά αυτές τις προφανείς ελλείψεις, το EAV έχει χρησιμοποιηθεί από καιρό για την επίλυση αυτού του τύπου προβλημάτων. Αυτές ήταν αναπόφευκτες ελλείψεις και απλώς δεν υπήρχε καλύτερη εναλλακτική.
Αλλά τότε μια νέα «τεχνολογία» εμφανίστηκε στο PostgreSQL...

Ξεκινώντας με την PostgreSQL 9.4, προστέθηκε ο τύπος δεδομένων JSONB για την αποθήκευση δυαδικών δεδομένων JSON. Αν και η αποθήκευση JSON σε αυτήν τη μορφή απαιτεί συνήθως λίγο περισσότερο χώρο και χρόνο από το JSON απλού κειμένου, η εκτέλεση λειτουργιών σε αυτό είναι πολύ πιο γρήγορη. Το JSONB υποστηρίζει επίσης την ευρετηρίαση, η οποία κάνει τα ερωτήματα ακόμα πιο γρήγορα.

Ο τύπος δεδομένων JSONB μας επιτρέπει να αντικαταστήσουμε το δυσκίνητο μοτίβο EAV προσθέτοντας μόνο μία στήλη JSONB στον πίνακα οντοτήτων μας, απλοποιώντας σημαντικά τον σχεδιασμό της βάσης δεδομένων. Πολλοί όμως υποστηρίζουν ότι αυτό πρέπει να συνοδεύεται από μείωση της παραγωγικότητας... Γι' αυτό έγραψα αυτό το άρθρο.

Ρύθμιση μιας βάσης δεδομένων δοκιμής

Για αυτήν τη σύγκριση, δημιούργησα τη βάση δεδομένων σε μια νέα εγκατάσταση του PostgreSQL 9.5 στην έκδοση $80 DigitalOcean Ubuntu 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"
  } 
}

Τώρα λοιπόν έχουμε τα ίδια δεδομένα και για τις δύο επιλογές. Ας αρχίσουμε να συγκρίνουμε υλοποιήσεις στη δουλειά!

Απλοποιήστε το σχέδιό σας

Προηγουμένως αναφέρθηκε ότι ο σχεδιασμός της βάσης δεδομένων απλοποιήθηκε πολύ: ένας πίνακας, χρησιμοποιώντας μια στήλη JSONB για ιδιότητες, αντί για χρήση τριών πινάκων για EAV. Πώς όμως αντανακλάται αυτό στα αιτήματα; Η ενημέρωση μιας ιδιότητας οντότητας μοιάζει με αυτό:

-- 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 κερδίζει εδώ! Χρησιμοποιούμε τον τελεστή JSON ->> για να λάβουμε το χρώμα ως τιμή κειμένου από ένα αντικείμενο JSONB. Υπάρχει επίσης ένας δεύτερος τρόπος για να επιτευχθεί το ίδιο αποτέλεσμα στο μοντέλο 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 δεν μπορούσαν να χρησιμοποιήσουν ευρετήρια (τα πεδία ξένων κλειδιών δεν είχαν ευρετηριαστεί). Μετά από αυτό δημιούργησα ένα ευρετήριο στις 2 στήλες ξένων κλειδιών του πίνακα τιμών EAV, καθώς και ένα ευρετήριο GIN για μια στήλη JSONB.

Η ενημέρωση δεδομένων έδειξε τα ακόλουθα αποτελέσματα από άποψη χρόνου (σε ms). Σημειώστε ότι η κλίμακα είναι λογαριθμική:

Βλέπουμε ότι το JSONB είναι πολύ (> 50000-x) ταχύτερο από το EAV εάν δεν χρησιμοποιείτε ευρετήρια, για τον λόγο που αναφέρθηκε παραπάνω. Όταν ευρετηριάζουμε στήλες με πρωτεύοντα κλειδιά, η διαφορά σχεδόν εξαφανίζεται, αλλά το JSONB εξακολουθεί να είναι 1,3 φορές ταχύτερο από το EAV. Σημειώστε ότι το ευρετήριο στη στήλη JSONB δεν έχει καμία επίδραση εδώ, καθώς δεν χρησιμοποιούμε τη στήλη ιδιοτήτων στα κριτήρια αξιολόγησης.

Για την επιλογή δεδομένων με βάση την αξία ιδιότητας, έχουμε τα ακόλουθα αποτελέσματα (κανονική κλίμακα):

Μπορείτε να παρατηρήσετε ότι το JSONB λειτουργεί και πάλι πιο γρήγορα από το EAV χωρίς ευρετήρια, αλλά όταν το EAV με ευρετήρια, εξακολουθεί να λειτουργεί πιο γρήγορα από το JSONB. Αλλά μετά είδα ότι οι χρόνοι για τα ερωτήματα JSONB ήταν οι ίδιοι, αυτό με ώθησε στο γεγονός ότι τα ευρετήρια GIN δεν λειτουργούν. Προφανώς όταν χρησιμοποιείτε ένα ευρετήριο GIN σε μια στήλη με συμπληρωμένες ιδιότητες, ισχύει μόνο όταν χρησιμοποιείτε τον τελεστή συμπερίληψης @>. Το χρησιμοποίησα σε μια νέα δοκιμή και είχε τεράστιο αντίκτυπο στον χρόνο: μόνο 0,153ms! Αυτό είναι 15000 φορές ταχύτερο από το EAV και 25000 φορές ταχύτερο από τον χειριστή ->>.

Νομίζω ότι ήταν αρκετά γρήγορο!

Μέγεθος πίνακα βάσης δεδομένων

Ας συγκρίνουμε τα μεγέθη του πίνακα και για τις δύο προσεγγίσεις. Στην psql μπορούμε να δείξουμε το μέγεθος όλων των πινάκων και των ευρετηρίων χρησιμοποιώντας την εντολή dti+

Για την προσέγγιση EAV, τα μεγέθη πινάκων είναι περίπου 3068 MB και ευρετήρια έως 3427 MB για συνολικά 6,43 GB. Η προσέγγιση JSONB χρησιμοποιεί 1817 MB για τον πίνακα και 318 MB για τα ευρετήρια, δηλαδή 2,08 GB. Αποδεικνύεται 3 φορές λιγότερο! Αυτό το γεγονός με εξέπληξε λίγο γιατί αποθηκεύουμε ονόματα ιδιοτήτων σε κάθε αντικείμενο JSONB.

Ωστόσο, οι αριθμοί μιλούν από μόνοι τους: στο EAV αποθηκεύουμε 2 ακέραια ξένα κλειδιά ανά τιμή χαρακτηριστικού, με αποτέλεσμα 8 byte πρόσθετων δεδομένων. Επιπλέον, το EAV αποθηκεύει όλες τις τιμές ιδιοτήτων ως κείμενο, ενώ το JSONB θα χρησιμοποιεί εσωτερικά αριθμητικές και boolean τιμές όπου είναι δυνατόν, με αποτέλεσμα μικρότερο αποτύπωμα.

Αποτελέσματα της

Συνολικά, πιστεύω ότι η αποθήκευση ιδιοτήτων οντοτήτων σε μορφή JSONB μπορεί να κάνει το σχεδιασμό και τη συντήρηση της βάσης δεδομένων σας πολύ πιο εύκολη. Εάν εκτελείτε πολλά ερωτήματα, τότε κρατώντας τα πάντα στον ίδιο πίνακα με την οντότητα θα λειτουργήσει πραγματικά πιο αποτελεσματικά. Και το γεγονός ότι αυτό απλοποιεί την αλληλεπίδραση μεταξύ των δεδομένων είναι ήδη ένα πλεονέκτημα, αλλά η βάση δεδομένων που προκύπτει είναι 3 φορές μικρότερη σε όγκο.

Επίσης, με βάση τα τεστ που έγιναν, μπορούμε να συμπεράνουμε ότι οι απώλειες απόδοσης είναι πολύ ασήμαντες. Σε ορισμένες περιπτώσεις, το JSONB είναι ακόμη πιο γρήγορο από το EAV, καθιστώντας το ακόμα καλύτερο. Ωστόσο, αυτό το σημείο αναφοράς φυσικά δεν καλύπτει όλες τις πτυχές (π.χ. οντότητες με πολύ μεγάλο αριθμό ιδιοκτησιών, σημαντική αύξηση στον αριθμό των ιδιοτήτων των υπαρχόντων δεδομένων,...), οπότε αν έχετε οποιεσδήποτε προτάσεις για το πώς να τις βελτιώσετε , μην διστάσετε να αφήσετε στα σχόλια!

Πηγή: www.habr.com