HN Explorer

Show HN: Hydra 1.0 – open-source column-oriented Postgres (hydra-so.notion.site)

269 points by coatue ↗ HN
hi hn, hydra ceo here

hydra is an open source, column-oriented postgres. you can set up remarkably fast aggregates on your project in minutes to query billions of rows instantly.

postgres is great, but aggregates can take minutes to hours to return results on large data sets. long-running analytical queries hog database resources and degrade performance. use hydra to run much faster analytics on postgres without making code changes. data is automatically loaded into columnar format and compressed. connect to hydra with your preferred postgres client (psql, dbeaver, etc).

following 4 months of development on hydra v0.3.0-alpha, our team is proud to share our first major version release. hydra 1.0 is under active development, but ready for use and feedback. we’re aiming to release 1.0 into general availability (ga) soon.

for testing, try the hydra free tier to create a column-oriented postgres instance on the cloud. https://dashboard.hydra.so/signup

83 comments

[ 99.3 ms ] story [ 2514 ms ] thread
s-mon
This looks wild! Been looking for a good event based logs DB and didn’t want to go full clickhouse. This will do!
mlenhard
Congrats on the launch!

For those who have not experimented with columnar based databases, I would highly recommend toying around with them.

The performance improvements can be substantial. Obviously there are drawbacks involved with integrating a new database into your infrastructure, so it is exciting to see columnar format introduced to Postgres. Removes the hurdle of learning, deploying and monitoring another database.

therealwardo
how does Hydra compare to Citus? https://www.citusdata.com
jerrysievert
generally faster across the board, a lot of work was done to expand and speed it up, plus updates, deletes, and vacuuming.

https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...

adr1an
Right when I was thinking URL shorteners were out of fashion... /S
biugbkifcjk
It's just there to make it easier for mobile users to click it..
setr
I don’t see why the GitHub link is any harder to click than the tiny url link in that post.

I’m pretty sure the only reason url shorteners exist with purpose is because of Twitter limits (and software that doesn’t visually hide egregiously long urls), but continues to be used outside of those places due to cargo culting

rubiquity
Since benchmarks can be misleading I want to point out that the differences between Hydra and the "tuned"[0] PostgreSQL (which are some very basic settings) are a lot less convincing, with plain old PG coming ahead on quite a few: https://tinyurl.com/eju9tht2

I also noticed quite a bit of parity between Hydra and Citus on data set size. Is Hydra a fork of Citus columnar storage?

0 - https://github.com/ClickHouse/ClickBench/blob/main/postgresq...

arp242
> plain old PG coming ahead on quite a few

I found that is common among these types of databases (e.g. Citus, Timescale, etc.) which perform well under very specific conditions, and worse for many (most?) other things, sometimes significantly worse.

That said, Hydra does take up ~17.5G for that benchmark and "PostgreSQL tuned" about 120G, the insert time is ~9 times faster, and "cold run" is quite a bit faster too. It's only "hot run" that shows a fairly small difference. I think it's fair to say Hydra "wins" that benchmark.

> Is Hydra a fork of Citus columnar storage?

Yes: "Hydra Columnar is a fork from Citus Columnar c. April, 2022".

riku_iki
> Hydra does take up ~17.5G for that benchmark and "PostgreSQL tuned" about 120G

you can run pg on compressed filesystem

arp242
I'm sure you can, but AFAIK neither uses compression in that benchmark so it's a fair comparison. Even if filesystem compression would reduce that to 17.5G (doubtable), it won't be free in terms of CPU cycles, and no matter what it's still ~120G to load in memory, bytes to scan/update, etc.
riku_iki
my bet is that hydra uses compression inside already, otherwise it is hard to explain where difference comes from.

> it won't be free in terms of CPU cycles

it can reduce IO traffic significantly, and it can be very positive trade off depending on circumstances.

arp242
I had assumed that PostgreSQL is so much larger because it creates heaps of indexes (which is probably also why inserts are so much slower for it), but I don't really have a good way to confirm that quickly.
riku_iki
one can choose to not create "heaps of indexes".
arp242
At which point your performance will drop like a brick for these types of queries – I'm pretty sure these indexes weren't added for the craic.
riku_iki
it depends on your query obviously.

In general, I did very deep benchmarking of pg, clickhouse and duckdb, and I sure didn't make stupid mistakes like this: https://news.ycombinator.com/item?id=36990831

