Thread: Why are stored procedures looked on so negatively?

I've done quite a bit of reading on stored procedures recently and the
consensus seems to be that you shouldn't use them unless you really must.

I don't understand this argument. If you implement all of your logic in
the application then you need to make a network request to the database
server, return the required data from the database to the app server, do
the processing and then return the results. A stored procedure is going
to be a lot faster than that even if you just take away network latency
/ transfer time.

I'm in the middle of building a database and was going to make extensive
use of stored procedures and trigger functions because it makes more
sense for the actions to happen at the database layer rather than in the
app layer.

Should I use them or not?

I presume you're refering to trigger. Since trigger often do something automagically :) and it sometime make developer
hardto debug when something wrong since they they do not aware that there are triggers exist in database.
 

Stored procedure is OK.

CIIMW
Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT

-----Original Message-----
From: Some Developer <someukdeveloper@gmail.com>
Sender: pgsql-general-owner@postgresql.orgDate: Wed, 24 Jul 2013 01:29:14 
To: <pgsql-general@postgresql.org>
Subject: [GENERAL] Why are stored procedures looked on so negatively?

I've done quite a bit of reading on stored procedures recently and the 
consensus seems to be that you shouldn't use them unless you really must.

I don't understand this argument. If you implement all of your logic in 
the application then you need to make a network request to the database 
server, return the required data from the database to the app server, do 
the processing and then return the results. A stored procedure is going 
to be a lot faster than that even if you just take away network latency 
/ transfer time.

I'm in the middle of building a database and was going to make extensive 
use of stored procedures and trigger functions because it makes more 
sense for the actions to happen at the database layer rather than in the 
app layer.

Should I use them or not?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 07/23/2013 05:29 PM, Some Developer wrote:
> I've done quite a bit of reading on stored procedures recently and the
> consensus seems to be that you shouldn't use them unless you really must.
>
> I don't understand this argument. If you implement all of your logic in
> the application then you need to make a network request to the database
> server, return the required data from the database to the app server, do
> the processing and then return the results. A stored procedure is going
> to be a lot faster than that even if you just take away network latency
> / transfer time.
>
> I'm in the middle of building a database and was going to make extensive
> use of stored procedures and trigger functions because it makes more
> sense for the actions to happen at the database layer rather than in the
> app layer.
>
> Should I use them or not?

Personally I figure the arguments for and against are closely correlated
with where on the development chain you are, and are tied in with job
security. If you are an app developer than it is in your interest to
have code in the app, if you are a database developer in the database.
Me, I am tend to go with your argument about keeping procedures, where
appropriate, in the database for the reasons you state. In other words
an API in the database.

>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Taking an absolutist position either way is pretty blind.   What is the
purpose of the procedure?  Is it enforcing business rules?  Are these
rules that must be enforced against already existing data or are they
more akin to validation of a credit card.  How many people are accessing
your database at one time?  And most importantly, what are you best at?

Adrian Klaver wrote:
> On 07/23/2013 05:29 PM, Some Developer wrote:
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really
>> must.
>>
>> I don't understand this argument. If you implement all of your logic in
>> the application then you need to make a network request to the database
>> server, return the required data from the database to the app server, do
>> the processing and then return the results. A stored procedure is going
>> to be a lot faster than that even if you just take away network latency
>> / transfer time.
>>
>> I'm in the middle of building a database and was going to make extensive
>> use of stored procedures and trigger functions because it makes more
>> sense for the actions to happen at the database layer rather than in the
>> app layer.
>>
>> Should I use them or not?
>
> Personally I figure the arguments for and against are closely
> correlated with where on the development chain you are, and are tied
> in with job security. If you are an app developer than it is in your
> interest to have code in the app, if you are a database developer in
> the database. Me, I am tend to go with your argument about keeping
> procedures, where appropriate, in the database for the reasons you
> state. In other words an API in the database.
>
>>
>>
>
>

On 24/07/13 01:55, John Meyer wrote:
> Taking an absolutist position either way is pretty blind.   What is the
> purpose of the procedure?  Is it enforcing business rules?  Are these
> rules that must be enforced against already existing data or are they
> more akin to validation of a credit card.  How many people are accessing
> your database at one time?  And most importantly, what are you best at?

Basically what happens is an object is created in the application and
saved to the database. When the insert has completed I need to start a
process immediately based on the information in the object on another
server (Amazon Simple Message Queue to be precise).

So basically I'll have a trigger function that fires on INSERTs and does
this work. That way the action will only be performed on INSERTs that
have successfully completed and I can be sure that the trigger will
always fire.

On top of that there are a few common data structures that need to be
written to the database that would be perfect for stored procedures
since there is a little logic involved in saving them which shouldn't
really be exposed to the application developers.

On Tue, Jul 23, 2013 at 06:55:56PM -0600, John Meyer wrote:
> are accessing your database at one time?  And most importantly, what
> are you best at?

That is one of the most important questions, for sure, but there's a
close second that I'd suggest: what are the scaling properties?

For practical purposes, if you're going to do complicated data
validation and business logic in the application, you have any
significant degree of contention, and you need to write some data, the
use pattern is going to look something like this (A is application, D
is database):

    A: get some data
    D: here you go, optimistic lock value L
    A: do some work
    A: given this value, get some more data
    D: here you go, optimistic lock value L2
    A: INS/UPD/DEL data, optimistic lock value L, RETURNING data
    D: ok, here you go, optimistic lock value L3
    A: do some work
    A: INS/UPD/DEL data, optimistic lock value L3
    D: ok

And that's if none of the optimistic locks fails.  That's a lot of
round trips.  If you have 20 transactions a minute, this is just fine.
If you have 2000 transactions per second, it totally sucks: you're
buried in round trips.

In my experience, if you want your application to scale to large
numbers of users, you need to avoid application<->database round
trips.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 24/07/13 02:56, Andrew Sullivan wrote:
> On Tue, Jul 23, 2013 at 06:55:56PM -0600, John Meyer wrote:
>> are accessing your database at one time?  And most importantly, what
>> are you best at?
>
> That is one of the most important questions, for sure, but there's a
> close second that I'd suggest: what are the scaling properties?
>
> For practical purposes, if you're going to do complicated data
> validation and business logic in the application, you have any
> significant degree of contention, and you need to write some data, the
> use pattern is going to look something like this (A is application, D
> is database):
>
>      A: get some data
>      D: here you go, optimistic lock value L
>      A: do some work
>      A: given this value, get some more data
>      D: here you go, optimistic lock value L2
>      A: INS/UPD/DEL data, optimistic lock value L, RETURNING data
>      D: ok, here you go, optimistic lock value L3
>      A: do some work
>      A: INS/UPD/DEL data, optimistic lock value L3
>      D: ok
>
> And that's if none of the optimistic locks fails.  That's a lot of
> round trips.  If you have 20 transactions a minute, this is just fine.
> If you have 2000 transactions per second, it totally sucks: you're
> buried in round trips.
>
> In my experience, if you want your application to scale to large
> numbers of users, you need to avoid application<->database round
> trips.
>
> Best,
>
> A
>

