<?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 Parampreet Singh on Medium]]></title>
        <description><![CDATA[Stories by Parampreet Singh on Medium]]></description>
        <link>https://medium.com/@paramsingh96?source=rss-ffb8fde02778------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*3GFLuBnnbsjqL2rkA8WIFg.jpeg</url>
            <title>Stories by Parampreet Singh on Medium</title>
            <link>https://medium.com/@paramsingh96?source=rss-ffb8fde02778------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 09:57:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@paramsingh96/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[How We Pushed a Million Keys to Redis in Seconds]]></title>
            <link>https://medium.com/gojekengineering/how-we-pushed-a-million-keys-to-redis-in-seconds-b99020e7cb0f?source=rss-ffb8fde02778------2</link>
            <guid isPermaLink="false">https://medium.com/p/b99020e7cb0f</guid>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[ruby]]></category>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[redis]]></category>
            <dc:creator><![CDATA[Parampreet Singh]]></dc:creator>
            <pubDate>Fri, 06 Mar 2020 03:49:09 GMT</pubDate>
            <atom:updated>2020-03-06T05:43:49.728Z</atom:updated>
            <content:encoded><![CDATA[<h4>Dealing with a lot of keys? Redis’ Pipe Mode is your friend.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gN5KGQcKSipjSlQqEppJMA.jpeg" /><figcaption><a href="http://bit.ly/335bM2t">gojek.jobs</a></figcaption></figure><p>Hello there!</p><p>In this post, I’ll share my ideas on how we populated Redis (running in a Kubernetes cluster)… in a matter of seconds.</p><p>Here’s what can you expect from this post:</p><blockquote>1. How to connect to Redis server running in a Kubernetes cluster ?</blockquote><blockquote>2. What is Port-forwarding ?</blockquote><blockquote>3. How to use Redis mass insertion &amp; push millions of keys in seconds ?</blockquote><blockquote>4. How to generate Redis Protocol ?</blockquote><blockquote>5. How to read /parse a CSV in Ruby ?</blockquote><blockquote>Wait, but why do I need to do this? 🤔</blockquote><p>At Gojek, we use Redis in one of the services for caching drivers for faster lookups. Since we have deployed this service to new clusters, we needed to populate Redis with ~81K keys.</p><h4>What we didn’t do (and should not be done)</h4><p>Well, this. 👇</p><p>$ redis-cli -h &quot;hostname&quot; -p 6379 set &quot;key&quot; &quot;value&quot;</p><p>This simple and easy way of storing a key through <a href="https://redis.io/topics/rediscli">redis-cli</a> is okay, but not for thousands or millions of keys. You don’t want to end up waiting for hours unless you are <a href="https://en.wikipedia.org/wiki/Phoebe_Buffay#%22Regina_Phalange%22">Regina Phalange</a>! 😛</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fgiphy.com%2Fembed%2F5xaOcLvROln5TmaaVOM%2Ftwitter%2Fiframe&amp;display_name=Giphy&amp;url=https%3A%2F%2Fgiphy.com%2Fgifs%2Fthecomebackhbo-comeback-the-val-cherish-5xaOcLvROln5TmaaVOM&amp;image=https%3A%2F%2Fmedia.giphy.com%2Fmedia%2F5xaOcLvROln5TmaaVOM%2Fgiphy.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=giphy" width="435" height="301" frameborder="0" scrolling="no"><a href="https://medium.com/media/83a8ef0b92ab605e4858cc53db5101f5/href">https://medium.com/media/83a8ef0b92ab605e4858cc53db5101f5/href</a></iframe><p>Using a normal Redis client to perform mass insertion is not a good idea. The naive approach of sending one command after the other is slow, because you have to pay for the round trip time for every command.</p><h3>Let’s do something different!</h3><p>We will use Redis mass insertion, but before going to that, let’s talk a bit about <a href="https://redis.io/topics/protocol">Redis Protocol</a>.</p><blockquote>Redis clients communicate with the Redis server using a protocol called <strong>RESP</strong> (REdis Serialization Protocol).</blockquote><p>With that said, let’s go write some code! I like toying around with <a href="https://www.ruby-lang.org/en/">Ruby</a>, so this was my language of choice.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/59319951b0d29feee60832a68f811a18/href">https://medium.com/media/59319951b0d29feee60832a68f811a18/href</a></iframe><p>gen_redis_proto function will generate the protocol required for mass insertion.</p><pre>2.6.3 &gt; <strong>puts gen_redis_proto(&quot;SET&quot;,&quot;mykey&quot;,&quot;Hello World!&quot;).inspect</strong></pre><p>Running the above command in Ruby console, will give us the following protocol.</p><pre>&quot;*3\r\n$3\r\nSET\r\n$5\r\nmykey\r\n$12\r\nHello World!\r\n&quot;</pre><p>Well, this is how a command is represented and sent to the Redis Server through <a href="https://redis.io/topics/protocol">Redis Protocol</a>.</p><pre>*&lt;args&gt;&lt;cr&gt;&lt;lf&gt;<br>$&lt;len&gt;&lt;cr&gt;&lt;lf&gt;<br>&lt;arg0&gt;&lt;cr&gt;&lt;lf&gt;<br>&lt;arg1&gt;&lt;cr&gt;&lt;lf&gt;<br>...<br>&lt;argN&gt;&lt;cr&gt;&lt;lf&gt;</pre><blockquote>Where &lt;cr&gt; means &quot;\r&quot; (or ASCII character 13) and &lt;lf&gt; means &quot;\n&quot; (or ASCII character 10).</blockquote><p>We can now run this script, but here’s a catch. Our Redis server runs in a Kubernetes cluster and we didn’t want to install Ruby and its gems inside a cluster. So now?</p><h3>Enter port-forwarding! 👍</h3><pre><strong>$ kubectl -n &quot;namespace&quot; port-forward &quot;pod-name&quot; 7000:6379</strong></pre><p>Connections made to local port 7000 are forwarded to port 6379 of the pod that is running the Redis server. With this connection in place we can use our local workstation to debug the database that is running in the pod.</p><blockquote><em>Finally, we run our script to populate Redis 😬</em></blockquote><pre><strong>$ ruby redis_mass_insert.rb | redis-cli -p 7000 --pipe</strong></pre><pre>All data transferred. Waiting for the last reply...<br>Last reply received from server.<br>errors: 0, replies: 81003</pre><p>We ran this script and it completed within a fraction of seconds!</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fgiphy.com%2Fembed%2FjRARA4zqym98KmvkUy%2Ftwitter%2Fiframe&amp;display_name=Giphy&amp;url=https%3A%2F%2Fgiphy.com%2Fgifs%2FjRARA4zqym98KmvkUy&amp;image=https%3A%2F%2Fmedia.giphy.com%2Fmedia%2FjRARA4zqym98KmvkUy%2Fgiphy.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=giphy" width="435" height="246" frameborder="0" scrolling="no"><a href="https://medium.com/media/2e2e07f03c03860b9c4584e56af701ce/href">https://medium.com/media/2e2e07f03c03860b9c4584e56af701ce/href</a></iframe><h3>But, how?</h3><p>In 2.6 or later versions of Redis the redis-cli utility supports a new mode called <strong>pipe mode</strong> that was designed in order to perform mass insertion.</p><h4>Under the hood of pipe mode</h4><p>According to the official doc:</p><ul><li><em>redis-cli — pipe tries to send data as fast as possible to the server.</em></li><li><em>At the same time it reads data when available, trying to parse it.</em></li><li><em>Once there is no more data to read from stdin, it sends a special </em><strong><em>ECHO</em></strong><em> command with a random 20 bytes string: we are sure this is the latest command sent, and we are sure we can match the reply checking if we receive the same 20 bytes as a bulk reply.</em></li><li><em>Once this special final command is sent, the code receiving replies starts to match replies with these 20 bytes. When the matching reply is reached it can exit with success.</em></li></ul><h3>Naice, what’s next?</h3><p>Well, I tried populating Redis locally with <em>a million keys.</em></p><p>It worked like a charm, in just ~2 seconds. 😄</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fgiphy.com%2Fembed%2Fj6ZQKMy3W3629lDJSX%2Ftwitter%2Fiframe&amp;display_name=Giphy&amp;url=https%3A%2F%2Fgiphy.com%2Fgifs%2Fhbo-avenue-5-avenue5-j6ZQKMy3W3629lDJSX&amp;image=https%3A%2F%2Fmedia.giphy.com%2Fmedia%2Fj6ZQKMy3W3629lDJSX%2Fgiphy.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=giphy" width="435" height="244" frameborder="0" scrolling="no"><a href="https://medium.com/media/1f4b8ea9d2c60c7b3ad7d8af5ebf58da/href">https://medium.com/media/1f4b8ea9d2c60c7b3ad7d8af5ebf58da/href</a></iframe><h3>That’s it!</h3><p>I really hope that this post gave you some new insights.</p><p>Thanks for reading! 💚</p><p><strong>References</strong></p><ol><li><a href="https://redis.io/topics/mass-insert">Redis Mass Insertion</a></li><li><a href="https://redis.io/topics/protocol">Redis Protocol</a></li><li><a href="https://kubernetes.io/docs/tasks/access-application-cluster/port-forward-access-application-cluster/">Port Forwarding in Kubernetes to access applications</a></li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Yigf1nGxRKjzV7vIC_YkYg.png" /></figure><p>Want our stories to land in your inbox? <a href="https://mailchi.mp/go-jek/gojek-tech-newsletter">Sign up for our newsletter!</a></p><figure><a href="http://bit.ly/335bM2t"><img alt="Gojek Job Openings" src="https://cdn-images-1.medium.com/max/803/1*XSAkmYAomyHp6_18rjjAfA.jpeg" /></a><figcaption>gojek.jobs</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b99020e7cb0f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/gojekengineering/how-we-pushed-a-million-keys-to-redis-in-seconds-b99020e7cb0f">How We Pushed a Million Keys to Redis in Seconds</a> was originally published in <a href="https://medium.com/gojekengineering">Gojek Product + Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing pattern searching queries in PostgreSQL]]></title>
            <link>https://medium.com/@paramsingh96/optimizing-pattern-searching-queries-in-postgresql-84087d9bac8c?source=rss-ffb8fde02778------2</link>
            <guid isPermaLink="false">https://medium.com/p/84087d9bac8c</guid>
            <category><![CDATA[index]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[gin]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Parampreet Singh]]></dc:creator>
            <pubDate>Sat, 20 Jul 2019 12:36:16 GMT</pubDate>
            <atom:updated>2020-02-24T05:33:52.933Z</atom:updated>
            <content:encoded><![CDATA[<h3>Optimizing Pattern Searching Queries in PostgreSQL</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*lp6JayELcs4HwWZCFXNP6g.jpeg" /></figure><p>While I was working on one of the projects that use PostgreSQL as a relational database, I got to know about how we can optimize our PSQL pattern searching queries for a text column. This article would highlight all the knicks and knacks of doing the same.</p><h4>Let’s get started</h4><p>Suppose we have <strong>authors</strong> table that has ~ 2M rows present in it and we want to use pattern searching queries on <strong>name </strong>column which could be LIKE queries or queries that uses regex to find desired rows.</p><pre>SELECT * FROM authors WHERE name LIKE &#39;%param%;  /* LIKE Query */<br>SELECT * FROM authors WHERE name ~ &#39;param.*&#39;;    /* Regex Query */</pre><p>But when we analyze these queries, we would get the following result.</p><pre>                             QUERY PLAN<br> — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -<br> Seq Scan on authors(cost=0.00..9393.21 rows=466 width=415) (actual time=631.270..631.271 rows=0 loops=1)<br> Filter: (name ~~ &#39;%param%&#39;::text)<br> Rows Removed by Filter: 60966<br> Planning Time: 5.128 ms<br> Execution Time: 634.562 ms<br>(5 rows)</pre><p>Obviously, ~600 ms just to get some desired rows is super slow which we would not like to use in our app even if we have got super-fast machines. So what to do?</p><h4>GIN Index comes to the rescue</h4><p><em>GIN stands for Generalized Inverted Index and should be considered as a genie, not a drink. GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search for element values that appear within the composite items. For example, the items could be documents, and the queries could be searches for documents containing specific words.</em></p><h4>How to use GIN index?</h4><p>First, we need to enable <strong>pg_trgm</strong> extension which requires at least PostgreSQL 9.4.</p><pre>CREATE EXTENSION pg_trgm;</pre><pre>CREATE INDEX CONCURRENTLY trgm_index_authors_name ON authors USING gin (lower(name) gin_trgm_ops);</pre><p>NOTE: <br>1. <em>CONCURRENTLY</em> is used to build our index without locking out writes.<br>2. We must use the <em>lower(pg_column)</em> attribute to allow efficient case-insensitive searches.<br>3. <em>gin_trgm_ops </em>is the operator class which we have to use for GIN indexing.</p><h4>Have we really optimized?</h4><p>Let’s analyze now the above LIKE or regex queries.</p><pre>explain analyze SELECT * FROM authors WHERE name LIKE &#39;%param%&#39;;</pre><pre>                               QUERY PLAN<br> — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — <br> Bitmap Heap Scan on authors (cost=28.07..63.31 rows=9 width=415) (actual time=0.130..0.131 rows=0 loops=1)<br>   Recheck Cond: (lower((name)::text) ~~ &#39;%param%&#39;::text)<br>   Rows Removed by Index Recheck: 1<br>   Heap Blocks: exact=1<br>     -&gt; Bitmap Index Scan on trgm_index_authors_name       (cost=0.00..28.07 rows=9 width=0) (actual time=0.099..0.099 rows=1   loops=1)<br>        Index Cond: (lower((name)::text) ~~ &#39;%param%&#39;::text)<br>   Planning Time: 2.282 ms<br>   Execution Time: 2.211 ms<br>(8 rows)</pre><p>Wait what, only ~2 ms, now this definitely feels good to use it. Isn’t it?</p><h4>How trigram indexing is actually working?</h4><p>Let us get an idea on how trigram indexes work by breaking up text into trigrams.</p><pre>select show_trgm(&#39;param&#39;);</pre><pre>           show_trgm<br> — — — — — — — — — — — — — — — — -<br> {&quot;  p&quot;,&quot; pa&quot;,&quot;am &quot;,ara,par,ram}<br>(1 row)</pre><p>For example, suppose we have the following three short documents.<br>(1) This article is short<br>(2) And the article is really good<br>(3) We like short articles</p><p>The trigram index for these three documents looks like:</p><pre>&quot;  a&quot;=&gt;[1, 2, 3]   &quot;  i&quot;=&gt;[1, 2]     &quot;  s&quot;=&gt;[1, 3]   &quot;  t&quot;=&gt;[1, 2]   <br>&quot; ar&quot;=&gt;[1, 2, 3]   &quot; is&quot;=&gt;[1, 2]     &quot; sh&quot;=&gt;[1, 3]   &quot; th&quot;=&gt;[1, 2] &quot;art&quot;=&gt;[1, 2, 3]   &quot;cle&quot;=&gt;[1, 2, 3]  &quot;his&quot;=&gt;[1]      &quot;hor&quot;=&gt;[1, 3] &quot;icl&quot;=&gt;[1, 2, 3]   &quot;is &quot;=&gt;[1, 2]     &quot;le &quot;=&gt;[1, 2]   &quot;ort&quot;=&gt;[1, 3] &quot;rt &quot;=&gt;[1, 3]      &quot;rti&quot;=&gt;[1, 2, 3]  &quot;sho&quot;=&gt;[1, 3]   &quot;thi&quot;=&gt;[1] &quot;tic&quot;=&gt;[1, 2, 3]   &quot;  g&quot;=&gt;[2]        &quot;  r&quot;=&gt;[2]      &quot; an&quot;=&gt;[2]     &quot; go&quot;=&gt;[2]         &quot; re&quot;=&gt;[2]        &quot;all&quot;=&gt;[2]      &quot;and&quot;=&gt;[2] &quot;eal&quot;=&gt;[2]         &quot;goo&quot;=&gt;[2]        &quot;he &quot;=&gt;[2]      &quot;lly&quot;=&gt;[2]<br>&quot;ly &quot;=&gt;[2]         &quot;nd &quot;=&gt;[2]        &quot;od &quot;=&gt;[2]      &quot;ood&quot;=&gt;[2] &quot;rea&quot;=&gt;[2]         &quot;the&quot;=&gt;[2]        &quot;  l&quot;=&gt;[3]      &quot;  w&quot;=&gt;[3]     &quot; li&quot;=&gt;[3]         &quot; we&quot;=&gt;[3]        &quot;es &quot;=&gt;[3]      &quot;ike&quot;=&gt;[3]   &quot;ke &quot;=&gt;[3]         &quot;les&quot;=&gt;[3]        &quot;lik&quot;=&gt;[3]      &quot;we &quot;=&gt;[3]</pre><p>Given a query to search documents having <em>articles </em>in it, it will break down <em>articles</em> in the following manner based on our search pattern and bring out the corresponding documents containing <em>articles.</em></p><pre>&quot; a&quot; AND &quot; ar&quot; AND &quot;art&quot; AND &quot;cle&quot; AND &quot;es &quot; AND &quot;icl&quot; AND &quot;les&quot; AND &quot;rti&quot; AND &quot;tic</pre><p>Based on the above expression we will get our desired document 3 containing <em>articles. Same</em></p><p>To dig further, how GIN indices use trigram indexes, we can follow this article.</p><p><a href="https://habr.com/en/company/postgrespro/blog/448746/">Indexes in PostgreSQL - 7 (GIN)</a></p><h4>Why only GIN? Why not another type of indices like GiST?</h4><p>GiST stands for Generalized Search Tree which is a height-balanced search tree that consists of node pages. The nodes consist of index rows. Each row of a leaf node (leaf row), in general, contains some <em>predicate</em> (boolean expression) and a reference to a table row (TID). Indexed data (key) must meet this predicate.<br><em>In choosing which index type to use, GiST or GIN, consider these performance differences:</em></p><ul><li><em>GIN index lookups are about three times faster than GiST</em></li><li><em>GIN indexes take about three times longer to build than GiST</em></li><li><em>GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled</em></li><li><em>GIN indexes are two-to-three times larger than GiST indexes</em></li></ul><p>As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data, GiST indexes are faster to update.</p><h4>How can we confirm if our query is actually using our new index?</h4><p>So when we have set up our app with pg_trgm and set up GIN indexing, we can monitor our pg_stat_user_indexes table for greater insights.</p><pre>select indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = &#39;authors&#39;;</pre><p>And we would get the following bunch of columns &amp; rows,</p><pre>indexrelname            | idx_scan | idx_tup_read | idx_tup_fetch<br>------------------------+----------+--------------+--------------<br>trgm_index_authors_name |       36 |       140614 |             0<br>authors_pkey            |   140124 |      1600097 |       1600096<br>(2 rows)</pre><p>We will start seeing some numbers in idx_scan of our index. With every query using our new index, we will see an increment in idx_scan of our table. That’s how we can assure in production as well.</p><h4>Some caveats as food for thought</h4><ol><li>Our input query must have at least 3 characters so Postgres can extract at least one trigram from the input query in order to use our trigram index.</li><li>Multi-column indexing is not supported.</li><li>GIN does not index NULL values.</li><li>Large tables can take a large amount of time to create GIN index which must be tested before deploying to production.</li><li>GIN index must be created CONCURRENTLY unless we want to lock out writes that might happen during the index creation.</li></ol><p>Bingo! We’re good to deploy now. <br>Till next time, happy learning and coding. Cheers! 😁</p><h4>References</h4><p>Some articles to dive deeper into GIN &amp; GIST indexing.</p><ul><li><a href="https://www.postgresql.org/docs/9.3/gin-intro.html">PostgreSQL: Documentation: 9.3: Introduction</a></li><li><a href="http://www.sai.msu.su/~megera/wiki/Gin">Gin</a></li><li><a href="https://habr.com/en/company/postgrespro/blog/444742/">Indexes in PostgreSQL - 5 (GiST)</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=84087d9bac8c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>