My dataset has 50B rows and 2tb of data, and I think columnar dbs are very overhiped and I chose pg because:

- pg performance is acceptable, maybe 2-5x times slower than clickhouse and duckdb on some queries if pg is configured correctly and run on compressed storage

- clickhouse and duckdb start falling apart very fast because they specialized on very narrow type of queries: https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47521 https://github.com/duckdb/duckdb/discussions/6696

zX41ZdbW
ClickHouse can do large GROUP BY queries, not limited by memory: https://clickhouse.com/docs/en/sql-reference/statements/sele...
riku_iki
as explained in https://github.com/ClickHouse/ClickHouse/issues/47521#issuec... it can't, that parameters only applies on pre aggregation phase but not aggregation.

Feature request is not implemented yet: https://github.com/ClickHouse/ClickHouse/issues/40588

zX41ZdbW
ClickHouse uses "grace hash" GROUP BY with the number of buckets = 256.

It can do size about 256 times larger than a memory because only one bucket has to be in memory while merging. It works for distributed query processing as well and is enabled by default.

About the linked issue - it looks like it is related to some extra optimization on top of what already exists.

[deleted]
(comment deleted)
riku_iki
> only one bucket has to be in memory while merging.

its hard for me to judge about implementation details, but per that person reply memory is also multiplied by number of threads which do aggregation.

benn0
Do you have happen to have any documentation about your benchmarking? I'm also considering these options at the moment (currently using pg+timescaledb) and interested in what you found.
riku_iki
I don't have documentation.

I just created large tables, and tried to join, group by, sort them in pg, clickhouse, duckdb, looked what failed or being slow, and tried to resolve it.

I am happy to answer specific questions, but I didn't use timescaledb.

arp242
"2-5x times slower" can mean the difference from 2 seconds to 4 to 10 seconds. Two seconds is still (barely) acceptable for interactive usage, ten seconds: not so much. You're also going to need less beefy servers, or fewer servers.

I also "just" use PostgreSQL for all of this by the way, but the limitations are pretty obvious. You're much more limited in what you can query with good performance, unless you start creating tons of queries or pre-computed data and such, which have their own trade-offs. Columnar DBs are "overhyped" in the sense that everything in programming seems to be, but they do exist for good reasons (the reason I don't use it are because they also come with their own set of downsides, as well as just plain laziness).

