The goal of this document is to investigate about the PostgreSQL capability to handle Json documents.
Introduction
PostgreSQL offers two types for storing JSON data: json
and jsonb. T
he json
data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb
data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb
also supports indexing, which can be a significant advantage.
Because the json
type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb
does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.
Note
Using PostgreSQL special type as "json" or "jsonb" rather than "text", all applications will lose any compatibility with H2 and MariaDB. They will be mentioned in this document but not recommended.
Requirements
- Policy types/Policies/Node Types/Node Templates are first order items
- Data Types have a scope of a first order item, so a data type definition only applies in the scope of a policy type or node type definition
- We should keep our current APIs, all changes should be internal
- We must provide an upgrade path to the new data structure and a rollback to the current structure
ORM Layer using Document Storage
ORM layer using document storage (PostgreSQL or MongoDB) could be organized in two layer:
- Document layer (Domain Model to be converted in Json) - implementation has no dependency from DB
- Persistence layer - (Domain Model depend of the DB used): Entities for PostgreSQL, Documents for MongoDB
An implementation on the Document layer could be found here: (https://gerrit.nordix.org/c/onap/policy/models/+/13633)
Example
In the example below DocToscaServiceTemplate should be serialized to Json.
In the example below the implementation of JpaToscaServiceTemplate for PostgreSQL/MariaDB
In the example below the implementation of JpaToscaServiceTemplate for MongoDB
Converters
Jakarta and Spring do no support json Type, but we can use Converters to convert DocToscaServiceTemplate to a Json String.
Item Table
Work in progress ...
db_demo=# \d item
Table "public.item"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
key | character varying(255) | | not null |
document | jsonb | | not null |
properties | jsonb | | not null |
text | character varying(255) | | not null |
Indexes:
"item_pkey" PRIMARY KEY, btree (key)
db_demo=# select * from public.item; key | document | properties | text -----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+-------------------- 1 | {"name": "DocumentExample", "elements": {"additionalProp1": {"name": "ElementExample 1", "tags": ["tag1"]}, "additionalProp2": {"name": "ElementExample 2", "tags": ["tag2"]}, "additionalProp3": {"name": "ElementExample 3", "tags": ["tag3"]}}} | {"additionalProp1": "Val 10", "additionalProp2": "11", "additionalProp3": "12"} | This is an example (1 row)
db_demo=# SELECT Cast(jsonb_path_query_array(document['elements'], '$[*].keyvalue().value.tags') as varchar) FROM item; jsonb_path_query_array -------------------------------- [["tag1"], ["tag2"], ["tag3"]] (1 row)
Note
- Using document storage, it involves only the ORM layer, it does not change the functionality of the application
- After migration to document storage as ORM layer, it will possible to adjust flexibility of Tosca Service Template Handling (POLICY-3236)
Example in Policy Admin models
Work in progress ...
JpaToscaWithTypeAndStringProperties class
Converters
Table of comparison
Type | DataStore | Validation | Support Query | Index | Preserve Order |
---|---|---|---|---|---|
longtext | MariaDB | SELECT | Yes | No | Yes |
longtext | PostgreSQL | No | No | No | Yes |
json | PostgreSQL | INSERT / UPDATE | Yes | No | Yes |
jsonb | PostgreSQL | INSERT / UPDATE | Yes | Yes | No |
Proposals
Work in progress ...
My propose options are shown below:
- replace all ElementCollections with Converters:
- Is a fix and not a change of technologies
- Small Json instead of Java binary code
- compatible with H2, MariaDB and PostgreSQL
- it impacts only onap/policy/models, persistence classes used into ElementCollections have to change to a Document oriented classes
- 50% of tables (with postfix as _ATTRIBUTES, _PROPERTIES, _METADATA, _OCCURRENCES, _CONSTRAINTS, _TARGETS, _TRIGGERS and _INPUTS) will be removed
- Unit tests only onap/policy/models
- onap/policy/models still compatible with not Spring Applications
- Unique Json String:
- ToscaServiceTemplace saved as Json
- compatible with H2, MariaDB and PostgreSQL
- it impacts onap/policy/models and all applications related (runtime-acm, api and pap), persistence classes have to change to a Document oriented classes
- 90% of tables will be removed
- Unit tests for onap/policy/models and minor impact for all applications
- onap/policy/models still compatible with not Spring Applications
- MongoDB/Cassandra
- Document oriented approach full supported by SpringBoot (not needs Converters)
- it impacts onap/policy/models and all applications related (runtime-acm, api and pap), and all repositories and persistence classes have to change to a Document oriented classes
- compatible only with MongoDB/Cassandra (MongoDB and Cassandra are not compatible to each other)
- Unit tests need an Embedded Server (example for cassandra: EmbeddedCassandraServerHelper or CassandraContainer)
Benchmark Performance of Controlloop Runtime
In order to generate the benchmark I have used:
- Jmeter to generate requests (same used by performance tests): Requests/sec: 287
- Prometheus for monitoring
- DMaap simulator
- Participant simulator
- MariaDB/PostgreSQL/MongoDB
The existing system
Eclipse-Link/Mariadb. Tosca Service template is saved as a schema entity relation.
Postgres with the JSON store
Hibernate/PostgreSQL. Tosca Service Template is saved into a jsonb type.
The equivalent in MariaDB
Hibernate/Mariadb. Tosca Service Template is saved into a longtext as Json.
MongoDB
MongoDB. Tosca Service Template is saved as MongoDB Document.
- id cannot have dot '.' in MongoDB : solved with minimal configuration
Benchmark Performance of Policy Pap
In order to generate the benchmark I have used:
- Jmeter to generate requests (same used by performance tests)
- Prometheus for monitoring
- DMaap simulator
- MariaDB
The existing system
Eclipse-Link/Mariadb with dao pattern and atomic transactions. At that time the migration to Spring repositories are not finished yet, so the benchmark is calculate by endpoints performance ('server_requests' metric).
Conclusion
- Moving to Spring Data JPA there are already improvements
- NoSql does not have standard, so PostgreSQL jsonb, MongoDB and Cassandra are not compatible to each other
- There is much more support for MongoDB/Cassandra than PostgreSQL jsonb
- In MongoDB/Cassandra JSON queries are much more intuitive than PostgreSQL jsonb
- PostgreSql seems to be the fastest