Show HN: Hydra - Open-Source Columnar Postgres (hydra.so)
hydra is an open-source extension that adds columnar tables to Postgres for efficient analytical reporting. With Hydra, you can analyze billions of rows instantly without changing code.
demo video (5 min): https://youtu.be/1yzxgb0Oyrw github repo: https://github.com/hydradatabase/hydra
For 1.0 GA release, aggregate queries are over *60% faster* than Hydra beta due to aggregate vectorization. Spatial indexes (gin, gist, spgist, and rum indexes) and pg_hint_plan are now enabled for performance optimization.
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 changing code.
for testing, try the hydra free tier to create a column postgres instance on the cloud. https://dashboard.hydra.so/signup
33 comments
[ 2.4 ms ] story [ 213 ms ] thread[0] https://www.ory.sh/hydra
You may want to check that box in the README, assuming it is already done.
Say I wanted to use this to create a high performance "aggregation" API of my existing "write heavy" tables.
Is there a way to keep a `heap` & `columnar` table in sync?
(relative Postgres noob here)
if the data is append-only, an insert trigger could work. if it gets updated and deleted, then insert, update, and delete triggers could be added. of course if the table is very active, this could get bad, fast.
alternately, you can do an insert every hour or so, like insert into table_columnar where created_at > DATE_TRUNC('hour', created_at)
or, even truncate the columnar table daily and re-insert all of the data.
likely none of these is the _best_ solution, but they could help you find what might be the best solution for you.
alternately, if the query patterns work well, you can simply convert the table to columnar, but that's not a panacea.
Please also add this info :
#1. to the pgsql-announce list: https://www.postgresql.org/search/?m=1&ln=pgsql-announce&q=h... "Your search for hydra returned no hits."
#2. to the https://planet.postgresql.org/
1. Is this optimized for constantly adding and removing rows to the columnar table?
2. Is this supported by Microsoft Azure Flexible Server for Postgres?
updates and deletes are available, as well as the ability to compact the table.
I'm personally really excited about all of the recent tooling for postgres aggregates. Definitely a pain point for a lot of developers and its easy to fall in trap where things work fine in the beginning and then query times explode as requirements change and the dataset grows. Nice to not have to spin up another DB in order to solve the problem as well.
Could you give a few examples of what you are speaking of?
I hope you choose to defend your name.
My dream scenario would be installing hydra as an extension into my main rails application database. My usecase is showing analytics numbers directly to users, like "how many people visited my listing", which regular row-level postgres is not suited to answer. To do this now we need a to get that data from our DW, which is slow for single queries, so we need a cache, which we need to keep in sync, which is complexity I don't want. It would be amazing if I could do user-facing analytics queries directly in my main app db.
What put me off after a quick scroll:
Installing the extension changes the default table type to be columnar. I don't want an installed extension to do that, my main workload is still row oriented oltp, I only want specific tables to be columnar and I don't want to change all my normal migrations to specify `USING heap`. IMO timescale does this really well, it's an extension, not a new database. At least that's how I would want it to be.
It also seems like you're trying to claim postgres foreign data wrappers as "hydra external tables", implying it's a new feature? Postgres does this (reading other databases and external files) out of the box and it feels sneaky to try and brand that.
Also the FAQ says "Hydra is not a fork." When the engine clearly is: https://github.com/hydradatabase/citus I realize you want to monetize this as a bigger platform and that's completely fair, but it strikes me as dishonest to deny the citus originins in the FAQ.
that is not the case, hydra as a service sets the default table type. the columnar extension does not make any changes like that, it simply ("simply") adds columnar as an option.
I'm just an engineer, so I'll leave the other comments for others :)
- Installing the extension itself does not change the default table type, this is only the case on Hydra Cloud and our Docker image.
- "Hydra is not a fork" refers to the fact that Hydra did not fork Postgres; it is an extension. We have put in a lot of effort since forking Citus, but it's not our intent to hide that fact.
- Yes, "Hydra External Tables" is a productization around FDWs, there's more we want to do with it but it hasn't been our focus lately.
Ah cool, thanks! How would I go about adding the extension to my own "FROM postgres:15" Dockerfile?
https://news.ycombinator.com/item?id=37247945
https://news.ycombinator.com/item?id=36987920
and a relevant observation is that there are actually multiple license files in the repo so the consumer should read their explicit licensing section of the readme <https://github.com/hydradatabase/hydra#license> since the GitHub sidebar is misleading