{"id":89498,"date":"2020-07-23T13:42:07","date_gmt":"2020-07-23T11:42:07","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql"},"modified":"2020-07-23T13:42:07","modified_gmt":"2020-07-23T11:42:07","slug":"testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","title":{"rendered":"Performance testing of analytical queries in PostgreSQL, ClickHouse, and clickhousedb_fdw (PostgreSQL)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>In this study, I wanted to see what performance improvements can be achieved by using ClickHouse as a data source instead of PostgreSQL. I know the performance advantages I gain from using ClickHouse. Will these advantages be preserved if I access ClickHouse from PostgreSQL via a foreign data wrapper (FDW)? <\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>The database environments being examined are PostgreSQL v11, clickhousedb_fdw, and the ClickHouse database. Ultimately, we will run various SQL queries from PostgreSQL v11 routed through our clickhousedb_fdw to the ClickHouse database. We will then see how the performance of FDW compares to the same queries executed in native PostgreSQL and native ClickHouse.<\/p>\n<p><\/p>\n<h3 id=\"baza-dannyh-clickhouse\">Clickhouse database<\/h3>\n<p><\/p>\n<p>ClickHouse is an open-source, columnar database management system that can achieve performance 100-1000 times faster than traditional database approaches, capable of processing over a billion rows in less than a second.<\/p>\n<p><\/p>\n<h3 id=\"clickhousedb_fdw\">Clickhousedb_fdw<\/h3>\n<p><\/p>\n<p>clickhousedb_fdw, the foreign data wrapper for ClickHouse, is an open-source project by Percona. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/clickhousedb_fdw\">Here is a link to the GitHub project repository<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/www.percona.com\/blog\/2019\/03\/29\/postgresql-access-clickhouse-one-of-the-fastest-column-dbmss-with-clickhousedb_fdw\/\">In March, I wrote a blog that tells you more about our FDW<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>As you will see, it provides an FDW for ClickHouse that allows SELECT from and INSERT INTO the ClickHouse database from a PostgreSQL v11 server.<\/p>\n<p><\/p>\n<p>The FDW supports advanced features such as aggregate and join. This significantly improves performance by utilizing the resources of the remote server for these resource-intensive operations.<\/p>\n<p><\/p>\n<h3 id=\"benchmark-environment\">Benchmark environment<\/h3>\n<p><\/p>\n<ul>\n<li>Supermicro server:\n<ul>\n<li>Intel\u00ae Xeon\u00ae CPU E5-2683 v3 @ 2.00GHz<\/li>\n<li>2 sockets \/ 28 cores \/ 56 threads<\/li>\n<li>Memory: 256GB of RAM<\/li>\n<li>Storage: Samsung SM863 1.9TB Enterprise SSD<\/li>\n<li>Filesystem: ext4\/xfs<\/li>\n<\/ul>\n<\/li>\n<li>OS: Linux smblade01 4.15.0-42-generic #45~16.04.1-Ubuntu<\/li>\n<li>PostgreSQL: version 11<\/li>\n<\/ul>\n<p><\/p>\n<h3 id=\"benchmark-tests\">Benchmark tests<\/h3>\n<p><\/p>\n<p>Instead of using some machine-generated dataset for this test, we used data from \"Performance over time, reporting operator run time\" from 1987 to 2018. You can access the data <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Percona-Lab\/ontime-airline-performance\/blob\/master\/download.sh\">with our script available here<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>The database size is 85 GB, providing one table with 109 columns.<\/p>\n<p><\/p>\n<h4 id=\"benchmark-queries\">Benchmark Queries<\/h4>\n<p><\/p>\n<p>Here are the queries I used to compare ClickHouse, clickhousedb_fdw, and PostgreSQL.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>Query Contains Aggregates and Group By<\/strong><\/p>\n<p>Q1<br \/>\nSELECT DayOfWeek, count(*) AS c FROM ontime WHERE Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY DayOfWeek ORDER BY c DESC;<\/p>\n<p>Q2<br \/>\nSELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay&gt;10 AND Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY DayOfWeek ORDER BY c DESC;<\/p>\n<p>Q3<br \/>\nSELECT Origin, count(*) AS c FROM ontime WHERE DepDelay&gt;10 AND Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10;<\/p>\n<p>Q4<br \/>\nSELECT Carrier, count(<em>) FROM ontime WHERE DepDelay&gt;10 AND Year = 2007 GROUP BY Carrier ORDER BY count(<\/em>) DESC;<\/p>\n<p>Q5<br \/>\nSELECT a.Carrier, c, c2, c<em>1000\/c2 as c3 FROM ( SELECT Carrier, count(<\/em>) AS c FROM ontime WHERE DepDelay&gt;10 AND Year=2007 GROUP BY Carrier ) a INNER JOIN ( SELECT Carrier,count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY Carrier)b on a.Carrier=b.Carrier ORDER BY c3 DESC;<\/p>\n<p>Q6<br \/>\nSELECT a.Carrier, c, c2, c<em>1000\/c2 as c3 FROM ( SELECT Carrier, count(<\/em>) AS c FROM ontime WHERE DepDelay&gt;10 AND Year &gt;= 2000 AND Year = 2000 AND Year &lt;= 2008 GROUP BY Carrier ) b on a.Carrier=b.Carrier ORDER BY c3 DESC;<\/p>\n<p>Q7<br \/>\nSELECT Carrier, avg(DepDelay) * 1000 AS c3 FROM ontime WHERE Year &gt;= 2000 AND Year &lt;= 2008 GROUP BY Carrier;<\/p>\n<p>Q8<br \/>\nSELECT Year, avg(DepDelay) FROM ontime GROUP BY Year;<\/p>\n<p>Q9<br \/>\nselect Year, count(*) as c1 from ontime group by Year;<\/p>\n<p>Q10<br \/>\nSELECT avg(cnt) FROM (SELECT Year,Month,count(*) AS cnt FROM ontime WHERE DepDel15=1 GROUP BY Year,Month) a;<\/p>\n<p>Q11<br \/>\nselect avg(c1) from (select Year,Month,count(*) as c1 from ontime group by Year,Month) a;<\/p>\n<p>Q12<br \/>\nSELECT OriginCityName, DestCityName, count(*) AS c FROM ontime GROUP BY OriginCityName, DestCityName ORDER BY c DESC LIMIT 10;<\/p>\n<p>Q13<br \/>\nSELECT OriginCityName, count(*) AS c FROM ontime GROUP BY OriginCityName ORDER BY c DESC LIMIT 10;<\/p>\n<p><strong>Query Contains Joins<\/strong><\/p>\n<p>Q14<br \/>\nSELECT a.Year, c1\/c2 FROM ( select Year, count(<em>)<\/em>1000 as c1 from ontime WHERE DepDelay&gt;10 GROUP BY Year) a INNER JOIN (select Year, count(*) as c2 from ontime GROUP BY Year ) b on a.Year=b.Year ORDER BY a.Year;<\/p>\n<p>Q15<br \/>\nSELECT a.\u201dYear\u201d, c1\/c2 FROM ( select \u201cYear\u201d, count(<em>)<\/em>1000 as c1 FROM fontime WHERE \u201cDepDelay\u201d&gt;10 GROUP BY \u201cYear\u201d) a INNER JOIN (select \u201cYear\u201d, count(*) as c2 FROM fontime GROUP BY \u201cYear\u201d ) b on a.\u201dYear\u201d=b.\u201dYear;<\/p>\n<p><\/p>\n<p><em>Table-1: Queries used in benchmark<\/em><\/p>\n<p><\/p>\n<h4 id=\"query-executions\">Query executions<\/h4>\n<p><\/p>\n<p>Here are the results of each query executed under different database settings: PostgreSQL with and without indexes, custom ClickHouse and clickhousedb_fdw. Time is displayed in milliseconds.<\/p>\n<p><\/p>\n<p><strong>Q#<\/strong><br \/>\n<strong>PostgreSQL<\/strong><br \/>\n<strong>PostgreSQL (Indexed)<\/strong><br \/>\n<strong>ClickHouse<\/strong><br \/>\n<strong>clickhousedb_fdw<\/strong><\/p>\n<p>Q1<br \/>\n27920<br \/>\n19634<br \/>\n23<br \/>\n57<\/p>\n<p>Q2<br \/>\n35124<br \/>\n17301<br \/>\n50<br \/>\n80<\/p>\n<p>Q3<br \/>\n34046<br \/>\n15618<br \/>\n67<br \/>\n115<\/p>\n<p>Q4<br \/>\n31632<br \/>\n7667<br \/>\n25<br \/>\n37<\/p>\n<p>Q5<br \/>\n47220<br \/>\n8976<br \/>\n27<br \/>\n60<\/p>\n<p>Q6<br \/>\n58233<br \/>\n24368<br \/>\n55<br \/>\n153<\/p>\n<p>Q7<br \/>\n30566<br \/>\n13256<br \/>\n52<br \/>\n91<\/p>\n<p>Q8<br \/>\n38309<br \/>\n60511<br \/>\n112<br \/>\n179<\/p>\n<p>Q9<br \/>\n20674<br \/>\n37979<br \/>\n31<br \/>\n81<\/p>\n<p>Q10<br \/>\n34990<br \/>\n20102<br \/>\n56<br \/>\n148<\/p>\n<p>Q11<br \/>\n30489<br \/>\n51658<br \/>\n37<br \/>\n155<\/p>\n<p>Q12<br \/>\n39357<br \/>\n33742<br \/>\n186<br \/>\n1333<\/p>\n<p>Q13<br \/>\n29912<br \/>\n30709<br \/>\n101<br \/>\n384<\/p>\n<p>Q14<br \/>\n54126<br \/>\n39913<br \/>\n124<br \/>\n1364212<\/p>\n<p>Q15<br \/>\n97258<br \/>\n30211<br \/>\n245<br \/>\n259<\/p>\n<p><\/p>\n<p><em>Table-1: Time taken to execute the queries used in benchmark<\/em><\/p>\n<p><\/p>\n<p>View Results<\/p>\n<p><\/p>\n<p>The graph shows the execution time of the query in milliseconds; the X-axis represents the query number from the tables above, while the Y-axis shows the execution time in milliseconds. Results from ClickHouse and data obtained from PostgreSQL using clickhousedb_fdw are displayed. The table shows that there is a huge difference between PostgreSQL and ClickHouse, but minimal difference between ClickHouse and clickhousedb_fdw.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Performance testing of analytical queries in PostgreSQL, ClickHouse, and clickhousedb_fdw (PostgreSQL)\" src=\"\/wp-content\/uploads\/2020\/07\/e084243ea7b327f30de5cb78339d3d3a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>This graph illustrates the difference between ClickHouseDB and clickhousedb_fdw. In most queries, the overhead of FDW is not significant and is hardly noticeable, except for Q12. This query involves joins and an ORDER BY clause. Due to the ORDER BY clause, the GROUP BY and ORDER BY are not skipped in ClickHouse.<\/p>\n<p><\/p>\n<p>In Table 2, we observe a spike in the execution time for queries Q12 and Q13. Again, this is due to the presence of the ORDER BY clause. To confirm this, I executed queries Q-14 and Q-15, both with and without the ORDER BY clause. Without the ORDER BY clause, the completion time is 259 ms, whereas with the ORDER BY clause, it jumps to 1364212 ms. For debugging this query, I have provided explanations for both queries, with the results included here.<\/p>\n<p><\/p>\n<p>Q15: Without ORDER BY Clause<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">bm=# EXPLAIN VERBOSE SELECT a.\"Year\", c1\/c2 \n     FROM (SELECT \"Year\", count(*)*1000 AS c1 FROM fontime WHERE \"DepDelay\" &gt; 10 GROUP BY \"Year\") a\n     INNER JOIN(SELECT \"Year\", count(*) AS c2 FROM fontime GROUP BY \"Year\") b ON a.\"Year\"=b.\"Year\";<\/code><\/pre>\n<p><\/p>\n<p>Q15: Query Without ORDER BY Clause<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">QUERY PLAN                                                      \nHash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  \nOutput: fontime.\"Year\", (((count(*) * 1000)) \/ b.c2)  \nInner Unique: true   Hash Cond: (fontime.\"Year\" = b.\"Year\")  \n-&gt;  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        \nOutput: fontime.\"Year\", ((count(*) * 1000))        \nRelations: Aggregate on (fontime)        \nRemote SQL: SELECT \"Year\", (count(*) * 1000) FROM \"default\".ontime WHERE ((\"DepDelay\" &gt; 10)) GROUP BY \"Year\"  \n-&gt;  Hash  (cost=999.00..999.00 rows=100000 width=12)        \nOutput: b.c2, b.\"Year\"        \n-&gt;  Subquery Scan on b  (cost=1.00..999.00 rows=100000 width=12)              \nOutput: b.c2, b.\"Year\"              \n-&gt;  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    \nOutput: fontime_1.\"Year\", (count(*))                    \nRelations: Aggregate on (fontime)                    \nRemote SQL: SELECT \"Year\", count(*) FROM \"default\".ontime GROUP BY \"Year\"(16 rows)<\/code><\/pre>\n<p><\/p>\n<p>Q14: Query With ORDER BY Clause<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">bm=# EXPLAIN VERBOSE SELECT a.\"Year\", c1\/c2 FROM(SELECT \"Year\", count(*)*1000 AS c1 FROM fontime WHERE \"DepDelay\" &gt; 10 GROUP BY \"Year\") a \n     INNER JOIN(SELECT \"Year\", count(*) as c2 FROM fontime GROUP BY \"Year\") b  ON a.\"Year\"= b.\"Year\" \n     ORDER BY a.\"Year\";<\/code><\/pre>\n<p><\/p>\n<p>Q14: Query Plan with ORDER BY Clause<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">QUERY PLAN \nMerge Join  (cost=2.00..628498.02 rows=50000000 width=12)   \nOutput: fontime.\"Year\", (((count(*) * 1000)) \/ (count(*)))   \nInner Unique: true   Merge Cond: (fontime.\"Year\" = fontime_1.\"Year\")   \n-&gt;  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)        \nOutput: fontime.\"Year\", (count(*) * 1000)       \nGroup Key: fontime.\"Year\"       \n-&gt;  Foreign Scan on public.fontime  (cost=1.00..-1.00 rows=100000 width=4)           \nRemote SQL: SELECT \"Year\" FROM \"default\".ontime WHERE ((\"DepDelay\" &gt; 10)) \n            ORDER BY \"Year\" ASC   \n-&gt;  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)       \nOutput: fontime_1.\"Year\", count(*)       \nGroup Key: fontime_1.\"Year\"       \n-&gt;  Foreign Scan on public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4)  \n              \nRemote SQL: SELECT \"Year\" FROM \"default\".ontime ORDER BY \"Year\" ASC(16 rows)<\/code><\/pre>\n<p><\/p>\n<p>Output<\/p>\n<p><\/p>\n<p>The results of these experiments show that ClickHouse offers truly impressive performance, and clickhousedb_fdw provides the performance benefits of ClickHouse from PostgreSQL. While there are some overheads when using clickhousedb_fdw, they are minimal and comparable to the performance achieved when running natively in ClickHouse. This also confirms that FDW in PostgreSQL delivers remarkable results.<\/p>\n<p><\/p>\n<p>Telegram chat about ClickHouse <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/clickhouse_ru\">https:\/\/t.me\/clickhouse_ru<\/a><\/noindex><br \/>\nTelegram chat about PostgreSQL <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/pgsql\">https:\/\/t.me\/pgsql<\/a><\/noindex><\/p>\n<p>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/511992\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL. \u042f \u0437\u043d\u0430\u044e, \u043a\u0430\u043a\u0438\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 ClickHouse \u044f \u043f\u043e\u043b\u0443\u0447\u0430\u044e. \u0411\u0443\u0434\u0443\u0442 \u043b\u0438 \u044d\u0442\u0438 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u044b, \u0435\u0441\u043b\u0438 \u044f \u043f\u043e\u043b\u0443\u0447\u0443 \u0434\u043e\u0441\u0442\u0443\u043f \u043a ClickHouse \u0438\u0437 PostgreSQL \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u043d\u0435\u0448\u043d\u0435\u0439 \u043e\u0431\u043e\u043b\u043e\u0447\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 (FDW)? \u0418\u0441\u0441\u043b\u0435\u0434\u0443\u0435\u043c\u044b\u043c\u0438 \u0441\u0440\u0435\u0434\u0430\u043c\u0438 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f PostgreSQL v11, clickhousedb_fdw [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":89499,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-89498","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0432 PostgreSQL, ClickHouse \u0438 clickhousedb_fdw (PostgreSQL) | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-07-23T11:42:07+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-07-23T11:42:07+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Performance testing of analytical queries in PostgreSQL, ClickHouse, and clickhousedb_fdw (PostgreSQL) | ProHoster","description":"In this study, I aimed to explore what performance improvements can be gained by using ClickHouse as a data source instead of PostgreSQL.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0432 PostgreSQL, ClickHouse \u0438 clickhousedb_fdw (PostgreSQL) | ProHoster","og:description":"\u0412 \u044d\u0442\u043e\u043c \u0438\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u0438 \u044f \u0445\u043e\u0442\u0435\u043b \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0443\u043b\u0443\u0447\u0448\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a \u0434\u0430\u043d\u043d\u044b\u0445 ClickHouse, \u0430 \u043d\u0435 PostgreSQL.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/testirovanie-proizvoditelnosti-analiticheskih-zaprosov-v-postgresql-clickhouse-i-clickhousedb_fdw-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-07-23T11:42:07+00:00","article:modified_time":"2020-07-23T11:42:07+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"89498","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:10:17","updated":"2022-10-10 22:45:45","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/89498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=89498"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/89498\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/89499"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=89498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=89498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=89498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}