<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Saiful Khan on Medium]]></title>
        <description><![CDATA[Stories by Saiful Khan on Medium]]></description>
        <link>https://medium.com/@saifulbkhan?source=rss-176bacda9736------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*38hNx7vZcirXxsNq.jpg</url>
            <title>Stories by Saiful Khan on Medium</title>
            <link>https://medium.com/@saifulbkhan?source=rss-176bacda9736------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 11:45:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@saifulbkhan/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Scaling Data Pipelines for High-throughput Bioinformatics]]></title>
            <link>https://medium.com/elucidata/scaling-data-pipelines-for-high-throughput-bioinformatics-006a815c8376?source=rss-176bacda9736------2</link>
            <guid isPermaLink="false">https://medium.com/p/006a815c8376</guid>
            <category><![CDATA[scalability]]></category>
            <category><![CDATA[bioinformatics]]></category>
            <category><![CDATA[pipeline]]></category>
            <dc:creator><![CDATA[Saiful Khan]]></dc:creator>
            <pubDate>Tue, 18 Jun 2024 08:54:11 GMT</pubDate>
            <atom:updated>2024-06-18T10:44:27.889Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Mq7ocdH71mAf7m2g2TKjSQ.png" /></figure><p>Data pipelines are an essential tool in bioinformatics. They enable the standardization of data-cleaning and complex data-processing which in turn results in better reproducibility. Most data pipelines use some kind of framework for executing its tasks. Modern pipeline orchestration frameworks do a good job of abstracting and separating pipeline logic from infrastructure complexity. But that complexity remains to be managed at some level.</p><h3>Latent Challenges in Pipeline Management</h3><p>Before we talk about the specifics of scalability, let us first consider a simplified model of the lifecycle of a bioinformatics data pipeline:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*w10i7xEVoMxabfisFS90Ew.png" /><figcaption>Lifecycle of a Bioinformatics Pipeline</figcaption></figure><p>Bioinformaticians are primarily trained and skilled at developing algorithms, performing data analysis, and interpreting the biological implications of data. Their expertise is central to understanding and solving biological problems through computational methods. Therefore, they will be highly involved in the development and interpretation stages of the pipeline. However, it is <strong>not a good use of their time to be responsible for managing deployment and configuring execution environments</strong> as well.</p><p>Deployment and maintenance of pipelines in a cloud environment requires understanding of cloud services, configuration management, containerization technologies (like Docker) and CI/CD principles. These tasks are time-consuming and can divert bioinformaticians from their primary role – research and data analysis. Bioinformatics pipelines have highly dynamic storage and computational needs as well. Efficiently managing computational resources to handle variable workloads, optimizing costs, handling scaling issues, and ensuring data security involves skills generally associated with cloud solutions experts. Getting involved with this will be yet another distraction.</p><h4>A Collaborative Solution</h4><p>This is where <strong>Polly Pipelines</strong>, Elucidata’s managed pipelines hosting and execution service can help. You write the code, we take care of deployment and provide a fully-managed &amp; secure infrastructure to run it. You can monitor your executions and finally download the output and reports for further analysis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-UAs1HtVqg2pxyHXKJkqxQ.png" /><figcaption>Polly Pipelines manages deployment &amp; execution stages completely</figcaption></figure><p>Providing such a service in a multi-tenant (or even a single-tenant) environment is not easy. We need to support highly concurrent workloads w.r.t. both development and execution of pipelines. In this post we will talk about our pipeline deployment and execution architecture to cater to the required throughput and concurrency.</p><h3>Scaling Pipeline Deployment with Polly</h3><p>The deployment of pipelines is often the most boring part of its development phase. It involves compiling or packaging the code, building and pushing execution containers, configuring environment variables and registering or updating the pipeline’s metadata with the orchestration framework. It is a multi-step process that is automated in most production environments.</p><p>In this section we will describe how our unique deployment strategies enable teams to scale both the number of pipelines and the number of contributors without being choked by operational blockers.</p><h4>Isolated Deployment</h4><p>Polly Pipelines gives us the ability to host all our pipelines in a central pipelines repository. This pattern can be replicated for any number of organizations, although we have not done it outside of Elucidata yet. Enterprises often adopt a “<a href="https://en.wikipedia.org/wiki/Monorepo">monorepo</a>” approach to help centralize code governance as well as maintain common utilities &amp; coding standards across pipelines. However, such repositories come with a major challenge – coupled deployment of pipelines.</p><p>At any given point of time, different pipelines in the repository can be in different stages of development. If we deploy the repository’s code because Alice’s Pipeline-1 has finished adding a new feature while Bob’s Pipeline-2 is still testing out a new feature, then we have Pipeline-2 being deployed to production with unverified behavior. This is non-ideal. Trying to manage this situation through people processes, often results in deliveries of one pipeline being blocked by another.</p><p>This is why we have carefully designed our continuous-deployment workflow such that <strong>each pipeline’s code (along with its dependencies) and container image are deployed in isolation from each other</strong>. If a developer makes changes to a pipeline and commits it to a live branch, then they are presented with a prompt for approving the deployment of that pipeline. Therefore, if Alice and Bob make some changes to Pipeline-1 and Pipeline-2, respectively, and commit them to the same branch, then they will be presented with two separate approval holds. Alice will simply approve only Pipeline-1’s deployment, leaving Pipeline-2 unchanged in the live environment.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eALpjofV1EAlKRPcTIAG-w.png" /><figcaption>Isolated deployment ensures that only intended changes are deployed</figcaption></figure><p>If all pipeline developers practice this simple approach – approve only the pipelines that they have made changes to – then it eliminates the need for any kind of manual coordination between members of a team or between members across teams.</p><h4>Multi-stage Deployment</h4><p>While we support and recommend local testing of pipeline code before pushing it to the main repository, some pipelines can simply not be tested offline on the developer’s local machine. This is most often due to a pipeline’s resource-intensive nature. Additionally, many teams also need a way to stage a new version of a pipeline in “testing” mode before moving it to production. This pre-production stage is where the pipeline may be used by internal peers or QA teams to make sure that the pipeline’s behavior satisfies the acceptance criteria set by the stakeholders.</p><p>To help with these requirements, <strong>Polly Pipelines&#39; registry supports three stages for each pipeline</strong>. The repository has three live branches, one for each stage.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Eh2tdDs0m376f7oV3VwlMA.png" /><figcaption>Each group (devs, QA specialists &amp; end-users) gets a separate deployment</figcaption></figure><p>Merging a pipeline’s feature branch to the develop branch deploys the pipeline in “dev” mode. When executed, a pipeline in “dev” stage will be run on the development environment. The development environment is completely isolated from other environments. It supports small-scale execution of pipelines in a true cloud environment. It is ideal for testing the pipeline behavior during development.</p><p>Merging a pipeline&#39;s feature branch to the staging branch deploys the pipeline in “test” mode. When executed, these pipelines will run in a “test” environment, once again isolated from other environments. This environment is useful for providing a pre-production replica of the pipeline for verification by peers and QA team members.</p><p>Finally, once the team is confident that the pipeline is ready for production, they can merge their feature branch to the master branch, which will deploy the pipeline to a main production environment. This environment, while isolated from the dev and test environments, has all the compute capacity that Polly Pipelines has to offer, which is what we will discuss next.</p><h3>Scaling Pipeline Execution with Polly</h3><p>Although bioinformatics pipelines are specialized and adapted to the unique demands of biological data analysis, they can still be considered as a type of <a href="https://en.wikipedia.org/wiki/Extract,_transform,_load">ETL</a> workload:</p><ol><li><strong>Extract</strong> – This often involves retrieving raw biological data from various sources, such as publicly available sequencing data (GEO, etc.), public databases (like GenBank, ENSEMBL, etc.), or private lab-generated datasets.</li><li><strong>Transform</strong> – This is the main analytical part of the pipeline. It includes a series of computational steps and tools that process raw data into meaningful insights. This might include quality control, sequence alignment, variant calling, gene prediction, and other bioinformatics analyses.</li><li><strong>Load</strong> – Finally the results, which could be annotated expression data, models or analytical reports, are often stored in repositories and databases for further use and interpretation. Optionally, this output may itself be input to yet another pipeline for other specific use cases.</li></ol><p>We are constantly innovating at each phase of the ETL. This section describes how we are solving the most challenging issues encountered by practitioners when running their bioinformatics pipelines.</p><h4>Seamless Data Import</h4><p>One of the bigger challenges of the data extraction phase is the migration of data from different sources. Each source will have its own authentication mechanism. Additionally, a pipeline that is run often for different customers may need to use different source of the same type (e.g., different S3 bucket) in each run. Managing the code and authentication logic for so many disparate sources for each pipeline is a minor headache for the developers.</p><p>Fortunately, Polly Pipelines comes with the ability to automatically import data from a variety of sources and make it easily accessible to your pipeline runs. In short, the user flow looks like:</p><ol><li>The user opens the pipeline execution form.</li><li>For parameters that can accept files or folders as inputs, the user opens a file browser.</li><li>If the source of files or folders has not been used previously, the user registers the new source with necessary authentication details. For security purposes, we may invalidate auth tokens at pre-defined intervals, after which they must be updated again by the user.</li><li>Once the registered, the user can browse files or folders from their source of choice. These sources remain available for all future runs.</li><li>The user starts the pipeline run and behind the scenes we bring in the specified data into an S3 bucket path accessible to their pipeline run. Each run can only access data imported for their run.</li><li>After the import is completed, the pipeline’s actual execution begins, where they can simply do an aws s3 cp on the path (passed in the place of the file or folder parameter).</li></ol><p>This approach makes writing pipelines a lot simpler for bioinformaticians. They no longer have to manage dozens of secrets and authentication mechanisms. They can focus on the transformation processes of their pipeline instead.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_JkM1lH23hV2UDmHOc3Y7A.png" /><figcaption>The import feature does all the heavy-lifting of your data extraction process</figcaption></figure><p><strong>For pipelines</strong> <strong>where multiple (potentially large) files are needed, we enable data import on a fleet of serverless workers</strong>. This greatly reduces the amount of time it would take to import large batches of files or folders.</p><p>As of this writing, we are starting with <a href="https://aws.amazon.com/s3/">Amazon S3</a>, <a href="https://polly-docs.readthedocs.io/en/latest/Getting%20Started/Workspaces.html">Polly Workspaces</a> and <a href="https://www.illumina.com/products/by-type/informatics-products/basespace-sequence-hub/order.html">Illumina BaseSpace</a> as our first few sources. However, the Polly Pipelines architecture ensures that we are agile in bringing support for new sources.</p><h4>Uniform Interface Across Executors</h4><p>Most data pipelines are defined in a DSL or framework specifically crafted for that purpose. One such DSL is <a href="https://www.nextflow.io/">Nextflow</a>. It has become increasingly popular in the scientific community due to its flexibility, scalability and ability to streamline complex computational workflows. For this reason, we also chose to go with Nextflow as our primary executor for all bioinformatics workloads.</p><p>However, since then, we have realized that not everyone will want to use Nextflow for their needs. For example, within Elucidata, we have data engineers who need to automate secondary or tertiary transformations &amp; loading of processed data. They prefer a python-native stack and do not need any bioinformatics specific utilities. To cater to their use cases, we created our own pipeline executor called PWL (which stands for “Polly Workflow Language”) written in Python.</p><p>At the same time, we often talk to customers who have already written and verified their data pipelines internally. Their choice of the workflow language could be something else like <a href="https://snakemake.readthedocs.io/en/stable/">Snakemake</a> or <a href="https://github.com/openwdl/wdl">WDL</a> but we still want them to benefit from all the features and scalability that Polly Pipelines has to offer.</p><p>Very soon, it was evident that to support the diversity of skilled professionals we will need a multi-framework platform. Therefore, we have carefully crafted programmatic and graphical interfaces without letting one framework dictate any of our choices. <strong>These interfaces will allow us to bring in new pipeline executors based on the demands of customers</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WpImzo8iMW7EsY4zBWk6fg.png" /><figcaption>Polly Pipelines’ API abstracts all executors behind a uniform interface</figcaption></figure><p>UX patterns are a neglected aspect of scalability. We firmly believe that providing a unified interface for running pipelines in any executor is the best way to serve the diverse and rapidly evolving field of bioinformatics.</p><h4>Horizontally Scaled Execution</h4><p>Modern bioinformatics projects often deal with massive datasets, such as genomic sequences, proteomic data, and large-scale imaging data. Scaling out allows the distribution of these large datasets across multiple computational nodes, facilitating efficient processing and analysis. This scale out can happen on any given axis based on the pipeline logic (most commonly “tasks” or “processes”). For example, transcriptomic datasets often come with multiple samples. The processing of each sample can be pushed a separate compute node, thereby significantly reducing the overall time required to complete the analysis. This is crucial for time-sensitive research.</p><p>Polly Pipelines automatically implements this scale out pattern based on the definition of your workflow. Any tasks that can be run parallelly will be run as such without the pipeline developer having to specify anything other than how compute intensive their tasks are. A child task only moves forward when all its parent tasks have finished executing. So the infrastructure is built in a true <a href="https://en.wikipedia.org/wiki/MapReduce">MapReduce</a> fashion, making the most effective usage of available compute.</p><p>And since we are availing compute from large cloud providers, our reserve is potentially limitless. However, in practice we do put certain restrictions on the number of tasks that we run parallelly. This is purely a cost-control mechanism and we will increase this limits should we observe that workloads are reaching our maximum capacity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*huPZ5FY2JP7cB-QEPdnhYA.png" /><figcaption>The pipeline execution infrastructure scales relative to the demands of running jobs</figcaption></figure><p>To give a sense of the current scale of our infrastructure, we have estimated that <strong>Polly Pipelines can successfully process 5k GB-samples of transcriptomics data per week</strong> through our custom RNA-Seq pipeline, which uses kallisto for (pseudo-)alignment &amp; quantification. In simple terms, this means we can process 5000 samples weekly where each sample is 1 GB in size.</p><p>Apart from raw compute power, we ensure that each executor has its own priority queues and execution clusters. Not only that, each stage of a pipeline gets its own execution cluster as well. Pipeline developers can expect each of these clusters to exhibit similar scalability semantics.</p><h4>Bottomless Storage per Node</h4><p>Bioinformatics pipelines have high storage capacity needs due to the nature of the data they handle and the computational processes involved. Each process may itself generate more data for the next process to be used. Moreover, this I/O happens on “local” storage of the nodes participating in pipeline execution. It is difficult to estimate how much storage a given pipeline run needs. It is difficult still to provision disks dynamically based on each run’s demands. So jobs frequently used to fail with out-of-disk errors. We therefore had to come up with a new design for the storage attached to compute nodes.</p><p>All compute instances in the cloud come with block storage of some kind. Either it will be a disk directly attached to the instance or a volume connected over the local network. These volumes have a fixed capacity. If a process continues writing more data to the volume, it will eventually reach the volume’s capacity. Normally, we would expect the process to fail at this point. However, if we are running our process on Polly Pipelines&#39; infrastructure, the user will never really find out whether they ran out of capacity. Internally, as soon as the instance’s disk space is exhausted we switch to a remote volume (using the <a href="https://en.wikipedia.org/wiki/Network_File_System">NFS</a> protocol). This remote volume can store an extreme amount of data and our <strong>processes effectively never run out of disk space</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hWYgMrG6RAZnsvk1E5WMCg.png" /><figcaption>A tandem arrangement of local &amp; NFS volumes maximizes storage capacity</figcaption></figure><p>The caveat here is that the NFS volume has a maximum throughput limit. If too many nodes are writing data concurrently to this volume, we may see slowdowns in pipeline runtime. To counter this effect, we simply provision more NFS volumes based on the load on our execution cluster. Tasks or processes from the same pipeline run, however, do share the same NFS volumes.</p><h4>S3 for Intermediate Data</h4><p>Data on disk is not the only produce from pipelines. Any non-trivial pipeline will have multiple tasks that execute in parallel as well as in succession. Each successive task needs the output from its parent task. So we need a data-exchange layer for storing this intermediate data. Finally, once a run completes, the output is generated which also needs to be saved durably.</p><p>For both of these needs we use Amazon S3. Intermediate data can be written by hundreds of tasks simultaneously, totalling terabytes in size. Most traditional storage devices will not able to handle this kind of write throughput. The same goes for reads as well. Amazon S3, being a distributed file-system, has no problem under this kind of load. It is <strong>built precisely to handle large number of concurrent read and writes</strong>. Moreover, it provides <a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/DataDurability.html">eleven 9s</a> of durability guarantee.</p><p>Similarly, we also store the pipeline’s final output in S3. But based on the instructions of the pipeline, the developer is free to upload that data wherever they want and in whatever format they want.</p><p>So far we have never faced an issue while using Amazon S3 for any of our pipeline runs, in terms of concurrency as well as durability.</p><h3>Network and Security in Polly Pipelines</h3><p>One final aspect of Polly Pipelines worth highlighting is security through network isolation. Bioinformatics often involves dealing with sensitive data. It is imperative to make sure that pipelines running on the cloud, especially in a multi-tenant systems, are operating with strict access controls in place. Therefore, we make sure that:</p><ul><li>Pipelines runs remain unaware of other pipeline runs. They can never access input, intermediate data or output from other pipelines. Even logs generated by the pipelines are kept in dedicated log streams.</li><li>Once your data is imported into the execution system, it never leaves our private network. Of course, the pipeline developers can still choose to push it to other remote data stores should they wish to do so.</li><li>For production environments, all Elucidata personnel (except system administrators) are restricted from viewing or downloading any data processed or generated by the pipeline execution.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c-YaeLHb9pgHp70hfiyzwA.png" /><figcaption>Polly Pipelines enforces strict access control and data isolation for each run</figcaption></figure><p>We also understand that there are organizations that deal with <a href="https://en.wikipedia.org/wiki/Personal_data">PII</a> data and have to follow stricter security policies. We recommend them to go with a single-tenant (enterprise) deployment of Polly Pipelines. This way, their data and their compute is completely owned and governed by the organization.</p><h3>Future Work</h3><p>There is still much work that we want to do to make sure that Polly is the best place to develop and run bioinformatics pipelines. A few ideas that we are currently entertaining are:</p><ul><li><strong>Enable a bring-your-own-pipeline journey</strong> – As mentioned previously, as of this writing, all pipelines we run are managed in a central repository by us. But often bioinformaticians would want to control their own code and <a href="https://en.wikipedia.org/wiki/Systems_development_life_cycle">SDLC</a> for pipelines. Letting them upload their pipeline code &amp; register it through our programmatic interfaces (library or CLI) will help us achieve that outcome.</li><li><strong>Support more executors</strong> – We plan to support more pipeline definition and orchestration frameworks, based on popular demand. The one we are currently ideating is Snakemake. Apart from Nextflow, it is probably the most widely used workflow management system in bioinformatics.</li><li><strong>Improve local storage throughput</strong> – Because of the shared nature of our NFS volumes, they are prone to becoming points of slowdown for our pipelines. Worker nodes will divide the overall throughput amongst themselves and depending on the number of nodes, their read/write speed can suffer. To counter this, we want to further introduce a storage layer for our nodes such that the I/O throughput remains independent on the number of concurrent workers. We have some ideas, but we will need a whole another post to talk details.</li></ul><p>In this post we have shown how Polly Pipelines is a versatile, scalable, and developer-friendly platform for writing bioinformatics pipelines. Its rich feature set and attention to every stage of a pipeline’s lifecycle makes it a good choice for bioinformaticians.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=006a815c8376" width="1" height="1" alt=""><hr><p><a href="https://medium.com/elucidata/scaling-data-pipelines-for-high-throughput-bioinformatics-006a815c8376">Scaling Data Pipelines for High-throughput Bioinformatics</a> was originally published in <a href="https://medium.com/elucidata">Elucidata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Benchmarking data stores for event-based monitoring]]></title>
            <link>https://medium.com/elucidata/benchmarking-data-stores-for-event-based-monitoring-f77fd9a01eac?source=rss-176bacda9736------2</link>
            <guid isPermaLink="false">https://medium.com/p/f77fd9a01eac</guid>
            <category><![CDATA[timestream]]></category>
            <category><![CDATA[benchmark]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[cloudwatch]]></category>
            <category><![CDATA[technology]]></category>
            <dc:creator><![CDATA[Saiful Khan]]></dc:creator>
            <pubDate>Mon, 19 Sep 2022 11:13:15 GMT</pubDate>
            <atom:updated>2022-09-19T11:13:15.482Z</atom:updated>
            <content:encoded><![CDATA[<p>Monitoring a process using the events it generates is a very common application in computing. Based on the complexity of the process and the target observer, this can be implemented in different ways.</p><p>The most basic form of event-based monitoring, that most developers will be familiar with, is logging. In such cases, the “process” is usually a computer program either running locally or in the cloud. The “events” are messages written to a log. Looking at the messages appear in this log over time, the developer is able to tell what is going on with their program.</p><p>An ETL job is another kind of process that data teams might want to monitor. Our data ingestion pipeline at <a href="https://www.elucidata.io/">Elucidata</a> has hundreds of such ETL jobs running at any given point of time. Jobs can be of different types, of varying scope with different responsibilities and even run on distributed compute units. One of our near-term goals is to let our users add and manage their data on <a href="https://www.elucidata.io/platform/features">Polly</a> by themselves. For this to be a successful feature, the users also have to be able to see the progress of their data jobs and its logs as it goes through various stages of processing. This calls for a sort of job monitoring service.</p><p>We built this job monitoring service using <a href="https://aws.amazon.com/timestream/">Amazon Timestream</a> as our event store. It looked like a promising product and we were excited to try it out for something useful. This post talks a little bit about our experience with Timestream and our benchmarks against another strong contender in this space — <a href="http://elastic.co/">Elasticsearch</a>. We included <a href="https://aws.amazon.com/cloudwatch/">Cloudwatch</a> (Logs &amp; Insights) in our benchmarks as well, because our internal monitoring dashboard is running on this. <a href="https://aws.amazon.com/rds/postgresql/">Postgres on RDS</a> was also included as a reference OLTP database.</p><h3>Motivation</h3><p>Let us quickly go over a simplified version of our requirements. For that, lets define the two main concepts in the system:</p><ul><li><strong>Jobs</strong> — A job transforms some piece of source data to generate some desired output data. Depending on the type of work being done, a job goes through a certain set of <strong>stages</strong>.</li><li><strong>Batch</strong> — A collection of jobs, grouped based on some common relationship between them. The relationship itself is not important other than the fact that this gives the user an easy way to collectively treat a large number of jobs as a single unit. Batches have a <strong>status</strong> that is determined by the stages of its underlying jobs.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GXEEqGlKSN1l7oaIyp1lRg.png" /></figure><p>Using the events generated from each job, we want to be able to do:</p><ul><li>Filtering — on static attributes of a batch, which is present in each event of each job in that batch</li><li>Aggregation — deduce the status of a batch, from the collective stages of its jobs</li><li>Filtering on aggregations — on dynamic attributes like status of a batch</li><li>Sorting — batches ordered based on creation time</li><li>Some time series — fetch batches created in a certain date range</li></ul><p>Currently, our internal data teams are monitoring the progress of their ETL jobs on a <a href="https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/CloudWatch_Dashboards.html">Cloudwatch Dashboard</a>. Our jobs are already generating logs and from those logs, we are able to get the relevant stats. But the latency observed on every refresh of the dashboard is not great at all. We want to be able to show jobs as old as 3 months. Fetching stats from 3 months of logs is very inefficient.</p><p>We were looking for a data store on top of which we can build a custom job monitoring dashboard as a part of Polly itself. That’s when we discovered Timestream. It is primarily built for timeseries data and supports regular and irregular timeseries. Ours is irregular timeseries (events) data. Apart from being serverless, it supports SQL based querying on its schemaless tables. This was a very attractive proposition and we ended up choosing TImestream for our job monitoring service.</p><h3>The problem</h3><p>We had done some basic benchmarking of Timestream by running it on a large number of dummy events and calculating the average response time. Most of the queries gave us sub-second averages. So it was a surprise to us that our GET APIs (running Timestream queries internally) were routinely taking anywhere from 2 to 5 seconds to respond. If you made subsequent API calls, the latency used to improve, but the 5 second max was still hurting. Our dashboard is supposed to have an auto-refresh rate of 1 minute, and it was those refresh requests that took the longest time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ns0raBI19ZmygkfTWzKKWg.png" /><figcaption>Timestream’s contribution to our latency</figcaption></figure><p>It seems like Timestream has a bit of a “cold-start” problem which is not uncommon in serverless systems. The gap between two cold-starts seems to be at least one minute. In the worst case, if we have just one user with their dashboard open and refreshing once a minute, they will notice this long wait every single time. At this rate, our custom dashboard, even though it looked prettier, would be no more performant than the existing Cloudwatch dashboard.</p><p>The reason we missed this behavior in our preliminary benchmarks earlier was because we were looking at average times over a large number of requests. Knowing this, we now wanted to perform a more thorough benchmark. This time we will also throw in a few other data stores to see how Timestream fares against them.</p><h3>Benchmarking setup</h3><p>Our requirements are fairly common and prevalent in most analytical workloads. We have plenty of options today for storing and querying on event data. But we are a small team and we want to stick to services which we can get started with quickly and are easy to maintain. Also, at this point our entire data infrastructure is hosted on AWS, so it makes sense to start with AWS services too. Therefore, the benchmark was performed on the following data-stores:</p><ul><li><strong>Timestream</strong> — We were already using it.</li><li><strong>Elasticsearch (on AWS)</strong> — Not just a search engine. Excellent support for all our requirements.</li><li><strong>Cloudwatch (Logs + Insights)</strong> — Our existing internal dashboard is running on Cloudwatch Logs being queried through Cloudwatch Insights. It made sense to put this in the comparison.</li><li><strong>Postgres (RDS)</strong> — Just added as a reference transactional database. Without any plugins, we are not expecting vanilla Postgres to perform well in this context.</li></ul><p>Please note that of the four above only Timestream and Elasticsearch are true contenders for our use case.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YV-L-Ys7yVFmBKu_Ajli8Q.png" /></figure><p>Events were generated by a “writer” and written to each of data stores. Events were then periodically consumed by a “reader”. They will write their measurements of write and read performances to a DynamoDB table.</p><h3>Benchmark design</h3><p>A series of artificial batches of jobs were generated and their events were written to each data store. But this was done in a systematic manner:</p><ul><li>8 sets of 20 batches each were generated. This was done so as to increase the scale of the data after each iteration.</li><li>Each batch had about 7000 jobs on average. Each job generates 7 events at most. Ultimately, we had <strong>about 4 million records in total, in each data store</strong>.</li><li>The jobs spanned over two days. The 566k jobs is a fair representation of the kind of workload we expect to see on our production infra.</li><li>Write performances were recorded for each batch of records written. Most databases perform better with batch writes. We chose batches of 100 messages each for Cloudwatch Logs. For Elasticsearch and Postgres we had batches of 500 records per write. For Timestream, we had 10 concurrent threads writing 100 records each and the cumulative time taken by the threads was taken as one write measurement.</li><li>After each iteration we ran the query benchmarks, giving us read performance at each scale (from 1x to 8x). Each query was run 10 times to get a distribution of read times.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZM7qBLcl3CyGYErksi_poA.png" /></figure><p>Each event contained a few static values and a few (dynamic) stage-based values. Normalization of data is clearly not our goal here.</p><h3>Performance results</h3><p>For all the quantitative benchmarks, we will plotting the performance data as <a href="https://en.wikipedia.org/wiki/Box_plot">box-plots</a>. This not only shows us the “typical” write or read times but also shows the extremes. Knowing these extremes is important. They can tell us whether we need to take extra measures to maintain a good write performance or low response latencies. Sometimes the worst-case performances may simply make it unviable to use a service for your requirements.</p><h3>Write performance</h3><p>Comparing write performance is not the primary purpose of this purpose. However, it would still be interesting to see how the different data stores fare against one another. The following plot shows the distributions of write times per 100 records.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iy4RJCINO6uigZgqG3ZPJA.png" /><figcaption>Write performance of each data store</figcaption></figure><p>Key observations:</p><ul><li>The average write performance for Cloudwatch logs is pretty good, but at its slowest, it is many times worse. It is these kinds of observations that are lost in simple bar plots.</li><li>Elasticsearch has the healthiest write performance numbers, with 100 ms at its worst.</li><li>Timestream write performances show a large variation. It should be noted that with more concurrent writers, Timestream might be able to achieve much better throughput.</li></ul><h3>Query performance, Type I</h3><p>We finally come to the query benchmarks. For each type of query, we will show an example SQL query to formally elucidate our intention.</p><p>The first query type is not at all useful for our monitoring dashboard, but its something simple that we can start with — <strong>get the total number of events per batch</strong>:</p><pre>  <strong>SELECT</strong> batch_id, COUNT(*) <strong>AS</strong> num_events<br>    <strong>FROM</strong> monitoring_events<br><strong>GROUP BY</strong> batch_id</pre><p><em>Note: </em>For each of the query benchmark types, we will show two plots. The first plot shows the distribution of query performances at 8x scale (highest number of records). <strong>The orange dot in the first plot is the read time when the first query request was made</strong>. The second plot shows how average query performance changes with scale (from 1x to 8x). In both plots, lower values are better.</p><p>Performance profile for Type I queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K8p2lzbtfwdBGtfuI9V93Q.png" /><figcaption>Query performance at 8x scale, Type I</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j2jrylwfJHXW1S9i1UGTAQ.png" /><figcaption>Average query performance with increasing scale, Type I</figcaption></figure><p>Key observations:</p><ul><li>Elasticsearch returns every single time in less than one-third of a second.</li><li>Timestream shows a large variance in query performances. The first call (cold-start?) is noticeably worse.</li></ul><h3>Query performance, Type II</h3><p>In the second query, we will be doing multiple aggregations by batch IDs. This is more useful query and one that we may run in certain situations — <strong>get the latest stats of each batch</strong>:</p><pre>  <strong>SELECT</strong> batch_id,<br>         COUNT(DISTINCT(job_id)) <strong>AS</strong> num_jobs,<br>         SUM(CAST(finished <strong>AS</strong> integer)) <strong>AS</strong> successful_jobs,<br>         SUM(CAST(errored <strong>AS</strong> integer)) <strong>AS</strong> errored_jobs,<br>         MIN(created_at) <strong>AS</strong> creation_time,<br>         MAX(time) <strong>AS</strong> last_updation_time<br>    <strong>FROM</strong> monitoring_events<br><strong>GROUP BY</strong> batch_id<br>   <strong>LIMIT</strong> 100</pre><p>Performance profile for Type II queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*esSOqWPQnrdQVTOElULigw.png" /><figcaption>Query performance at 8x scale, Type II</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zoWOmIW6oVeqFwk81Yd_RA.png" /><figcaption>Average query performance with increasing scale, Type II</figcaption></figure><p>Key observations:</p><ul><li>Elasticsearch still shows the best performance, with 75% queries returning under half a second.</li><li>Timestream gives more predictable performance this time. The worse case performance is still not good though.</li><li>Postgres has to likely due do full table scans this time around.</li></ul><h3>Query performance, Type III</h3><p>In this query, we will be doing multiple aggregations but also applying filters on static as well as aggregated attributes. Finally we will be applying a sorting based on an aggregated attribute. This sort of query will routinely run on our dashboard — <strong>get batches that have completed successfully, sorted by creation time</strong>:</p><pre>  <strong>SELECT</strong> batch_id,<br>         COUNT(DISTINCT(job_id)) <strong>AS</strong> num_jobs,<br>         SUM(CAST(finished <strong>AS</strong> integer)) <strong>AS</strong> successful_jobs,<br>         SUM(CAST(errored <strong>AS</strong> integer)) <strong>AS</strong> errored_jobs,<br>         MIN(created_at) <strong>AS</strong> creation_time,<br>         MAX(time) <strong>AS</strong> last_updation_time<br>    <strong>FROM</strong> monitoring_events<br>   <strong>WHERE</strong> user_id = &#39;1110&#39;<br><strong>GROUP BY</strong> batch_id<br>   <strong>WHERE</strong> successful_jobs = num_jobs<br><strong>ORDER BY</strong> creation_time</pre><p>Performance profile for Type III queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tJpvjYXfxd5Fi8_obuwrfA.png" /><figcaption>Query performance at 8x scale, Type III</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7FhzwVvLtWA9xJX_q4r0bw.png" /><figcaption>Average query performance with increasing scale, Type III</figcaption></figure><p>Key observations:</p><ul><li>Every single Elasticsearch query returns under half-a-second.</li><li>Postgres and Timestream are doing much better this time. This is likely due to the filters being applied, which probably narrows down the search space.</li></ul><h3>Query performance, Type IV</h3><p>This fourth query is unique in the way that it finds out values unique based on the maximum value of another dimension. This is another query we will run often, when viewing jobs within a batch — <strong>get the latest stage and progress for each job</strong>:</p><pre>  <strong>SELECT</strong> job_id,<br>         MAX_BY(stage, time) <strong>AS</strong> stage,<br>         MAX_BY(stage_progress, time) <strong>AS</strong> stage_progress,<br>         MAX_BY(errored, time) <strong>AS</strong> errored,<br>         MAX(time) <strong>AS</strong> last_updation_time<br>    <strong>FROM</strong> monitoring_events<br>   <strong>WHERE</strong> batch_id = &#39;16554252419__1661749456__1110&#39;<br><strong>GROUP BY</strong> job_id</pre><p>Performance profile for Type IV queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fP-6YUVZCTJ5N9HBnUoFuQ.png" /><figcaption>Query performance at 8x scale, Type IV</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sGgDU7fYm0xAeYjH39oPmw.png" /><figcaption>Average query performance with increasing scale, Type IV</figcaption></figure><p>Key observations:</p><ul><li>All queries, except those run on Cloudwatch logs, are performing extremely well. The search space here has been reduced drastically — to a single batch.</li><li>Cloudwatch insights queries taking 3 seconds at this point a consistent trend.</li></ul><h3>Query performance, Type V</h3><p>This final query is again not needed for our dashboard, but we want to test how each of the data stores perform for a common timeseries use case — <strong>get number of jobs finished per repo, each minute, for a small set of users</strong>:</p><pre>  <strong>SELECT</strong> repo_id,<br>         BIN(time, 1m) <strong>AS</strong> interval,<br>         COUNT(DISTINCT(job_id)) <strong>AS</strong> num_jobs<br>    <strong>FROM</strong> monitoring_events<br>   <strong>WHERE</strong> stage = &#39;Finished&#39;<br>     <strong>AND</strong> user_id <strong>IN</strong> (&#39;0011&#39;, &#39;0111&#39;, &#39;1111&#39;, &#39;1110&#39;, &#39;1100&#39;)<br><strong>GROUP BY</strong> repo_id, interval<br><strong>ORDER BY</strong> interval <strong>DESC</strong></pre><p>Performance profile for Type V queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KjGRKLWm954ujAqy06agyg.png" /><figcaption>Query performance at 8x scale, Type V</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yCFBVKKy4pySM_EUrEt3HA.png" /><figcaption>Average query performance with increasing scale, Type V</figcaption></figure><p>Key observations:</p><ul><li>Elasticsearch is once again excellent. Far superior to Timestream, which is supposed to be a timeseries database.</li><li>Postgres starts off being better than Timestream but with scale it eventually gets left behind.</li><li>Timestream’s performance, although not as good as expected, is quite stable even with increasing amounts of data.</li></ul><h3>Qualitative comparison</h3><p>Good performance is definitely important but it is not everything. Depending on one’s use-cases and the amount of time one can afford, some of these data-stores will be more suitable than others. Let us compare our data stores based on some commonly desired qualities:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JZYbQQDHF9Ot62neHUvA0g.png" /><figcaption>Feature matrix comparing the data stores</figcaption></figure><p>Based on the amount of green and yellow, Timestream seems to be the most featureful option among the lot.</p><h3>Cost comparison</h3><p>No benchmark is complete without a cost comparison. The pricing of a service should not be unjustifiable for the performance it provides. Predicting costs over some constant period of time would be difficult and prone to inaccuracy. So, here we will just attach the cost incurred by each instance of the service for the duration of the benchmark, which took place over a span 4 days:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z7I-fSjFGZ_Xsv6BgCr4oQ.png" /><figcaption>Service costs incurred throughout the benchmark</figcaption></figure><p>Cloudwatch wins here, since it is built to store vast number of messages and per query cost is very low. Elasticsearch costs the most but it should be noted that those costs will remain constant per day for up-to a certain threshold of data volume (which is pretty high). Timestream also seems to be very economical service for our purposes.</p><h3>Caution!</h3><p>Before we conclude this post, we would like to mention some possible biases that may make this benchmark “not true” or completely inapplicable to your work:</p><ul><li><strong>Scale of data</strong> — The amount of data we performed this benchmark with, may not have been large enough to notice some emergent scalability patterns.</li><li><strong>Variety of queries</strong> — We started off with explaining why we were doing this exercise. The types of queries we ran were specific to our data model and use cases. They were not exhaustive and your primary use case may not have been covered at all.</li><li><strong>Minimal optimizations</strong> — While we did pay attention to model the data so as to support our queries well, some of the data stores (especially Postgres and Elasticsearch) may be further optimized to beat the competition on certain metrics. But the idea here is that, the data store should be capable of achieving “good enough” performance without a whole lot of tinkering and optimizations on the developers part. We did spend some time on trying to come up with more performant queries for each use case.</li></ul><h3>Conclusion</h3><p>There is definitely a winner here for <em>us</em>. However, your requirements might be different enough that one of the others may be more attractive to you. We will end this post with some notes on the strengths and weaknesses of each:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eULd_cvYOtZw6FtzZ8ziWw.png" /></figure><p>We really like what Elasticsearch has to offer in terms of predictable performance and costs. It also checks most of the boxes in terms of features. Therefore, we are planning to replace Timestream with Elasticsearch in our job monitoring service, sometime in the near future.</p><p>With that, we come to an end of our benchmarks. If you have other data stores in mind that you want to compare against this plan, you can do so by adding to the source code. The infra configuration, source and analysis scripts are all available in this <a href="https://github.com/saiful-elucidata/event-based-monitoring-benchmarks">GitHub repository</a>. In case you want to suggest changes or face issues with reproducibility, please raise an issue there itself.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f77fd9a01eac" width="1" height="1" alt=""><hr><p><a href="https://medium.com/elucidata/benchmarking-data-stores-for-event-based-monitoring-f77fd9a01eac">Benchmarking data stores for event-based monitoring</a> was originally published in <a href="https://medium.com/elucidata">Elucidata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to build a scalable backend to query and search billions of records?]]></title>
            <link>https://medium.com/elucidata/how-to-build-a-scalable-backend-to-query-and-search-billions-of-records-16a532433fda?source=rss-176bacda9736------2</link>
            <guid isPermaLink="false">https://medium.com/p/16a532433fda</guid>
            <category><![CDATA[cloud]]></category>
            <category><![CDATA[elasticsearch]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[architecture]]></category>
            <dc:creator><![CDATA[Saiful Khan]]></dc:creator>
            <pubDate>Thu, 05 May 2022 15:26:29 GMT</pubDate>
            <atom:updated>2022-05-11T03:12:37.441Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ET4_XRRD-AIHMj5aqzCr_A.jpeg" /></figure><p>Every day, Elucidata’s Polly ingests gigabytes(GB) of biomolecular data and transforms it into analysis-ready format for the biologists around the world. This rate of data ingestion presents us with problems of scale and storage. We have recently written about how Elucidata handles biomolecular data at scale with <a href="https://elucidata.io/how-to-build-highly-effective-etl-pipelines/">highly effective ETL pipelines</a>. In this article, we shall look at how engineers at Elucidata handled the challenges of storage and figured out a way to effectively store and make data consumable.</p><p>Historically, Polly’s data platform has relied solely on <a href="https://aws.amazon.com/opensearch-service/the-elk-stack/what-is-elasticsearch/">Elasticsearch</a> to store consumable data. And, It has worked well. Response times for most queries were swift. We were even storing large numerical matrices, serialized as documents, in Elasticsearch. The AWS Elasticsearch service (now OpenSearch service) allows users to use the <a href="https://opendistro.github.io/for-elasticsearch-docs/docs/sql/">SQL plugin</a> to make SQL queries on our indices.</p><p>Moreover, Elasticsearch — -just like other NoSQL data stores — -does not require a predefined schema for data indexing. But, there is a flip side to this feature. Elucidata deals with a lot of semi-structured data coming from heterogeneous sources. Still, we ended up choosing something that mandated the definition of schemas before starting ingestion. We slowly realized that Elasticsearch has several limitations that makes it unsuitable to serve as our query engine. But, before we go further, we need to explain what data means in this context.</p><h3>What do we mean when we say “data“</h3><p>A majority of data on Polly exists in the form of <strong>datasets</strong>. A typical dataset comprises the following:</p><ul><li>A single dataset-level metadata</li><li>A set of <em>n</em> <strong>samples</strong>, each having its metadata</li><li>A set of <em>m</em> <strong>features</strong> measured per sample, each feature having its metadata</li><li>An (<em>n</em> ⨯ <em>m</em>) <strong>data-matrix</strong>, storing the actual measurements</li></ul><p>Each entity’s <strong>metadata is a dictionary of key-value pairs</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0XI770lgbULirT3eA0HCKQ.png" /><figcaption>Layout and contents of a typical dataset</figcaption></figure><p>For most Polly datasets, the <strong>data matrix constitutes &gt;95% of the </strong>file contents and information volume. One final concept to remember is “<strong>repositories</strong>,” which are collections of datasets. Datasets in a repository share some common attributes due to exact origin (source), same data type, or even based on the presence of a particular tissue.</p><h3>Our requirements</h3><p>Ultimately, our users need to consume the data present in these datasets. And we have three primary use cases here:</p><ul><li><strong>Search on metadata</strong> for finding datasets and samples of interest.</li><li><strong>Query metadata</strong> of datasets, samples, and features using SQL.</li><li><strong>Query data-matrix</strong> of datasets and features of interest using SQL.</li></ul><p>We need an API that accepts full-text search (FTS) queries and an API that accepts SQL queries. The data size on which these queries will run is already in terabytes, increasing every day.</p><p>Before you make any guesses on how we addressed all of these requirements with just Elasticsearch, we should first take a look at the limitations of Elasticsearch.</p><h3>Elasticsearch scaling issue</h3><p>For the past few months, Polly has been ingesting hundreds of datasets every day. Some of these datasets have hundreds of thousands of features. The combined size of Elasticsearch indexes quadrupled(from 1 to 4 TB) very quickly. At Elucidata, we anticipated that this trend would continue.</p><p>Usually, cloud storage is not very expensive. But with AWS Elasticsearch service (now OpenSearch service), for a given class of nodes, there’s a limit on the size of EBS volume one can attach to each node. When a size limit is reached, users either have to increase the number of nodes or upgrade the nodes to a more powerful and costlier instance class.</p><p>Typically, the Elasticsearch cluster would scale with the surge in data to seamlessly handle the increasing load during data search. But in Elucidata’s case, data volumes were increasing at an alarming rate. Very often, a majority of the data matrices ended up not being queried. A user would have to compromise by narrowing down on a small number of datasets of interest and then fetch the measurement data present in their matrices. We concluded that querying on data matrices was sub-optimal.</p><p>This meant that while we were horizontally or vertically scaling our Elasticsearch cluster, because of storage demands, we were not actually using the compute that was coming with it. <strong>We were paying for both storage and compute, when we needed only one</strong>.</p><h3>Limitations in querying</h3><p>Elasticsearch cluster has a reputation for being an excellent search engine with the added advantage of vast community support. There are various plugins available that augment the abilities of an Elasticsearch cluster that enable index querying using SQL as if they were a traditional database table. The engineers at Elucidata leveraged the plugins available via Elasticsearch to query using SQL. Elasticsearch was being used for both the FTS and SQL APIs until the engineers realized that the Elasticsearch is an FTS engine and is not the best choice for SQL activities. Few of the challenges that we have faced while performing SQL activities:</p><ul><li>Getting more than 10,000 rows for any non-trivial query.</li><li>Limited scope for joins and aggregations on joins.</li><li>Querying nested data like in the data-matrix — one document per row where each sample’s name and measurement was in an array of nested documents.</li></ul><p>These limitations were very problematic for a platform whose purpose is to add value and expose large amounts of analysis-ready biomedical data.</p><h3>Absence of schemas</h3><p>Elasticsearch allows users to dump data and make it easily queryable. Except with a limit on how many fields you can have per index. And for a good reason, too — otherwise, there will be no upper bound on how much memory a query needs, which is not a good thing for a reliable distributed system.</p><p>Polly’s repositories can have datasets coming in from heterogeneous sources, which makes different metadata fields easy to access. However, the union of these fields can easily exceed Elasticsearch’s field limit. And, it is not recommended to index everything. This issue can be managed through proper processes.</p><p>However, writing structured queries (SQL) on Polly’s data turned out to be the real challenge without schemas. The system that was put into use initially didn’t need any schemas. And, writing SQL queries with no documented schema is challenging. <strong>Users had to tediously figure out columns and their types f</strong>rom thousands of available fields.</p><h3>Our solution</h3><p>While our search APIs were working very well, <strong>it soon became apparent that we needed a separate backend to store and serve SQL on structured data</strong>. Polly needed something that:</p><ul><li>has native SQL support,</li><li>scales well with very large amounts of data per table,</li><li>scales with a large number of tables,</li><li>enforces a schema for each table.</li></ul><h4>A distributed file-system</h4><p>First, the engineers at Elucidata figured that using a data-lake format and serializing our tables on a distributed file system would help with scalability since storage is cheap. <strong>S3 is a great choice here</strong>, given that the rest of the cloud platform already runs on AWS.</p><h4>The data format</h4><p>Second, there is a need to select a data format that can support on-disk SQL querying and enforces schema as well. Engineers evaluated a few choices:</p><ul><li><a href="https://docs.tiledb.com/main/">TileDB embedded</a> — Looks great but is yet to be adopted widely. There’s limited community support information.</li><li><a href="https://iceberg.apache.org/">Apache Iceberg</a> — An Apache project (originally developed at Netflix). Decent community support and adoption. But is not a good choice for workloads that involve frequent updates and deletions to your tables, which is the case for us.</li><li><a href="https://hudi.apache.org/">Apache Hudi</a> — Another Apache project. Decent community support and adoption. Hudi was almost a right fit.</li><li><a href="https://docs.delta.io/latest/delta-intro.html">Delta Lake</a> — Developed and maintained by <a href="https://databricks.com/">Databricks</a>, the open-source version checked all the boxes. Delta lake is widely adopted and also provides an option to move to the more robust enterprise version if the need arises.</li></ul><p>We concluded that <strong>using Delta Lake is the right fit for all our data storage issues.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PpIcjZMJ2x6ziKqB0Ca-GA.png" /><figcaption>Delta is based on Parquet and augments it</figcaption></figure><h4>Separate backends for search and query</h4><p>That said, complete migration to Delta Lake (running on S3) and ditching Elasticsearch wasn’t the plan either. We still wanted to keep our already excellent search interface based on Elasticsearch. It works exceptionally well. We decided to <strong>separate search and query backends, even at the cost of data duplication between these backends to</strong> optimize the backends separately so as to serve their respective roles in the consumption journey.</p><p>Moreover, only SQL queries needed to be run on data matrices, and there was no need for a <em>search</em>. This allowed the engineers to keep the data matrices in Delta Lake as individual tables and not index them in Elasticsearch. This greatly alleviated Polly’s Elasticsearch cluster’s storage requirements.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aFJtsrOwTiCJ6X0VnfzrQg.png" /><figcaption>Separation and duplication of data in Elasticsearch and Delta Lake</figcaption></figure><h4>Running SQL on Delta Lake</h4><p>Consuming data through SQL requires a query engine supported by Delta Lake on S3. Here are a few options to choose from:</p><ul><li><a href="https://spark.apache.org/">Spark</a></li><li><a href="https://prestodb.io/">Presto</a></li><li><a href="https://aws.amazon.com/athena">Athena</a></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hWca9uPd1a_5KD1xGX5QsQ.png" /></figure><p>We <strong>chose to go with Athena because it requires the least effort to get started</strong>. Athena is based on Presto (v0.217), and they both <strong>require the definition of all external queryable tables to be present in a Hive-compatible metastore, which</strong> is how the query engine will know what fields (and their types) are available for querying and what files on S3 should be read to get the actual data. For the complete documentation for the table registration process, check <a href="https://docs.delta.io/latest/presto-integration.html">here</a>. Furthermore, in the future, if the current service reaches its threshold for simultaneous query executions, we will move to a self-managed infrastructure running Presto or Spark.</p><p>Once again, <strong>instead of setting up and maintaining the Hive instance, </strong><a href="https://docs.aws.amazon.com/glue/latest/dg/populate-data-catalog.html">AWS Glue Data Catalog</a> was used. The Glue Data Catalog service is supported by Delta Lake and Athena and has a pay-per-table object model, saving us the effort and upfront cost of maintaining our Hive metastore. In case, the Glue Data Catalog is getting either too restrictive or expensive in the future, there is always an option to move to a self-managed solution.</p><h3>Final Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jRMSDF8zm1RGgW4HEwp05A.png" /><figcaption>The final architecture of our storage and consumption infrastructure</figcaption></figure><p>As shown in the figure above, two different backends serve Polly’s search API and SQL query API. They both go through a Lambda whose job is to perform some common tasks such as:</p><ul><li>Repository-level access control — each user has access to a certain set of read-only datasets.</li><li>Capturing usage metrics for getting insights on the usage of data repositories.</li></ul><p>Polly’s new storage infrastructure enables entity metadata to be present in both Elasticsearch and Delta Lake. From our initial estimates, the persistent increase in storage cost amounts to just ~<strong>$3 per terabyte</strong> of data ingested. Though a bit expensive, the risks associated with scaling Elasticsearch vertically or horizontally frequently have been eliminated by accommodating the disproportionately sized data matrices.</p><p>The new consumption infrastructure of Polly’s new storage backend allows users to make powerful queries on biomolecular data. It also forced the engineers and scientists internally to set up processes for schema definition for each source and type of data, which led to better documentation and management of data complexity and significantly enhanced a user’s querying experience.</p><p>What do you think about our scalable backend storage solution? Do you think you have a better solution? Then come work with us at Elucidata. We are hiring!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=16a532433fda" width="1" height="1" alt=""><hr><p><a href="https://medium.com/elucidata/how-to-build-a-scalable-backend-to-query-and-search-billions-of-records-16a532433fda">How to build a scalable backend to query and search billions of records?</a> was originally published in <a href="https://medium.com/elucidata">Elucidata</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>