Thanks for the response. Obviously since I am still in the development
stage I have no idea of the number of transactions I will need to handle
but the business has the potential to be quite popular so I'd rather be
safe than sorry and be able to handle large amounts of traffic from day one.

I think ultimately it'll be simpler this way because the system I am
developing is a quasi distributed system with lots of independent parts
that need to be able to communicate and to share data with each other.

Some Developer wrote:
> On 24/07/13 01:55, John Meyer wrote:
> > Taking an absolutist position either way is pretty blind.   What is the
> > purpose of the procedure?  Is it enforcing business rules?  Are these
> > rules that must be enforced against already existing data or are they
> > more akin to validation of a credit card.  How many people are accessing
> > your database at one time?  And most importantly, what are you best at?
> 
> Basically what happens is an object is created in the application and
> saved to the database. When the insert has completed I need to start a
> process immediately based on the information in the object on another
> server (Amazon Simple Message Queue to be precise).
> 
> So basically I'll have a trigger function that fires on INSERTs and does
> this work. That way the action will only be performed on INSERTs that
> have successfully completed and I can be sure that the trigger will
> always fire.

If you want to write a (trigger) function that starts a process on
a remote machine, there are a few points to think about:

- Should the INSERT fail if the remote process cannot be started?
  If yes, then a trigger is a good idea.
- If you code it as a trigger, be aware that the transaction
  is not complete until the remote process has been started.
  That might be a noticable delay and might affect concurrency
  negatively.

Yours,
Laurenz Albe

On Wed, Jul 24, 2013 at 2:29 AM, Some Developer
<someukdeveloper@gmail.com> wrote:
> I've done quite a bit of reading on stored procedures recently and the
> consensus seems to be that you shouldn't use them unless you really must.

I believe because most developers are not DBAs, and therefore are
scared about something they cannot control.
Placing as much logic as possible in the database is, in my opinion,
good since it will prevent any accidental (?) connection to the
database to corrupt your data. By accidental connection I mean a
developer/dba connecting to the database to change some value and
corrupting some constraint (that reside in the application) or by an
aside application or a refactoring of the application (e.g., in order
to change the application technology).
Thanks to the PostgreSQL support to many pl languages, you can even
reuse some existing application logic into the database, but it does
not mean this is the smarter choice (performance? OOP vs procedural?).
Of course, as placing business logic into the database makes the
database "code" more complex, it is required to do unit testing on the
code itself (e.g. pgtap).
Finally, another point in being "scared" of using stored procedure is
portability: a lot of frameworks claim to be portable across database
because they use a minimal survival subset of SQL features that are
almost supported on any decent database. Using a stored procedure will
make more complex the portability, since pl procedures need to be
translated from one database to another.

Luca

The information contained in this message may be privileged and confidential and protected
from disclosure. If the reader of this message is not the intended recipient, or an 
employee or agent responsible for delivering this message to the intended recipient, 
you are hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication in error, 
please notify us immediately by replying to the message and deleting it from your 
computer. Under Florida law, e-mail addresses are public records. If you do not want 
your e-mail address released in response to a public-records request, do not send 
electronic mail to this entity. Instead, contact this office by phone or in writing.

2013/7/24 Aaron Abreu <abreual@bay.k12.fl.us>:
> a NON-technical version...
>
> st.procedures and automation are great...
>
> but...
> sounds like everybody is dancing around the main theme..
> so lets say it....
> that dreaded word that developers and DBA's cring to hear...
> the one part of our job that we all hate...
>
> DOCUMENTATION !!!!!
>
> My worst fear is simply this...
> having to fix something somebody else wrote.. and they
> are not there anymore..... and the only documentation is the code itself..
> been there... on a few occasions just had to write something new...
>
>
>
>
>
>
> On Tue, Jul 23, 2013 at 7:29 PM, Some Developer <someukdeveloper@gmail.com>
> wrote:
>>
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really must.
>>
>> I don't understand this argument. If you implement all of your logic in
>> the application then you need to make a network request to the database
>> server, return the required data from the database to the app server, do the
>> processing and then return the results. A stored procedure is going to be a
>> lot faster than that even if you just take away network latency / transfer
>> time.
>>
>> I'm in the middle of building a database and was going to make extensive
>> use of stored procedures and trigger functions because it makes more sense
>> for the actions to happen at the database layer rather than in the app
>> layer.
>>
>> Should I use them or not?


The same applies to tables, triggers and so on.
The point is that you need to spend time in any case, only that most
of us feel more comfortable with tables than with procedures.
But a (modern) database is a mix of tables, grants, schemas and, of
course, stored procedures.
We need to cope with all of them.

And, please, don't top-post.

2013/7/24 Aaron Abreu <abreual@bay.k12.fl.us>:
> a NON-technical version...
>
> st.procedures and automation are great...
>
> but...
> sounds like everybody is dancing around the main theme..
> so lets say it....
> that dreaded word that developers and DBA's cring to hear...
> the one part of our job that we all hate...
>
> DOCUMENTATION !!!!!
>
> My worst fear is simply this...
> having to fix something somebody else wrote.. and they
> are not there anymore..... and the only documentation is the code itself..
> been there... on a few occasions just had to write something new...
>
>
>
>
>
>
> On Tue, Jul 23, 2013 at 7:29 PM, Some Developer <someukdeveloper@gmail.com>
> wrote:
>>
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really must.
>>
>> I don't understand this argument. If you implement all of your logic in
>> the application then you need to make a network request to the database
>> server, return the required data from the database to the app server, do the
>> processing and then return the results. A stored procedure is going to be a
>> lot faster than that even if you just take away network latency / transfer
>> time.
>>
>> I'm in the middle of building a database and was going to make extensive
>> use of stored procedures and trigger functions because it makes more sense
>> for the actions to happen at the database layer rather than in the app
>> layer.
>>
>> Should I use them or not?


The same applies to tables, triggers and so on.
The point is that you need to spend time in any case, only that most
of us feel more comfortable with tables than with procedures.
But a (modern) database is a mix of tables, grants, schemas and, of
course, stored procedures.
We need to cope with all of them.

And, please, don't top-post.

On Wed, Jul 24, 2013 at 7:52 AM, Aaron Abreu <abreual@bay.k12.fl.us> wrote:
> a NON-technical version...
>
> st.procedures and automation are great...
>
> but...
> sounds like everybody is dancing around the main theme..
> so lets say it....
> that dreaded word that developers and DBA's cring to hear...
> the one part of our job that we all hate...
>
> DOCUMENTATION !!!!!

