Postgres Just Cracked the Top Fastest Databases for Analytics

Post by Eikon » Sat, Mar 08, 2025, 12:28 PM UTC

Galanwe wrote: post #44

bhouston wrote: post #36

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com but it is an absolute pain. 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 5…

> 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…

    EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE];

    Index Scan using table_name_pkey on table_name  (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1)
     Index Cond: (id =  '[ID_VALUE]'::bigint)
    Planning Time: 0.056 ms
    Execution Time: 0.052 ms

Post by jodrellblank » Sat, Mar 08, 2025, 12:54 PM UTC

Eikon wrote: post #51

Galanwe wrote: post #44

Earlier quoted context omitted.

> 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…

Same for https://www.merklemap.com/ The biggest table contains 30B records. A query that uses a B-tree index completes in a few microseconds. EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms

Post by layer8 » Sat, Mar 08, 2025, 12:58 PM UTC

gbin wrote: post #52

bhouston wrote: post #50

Earlier quoted context omitted.

> What do you mean by "standard" SQL? No extensions, just the default Postgres Cloud SQL offering from Google Cloud. > Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants. I view BigQuery as not a standard DB because it really doesn't do transactions, rollbacks, foreign key constraints, indices, stored procedures, etc. I view it as a non-SQL datab…

I think you mean a relational database.

Post by Galanwe » Sat, Mar 08, 2025, 1:04 PM UTC

jodrellblank wrote: post #53

Eikon wrote: post #51

Earlier quoted context omitted.

Same for https://www.merklemap.com/ The biggest table contains 30B records. A query that uses a B-tree index completes in a few microseconds. EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms

If the original comment is talking about saving money and running in Google cloud, could they be using virtual machines and HDD while both you and the parent talking about good speeds (and not money) are using physical servers and SSD?

Post by Eikon » Sat, Mar 08, 2025, 1:09 PM UTC

jodrellblank wrote: post #53

Eikon wrote: post #51

Earlier quoted context omitted.

Same for https://www.merklemap.com/ The biggest table contains 30B records. A query that uses a B-tree index completes in a few microseconds. EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms

If the original comment is talking about saving money and running in Google cloud, could they be using virtual machines and HDD while both you and the parent talking about good speeds (and not money) are using physical servers and SSD?