How Rapid7 constructed multi-tenant analytics with Amazon Redshift utilizing near-real-time datasets



This can be a visitor publish co-written by Rahul Monga, Principal Software program Engineer at Rapid7.

Rapid7 InsightVM is a vulnerability evaluation and administration product that gives visibility into the dangers current throughout a corporation. It equips you with the reporting, automation, and integrations wanted to prioritize and repair these vulnerabilities in a quick and environment friendly method. InsightVM has greater than 5,000 prospects throughout the globe, runs solely on AWS, and is accessible for buy on AWS Market.

To supply near-real-time insights to InsightVM prospects, Rapid7 has lately undertaken a challenge to boost the dashboards of their multi-tenant software program as a service (SaaS) portal with metrics, tendencies, and aggregated statistics on vulnerability info recognized of their buyer belongings. They selected Amazon Redshift as the info warehouse to energy these dashboards attributable to its potential to ship quick question efficiency on gigabytes to petabytes of information.

On this publish, we talk about the design choices that Rapid7 evaluated to construct a multi-tenant knowledge warehouse and analytics platform for InsightVM. We are going to deep dive into the challenges and options associated to ingesting near-real-time datasets and easy methods to create a scalable reporting answer that may effectively run queries throughout greater than 3 trillion rows. This publish additionally discusses an choice to deal with the situation the place a selected buyer outgrows the typical knowledge entry wants.

This publish makes use of the phrases prospects, tenants, and organizations interchangeably to characterize Rapid7 InsightVM prospects.


To gather knowledge for InsightVM, prospects can use scan engines or Rapid7’s Perception Agent. Scan engines assist you to acquire vulnerability knowledge on each asset linked to a community. This knowledge is simply collected when a scan is run. Alternatively, you’ll be able to set up the Perception Agent on particular person belongings to gather and ship asset change info to InsightVM quite a few instances every day. The agent additionally ensures that asset knowledge is distributed to InsightVM no matter whether or not or not the asset is linked to your community.

Knowledge from scans and brokers is distributed within the type of packed paperwork, in micro-batches of a whole bunch of occasions. Round 500 paperwork per second are obtained throughout prospects, and every doc is round 2 MB in dimension. On a typical day, InsightVM processes 2–3 trillion rows of vulnerability knowledge, which interprets to round 56 GB of compressed knowledge for a big buyer. This knowledge is normalized and processed by InsightVM’s vulnerability administration engine and streamed to the info warehouse system for near-real-time availability of information for analytical insights to prospects.

Structure overview

On this part, we talk about the general architectural setup for the InsightVM system.

Scan engines and brokers acquire and ship asset info to the InsightVM cloud. Asset knowledge is pooled, normalized, and processed to establish vulnerabilities. That is saved in an Amazon ElastiCache for Redis cluster and likewise pushed to Amazon Kinesis Knowledge Firehouse to be used in near-real time by InsightVM’s analytics dashboards. Kinesis Knowledge Firehose delivers uncooked asset knowledge to an Amazon Easy Storage Service (Amazon S3) bucket. The information is reworked utilizing a {custom} developed ingestor service and saved in a brand new S3 bucket. The reworked knowledge is then loaded into the Redshift knowledge warehouse. Amazon Easy Notification Service (Amazon SNS), Amazon Easy Queue Service (Amazon SQS), and AWS Lambda are used to orchestrate this knowledge movement.  As well as, to establish the most recent timestamp of vulnerability knowledge for belongings, an auxiliary desk is maintained and up to date periodically with the replace logic within the Lambda operate, which is triggered via an Amazon CloudWatch occasion rule. Customized-built middleware elements interface between the online person interface (UI) and the Amazon Redshift cluster to fetch asset info for show in dashboards.

The next diagram exhibits the implementation structure of InsightVM, together with the info warehouse system:

Rapid-7 Multi-tenant Architecture

The structure has built-in tenant isolation as a result of knowledge entry is abstracted via the API. The appliance makes use of a dimensional mannequin to help low-latency queries and extensibility for future enhancements.

Amazon Redshift knowledge warehouse design: Choices evaluated and choice

Contemplating Rapid7’s want for near-real-time analytics at any scale, the InsightVM knowledge warehouse system is designed to fulfill the next necessities:

  • Means to view asset vulnerability knowledge at near-real time, inside 5–10 minutes of ingest
  • Lower than 5 seconds’ latency when measured at 95 percentiles (p95) for reporting queries
  • Means to help 15 concurrent queries per second, with the choice to help extra sooner or later
  • Easy and easy-to-manage knowledge warehouse infrastructure
  • Knowledge isolation for every buyer or tenant