urk.  your typical java programmer isn't any more likely to write
documentation and unit tests than your typical database developer.
sql is very at least somewhat self documenting; I'd rather trawl
through someone else's sql than just about any other language.

stored procedures also tend to be very robust, especially if you avoid
excessive use of variables and loops; they are tightly coupled with
the database transaction environment: errors roll back ALL DATA
STRUCTURES as well as the execution point to a known good place.  also
the mvcc locking model is very clean vs your typical threaded drek.

merlin

I find stored procedures to be a God-send.  The alternative, external code, is the risky, difficult and often poorer
performingapproach to the problems sp's solve.   What better way to interact programatically with your database than
WITHyour database? 

The only people that I see frown upon them don't understand them, are afraid of them, and so find ways to justify their
viewsabout them in negative terms.  I suppose that's human nature.  But once they get "turned on" to stored procedures,
theirviews change.   

As for selling sp's to them, especially if they are management, there's nothing more convincing than a demo.  And a
realgood way to demo their effectiveness is through a remote connection, preferrably across a time zone or two, where
thetask involves many (hundreds of thousands) of queries that the external script would have to do one at a time, over
thenet.  The sp would just run them inside as part of the sp call, locally, in a tiny fraction of the time.   



-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
Sent: Tuesday, July 23, 2013 8:29 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Why are stored procedures looked on so negatively?

I've done quite a bit of reading on stored procedures recently and the consensus seems to be that you shouldn't use
themunless you really must. 

I don't understand this argument. If you implement all of your logic in the application then you need to make a network
requestto the database server, return the required data from the database to the app server, do the processing and then
returnthe results. A stored procedure is going to be a lot faster than that even if you just take away network latency
/transfer time. 

I'm in the middle of building a database and was going to make extensive use of stored procedures and trigger functions
becauseit makes more sense for the actions to happen at the database layer rather than in the app layer. 

Should I use them or not?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 24/07/13 14:21, Gauthier, Dave wrote:
> I find stored procedures to be a God-send.  The alternative, external code, is the risky, difficult and often poorer
performingapproach to the problems sp's solve.   What better way to interact programatically with your database than
WITHyour database? 
>
> The only people that I see frown upon them don't understand them, are afraid of them, and so find ways to justify
theirviews about them in negative terms.  I suppose that's human nature.  But once they get "turned on" to stored
procedures,their views change. 
>
> As for selling sp's to them, especially if they are management, there's nothing more convincing than a demo.  And a
realgood way to demo their effectiveness is through a remote connection, preferrably across a time zone or two, where
thetask involves many (hundreds of thousands) of queries that the external script would have to do one at a time, over
thenet.  The sp would just run them inside as part of the sp call, locally, in a tiny fraction of the time. 
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
> Sent: Tuesday, July 23, 2013 8:29 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>
> I've done quite a bit of reading on stored procedures recently and the consensus seems to be that you shouldn't use
themunless you really must. 
>
> I don't understand this argument. If you implement all of your logic in the application then you need to make a
networkrequest to the database server, return the required data from the database to the app server, do the processing
andthen return the results. A stored procedure is going to be a lot faster than that even if you just take away network
latency/ transfer time. 
>
> I'm in the middle of building a database and was going to make extensive use of stored procedures and trigger
functionsbecause it makes more sense for the actions to happen at the database layer rather than in the app layer. 
>
> Should I use them or not?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Thank you all for the responses. I feel better about making use of them now.

Now for one final question: I was planning on using plpython2u to write
my stored procedures since Python is a language I am very familiar with.
I understand that many people would want to use plpgsql instead but
it'll be quicker for me to do it in Python.

Will there be much of a performance difference between the two at all?
Are there any very convincing arguments that will make me use plpgsql
instead or does it not really matter?

On 07/24/2013 06:31 AM, Some Developer wrote:

>>
>
> Thank you all for the responses. I feel better about making use of them
> now.
>
> Now for one final question: I was planning on using plpython2u to write
> my stored procedures since Python is a language I am very familiar with.
> I understand that many people would want to use plpgsql instead but
> it'll be quicker for me to do it in Python.
>
> Will there be much of a performance difference between the two at all?
> Are there any very convincing arguments that will make me use plpgsql
> instead or does it not really matter?

I have faced this choice also. What I found is that plpgsql tends to be
more succinct for doing database operations, probably by virtue of being
an 'extended' sql. plpythonu has developed more capabilities over time
but there is still a translation portion, Python --> SQL --> Python. You
will find that you will end up using both.

>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
<someukdeveloper@gmail.com> wrote:
> On 24/07/13 14:21, Gauthier, Dave wrote:
>>
>> I find stored procedures to be a God-send.  The alternative, external
>> code, is the risky, difficult and often poorer performing approach to the
>> problems sp's solve.   What better way to interact programatically with your
>> database than WITH your database?
>>
>> The only people that I see frown upon them don't understand them, are
>> afraid of them, and so find ways to justify their views about them in
>> negative terms.  I suppose that's human nature.  But once they get "turned
>> on" to stored procedures, their views change.
>>
>> As for selling sp's to them, especially if they are management, there's
>> nothing more convincing than a demo.  And a real good way to demo their
>> effectiveness is through a remote connection, preferrably across a time zone
>> or two, where the task involves many (hundreds of thousands) of queries that
>> the external script would have to do one at a time, over the net.  The sp
>> would just run them inside as part of the sp call, locally, in a tiny
>> fraction of the time.
>>
>>
>>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
>> Sent: Tuesday, July 23, 2013 8:29 PM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>>
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really must.
>>
>> I don't understand this argument. If you implement all of your logic in
>> the application then you need to make a network request to the database
>> server, return the required data from the database to the app server, do the
>> processing and then return the results. A stored procedure is going to be a
>> lot faster than that even if you just take away network latency / transfer
>> time.
>>
>> I'm in the middle of building a database and was going to make extensive
>> use of stored procedures and trigger functions because it makes more sense
>> for the actions to happen at the database layer rather than in the app
>> layer.
>>
>> Should I use them or not?
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
>> changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> Thank you all for the responses. I feel better about making use of them now.
>
> Now for one final question: I was planning on using plpython2u to write my
> stored procedures since Python is a language I am very familiar with. I
> understand that many people would want to use plpgsql instead but it'll be
> quicker for me to do it in Python.
>
> Will there be much of a performance difference between the two at all? Are
> there any very convincing arguments that will make me use plpgsql instead or
> does it not really matter?

plpgsql is generally the fastest/easiest language for a certain (but
important) class of operations. it runs closer to the SQL execution
engine and automatically plans all your queries (which can be a pretty
big deal for certain types of coding).  all error handling is native
(so that you don't have to catch a python exception and peek into the
sql aspects of it for appropriate handling) which is a bigger deal
than it appears on the surface.  also it's good to exercise your SQL
skills.

