[traceroute66]

I did a search in that post for "function", zero results.

Unimpressive. Not even the most cursory of discussion of stored functions ?

Given that many startup's Postgres instances will no doubt be backing some web-ui or app that takes untrusted input, surely they could have at least had a brief discussion about how stored functions can help against SQL injection attacks ?

Not only that but it means you have to think, it prevents devs just writing their own random queries.

Also zero mention of `text`, which is highly encouraged in Postgres instead of the silly old `varchar(255)`

[abelanger]

OP here, I appreciate the feedback! I tried to focus on things which could take down your database, so things like particularly slow reads and writes, autovacuum settings, reducing lock contention, particularly focused on cases that I've seen. There are lots of things that I left out which would belong in a general user guide.

We're heavy users of stored functions because we're (perhaps overly) reliant on Postgres triggers, which can improve performance by reducing network round-trips but are fairly risky because they're difficult to monitor and observe.

[yladiz]

Stored procedures and SQL injection are orthogonal concerns. You can have a parameterized query using PREPARE without needing to resort to stored procedures, and many database drivers or wrappers help you with this by making you provide a string with something like $1 and then the values which are sanitized.

Stored procedures are useful in cases such as annoying data type conversions (for example, before the newer ltree versions, its path couldn't accept hyphens and so if you were using UUIDs you needed a way to convert the UUID to a ltree compatible representation) or when you want to write a function that is used by a constraint, but it's not something I would generally reach for and certainly not for SQL injection reasons.

[chasd00]

> it prevents devs just writing their own random queries.

which in turn makes every single change in schema or logic dependent on a DBA making the change in Postgres balanced against their lunch schedule. Good for DBA job security but terrible for productivity and sanity.

[Tostino]

That is entirely a design choice if you make your DBA responsible for that type of thing.

I have all my database functions version controlled and deployed by liquibase on every release (along with any other migrations that need to go out).

They are treated like code like any other piece of code in my codebase, get changed along with the rest of the application as necessary, and are deployed automatically with the rest of my application.

DB functions / stored procedures are the right tool for certain jobs. When they are the right fit, they can save your ass performance wise.

[solumos]

> devs just writing their own random queries.

I've spent a lot of time writing my own random queries. I don't know that I've ever written a stored function.

[traceroute66]

> I've spent a lot of time writing my own random queries. I don't know that I've ever written a stored function.

And I've spent a lot of my working life cleaning up after people who write random queries who then start blaming the database for being "slow" and insisting they need some sort of over-engineered Redis caching layer or whatever.

100% of the time the database is perfectly fine, but the query is slop.

Not saying you are one of them, but you would very much be in the tiny minority if you are not. ;)

[solumos]

I’m in the tiny minority.

[whalesalad]

you are missing out

[stackskipton]

Stored Functions/Procedures tend to make database into monolith with API that everyone calls, with endless screaming when it gets too big and any schema change takes days to accomplish.

That's something that OP didn't discuss is shared databases vs services owning their own. When you are startup, it's really tempting to have services reach into database and skip API call.

[brianhama]

Stored procedures can be such a performance hack. Removing the need for multiple round trips to the database can make a huge difference.

[fabian2k]

You do not need stored procedures or functions to prevent SQL injection. Any Postgres client library from the last decade or two supports parametrized queries, and that's enough. Odds are, most people will use an ORM anyway, which also avoids SQL injection.

In most situations I'd try to avoid using stored procedures. Unless you're all in on them, the effect will be that it hides some logic from the developers since it is not in the main part of the codebase.

[traceroute66]

> it hides some logic from the developers

I do not buy this argument.

Its called a documented function.

The developers know the function's inputs and outputs and what it does.

That's all they should need to know.

Its no different to functions in the libraries of whatever programming language you are using.

Devs just do their coding based off the function signature and docs. They know what goes in, what comes out and what the function does.

How many developers do you know who've gone back and read the source code of the function ? Assuming its open-source anyway and not a OS API.

[fabian2k]

It's more of a problem with triggers. But in the end you're switching languages at that point, and devs that have no problem reading your backend language will not necessarily be good at reading stored procedures. Of course depends on how complex you make them.

And of course devs read the content of functions they call. Unless it's a well written library used by many different people, odds are the function isn't documented well enough and has quirks that force you to understand in more detail how it works. This is not external library code, it's still part of your application.

[traceroute66]

> devs that have no problem reading your backend language will not necessarily be good at reading stored procedures

But again ... why do they need to ?

If you are a developer and you want to convert a string to upper case, you just use `strings.ToUpper($foo)` or whatever based on the signature.

Again, its no different with stored procedures.

Dev wants to add a new user ? They look through the stored procedure headers and see a `add_user($foo,$bar)` signature and code a call against that.

Do they need to know that in the background `add_user($foo,$bar)` inserts into table `users`, `groups` etc. ? No.

Infact it makes the dev's life simpler because instead of sending multiple calls themselves (or perhaps forgetting one or two), they just called the stored procedure.

That is my problems with devs who think they are some sort of geniuses that need absolute access to the database because they think nobody can write SQL queries as well as they can ... too many times they come running to me complaining the database is "slow" when in fact it is their sloppy SQL queries that are slow.

[traceroute66]

> people will use an ORM anyway

Even worse !

Don't get me started on people who treat databases like a black-box dumping ground and insist they must have "portable schemas".

[raverbashing]

The last thing a startup has time to do is stored functions

And if they "do have", they're not spending enough time with their service-market match

[traceroute66]

> The last thing a startup has time to do is stored functions

If they have time to write SQL queries, they have time to write stored functions.

Its really not that difficult and it certainly does not take a substantial amount of time.

[raverbashing]

No

They have the time to write SQL queries in their code

They don't have time to (or better, shouldn't) materialize them as a stored function in the DB

"Oh but your CI/CD should automatically..." Let me stop right there

The time they spend with this can be better used to ship and to improve their SW to customers, not with yak shaving

[traceroute66]

> They don't have time to ...

Which is why they end up spending time on mea-culpa "we take your data security seriously, but clearly not seriously enough" emails when they inevitably get pwned by a completely predictable and avoidable SQL injection attack.

The sort of startups you describe are jokes that barley take security seriously, let alone know what a pen-test or code audit is, let alone actually do them on a regular basis.

[euiq]

You can do safe parametrization with PREPARE, you don't need CREATE FUNCTION. Don't most PostgreSQL libraries handle such concerns for you, anyway?

[raverbashing]

You don't need a stored procedure to use parameters with the query lol