4 ms·

STORED PROCEDURES: Available since PG11. Name them whatever you want, and call them with parameters. They can be written in multiple languages like PL/pgSQL whi

STORED PROCEDURES: Available since PG11. Name them whatever you want, and call them with parameters. They can be written in multiple languages like PL/pgSQL which supports more constructs than standard SQL, and support defining multiple transactions internally. They are part of your schema and always available with support for security access. PREPARED STATEMENTS: Queries that are executed often. Setup on every new session but will be automatically compiled, cached for the session lifetime, and executed with just a minimal identifier. Depends on your client driver to handle seamlessly. More info: https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html

SigmundA 7y ago
Looks like the plan caching is only per session which is a big bummer if you call the same statements over and over from multiple sessions. Plus you have manually deal with this which is weird coming from MSSQL where it just does it with a global cache based on statement text hash all the time. Brings me back to early 2000's when it was recommended to always use stored procedures because either MSSQL 7 or 2000 can't remember didn't cache ad-hoc statements only sprocs.
- deleted 7y ago
  [deleted]
- manigandham 7y ago
  Plan caching is different from prepared statements, but yes PG is not as advanced as other commercial databases in execution. Usually the database client drivers will handle this for you. If you're using .NET, the npgsql driver maintains a connection pool and creates prepared statements for each new session. If you have something like PGBouncer in the middle then it gets more complicated.
  - SigmundA 7y ago
    There is almost no point is using prepared statements in MSSQL anymore, not for a long time. The only benefit comes from not sending the prepared statement over the wire each time and not having to hash it to do a plan lookup. That benefit is so very minor compared to the time taken to parse and plan the statement that almost none uses it, for more details: https://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721 https://dba.stackexchange.com/questions/129659/what-is-the-s... In PG its seems to be the only choice and on top of that isn't even shared among connections, this honestly blows my mind and will have to remember if I move anything serious over to PG. I like PG a lot and it does a lot of thing MSSQL doesn't but this is a big missing performance piece.
    - manigandham 7y ago
      I'm very familiar with both (and we use both) but plan caching isn't going to make that much of a difference for many scenarios. PG also has simpler planning than MSSQL anyway. I doubt you'd notice a difference unless you're extremely complex queries or a very high rate of transactions.
janpot 7y ago
yes, I'm proposing stored procedure without the management headache
- manigandham 7y ago
  Ah, so temporary stored procedures that are created and cached at the connection level and lifetime. Makes sense, might be worth a feature request to the PG team.
  - janpot 7y ago
    Or longer, they can exist for the lifetime of a specific version of my app. they'd why I'd cache and identify by their hash.
    - manigandham 7y ago
      If you're doing that then why not just use stored procedures? Prefix with a version and dropping them to cleanup is a one liner. The only issue is dependency tracking in case you change any schemas but that's an issue with any saved code in the database.
    - munk-a 7y ago
      How are you going to identify them by hash or know which hash is significant? Why not store that determiner in the SP name and use that to pick and chose different versions based on that determiner. Unless each binary gets its own hash (in which case you could solve it by building the SP when the package is built and then injecting it into the binary) there's some piece of information you'd use to chose which one to use - why obfuscate that information behind a hash instead of just using it directly as a descriptor?
Waterluvian 7y ago
What is the fundamental point of these vs. Getting data, using your preferred language, and then writing data? Is it basically all about performance, or are there other gains?
- didericis 7y ago
  I dabbled with stored procedures when I was dealing with a problem that required one bit of data in the database to be in sync with another bit of data. Basically I had a list of records with time values, and I wanted to have another record store the sum of all those time values. There were lots of records, and writes didn’t happen that often, so I thought a stored procedure that was triggered when a records time value was updated would work well. I wanted to be able to have multiple APIs wrapping the database (one GraphQL layer one that would make sql queries directly and be consumed by an iOS and web app, and another traditional REST one designed for integration with third parties that don’t support GraphQL). I could have either duplicated that logic to sum up the values across those layers or made a shared api later underneath the REST and GraphQL layers (or put the rest layer underneath the graphql layer), but those other options seemed less ideal. I wanted the API layer only to be responsible for sending data to and from the database, authentication and permissions. That app was a side project that I lost interest in, so I never got to see the long term implications of that, but I still think stored procedures were the right call for that particular problem. As much for data consistency and layer responsibility reasons as for performance reasons. The main difficulty with stored procedures seems to be that they’re kind of hard to test and isolate. But when used sparingly, and when you test that the effects your expecting happen on writes (even if you can’t test the procedure explicitly), I think they’re a good way to keep purely data consistency related logic inside the database.