whichever way you go, good procedure practices generally involve
approximating scripted SQL to the extent possible.   also you should
separate routines that read from and write to the database (and try to
keep as much code as possible in the read side).  make sure to mark
routines immutable/stable as appropriate.   another underutilized
function decoration is STRICT -- it's very fast when it fires and can
save you a lot of debugging headaches.

merlin

On 24/07/2013 14:58, Merlin Moncure wrote:
> On Wed, Jul 24, 2013 at 8:31 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> On 24/07/13 14:21, Gauthier, Dave wrote:
>>>
>>> I find stored procedures to be a God-send.  The alternative, external
>>> code, is the risky, difficult and often poorer performing approach to the
>>> problems sp's solve.   What better way to interact programatically with your
>>> database than WITH your database?
>>>
>>> The only people that I see frown upon them don't understand them, are
>>> afraid of them, and so find ways to justify their views about them in
>>> negative terms.  I suppose that's human nature.  But once they get "turned
>>> on" to stored procedures, their views change.
>>>
>>> As for selling sp's to them, especially if they are management, there's
>>> nothing more convincing than a demo.  And a real good way to demo their
>>> effectiveness is through a remote connection, preferrably across a time zone
>>> or two, where the task involves many (hundreds of thousands) of queries that
>>> the external script would have to do one at a time, over the net.  The sp
>>> would just run them inside as part of the sp call, locally, in a tiny
>>> fraction of the time.
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: pgsql-general-owner@postgresql.org
>>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Some Developer
>>> Sent: Tuesday, July 23, 2013 8:29 PM
>>> To: pgsql-general@postgresql.org
>>> Subject: [GENERAL] Why are stored procedures looked on so negatively?
>>>
>>> I've done quite a bit of reading on stored procedures recently and the
>>> consensus seems to be that you shouldn't use them unless you really must.
>>>
>>> I don't understand this argument. If you implement all of your logic in
>>> the application then you need to make a network request to the database
>>> server, return the required data from the database to the app server, do the
>>> processing and then return the results. A stored procedure is going to be a
>>> lot faster than that even if you just take away network latency / transfer
>>> time.
>>>
>>> I'm in the middle of building a database and was going to make extensive
>>> use of stored procedures and trigger functions because it makes more sense
>>> for the actions to happen at the database layer rather than in the app
>>> layer.
>>>
>>> Should I use them or not?
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
>>> changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>> Thank you all for the responses. I feel better about making use of them now.
>>
>> Now for one final question: I was planning on using plpython2u to write my
>> stored procedures since Python is a language I am very familiar with. I
>> understand that many people would want to use plpgsql instead but it'll be
>> quicker for me to do it in Python.
>>
>> Will there be much of a performance difference between the two at all? Are
>> there any very convincing arguments that will make me use plpgsql instead or
>> does it not really matter?
>
> plpgsql is generally the fastest/easiest language for a certain (but
> important) class of operations. it runs closer to the SQL execution
> engine and automatically plans all your queries (which can be a pretty
> big deal for certain types of coding).  all error handling is native
> (so that you don't have to catch a python exception and peek into the
> sql aspects of it for appropriate handling) which is a bigger deal
> than it appears on the surface.  also it's good to exercise your SQL
> skills.
>
> whichever way you go, good procedure practices generally involve
> approximating scripted SQL to the extent possible.   also you should
> separate routines that read from and write to the database (and try to
> keep as much code as possible in the read side).  make sure to mark
> routines immutable/stable as appropriate.   another underutilized
> function decoration is STRICT -- it's very fast when it fires and can
> save you a lot of debugging headaches.
>
> merlin
>

Thanks. I'll be sure to bear that information in mind.

On Tue, Jul 23, 2013 at 5:29 PM, Some Developer
<someukdeveloper@gmail.com> wrote:
> I've done quite a bit of reading on stored procedures recently and the
> consensus seems to be that you shouldn't use them unless you really must.

I think that mostly speaks to the method you used for finding things
to read.  This is a well known holy war.

>
> I don't understand this argument.

That is a conclusion, not an argument.  You didn't give us the
argument behind the conclusion!

> If you implement all of your logic in the
> application then you need to make a network request to the database server,
> return the required data from the database to the app server, do the
> processing and then return the results. A stored procedure is going to be a
> lot faster than that even if you just take away network latency / transfer
> time.

Sorry, I don't get this at all.  Whether you use a stored procedure or
not, the database needs to be told what to do by the outside word, and
needs to return the result to the outside world.  So you can not get
rid of that minimal round trip, no matter what, unless your database
becomes solipsist.  Now, if the application-side code needs to make a
lot of round trips to the database in order to implement one logical
unit of work, that is a different matter and stored procedures could
help there (but so could consolidating the round trips into a fewer
number of more sophisticated SQL--which is often but not always
possible).

One of the very annoying uses of stored procedures I see is insisting
that all access goes through them, with no direct access to the
underlying tables via ordinary SQL.  They have now replaced one of the
most successful, powerful, and well-known data access APIs ever, with
some home grown API that is probably half-baked.  Sometimes a case can
be made for that (particularly for large bureaucratic organizations,
or intensely regulated ones) , but don't expect it to be free of
consequences.

> I'm in the middle of building a database and was going to make extensive use
> of stored procedures and trigger functions because it makes more sense for
> the actions to happen at the database layer rather than in the app layer.
>
> Should I use them or not?

Are you a one man shop, and always will be?  If so, i think it is
mostly a matter of what you prefer developing in, and what you are
most used to developing in.  If you hire someone to help you out, do
you want that person to be able to do interesting (and perhaps bad)
things with the database through SQL, or do you want them to be mostly
restricted to changing the font and color of the web page showing the
results?  A case could be made for either way.

Cheers,

Jeff

On 24/07/13 20:33, Jeff Janes wrote:
> On Tue, Jul 23, 2013 at 5:29 PM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really must.
>
> I think that mostly speaks to the method you used for finding things
> to read.  This is a well known holy war.
>
>>
>> I don't understand this argument.
>
> That is a conclusion, not an argument.  You didn't give us the
> argument behind the conclusion!
>
>> If you implement all of your logic in the
>> application then you need to make a network request to the database server,
>> return the required data from the database to the app server, do the
>> processing and then return the results. A stored procedure is going to be a
>> lot faster than that even if you just take away network latency / transfer
>> time.
>
> Sorry, I don't get this at all.  Whether you use a stored procedure or
> not, the database needs to be told what to do by the outside word, and
> needs to return the result to the outside world.  So you can not get
> rid of that minimal round trip, no matter what, unless your database
> becomes solipsist.  Now, if the application-side code needs to make a
> lot of round trips to the database in order to implement one logical
> unit of work, that is a different matter and stored procedures could
> help there (but so could consolidating the round trips into a fewer
> number of more sophisticated SQL--which is often but not always
> possible).

