EAV - jsonb

This is project is an implementation of the EAV (Entity-Attribute-Value) model based on PostgreSQL database using jsonb type.

This EAV-model combines the power of a relational database with the search speed of a non-relational database.

The idea was written here: https://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/

Getting started

1. Basis

1.1. Creating the structure

Run this console command to create EAV model tables in the database (need use the correct DSN value to connect to the PostgreSQL database):

php setup.php "PDO_PGSQL_DSN=pgsql:host=localhost;port=5432;dbname=postgres;user=postgres;password=mypass"

Will be output:

Successfully completed.

The result will be creating of 5 tables in your database with the prefix eavjsonb_:

eavjsonb_attribute
| id | name        |
| -- | ----------- |
| 1  | Attribute 1 |

eavjsonb_value
| id | attribute_id | name    |
| -- | ------------ | ------- |
| 1  | 1            | Value 1 |

eavjsonb_category
| id | name       |
| -- | ---------- |
| 1  | Category 1 |

eavjsonb_category_attribute
| id | attribute_id | category_id | is_diapason |
| -- | ------------ | ----------- | ----------- |
| 1  | 1            | 1           | false       |

eavjsonb_entity
| id | name     | category_id | attribute_values |
| -- | -------- | ----------- | ---------------- |
| 1  | Entity 1 | 1           | {"1": "Value 1"} |

Note: You can add PDO_PGSQL_DSN setting to your .env file (see .env.example file)

1.2. Test data generation

Test data is needed here. Test data can be generated by the following console command:

php generateTestData.php

Will be output:

Estimated execution time: 15 min

Successfully completed.

The result will be 10 million created entities with different attributes and values.

1.3. Data selecting test

You can test of data selecting from the EAV model by running the console command:

php test.php

The test result can be as follows:

Total records count: 10000000

Filtered records count where {"1":"Value 1"}: 1

Execution time: 4728.9 ms

2. Adding indexing

2.1. Next, run this console command to adding indexing in the database:

php setup2.php

The result of execution will be the creation index for jsonb field:

Estimated execution time: 18 min

Successfully completed.

2.2. Then it can be tested again by running the console command:

php test.php

Now the test result will be as follows:

Total records count: 10000000

Filtered records count where {"1":"Value 1"}: 1

Execution time: 0.8 ms

This is a stunning result! This is less than 1 ms per 10 million records!

Implementation

You can use your migration mechanism to pull changes from the database after creating the EAV model tables.

Alternative implementation

Alternative is manual creating your migration with SQL-queries which is in the src/Migration/Setup*.php files.