- aargh_aargh 7y ago
  That works just fine until it doesn't. Doing all the work on the RDBMS side within a single transaction guarantees that nobody has changed the data you read before you wrote it back. If they have, either your transaction fails or theirs will, but consistency of the data is guaranteed.

SigmundA 7y ago

Looks like the plan caching is only per session which is a big bummer if you call the same statements over and over from multiple sessions. Plus you have manually deal with this which is weird coming from MSSQL where it just does it with a global cache based on statement text hash all the time. Brings me back to early 2000's when it was recommended to always use stored procedures because either MSSQL 7 or 2000 can't remember didn't cache ad-hoc statements only sprocs.

deleted 7y ago
[deleted]
manigandham 7y ago
Plan caching is different from prepared statements, but yes PG is not as advanced as other commercial databases in execution. Usually the database client drivers will handle this for you. If you're using .NET, the npgsql driver maintains a connection pool and creates prepared statements for each new session. If you have something like PGBouncer in the middle then it gets more complicated.
- SigmundA 7y ago
  There is almost no point is using prepared statements in MSSQL anymore, not for a long time. The only benefit comes from not sending the prepared statement over the wire each time and not having to hash it to do a plan lookup. That benefit is so very minor compared to the time taken to parse and plan the statement that almost none uses it, for more details: https://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721 https://dba.stackexchange.com/questions/129659/what-is-the-s... In PG its seems to be the only choice and on top of that isn't even shared among connections, this honestly blows my mind and will have to remember if I move anything serious over to PG. I like PG a lot and it does a lot of thing MSSQL doesn't but this is a big missing performance piece.
  - manigandham 7y ago
    I'm very familiar with both (and we use both) but plan caching isn't going to make that much of a difference for many scenarios. PG also has simpler planning than MSSQL anyway. I doubt you'd notice a difference unless you're extremely complex queries or a very high rate of transactions.

janpot 7y ago

yes, I'm proposing stored procedure without the management headache

manigandham 7y ago
Ah, so temporary stored procedures that are created and cached at the connection level and lifetime. Makes sense, might be worth a feature request to the PG team.
- janpot 7y ago
  Or longer, they can exist for the lifetime of a specific version of my app. they'd why I'd cache and identify by their hash.
  - manigandham 7y ago
    If you're doing that then why not just use stored procedures? Prefix with a version and dropping them to cleanup is a one liner. The only issue is dependency tracking in case you change any schemas but that's an issue with any saved code in the database.
  - munk-a 7y ago
    How are you going to identify them by hash or know which hash is significant? Why not store that determiner in the SP name and use that to pick and chose different versions based on that determiner. Unless each binary gets its own hash (in which case you could solve it by building the SP when the package is built and then injecting it into the binary) there's some piece of information you'd use to chose which one to use - why obfuscate that information behind a hash instead of just using it directly as a descriptor?

Waterluvian 7y ago

What is the fundamental point of these vs. Getting data, using your preferred language, and then writing data? Is it basically all about performance, or are there other gains?

didericis 7y ago
I dabbled with stored procedures when I was dealing with a problem that required one bit of data in the database to be in sync with another bit of data. Basically I had a list of records with time values, and I wanted to have another record store the sum of all those time values. There were lots of records, and writes didn’t happen that often, so I thought a stored procedure that was triggered when a records time value was updated would work well. I wanted to be able to have multiple APIs wrapping the database (one GraphQL layer one that would make sql queries directly and be consumed by an iOS and web app, and another traditional REST one designed for integration with third parties that don’t support GraphQL). I could have either duplicated that logic to sum up the values across those layers or made a shared api later underneath the REST and GraphQL layers (or put the rest layer underneath the graphql layer), but those other options seemed less ideal. I wanted the API layer only to be responsible for sending data to and from the database, authentication and permissions. That app was a side project that I lost interest in, so I never got to see the long term implications of that, but I still think stored procedures were the right call for that particular problem. As much for data consistency and layer responsibility reasons as for performance reasons. The main difficulty with stored procedures seems to be that they’re kind of hard to test and isolate. But when used sparingly, and when you test that the effects your expecting happen on writes (even if you can’t test the procedure explicitly), I think they’re a good way to keep purely data consistency related logic inside the database.
aargh_aargh 7y ago
That works just fine until it doesn't. Doing all the work on the RDBMS side within a single transaction guarantees that nobody has changed the data you read before you wrote it back. If they have, either your transaction fails or theirs will, but consistency of the data is guaranteed.