Re:Ask HN: Have you ever used PL/pgSQL – the PostgreSQL prog...
by kumarvvr on Monday September 03, 2018 @12:43AM
(#17900377)
Thanks for the detailed reply.
What is your observation on the performance of such a system, when compared to systems where the middle language has all the logic and the database is used just as a dumb datastore?
Also, what are your observations on the scalability of such a system you have mentioned above?
Re:Ask HN: Have you ever used PL/pgSQL – the PostgreSQL prog...
by combatentropy on Monday September 03, 2018 @02:07AM
(#17900686)
> performance
Performance is usually better in the database, because the data is local. However, like any language, there is more than one way to say it in SQL. When the query is complicated, with joins and subselects and aggregates, I have made it literally a thousand times faster by rearranging and rewriting things. I'm not even talking about adding indexes yet.
> scalability
I think 99% of applications could scale just fine with Postgres.
Now that doesn't mean PL/PGSQL, because I like to use the simplest tool that does the job. From simplest to most complex, you can:
1. Put it in the table definition, things like: data type, null or not null, unique, foreign key, and check constraints:
2. Put it in a database view definition. If your SQL is getting long and complex, save it to a view, so your application doesn't have a tower of SQL in its source code. Views can also warn you when you're changing tables in a way that will break something ("You can't drop this column because this view depends on it, you can't change this column's type to that because a view depends on it, etc."). Of course you can drop and recreate the view after changing the table, but at least it warns you. Other times, like if you rename a column, I think the view updates automatically. None of this would happen automatically if the SQL is hardcoded in your Python script.3. Put it in an SQL function (SQL, not PL/PGSQL).
4. Put it in a PL/PGSQL function.
That's the order I take, from smallest hammer to largest.