We tried using Postgres with large analytics at my previous company https://threekit.com but it is …

> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk.

> a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time I mean, I don't know what you call "close to real time", and what kind of query you did, but I have Postgres serving requests from a 20B rows table just fine, with some light tweaking of indexes and partitions (I'm by no means a DBA). Unless we are digging much deeper than that in the specifics of the data and queries, I don't think any conclusion can be taken from either experience.

>> Just to be clear, standard SQL databases are not great for large-scale analytics. What do you mean by "standard" SQL? Were you using an OLTP or OLAP db when you faced these difficulties? Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.

Did you use plain Postgres tables or a columnar extension like Timescale, Citus, or pg_mooncake? Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. However, it looks like these extensions can be quite decent, at least at queries (you might still run into write performance due to Postgres' OLTP architecture, but I've never benchmarked it). In OLAP terms the size of your data is tiny and this would probably work just fine. Personally I'm a huge fan of dedicated databases like ClickHouse. BigQuery gets very expensive as your data and query volume grows.

As someone dealing with billions of records on it, BigQuery is far from cheap; G will not charge you much for storage as they will charge you for queries and data transfer. AFAIK, the cheapest Postgres server on GCP is very expensive compared to the usual Postgres installation (price/performance).

> it was basically untouchable because it was so slow. I've worked with a 5B row table on Snowflake with (maybe) no indexes, and while somewhat slow you could still run reasonable queries on it in a minute or two.

same. started writing data into parquet to analyze from there with a big gain in performance and hosting costs

> Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time Analytics isn't typically something that needs real-time capabilities, for one. > a rapidly growing table [emphasis mine] I think I see part of the problem here. If you had a single table, that means it's completely denormalized, so your schema probably looked something like this (or wider): CREATE TABLE UserEvent ( id UUID PRIMARY KEY, user_id UUID NOT NULL, user_ip_address TEXT NOT NULL, user_agent TEXT NOT NULL, event_data JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), event_type TEXT ); CREATE INDEX UserEvent_user_id_idx ON UserEvent (user_id); CREATE INDEX UserEvent_created_at_idx ON UserEvent (created_at); …