| Ratings | | Unique User Downloads | | Download Rankings |
| Not yet rated by the users | | Total: 28 | | All time: 11,156 This week: 455 |
|
| Description | | Author |
This package can measure the performance of PostgreSQL JSONB fields.
It implements a tool that measures the performance of several types of operations to manipulate records of tables of a PostgreSQL database with JSONB fields.
The package can executes several types of SQL queries with JSONB table fields and outputs the results in table that appears on a terminal shell console.
Currently, it can execute SQL queries to:
- Sorting results
- Filtering results using conditions for matching records with certain JSONB field values
- Aggregate results
- Get result averages
- Etc.. Innovation Award
June 2023
Number 6 |
JSON is a text format often used to represent complex data structures like objects and arrays.
JSONB is a modern type of field that the latest versions of the PostgreSQL database servers provide to store JSON data in binary format.
The possibility that PostgreSQL servers provide can make processing JSON data stored in PostgreSQL databases faster.
This PHP package implements a benchmark tool to measure the performance of accessing PostgreSQL database tables with JSONB fields for everyday database operations like indexing, searching, and filtering results.
Manuel Lemos |
| |
 |
|
Innovation award
Nominee: 18x |
|
Details
Postgres benchmark attribute-value table vs JSONB for store user-defined forms data
[ReadOnly]
Require PHP >=7.4; composer; docker
Usage
- clone repo
- run make init for install dependencies and run docker
- run make migrate for prepare database structure
- run make seed for fill databases with same fake data
- run make bench for run test queries across all databases
#### Results
Legend:
simple - stored in table (field, value)
typed - stored in table (field, value_bool, value_int, value_str, value_date... etc)
json - not indexed jsonb column
json_indx - btree indexed jsonb column
Show used queries
| query type | pg10 | pg11 | pg12 | pg13 |
|--------------------------------------|--------|--------|--------|--------|
|Sort|||||
| typed | 0.143 | 0.1435 | 0.1246 | 0.1212 |
| simple | 0.1391 | 0.151 | 0.1229 | 0.1022 |
| json | 0.0982 | 0.0973 | 0.0949 | 0.0933 |
| json_indx | 0.1 | 0.0963 | 0.0949 | 0.0928 |
| Multi sort|||||
| typed | 0.185 | 0.1856 | 0.1655 | 0.1675 |
| simple | 0.1971 | 0.1853 | 0.1657 | 0.1382 |
| json | 0.1243 | 0.1148 | 0.1138 | 0.1124 |
| json_indx | 0.13 | 0.1156 | 0.1147 | 0.1119 |
| Date filter|||||
| typed | 0.1305 | 0.1356 | 0.112 | 0.1154 |
| simple | 0.1492 | 0.1439 | 0.1165 | 0.0931 |
| json | 0.055 | 0.0622 | 0.051 | 0.0542 |
| json_indx | 0.056 | 0.0513 | 0.0515 | 0.0517 |
| Json filter|||||
| typed | 0.1286 | 0.1209 | 0.1057 | 0.1073 |
| simple | 0.1417 | 0.1291 | 0.1094 | 0.0861 |
| json | 0.0152 | 0.0163 | 0.014 | 0.0174 |
| json_indx | 0.0186 | 0.0212 | 0.015 | 0.0143 |
| Foreign key inject|||||
| typed | 0.1748 | 0.0953 | 0.0906 | 0.1419 |
| simple | 0.1679 | 0.1653 | 0.1471 | 0.1199 |
| json | 0.1197 | 0.1124 | 0.111 | 0.1107 |
| json_indx | 0.1169 | 0.1129 | 0.1106 | 0.1094 |
| Group by count|||||
| typed | 0.146 | 0.1454 | 0.1334 | 0.1273 |
| simple | 0.1532 | 0.1489 | 0.1293 | 0.1012 |
| json | 0.0178 | 0.0139 | 0.0194 | 0.014 |
| json_indx | 0.0173 | 0.0144 | 0.0143 | 0.0142 |
| Filters by bool,json,int sort by int|||||
| typed | 0.0913 | 0.0919 | 0.0816 | 0.0805 |
| simple | 0.142 | 0.1315 | 0.1142 | 0.0993 |
| json | 0.0474 | 0.0399 | 0.0391 | 0.0378 |
| json_indx | 0.0462 | 0.04 | 0.0388 | 0.0389 |
| Inject fk, filter and sort by date|||||
| typed | 0.0396 | 0.034 | 0.0902 | 0.0366 |
| simple | 0.1656 | 0.1446 | 0.1269 | 0.1139 |
| json | 0.0472 | 0.0398 | 0.0389 | 0.0393 |
| json_indx | 0.0408 | 0.0432 | 0.0469 | 0.0391 |
| Average from filtered|||||
| typed | 0.0896 | 0.0783 | 0.0717 | 0.0683 |
| simple | 0.1317 | 0.1196 | 0.1034 | 0.0914 |
| json | 0.0246 | 0.0131 | 0.0133 | 0.0133 |
| json_indx | 0.0285 | 0.0137 | 0.0135 | 0.0132 |
| Filter greater than average value|||||
| typed | 0.1983 | 0.1797 | 0.1604 | 0.1597 |
| simple | 0.2658 | 0.2457 | 0.2119 | 0.1736 |
| json | 0.0463 | 0.0355 | 0.0351 | 0.0341 |
| json_indx | 0.051 | 0.0369 | 0.0353 | 0.0344 |
| |
Applications that use this package |
|
No pages of applications that use this class were specified.
If you know an application of this package, send a message to the author to add a link here.
Download
Files
Install with Composer
composer.json
