6 ms·
Omnigres: Postgres as a Platform
- evanjrowley 3y agoCool project! About a decade ago, I had a job that involved entering data into an Oracle-based equivalent of this. I wish I could remember what it was, called but perhaps it is better for such a monster to remain nothing more than a myth.
- yrashk 3y agoAuthor here. Thanks for the feedback! The intention we have is to take what's right with the colocation approach and take the learnings of what was wrong with those before (such as the above or Illustra). Essentially, we're aiming create a modern polyglot runtime that has an embedded database with all its functionality (Postgres), with a contemporary, lightweight D3X.
- ronjouch 3y agoCouchDB also tries to do something similar (natively hosting webapps close to a DB good at replication between replicas). https://guide.couchdb.org/draft/standalone.html https://guide.couchdb.org/draft/standalone.html
- LeonenTheDK 3y agoThat's very interesting, thanks for the link.
- thecal 3y agoYou are probably thinking of this: https://apex.oracle.com/en/ https://apex.oracle.com/en/
- lovasoa 3y agoNice to see Omnigres trending on hn; congratulations Yurii! The project is interesting, and thought provoking, because it goes against the often recommended "good practice" of separating storage and compute. Doing the exact opposite has a lot to offer in terms of performance, simplicity, and speed of development. I am currently also working on a database-first web application framework [1], with different goals and use cases, and I bet we'll see more of these in the future. [1] https://sql.ophir.dev https://sql.ophir.dev
- civilized 3y ago> the often recommended "good practice" of separating storage and compute. This gets me thinking... has developer conventional wisdom ever recommended binding things together? Or does it only ever recommend more separation, more abstraction?
- wk_end 3y agoReact is a recent example of the “right” thing being less separated than prior conventional wisdom - we’re now mixing JS, often styling, and “HTML” when the historical powers that be demanded separation.
- Kinrany 3y agoEvery time something is described as opinionated.
- ndriscoll 3y ago10 years ago when people still used the term "big data" there was some enterprise buzzword usage around "hyper convergence" that was supposed to make it easy to bring compute and storage together/move compute jobs close to the storage they were supposed to operate on. I guess the idea was something like if you had a map-reduce job, you have some scheduler that runs workers on nodes that are in the same rack/directly connected to the storage partition they'll need.
- renewiltord 3y agoMechanical sympathy. And conventional low-latency systems do that as much as possible - binding all sorts of layers. When you're picking the first few bytes of the packet and semi-parsing it, you don't traditionally and conventionally have that go through some vtable and perform dynamic dispatch to find the right parser or whatever. It's all plugged into everything monolithically.
- bzzzt 3y agoObject oriented programming prescribes binding code and data together in classes.
- 3y ago
- anonu 3y agoI saw omnigres mentioned on a shakti mailing list. Many of these concepts have been around with k (kdb) and some other APL-like languages for some time. But Postgres will give these concepts a much broader appeal.
- yrashk 3y agoI am aware of kdb, J and others in the space! Love their brevity and ingenuity. As you rightly point out, the idea behind Omnigres is to make a lot of these concepts available broadly.
- dvasdekis 3y agoI was interested in how you do authentication, but currently the 'Omni_web' link & readme is missing. Suggest you could use the pgjwt[0] approach for this for simple logins in the short term, but supporting OpenID Connect would be a larger engineering effort. [0] https://github.com/michelp/pgjwt https://github.com/michelp/pgjwt
- dvasdekis 3y agoAlso forgot to say, love the project and love the objectives! Allowing Postgres to do it all means so much from a server management perspective - imagine not having to manage any redundancy/performance/analysis outside of the health of the Postgres box - fantastic :)
- yrashk 3y agoThank you for your kind words! The original dream was indeed a "one box" approach, even when they scale horizontally and to the edge. Perhaps a more accurate depiction of this is a "unified interface".
- yrashk 3y agoThe current approach employed by Omnigres users is good old sessions (since latency to the database is non-existent) and omni_txn's transactional variables (https://docs.omnigres.org/omni_txn/variables/ https://docs.omnigres.org/omni_txn/variables/) to store session-related data. This way we don't need to handle the difficult parts of JWT (forced expiration, etc.) and the mental model becomes rather simple.
- rtpg 3y agoThis sort of stuff (and a lot of other popular things) highlights something that I've felt for years: we are still so far away from a good ops story as an industry. Why would someone use this? Because they only want to deal with "one thing" in production. Dealing with multiple stacks is annoying. People show stuff like kubernetes as a way to handle multiple stacks. That stuff is also complicated. There is still a huge opportunity for a methodology (accessible to non-operators) to get systems in production easily, where we wouldn't feel the need to force it all into these kinds of models. Having said all of that, this is pretty fun looking!
- axelthegerman 3y agoI hear you about the need for simplistic hosting. However I doubt shoving everything into the DB will get us there - besides some hardcore PG enthusiasts like OP & co. Sqlite together with the application layer of your choice seems to make much more sense to me, but hey we don't have to all like the same stuff (better we don't). Also without reading too much in detail but I wonder how testing/monitoring etc would be supported
- ako 3y agoWhy do you think shoving everything into a DB will not get us there. What about a scalable database platform like snowflake?
- samus 3y agoThe language probably won't be perceived as "sexy" enough compared to, i.e., full-stack JavaScript. Most people don't really like SQL and aren't that good at it. Hence the continued popularity of ORMs that support the often-seen pattern of bulk-loading everything into the application server for processing instead of using a simple UPDATE statement.
- yrashk 3y agoWe're actually first-classing a number of languages and ecosystem support precisely so that people can use their languages of choice and not just SQL or Pl/pgSQL.
- a-b 3y agoOmnigress developed a fresh view of the otherwise stalled industry approach to deploying and running software. Edge computing is a way to go!
- axelthegerman 3y agoI didn't read anything about edge computing in the project readme. Deploying a single instance would be the opposite of that. Unless you mean edge as opposed to mainstream?
- yrashk 3y agoIt's not currently spelled out in the readme, but our approach does lend for straightforward shipping of complete systems (data + code) to the edge because it's effectively just a replication of the database. We're working on further primitives that facilitate such an operational model. We believe that a practical edge [backend] requires the presence of data next to the code, which is precisely what Omnigres promotes.
- canadiantim 3y agoDoes this mean omnigres could be used offline on mobile devices? Similar to an sqlite instance?
- yrashk 3y agoWe are primarily talking about the backend side of this story at this time. We'll see what the future will bring, as the real edge of computing is in user's hands. There's a keen interest to get there for Omnigres applications, but the shape of that is still somewhat vague.
- Inviz 3y agoI did something similar once, but integrated everything RESTful into nginx which connected to postgres directly, which ran triggers with JS. What I didnt like about it is being bound by max connections to postgres, and then blocking postgres thread when running javascript in it. The parallelization story just wasnt competitive enough. Is this something that omnigres addresses?
- yrashk 3y agoWe don't really use connections but a pool of workers to process requests. Having http server tightly coupled with the database server makes for a responsive combo. We are still blocking workers, but prototyping improvements that will alleviate this.
- Inviz 3y agoOh it's Yuri Rashkovskii, one name I actually remember from the olden times. I think you have a good project on hands, happy to see you could establish it as a tech startup. Over the years my mind keeps wandering into this place where an apps are much tigther integrated with the database. Where a full class of engineering tasks (and bugs caused by them) is eliminated completely. I'll be checking your progress.
- yrashk 3y agoThanks! I am pleasantly surprised my name rings the bell for some :)
- throwawaaarrgh 3y ago[flagged]
- yrashk 3y agoActually, some of the feedback that we received from fairly close to the source, was that Postgres as an application platform of a sort was the original vision for it. But I can see how this work can be perceived through your lense.
- dubesar 3y agoThis is great
- yrashk 3y agoThank you for posting Omnigres on HN! Just a quick note to the readers: we're still in the early days, in a pre-release mode. A lot of things work, but not everything is there yet and there are some bugs (we know some for sure!). A lot to come as we progress: first-class Python support, CLI, schema & package management, etc. Our initial users are using it with our support and our timeline is sometimes advised by their milestones. That said, happy to see you try it out and please join our Discord (https://discord.omnigr.es/ https://discord.omnigr.es/) if you want to chat.
- vira28 3y agoMostly an observation. Many thought RDBMS is dead (at least 10 years ago), well here we are. Dynamic languages were all the craze and now pretty much all those languages added type safety (Typescript, recent Python versions) Looks like what goes around comes around. Folks like Martin Casado noticing the trend [1] and explained what was the challenge [2] [1] https://twitter.com/martin_casado/status/1705969880225513815 https://twitter.com/martin_casado/status/1705969880225513815 [2] https://twitter.com/martin_casado/status/1706045633571041731 https://twitter.com/martin_casado/status/1706045633571041731
- jordanthoms 3y agoThis is an interesting project, but I'd be unlikely to use it, for a couple of reasons: - In my experience the performance of the stateful DB server has been the biggest bottleneck when scaling - it's much easier to scale the stateless application servers which sit between end user requests and your DB in a traditional architecture. So usually I'm wanting to move as much work as possible away from the DB in order to squeeze the most out of it before needing to shard the DB or move to a different solution, rather than moving more responsibilities into the DB. - It's frankly pretty scary to load a C extension into postgres which is opening ports and parsing requests etc - bugs in it could crash the server or open security holes, and if you were able to exploit a vulnerability you'd be able to grab any of the data in the DB and easily exfiltrate it. This would be less of an issue if using this for an internal service which isn't directly exposed to the internet, but it still could make it easier for an attacker to escalate their access. (This isn't a 'it should be rust' comment really, even if this was in rust it would still be pretty worrying). - Even if think you only need simple CRUD actions, over time you tend to need more and more logic around those actions. Authentication, verification, triggering processes in other systems, maybe you make schema changes and need to adapt requests from old clients, etc. It's really nice to have a more heavyweight application server where you can implement that logic - I'm pretty skeptical that row level permissions, triggers etc will be able to cleanly handle all those as you add new requirements over time. This applies also to other tools for more directly exposing your DB ( e.g. PostgREST ). IMO starting off using a tool like this is really just setting you up to have to do a pretty painful rebuild later on. Am I missing something here, maybe I have misunderstood the intended use case?
- lovasoa 3y agoThese are good points, but I'd like to nuance some of them. Disclaimer: I work on an SQL web application builder [1] that shares a lot of the philosophy behind Omnigres. - about scaling: you have to get very far before saturating a single postgres server. A lot of applications certainly do get to that point, but most don't. And once you get there, scaling postgres is definitely more work than scaling a stateless service, but it also gives you a lot more in terms of performance, reliability and further scalability. - about C being scary: as a rust afficionado, I am not going to contradict you. But postgres itself is already C, and @yrashk is not just any C developer. Notably, he contributes to postgres itself. - about managing complexity: postgREST, Omnigres, hasura, SQLPage and other tools that simplify building directly on top of the database never require exclusive access to the database. You can always put some of the complexity outside if you need to, when you need to. [1] https://sql.ophir.dev https://sql.ophir.dev
- tacone 3y agoLove this. The problem I have with this approach, though, is SQL itself. Until we get simple things such as inserts and unions based on field name rather than field order, there are simply too many pitfalls for me.
- Twisell 3y agoPlease elaborate because that sentence make barely no sense for modern SQL engine such as PostgreSQL. A good ressource to start exploring would be this site with compare features between vendors: https://modern-sql.com/ https://modern-sql.com/
- FridgeSeal 3y agoI believe what they’re talking about is being able to pass something like a struct of key-value pairs, which correspond to column names in the inserted table. This would let us pass straight records/maps/whatever you want to call them to the db, and have the correct insert performed, instead of the genuinely horrible string-interpolation-hope-you-got-the-right-number-of-params-in-the-right-order-and-too-bad-if-you-didn’t dance that we currently have. The interface we have with databases could be so much better.
- lovasoa 3y agoI think they were talking about syntax like INSERT INTO t(a,b,c) VALUES (1,2,3) I agree that something like INSERT INTO t VALUES a=1, b=2, c=3 would be much more readable.
- yrashk 3y agoInterestingly enough, I've toyed with augmenting SQL to make it more useful. I remember doing an experimental improvement allowing to do FROM where SELECT what syntax. I agree there's value there. Omnigres is able to intercept query expressions to do the augmentation.
- deep_sea03 3y agoOh wow, this is great. Congratulations Yurii. So i Tried omnigress to build a simple api that does few db calls a month back to mock real world scenario. And damn, the numbers were quite thought provoking. Comparing those numbers with a similar api built using fastapi and asyncpg, there was if i remember correctly at least 4x to 5x throughput increase. Even though the project is in quite nascent stage, the idea is promising. This tackles the concept or should i rephrase, pain of having multiple platform to run a service and the needed expertise that is necessary. If the scaling part and dev convenience can be fully sorted, and also the security aspect like many have highlighted, this could very well have the potential to change the game.
- vira28 3y agoThose numbers sound similar to what the author benchmarked in the past. https://yrashk.com/blog/2023/02/16/what-happens-if-you-put-http-server-inside-postgres/ https://yrashk.com/blog/2023/02/16/what-happens-if-you-put-h...
- returningfory2 3y ago> Comparing those numbers with a similar api built using fastapi and asyncpg, there was if i remember correctly at least 4x to 5x throughput increase. I wonder to what extent this improvement comes from not using Python, as opposed to using this alternative solution? For example after Python to Go migrations it’s common to see this kind of speed up I believe.
- fulafel 3y agoThey might have used Python still - the Omnigres page lists Python support, and PG has had PL/Python for a long time.
- yrashk 3y agoWe are working on first-class support for Python indeed (and other languages like JavaScript) An important thing here is that we see Omnigres as a polyglot runtime with a database inside (Postgres) and we want people to use languages they prefer.
- revskill 3y agoHow about background job and queueing ?
- milutinovici 3y agoThis project seems to have a lot of overlap with PostgREST, which I really like. What are the differences in approach/goals?
- lovasoa 3y agopostgREST is a traditional web service that opens tcp connections to the database. Omnigres allows you to run your own custom http request handling code inside the database.
- ezekiel68 3y agoAt some point during the dot-com era, Microsoft toyed with adding an HTTP server to its SQL Server product to enable similar development paradigms. My recollection is that this was a disaster on several fronts, not the least of which was that successfully breaching the HTTP server gave an attacker direct access to the database. This (and other considerations) make a good case for the kind of separation-of-concerns which would avoid entwining API service with DB access. I read the README, but it focuses on the practical aspects of getting the solution to work, not its philosophical justification. edit: When I say "disaster" for the MS SQL Server attempt, I don't believe anyone ever experienced a breach, etc. But, as I recall, there were no large customers (MS caters to the larger corporate crowd) with IT deparments willing to risk exposing their DB servers to the "DMZ zone" of public Internet access (or only 1 layer past is, such as behind a proxy). So from that standpoint the disaster was providing a solution nobody (with money and corporate experience) wanted. I guess for hobbies or Silicon Valley MVPs, the concept might have some legitimate appeal.
- dmitry-vsl 3y agoThere was a similar tool developed by Oracle called mod_plsql, which served as an Apache web server module. As far as I remember, it allowed you to configure a mapping from a URL to a stored procedure. These stored procedures could receive HTTP request parameters and return HTML content. I've always felt that this approach is the right way to build applications. Application servers seemed like an unnecessary layer to me, essentially serving as intermediaries that merely passed data from the database to the browser. In the past, they played a more critical role in generating HTML, but nowadays, application servers are primarily used for handling APIs. Consequently, they often lack meaningful tasks to justify their existence. Having your code closely integrated with the data also has the benefit of improving performance.
- deleted 3y ago[deleted]
- sixbrx 3y agoYes I believe this is often used as part of Oracle's APEX (Application Expresss) tool which has similar goals to Omnigres. It's used to put together internal business CRUD and reporting apps very quickly for some orgs I've worked for.
- mkl95 3y agoThis architecture has lots of potential for prototyping and internal tools. I would think twice before exposing some app built with it to the world, though.
- zubairq 3y agoHaving been an Oracle Apex developer I can't help but compare this to Apex. Does Omnigres have a UI editor?
- yrashk 3y agoIt does not have any UI yet. Something we haven't thought much about yet. Technically, feasible, just need to have a good story to drive a good experience there.
- cpursley 3y agoThis is the sort of thing that gets me really excited. What’s the migration story? Migrations are what always trip me up, especially when there’s dependent logic.
- yrashk 3y agoCurrently, we have an early version of `omni_schema` [1] that allows traditional incremental migrations and in-place migration of certain objects. However, we're not quite satisfied with this and working on a more sophisticated system that would allow us to derive incremental changes where possible, lint schema, load application code with the right dependencies on types and other functions where necessary. [1] https://docs.omnigres.org/omni_schema/reference/ https://docs.omnigres.org/omni_schema/reference/
- jarpineh 3y agoCouple of questions (with notes): Yes, I'd really like to write code next to the db with more suitable (for task) languages like Python. But at the moment SQL alone is supported..? Could one connect with Jupyter notebook somehow and have a REPL like experience with Omnigres instance? Also, this [1] seems intriguing. How do containers connect to the db? What would the performance differences to the "internal" approach? Is this feature more like Lambda or for long running processes? Or something else? In any case very interesting. Thank you! [1] https://github.com/omnigres/omnigres/tree/master/extensions/omni_containers https://github.com/omnigres/omnigres/tree/master/extensions/...
- yrashk 3y agoYou can use any language Postgres supports or will support to write your logic. We are adding first-class Python support right now. It's already possible to extrsct stored functions from decorated functions and their type hunts and we're working on providing standard Python APIs like DBAPI, WSGI support, etc. We have a branch on which we ran Flask applications inside Postgres. As it matures, it will be merged and documented. As for the containers, they receive the database credentials over env variables currently. The performance characteristics of such applications aren't as good currently. The intended use case for this is third-party apps and legacy pieces of own applications. Please keep in mind that this extension hasn't received much updates in the past couple of months but there will be upcoming changes to simplify ot a lot and provide more functionality. We also have a future experimental goal of going all the way through a runtime like crun to remove moving pieces.
- jarpineh 3y agoThank you. I’ll be sure to check this one out when the Python supports lands. Perhaps not same level as containers, but WASM runtime could be a powerful addition here. Or a container running said WASM code. I’m thinking more about untrusted client code for ad hoc data analysis and such. One additional question: is Postgres foreign data wrappers going to be supported?
- IshaanAdarsh 3y agoAmazing