The goal of this issue is to investigate possible solutions to a set of performance and scalability problems that have affected Piwik since the beginning.

Use cases covered:

The following features don't work well when a datatable has thousands of sub-tables, as they will try to load in memory all sub-tables at once (can be many thousands tables used to build one hierarchical table report):

• Search - Searching recursively through a hierarchical DataTable
• Flatten - Flattening a hierarchical data table into one-level report (Page URLs, Page titles, Events, ....)
• Date range - Custom Date range processing of Actions reports (covered in Custom date range reports are slow: how to make them archive faster? #4768)

Possible solutions or ideas

• Store blobs as XML instead of gzip serialized (helps Search)
• Store blobs as JSON instead of gzip (can we search through JSON cells as with modern Postgresql?)
• Store all sub-tables in the same piwik_archive_blob_ row instead of storing each sub-table in a new piwik_archive_blob_ row.
• Experiment with different ways of storing millions of sub-tables (eg. store them in 100 chunks, or store them in equal chunks of ~50k rows)
• Pre-process the Flattened reports (help Flatten)?

see general performance issue #6759, date range issues #4768, flatten performance issues #5098