The reason that I think stored procedures and triggers are the correct
way to go for my database is because I need certain actions to be
performed when data is inserted, updated and deleted. Doing that in the
app layer would be a waste of time since the database already provides a
very well tested set of functionality to handle this.

The added advantage of removing load from the app servers so they can
actually deal with serving the app is a bonus.

I'm not planning on creating a complex application in the database in
its own right, just augmenting what is already available with a few time
savers and (a couple of) speed optimisations for commonly carried out tasks.

> One of the very annoying uses of stored procedures I see is insisting
> that all access goes through them, with no direct access to the
> underlying tables via ordinary SQL.  They have now replaced one of the
> most successful, powerful, and well-known data access APIs ever, with
> some home grown API that is probably half-baked.  Sometimes a case can
> be made for that (particularly for large bureaucratic organizations,
> or intensely regulated ones) , but don't expect it to be free of
> consequences.

I certainly won't be doing that. All the queries will be accessing the
tables directly and the stored procedures will only fire when a trigger
goes off. This is more about have async actions take place when a user
creates / edits / deletes a certain type of action. The added advantage
that triggers work well with the PostgreSQL transaction system is a real
bonus.

>> I'm in the middle of building a database and was going to make extensive use
>> of stored procedures and trigger functions because it makes more sense for
>> the actions to happen at the database layer rather than in the app layer.
>>
>> Should I use them or not?
>
> Are you a one man shop, and always will be?  If so, i think it is
> mostly a matter of what you prefer developing in, and what you are
> most used to developing in.  If you hire someone to help you out, do
> you want that person to be able to do interesting (and perhaps bad)
> things with the database through SQL, or do you want them to be mostly
> restricted to changing the font and color of the web page showing the
> results?  A case could be made for either way.
>
> Cheers,
>
> Jeff
>

Thanks for your input. Hopefully I've explained in a bit more detail
what I am trying to do.

On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
<someukdeveloper@gmail.com> wrote:
> The added advantage of removing load from the app servers so they can
> actually deal with serving the app is a bonus.

Uhm...I don't know what application you are developing, but I don't
buy your explaination.
While it is true that you are moving CPU cycles from the application
server to the database server, you will probably end with the
application server waiting for the database to acknowledge (and
therefore not serving requests) and usually the computation is not
that heavy for an online transaction (it would be better to do it as
batch if that is really heavy). Therefore this is not an advantage for
me.
Again, the only reason to use database facilities (like stored
procedures) is to arm the database so that even a different
application/connection/user will interact following as much business
rules as possible.

Moreover, please also note that one reason developers tend to avoid
database facilities is that they are using some kind of
stack/orm/automagical library that does not allow the usage of deep
features in sake of portability.



>
> I'm not planning on creating a complex application in the database in its
> own right, just augmenting what is already available with a few time savers
> and (a couple of) speed optimisations for commonly carried out tasks.
>

I don't understand the "time saving" argument: you have to implement
the logic either in the application or the database, so let's say the
time of the implementation is the same. The only advantage of the
database is the code reuse. But take into account that there are
drawbacks, like debugging that is not always so simple.

Luca

2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> The added advantage of removing load from the app servers so they can
>> actually deal with serving the app is a bonus.
>
> Uhm...I don't know what application you are developing, but I don't
> buy your explaination.
> While it is true that you are moving CPU cycles from the application
> server to the database server, you will probably end with the
> application server waiting for the database to acknowledge (and
> therefore not serving requests) and usually the computation is not
> that heavy for an online transaction (it would be better to do it as
> batch if that is really heavy). Therefore this is not an advantage for
> me.
> Again, the only reason to use database facilities (like stored
> procedures) is to arm the database so that even a different
> application/connection/user will interact following as much business
> rules as possible.
>
> Moreover, please also note that one reason developers tend to avoid
> database facilities is that they are using some kind of
> stack/orm/automagical library that does not allow the usage of deep
> features in sake of portability.
>
>
>
>>
>> I'm not planning on creating a complex application in the database in its
>> own right, just augmenting what is already available with a few time savers
>> and (a couple of) speed optimisations for commonly carried out tasks.
>>
>
> I don't understand the "time saving" argument: you have to implement
> the logic either in the application or the database, so let's say the
> time of the implementation is the same. The only advantage of the
> database is the code reuse. But take into account that there are
> drawbacks, like debugging that is not always so simple.
>
> Luca

I could be wrong, but the main advantage you gain by using stored
procedures is what Luca says: unique data access interface.
Just that.
I don't think you'll save a single CPU cycle by moving logic from
"application" to "DB" (or the other way around).
That logic need to be implemented (and run) on either part.
The only saving would happen if you push the logic straight to the client.
And keep in mind than not all PLs are the same and have the same effectiveness.
So, for example, instead of INSERTing rows from program, you could
SELECT from a stored procedure which will do the INSERT possibly with
the very same checks you would do in the application. Only put
together in a single place. The stored procedure.

Finally, I fear this is kind of "religion" war. So feel free to follow
any or establish your own.

The bottom line here is: PLs are OK. It just depends on what you do and how.

2013/7/25 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>> <someukdeveloper@gmail.com> wrote:
>>> The added advantage of removing load from the app servers so they can
>>> actually deal with serving the app is a bonus.
>>
>> Uhm...I don't know what application you are developing, but I don't
>> buy your explaination.
>> While it is true that you are moving CPU cycles from the application
>> server to the database server, you will probably end with the
>> application server waiting for the database to acknowledge (and
>> therefore not serving requests) and usually the computation is not
>> that heavy for an online transaction (it would be better to do it as
>> batch if that is really heavy). Therefore this is not an advantage for
>> me.
>> Again, the only reason to use database facilities (like stored
>> procedures) is to arm the database so that even a different
>> application/connection/user will interact following as much business
>> rules as possible.
>>
>> Moreover, please also note that one reason developers tend to avoid
>> database facilities is that they are using some kind of
>> stack/orm/automagical library that does not allow the usage of deep
>> features in sake of portability.
>>
>>
>>
>>>
>>> I'm not planning on creating a complex application in the database in its
>>> own right, just augmenting what is already available with a few time savers
>>> and (a couple of) speed optimisations for commonly carried out tasks.
>>>
>>
>> I don't understand the "time saving" argument: you have to implement
>> the logic either in the application or the database, so let's say the
>> time of the implementation is the same. The only advantage of the
>> database is the code reuse. But take into account that there are
>> drawbacks, like debugging that is not always so simple.
>>
>> Luca
>
> I could be wrong, but the main advantage you gain by using stored
> procedures is what Luca says: unique data access interface.
> Just that.
> I don't think you'll save a single CPU cycle by moving logic from
> "application" to "DB" (or the other way around).
> That logic need to be implemented (and run) on either part.
> The only saving would happen if you push the logic straight to the client.
> And keep in mind than not all PLs are the same and have the same effectiveness.
> So, for example, instead of INSERTing rows from program, you could
> SELECT from a stored procedure which will do the INSERT possibly with
> the very same checks you would do in the application. Only put
> together in a single place. The stored procedure.
>
> Finally, I fear this is kind of "religion" war. So feel free to follow
> any or establish your own.
>
> The bottom line here is: PLs are OK. It just depends on what you do and how.

