-
Notifications
You must be signed in to change notification settings - Fork 31
Performance
CasePro deployments may accumulate millions of messages and need to provide fast searchable access to them by users with different access restrictions.
This is the view of all labelled non-archived messages that a user has access to. Org-level users have access to all labels so they see all messages with any label. Partner-level users may see the same if their partner org isn't restricted, or may see messages with a subset of the org's labels.
To optimize this view, a Message.has_labels flag is maintained using database-level triggers, and a partial index then uses that to include only labelled messages:
CREATE INDEX msgs_inbox ON msgs_message(org_id, created_on DESC)
WHERE is_active = TRUE AND is_handled = TRUE AND is_archived = FALSE AND has_labels = TRUE;Filtering by label currently relies on msgs_message_labels and its indexes being in memory to be performant. At some point it may be necessary to have some fields from Message mirrored on to that table so that partial indexes can be used.
The charts of replies sent per month at the org, partner and user levels are generated from per-day counts of replies (statistics.DailyCount). To avoid deadlocks, new items are always recorded by inserting a new count of 1 rather than updating an existing count for that day. When counts are read from the database, they are summed for each day or month. A scheduled background process is then responsible for squashing those counts into a single count per day, so that the count table doesn't get too big.
Total counts can be calculated from the daily counts but in some cases (e.g. all labels displayed on the inbox page with their counts), it's better to maintain a separate total count (statistics.TotalCount).
These are maintained with database-level triggers on msgs_message and msgs_message_labels.
There is a utility command perftest which is designed to be run on a real world database. It has a set of views which it will measure access time to, logged in as different users - an administrator user from each org, and a user from each partner org.
$ python manage.py perftest
Problems...
> /outgoing/search_replies/?page=1 15.535 secs (org='Org #1', partner='')
> /message/search/?folder=archived&page=1 5.430 secs (org='Other Org', partner='McPartners Ltd')