Kafka Vs. PostgreSQL: How We Implemented Our Queuing System Using PostgreSQL
Tags: postgres, database, ios, rudderstack
Published: 2020-10-08 03:22:09

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/x5jc64zv06rl62p0odny.png)
<!-- wp:heading -->
<h2><strong>Overview</strong></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In our <a href="https://rudderstack.com/blog/why-rudderstack-used-postgres-over-apache-kafka-for-streaming-engine/">previous post</a>, we discussed why Apache Kafka wasn’t the right solution for RudderStack’s core streaming/queuing engine. Instead, we built our own streaming engine on top of PostgreSQL. This article discusses the internals of our implementation using the queuing system in more detail.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2><strong>Introduction to Queuing Systems</strong></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The core concept behind any queuing system is trivial. A CS101 implementation involves a linked list of items. A queuing system adds elements (or, in our case, events) to one end while consuming them from the other, as shown in the figure below. Once the system consumes an event,  one can remove it from the list.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="https://lh3.googleusercontent.com/w6FT-VQnz_h5Q3GNwT85fa7WaO5BsP409TMk670pVkMjrTPf8oVMBgkeFD0N4epKyyB_EtfVYsBu2jTKGDCQxBPaHM1OfoKsm8ZSC-WogTmqn1Wxsb5oHG9G18ZEkO_vJ_4CK333" alt="A diagram of linked list Queuing System - CS101 Implementation"/><figcaption>Queuing System - CS101 Implementation</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>For using a queuing system in practice, though, especially in a complex system like RudderStack, it must address several challenges.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2><strong>Challenge 1: Persistence in Queuing System</strong></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Firstly, the system needs to persist the queue on disk for resiliency and to handle system failures. Unfortunately, the minimum storage unit in a disk-based system is a block anywhere from 4 KBs to 64 KBs.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Furthermore, it is often desirable for performance reasons to read &amp; write in units of multiple blocks, often as large as a few MBs.&nbsp; It is not practical to store individual events in the disk as shown in basic CS101 implementation in the previous section.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>You can address this problem by batching events in the memory and flushing the whole batch to the disk, as shown below. In our case, we batch in units of ~128 events. We found the ideal batch size to provide a good balance between I/O time and the client <strong>acknowledgment (ACK)</strong> latency through testing. We don't send an ACK to the client unless the event persists to disk. Furthermore, we can't have the clients hanging too long for an ACK.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>On the consumer side, we can process much bigger batches to avoid the overhead of doing small I/O. For this case, we create on-disk segments of 100K events, which the system processes together. Once the system processes a segment of batches, one can delete it (see the diagram below), in most cases:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="https://lh3.googleusercontent.com/Mkpf_ZFPlubRVZJmS11WThMbqC4fo65uZPADE4RUjv8Ub9pv-yixsD-D8oDU5koTWtJOggS-qpYncNosCOZmPEGSpbcaKTS_V3FUzdJKamQW4RinRkffY6iX6FtxDuYhDt0W44jp" alt="Persisting a queue on disk for resiliency and to handle system failures."/><figcaption>Persistence</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2><strong>Challenge 2: Processing Order</strong> of Queues</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In the CS101 example, events are consumed in order, from the top of the queue, which makes deleting them trivial. In practice, though, events may have to be processed out of order.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>For example, if we fail to deliver an event, we mark it as <strong>failed</strong> and block further events from the user. In the meantime, the other unrelated events present in the queue after this failed event can be processed. This process could lead to a scenario (shown in the following diagram) where some events have been processed (marked with a green tick) while others (marked with a red cross) are waiting to be processed:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="https://lh5.googleusercontent.com/Tt5iLZBALHrmjglU1-yHGZ_YseEu0TX5NWWFUi4SJfBrkOLrK4aInA2DjFPOKFsd2W4djavB5aBClBzSlPpR24IkVZJm3lZkOLzVMqB1E9_fT0eZbUsy1fIKu4sFnCV8nEMScPaE" alt="Processing order - partial processing"/><figcaption>Processing Order - Partial Processing</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>This dynamic of inconsistent event delivery makes reclaiming space complicated. While we could wait for all the events in a block to be processed before deleting it, this is not ideal. A single unprocessed event can prevent the entire block from being reclaimed, leading to space overage.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3>Processing Order - Compaction</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>We address this problem by running what we call <strong>compaction</strong>, where the system compacts multiple, partially processed blocks into one block.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>During the compaction process, the system removes already-processed events, while it retains the unprocessed events. As you can see in the diagram below, the process has compacted the two leading processed blocks into a single block:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="https://lh3.googleusercontent.com/ESgOYpLjxhQd95Onx4ZZ69WLvYS3CbZO8oZC-B5LVND-HWD0BtlA3SE2L9ZupHkYu9bl4vS5BnMU7PBq5ZZprI5SwdIxe8mkwzrdd9LiuBW526HheK4s5kHl_DhjDgVrfzuAbofF" alt="Processing order - compaction, where multiple, partially processed blocks are compacted into one block"/><figcaption>Processing Order - Compaction</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>You can run the compaction algorithm periodically or by triggering. For example, once there are enough <strong>uncompacted</strong> blocks or both, as in our case - we run compaction every 30 minutes. However, we do this only if there are at least two blocks with at least 80 percent processed jobs. This logic ensures that we can reclaim enough space during compaction. At the same time, it also ensures we don't run it too often (as compaction slows down the system).</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In a normal steady-state, all jobs succeed, and the compaction job handles blocks that don't have any pending jobs, while the main processing jobs handle unprocessed blocks. As a result, there is little to no contention between the compaction job and the main processing job.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2><strong>Challenge 3: In-place Status Update</strong> with Status Queue</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In the above diagram, we showed events marked as successful or failed in place as the system processes them. Unfortunately, updating blocks in place is inefficient because of the underlying disk fundamentals.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>It would require us to read the block from the disk (incurring a random I/O), update in memory, and sync it back (incurring another random I/O). The two random I/Os incurred in this process can take anywhere from a few milliseconds to hundreds of milliseconds. These I/Os would be a bottleneck in our high throughput event processing pipeline.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>We address this problem by not updating in-place and keeping the event status in a separate queue instead. As the system processes events, it appends the success/failure status and other event metadata (like event ID, failure reason, etc.) to a separate queue.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>It’s important to note that the system may process an event multiple times. For example, if the event fails the first time, the system will retry it, resulting in two separate entries in the status queue.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="https://lh4.googleusercontent.com/G1ciJqIbQgYidXB6XD16YB_3sLhBMCcutx_6Q_fIeiYQ5uLyLwy8JqvE6ln4j_kWQ1lEaS-Sab6LPEoYvkXYNrds5fefNHV4jDI-shYFxLt7kxyZLn2pTLJcO3KrGcmNnFMQ9GU0" alt="Status queue with in-place status update"/><figcaption>Status Queue</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Marking a status incurs sequential I/O into the status queue, which can be orders of magnitude faster than a random I/O for an in-place update. However, this approach's downside is that we need to access two separate blocks to get the job status and identify the next job that the system needs to process.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In practice, though, the system would process current blocks (the status block and the event block) as well as cache them in memory, so these operations don’t require disk I/O.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2><strong>Conclusion</strong></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In this post, we discussed the three main design principles behind our queue implementation using PostgreSQL. Each block in the diagram shown above is stored as a separate table in PostgreSQL. The tables/blocks are numbered as <code>&lt;queue_name&gt;_job_1</code>, <code>&lt;queue_name&gt;_job_2</code>, etc., where <code>queue_name</code> is a unique identifier for a queue. We have multiple queues inside of RudderStack.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The statuses are stored in separate tables <code>&lt;queue_name&gt;_job_status_1</code>, <code>&lt;queue_name&gt;_job_status_2</code>, etc. Once a particular table or a sequence of tables has been processed, they are compacted into a single block (or no block if all jobs are successful). This compaction runs in a separate thread.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The queries to find unprocessed jobs in the main thread also find processed jobs during compaction, and they are all implemented in SQL. The SQL interface has also been a great debugging tool for inspecting and updating the jobs. For example, we sometimes have to force retry jobs which have aborted, or vice-versa, which can be easily done in SQL.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Implementing these concepts is certainly interesting, but we will let you dig into the actual code yourself in our <a href="https://github.com/rudderlabs/rudder-server/tree/master/jobsdb">GitHub repository</a>.</p>
<!-- /wp:paragraph -->