+1

exactly

Pavel
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 25/07/13 07:57, Luca Ferrari wrote:
> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
> <someukdeveloper@gmail.com> wrote:
>> The added advantage of removing load from the app servers so they can
>> actually deal with serving the app is a bonus.
>
> Uhm...I don't know what application you are developing, but I don't
> buy your explaination.
> While it is true that you are moving CPU cycles from the application
> server to the database server, you will probably end with the
> application server waiting for the database to acknowledge (and
> therefore not serving requests) and usually the computation is not
> that heavy for an online transaction (it would be better to do it as
> batch if that is really heavy). Therefore this is not an advantage for
> me.
> Again, the only reason to use database facilities (like stored
> procedures) is to arm the database so that even a different
> application/connection/user will interact following as much business
> rules as possible.

You are forgetting that you can execute a query asynchronously using
libpq therefore the app server can continue serving requests whilst the
database server chugs away on its work. You just poll the server every
now and again to see if the work has finished.

> Moreover, please also note that one reason developers tend to avoid
> database facilities is that they are using some kind of
> stack/orm/automagical library that does not allow the usage of deep
> features in sake of portability.
>
>
>
>>
>> I'm not planning on creating a complex application in the database in its
>> own right, just augmenting what is already available with a few time savers
>> and (a couple of) speed optimisations for commonly carried out tasks.
>>
>
> I don't understand the "time saving" argument: you have to implement
> the logic either in the application or the database, so let's say the
> time of the implementation is the same. The only advantage of the
> database is the code reuse. But take into account that there are
> drawbacks, like debugging that is not always so simple.
>
> Luca
>

Time saving was probably the wrong expression to use. The application
has parts written in different languages running on different servers.
It saves time to have the main work done using stored procedures since
they can be shared between the different systems regardless of the
language that particular service was written or the operating system
that it is running on.

On 25/07/13 08:14, Vincenzo Romano wrote:
> 2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>> <someukdeveloper@gmail.com> wrote:
>>> The added advantage of removing load from the app servers so they can
>>> actually deal with serving the app is a bonus.
>>
>> Uhm...I don't know what application you are developing, but I don't
>> buy your explaination.
>> While it is true that you are moving CPU cycles from the application
>> server to the database server, you will probably end with the
>> application server waiting for the database to acknowledge (and
>> therefore not serving requests) and usually the computation is not
>> that heavy for an online transaction (it would be better to do it as
>> batch if that is really heavy). Therefore this is not an advantage for
>> me.
>> Again, the only reason to use database facilities (like stored
>> procedures) is to arm the database so that even a different
>> application/connection/user will interact following as much business
>> rules as possible.
>>
>> Moreover, please also note that one reason developers tend to avoid
>> database facilities is that they are using some kind of
>> stack/orm/automagical library that does not allow the usage of deep
>> features in sake of portability.
>>
>>
>>
>>>
>>> I'm not planning on creating a complex application in the database in its
>>> own right, just augmenting what is already available with a few time savers
>>> and (a couple of) speed optimisations for commonly carried out tasks.
>>>
>>
>> I don't understand the "time saving" argument: you have to implement
>> the logic either in the application or the database, so let's say the
>> time of the implementation is the same. The only advantage of the
>> database is the code reuse. But take into account that there are
>> drawbacks, like debugging that is not always so simple.
>>
>> Luca
>
> I could be wrong, but the main advantage you gain by using stored
> procedures is what Luca says: unique data access interface.
> Just that.
> I don't think you'll save a single CPU cycle by moving logic from
> "application" to "DB" (or the other way around).
> That logic need to be implemented (and run) on either part.
> The only saving would happen if you push the logic straight to the client.
> And keep in mind than not all PLs are the same and have the same effectiveness.
> So, for example, instead of INSERTing rows from program, you could
> SELECT from a stored procedure which will do the INSERT possibly with
> the very same checks you would do in the application. Only put
> together in a single place. The stored procedure.
>
> Finally, I fear this is kind of "religion" war. So feel free to follow
> any or establish your own.
>
> The bottom line here is: PLs are OK. It just depends on what you do and how.
>

When I was talking about improving speed I was talking about reducing
load on the app servers by putting more of the work load on the database
server. I know that it won't actually save CPU cycles (one of the
machines has to do it) but it will save load on the app servers. As I
said above using the asynchronous abilities of libpq helps keep the app
servers serving requests whilst the database gets on with its tasks.

In fact the whole design of this application is asynchronous in nature.

Hi,

 

>the whole design of this application is asynchronous in nature.

Then you'll be MUCH better off with SPs, from an architectural POV, as you can basically design "building blocks" by initially just making SPs that deliver a mock result, and have the entire development of the app server being in dependent on the SQL development. This way none of the branches blocks the other (provided that you can actually freeze the design).

 

Cheers

Bèrto

 

 

On 25 July 2013 09:44, Some Developer <someukdeveloper@gmail.com> wrote:

 

On 25/07/13 08:14, Vincenzo Romano wrote:

 

2013/7/25 Luca Ferrari <fluca1978@infinito.it>:

 

On Thu, Jul 25, 2013 at 2:57 AM, Some Developer

<someukdeveloper@gmail.com> wrote:

 

The added advantage of removing load from the app servers so they can

actually deal with serving the app is a bonus.

 

 

Uhm...I don't know what application you are developing, but I don't

buy your explaination.

While it is true that you are moving CPU cycles from the application

server to the database server, you will probably end with the

application server waiting for the database to acknowledge (and

therefore not serving requests) and usually the computation is not

that heavy for an online transaction (it would be better to do it as

batch if that is really heavy). Therefore this is not an advantage for

me.

Again, the only reason to use database facilities (like stored

procedures) is to arm the database so that even a different

application/connection/user will interact following as much business

rules as possible.

 

Moreover, please also note that one reason developers tend to avoid

database facilities is that they are using some kind of

stack/orm/automagical library that does not allow the usage of deep

features in sake of portability.

 

 

 

 

 

I'm not planning on creating a complex application in the database in its

own right, just augmenting what is already available with a few time savers

