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

Dbdev – A database package manager for PostgreSQL trusted language extensions

database.dev

Post reply on HN
Print view View original

41–50 of 57 posts

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#41

Post by nijave »

mike_hearn wrote: post #32
nijave wrote: post #17

Earlier quoted context omitted.

Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.

This is also discussed in the talk, which I hope will be on YouTube soon enough (it was live streamed). DB connections are heavier than web server connections, in particular in terms of memory (when using postgres, not sure about mysql). Multiplexers like pgbouncer can address this, but then you lose a bit of the deployment simplicity. Fortunately RAM is pretty cheap nowadays, you can configure the DB to disconnect i…

It seems like this uses native Postgres security/roles which I think would prevent a connection pooler from working (unless you held open 1 connection per user)

Ultimately it'd be nice if Postgres could keep a hot worker pool for connections instead of forking processes but afaik there's been plenty of debate over the years and no consensus

I think high connection thrashing also eats up CPU and there's the issue of increasing load eats more RAM which reduces Postgres memory cache. We had a connection leak problem and clearing ~2600 PG connections saved something like 80-100GB of RAM

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#42

Post by giovannibonetti »

conceptme wrote: post #39
mike_hearn wrote: post #35

Earlier quoted context omitted.

See my other post for a discussion of non-user security topics. Every app user has a database user, mapped 1:1, so there are no credentials to leak. The DB credentials are the user credentials. In the demo app repository I implement open signup with email confirmation using stored procedures/a server extension. It works by creating a guest user with a well known password, but it's locked down so that the only things…

Connections are pretty expensive in PG how can you do pooling if every user connects directly?

Connection poolers like PgBouncer [1] (traditional) and Supabase's Supavisor [2] (new) come to mind.

[1] https://www.pgbouncer.org/ [2] https://github.com/supabase/supavisor

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#43

Post by mike_hearn »

nijave wrote: post #41
mike_hearn wrote: post #32

Earlier quoted context omitted.

This is also discussed in the talk, which I hope will be on YouTube soon enough (it was live streamed). DB connections are heavier than web server connections, in particular in terms of memory (when using postgres, not sure about mysql). Multiplexers like pgbouncer can address this, but then you lose a bit of the deployment simplicity. Fortunately RAM is pretty cheap nowadays, you can configure the DB to disconnect i…

It seems like this uses native Postgres security/roles which I think would prevent a connection pooler from working (unless you held open 1 connection per user) Ultimately it'd be nice if Postgres could keep a hot worker pool for connections instead of forking processes but afaik there's been plenty of debate over the years and no consensus I think high connection thrashing also eats up CPU and there's the issue of i…

I think you can use https://github.com/pgaudit/set_user combined with some pgbouncer configuration to solve that, but agreed that postgres doesn't have perfect support for this scenario. It'd be better to have a proxy that is designed specifically for this use case that knows how to drive something like the set_user extension (and better if it were to be merged upstream).

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#45

Post by benatkin »

mike_hearn wrote: post #14

At KotlinConf today I gave a talk on designing apps with two-tier architecture, where you implement your entire app without the web stack appearing anywhere at all. Instead you publish desktop and mobile apps that connect directly to an RDBMS like PostgreSQL via its native protocol, and use server extensions for any logic that is inconvenient to do with SQL. This approach might seem horrifyingly outside-the-box but h…

> This approach might seem horrifyingly outside-the-box but has a lot of advantages

You don't need to worry, there are a lot of people who are into stored procedures. If there weren't, there wouldn't be people disagreeing with them, like the author of Rails: http://web.archive.org/web/20060418215514/http://www.loudthi...

The slippery slope of liking stored procedures a whole lot means them taking over the rest of the middle of the stack, and that isn't a new idea.

I like the idea of having instant access to the database, and microservices are my favored approach.

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#46

Post by mike_hearn »

giovannibonetti wrote: post #42
conceptme wrote: post #39

Earlier quoted context omitted.

Connections are pretty expensive in PG how can you do pooling if every user connects directly?

Connection poolers like PgBouncer [1] (traditional) and Supabase's Supavisor [2] (new) come to mind. [1] https://www.pgbouncer.org/ [2] https://github.com/supabase/supavisor

Supavisor looks great. Unclear if it can be configured to use https://github.com/pgaudit/set_user though.

That said, there are quite a lot of CRUD apps in the world that don't need to support lots of simultaneous users, or where you can just add read replicas quite cheaply. Think internal apps, dashboards, etc.

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#47

Post by oliverrice »

bashinator wrote: post #44

You talking with the PostGIS folks?

PostGIS is a native extension mostly written in C. Since there is no C trusted language it would be hard to fit PostGIS into the TLE paradigm

Its less of a problem for well known and trusted extensions like PostGIS though because it comes pre-installed on most hosted providers (Supabase, RDS, etc)

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#48

Post by majkinetor »

mike_hearn wrote: post #30
GordonS wrote: post #28

Earlier quoted context omitted.

> This approach might seem horrifyingly outside-the-box I mean, this is how we routinely built client-server apps 20-30 years back. Everything old is new again! One of the big reasons so many moved away from this approach was security. Giving end users direct access to the database (even if "obscured"), gives me the shivers!

One of the things that's changed in recent years is that free databases like PostgreSQL have far more security features. You can grant fine grained privileges on individual objects like queries (via views), individual table rows, stored procedures/functions and so on. In the demo app, I use a simple approach to security: users don't have permissions to directly access any tables. Read access is via views and could us…

This is basically how its done today with tools like Postgrest.

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#49

Post by RowanH »

GordonS wrote: post #28
mike_hearn wrote: post #14

At KotlinConf today I gave a talk on designing apps with two-tier architecture, where you implement your entire app without the web stack appearing anywhere at all. Instead you publish desktop and mobile apps that connect directly to an RDBMS like PostgreSQL via its native protocol, and use server extensions for any logic that is inconvenient to do with SQL. This approach might seem horrifyingly outside-the-box but h…

> This approach might seem horrifyingly outside-the-box I mean, this is how we routinely built client-server apps 20-30 years back. Everything old is new again! One of the big reasons so many moved away from this approach was security. Giving end users direct access to the database (even if "obscured"), gives me the shivers!

Who remembers SQL in Delphi UI :)

Re: Dbdev – A database package manager for PostgreSQL trusted language extensions

#50

Post by asguy »

mike_hearn wrote: post #14

At KotlinConf today I gave a talk on designing apps with two-tier architecture, where you implement your entire app without the web stack appearing anywhere at all. Instead you publish desktop and mobile apps that connect directly to an RDBMS like PostgreSQL via its native protocol, and use server extensions for any logic that is inconvenient to do with SQL. This approach might seem horrifyingly outside-the-box but h…

I had fun with a similar concept: just access the database from the browser using nginx/TLS/lua: https://github.com/alexanderguy/pgress

If you're good with your authenticated users directly talking to the DB (which there are plenty of uses for), it's a great way to get your data into the browser.

Post reply on HN