<?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 Matvey Arye on Medium]]></title>
        <description><![CDATA[Stories by Matvey Arye on Medium]]></description>
        <link>https://medium.com/@cevian?source=rss-f37f56a99c66------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*AEd8C1LiDPtvxZrEKXg7jw.jpeg</url>
            <title>Stories by Matvey Arye on Medium</title>
            <link>https://medium.com/@cevian?source=rss-f37f56a99c66------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 17:45:19 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@cevian/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[SQL vs. Flux: Choosing the right query language for time-series data]]></title>
            <link>https://medium.com/timescale/sql-vs-flux-influxdb-query-language-time-series-database-290977a01a8a?source=rss-f37f56a99c66------2</link>
            <guid isPermaLink="false">https://medium.com/p/290977a01a8a</guid>
            <category><![CDATA[influxdb]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[query]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Matvey Arye]]></dc:creator>
            <pubDate>Thu, 02 Aug 2018 18:04:36 GMT</pubDate>
            <atom:updated>2018-08-30T16:51:04.756Z</atom:updated>
            <content:encoded><![CDATA[<p><em>An examination of the strengths and weaknesses of SQL, and why query planners exist, by way of comparing TimescaleDB and InfluxDB</em></p><p>Generally in the world of database query languages, there have been two extremes: full SQL support on one end, and completely custom languages (sometimes known as “NoSQL”) on the other.</p><p>With time-series databases, the differences between these languages can be seen by comparing <a href="http://www.timescale.com/?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=sql-vs-flux-post&amp;utm_content=link1"><strong>TimescaleDB</strong></a> and <strong>InfluxDB</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UF_zemLVnvMmksAN5dgZDg.png" /></figure><p>From the beginning, TimescaleDB has firmly existed at the SQL end of the spectrum, fully embracing the language from day 1, and later further extending it to simplify time-series analysis. This has enabled TimescaleDB to require a zero learning curve for new users, and allowed it to inherit the entire SQL ecosystem of 3rd party tools, connectors, and visualization options, which is larger than that of any other time-series database.</p><p>In contrast, InfluxDB began with a “SQL-like” query language (called <a href="https://docs.influxdata.com/influxdb/v1.6/query_language/spec/">InfluxQL</a>), placing it in the middle of the spectrum, and has recently made a marked move towards the “custom” end with its new <a href="https://github.com/influxdata/platform/blob/master/query/docs/SPEC.md">Flux</a> query language. This has allowed InfluxDB to create a new query language that its creators would argue overcomes some SQL shortcomings that they experienced. (Read the <a href="https://www.influxdata.com/blog/why-were-building-flux-a-new-data-scripting-and-query-language/">Flux announcement</a> and the <a href="https://news.ycombinator.com/item?id=17567554">Hacker News reaction</a>.)</p><p>So how do these two query languages compare?</p><p>To analyze the differences between SQL and Flux, let’s first dig into why the InfluxDB team created Flux, as opposed to embracing SQL.</p><p>According to <a href="https://www.influxdata.com/blog/why-were-building-flux-a-new-data-scripting-and-query-language/">this post</a>, InfluxDB created Flux primarily for two reasons:</p><ol><li><strong>Query support: </strong>They concluded SQL could not easily handle the types of queries they wanted to run on time-series data.</li><li><strong>Relational algebra vs. flow-based functional processing:</strong> They believed that a time-series query language needed a flow-based functional model, as opposed to the relational algebra model in SQL.</li></ol><p><em>(There’s also a third reason stated in the post: that SQL was invented in the 1970s. This point is odd, especially considering that other foundational technologies were also born in that same era: the relational database, the microprocessor, and the Internet. These technologies, like SQL, have evolved significantly since their initial invention, while other technologies have not. Also, common modern languages such as English were “invented” hundreds of years ago, and have also evolved over time, while other languages have fallen out of use. So we believe the decade in which something was invented is irrelevant to this discussion. With that in mind, we’ll return to the main discussion.)</em></p><p>Let’s unpack both of these reasons.</p><h3>Query support</h3><p>Here’s the canonical example InfluxDB uses in their post, which computes an <a href="https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average"><em>exponential moving average</em></a> (EMA), to support their claim:</p><p><strong>Flux:</strong></p><pre>from(db:&quot;telegraf&quot;)<br>|&gt; range(start:-1h)<br>|&gt; filter(fn: (r) =&gt; r._measurement == &quot;foo&quot;)<br>|&gt; exponentialMovingAverage(size:-10s)</pre><p>The post authors then struggle to write this query in SQL, and instead produce a fairly complex query that computes a simple moving average:</p><p><strong>SQL 1:</strong></p><pre>select id,<br>       temp,<br>       avg(temp) over (partition by group_nr order by time_read) as rolling_avg<br>from (<br>  select id,<br>         temp,<br>         time_read,<br>         interval_group,<br>         id - row_number() over (partition by interval_group order by time_read) as group_nr<br>  from (<br>     select id,<br>            time_read,<br>            &#39;epoch&#39;::timestamp + &#39;900 seconds&#39;::interval * (extract(epoch from time_read)::int4 / 900) as interval_group,<br>            temp<br>     from readings<br>  ) t1<br>) t2<br>order by time_read;</pre><p>Yes, this SQL example is quite verbose, and only computes part of the desired output (i.e., it only computes a moving average, not an exponential moving average). As a result, their argument is that common time-series related queries (such as computing an EMA) are quite complex in SQL.</p><p>We find this argument incorrect for a key reason. The only way that Flux could support this function is if its creators wrote their own exponentialMovingAverage() function.</p><p>One can similarly write the same function in modern SQL, by creating a custom aggregate. Creating a custom aggregate is supported in most modern SQL implementations, including PostgreSQL. The syntax in PostgreSQL is the following (via <a href="https://stackoverflow.com/questions/8871426/how-to-calculate-an-exponential-moving-average-on-postgres#8879118">StackOverflow</a>):</p><p><strong>SQL: Creating the custom aggregate</strong></p><pre>CREATE OR REPLACE FUNCTION exponential_moving_average_sfunc<br>(state numeric, next_value numeric, alpha numeric)</pre><pre>RETURNS numeric LANGUAGE SQL AS <br>$$</pre><pre>SELECT <br>         CASE <br>                WHEN state IS NULL THEN next_value <br>                ELSE alpha * next_value + (1-alpha) * state <br>         END <br>$$;<br>CREATE AGGREGATE exponential_moving_average(numeric, numeric)<br>(sfunc = exponential_moving_average_sfunc, stype = numeric);</pre><p><em>(This is a simple SQL aggregate window function that computes the exponential moving average. It is an aggregate function because it takes multiple input rows, and is a window function because it computes a value for each input row. By comparison, computing a regular SUM would be an aggregate but not a window function, while comparing the delta between two rows would be a window function but not an aggregate. Yes, SQL is powerful.)</em></p><p>Then querying for the exponential moving average of the entire dataset can be rather simple:</p><p><strong>SQL 2:</strong></p><pre>SELECT time,<br>       exponential_moving_average(value, 0.5) OVER (ORDER BY time)<br>FROM telegraph<br>WHERE measurement = &#39;foo&#39; and time &gt; now() - &#39;1 hour&#39;;</pre><p>This function also allows you to calculate the EMA not just across time, but also by any other column (e.g., “symbol”). In the following example, we calculate the EMA for each different stock symbol over time:</p><p><strong>SQL 3:</strong></p><pre>SELECT date,<br>       symbol,<br>       exponential_moving_average(volume, 0.5) OVER (<br>         PARTITION BY symbol ORDER BY date ASC)<br>FROM telegraph<br>WHERE measurement = &#39;foo&#39; and time &gt; now() - &#39;1 hour&#39;<br>ORDER BY date, symbol;</pre><p>(You can see more at this <a href="http://sqlfiddle.com/#!17/79fd3/1">SQL Fiddle</a>.)</p><p>Yes, writing a new function requires some work, but less work than learning a whole new language, not to mention forcing everyone else (who could just use the same function) to learn that language.</p><p>As developers of a database, we would also argue that writing a new SQL function requires far less work than writing a new query language from scratch. Especially when you consider that even after writing the language, one would still need to write that same specific function (which is what the Flux creators have done).</p><p>Given the large corpus of prior efforts in SQL to perform tasks like calculating an EMA (such as StackOverflow), and given the extensibility of SQL, we would also argue that spending more time in SQL would be a better return on investment than learning or even developing a new query language from scratch, let alone forcing all future database users to also learn that language.</p><h4>What about verbosity?</h4><p>One could argue that the Flux syntax is slightly less verbose than the SQL syntax. But forcing users to learn a new query language so they can save a few keystrokes when writing their queries feels like the wrong trade-off. Programming language complexity is not really about the number of keystrokes one types, but the mental burden to do so: for example, a 1 line program in one language might in fact be more difficult and time-consuming to write (and maintain) than a 5 line program in another language.</p><h4>What about the SQL standard?</h4><p>One could also argue that creating new functions (or aggregates) breaks the SQL standard. Having a standard is valuable for two reasons: code portability, and skills re-use. Here we find that extending SQL is still a superior path than using a new query language. While creating new functions in SQL reduces code portability, it does so far less than adopting a non-standard language. And because the new function syntax is quite similar (if not equivalent) to the standard, it allows database users to leverage their existing knowledge from other systems, which would not be possible with a non-standard language. And SQL’s extensibility allows you to mold the language around your problem domain.</p><h3>Relational algebra vs. flow-based functional processing</h3><p>The other reason InfluxDB cites for developing Flux is that they wanted a language that processed data in a flow-based functional model, versus SQL which is based on relational algebra and sets.</p><p>For example, the Flux creators wanted to easily chain query processing steps in an incremental way. Their argument is that chaining operations and building incremental queries is not easy in SQL. In Flux, this chaining is established by the pipe-forward (“|&gt;“) operator. Flux also allows for arbitrary code to be run in each step of the pipeline (e.g. the filter operator in their example above).</p><p>We find that this argument has some merit, but not without trade-offs.</p><p>First, we agree that writing incremental queries in SQL is not as easy as chaining the output from one function into another (although sub-SELECTs and <a href="https://www.postgresql.org/docs/current/static/queries-with.html">Common Table Expressions</a> make this easier). Updating a query typically requires some re-writing.</p><p>However, this limitation is not arbitrary, but exists for a good reason: a query language that prescribes an ordering of operations inherently weakens the database’s ability to do its job.</p><h4>The power of reordering operations</h4><p>One of the most useful optimizations a database does, via a query planner, is reordering operations to achieve faster query execution. This allows the planner to analyze a query, the data, and any related data structures (e.g., indexes) and reorder query operations to achieve faster execution. But in order to do this, the query planner needs to know that reordering operations won’t change the output.</p><p>In SQL, users tell the database what question they want to answer (without any notion of operations ordering), and let the database execute the optimal path for returning the answer. (In PostgreSQL, users trust the <a href="https://www.slideshare.net/pgconf/the-postgresql-query-planner">query planner</a> to do this.)</p><p>Here’s an example (using our <em>SQL 2</em> from before): <em>I want the exponential weighted moving average of foo over the past hour grouped by 10 second intervals, but I want the database to compute the answer in whatever it determines is the optimal way. </em>To answer this request, the database can decide between using an index for metric=fooand then filter by the last hour or, alternatively, use an index for the last hour and then filter by metric=foo.</p><p>With Flux, users tell the database not only the question they want to answer, but <em>also the order in which to process the data to generate the result</em>. In the Flux example:<em> Select all data in the past hour; then filter it for a given metric; then compute the exponential moving average of that metric grouped by 10 second intervals. </em>This constrains the query planner to first filter data by the first hour (maybe using an index) and then filter by metric.</p><p>Couldn’t the Flux planner still re-order the query? The answer is not always. Flux is designed to be a scripting language, not just a query language, which means that it allows for arbitrary functions (i.e., not only <a href="https://en.wikipedia.org/wiki/Pure_function">pure functions</a>). Arbitrary functions can have <a href="https://en.wikipedia.org/wiki/Side_effect_(computer_science)">side effects</a> that prevent reordering. (As an example, if a function fires events or alerts to another system, it would be incorrect to reorder it, as then the other system would receive a different ordering of events.)</p><p>The difference amounts to this: compared to the SQL relational algebraic model, the flow-based functional model in Flux provides greater control to the user, but at the cost of reducing the power of the database and its query planner.</p><h4>Why query planners exist</h4><p>Let’s take a step back to understand why query planners exist in the first place. As a database becomes more advanced, choosing the optimal execution path to perform a query becomes harder. When features such as joins, multiple indexes, or even index types are introduced, the number of ways a query can be executed also multiplies: e.g., sequential scans, index scans, hash joins, index joins, bitmap scans, etc.</p><p>Given a query of reasonable complexity on a database of reasonable capability, database users will rarely have the expertise to determine which method is best. Furthermore, even if some users have that expertise, they may not have the right context, because the best method often depends on data properties that can change over time (e.g., the number of distinct items vs. available machine memory for choosing an in-memory or spill-to-disk sort method). Pushing these decisions to the planner reduces the burden on developers. (There is a parallel here with software development and compilers. Today, it makes more sense to write clean code and allow the compiler to optimize it, and not optimize by hand in code.)</p><p>This is why enterprise databases since System R in the 1970s have used a declarative language combined with a query planner to push this decision to the database and not the programmer.</p><p>Considering that database query planners have only increased in power and sophistication since then, and that <a href="https://ai.google/research/pubs/pub46518">new machine learning based approaches</a> are being proposed to push the role of the query planner even further, it seems that giving the query planner more power is the right approach. This all becomes significantly harder if one uses a language with ordering features that take the responsibility of query planning away from the database.</p><p>In short, giving the user greater control by adding ordering semantics to a query language may seem tempting. But in return, it also constrains the power of the query planner and thus asks each of your users to become a database expert, or it says that it’s okay to give up on optimizing query latency. Yet the reality is that query latency is likely important, and that most of your database users are likely not database experts because their roles are focused on other areas: e.g., software development, DevOps, business analytics, operations.</p><p>As database designers, we would prefer to create a database one can trust with more responsibility, and let our users focus on their main job. Which is why we believe that the flow-based functional model is the wrong approach for a database query language, including one for a time-series database.</p><h3>Summary: SQL &gt; Flux</h3><p>For most use cases, we believe that SQL is the right query language for a time-series database.</p><p>In some cases, the greater user control of Flux may be preferable, but only if the surface area of possible users is small, and you feel confident that all of your users can be trusted by choosing the optimal query execution path (or query latency is not as important).</p><p>In other words, there are significant trade-offs to adopting a custom query language, particularly one based on a flow-based functional model such as Flux. In particular, merging the query language with a scripting language may grant database users more power, but it does so by reducing the power of the database. If SQL seems inaccessible to functional programmers, we would recommend using many of the ORMs (e.g., Django) or scripting languages (e.g., Python, R) that can sit on top, instead of weakening your database.</p><p>The fact is that new query languages introduce significant overhead and reduce readability. They force a greater learning curve onto new users and possess a scarcity of compatible tools.</p><p>And they may not even be a viable option: rebuilding your system and re-educating your company to write and read a new query language is often not practically possible. Particularly if you are already using SQL-compatible tools on top of the database, e.g., Tableau for visualization.</p><p>Owing to these reasons, one could argue that the choice of query language is the most important criteria for choosing a database: reliability or performance doesn’t matter if the method for writing/accessing data is incompatible with your needs.</p><p>This is also why <a href="https://blog.timescale.com/why-sql-beating-nosql-what-this-means-for-future-of-data-time-series-database-348b777b847a">SQL is making a comeback</a> as the query language of choice for data infrastructure in general.</p><p>If you choose SQL for time-series data, then please consider <a href="http://www.timescale.com/?utm_source=timescale-blog&amp;utm_medium=referral&amp;utm_campaign=sql-vs-flux-post&amp;utm_content=link2"><strong>TimescaleDB</strong></a><strong> </strong>— the only time-series database that supports full SQL.</p><p>The decision is yours, but we urge you to carefully weigh the pros and cons to avoid investing in the wrong choice. <a href="https://www.youtube.com/watch?v=fB_8VCwXydM">After all</a>¹, <a href="https://www.youtube.com/watch?v=NfBmIKvUOdo">some things</a>² <a href="https://www.youtube.com/watch?v=9Vjj6J7gXpY">originally created</a>³ <a href="https://www.youtube.com/watch?v=fJ9rUzIMcZQ">in the 1970s</a>⁴ <a href="https://www.youtube.com/watch?v=yE3yjVjnA8Q">are still</a>⁵ <a href="https://www.youtube.com/watch?v=yHfLyMAHrQE">quite good</a>⁶.</p><p>[1] 1972: <a href="https://www.youtube.com/watch?v=fB_8VCwXydM">The Godfather</a></p><p>[2] 1972: <a href="https://www.youtube.com/watch?v=NfBmIKvUOdo">HBO</a></p><p>[3] 1974: <a href="https://www.youtube.com/watch?v=9Vjj6J7gXpY">Stevie Wonder’s Superstition</a></p><p>[4] 1975: <a href="https://www.youtube.com/watch?v=fJ9rUzIMcZQ">Queen’s Bohemian Rhapsody</a></p><p>[5] 1976: <a href="https://www.youtube.com/watch?v=yE3yjVjnA8Q">Apple Computer</a></p><p>[6] 1977: <a href="https://www.youtube.com/watch?v=yHfLyMAHrQE">Star Wars</a></p><p><em>Like this post? Please recommend and/or share.</em></p><p><em>Want to learn more? Join our </em><a href="http://slack.timescale.com/"><em>Slack community</em></a><em>, follow us </em><a href="https://blog.timescale.com/"><em>here</em></a><em> on Medium, check out our </em><a href="https://github.com/timescale/timescaledb"><em>GitHub</em></a><em>, and sign up for the community mailing below.</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Fc241e3%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2Fc241e3%2F&amp;image=https%3A%2F%2Fucarecdn.com%2Fa96120e2-4111-481d-bf89-0ec8cf514c6a%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/3fa9da3aa0b2321ee1227a8d659c86bc/href">https://medium.com/media/3fa9da3aa0b2321ee1227a8d659c86bc/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=290977a01a8a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/timescale/sql-vs-flux-influxdb-query-language-time-series-database-290977a01a8a">SQL vs. Flux: Choosing the right query language for time-series data</a> was originally published in <a href="https://medium.com/timescale">Timescale</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing queries on TimescaleDB hypertables with thousands of partitions]]></title>
            <link>https://medium.com/timescale/optimizing-queries-timescaledb-hypertables-with-partitions-postgresql-6366873a995d?source=rss-f37f56a99c66------2</link>
            <guid isPermaLink="false">https://medium.com/p/6366873a995d</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[software-development]]></category>
            <dc:creator><![CDATA[Matvey Arye]]></dc:creator>
            <pubDate>Wed, 27 Jun 2018 19:15:27 GMT</pubDate>
            <atom:updated>2018-08-31T04:05:17.174Z</atom:updated>
            <content:encoded><![CDATA[<p>A few months ago, we wrote about how <a href="https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5">Postgres 10 insert performance degraded</a> when a table had many partitions (<a href="http://docs.timescale.com/v0.9/introduction/architecture#chunks">chunks</a> in <a href="http://docs.timescale.com/v0.9/introduction/architecture#overview">TimescaleDB parlance</a>) and how TimescaleDB does not suffer from the same degradation.</p><p><a href="https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5">Problems with PostgreSQL 10 for time-series data</a></p><p>Similar to the insert performance problem in Postgres 10, there is a problem in Postgres when executing a SELECT on tables with many partitions.</p><p>This problem presented itself in TimescaleDB during high query planning times on queries over <a href="http://docs.timescale.com/v0.9/introduction/architecture#hypertables">hypertables</a> with many chunks. In our test queries, hypertables with 4000 chunks spent 600ms just for planning this query. In traditional OLTP workloads, 4000 partitions is unrealistic. However, because of the nature of time-series workloads, this is not an uncommon scenario. <a href="https://blog.timescale.com/time-series-data-why-and-how-to-use-a-relational-database-instead-of-nosql-d0cd6975e87c">Data is continually inserted over time and chunks are continually created</a> to cover new time periods. These hypertables can get quite large: we have customers with tens of thousands of chunks!</p><p>Crucially, query planning slowdown happened even when the query itself did not have to process many chunks. For example, if the dataset included a year’s worth of data but the query specified one <em>specific</em> week, then the planner conceptually could exclude most of the chunks during the planning process (e.g., using <a href="https://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION">constraint_exclusion</a>). One would hope that performance should be a function of just this one week, not the entire year. Yet in fact, this planning overhead is a function of the <em>entire</em> dataset<em> </em>size, and is paid for every query on the hypertable.</p><p>Having looked at this process for inserts previously, we had a hunch that the slowdown was caused by the planner opening every single chunk belonging to a hypertable. The “open” operation also takes a lock on the table to prevent concurrent commands from changing the schema of the table in the middle of the execution of select queries. But when we actually profiled the planner, we were surprised to learn that the culprit was in a different spot.</p><p>Below is a flamegraph of the profile we saw (please see the caption for how to read this type of graph):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZG-puIDkJzmVl2qmaWGKmA.png" /><figcaption><em>Profile of a simple SELECT query on a hypertable with 1000 chunks under concurrency. In these types of graphs, time is shown on the x-axis from left-to-right, with calls made within a function “stacked” above the function call. So we see that </em>get_relation_info()<em> takes roughly two-thirds of the total planning execution time, with itself making subcalls to RelationGetNumberOfBlocks() and estimate_rel_size().</em></figcaption></figure><p>The flamegraph shows that most of the time spent in the profile was under the get_relation_info()function. After some more digging, we realized that this function fetches the statistics for the table. Part of those statistics include fetching an approximate row count for the table. This, in turn, is achieved by opening the data file for the heap of the table and reading metadata located inside this file. And this is in fact the problem. Each query to a hypertable has to open and read metadata from the heap data files for <em>all</em> of these chunks. This seems redundant and is a waste of time if the chunks can be excluded from the query altogether, since chunk exclusion does not require any table statistics at all!</p><p>Our optimization of this process, as seen in TimescaleDB <a href="https://github.com/timescale/timescaledb/pull/502">PR #502</a>, moves the chunk exclusion to before chunks are opened and statistics fetched. Thus, it prevents all of the unnecessary work of getting statistics for chunks that will never be used.</p><p>As part of the update, we also changed the exclusion mechanism from PostgreSQL’s native constraint exclusion to our own take on <em>range exclusion, </em>inspired by Amit Langote’s <a href="https://commitfest.postgresql.org/17/1272/">Faster Partition Pruning PR</a> to Postgres 11. Our change makes exclusion more efficient in finding the chunks that match and more effective in being able to exclude chunks based on more types of WHERE clauses.</p><p>From our tests, we found significant reduction in planning times resulting from these changes. For example, our test dataset with 4000+ chunks, <strong>dramatically decreased planning times from 600ms to 36ms</strong>,<strong> </strong>around a 15x improvement.</p><p>And on hypertables with only a few chunks, there was no corresponding additional cost to these changes. In fact, with 6 chunks, we saw the modest improvement of planning times from 6.6ms to 5.9ms.</p><p>These new planning optimizations seemed like a “no brainer” to include in TimescaleDB and we’re happy to announce that <a href="https://github.com/timescale/timescaledb/releases/tag/0.10.0">version 0.10.0, being released today</a>, will include these performance improvements.</p><p>Overall, this is just one example of our mission to bring scalable time-series storage and analytics to PostgreSQL.</p><p><em>Like this post and interested in learning more?</em></p><p><em>Follow us </em><a href="https://blog.timescale.com/"><em>here</em></a><em> on Medium, check out our </em><a href="https://github.com/timescale/timescaledb"><strong><em>GitHub</em></strong></a><em>, join our </em><a href="http://slack.timescale.com/"><strong><em>Slack community</em></strong></a><em>, and sign up for the community mailing list below. We’re also </em><a href="https://www.timescale.com/careers"><em>hiring</em></a><em>!</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fupscri.be%2Ff8fee5%3Fas_embed%3Dtrue&amp;dntp=1&amp;url=https%3A%2F%2Fupscri.be%2Ff8fee5%2F&amp;image=https%3A%2F%2Fucarecdn.com%2Fe29295de-a193-46a3-b892-65f57e7de550%2F&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=upscri" width="800" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/916e1ece49aa9258a984a4328acd381d/href">https://medium.com/media/916e1ece49aa9258a984a4328acd381d/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6366873a995d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/timescale/optimizing-queries-timescaledb-hypertables-with-partitions-postgresql-6366873a995d">Optimizing queries on TimescaleDB hypertables with thousands of partitions</a> was originally published in <a href="https://medium.com/timescale">Timescale</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 scale PostgreSQL 10 using table inheritance and declarative partitioning]]></title>
            <link>https://medium.com/timescale/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1?source=rss-f37f56a99c66------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd48a712a9a1</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Matvey Arye]]></dc:creator>
            <pubDate>Thu, 09 Nov 2017 03:55:11 GMT</pubDate>
            <atom:updated>2017-11-09T15:55:36.556Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*TVa9v8BVGYj0Y6OWxloG6w.jpeg" /><figcaption>Partitioning PostgreSQL</figcaption></figure><p><a href="https://www.postgresql.org/about/news/1786/">PostgreSQL 10 is now out in the wild</a> <em>(edit: </em><a href="https://www.postgresql.org/docs/10/static/release-10-1.html"><em>10.1 was released</em></a><em> right after we published)</em>. This is exciting for many reasons. PG10 introduces quite a few <a href="https://wiki.postgresql.org/wiki/New_in_postgres_10">substantial new features</a>: logical replication, full text search support for JSON / JSONB, cross-column statistics, and better support for parallel queries. Overall, PG10 is a significant milestone in the steady development of a venerable open-source database.</p><p>One of the more exciting new features in PG10 is declarative partitioning. This essentially makes it easier to scale PostgreSQL to “big data” volumes.</p><p><strong>Making PostgreSQL scalable is critical to its future success.</strong> Developers, over the years, have complained that PostgreSQL is too difficult to scale. Consequently popular opinion shifted towards “NoSQL” solutions that promised scalability, and they were adopted enthusiastically. Yet these NoSQL options often sacrificed reliability, performance, and ease of use (<a href="https://blog.timescale.com/why-sql-beating-nosql-what-this-means-for-future-of-data-time-series-database-348b777b847a">which is one reason why developers lately have been returning to SQL</a>).</p><p>With declarative partitioning, PG10 lays the groundwork to compete with these other technologies. One can think of declarative partitioning as the first step for making all that PostgreSQL has to offer — <strong>rock-solid reliability, powerful complex queries, a vast ecosystem of tools and connectors </strong>— finally available for more big data applications. And as the developers of <a href="http://www.timescale.com/">a new time-series database built on top of PostgreSQL</a>, we are hugely excited by the direction that Postgres is pursuing.</p><p>To be clear, partitioning in general is not new in PostgreSQL 10. For years, many have utilized table inheritance for scaling PostgreSQL tables. Declarative partitioning is just an additional, intriguing option.</p><p>This post examines these two options for partitioning in PostgreSQL.</p><p><strong>In particular, this post covers:</strong></p><ul><li>Why PostgreSQL doesn’t naturally scale well (why we need to partition our data in the first place)</li><li>How partitioning via table inheritance works (the primary option pre- PostgreSQL 10)</li><li>How declarative partitioning in PostgreSQL 10 works</li><li>Limitations of the new declarative partitioning</li></ul><p>Our experience has been that developers come to PostgreSQL with a wide variety of expertise, so this post starts with some fundamentals and then delves deeper into the details. Of course, depending on your own level of expertise, feel free to skip ahead to the first section relevant to you.</p><p><em>(Note: Declarative partitioning is only the first step in a new direction for PostgreSQL partitioning. Future improvements will build upon this foundation. This post, however, will only consider the PG10 state-of-the-art, as opposed to future plans.)</em></p><h3>Why doesn’t PostgreSQL naturally scale well?</h3><p>It comes down to the performance gap between RAM and disk.</p><p>In most relational databases, a table is stored as an unordered collection of fixed-size pages of data (e.g., 8KB pages in PostgreSQL), on top of which the system builds data structures (e.g., heaps) to index the data. With an index, a query can quickly find a row with a specified ID (e.g., bank account number) without scanning or “walking” the entire table in some sorted order.</p><p>But if the data is sufficiently large that we can’t fit all (similarly fixed-size) pages of our index in memory, then updating a random part of the tree can involve significant disk I/O as we read pages from disk into memory, modify in memory, and then write back out to disk (when evicted to make room for other pages). And a relational database like PostgreSQL keeps an index (or other data structure, such as a <a href="https://en.wikipedia.org/wiki/B-tree">B-tree</a>) for each table index, in order for values in that index to be found efficiently. So, the problem compounds as you index more columns.</p><p>The cost of swapping in and out of memory can be seen in the following performance graph from PostgreSQL, where insert throughput plunges with table size and increases in variance (depending on whether requests hit in memory or require (potentially multiple) fetches from disk).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JcO1CShQUAh-xmUeKGHYXA.gif" /></figure><p><em>Insert throughput as a function of table size for PostgreSQL 9.6.2, running with 10 workers on an Azure standard DS4 v2 (8 core) machine with SSD-based (premium LRS) storage. Clients insert individual rows into the database (each of which has 12 columns: a timestamp, an indexed randomly-chosen primary id, and 10 additional numerical metrics). The PostgreSQL rate starts over 15K inserts/second, but then begins to drop significantly after 50M rows and experiences very high variance (including periods of only 100s of inserts/sec).</em></p><p>(A benchmark showing vanilla PostgreSQL’s insert performance out to 1 billion rows, with 10K row batch inserts can be seen <a href="https://blog.timescale.com/timescaledb-vs-6a696248104e">here</a>.)</p><h3>The solution: Partitioning</h3><p>Table partitioning helps to avoid this expensive swap-to-disk problem. By splitting a large table into smaller partitions, the resulting sub-tables’ indexes (if done correctly) will be kept in memory during insert.</p><p>This results in several benefits, including:</p><ol><li>Improved inserts and queries from being able to fit all indexes in memory.</li><li>Improved queries from being able to use <a href="https://www.postgresql.org/docs/current/static/ddl-partitioning.html#ddl-partitioning-constraint-exclusion">constraint exclusion</a>.</li><li>Having a smaller dataset (per partition) may enable the database to use sequential scans efficiently.</li><li>Maintenance operations like VACUUM and REINDEX run faster because of smaller indexes.</li><li>Deleting entire partitions is much faster, as it only requires a DROP TABLE and avoids expensive vacuuming operations.</li></ol><p>The exact point at which partitioning should be considered instead of a large table depends on the workload and machine resources. For example, the graph above shows PostgreSQL’s performance degrading after 50M rows for the specified workload and machine specifications. Adding more memory is kicking the proverbial can down the road. <strong>In general, if you are writing a lot of data to a PostgreSQL table, at some point you’ll need partitioning.</strong></p><h3>Partitioning via table inheritance</h3><p>Before PostgreSQL 10, table partitioning was only possible via <a href="https://www.postgresql.org/docs/current/static/ddl-inherit.html">table inheritance</a>. Even with PG10, table inheritance is still a viable option for partitioning in situations where declarative partitioning falls short.</p><p>To partition using table inheritance, users first create a parent table, and then create a child table for each partition, using the INHERITS clause of the CREATE TABLE statement (which creates an inheritance link). When querying a parent table, data from both the parent and child tables is returned.</p><p>More specifically, to implement table inheritance users need to :</p><ol><li>Create the “parent” table, from which all of the partitions will inherit.</li><li>Create several “child” tables (each representing a partition of the data) that each inherit from the parent.</li><li>Add constraints to the partition tables to define the row values in each partition.</li><li>Create indexes on any parent and child tables individually. (Indexes do not propagate from the parent tables to child tables).</li><li>Write a suitable trigger function to the master table so that inserts into the parent table redirect into the appropriate partition table.</li><li>Create a trigger that calls the trigger function.</li><li>Remember to redefine the trigger function when the set of child tables changes.</li></ol><p>Table inheritance for a long time has given developers a viable way to partition unwieldy tables in PostgreSQL. It also provides useful flexibility: for example, child tables are permitted to have extra columns not present in the parent table and data can be divided in a manner of the user’s choosing.</p><p>But, as the above steps indicate, table inheritance requires a non-trivial amount of manual work. And while scripts can be used to automate some steps, those would still require upkeep and increase engineering overhead.</p><h4>Table inheritance also comes with some limitations:</h4><ol><li>Data consistency depends on the CHECK constraints specified on each child table.</li><li>INSERT and COPY commands do not automatically propagate data to other child tables in the inheritance hierarchy, but instead rely on triggers, resulting in slower inserts.</li><li>Substantial manual work is required to create and maintain child sub-tables.</li><li>Given <strong>3</strong>, re-partitioning is non-trivial (e.g., when adding disks, making schema changes).</li><li>Ensuring uniqueness across partitioned tables is not supported.</li><li>Indexes, constraints, and many table maintenance commands need to be applied to child tables explicitly. This greatly complicates management tasks.</li></ol><p><strong>To overcome these limitations and the manual work required, declarative partitioning was introduced in PostgreSQL 10.</strong></p><p>PostgreSQL 10 declarative partitioning solves issues <strong>1</strong> and <strong>2</strong> above. It also simplifies issue <strong>3</strong>, but significant manual work and limitations still remain.</p><h3>Declarative partitioning in PostgreSQL 10</h3><p>Declarative table partitioning reduces the amount of work required to partition data in PostgreSQL. It achieves this by providing an improved syntax to create partitioned tables and their child table partitions.</p><p>Currently, declarative partitioning supports RANGE and LIST partitions:</p><ul><li>RANGE — partition the table into “ranges” defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example, <strong>device_id</strong>.</li><li>LIST— partition the table by explicitly listing which key values appear in each partition, and are limited to a single column. For example, <strong>device_type</strong>.</li></ul><h3>How does it work?</h3><p>To start, we create a partitioned table:</p><ul><li>Create a partitioned table using the PARTITION BY clause, which includes the partitioning method (RANGE in this example) and the list of column(s) to use as the partition key (examples are directly from the <a href="https://www.postgresql.org/docs/10/static/ddl-partitioning.html">PostgreSQL 10 documentation</a>):</li></ul><pre>CREATE TABLE measurement (<br> city_id int not null,<br> logdate date not null,<br> peaktemp int,<br> unitsales int<br>) PARTITION BY RANGE (logdate);</pre><ul><li>After creating the partitioned table, partitions can be manually created:</li></ul><pre>CREATE TABLE measurement_y2006m02 PARTITION OF measurement<br> FOR VALUES FROM (‘2006–02–01’) TO (‘2006–03–01’)</pre><pre>CREATE TABLE measurement_y2006m03 PARTITION OF measurement<br> FOR VALUES FROM (‘2006–03–01’) TO (‘2006–04–01’)</pre><pre>CREATE TABLE measurement_y2006m04 PARTITION OF measurement<br> FOR VALUES FROM (‘2006–04–01’) TO (‘2006–05–01’)<br>...<br>CREATE TABLE measurement_y2007m11 PARTITION OF measurement<br> FOR VALUES FROM (‘2007–11–01’) TO (‘2007–12–01’)</pre><ul><li>And then indexes can be created:</li></ul><pre>CREATE INDEX ON measurement_y2006m02 (logdate);<br>CREATE INDEX ON measurement_y2006m03 (logdate);<br>CREATE INDEX ON measurement_y2006m04 (logdate);<br>...<br>CREATE INDEX ON measurement_y2007m11 (logdate);</pre><p>The partitioned table functions similarly to the parent or “master” table in table inheritance but is much improved, serving as a much more powerful query interface to the underlying child table partitions. For instance, commands such as TRUNCATE and COPY now propagate to child tables via execution on the partitioned table. Additionally, users can insert data into underlying child tables via the partitioned table, since tuples (i.e., rows) now automatically route to the right partition on INSERT and no longer depend on triggers, as is the case with table inheritance.</p><p><strong>As can be seen, declarative partitioning drastically simplifies the 8-step process of table inheritance.</strong> The 3-step process is less manual and more straightforward for the user. The automation of inserts and additional propagation of table commands via the partitioned table are also big wins.</p><p>Declarative partitioning is just the first step in a new approach to partitioning for PostgreSQL. As far as we understand, additional constraints on partitioning — such as the fact that the parent table itself cannot hold data — introduced by declarative partitioning (as opposed to table inheritance) will allow PostgreSQL to build in more powerful query optimization and other features in the future. In other words, in PG10 declarative partitioning is mostly about an improved interface and syntax for defining partitions, but future versions should be able to use this structure to implement more advanced features and optimizations.</p><h3>Limitations of declarative partitioning</h3><p>Undoubtedly, declarative partitioning is a significant step toward the automation of partitioning in PostgreSQL. That said, there are still several limitations.</p><h4>Manual partition management</h4><p>Declarative partitioning makes partition management easier, but still requires a fair amount of manual work.</p><p>For example, child tables for the data need to exist before the data is inserted. This can become a management challenge for when data evolves. For example, if partitioning by time, it would be nice if the system would auto-create new partitions as new data comes in. Currently this has to be done manually by the database administrator. At the same-time, pre-creating many partitions may negatively affect performance.</p><p>Some of other the other limitations requiring manual management are listed in the <a href="https://www.postgresql.org/docs/10/static/ddl-partitioning.html">documentation</a> (section 5.10.2.3), for example:</p><ul><li>Cannot create indexes on all partitions automatically. Indexes still need to be manually created on each partition.</li><li>Updates that would move a row from one partition to another will fail.</li><li>Row triggers must be defined on individual partitions.</li></ul><p>These limitations mean that when creating tables, a user still needs to manually assign tablespaces and create all indexes and constraints, as appropriate. Similarly, when performing changes to the parent table, all changes to the indexes and constraints need to be manually applied to every partition. Also, many commands like CLUSTER and REINDEX need to be applied to individual child tables.</p><p>To summarize, the management of individual partitions in PG10 is still not yet done exclusively through the partitioned table interface. These additional operational requirements have the potential to create data management bottlenecks, especially as more and more partitions are created, and as changes to the data model occur.</p><h4>Multi-dimensional partitioning is cumbersome</h4><p>In the case of multi-dimensional partitioning — important for scalability and additional query improvements — sub-partitions must be manually created on existing partitions after the first partition is created. As an example, partitioning on one LIST (e.g., device) and one RANGE (e.g., time) dimension first requires creating all LIST partitions and then, in each of those child tables, creating corresponding RANGE sub-partitions, or vice versa. This creates a tree hierarchy of tables where the leafs are the effective sub-partitions that hold tuples.</p><pre>-- Create device partitions<br>CREATE TABLE conditions_p1 PARTITION OF conditions<br>      FOR VALUES FROM (MINVALUE) TO (&#39;g&#39;)<br>      PARTITION BY RANGE (time);<br>CREATE TABLE conditions_p2 PARTITION OF conditions<br>      FOR VALUES FROM (&#39;g&#39;) TO (&#39;n&#39;)<br>      PARTITION BY RANGE (time);<br>CREATE TABLE conditions_p3 PARTITION OF conditions<br>      FOR VALUES FROM (&#39;n&#39;) TO (&#39;t&#39;)<br>      PARTITION BY RANGE (time);<br>CREATE TABLE conditions_p4 PARTITION OF conditions<br>      FOR VALUES FROM (&#39;t&#39;) TO (MAXVALUE)<br>      PARTITION BY RANGE (time);</pre><pre>-- Create time partitions for the first week in each device partition<br>CREATE TABLE conditions_p1_y2017m10w01 PARTITION OF conditions_p1<br>      FOR VALUES FROM (&#39;2017-10-01&#39;) TO (&#39;2017-10-07&#39;);<br>CREATE TABLE conditions_p2_y2017m10w01 PARTITION OF conditions_p2<br>      FOR VALUES FROM (&#39;2017-10-01&#39;) TO (&#39;2017-10-07&#39;);<br>CREATE TABLE conditions_p3_y2017m10w01 PARTITION OF conditions_p3<br>      FOR VALUES FROM (&#39;2017-10-01&#39;) TO (&#39;2017-10-07&#39;);<br>CREATE TABLE conditions_p4_y2017m10w01 PARTITION OF conditions_p4<br>      FOR VALUES FROM (&#39;2017-10-01&#39;) TO (&#39;2017-10-07&#39;);</pre><pre>-- Create time-device index on each leaf partition<br>CREATE INDEX ON conditions_p1_y2017m10w01 (time);<br>CREATE INDEX ON conditions_p2_y2017m10w01 (time);<br>CREATE INDEX ON conditions_p3_y2017m10w01 (time);<br>CREATE INDEX ON conditions_p4_y2017m10w01 (time);</pre><pre>INSERT INTO conditions VALUES (&#39;2017-10-03 10:23:54+01&#39;, 73.4, 40.7, &#39;sensor3&#39;);</pre><p>This multi-step process and the additional dimensions / sub-levels of partitions further complicate things and unnecessarily large inheritance trees can negatively impact performance and scalability as more tables need to be processed for both inserts and queries.</p><h4>Unsupported data-integrity features</h4><p>Some data integrity features commonly used in Postgres are not supported in PG 10, as described in <a href="https://www.postgresql.org/docs/10/static/ddl-partitioning.html">documentation</a> (same section 5.10.2.3):</p><ul><li>Cannot create primary keys on partitions: meaning that foreign keys referencing partitioned tables are not supported, nor are foreign key references from a partitioned table to another table.</li><li>No support for enforcing uniqueness (or an exclusion constraint) across an entire partitioned table. Unique or exclusion constraints can only be created on individual partitions. Thus, ON CONFLICT clauses are also unsupported (<a href="http://rhaas.blogspot.se/2017/08/plans-for-partitioning-in-v11.html">but is apparently slated for PG11</a>).</li></ul><p>We have seen that many IoT and time-series applications make use of upserts (ON CONFLICT clauses) so this may be a significant hurdle for these application domains. The lack of primary keys may also be an issue for users using ORMs that assume the existence of primary keys.</p><h3>Parting thoughts</h3><p>Declarative partitioning is an important step in the drive to make PostgreSQL scale for large data sets. In PG10, it introduces a simpler syntax for defining partitions and enables tuple routing. Both of these steps make setting up partitioning easier and will be beneficial for many. <strong>However, from what we see, the most exciting results from this ongoing work are query optimizations yet to come</strong> (slated for PG11 and later). Significant opportunities also remain in improving management and ease of use.</p><p>But parallel to the continued core development of PostgreSQL (and thanks to the fact that Postgres is so extensible), the state of partition management and ease of use can be accelerated by extensions, such as <a href="https://github.com/timescale/timescaledb">TimescaleDB</a>.</p><p>These extensions allow PostgreSQL to be used effectively for more diverse workloads than traditional OLTP, such as the <a href="https://blog.timescale.com/what-the-heck-is-time-series-data-and-why-do-i-need-a-time-series-database-dcf3b1b18563">exponentially growing amount of time-series data in our world</a>.</p><p>If you’d like to learn more about how PG10 declarative partitioning works for time-series data (and how our partitioning approach differs), please check out this recent post: <a href="https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5"><strong>Problems with PostgreSQL 10 for time-series data</strong></a>.</p><p><em>Like this post? Please recommend and/or share.</em></p><p><em>And if you’d like to learn more about TimescaleDB, please check out our </em><a href="https://github.com/timescale/timescaledb"><strong><em>GitHub</em></strong></a><em> (stars always appreciated), and please </em><a href="/service/mailto:hello@timescale.com"><em>let us know</em></a><em> how we can help.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd48a712a9a1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/timescale/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1">How to scale PostgreSQL 10 using table inheritance and declarative partitioning</a> was originally published in <a href="https://medium.com/timescale">Timescale</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>