postgresqlbusiness-logicdata-layersapplication-layer

Using Postgres' external procedural languages over application code

I am trying to figure out the advantages and disadvantages of using non-plpgsql procedural languages (PL/Python, PL/Perl, PL/v8, etc.) to implement data manipulation logic on the database level instead of going up to model level/ORM of the application framework that interacts with the database (Rails, Entity Framework, Django, etc.) and implementing it there.

To give a concrete example, say, I have a table that contains Mustache templates, and I want to have them "rendered" somehow. Table definition:

create table templates (
  id serial primary key,
  content text not null,
  data jsonb not null
);

Usually I would go the model code and add and extra method to render the template. Example in Rails:

class Template < ApplicationRecord
  def rendered
    Mustache.render(content, data)
  end
end

However, I could also write a PL/Python function that would do just that but on the database level:

create or replace function fn_mustache(template text, data jsonb)
returns text 
language plpython3u
as $$
  import chevron
  import json
  return chevron.render(template, json.loads(data))
$$;

create view v_templates as 
  select id, content, data, fn_mustache(content, data) as rendered
    from templates;

This yields virtually the same result functionality-wise. This example is very basic, yet the idea is to use PL/Python (or others) to manipulate the data in a more advanced manner than PL/pgsql can allow for. That is, PL/pgsql does not have the same amount of libraries that any generic programming language provides today (in the example am relying on implementations of Mustache templating system which would not be practical to implement in PL/pgsql in this case). I obviously would not use PL/Python for any sort of networking or other OS-level features, but for operations exclusively on data this seems like a decent approach (change my mind).

Points that I can observe so far:

What are the other advantages and disadvantages of these two approaches? (e.g. performance, maintainability)

Solution

  • You are wondering whether to have application logic inside the database or not. This is to a great extent a matter of taste. In the days of yore, the approach to implement application logic in database functions was more popular, but today it is usually frowned upon.

    Extreme positions in this debate are

    The best solution is typically somewhere in the middle, but where is largely a matter of taste. You see that this is a typical opinion-based question. However, let me supply some arguments that help you make a decision.

    Points speaking against application logic in the database:

    Points speaking for application logic in the database:

    My personal opinion is that anything that has to do with basic data integrity should be implemented in the database: