io.Manager Documentation

Databases

Overview

io.Manager supports connecting to MongoDB, PostgreSQL and Microsoft SQL Server databases. The default io.Manager deployment includes a MongoDB database.

The minimum required versions of the supported databases are as follows:

  • MongoDB 4.4 or later;
  • PostgreSQL 12 or later;
  • Microsoft SQL Server 2016 or later;

This section offers general guidance on determining the recommended database size for your io.Manager deployment. Providing an exact figure is challenging due to several factors:

  • The number of apps vary in each environment.

  • Apps can store custom data through the "customProperties" property in the app definition, which means that the size of the app table is not only dependent on the number of apps, but on the amount of custom data stored in each definition.

  • If you allow users to store custom Global Layouts or Workspaces, the number of Layouts and the size of the Layouts stored depends on the average number of Layouts stored per user.

  • Apps can store custom data when being saved in Layouts, which means that Layout size is not only dependent on the number of apps saved in the Layout, but on the amount of custom data stored by each app.

  • The App Preferences API allows apps to store custom data in the server per user. This means that the size of the preferences table is undeterminable and depends on the amount of custom data stored by each app and the number of users.

  • The sessions table depends on the usage of the system. Each time a user opens a session to the server, it creates an entry in the sessions table. There is no internal pruning mechanism for these tables, so their size will grow indefinitely.

  • The audit logs table depend on the usage of the system. Each time data is modified (e.g., creating apps or Layouts, updating preferences and so on) the server will create an audit entry in the audit table. There is no internal pruning mechanism for these tables, so their size will grow indefinitely.

  • The feedback and crashes tables depend on the rate of feedback and crashes generated by the users. There is no internal pruning mechanism for these tables, so their size will grow indefinitely.

The following table summarizes the typical sizes of single entities for the different database tables:

Table Typical Single Entity Size Notes
apps 2-10 KB The total size depends on the number of apps in an environment and the custom data stored in app definitions.
audit less than 1KB The total size depends on the system usage. No pruning mechanism, size grows indefinitely.
commands 1 KB -
commands_for_versions negligible -
crashes 5 MB The total size depends on the rate of crashes generated by users. No pruning mechanism, size grows indefinitely.
feedbacks 5 MB The total size depends on the rate of feedback generated by users. No pruning mechanism, size grows indefinitely.
groups less than 1 KB -
last_updated negligible -
layouts 2 - 100 KB The total size depends on the average number of Layouts saved per user and the data stored by apps in Layouts.
machines 2 KB -
prefs undeterminable The total size depends on the amount of preferences data stored by each app per user.
sessions 1 KB The total size depends on the system usage. No pruning mechanism, size grows indefinitely.
system_configs negligible -
users less than 1 KB -

To create an approximation about the size of the database in MB, you can use the following formula:

Size =
    // App definition size.
    NUM_APPS * 0.01 +
    // Layouts size.
    NUM_USERS * AVG_LAY_USER * 0.1 +
    // Preferences stored by apps per user.
    NUM_APPS * NUM_USERS * AVG_APP_PREFS +
    // Custom data stored in app definitions.
    NUM_APPS * AVG_APP_CD +
    // Custom data stored in Layouts by apps.
    NUM_USERS * AVG_LAY_USER * AVG_LAY_CD +
    // Data per machine per user.
    NUM_USERS * 0.002

Where:

Variable Description
AVG_APP_PREFS Average app preferences per app in MB.
AVG_APP_CD Average custom data stored in app definitions in MB.
AVG_LAY_CD Average custom data saved by apps in Layouts in MB.
AVG_LAY_USER The average number of Layouts saved per user.
NUM_APPS The number of apps in your environment.
NUM_USERS The number of users in your environment.

The size will also grow a certain amount in MB per month. The formula for size growth works with the following assumptions:

  • 1 session per day per user;
  • 0.1% of users report feedback per month;
  • 0.01% of users experience a crash per month;
  • 100000 data update operations per month resulting in audit log entries;
Growth =
    // Session data.
    NUM_USERS * 0.001 * 30 +
    // Feedback data.
    NUM_USERS * 0.1 * 5 +
    // Crashes data.
    NUM_USERS * 0.01 * 5 +
    // Data update operations.
    100000 * 0.001

The following is an example approximation of the database size and monthly growth size in MB:

NUM_APPS = 100
AVG_LAY_USER = 5
NUM_USERS = 10000
// Not every app stores custom data.
AVG_APP_CD = 0.01
// Not every app saves data in Layouts.
AVG_LAY_CD = 0.01
// Not every app stores preferences data.
AVG_APP_PREFS = 0.01

Size =
    100 * 0.01 +
    10000 * 5 * 0.1 +
    100 * 10000 * 0.01 +
    100 * 0.01 +
    10000 * 5 * 0.01 +
    10000 * 0.002
    = 15522 MB

Growth =
    10000 * 0.001 * 30 +
    10000 * 0.1 * 5 +
    10000 * 0.01 * 5 +
    100000 * 0.001
    = 5900 MB

To facilitate better planning for clients, the following approximations have been calculated for the recommended database sizes for small (50 users), medium (500 users), and large (5000 users) deployments over a two-year period and assuming high-usage patterns. These estimates include a buffer to accommodate growth from audit logs, sessions, feedback, and crashes.

  • Initial Size: 750 MB
  • Monthly Growth: 300 MB
  • Two-Year Size Estimate: 750 MB + (300 MB * 24) = 8 GB
  • Initial Size: 5 GB
  • Monthly Growth: 2.5 GB
  • Two-Year Size Estimate: 5 GB + (2.5 GB * 24) = 65 GB
  • Initial Size: 50 GB
  • Monthly Growth: 25 GB
  • Two-Year Size Estimate: 50 GB + (25 GB * 24) = 650 GB