<?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 Mikhail Kashkin on Medium]]></title>
        <description><![CDATA[Stories by Mikhail Kashkin on Medium]]></description>
        <link>https://medium.com/@xen?source=rss-ff73b036630c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*2D-jPkId3A_CDO1k.png</url>
            <title>Stories by Mikhail Kashkin on Medium</title>
            <link>https://medium.com/@xen?source=rss-ff73b036630c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 02:54:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@xen/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[The right fast pagination ]]></title>
            <link>https://medium.com/@xen/fffast-pagination-cb8a1b6e4f81?source=rss-ff73b036630c------2</link>
            <guid isPermaLink="false">https://medium.com/p/cb8a1b6e4f81</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[highload]]></category>
            <category><![CDATA[development]]></category>
            <dc:creator><![CDATA[Mikhail Kashkin]]></dc:creator>
            <pubDate>Mon, 21 Aug 2017 15:56:56 GMT</pubDate>
            <atom:updated>2017-08-21T20:22:59.303Z</atom:updated>
            <content:encoded><![CDATA[<p>You have a site and want to publish some data. But you have more data than possible to fit on one single page. So you decide to make one page and continue on another and you added small Next&gt; button on the very bottom of your page. Now you have pagination.</p><p>Your link will be look something like href=&#39;/link?page=N where the N will be the number of the next page. But what is happening inside backend?</p><p><em>We at </em><a href="https://www.askpapir.com/"><em>Papir</em></a><em> have about 60M records from</em> <strong>Ministry of Finance of Ukraine</strong><em> that we got from own partners from </em><a href="https://e-data.gov.ua/"><em>https://e-data.gov.ua</em></a><em>. And we want to publish them all.</em></p><h4>Pagination SQL</h4><p>Almost every pagination code I’ve met was based on simple SQL mechanics: LIMIT and OFFSET instructions. But what will happen if we will go much deeper than usual few first pages? Here is the log of my interactions using psql:</p><pre>$ psql -d papir<br>psql (9.5.8)<br>Type &quot;help&quot; for help.</pre><pre>papir=# \timing<br>Timing is on.</pre><pre>papir=# select trans_id, amount from spending offset 0 limit 2;<br>trans_id | amount<br>----------+---------<br>10374495 | 540,00<br>10374497 | 1027,01<br>(2 rows)</pre><pre>Time: <strong>0.231</strong> ms</pre><pre>papir=# select trans_id, amount from spending offset 10000 limit 2;<br>[...skip...]<br>Time: <strong>5.020</strong> ms</pre><pre>papir=# select trans_id, amount from spending offset 100000 limit 2;<br>[...skip...]<br>Time: <strong>29.916</strong> ms</pre><pre>papir=# select trans_id, amount from spending offset 1000000 limit 2;<br>[...skip...]<br>Time: <strong>270.332</strong> ms</pre><pre>papir=# select trans_id, amount from spending offset 10000000 limit 2;<br>trans_id | amount<br>----------+--------<br>21376096 | 266,70<br>21375771 | 126,00<br>(2 rows)</pre><pre>Time: <strong>28261.112</strong> ms</pre><p>We see the pretty straightforward direction, increasing offset increase execution time. If for small numbers it is still acceptable speed when we make another big step then growth of the execution time becomes unpredictable:</p><pre>papir=# select trans_id, amount from spending offset 20000000 limit 2;<br>trans_id |  amount<br>----------+----------<br>41417087 | 50000,00<br>41417404 | 1253,22<br>(2 rows)</pre><pre>Time: <strong>71704.054</strong> ms</pre><p>I know this is very big depth, in real life I don’t think that somebody will list your pages to get to 20M’s records. But the same time this query is very simple and I use it only for demonstration purpose. In reality, if you can have a much more complex query that includes several JOIN&#39;s and require more sophisticated logic.</p><h4>Can we change pagination to make it execution speed predictable on any amount of data?</h4><p>Actually <strong>yes</strong>, and I wonder why so many tutorials that teach people to use LIMIT and OFFSET.</p><p>Let’s rewrite our query a little and see what happens. First of all, you need to make sort, data are stored in database its own order. And you almost always need to use sort. It is natural if you run your own blog then you will sort posts in chronological order. But for this approach, you need to sort by column with the <strong>unique</strong> field. For my data, it is simple since our records are stored with unique transaction id that <a href="https://en.wikipedia.org/wiki/Monotonic_function">monotonous increase</a> and already making an order we need. For your needs, you probably need to make put all your sorting fields that you use before the unique field.</p><p>Now we can cut of using this unique field. For example, let’s take trans_id from the last query and see what speed we will get:</p><pre>papir=# select trans_id, amount from spending where trans_id&gt;=41417087 order by trans_id limit 2;</pre><pre>trans_id |  amount<br>----------+-----------<br>41417087 | 50000,00<br>41417088 | 148775,30<br>(2 rows)</pre><pre>Time: <strong>0.345</strong> ms</pre><p>This is the speed that we deserved. You can change link code to href=&#39;/link?next_id={trans_id}.</p><p>One last reminder, your column should have an index. If it is unique then it already has index.</p><p>P.S. If you use more complex pagination than prev and next buttons you probably will need more coding.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cb8a1b6e4f81" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Me on machine learning. Day one]]></title>
            <link>https://medium.com/@xen/me-on-machine-learning-day-one-9ffb054ffbbe?source=rss-ff73b036630c------2</link>
            <guid isPermaLink="false">https://medium.com/p/9ffb054ffbbe</guid>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[education]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <dc:creator><![CDATA[Mikhail Kashkin]]></dc:creator>
            <pubDate>Sun, 14 Feb 2016 00:29:26 GMT</pubDate>
            <atom:updated>2016-02-14T00:30:03.550Z</atom:updated>
            <content:encoded><![CDATA[<p>This year I decided to start machine learning, with support of my wife we plan to make me the new generation student that will do study on Coursera, Khan Academy and other similar sites.</p><p>Today was my first day of this big journey. Also, I decided to return blogging to make footnotes about things that will touch me and my thoughts during education.</p><p>First of all, I need to admit that I don’t know what I know and what don’t know about this specialty. Generally speaking I already spent months researching data in the past. Most biggest project I was involved was road traffic optimization process in one of the biggest city in the world — Moscow (Russia). I even have some science publication (as co-author) about our research. I do data analysis in other companies and for myself, but I never present myself as data scientist in the past.</p><p>I have 10+ years of experience in python development, so switch looks pretty natural for me. I think about it as my next big career step, and since I get bored of doing another web site I don’t have many interesting options where to make my next step.</p><p>So, today was my first day when I started.</p><p>First of all I took course <a href="https://www.coursera.org/learn/mathematics-and-python">Математика и Python для анализа данных</a> (Math and Python for data analysis) on Coursera.org. And watched lections for first two weeks. I can say only one thing — I don’t like it as much as I don’t like an idea of studying in Russian. This doesn’t make me closer to what I want to achieve in different ways. But I wanted to test myself what I don’t understand in basics before I’ll start my target course by Andrew Ng, that will start later this month.</p><p>So, what I learned was actually very productive. I need to step back and start from Calculus. For this I will took classes on Khan Academy. This is my plan for the next studying days.</p><p>Wish me luck ;)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9ffb054ffbbe" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>