Обсуждение: Is it considered good practice to use stored procedures for mosttasks?
Hi, I'm about to build a website using PostgreSQL and for the first time I am not going to be using an ORM. I want to do it manually because I want to take the time to learn to use PostgreSQL properly on its own. The question is should I use stored procedures for the majority of the database operations or should I just use ad hoc queries as and when I need them? The advantage I can see for stored procedures is that you can do complex queries just by calling a single function rather than having to make multiple queries to get the result that you need. Plus it keeps the majority of data handling code at the database level rather than in the application itself. I was wondering what the consensus was for this? Should I try and use stored procedures as much as possible or should I only use them for specific types of tasks?
Re: Is it considered good practice to use stored procedures for most tasks?
От
Gaetano Mendola
Дата:
For sure do not allow your application to touch directly tables, use views and sp, lately I'm abandoning views for table functions.
On Wed, Apr 17, 2019, 21:02 Simon Connah <scopensource@gmail.com> wrote:
Hi,
I'm about to build a website using PostgreSQL and for the first time I
am not going to be using an ORM. I want to do it manually because I want
to take the time to learn to use PostgreSQL properly on its own. The
question is should I use stored procedures for the majority of the
database operations or should I just use ad hoc queries as and when I
need them? The advantage I can see for stored procedures is that you can
do complex queries just by calling a single function rather than having
to make multiple queries to get the result that you need. Plus it keeps
the majority of data handling code at the database level rather than in
the application itself.
I was wondering what the consensus was for this? Should I try and use
stored procedures as much as possible or should I only use them for
specific types of tasks?
Do you have a "why" for your suggestion for not allowing direct table access?
On Wed, Apr 17, 2019 at 4:32 PM Gaetano Mendola <mendola@gmail.com> wrote:
For sure do not allow your application to touch directly tables, use views and sp, lately I'm abandoning views for table functions.On Wed, Apr 17, 2019, 21:02 Simon Connah <scopensource@gmail.com> wrote:Hi,
I'm about to build a website using PostgreSQL and for the first time I
am not going to be using an ORM. I want to do it manually because I want
to take the time to learn to use PostgreSQL properly on its own. The
question is should I use stored procedures for the majority of the
database operations or should I just use ad hoc queries as and when I
need them? The advantage I can see for stored procedures is that you can
do complex queries just by calling a single function rather than having
to make multiple queries to get the result that you need. Plus it keeps
the majority of data handling code at the database level rather than in
the application itself.
I was wondering what the consensus was for this? Should I try and use
stored procedures as much as possible or should I only use them for
specific types of tasks?
Gaetano Mendola wrote: > For sure do not allow your application to touch directly tables, use views and sp, > lately I'm abandoning views for table functions. As far as I know, a table function result is materialized on the server, and you have to wait for the query to finish before you can retrieve the first row. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Is it considered good practice to use stored procedures for most tasks?
От
Gaetano Mendola
Дата:
Because in case of change in the database structure you can avoid to touch the application, also due the fact you can change table definitions inside a transaction you can even upgrade your database without downtime
On Thu, Apr 18, 2019, 01:04 Steve Horn <steve@stevehorn.dev> wrote:
Do you have a "why" for your suggestion for not allowing direct table access?On Wed, Apr 17, 2019 at 4:32 PM Gaetano Mendola <mendola@gmail.com> wrote:For sure do not allow your application to touch directly tables, use views and sp, lately I'm abandoning views for table functions.On Wed, Apr 17, 2019, 21:02 Simon Connah <scopensource@gmail.com> wrote:Hi,
I'm about to build a website using PostgreSQL and for the first time I
am not going to be using an ORM. I want to do it manually because I want
to take the time to learn to use PostgreSQL properly on its own. The
question is should I use stored procedures for the majority of the
database operations or should I just use ad hoc queries as and when I
need them? The advantage I can see for stored procedures is that you can
do complex queries just by calling a single function rather than having
to make multiple queries to get the result that you need. Plus it keeps
the majority of data handling code at the database level rather than in
the application itself.
I was wondering what the consensus was for this? Should I try and use
stored procedures as much as possible or should I only use them for
specific types of tasks?
Is it considered good practice to use stored procedures for most tasks?
My experience is that if you keep the business logic out of the database, but for instance deterministic data transformations, data integrity functions etc. in - a view is a good example of it, but it can be a stored procedure, trigger, converting results into JSON or whatever you like -, you can save much work on the back end code because you deliver solid data with fix shape. It acts like a good API to your data without involving the problem of changing business features.
In the database you have an actual implementation of a data model. In my opinion the data model is more business related or how the software developers/architects imagine these entities. The implementation is more how you actually build it in the database - your table structure, level of normalization or denormalization, handling defaults or implementing triggers for better consistency etc. The data model can be relative stable - as probably a number of other system components are depending on it and it represents the understanding of the entities related to the business.
On the other hand your actual implementation may change due to table or query optimizations, for instance. Such DB refactoring can be very painful if the DB internals are exposed to the back end (experience). Plus generally speaking most back end developers are not really good optimizing databases and understanding the performance implications what a database can offer and twisting their mindset around SQL which is a declarative language, not a procedural.
If you bring business logic into the database - over the time you can easily go into trouble. The decision is not always easy but in those case go on the safe side: if you can't decide if it is business logic or else then maybe better to consider it as business logic first.
BR,
salmonix
On Thu, Apr 18, 2019 at 10:36 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
Gaetano Mendola wrote:
> For sure do not allow your application to touch directly tables, use views and sp,
> lately I'm abandoning views for table functions.
As far as I know, a table function result is materialized on the server,
and you have to wait for the query to finish before you can retrieve the
first row.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Laurenz Albe schrieb am 18.04.2019 um 10:36: >> For sure do not allow your application to touch directly tables, use views and sp, >> lately I'm abandoning views for table functions. > > As far as I know, a table function result is materialized on the server, > and you have to wait for the query to finish before you can retrieve the > first row. Is that true for SQL functions as well? Especially when they are inlined, I can't imagine they are handled differently than a regular query. Thomas