[dead]
Postgres Just Cracked the Top Fastest Databases for Analytics
61–70 of 126 posts
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#62Just 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…
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).
That and RDS are kind of a rip-off if you are trying to have a sandbox out there, short of running your own instance.
For that, I found Digital Ocean to be very reasonable.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#63Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#64Earlier 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
Not OP but OP seemed to be talking about analytical queries. Which typically summarise data over a period of time. For year on year comparisons this is summarising data over two years. So the query would have at least one aggregate function.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#65Just 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…
> 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.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#66It would be great if Postgres had native columnar storage.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#67A question that I have had for a while that I can't seem to find an answer: for teams that are using various columnar store extensions to turn Postgres into a viable OLAP solution - are they doing so in the same instance of their Postgres that they are using for OLTP? Or are they standing up a separate Postgres instance?
I'm trying to understand if there is any potential performance impact on the OLTP workload by including the OLAP in the same process.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#68Earlier quoted context omitted.
> 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…
> 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. Or just use BigQuery and it is works, it is cheaper to run (by 10x to 100x) and can be done by a junior dev rather than a PhD in Database configuration. I prefer simpl…
I have 50 columns in a table with 10B records in a partitioned table. People can search using any combination. There are a huge number of possible composite indexes, so we don't have any, but we have every column indexed. Most queries take a minute or two, and we also experienced a sudden drop in performance even for queries that used to work OK. Not sure what to do next to improve query speed.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#69Just 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…
same. started writing data into parquet to analyze from there with a big gain in performance and hosting costs
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#70> To enhance query execution speed, we embedded DuckDB as the execution engine for columnstore queries So is it Postgres or DuckDB that cracked the analytics top ?
well, pg_mooncake is a Postgres extension, and Postgres + pg_mooncake is still just Postgres. Users deploy pg_mooncake as a Postgres extension and write and query all tables through psql. Fast analytic databases need two key things: columnar storage and a vectorized execution engine. We introduce a columnstore table access method in Postgres with data stored in Parquet) and execute queries on those tables using DuckD…
Fast analytics databases need a third key technology. I can't say what. You seem unaware of it.