from Hacker News

Ask HN: What is the best data storage solution for BIG data and fast queries?

by harrisreynolds on 1/6/22, 7:37 PM with 10 comments

I'm working with a client now that has a very large Postgres database. It is currently in the terabyte range but needs to support petabytes.

What is the best solution for storing this data that is fast and supports very large datasets?

For context the product competes in a geo-spatial market and loads GPS data from a large number of vehicles that are updating every 5-10 seconds.

We are considering Apache Pino but I am curious what the HN community would recommend here.

Thank you for any input!!

  • by stocktech on 1/6/22, 7:58 PM

    We'd need a lot more info to make a meaningful suggestion, but I'd at least investigate TimescaleDB to see if it fits. The fact it sits on postgres should be attractive to your client.

  • by zX41ZdbW on 1/6/22, 8:42 PM

    I would consider ClickHouse. It is perfect for interactive analytical queries on large datasets.

    > the product competes in a geo-spatial market and loads GPS data from a large number of vehicles that are updating every 5-10 seconds

    There are multiple companies from this field that are using ClickHouse: https://clickhouse.com/docs/en/introduction/adopters/

  • by ammar_x on 1/6/22, 10:22 PM

    I recommend Google BigQuery. Its storage is cheap ($0.02/GB) and can become even cheaper. You can process huge amounts of data quickly and pay $5 for each terabyte your query processes.

    It's easy to use too and its version of SQL is quite powerful.

    On AWS, there is Athena which works on data stored in S3 and has the same processing price as BigQuery ($5/TB.) However, from my experience, I recommend BigQuery.

  • by samspenc on 1/6/22, 9:31 PM

    If you want an open-source solution, would recommend HBase or Cassandra -- those have been battle-tested and used in a variety of small and large companies.

    They allow you to store huge amounts of data, and as long as you design the primary key properly, allow you to make really fast queries to find the needle in the haystack (milliseconds) as well.

    There are some tradeoffs of course: most engineers I've worked with who come from RDBMS to these tools find the lack of first-class support for secondary indices and SQL or SQL-like queries to be a bummer.

  • by karmakaze on 1/7/22, 12:55 AM

    The large amount of data and number of vehicles seem to be naturally partitioned. In that case you could use anything you want with sharding. Or is it the case that any vehicle can read/write data for any location or perform global analytics?

  • by evv555 on 1/6/22, 8:47 PM

    Not enough information provided but if the data can be organized into meaningful partitions then S3 using Hive partition schema. Pinot should be able to consume from there as well.

  • by prirun on 1/6/22, 10:33 PM

  • by ubadair on 1/6/22, 10:51 PM

    Have a look at https://www.ocient.com

    Not affiliated, but I know people who work there.

  • by nojito on 1/6/22, 8:11 PM

    Clickhouse depending on the true size and retention requirements you have.