and (a couple of) speed optimisations for commonly carried out tasks.

 

 

 

I don't understand the "time saving" argument: you have to implement

the logic either in the application or the database, so let's say the

time of the implementation is the same. The only advantage of the

database is the code reuse. But take into account that there are

drawbacks, like debugging that is not always so simple.

 

Luca

 

 

I could be wrong, but the main advantage you gain by using stored

procedures is what Luca says: unique data access interface.

Just that.

I don't think you'll save a single CPU cycle by moving logic from

"application" to "DB" (or the other way around).

That logic need to be implemented (and run) on either part.

The only saving would happen if you push the logic straight to the client.

And keep in mind than not all PLs are the same and have the same effectiveness.

So, for example, instead of INSERTing rows from program, you could

SELECT from a stored procedure which will do the INSERT possibly with

the very same checks you would do in the application. Only put

together in a single place. The stored procedure.

 

Finally, I fear this is kind of "religion" war. So feel free to follow

any or establish your own.

 

The bottom line here is: PLs are OK. It just depends on what you do and how.

 

 

 

When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the work load on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but it will save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app servers serving requests whilst the database gets on with its tasks.

 

In fact the whole design of this application is asynchronous in nature.

 

 

 

--

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-general

 

 

 

--

==============================

If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

-- 
http://www.fastmail.fm - Faster than the air-speed velocity of an
                          unladen european swallow

On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>
>
> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving requests whilst the database gets on with its tasks. 
>

App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU
there,drop another cheap box in the rack. 

Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box
(increasinglyexpensive) or rearchitect the application (even more expensive). 

I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code to
thedatabase server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd
approach.

Cheers,
  Steve

2013/7/25 Steve Atkins <steve@blighty.com>:
>
> On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>>
>>
>> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving requests whilst the database gets on with its tasks. 
>>
>
> App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU
there,drop another cheap box in the rack. 
>
> Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box
(increasinglyexpensive) or rearchitect the application (even more expensive). 
>
> I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code
tothe database server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd
approach.

It is false idea.

What is a stored procedure? A few procedural construct and lot of SQL
queries. A procedural code is +/- zero overhead - significantly more
expensive are SQL queries - and these queries you will send from
procedures and from application server too. I can say so good written
stored procedures has zero negative effect on server performance -
more it has positive effect due elimination network latency it
decrease lock times.

Stored procedures is good environment for business logic
implementation or workflow implementation and bad for expensive
numeric calculations - and if you respect this rule, then stored
procedures will be faster always with less server load.

Regards

Pavel


>
> Cheers,
>   Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

I have a DB that relies heavily on recursive stored procedures that tap reflexive tables that store hierarchical data.
Theseprocedures are called from queries and return record streams.  Temp tables are used to store collected data as the
procedureruns up/down the hierarchy.  And many other stored procedurea are called by the recursive procedure along the
wayto identify the correct hier path(s) to follow.  These things run amazingly fast.  Attempts others haev made to do
thisin perl-DBI were shaping up to be so slow that they were discarded early on, merely on that issue alone.  
 

In another DB, I use stored procedures in triggers, again, recursive and working with hierarchy, only this time,
inserting/updating/deletingrecords along the way.  Again, fast as compared with the external "competition", but the
matterof data integrity is another winner here because it makes it impossible for users at the SQL prompt to screw up
thehierarchies with singular DML calls.  The hierarchies end up being correct by construction.  Ironically, a different
grouptried to implement this without triggers, sps or even PG (they used MySQL).  And it's been nothing but
headaches...poor performance and broken hierarchies all the time.  When they asked me to port my PG triggers/sps to
MySQL,I hit walls that involved... 1) inability to defer constraint checking (for foreign key constraints),  2)
inabilityto leave cursors open in recursive calls (globally vs locally scoped cursors), and no support for "record"
datatypes.    
 

For me, the question is more along the lines of why I can't or shouldn't use stored procedures over external code, the
defaultbeing sps.
 

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: Thursday, July 25, 2013 11:09 AM
To: Steve Atkins
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why are stored procedures looked on so negatively?

2013/7/25 Steve Atkins <steve@blighty.com>:
>
> On Jul 25, 2013, at 1:44 AM, Some Developer <someukdeveloper@gmail.com> wrote:
>>>
>>
>> When I was talking about improving speed I was talking about reducing load on the app servers by putting more of the
workload on the database server. I know that it won't actually save CPU cycles (one of the machines has to do it) but
itwill save load on the app servers. As I said above using the asynchronous abilities of libpq helps keep the app
serversserving requests whilst the database gets on with its tasks.
 
>>
>
> App servers don't tend to maintain much global state, so are almost perfectly parallelizable. If you run out of CPU
there,drop another cheap box in the rack.
 
>
> Database servers aren't. Once you top out a database server your main options are to replace it with a bigger box
(increasinglyexpensive) or rearchitect the application (even more expensive).
 
>
> I'll always put more work on the cheaply scalable app servers if I can reduce the load on the database. Moving code
tothe database server for reasons of CPU cost (as opposed to, say, data or business rule consistency) seems an odd
approach.

It is false idea.

What is a stored procedure? A few procedural construct and lot of SQL queries. A procedural code is +/- zero overhead -
significantlymore expensive are SQL queries - and these queries you will send from procedures and from application
servertoo. I can say so good written stored procedures has zero negative effect on server performance - more it has
positiveeffect due elimination network latency it decrease lock times.
 

Stored procedures is good environment for business logic implementation or workflow implementation and bad for
expensivenumeric calculations - and if you respect this rule, then stored procedures will be faster always with less
serverload.
 

Regards

Pavel


>
> Cheers,
>   Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To 
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Jul 23, 2013, at 7:29 PM, Some Developer <someukdeveloper@gmail.com> wrote:

> I've done quite a bit of reading on stored procedures recently and the consensus seems to be that you shouldn't use
themunless you really must. 

Application architecture is a specific software engineering discipline.  These types of generalizations come from
coderswho don't really understand application architecture and/or databases.  There are specific reasons to put code in
thedatabase server, application middleware, or the application.  To make this decision, much more must be known that
whathas been presented in this thread. 

For example, if you want to maintain data integrity, then you really want to use very specific table definitions with
foreignkeys, defaults, and constraints.  While this is not related to stored procedures, application coders try to shy
awayfrom these (like they do stored procedures) because it makes working with the database harder.  It forces the data
tobe correct before it comes into the database. When foreign keys, defaults, and constraints are not enough to ensure
dataintegrity then stored procedures should be used.  The question is, how important is your data and how much time do
youwant to spend correcting it after it enters the database? 