ushakov
Are you funded?
coatue
Check out the "about us" section on the page for more details! "We are excited to share that Hydra raised a $3.1M seed round to drive development of columnar Postgres. We remain committed to sharing our upcoming releases to open source."
zhendlin
awesome project - and we tested Zing Data ( http://www.zingdata.com ) with Hydra to make really fast analytical queries on postgres scale to analytics users on mobile and so far have seen great results.
mitchpatin
super impressive performance improvements!

do most of your customers replicate their postgres database to Hydra for analytics jobs, or what's the typical set up?

mdaniel
I wanted to say thank you for using actual Open Source licenses. It's gotten to where I treat any recent "Launch HN" or "Show HN" containing "open source" in the title as "well, I wonder which crazy license this project is using"
dang
Can you point me to examples of Launch HNs using funky licenses? Show HNs are free-form but Launch HNs are curated by us, and I'd like to know what red flags to watch for.

(As this is offtopic for the current Show HN, it might be better for to email hn@ycombinator.com if you, or anyone, would be willing to share that way.)

mdaniel
I sent email to avoid being a distraction, but I did want to follow up publicly and say that I apologize for lumping Launch HN into the same bucket as Show HN. For the most part the Launch ones are really Open Source and I apologize for the over generalization :-(
Iwan-Zotow
> to query billions of rows instantly

Rows? Rows?!? What's the point to have columnar DB to query rows?

ithkuil
Columnar DBs often allow you to have tables consisting of multiple columns where values in a column are correlated with values in the other columns. All such correlated values belonging to different columns are commonly called a "row" despite not being stored contiguously.

Generally what you do is to scan a column and evaluate a predicate in each value you encounter during the scan (possibly in parallel). For each value of that column that matches the predicate you then keep track of the "position" of the value in the column (a common technique is sparse set data structure such as for example a roaring bitmap). Then you scan through another column and select values for the saved "positions".

As you can see, it's not a stretch to view values from different columns that belong to the same "position" as belonging to the same "row" and the "position" to be the "row index" or "row id"

Iwan-Zotow
Sure, you could think of a row as "tuple of values returned from all columns at the same index". But why it was that important to measure and use and present for primarily columnar store? What's the point to measure and count row access? Isn't existing row DBs working for such case?
joshgray
Congrats to the entire Hydra team on the launch! We (Artemis - https://www.artemisdata.io/) are stoked to be build with you as a partner to help data teams analyze data even faster!
hgimenez
Always awesome to see folks moving Postgres forward. Congrats on the launch!
burcs
This is really cool, just played around with it a bit but excited to do a deeper dive later. Nice work guys!
coatue
Thanks! feel free to DM me in the hydra discord (or email) anytime
zrizavi17
Such a game changer and useful alternative to legacy databases!
chrisjc
Very out of touch with Postgres, but is there a native column oriented table type option in Postgres so that you choose either row-based or columnar in the CREATE TABLE DDL?
techwizrd
This looks really impressive, and I'm excited to see how it performs on our data!

P.S., I think the name conflicts with Hydra, the configuration management library: https://hydra.cc/

say_it_as_it_is
Hydra Columnar is a fork from Citus Columnar c. April, 2022.
thih9
Citus: https://github.com/citusdata/citus

BTW, Citus license is GNU Affero General Public License (github lists “conditions: same license”) and hydra is Apache. How is that possible if the latter is a fork? There’s probably something about these licenses I’m not aware of and I’m curious.

jerrysievert
hydra columnar inherits its license from citus: https://github.com/hydradatabase/hydra/blob/main/columnar/LI...

but, hydra itself is more than just the columnar extension.

thih9
Thanks for explaining. This is confusing to me as a github user, i.e. if I saw a license in the project’s description, I wouldn’t expect another license in a subdirectory.

Github now has UI for repos with multiple licenses: https://github.blog/changelog/2022-05-26-easily-discover-and... , that would have been clearer for me.

mdaniel
they must not look at anything other than the top directory, then, because this repo's sidebar only says "Apache-2.0 license" which I grant is clarified down at the _bottom_ of the README but if that multi-license thing really wants to be helpful it should at least look one subdirectory down
pella
Congratulations!

Can we expect support for gist, gin, spgist, and brin indexes sometime in the near future?

Based on the source code, it appears that they are not supported:

https://github.com/hydradatabase/hydra/blob/96056312e7c0f413...

"... Columnar supports `btree` and `hash `indexes (and the constraints requiring them) but does not support `gist`, `gin`, `spgist` and `brin` indexes."

nerdponx
Do different kinds of indexes work better for columnar storage? Or is it the same principles for both?
hodgesrm
Difference principles of indexing, as least based on my experience with ClickHouse.

* Column-based stores have really fast scans due to compression and vectorization, so you'll generally always read down the column. The way to speed it up is to have "skip indexes" that allow you to skip blocks, e.g., don't even bother to read/decompress them.

* Commonly used indexes need to be very sparse, so they fit in memory even when tables run to hundreds of billions of rows.

* Finally highly compressed columns can be used as indexes to filter data rapidly. ClickHouse calls this PREWHERE processing.

Edit: clarify skip indexes

ryanb_wise
Talked to OP last night and played around with it this morning. This is something I've wanted to see added to postgres for a long time, and couldn't have been done by a nicer and more accommodating founder. Very excited.
pajep
can I ask if you guys take contributors?
wuputah
of course :) drop by our Discord if there's something you'd like to contribute and want to chat about it beforehand, need help/have questions getting started, etc. https://hydra.so/discord
florianherrengt
Can you add the extension to an existing database?
ahmedfromtunis
That's also my question. Couldn't find anything for it in the docs provided.

Also, how to migrate data from an existing database? (Is it the usual pg_dump/psql combo?)

d4rkp4ttern
Indeed I am also wondering — if I have an existing Postgres DB , how do I leverage this?
qeternity
Ok so this is a Citus fork.

Where can I read about what the differences / trade offs are? I don’t see anything in the docs.

[deleted]
(comment deleted)
quadrature
How are updates handled, is it doing a merge on read ?
wuputah
First we added a bitmask to mark rows as deleted - these rows are filtered out on read. Then updates are implemented as deletions + inserts. We have also added vacuum functions to remove/rewrite stripes that have >20% of deleted rows in order to reclaim space and optimize those stripes.