<?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 Lucio Ferrao on Medium]]></title>
        <description><![CDATA[Stories by Lucio Ferrao on Medium]]></description>
        <link>https://medium.com/@lucio.ferrao?source=rss-6e9886b8f629------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*O0ky3KQhqaDTyF82WFZnCA.jpeg</url>
            <title>Stories by Lucio Ferrao on Medium</title>
            <link>https://medium.com/@lucio.ferrao?source=rss-6e9886b8f629------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 06:22:32 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@lucio.ferrao/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[Pump Up the Volume: Scalable Database Queueing]]></title>
            <link>https://medium.com/outsystems-engineering/pump-up-the-volume-scalable-database-queueing-86075fa532cb?source=rss-6e9886b8f629------2</link>
            <guid isPermaLink="false">https://medium.com/p/86075fa532cb</guid>
            <category><![CDATA[outsystems]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[coding]]></category>
            <category><![CDATA[craftsmanship]]></category>
            <dc:creator><![CDATA[Lucio Ferrao]]></dc:creator>
            <pubDate>Fri, 02 Mar 2018 16:38:48 GMT</pubDate>
            <atom:updated>2018-07-06T15:32:18.614Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ggc3KIiyRiVfZgs2KtkBHw.png" /></figure><p>OutSystems allows processing events at ridiculous speeds! Our laboratory tests revealed that the pipeline can handle impressive amounts of sustained throughput.</p><p>Not only that, but our customers have started to deliver solutions that can batch process peaks of <strong>400,000 payments</strong> in minutes.</p><p>These are so-called light processes, which can only run faster if we skip all the database interaction that long-running processes require. They are specifically suited to track events that are fast to process.</p><p>But it wasn’t always so. Five years ago, OutSystems launched Business Process Technology (BPT) as a pragmatic approach to process modeling and execution. At the time, <a href="https://en.wikipedia.org/wiki/Business_Process_Model_and_Notation">BPMN</a> had inspired the notation, and <a href="https://en.wikipedia.org/wiki/Business_Process_Execution_Language">BPEL </a>had inspired the execution.</p><p>BPT allowed customers to model and execute long-running processes where activities could wait for minutes, hours, or months until a given event occurred. Problem solved, right? Not so fast. Our customers had other ideas. So, we had to act. Here’s how it all went down.</p><h3>Looking Under the Covers</h3><p>Processes can advance whenever database entities are updated. So, at the time, we introduced the concept of “insert” and “update” events for specific database entities. This way, processes could wait for specific instances of a process or activity by specifying the primary key of the entity they were waiting for.</p><p>These events are captured using database triggers that are attached to the “insert” and “update” statements, adding new entries to a queue that is also in the database. These queued entries are then fetched by multiple schedulers that compete to process these entries in the context of a process definition.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/0*NNQb9k8Xc8UXzNpC." /></figure><p>This simplified the coding of fast reaction processes — processes that react to human activities in less than a second — because then developers only needed to define the entity they are waiting for to release a specific instance. This resulted in great joy for OutSystems developers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/384/0*yncvGr7nYGi2Vr6w." /><figcaption>Sample process using OutSystems BPT.</figcaption></figure><p>We were caught by surprise when we learned that some of our customers were using BPT for another purpose: large-scale batch-processing and also event broker systems.</p><h3>In Real Life: Creative Usage of BPT</h3><p>With the original BPT model, the simplest process for handling an event creates three application server requests and nine database transactions. For the scenarios we had envisioned, this capability was more than acceptable.</p><p>Reality soon kicked in, and customers quickly turned up the volume. They found themselves dealing with more events than we had planned for, reaching or even surpassing 500.000 daily events on average!</p><p>BPT was not designed for such loads, leading to a performance degradation that in worst-case scenarios, could bring the whole system to a screeching halt.</p><p>The BPT activity identifier was a 32-bit integer. The customers who were consuming over 500.000 identifiers per day (and growing) would soon not be able to maintain the system for the number of years they had planned for.</p><p>Once we were aware of this issue, we looked into it, and we realized that the performance problem led some customers to avoid BPT altogether. This was disappointing because BPT’s expressiveness is the perfect fit for such event broker patterns.</p><h3>Revising the Event Handling Pipeline</h3><p>We revisited our entire event handling pipeline to search for bottlenecks and critical issues, and we realized that the scheduler query was taking a long time to dequeue and lock events in the database.</p><p>This query is one of the most complicated in the <a href="https://www.outsystems.com/platform/">OutSystems platform</a>. The scheduler service (inside each front-end server) uses it to gather events that the corresponding application server will then process.</p><p>Originally, this was a query that captured all the available events followed by a cycle that iterated the query’s results trying to lock each row. If the row were already locked, it would be skipped; otherwise, the row was marked to be fetched and collected. This cycle finished once it collected the first 10 or 20 rows.</p><p>Conceptually it was correct, and most of the time the database optimizer did a good job of using the appropriate strategy to optimize this query. But once the queue grew to a few hundred thousand rows, this was no longer true. The optimizer would frequently choose an inefficient strategy, and row locks were appearing for too many rows and even escalating to table locks.</p><h4>Revisiting the Query</h4><p>The first measure we took was to split the query into smaller and simpler ones. So, the first is a query to populate a temporary table that synthesizes the available modules (eSpaces) for a specific server using the “zone” configuration. The result is the delivery of a small set of identifiers, and its execution is trivial for the database optimizer.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b9573f1d5c565012cb9aece18401d2d7/href">https://medium.com/media/b9573f1d5c565012cb9aece18401d2d7/href</a></iframe><p>Next, we focused on the queue table. The records of the previous tables were already placed into temporary tables, simplifying the optimizer work at this step.</p><p>The critical part here is getting the rows on the top of the queue using “updlock, rowlock, readpast” hints for the table that represents the queue. So we make sure the query is protected with a SELECT TOP(@batchsize) to lock only the rows you really want to fetch:</p><ul><li>“rowlock” forces row level locks instead of page or table locks.</li><li>“updlock” locks each returned row.</li><li>“readpast” skips all previously locked rows.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5e9ee3231b4dcd7cae3be8736c64cb6c/href">https://medium.com/media/5e9ee3231b4dcd7cae3be8736c64cb6c/href</a></iframe><p>Once the rows are locked, we update them in a different query:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c6abee77f31cde3294c3db132ebe1dd8/href">https://medium.com/media/c6abee77f31cde3294c3db132ebe1dd8/href</a></iframe><p>And finally, the results are returned with the joins we need from other tables with a trivial query.</p><p>This solved most of the scalability problems. We were finally able to execute a very fast query with only the number of returned events as a dependency.</p><h4>Revisiting the Identifier: Defining an Event Pipeline</h4><p>But we still had to deal with the identifier exhaustion problem. Changing the database metamodel was not an option due to the upgrade costs and risks. The OutSystems customers who depend on BPT have a zero downtime policy for such applications. A substantial database upgrade would violate that policy.</p><p>So, we needed to design a different approach. We ended up defining an entire event pipeline for the kind of process that was flooding the scheduler.</p><p>In the new pipeline, these processes are triggered by an “insert” entity event and are handled by the single automatic activity inside the process. Such processes are so simple that they don’t need to be tracked by our process metamodel. There are no sequences, no decisions, no wait activities, no human activities involved.</p><p>This new pipeline uses a new queue of events that is not restricted to the 32 identifier limitation. Each event only triggers a single application request to handle the code defined inside the automatic activity in a single database transaction.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/751/0*CnIa_lmGgcoTyAaO." /></figure><h3>Customer Scenario: Taking BPT All the Way Up to 11</h3><p>Let’s take a look at a specific customer scenario so you can see how this works.</p><p>One of our customers needed to batch-process peaks of 400K payments in just a few minutes. Event-driven data processing with high parallelization is key in this situation to prevent the bottleneck of pooling payment requests and serializing the processing. This means that you need to add a scalable architecture on top of the light process execution.</p><p>The following picture depicts the solution that allows us to implement the massive batch processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/0*zp7a8_HTkiBbpkJC." /></figure><ol><li>Files are sent from different commercial systems and are processed by a timer scheduled to run at regular intervals.</li><li>Each file contains thousands of records that are inserted in bulk into a Payment table.</li><li>For each set of 2,000 payment records, a single record is inserted into a bucket control table, setting the initial and final positions of the payments bound to the bucket.</li></ol><p>At this point, a BPT process is fired, reacting to the bucket record insertion, and the payment records bound to the same bucket are iterated to validate and classify each payment (Clearing House).</p><p>The Clearing House then decides which payment channel should be used to optimize operation costs.</p><p>The process could be fired when the record is inserted in the Payment table, but the bucket strategy lets you balance between parallelism and overhead. Smaller bucket sizes increase parallelism and reduce the scope of potential processing failures. The tradeoff is that you get additional overhead from more requests, more database transactions, and more context gathering for each bucket.</p><p>The right balance must be achieved by trying, measuring, and iterating until you are happy with the results.</p><p>Finally, at the end of the processing period, a timer will group payments to direct them per channel.</p><p>To respect the maximum window of time required to respond to a certain peak, you can adjust your horizontal scaling options, such as number of front-end servers, timers per front-end server, and event processing threads per front-end server. In the example, you can see the results for fine-tuning the handling of 400,000 payments, when the requirement is to process all the payments in less than half an hour. With minimal infrastructure — values above the red line — it took us <strong>89 minutes</strong> to process the 400,000 requests.</p><p>After some tests of the scalable options (see values below the red line), by duplicating the number of front-ends dedicated to each phase and increasing the number of threads, we again reduced the processing time to a mere <strong>14 minutes</strong>.</p><h3>Light Events at Work</h3><p>OutSystems processes are designed to model and execute long-running activities where a specific event can take weeks or months to occur. This lighter execution mode is useful for scaling database queueing in the case of large-scale event-driven processes that handle several thousands of events processed per day. To learn how this works, read <a href="https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Processes/Design_Scalable_Database_Queueing_Using_Light_Processes">our documentation</a>.</p><h3>Want More?</h3><p>Learn more <a href="https://www.outsystems.com/the-small-book/">about us</a>, our <a href="https://www.outsystems.com/platform/">product</a>, and find <a href="https://www.outsystems.com/company/careers/job-list/Engineering/?utm_source=medium">work opportunities</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=86075fa532cb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/outsystems-engineering/pump-up-the-volume-scalable-database-queueing-86075fa532cb">Pump Up the Volume: Scalable Database Queueing</a> was originally published in <a href="https://medium.com/outsystems-engineering">OutSystems Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>