Rapid7 evaluated Amazon Redshift RA3 cases to help these necessities. When designing the Amazon Redshift schema to help these targets, they evaluated the next methods:

  • Bridge mannequin – Storage and entry to knowledge for every tenant is managed on the particular person schema degree in the identical database. On this method, a number of schemas are arrange, the place every schema is related to a tenant, with the identical actual construction of the dimensional mannequin.
  • Pool mannequin – Knowledge is saved in a single database schema for all tenants, and a brand new column (tenant_id) is used to scope and management entry to particular person tenant knowledge. Entry to the multi-tenant knowledge is managed utilizing API-level entry to the tables. Tenants aren’t conscious of the underlying implementation of the analytical system and may’t question them instantly.

For extra details about multi-tenant fashions, see Implementing multi-tenant patterns in Amazon Redshift utilizing knowledge sharing.

Initially when evaluating the bridge mannequin, it supplied a bonus for tenant-only knowledge for queries, plus the power to decouple a tenant to an unbiased cluster in the event that they outgrow the sources which can be accessible within the single cluster. Additionally, when the p95 metrics have been evaluated on this setup, the question response instances have been lower than 5 seconds, as a result of every tenant knowledge is remoted into smaller tables. Nonetheless, the most important concern with this method was with the near-real-time knowledge ingestion into over 50,000 tables (5,000 buyer schemas x roughly 10 tables per schema) each 5 minutes. Having hundreds of commits each minute into a web-based analytical processing (OLAP) system like Amazon Redshift can result in most sources being exhausted within the ingestion course of. Because of this, the appliance suffers question latencies as knowledge grows.

The pool mannequin gives a less complicated setup, however the concern was with question latencies when a number of tenants entry the appliance from the identical tables. Rapid7 hoped that these considerations could be addressed by utilizing Amazon Redshift’s help for massively parallel processing (MPP) to allow quick execution of most advanced queries working on giant quantities of information. With the best desk design utilizing the best type and distribution keys, it’s attainable to optimize the setup. Moreover, with automated desk optimization, the Amazon Redshift cluster can robotically make these determinations with none handbook enter.

Rapid7 evaluated each the pool and bridge mannequin designs, and determined to implement the pool mannequin. This mannequin gives simplified knowledge ingestion and may help question latencies of underneath 5 seconds at p95 with the best desk design. The next desk summarizes the outcomes of p95 assessments carried out with the pool mannequin setup.

Question P95
Giant buyer: Question with a number of joins, which record belongings, their vulnerabilities, and all their associated attributes, with aggregated metrics for every asset, and filters to scope belongings by attributes like location, names, and addresses Lower than 4 seconds
Giant buyer: Question to return vulnerability content material info given a listing of vulnerability identifiers Lower than 4 seconds

Tenet isolation and safety

Tenant isolation is prime to the design and improvement of SaaS programs. It permits SaaS suppliers to reassure prospects that, even in a multi-tenant setting, their sources can’t be accessed by different tenants.

With the Amazon Redshift desk design utilizing the pool mannequin, Rapid7 constructed a separate knowledge entry layer within the middleware that templatized queries, augmented with runtime parameter substitution to uniquely filter particular tenant and group knowledge.

The next is a pattern of templatized question:

