6 ms·

Hydra – the fastest Postgres for analytics [benchmarks]

155 points by coatue 4y ago

coatue 4y ago
Hi HN, we’re Joe and JD from Hydra (https://hydra.so/ https://hydra.so/). We're excited to announce our team has added parallel query execution and vectorization to columnar storage on Postgres. In our blog you can review the clickbench benchmarks and the method of how we built Hydra. Hydra open source (https://github.com/HydrasDB/hydra https://github.com/HydrasDB/hydra) Starting today, we are now offering 14-day free trials of our cloud managed Hydra databases. Click the 'Get Started for Free" button on (https://hydra.so/ https://hydra.so/) to get one. Power to the Postgres people!
- senttoschool 4y ago
  Just curious, can this be used together with TimescaleDB?
- hartator 4y ago
  I am a little confused by what you mean by columnar storage and "in fact, it doesn’t use indexes at all". Does that mean every columns (lines) are auto-indexed? Can you use that strategy for storing things like users?
  - wuputah 4y ago
    The metadata can act as a basic form of indexing (or sometimes caching, though Hydra doesn't use metadata to calculate results yet), but it's not an index in the traditional sense. It's used to eliminate stripes and blocks from consideration during a scan. Columnar is not ideal for a `users` table where you want to select and update specific rows, often in very small, quick transactions (OLTP). You would want to continue to use a traditional (heap) table in that case. That's certainly something you can still do with Hydra, and combining both kinds of tables is considered HTAP, and something that is a unique use case of our product. To contrast, columnar is best for "fact" tables -- data about something that happened (thus it does not change) that will be analyzed in an aggregate way. Those might be logs, events, transactions, etc.
    - winrid 4y ago
      Clickhouse works the same way right?
- pella 4y ago
  Any info for "Columnar storage" for geo data? or PostGIS support?
  - wuputah 4y ago
    Yes, Hydra Columnar and PostGIS get along just fine. We've not looked into any PostGIS-specific optimizations yet, but if users run into issues, we'll be happy to investigate.
    - pella 4y ago
      based on https://github.com/HydrasDB/hydra/blob/main/columnar/src/backend/columnar/README.md https://github.com/HydrasDB/hydra/blob/main/columnar/src/bac... the columnar "does not support gist, gin, spgist and brin indexes." :-( and "gist" is important for spatial indexing : http://postgis.net/workshops/postgis-intro/indexing.html http://postgis.net/workshops/postgis-intro/indexing.html
- blixtra 4y ago
  The project looks very interesting. But I had a look at the license of the Citus source code and it appears to be under an AGPL license and I didn't see an exception for the part of the code that you're including in Hydra. FWIU, AGPL code is not compatible with including in Apache code, although the opposite is compatible. So, I'd be interested to know if I'm understanding this wrong or if there is some license exception I'm not seeing.
  - blixtra 4y ago
    I'll answer my own question after doing some more digging. It seems that as the columnar code is a self-contained PostgreSQL extension, you're only using the API which would be fine as there is no linking involved.
OJFord 4y ago
Why benchmark against AWS Aurora rather than Redshift, which would seem a closer/more relevant comparison to me? Also omits ClickHouse, despite using its benchmarking tooling? Edit: Oh, because for some reason Redshift isn't considered postgres compatible in ClickBench. Still, 'the fastest' is 'greenplum' according the full thing: https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIChwYXJ0aXRpb25lZCkiOnRydWUsIkF0aGVuYSAoc2luZ2xlKSI6dHJ1ZSwiQXVyb3JhIGZvciBNeVNRTCI6dHJ1ZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjp0cnVlLCJCeXRlSG91c2UiOnRydWUsIkNpdHVzIjp0cnVlLCJjbGlja2hvdXNlLWxvY2FsIChwYXJ0aXRpb25lZCkiOnRydWUsImNsaWNraG91c2UtbG9jYWwgKHNpbmdsZSkiOnRydWUsIkNsaWNrSG91c2UiOnRydWUsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHpzdGQpIjpmYWxzZSwiQ2xpY2tIb3VzZSBDbG91ZCI6dHJ1ZSwiQ3JhdGVEQiI6dHJ1ZSwiRGF0YWJlbmQiOnRydWUsImRhdGFmdXNpb24iOnRydWUsIkFwYWNoZSBEb3JpcyI6dHJ1ZSwiRHJ1aWQiOnRydWUsIkR1Y2tEQiAoUGFycXVldCkiOnRydWUsIkR1Y2tEQiI6dHJ1ZSwiRWxhc3RpY3NlYXJjaCI6dHJ1ZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR3JlZW5wbHVtIjp0cnVlLCJIZWF2eUFJIjp0cnVlLCJIeWRyYSI6dHJ1ZSwiSW5mb2JyaWdodCI6dHJ1ZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6dHJ1ZSwiTWFyaWFEQiI6ZmFsc2UsIk1vbmV0REIiOnRydWUsIk1vbmdvREIiOnRydWUsIk15U1FMIChNeUlTQU0pIjp0cnVlLCJNeVNRTCI6dHJ1ZSwiUGlub3QiOnRydWUsIlBvc3RncmVTUUwiOnRydWUsIlF1ZXN0REIgKHBhcnRpdGlvbmVkKSI6dHJ1ZSwiUXVlc3REQiI6dHJ1ZSwiUmVkc2hpZnQiOnRydWUsIlNlbGVjdERCIjp0cnVlLCJTaW5nbGVTdG9yZSI6dHJ1ZSwiU25vd2ZsYWtlIjp0cnVlLCJTUUxpdGUiOnRydWUsIlN0YXJSb2NrcyAodHVuZWQpIjpmYWxzZSwiU3RhclJvY2tzIjp0cnVlLCJUaW1lc2NhbGVEQiAoY29tcHJlc3Npb24pIjp0cnVlLCJUaW1lc2NhbGVEQiI6dHJ1ZX0sInR5cGUiOnsic3RhdGVsZXNzIjpmYWxzZSwibWFuYWdlZCI6ZmFsc2UsIkphdmEiOmZhbHNlLCJjb2x1bW4tb3JpZW50ZWQiOmZhbHNlLCJDKysiOmZhbHNlLCJNeVNRTCBjb21wYXRpYmxlIjpmYWxzZSwicm93LW9yaWVudGVkIjpmYWxzZSwiQyI6ZmFsc2UsIlBvc3RncmVTUUwgY29tcGF0aWJsZSI6dHJ1ZSwiQ2xpY2tIb3VzZSBkZXJpdmF0aXZlIjpmYWxzZSwiZW1iZWRkZWQiOmZhbHNlLCJSdXN0IjpmYWxzZSwic2VhcmNoIjpmYWxzZSwiZG9jdW1lbnQiOmZhbHNlLCJ0aW1lLXNlcmllcyI6ZmFsc2V9LCJtYWNoaW5lIjp7InNlcnZlcmxlc3MiOnRydWUsIjE2YWN1Ijp0cnVlLCJMIjp0cnVlLCJNIjp0cnVlLCJTIjp0cnVlLCJYUyI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDUwMGdiIGdwMiI6dHJ1ZSwiYzUuNHhsYXJnZSwgNTAwZ2IgZ3AyIjp0cnVlLCJjNmEubWV0YWwsIDUwMGdiIGdwMiI6dHJ1ZSwiMTYgdGhyZWFkcyI6dHJ1ZSwiMjAgdGhyZWFkcyI6dHJ1ZSwiMjQgdGhyZWFkcyI6dHJ1ZSwiMjggdGhyZWFkcyI6dHJ1ZSwiMzAgdGhyZWFkcyI6dHJ1ZSwiNDggdGhyZWFkcyI6dHJ1ZSwiNjAgdGhyZWFkcyI6dHJ1ZSwibTVkLjI0eGxhcmdlIjp0cnVlLCJmMTZzIHYyIjp0cnVlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6dHJ1ZSwicmEzLjE2eGxhcmdlIjp0cnVlLCJyYTMuNHhsYXJnZSI6dHJ1ZSwicmEzLnhscGx1cyI6dHJ1ZSwiUzIiOnRydWUsIlMyNCI6dHJ1ZSwiMlhMIjp0cnVlLCIzWEwiOnRydWUsIjRYTCI6dHJ1ZSwiWEwiOnRydWV9LCJjbHVzdGVyX3NpemUiOnsiMSI6dHJ1ZSwiMiI6dHJ1ZSwiNCI6dHJ1ZSwiOCI6dHJ1ZSwiMTIiOnRydWUsIjE2Ijp0cnVlLCIzMiI6dHJ1ZSwiNjQiOnRydWUsIjEyOCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwidW5kZWZpbmVkIjp0cnVlfSwibWV0cmljIjoiaG90IiwicXVlcmllcyI6W3RydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWVdfQ== https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...
garysahota93 4y ago
Just checked out the site: I really like the fact that you guys are addressing some of the core capabilities in the data stack (ie ETL). The fact that you guys are supporting DBT natively is a HUGE plus for a data professional like myself. Will definitely be kicking the tires soon & testing against my own workloads.
drchaim 4y ago
Congrats for the launch! As I’ve anticipated, clickbench will become the defacto analytics benchmark, which is great. Something to improve IMHO it’s documentation regarding Cloud and Open Source. Is somehow mixed (the same happen in ClickHouse docs) As a Postgres user I would like to see: how can I install this extension in my current Database? How to migrate to new tables. Timescale makes a really great job in this case. Anyway, as I’ve said. Congrats and good luck!
chrisfrantz 4y ago
Congrats on the launch!
ahachete 4y ago
I'm a deep Postgres person and I'm very interested in the project. Congratulations and welcome as a new open source project to the ecosystem. One important recommendation: please do not run benchmarks on variable-performance storage (gp2 in this case), as it obviously may yield different performance depending on the credit situation, potentially delivering more or less performance to different benchmark runs / scenarios. Specifically, for 500GB you get the max throughput (250MB/s, which BTW is pretty low for an OLAP-style bench, in my opinion) but only 1.5K IOPS. See [1] for more information. An easy alternative would have been gp3 volumes, which do not burst performance, and can be set to provide 16K IOPS and 1Gbps. [1] https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-purpose.html#gp2-performance https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-...
- twoodfin 4y ago
  The one table used for Clickbench isn’t that large. I assume these are the “warm” results that throw away the first execution of each query & all perf being measured here is in-memory.
  - ahachete 4y ago
    That's an assumption. My #1 rule for a benchmark is that it should have reproducible results. Using variable-performance storage goes directly against reproducible results. On a related topic: an OLAP benchmark with a small dataset that fits in memory caters only to what I'd consider a small set of OLAP use cases. I'd love to see one with a large dataset much bigger than memory.
- wuputah 4y ago
  Yeah, I agree! However, ClickBench has used 500GB GP2 as the "standard" for some time, so I stuck to it for consistency. We use GP3 for our hosted service, and I did test on GP3 as well with identical settings as GP2 and the results are very similar.
  - ahachete 4y ago
    But something that is variable in time cannot be "consistent" by definition ;) Great to know this is known. My recommendation still holds: publish results with GP3: whatever others do (potentially, wrong) shouldn't prevent you from doing it right. I'd be giving a deeper look at the project.
twoodfin 4y ago
Clickbench is single-table. Do you have a join story?
- wuputah 4y ago
  Of course, that's the power of Postgres. You can join between columnar tables or between columnar and heap (row-based) tables. The performance of joins hasn't been a specific focus of our engineering work yet, but I made a little test of enriching an analytical query with user data here: https://gist.github.com/wuputah/e62b83f86880bd7e6623809afe4c1e41 https://gist.github.com/wuputah/e62b83f86880bd7e6623809afe4c...
bjering1984 4y ago
Congratulations on the launch!
glogla 4y ago
Few questions: How does it work for writing, I saw somewhere that the columnar tables are append-only? Are there any plans on storage which can be merge or updated into? It is transactional, right? That part of Postgres doesn't change with the plugin. Would you support a business model "install in customer cloud and we provide support" or is your own cloud the only option?
- coatue 4y ago
  Currently columnar is append-only. There are plans to change / improve this! Yes, Hydra is transactional- that doesn't change. Happy to make suggestions around "install and provide support." If you want to chat about it, click "get started for free" on hydra.so, book a time slot, and we can discuss.
  - glogla 4y ago
    I don't have a use case for this myself, I just wanted to share that for "traditionalist" companies, connecting to "completely new cloud" is pretty difficult, while "run some software on ec2 with a support contract" is doable. I don't if that is worth it to you as a possible model, of course.

155 points by coatue 4y ago

Hi HN, we’re Joe and JD from Hydra (https://hydra.so/ https://hydra.so/). We're excited to announce our team has added parallel query execution and vectorization to columnar storage on Postgres. In our blog you can review the clickbench benchmarks and the method of how we built Hydra. Hydra open source (https://github.com/HydrasDB/hydra https://github.com/HydrasDB/hydra) Starting today, we are now offering 14-day free trials of our cloud managed Hydra databases. Click the 'Get Started for Free" button on (https://hydra.so/ https://hydra.so/) to get one. Power to the Postgres people!

senttoschool 4y ago
Just curious, can this be used together with TimescaleDB?
hartator 4y ago
I am a little confused by what you mean by columnar storage and "in fact, it doesn’t use indexes at all". Does that mean every columns (lines) are auto-indexed? Can you use that strategy for storing things like users?
- wuputah 4y ago
  The metadata can act as a basic form of indexing (or sometimes caching, though Hydra doesn't use metadata to calculate results yet), but it's not an index in the traditional sense. It's used to eliminate stripes and blocks from consideration during a scan. Columnar is not ideal for a `users` table where you want to select and update specific rows, often in very small, quick transactions (OLTP). You would want to continue to use a traditional (heap) table in that case. That's certainly something you can still do with Hydra, and combining both kinds of tables is considered HTAP, and something that is a unique use case of our product. To contrast, columnar is best for "fact" tables -- data about something that happened (thus it does not change) that will be analyzed in an aggregate way. Those might be logs, events, transactions, etc.
  - winrid 4y ago
    Clickhouse works the same way right?
pella 4y ago
Any info for "Columnar storage" for geo data? or PostGIS support?
- wuputah 4y ago
  Yes, Hydra Columnar and PostGIS get along just fine. We've not looked into any PostGIS-specific optimizations yet, but if users run into issues, we'll be happy to investigate.
  - pella 4y ago
    based on https://github.com/HydrasDB/hydra/blob/main/columnar/src/backend/columnar/README.md https://github.com/HydrasDB/hydra/blob/main/columnar/src/bac... the columnar "does not support gist, gin, spgist and brin indexes." :-( and "gist" is important for spatial indexing : http://postgis.net/workshops/postgis-intro/indexing.html http://postgis.net/workshops/postgis-intro/indexing.html
blixtra 4y ago
The project looks very interesting. But I had a look at the license of the Citus source code and it appears to be under an AGPL license and I didn't see an exception for the part of the code that you're including in Hydra. FWIU, AGPL code is not compatible with including in Apache code, although the opposite is compatible. So, I'd be interested to know if I'm understanding this wrong or if there is some license exception I'm not seeing.
- blixtra 4y ago
  I'll answer my own question after doing some more digging. It seems that as the columnar code is a self-contained PostgreSQL extension, you're only using the API which would be fine as there is no linking involved.

OJFord 4y ago

Why benchmark against AWS Aurora rather than Redshift, which would seem a closer/more relevant comparison to me? Also omits ClickHouse, despite using its benchmarking tooling? Edit: Oh, because for some reason Redshift isn't considered postgres compatible in ClickBench. Still, 'the fastest' is 'greenplum' according the full thing: https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIChwYXJ0aXRpb25lZCkiOnRydWUsIkF0aGVuYSAoc2luZ2xlKSI6dHJ1ZSwiQXVyb3JhIGZvciBNeVNRTCI6dHJ1ZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjp0cnVlLCJCeXRlSG91c2UiOnRydWUsIkNpdHVzIjp0cnVlLCJjbGlja2hvdXNlLWxvY2FsIChwYXJ0aXRpb25lZCkiOnRydWUsImNsaWNraG91c2UtbG9jYWwgKHNpbmdsZSkiOnRydWUsIkNsaWNrSG91c2UiOnRydWUsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHpzdGQpIjpmYWxzZSwiQ2xpY2tIb3VzZSBDbG91ZCI6dHJ1ZSwiQ3JhdGVEQiI6dHJ1ZSwiRGF0YWJlbmQiOnRydWUsImRhdGFmdXNpb24iOnRydWUsIkFwYWNoZSBEb3JpcyI6dHJ1ZSwiRHJ1aWQiOnRydWUsIkR1Y2tEQiAoUGFycXVldCkiOnRydWUsIkR1Y2tEQiI6dHJ1ZSwiRWxhc3RpY3NlYXJjaCI6dHJ1ZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR3JlZW5wbHVtIjp0cnVlLCJIZWF2eUFJIjp0cnVlLCJIeWRyYSI6dHJ1ZSwiSW5mb2JyaWdodCI6dHJ1ZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6dHJ1ZSwiTWFyaWFEQiI6ZmFsc2UsIk1vbmV0REIiOnRydWUsIk1vbmdvREIiOnRydWUsIk15U1FMIChNeUlTQU0pIjp0cnVlLCJNeVNRTCI6dHJ1ZSwiUGlub3QiOnRydWUsIlBvc3RncmVTUUwiOnRydWUsIlF1ZXN0REIgKHBhcnRpdGlvbmVkKSI6dHJ1ZSwiUXVlc3REQiI6dHJ1ZSwiUmVkc2hpZnQiOnRydWUsIlNlbGVjdERCIjp0cnVlLCJTaW5nbGVTdG9yZSI6dHJ1ZSwiU25vd2ZsYWtlIjp0cnVlLCJTUUxpdGUiOnRydWUsIlN0YXJSb2NrcyAodHVuZWQpIjpmYWxzZSwiU3RhclJvY2tzIjp0cnVlLCJUaW1lc2NhbGVEQiAoY29tcHJlc3Npb24pIjp0cnVlLCJUaW1lc2NhbGVEQiI6dHJ1ZX0sInR5cGUiOnsic3RhdGVsZXNzIjpmYWxzZSwibWFuYWdlZCI6ZmFsc2UsIkphdmEiOmZhbHNlLCJjb2x1bW4tb3JpZW50ZWQiOmZhbHNlLCJDKysiOmZhbHNlLCJNeVNRTCBjb21wYXRpYmxlIjpmYWxzZSwicm93LW9yaWVudGVkIjpmYWxzZSwiQyI6ZmFsc2UsIlBvc3RncmVTUUwgY29tcGF0aWJsZSI6dHJ1ZSwiQ2xpY2tIb3VzZSBkZXJpdmF0aXZlIjpmYWxzZSwiZW1iZWRkZWQiOmZhbHNlLCJSdXN0IjpmYWxzZSwic2VhcmNoIjpmYWxzZSwiZG9jdW1lbnQiOmZhbHNlLCJ0aW1lLXNlcmllcyI6ZmFsc2V9LCJtYWNoaW5lIjp7InNlcnZlcmxlc3MiOnRydWUsIjE2YWN1Ijp0cnVlLCJMIjp0cnVlLCJNIjp0cnVlLCJTIjp0cnVlLCJYUyI6dHJ1ZSwiYzZhLjR4bGFyZ2UsIDUwMGdiIGdwMiI6dHJ1ZSwiYzUuNHhsYXJnZSwgNTAwZ2IgZ3AyIjp0cnVlLCJjNmEubWV0YWwsIDUwMGdiIGdwMiI6dHJ1ZSwiMTYgdGhyZWFkcyI6dHJ1ZSwiMjAgdGhyZWFkcyI6dHJ1ZSwiMjQgdGhyZWFkcyI6dHJ1ZSwiMjggdGhyZWFkcyI6dHJ1ZSwiMzAgdGhyZWFkcyI6dHJ1ZSwiNDggdGhyZWFkcyI6dHJ1ZSwiNjAgdGhyZWFkcyI6dHJ1ZSwibTVkLjI0eGxhcmdlIjp0cnVlLCJmMTZzIHYyIjp0cnVlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6dHJ1ZSwicmEzLjE2eGxhcmdlIjp0cnVlLCJyYTMuNHhsYXJnZSI6dHJ1ZSwicmEzLnhscGx1cyI6dHJ1ZSwiUzIiOnRydWUsIlMyNCI6dHJ1ZSwiMlhMIjp0cnVlLCIzWEwiOnRydWUsIjRYTCI6dHJ1ZSwiWEwiOnRydWV9LCJjbHVzdGVyX3NpemUiOnsiMSI6dHJ1ZSwiMiI6dHJ1ZSwiNCI6dHJ1ZSwiOCI6dHJ1ZSwiMTIiOnRydWUsIjE2Ijp0cnVlLCIzMiI6dHJ1ZSwiNjQiOnRydWUsIjEyOCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwidW5kZWZpbmVkIjp0cnVlfSwibWV0cmljIjoiaG90IiwicXVlcmllcyI6W3RydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWVdfQ== https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...

garysahota93 4y ago

Just checked out the site: I really like the fact that you guys are addressing some of the core capabilities in the data stack (ie ETL). The fact that you guys are supporting DBT natively is a HUGE plus for a data professional like myself. Will definitely be kicking the tires soon & testing against my own workloads.

drchaim 4y ago

Congrats for the launch! As I’ve anticipated, clickbench will become the defacto analytics benchmark, which is great. Something to improve IMHO it’s documentation regarding Cloud and Open Source. Is somehow mixed (the same happen in ClickHouse docs) As a Postgres user I would like to see: how can I install this extension in my current Database? How to migrate to new tables. Timescale makes a really great job in this case. Anyway, as I’ve said. Congrats and good luck!

chrisfrantz 4y ago

Congrats on the launch!

ahachete 4y ago

I'm a deep Postgres person and I'm very interested in the project. Congratulations and welcome as a new open source project to the ecosystem. One important recommendation: please do not run benchmarks on variable-performance storage (gp2 in this case), as it obviously may yield different performance depending on the credit situation, potentially delivering more or less performance to different benchmark runs / scenarios. Specifically, for 500GB you get the max throughput (250MB/s, which BTW is pretty low for an OLAP-style bench, in my opinion) but only 1.5K IOPS. See [1] for more information. An easy alternative would have been gp3 volumes, which do not burst performance, and can be set to provide 16K IOPS and 1Gbps. [1] https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-purpose.html#gp2-performance https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/general-...

twoodfin 4y ago
The one table used for Clickbench isn’t that large. I assume these are the “warm” results that throw away the first execution of each query & all perf being measured here is in-memory.
- ahachete 4y ago
  That's an assumption. My #1 rule for a benchmark is that it should have reproducible results. Using variable-performance storage goes directly against reproducible results. On a related topic: an OLAP benchmark with a small dataset that fits in memory caters only to what I'd consider a small set of OLAP use cases. I'd love to see one with a large dataset much bigger than memory.
wuputah 4y ago
Yeah, I agree! However, ClickBench has used 500GB GP2 as the "standard" for some time, so I stuck to it for consistency. We use GP3 for our hosted service, and I did test on GP3 as well with identical settings as GP2 and the results are very similar.
- ahachete 4y ago
  But something that is variable in time cannot be "consistent" by definition ;) Great to know this is known. My recommendation still holds: publish results with GP3: whatever others do (potentially, wrong) shouldn't prevent you from doing it right. I'd be giving a deeper look at the project.

twoodfin 4y ago

Clickbench is single-table. Do you have a join story?

wuputah 4y ago
Of course, that's the power of Postgres. You can join between columnar tables or between columnar and heap (row-based) tables. The performance of joins hasn't been a specific focus of our engineering work yet, but I made a little test of enriching an analytical query with user data here: https://gist.github.com/wuputah/e62b83f86880bd7e6623809afe4c1e41 https://gist.github.com/wuputah/e62b83f86880bd7e6623809afe4c...

bjering1984 4y ago

Congratulations on the launch!

glogla 4y ago

Few questions: How does it work for writing, I saw somewhere that the columnar tables are append-only? Are there any plans on storage which can be merge or updated into? It is transactional, right? That part of Postgres doesn't change with the plugin. Would you support a business model "install in customer cloud and we provide support" or is your own cloud the only option?

coatue 4y ago
Currently columnar is append-only. There are plans to change / improve this! Yes, Hydra is transactional- that doesn't change. Happy to make suggestions around "install and provide support." If you want to chat about it, click "get started for free" on hydra.so, book a time slot, and we can discuss.
- glogla 4y ago
  I don't have a use case for this myself, I just wanted to share that for "traditionalist" companies, connecting to "completely new cloud" is pretty difficult, while "run some software on ec2 with a support contract" is doable. I don't if that is worth it to you as a possible model, of course.