Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Mastering PostgreSQL in Application Development

masteringpostgresql.com

Post reply on HN
Print view View original

41–50 of 101 posts

Re: Mastering PostgreSQL in Application Development

#41

Post by doh »

jonstaab wrote: post #26

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?

Not 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....

[1] https://pgbouncer.github.io

[2] https://www.citusdata.com

Re: Mastering PostgreSQL in Application Development

#42

Post by pritambaral »

jonstaab wrote: post #26

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?

This 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.

Re: Mastering PostgreSQL in Application Development

#43

Post by pc86 »

Looks like a pretty rote implementation of Authority[0], but with a more modern UI. I wish we used PostgreSQL at my day job, this looks pretty interesting! I particularly like the idea of including the Docker container at the highest price point.

Edit: Looks like email sign ups are through ConvertKit, which is Nathan's company. Definitely Authority!

[0] http://nathanbarry.com/authority/

Re: Mastering PostgreSQL in Application Development

#46

Post by davidkuhta »

tomca32 wrote: post #45

I bought the dead tree edition. The FAQ page says that I should send author an email with the receipt to get the pdf and mobi version; however, I can't find the author's email address anywhere. Any clues?

disappointed /u/tomca32... easy as: SELECT email FROM authors;...

dim [at] tapoueh.org per his github: https://github.com/dimitri

Enjoy your day.

Edit: revised @ to [at] to reflect format in author's FAQ update.

Re: Mastering PostgreSQL in Application Development

#47

Post by totalrobe »

esaym wrote: post #39

Looks interesting, wish I could "preview" before buying. Thankfully before venturing out on my own in the web development world, I was a member on a small business analytics team were we would all regularly write 3+ page SQL queries for reports. Sometimes they's take days to run. It was awesome and horrifying all at once but a good experience nonetheless and has allowed me to do what I do today.

There is a 1 chapter preview available, not even halfway down the page.

Re: Mastering PostgreSQL in Application Development

#50

Post by [deleted] »

esaym wrote: post #39

Looks interesting, wish I could "preview" before buying. Thankfully before venturing out on my own in the web development world, I was a member on a small business analytics team were we would all regularly write 3+ page SQL queries for reports. Sometimes they's take days to run. It was awesome and horrifying all at once but a good experience nonetheless and has allowed me to do what I do today.

[deleted]
Post reply on HN