<#if useDefaultVersion()>
currentAssetInstances AS (
SELECT tablename.*
FROM tablename
<#if (applyTags())>
JOIN dim_asset_tag USING (organization_id, attribute2, attribute3)
WHERE organization_id ${getOrgIdFilter()}
<#if (applyTags())>
AND tag_id IN ($(tagIds))

The next is a Java interface snippet to populate the template:

public interface TemplateParameters {

boolean useDefaultVersion(); boolean useVersion(); default Set<String> getVersions() {
return null;
} default String getVersionJoin(String var1) {
return "";

String getTemplateName();

String getOrgIdString();

default String getOrgIdFilter() {
return "";

Each question makes use of organization_id and extra parameters to uniquely entry tenant knowledge. Throughout runtime, organization_id and different metadata are extracted from the secured JWT token that’s handed to middleware elements after the person is authenticated within the Rapid7 cloud platform.

Greatest practices and classes realized

To completely understand the advantages of the Amazon Redshift structure and design for the a number of tenants & close to real-time ingestion, concerns on the desk design assist you to take full benefit of the massively parallel processing and columnar knowledge storage. On this part, we talk about the perfect practices and classes realized from constructing this answer.

Kind key for efficient knowledge pruning

Sorting a desk on an acceptable type key can speed up question efficiency, particularly queries with range-restricted predicates, by requiring fewer desk blocks to be learn from disk. To have Amazon Redshift select the suitable type order, the AUTO choice was utilized. Automated desk optimization constantly observes how queries work together with tables and discovers the best type key for the desk. To successfully prune the info by the tenant, organization_id is recognized as the type key to carry out the restricted scans. Moreover, as a result of all queries are routed via the info entry layer, organization_id is robotically added within the predicate situations to make sure efficient use of the type keys.

Micro-batches for knowledge ingestion

Amazon Redshift is designed for giant knowledge ingestion, slightly than transaction processing. The price of commits is comparatively excessive, and extreme use of commits can lead to queries ready for entry to the commit queue. Knowledge is micro-batched throughout ingestion because it arrives for a number of organizations. This leads to fewer transactions and commits when ingesting the info.

Load knowledge in bulk

For those who use a number of concurrent COPY instructions to load one desk from a number of information, Amazon Redshift is pressured to carry out a serialized load, and this kind of load is way slower.

The Amazon Redshift manifest file is used to ingest the datasets that span a number of information in a single COPY command, which permits quick ingestion of information in every micro-batch.

RA3 cases for knowledge sharing

Speedy 7 makes use of Amazon Redshift RA3 cases, which allow knowledge sharing to assist you to securely and simply share stay knowledge throughout Amazon Redshift clusters for reads. On this multi-tenant structure when a tenant outgrows the typical knowledge entry wants, it may be remoted to a separate cluster simply and independently scaled utilizing the info sharing. That is achieved by monitoring the STL_SCAN desk to establish totally different tenants and isolate them to permit for unbiased scalability as wanted.

Concurrency scaling for persistently quick question efficiency

When concurrency scaling is enabled, Amazon Redshift robotically provides further cluster capability while you want it to course of a rise in concurrent learn queries. To satisfy the uptick in person requests, the concurrency scaling function is enabled to dynamically deliver up further capability to offer constant p95 values that meet Rapid7’s outlined necessities for the InsightVM utility.

Outcomes and advantages

Rapid7 noticed the next outcomes from this structure:

  • The brand new structure has diminished the time required to make knowledge accessible to prospects to lower than 5 minutes on common. The earlier structure had increased degree of processing time variance, and will typically exceed 45 minutes
  • Dashboards load quicker and have enhanced drill-down performance, enhancing the end-user expertise
  • With all knowledge in a single warehouse, InsightVM has a single supply of reality, in comparison with the earlier answer the place InsightVM had copies of information maintained in several databases and domains, which may sometimes get out of sync
  • The brand new structure lowers InsightVM’s reporting infrastructure value by nearly thrice, as in comparison with the earlier structure


With Amazon Redshift, the Rapid7 crew has been in a position to centralize asset and vulnerability info for InsightVM prospects. The crew has concurrently met its efficiency and administration targets with the usage of a multi-tenant pool mannequin and optimized desk design. As well as, knowledge ingestion through Kinesis Knowledge Firehose and custom-built microservices to load knowledge into Amazon Redshift in near-real time enabled Rapid7 to ship asset vulnerability info to prospects greater than 9 instances quicker than earlier than, enhancing the InsightVM buyer expertise.

Concerning the Authors

Rahul Monga is a Principal Software program Engineer at Rapid7, presently engaged on the subsequent iteration of InsightVM. Rahul’s focus areas are extremely distributed cloud architectures and massive knowledge processing. Initially from the Washington DC space, Rahul now resides in Austin, TX together with his spouse, daughter, and adopted pup.

Sujatha Kuppuraju is a Senior Options Architect at Amazon Net Providers (AWS). She works with ISV prospects to assist design secured, scalable and well-architected options on the AWS Cloud. She is keen about fixing advanced enterprise issues with the ever-growing capabilities of know-how.

Thiyagarajan Arumugam is a Principal Options Architect at Amazon Net Providers and designs buyer architectures to course of knowledge at scale. Previous to AWS, he constructed knowledge warehouse options at In his free time, he enjoys all outside sports activities and practices the Indian classical drum mridangam.