4 ms·

Interesting quote: "we decided to compute all statistics on demand. This was something we previously tried in RethinkDB, but the results were not good... When w

by firasd 9y ago

Interesting quote: "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." I think standard line "use right tool for the job" is still the ultimate answer. Data in most applications is relational, and you need to query it in different ways that weren't anticipated at the beginning, hence the longevity of SQL. That said, I too often see HN commentators say something like "this data was only 100 GB? Why didn't they just put it in Postgres?" 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 'big data' tools will do it much quicker than SQL because its a different category of problem. It's not about the data size, it's about the type of processing required. (Edited my last line here a bit based on replies below.)

qaq 9y ago
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. "Try doing text search on a few million SQL rows" we are doing it on billions of rows.
- probablybroken 9y ago
  I think some of this can be attributed to expectations; Many systems such as elastic are expected to run on ram, whereas people tend to expect relational systems to perform reasonably well on disk. I think we all want a magic black box that makes such problems disappear, and we always seem to be disappointed with the system in question when at some point it doesn't.
  - qaq 9y ago
    You are 100% right the perpetual search for "magic black box" is the cause of much pain on the bright side it's also a source of significant amount of high paying work to clean up the mess.
- firasd 9y ago
  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. As for the other stuff I mentioned (recommendations, etc.) I'm not just basing it on my personal experience--here'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! https://medium.com/@Pinterest_Engineering/tracker-ingesting-mysql-data-at-scale-part-1-424cf43fa7c3 https://medium.com/@Pinterest_Engineering/tracker-ingesting-...
  - qaq 9y ago
    It will not be as fast but it will be fast enough for the wast majority of use cases.
  - kuschku 9y ago
    I'm doing fulltext search on 270 million rows in postgres (with textvector indices) in below 65ms on commodity hardware (sub 8€/mo). And it works better than ElasticSearch, just due to the lower additional overhead.
    - ngrilly 9y ago
      How do you sort the result? Do you use ts_rank? What is the size of your database on disk?
      - kuschku 9y ago
        I use ts_rank combined with many other factors (as I also rank by time, by message type, etc). The size of the database is a few dozen gigabytes by now, but that isn’t relevant with tsvector, only the row count has an effect on search speed.
        ngrilly 9y ago
        Thanks for following up :-) I was asking because ranking can be slow in PostgreSQL. PostgreSQL can use a GIN or GiST index for filtering, but not for ranking, because the index doesn't contain the positional information needed for ranking. This is not an issue when your query is highly selective and returns a low number of matching rows. But when the query returns a large number of matching rows, PostgreSQL has to fetch the ts_vector from heap for each matching row, and this can be really slow. People are working on this but it's not in PostgreSQL core yet: https://github.com/postgrespro/rum https://github.com/postgrespro/rum. This is why I'm a bit surprised by the numbers you shared: fulltext search on 270 million rows in below 65ms on commodity hardware (sub 8€/mo). A few questions, if I may: - What is the average number of rows returned by your queries? Is there a LIMIT? - Is the ts_vector stored in the table? - Do you use a GIN or GiST index on the ts_vector? Cheers.
- gdulli 9y ago
  Exactly. People overestimate the amount of effort and cleverness it takes to horizontally shard/scale your data yourself rather than trust a specialized database to do it as a black box. And overestimate the cleverness of the black box. What you retain by staying with Postgres rather than going to a more exotic database is priceless. There is a threshold of data size or product need that makes a more specialized database the right choice. It's just well above 100 GB and your application should have some very specific needs to justify it.
bulldoa 9y ago
is there some resource one can figure out what category of problem need what storage solution? What storage solutions would you use for product recommendations or trending topics?
- firasd 9y ago
  I used Elasticsearch. But I didn't use it as a primary datastore, more just a mirror of the MySQL data. It comes out of the box with a way to find "More Like This" https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-mlt-query.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... For trending topics I believe I used something related to this: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-significantterms-aggregation.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... An interesting article on using ES: https://auth0.engineering/from-slow-queries-to-over-the-top-performance-how-elasticsearch-helped-us-scale-4fe72ffcb823 https://auth0.engineering/from-slow-queries-to-over-the-top-...

by firasd 9y ago

qaq 9y ago

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. "Try doing text search on a few million SQL rows" we are doing it on billions of rows.

probablybroken 9y ago
I think some of this can be attributed to expectations; Many systems such as elastic are expected to run on ram, whereas people tend to expect relational systems to perform reasonably well on disk. I think we all want a magic black box that makes such problems disappear, and we always seem to be disappointed with the system in question when at some point it doesn't.
- qaq 9y ago
  You are 100% right the perpetual search for "magic black box" is the cause of much pain on the bright side it's also a source of significant amount of high paying work to clean up the mess.
firasd 9y ago
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. As for the other stuff I mentioned (recommendations, etc.) I'm not just basing it on my personal experience--here'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! https://medium.com/@Pinterest_Engineering/tracker-ingesting-mysql-data-at-scale-part-1-424cf43fa7c3 https://medium.com/@Pinterest_Engineering/tracker-ingesting-...
- qaq 9y ago
  It will not be as fast but it will be fast enough for the wast majority of use cases.
- kuschku 9y ago
  I'm doing fulltext search on 270 million rows in postgres (with textvector indices) in below 65ms on commodity hardware (sub 8€/mo). And it works better than ElasticSearch, just due to the lower additional overhead.
  - ngrilly 9y ago
    How do you sort the result? Do you use ts_rank? What is the size of your database on disk?
    - kuschku 9y ago
      I use ts_rank combined with many other factors (as I also rank by time, by message type, etc). The size of the database is a few dozen gigabytes by now, but that isn’t relevant with tsvector, only the row count has an effect on search speed.
      - ngrilly 9y ago
        Thanks for following up :-) I was asking because ranking can be slow in PostgreSQL. PostgreSQL can use a GIN or GiST index for filtering, but not for ranking, because the index doesn't contain the positional information needed for ranking. This is not an issue when your query is highly selective and returns a low number of matching rows. But when the query returns a large number of matching rows, PostgreSQL has to fetch the ts_vector from heap for each matching row, and this can be really slow. People are working on this but it's not in PostgreSQL core yet: https://github.com/postgrespro/rum https://github.com/postgrespro/rum. This is why I'm a bit surprised by the numbers you shared: fulltext search on 270 million rows in below 65ms on commodity hardware (sub 8€/mo). A few questions, if I may: - What is the average number of rows returned by your queries? Is there a LIMIT? - Is the ts_vector stored in the table? - Do you use a GIN or GiST index on the ts_vector? Cheers.
gdulli 9y ago
Exactly. People overestimate the amount of effort and cleverness it takes to horizontally shard/scale your data yourself rather than trust a specialized database to do it as a black box. And overestimate the cleverness of the black box. What you retain by staying with Postgres rather than going to a more exotic database is priceless. There is a threshold of data size or product need that makes a more specialized database the right choice. It's just well above 100 GB and your application should have some very specific needs to justify it.

bulldoa 9y ago

is there some resource one can figure out what category of problem need what storage solution? What storage solutions would you use for product recommendations or trending topics?

firasd 9y ago
I used Elasticsearch. But I didn't use it as a primary datastore, more just a mirror of the MySQL data. It comes out of the box with a way to find "More Like This" https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-mlt-query.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... For trending topics I believe I used something related to this: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-significantterms-aggregation.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... An interesting article on using ES: https://auth0.engineering/from-slow-queries-to-over-the-top-performance-how-elasticsearch-helped-us-scale-4fe72ffcb823 https://auth0.engineering/from-slow-queries-to-over-the-top-...