Performance testing

Hardware

Senesi Dataset

Performance results

104 - 129x PostgreSQL, 5.9 - 12x Elasticsearch

Performance results

80 - 190x PostgreSQL, 20 - 24x Elasticsearch

Performance results

38 - 54x PostgreSQL, 12 - 15x Elasticsearch

PostgreSQL implementation

• tables used only as "indexes"
• entities are stored as blob (documents)
• facets are stored as JSON of precomputed data

The most visible change is the shift in the purpose of tables representing the inner entity datasets (attributes, prices, and references). In the original model, all tables were used to search entities as well as to reconstruct entities into memory which turned out to be quite expensive. Therefore, in the new model, entities are reconstructed from binary form, and tables of these collections are now used only as indexes to search entities.

This layout complicates transactional updates (lock per document).

PostgreSQL implementation

• 4x speedup was when inserts were trapped in memory during entity inserts
  and flushed in batch updates (inversion of data loader pattern but for writes)
• in warming phase no indexes were present,
  create index statements were issued when the database was filled up

This means, that those data cannot be queried at the same time.

PostgreSQL implementation

• frequent usage of CTEs
• hierarchy is computed by PMPTT algorithm instead of recursive query
• instead of external business keys (entityId + id), or simple int ids per collection
  they generate unique id for all entities from single sequence

Elasticsearch implementation

Storing single entity into a single document was not enough.

Multiple indexes are affected by entity upsert / delete.

This introduces consistency problems - since ES guarantee atomicity on document level only.

• locking (global, index Locking, document, tree)
  no automatic unlock mechanism when process is killed
• introduce 2-phase commit on application level
• or something like compensation process on rollback
• this was not implemented in the prototype and
  would further complicate existing code and limit write performance

Elasticsearch implementation

• PK sequence was solved by single document versioning process
  it's slow when fetching 1-by-1, and ids were fetched in bulk (hundreds)
• hierarchical queries are not supported by ES and PMPTT approach was used in the end
  there is alternative maintaining `/parent/path/structure` with wildcard matching
  but it cannot be easily used for category statistics and was quite slow
• facet queries couldn't have been used either
  because we need to support different relation operations (not)
  impact computation triggers a bunch of parallel queries for each possible combination
  parallel queries deplete thread pools in a unpredictable fashion

Elasticsearch implementation

Price calculation is complex and required using "painless scripting language" to produce runtime field and post-filter is used for "between" constraint, this prevents valid evaluation of queries like:

or(
      and(
            facet("Category", 5),
            startsWith("code, "Gorgeous-Rubber-Wallet-2-15"),
            priceBetween(800, 2000)
      ),
      facet("Category", 1)
)
				

Elasticsearch implementation

• team fought with the memory consumption trying to balance performance achieved by denormalization and the size of the index held in memory
• when ES needed more memory than available the ES shuts down unpredictably due to:
  • the indexed data size
  • complex aggregations requiring a lot of operational memory
• changes in the API:

  According to the documentation, several different functionalities can be used for this in ES, starting with the enabled tag, indexed, or even the field type (object) itself. Unfortunately, the use of these features in the mapping varied in different versions of ES, and therefore, the references in different parts of the documentation were not consistent about which attribute did what.

• high level client deprecated with no mention of compatibility mode in documentation
• hard to debug - the DSL is very verbose, the queries span over thousands lines of code
  may be that's why query visualizers exist?!