4 ms·
I've been wondering for a while about the practicalities of using postgres as a replacement or near-replacement for a traditional backend - does anyone have any
by jonstaab 9y ago
I've been wondering for a while about the practicalities of using postgres as a replacement or near-replacement for a traditional backend - does anyone have any opinions on how far it's wise to go with this (postgres-only? authentication? direct connections from the client?) and/or how feasible that kind of thing is?
- nimchimpsky 9y agoA postgres backend is traditional isn't it ? Am I that old that a relational database no longer traditinol, everything has come back round again now document stores are tradtional, and relational is new and trendy ?
- dpwm 9y agoDerek Sivers wrote in 2015 about merging more of the business logic into postgres, including generating JSON in postgres [0]. I've personally used this approach and found it quite powerful. There may be downsides to that approach if somebody else is going to be maintaining the project and doesn't understand postgres too well. There's also PostgREST[1], which exposes a REST API to a postgres database and has been discussed on HN before [2,3] [0] https://sivers.org/pg https://sivers.org/pg [1] https://postgrest.com/en/v4.3/ https://postgrest.com/en/v4.3/ [2] https://news.ycombinator.com/item?id=13959156 https://news.ycombinator.com/item?id=13959156 [3] https://news.ycombinator.com/item?id=9927771 https://news.ycombinator.com/item?id=9927771
- Mongus 9y agoI haven't put anything into production yet but I've been playing with PostGraphQL (https://github.com/postgraphql/postgraphql https://github.com/postgraphql/postgraphql). It automatically creates a GraphQL endpoint based on your schema using Node. You define your application logic as functions in your schema. It also can handle authentication for you. I'd definitely recommend checking it out.
- doh 9y agoNot sure that I would do authentication directly in PG (although it has many different methods [0]), I think it's still doable. You could move whole oAuth to PG through PL/SQL (maybe there is an extension for it?). I would also encourage to introduce a proxy in between (e.g. pgbouncer [1]) to help you to handle connections and deal with the authentication. We're using PG as "the brain" of our service since 2014 and it never failed us. The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. However once you have functional logic, you can write fairly lightweight application layer around it and change it as often as you want. You can also scale this setup almost indefinitely thanks to extension like citus [2] and still keep your application layer fairly thin. [0] https://www.postgresql.org/docs/current/static/auth-methods.html https://www.postgresql.org/docs/current/static/auth-methods.... [1] https://pgbouncer.github.io https://pgbouncer.github.io [2] https://www.citusdata.com https://www.citusdata.com
- deleted 9y ago[deleted]
- combatentropy 9y ago> Not sure that I would do authentication directly in PG I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache. db.exec('set role to $1', req.remote_user); Apache 2.4's form-based authentication makes this attractive. > The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. Isn't there the explain command (https://www.postgresql.org/docs/current/static/using-explain.html https://www.postgresql.org/docs/current/static/using-explain...) and the \timing option for the command-line client, psql (https://www.postgresql.org/docs/10/static/app-psql.html https://www.postgresql.org/docs/10/static/app-psql.html)?
- doh 9y ago> I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache. I figured. If you isolate the data well enough, it could work. I'm always paranoid when it comes to DB. > Isn't there the explain command and the \timing option for the command-line client, psql? Yes, there is. This however doesn't help you with triggers and UDF, which is how you usually create the logic in the DB.
- pritambaral 9y agoThis depends a lot on the usecase. You have to consider things beyond the simple serving of requests, things like rate-limiting, caching, logging, event tracking, for example. About a year ago I designed a system for a client that was just Postgres with a Go http frontend. Go was used to handle http requests and responses, to translate the API from http to postgres functions/views, and serve the response straight from postgres. Even authentication and access control was handled using postgres's role system and RLS was looked into (and found to be viable for when needed). Postgres was a good choice because the project involved mainly a lot of data wrangling. Of course, I could do it this way because it was a completely internal system with a fixed number of users, and the burden of maintenance was minimal. Main reasons for not going this direction, IMO, would be: 1. Developer proficiency, while building and maintaining. Far more people know Python, Ruby, JavaScript etc. than SQL. Far more people know how to think in imperative programming than to think in data models. Of course, one can write Postgres-hosted imperative programs as functions (in languages ranging from plpgsql to javascript), but at that point using the same language in a node or django app is much easier. 2. Unsuitability. Some things are just not suited to be run in a database. Realtime multiplayer games, multiple-source data compositing APIs, view rendering etc. come to mind. The truth remains, that general purpose programming languages (or special purpose, where the purpose is serving, languages) and environments have more possibilities than an environment that grew around dealing with data.