News for hackers. Stuff that matters.
Re:Let Postgres Do the Work (2013) by azernik on Saturday November 26, 2016 @04:08PM (#13045866)
Oh absolutely. I've tried doing complicated logic with (MySQL) stored procedures. It's painful. Definitely send the queries from a sane programming language.

One way to think about this is that you just want to take control back from the ORM of the exact database queries sent down the pipe. Which, BTW, is why every ORM worth its salt has functions for dropping down to plain SQL - teach that as best practice for doing computation in SQL queries, not stored procedures.

Re:Let Postgres Do the Work (2013) by phereford on Saturday November 26, 2016 @04:14PM (#13045896)
Most ORMs will not do the level of aggregations you want or need without writing raw SQL within your ORM. If you are already writing raw SQL in your ORM for aggregation level computations, you might as well create a stored procedure, make it IMMUTABLE (if you can) and gain those performance benefits.

At the end of the day, I would just use the tool that has the most comfortability with the team on the project. You can obviously start with an ORM, but there may come a point in time where performance needs a little boost.

Re:Let Postgres Do the Work (2013) by azernik on Saturday November 26, 2016 @04:26PM (#13045958)
What are the real performance benefits of stored procedures over sending the hand-written queries over the wire through your ORM? I suspect small to none, and at the cost of having code that lives in the database rather than in version-controlled code.
Re:Let Postgres Do the Work (2013) by gaius on Saturday November 26, 2016 @04:31PM (#13045979)
code that lives in the database rather than in version-controlled code

If you think you can't version control stored procs, then you are doing something badly, badly wrong.

What's the difference between a "script" which is a text file on a filesystem at the end of the day, versus a "stored proc" which is a block of text in a database, loaded off a file in a filesystem?

Re:Let Postgres Do the Work (2013) by jstanley on Saturday November 26, 2016 @04:38PM (#13046025)
The "script" gets deployed simply and reliably using your existing tooling, and stays in sync with the surrounding code.
Re:Let Postgres Do the Work (2013) by tokenizerrr on Saturday November 26, 2016 @04:57PM (#13046113)
Replace the stored procedure from version control at deploy time.
Re:Let Postgres Do the Work (2013) by azernik on Saturday November 26, 2016 @05:17PM (#13046231)
And then you have to make sure your deploys are atomic so that the app code and stored procedures don't get out of sync on failed deploys...

Or your stored procedures now become one more thing that has to be backwards compatible, in addition to your DB schema...

I've run into all of these problems with stored procedures, and they SUCK. Good deployment tooling can solve them, but it's a lot of effort for questionable reward.

Re:Let Postgres Do the Work (2013) by gaius on Sunday November 27, 2016 @04:05AM (#13048401)
a lot of effort for questionable reward

It's a small amount of effort for a thousand-fold increase in performance and not having to re-implement the same functionality in every app and in every language that connects to that DB. YMMV.

Re:Let Postgres Do the Work (2013) by icebraining on Sunday November 27, 2016 @04:45AM (#13048492)
The argument was between using SQL in stored procedures or SQL in queries sent by the application, so you don't have to re-implement anything (just share the SQL queries between apps), and I very much doubt you'll get a thousand-fold increase in performance.
Re:Let Postgres Do the Work (2013) by spdionis on Monday November 28, 2016 @10:29AM (#13055982)
Thousand-fold increase in performance by shaving off network and query compilation time?
Re:Let Postgres Do the Work (2013) by kofejnik on Saturday November 26, 2016 @05:47PM (#13046403)
this absolutely mirrors my experience
Re:Let Postgres Do the Work (2013) by grzm on Saturday November 26, 2016 @04:38PM (#13046023)
One performance benefit of stored procedures in Postgres is that you can perform a multi-statement transaction in the stored procedure, saving the network overhead of multiple round-trips between the application and the database. This may not be a common occurrence for many applications, but it is a legitimate performance benefit when the need arises.
Re:Let Postgres Do the Work (2013) by anarazel on Saturday November 26, 2016 @04:57PM (#13046119)
Note that the network protocol allows pipelining for multiple statements (including transaction control ones), too. Unfortunately only few client libraries (e.g. pgjdbc) make use of that :(
Re:Let Postgres Do the Work (2013) by azernik on Saturday November 26, 2016 @05:16PM (#13046221)
I don't know about Rails and ActiveRecord, but I can do this in Slick.
Re:Let Postgres Do the Work (2013) by kofejnik on Saturday November 26, 2016 @08:09PM (#13047100)
you can do multi-statement and nested transactions even in Rails
Re:Let Postgres Do the Work (2013) by mst on Saturday November 26, 2016 @04:59PM (#13046129)
If you're already version controlling your schema, the CREATE OR REPLACE FUNCTION call lives in whatever's version controlling your schema.

If you're not already version controlling your schema, you should seriously consider starting whether you're using sprocs or not.

Re:Let Postgres Do the Work (2013) by feld on Sunday November 27, 2016 @09:04AM (#13049165)
You can literally do version control in the database as well. Upgrade and downgrade your entire scheme at will. Unfortunately the only open source software I'm aware of that does this is Archiveopteryx

http://archiveopteryx.org/db/mailstore

http://archiveopteryx.org/db/schema

Re:Let Postgres Do the Work (2013) by phereford on Saturday November 26, 2016 @04:33PM (#13045994)
Honestly, it's yak shaving at that point. Like the article pointed out, you can create an index on a stored procedure that is IMMUTABLE. That will have some effect on the performance of the query.

There are other patterns you can use to track via version control the changes for your stored procedure.