<story><title>Migrating from RethinkDB to Postgres – An Experience Report</title><url>https://medium.com/fuzzy-sharp/migrating-to-postgres-2dc1519a6dc7</url></story><parent_chain><item><author>firasd</author><text>Sorry, I&amp;#x27;m just not buying that it&amp;#x27;s about the file size of data.&lt;p&gt;Right now I&amp;#x27;m working on an app that works with tweets. I want to find all tweets that link to iTunes.&lt;p&gt;When I was using:&lt;p&gt;&amp;gt; select * from `tweets` where `url` not like &amp;#x27;%twitter.com%&amp;#x27; and `url` like &amp;#x27;%itunes.apple.com%&amp;#x27;&lt;p&gt;I could scale my server up to 16 CPUs, it would still take several minutes to search a few million tweets.&lt;p&gt;Yesterday I added another field to the database, `is_audio_url` where I pre-compute whether the URL is an itunes link (by string matching in the app code) when I insert the record into the database. So I can do:&lt;p&gt;&amp;gt; select * from `tweets` where `is_audio_url` = 1&lt;p&gt;And now it&amp;#x27;s blazing fast. It is just my most recent of many experiences that MySQL really struggles with text matching.</text></item><item><author>qaq</author><text>I can assure you that Pinterest&amp;#x27;s dataset is vastly bigger than 100GB :) at certain scale RDBMS will obviously experience issues and might no longer be the optimal solution. For PG (hard to generalize) but beyond 10-20TB things become painful. Now the thing is that &amp;quot;limit&amp;quot; is constantly shifting so if you are starting with 100GB datasets and it is growing at 200GB a year you can basically stay on single instance RDBMS forever.</text></item><item><author>firasd</author><text>Do you have FULLTEXT indexes on those rows? I find it hard to believe that searching something like e.g. a fragment of text in Youtube comments can be as fast in a SQL system (even in RAM) as in Elasticsearch.&lt;p&gt;As for the other stuff I mentioned (recommendations, etc.) I&amp;#x27;m not just basing it on my personal experience--here&amp;#x27;s a write-up from Pinterest about having to dump all their MySQL data to Hadoop to drive various types of analysis. I doubt they would do it it if just putting the SQL DBs in RAM was adequate! &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;medium.com&amp;#x2F;@Pinterest_Engineering&amp;#x2F;tracker-ingesting-mysql-data-at-scale-part-1-424cf43fa7c3&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;medium.com&amp;#x2F;@Pinterest_Engineering&amp;#x2F;tracker-ingesting-...&lt;/a&gt;</text></item><item><author>qaq</author><text>We do exactly what you describe on about 80TB dataset stored across a number of PG instances. The 100GB comments are extremely clever because running a query against a dataset that fully fits in RAM will be blazingly fast. &amp;quot;Try doing text search on a few million SQL rows&amp;quot; we are doing it on billions of rows.</text></item><item><author>firasd</author><text>Interesting quote: &amp;quot;we decided to compute all statistics on demand. This was something we previously tried in RethinkDB, but the results were not good... When we tried implementing statistics as SQL queries in Postgres, we were amazed by the performance. We could implement complex statistics involving data from many tables.&amp;quot;&lt;p&gt;I think standard line &amp;quot;use right tool for the job&amp;quot; is still the ultimate answer. Data in most applications is relational, and you need to query it in different ways that weren&amp;#x27;t anticipated at the beginning, hence the longevity of SQL.&lt;p&gt;That said, I too often see HN commentators say something like &amp;quot;this data was only 100 GB? Why didn&amp;#x27;t they just put it in Postgres?&amp;quot; which is not as clever as the writer may think. Try doing text search on a few million SQL rows, or generating product recommendations, or finding trending topics... Elasticsearch and other &amp;#x27;big data&amp;#x27; tools will do it much quicker than SQL because its a different category of problem. It&amp;#x27;s not about the data size, it&amp;#x27;s about the type of processing required. (Edited my last line here a bit based on replies below.)</text></item></parent_chain><comment><author>Lazare</author><text>Full text search in Postgres is fast if you have it configured correctly.&lt;p&gt;&amp;gt; It is just my most recent of many experiences that MySQL&lt;p&gt;1) You&amp;#x27;re using MySQL not Postgres; given that this is a discussion about whether Postgres can compete with Elasticsearch, that&amp;#x27;s not super relevant.  :)&lt;p&gt;&amp;gt; select * from `tweets` where `url` not like &amp;#x27;%twitter.com%&amp;#x27; and `url` like &amp;#x27;%itunes.apple.com%&amp;#x27;&lt;p&gt;2) That&amp;#x27;s not how you query a full text index; that&amp;#x27;s going to be glacially slow.&lt;p&gt;You need a FULLTEXT index and to use a MATCH...AGAINST query.  Check out the docs[1].&lt;p&gt;[1]: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;dev.mysql.com&amp;#x2F;doc&amp;#x2F;refman&amp;#x2F;5.7&amp;#x2F;en&amp;#x2F;fulltext-search.html&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;dev.mysql.com&amp;#x2F;doc&amp;#x2F;refman&amp;#x2F;5.7&amp;#x2F;en&amp;#x2F;fulltext-search.html&lt;/a&gt;</text></comment>
<story><title>Migrating from RethinkDB to Postgres – An Experience Report</title><url>https://medium.com/fuzzy-sharp/migrating-to-postgres-2dc1519a6dc7</url></story><parent_chain><item><author>firasd</author><text>Sorry, I&amp;#x27;m just not buying that it&amp;#x27;s about the file size of data.&lt;p&gt;Right now I&amp;#x27;m working on an app that works with tweets. I want to find all tweets that link to iTunes.&lt;p&gt;When I was using:&lt;p&gt;&amp;gt; select * from `tweets` where `url` not like &amp;#x27;%twitter.com%&amp;#x27; and `url` like &amp;#x27;%itunes.apple.com%&amp;#x27;&lt;p&gt;I could scale my server up to 16 CPUs, it would still take several minutes to search a few million tweets.&lt;p&gt;Yesterday I added another field to the database, `is_audio_url` where I pre-compute whether the URL is an itunes link (by string matching in the app code) when I insert the record into the database. So I can do:&lt;p&gt;&amp;gt; select * from `tweets` where `is_audio_url` = 1&lt;p&gt;And now it&amp;#x27;s blazing fast. It is just my most recent of many experiences that MySQL really struggles with text matching.</text></item><item><author>qaq</author><text>I can assure you that Pinterest&amp;#x27;s dataset is vastly bigger than 100GB :) at certain scale RDBMS will obviously experience issues and might no longer be the optimal solution. For PG (hard to generalize) but beyond 10-20TB things become painful. Now the thing is that &amp;quot;limit&amp;quot; is constantly shifting so if you are starting with 100GB datasets and it is growing at 200GB a year you can basically stay on single instance RDBMS forever.</text></item><item><author>firasd</author><text>Do you have FULLTEXT indexes on those rows? I find it hard to believe that searching something like e.g. a fragment of text in Youtube comments can be as fast in a SQL system (even in RAM) as in Elasticsearch.&lt;p&gt;As for the other stuff I mentioned (recommendations, etc.) I&amp;#x27;m not just basing it on my personal experience--here&amp;#x27;s a write-up from Pinterest about having to dump all their MySQL data to Hadoop to drive various types of analysis. I doubt they would do it it if just putting the SQL DBs in RAM was adequate! &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;medium.com&amp;#x2F;@Pinterest_Engineering&amp;#x2F;tracker-ingesting-mysql-data-at-scale-part-1-424cf43fa7c3&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;medium.com&amp;#x2F;@Pinterest_Engineering&amp;#x2F;tracker-ingesting-...&lt;/a&gt;</text></item><item><author>qaq</author><text>We do exactly what you describe on about 80TB dataset stored across a number of PG instances. The 100GB comments are extremely clever because running a query against a dataset that fully fits in RAM will be blazingly fast. &amp;quot;Try doing text search on a few million SQL rows&amp;quot; we are doing it on billions of rows.</text></item><item><author>firasd</author><text>Interesting quote: &amp;quot;we decided to compute all statistics on demand. This was something we previously tried in RethinkDB, but the results were not good... When we tried implementing statistics as SQL queries in Postgres, we were amazed by the performance. We could implement complex statistics involving data from many tables.&amp;quot;&lt;p&gt;I think standard line &amp;quot;use right tool for the job&amp;quot; is still the ultimate answer. Data in most applications is relational, and you need to query it in different ways that weren&amp;#x27;t anticipated at the beginning, hence the longevity of SQL.&lt;p&gt;That said, I too often see HN commentators say something like &amp;quot;this data was only 100 GB? Why didn&amp;#x27;t they just put it in Postgres?&amp;quot; which is not as clever as the writer may think. Try doing text search on a few million SQL rows, or generating product recommendations, or finding trending topics... Elasticsearch and other &amp;#x27;big data&amp;#x27; tools will do it much quicker than SQL because its a different category of problem. It&amp;#x27;s not about the data size, it&amp;#x27;s about the type of processing required. (Edited my last line here a bit based on replies below.)</text></item></parent_chain><comment><author>qaq</author><text>I am not selling anything to you :) You can scale your server to even 100 vCpu or whatever blackbox name the provider is using and still have same speed mainly because MySQL does not have parallel query :). BTW you none ever mentioned MySQL but you keep brining it up.</text></comment>