All you need is postgres: part 73
Postgres Just Cracked the Top Fastest Databases for Analytics
71–80 of 126 posts
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#72A 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 inc…
And further, with this pg_mooncake extension allowing you to store the data in S3, is Postgres simply providing compute to run DuckDB? I suppose it's also providing a standardized interface and "data catalog."
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#73Earlier quoted context omitted.
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 you…
> Did you use plain Postgres tables or a columnar extension like Timescale or pg_mooncake? Plain Postgres tables hosted on Google Cloud SQL on a decent machine. > Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. Which is what I am saying. Standard means without extensions. > Personally I'm a huge fan of dedicated databases like ClickHouse. Where do you run it? What…
Your comment is confusing to me because it appears to have nothing to do with the article, which is not about using Postgres' heap tables. In fact, you're arguing for a purpose-built storage engine (BigQuery), which is what pg_mooncake also is, except the latter is integrated with Postgres.
At my company we self-host ClickHouse, but if we started out today we'd definitely adopt ClickHouse Cloud. One big advantage of CH is that tables can be backed by ordinary cloud storage like S3 and GCS, which is incredibly cheap. BQ has support for external Iceberg tables in GCS, but (from what I recall) no write support.
We are shuffling several TBs/week into BigQuery, and that is extremely expensive. Querying this amount of data (billions of rows per day) is also very expensive unless you purchase flat pricing.
If your needs are really modest, then BQ is definitely very reasonable.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#74Earlier quoted context omitted.
I think you mean a relational database.
All the features mentioned are part of SQL.
SQL (the DML) is just a query language. It could be implemented on anything.
Parts of SQL DDL is implementation specific. The SQL standard hardcodes the assumption of (by modern standards) a small-scale system and therefore will deviate significantly on large-scale systems. There is no practical way to make this "standard", you have to understand the implementation it reflects.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#75As others have mentioned the results are from an extension, not postgres. Unfortunately using an extension that's not "part" of postgres (like pg_stat_statements) is not trivial for most people since there are both technical and legal issues.
What are th legal issues?
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#76Any differentiation vs Hydra ? They also put duckdb inside pg https://www.hydra.so/
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#77Earlier quoted context omitted.
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.
DO’s pricing has increased a lot. I was surprised, I couldn’t start a $5 vm anymore, it’s worth checking again.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#78Just 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…
The OP is trying to run a mixed analytic workload which Postgres does poorly even at relatively small scales. No amount of "light tweaking" can fix it, it is intrinsic to the architecture. You can put 100B records in a Postgres table (I have) and it works if your workload is trivial but not well. The Postgres internals are not designed for data this large, it is an OLTP system.
You can do fast-twitch analytical queries (sub-second, a few seconds at the tail) on complex operational data models where you are inserting millions of records per second on 100B record tables while those queries are running. Just not on Postgres.
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#79[deleted]
Re: Postgres Just Cracked the Top Fastest Databases for Analytics
#80Earlier quoted context omitted.
> 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…
Indices are one the first things you learn in any decent DB course, so you don’t need a PhD But if BigTable just solves the problem it seems the way to go PostGres is popular for a reason, it’s ACID unlike BigTable You may run into these problems later on, you may not
I love Postgres and use it a lot but not for analytics.