SQL isn't the problem IMO. The problem is that the implementation we're using (PostgreSQL) is a row-store which is indeed more optimal for the kind of operation

SQL isn't the problem IMO. The problem is that the implementation we're using (PostgreSQL) is a row-store which is indeed more optimal for the kind of operations you mention. However, SQL isn't limited to row-stores. There are column-store implementations that are quite amazing for aggregate queries, e.g. Clickhouse. Using one of those would very likely work for us, but my understanding is that loading data into them in real-time is problematic.