The next reason is performance.  Stored procedures can in certain circumstances dramatically increase or decrease
performanceof both the client and the server, network traffic, and application response time.  But which one is most
importantin your application?  The consensus does not know.  The best practice depends on the type of multitasking the
applicationis performing, the type of client, the client coding environment, the locations of the data being processed,
thelocking requirements, the concurrency requirements, the capacity of the servers and clients, the network topology,
theexpected response time for the activity, etc.  It is not at all uncommon to think that a stored procedure should be
inthe database server and to have performance testing show that it is better in the application and vice versa.  Keep
inmind that as the database becomes loaded, these performance issues may change and any decisions you make on a
developmentdatabase with only partial data may not prove out in the final application. 

There may also be reasons to normalize/denormalize data in the database, but present a different view to the
application. This should, if done correctly, make the application code simpler to maintain and understand.  What is
yoursupport experience level?  No experienced DBAs, this is probably a bad idea.  Relatively inexperienced application
coders,this is probably a really good idea. 

Sophisticated applications may even have more than one database server.  One update server and multiple read only
serversis very common in the environments I work in.  Since the update server is not burdened by providing all of the
readonly data, it has much more capacity to handle stored procedures.  Some of our environments see 80 or 90% of the
loadas read only.  This is the network topology part. 

Another example, if the result of a procedure is one number, but requires 15 columns, from 200 rows the question is, is
itfaster to do it on the server and only put one resulting number back on the network, or should the system get all 15
columnstimes 200 rows worth of data and put that on the network for the client to analyze?  The answer is, well it
depends? Well, maybe not for this example, but hopefully you get the point.  Now if part of the procedure requires data
thatcomes from a GUI table or user entered data that only resides in the application, then the situation changes. 

Wherever you put the code, you should have specific reasons for doing so and for high performance applications it is
notappropriate to generalize that all the code should go exclusively into the database or the app. 

Neil

Neil Tiffin <neilt@neiltiffin.com> wrote:
> Some Developer <someukdeveloper@gmail.com> wrote:
>
>> I've done quite a bit of reading on stored procedures recently and the
>> consensus seems to be that you shouldn't use them unless you really must.
>
> Application architecture is a specific software engineering discipline.  These
> types of generalizations come from coders who don't really understand
> application architecture and/or databases.  There are specific reasons to put
> code in the database server, application middleware, or the application.  To
> make this decision, much more must be known that what has been presented in this
> thread.

+1

> For example, if you want to maintain data integrity, then you really want to use
> very specific table definitions with foreign keys, defaults, and constraints.
> While this is not related to stored procedures, application coders try to shy
> away from these (like they do stored procedures) because it makes working with
> the database harder.  It forces the data to be correct before it comes into the
> database. When foreign keys, defaults, and constraints are not enough to ensure
> data integrity then stored procedures should be used.  The question is, how
> important is your data and how much time do you want to spend correcting it
> after it enters the database?

Agreed.

> The next reason is performance.

I'm going to skip the rest of this well-reasoned and well-written
response to give just a couple data points on this.

When working as a consultant, one client was doing everything
client-side and engaged me to fix some performance problems.  In
one case a frequently run query was taking two minutes.  As a
stored procedure the correct results were returned in two seconds.
This same client had a report which ran for 72 hours.  A stored
procedure was able to return the correct data in 2.5 minutes,
although it took another 10 minutes for the client side to process
it into the output format.

Stored procedures are not a panacea, however.  Writing in a
declarative format is, in my experience, much more important.  I
saw one case where a SQL procedure written in imperative form,
navigating through linkages a row at a time, was on pace to
complete in over a year.  Rewritten in declarative form it ran in a
few minutes.  As a side benefit, the declarative form is usually
10% to 20% the number of lines of code, and less buggy.  For
retrieval of complex data sets, the big thing is to learn to write
SQL which specifies *what you want* rather then trying to specify
*how to get it*.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/08/13 08:24, Kevin Grittner wrote:
[...]
> When working as a consultant, one client was doing everything
> client-side and engaged me to fix some performance problems.  In one
> case a frequently run query was taking two minutes.  As a stored
> procedure the correct results were returned in two seconds. This same
> client had a report which ran for 72 hours.  A stored procedure was
> able to return the correct data in 2.5 minutes, although it took
> another 10 minutes for the client side to process it into the output
> format. Stored procedures are not a panacea, however.  Writing in a
> declarative format is, in my experience, much more important.  I saw
> one case where a SQL procedure written in imperative form, navigating
> through linkages a row at a time, was on pace to complete in over a
> year.  Rewritten in declarative form it ran in a few minutes.  As a
> side benefit, the declarative form is usually 10% to 20% the number of
> lines of code, and less buggy.  For retrieval of complex data sets,
> the big thing is to learn to write SQL which specifies *what you want*
> rather then trying to specify *how to get it*. -- Kevin Grittner EDB:
> http://www.enterprisedb.com The Enterprise PostgreSQL Company

Trust the Planner, Luke!
(Apologies to Star Wars)

Very informative, learnt more in the above, and omitted text, than I
have for a long while - certainly clarified my ideas on the subject.


Cheers,
Gavin

On Fri, Aug 2, 2013 at 1:49 AM, Chris Travers <chris.travers@gmail.com> wrote:
> Here's my $0.02
>
> Stored procedures have a bunch of problems historically.  Part of this is
> because the interface traditionally is pretty spartan, and partly because
> some people take them too far.
>
> The first issue is that if you have a stored procedure which takes 2
> arguments and you need to extend it to three, then you have to change every
> call in the calling application.  This can create a maintenance problem.
> Variadic functions help somewhat but there are limits to what a variadic
> function can do here.

This is true of most popular languages.  The other defenses are
default arguments (use very sparingly), overloading, and named
parameter arguments.  If you're writing library routines that need to
accommodate a lot of behaviors, named arguments + use of defaults is a
pretty neat way to go.

merlin

On 08/02/2013 09:18 PM, Merlin Moncure wrote:
> On Fri, Aug 2, 2013 at 1:49 AM, Chris Travers <chris.travers@gmail.com> wrote:
>> Here's my $0.02
>>
>> Stored procedures have a bunch of problems historically.  Part of this is
>> because the interface traditionally is pretty spartan, and partly because
>> some people take them too far.
>>
>> The first issue is that if you have a stored procedure which takes 2
>> arguments and you need to extend it to three, then you have to change every
>> call in the calling application.  This can create a maintenance problem.
>> Variadic functions help somewhat but there are limits to what a variadic
>> function can do here.
>
> This is true of most popular languages.

I think part of the issue is that people tend to consider stored
procedures part of the application's internal implementation where you
just change all the call sites when you change the function.

Normally stored proc are really more like a library API - something
that's a bit of a pain to change due to asynchronous updates of apps and
interface, multiple interface users, etc.

If you think about them that way the question "should this be done in
apps or in a stored proc" must be asked for each individual procedure.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services