Ask HN: Good tech talks on how analytics systems are implemented?

Partitioning by date gets you really far because the vast majority of queries are interested in a specific timeframe. If your data is growing, but the ingestion rate is not, v0 might work forever.

v0.5 is simply to add another level of partitioning. This can get you an order of magnitude performance gain in many situations. I've often not needed to go past this.

v1 is a different beast. The nice thing about the simplicity of v0 is that it gives you time to learn the requirements for v1 (including whether you even need a v1) and the data is JSON on S3 so it's never hard to migrate it somewhere else. v1 is usually driven by very specific, known requirements, so there's no generic answer. For many requirements, it's useful to put a queue like Kafka (powerful) or Kinesis (convenient/cheap) in front of S3 and have a Spark job performing actions on that queue.

If speed of a single query is the issue, too many small files could be the root problem. Then, a good option is to read batches from the queue and write them to s3 as a single Parquet or ORC file. This could also be a good option if the issue is the cost of s3 API calls (when you're looking at 10k JSONs per second, PUT calls get very pricy), but at large enough scale, you might be better off using Redshift.

With Postgres or Redshift, you can run into contested resource problems when the number of users querying the data scales. I haven't found that to be a problem with Athena due to separated compute and storage, but if it is, you could maybe replicate data to another AWS account. Or, depending on the query patterns, you can build a higher-level table (e.g. given a series of events, tell me the most current state for every entity) from the raw data, either using ETL or by writing a new Spark job that reads from the Kafka queue, figures out the current state and writes the higher-level data a new s3+Athena table.

If the issue is time-to-answer (e.g. if there is a problem, how quickly will that be apparent in your analytics since batched writing introduces delay), streaming analytics could be the solution. You can also use the lambda architecture, but that's always looked too hard to implement and maintain to me.

If the issue is auth/data visibility in an enterprise context, I would lean towards using a fully featured enterprise solution, preferably one that uses a well-documented format on s3 as the underlying datastore with separated compute and storage. Alternatively, you could build or buy a query layer that sits on top of Athena that handles the permissions (e.g. Looker).

At a certain point, you should consider just adopting Snowflake as they have solved many of these problems already and is building an analytics solution really your core competency?