5 ms·

why would anyone want to use PL/Rust over PL/PQSL? what is the use case?

mkl95 3y ago
> PL/Rust is a loadable procedural language that enables writing PostgreSQL functions in the Rust programming language Use to write PostgreSQL functions in Rust. Also > The top advantages of PL/Rust include writing natively-compiled functions to achieve the absolute best performance, access to Rust's large development ecosystem, and Rust's compile-time safety guarantees.
- systems 3y ago
  As someone who have done a lot of database development, none of these sound advantageous Using a text oriented language like Perl with a good regexp engine might DB performance, comes from indexes , table partitioning and in-memory tables and to compile query execution plans, so you save some time the very first you run a procedure
  - ixfo 3y ago
    Doing in-database computation can be very advantageous for some applications, and writing those functions in Rust would be fantastic for some uses, not least for the library ecosystem. I did some work on video similarity search with in-DB search which would've certainly benefited.
  - ttfkam 3y ago
    DB performance also comes from size efficiency of user-defined data types, user-defined operator functions (typically for use with those user-defined data types), etc. Smaller, more efficient types directly translate to less disk usage and smaller indexes, both of which measurably improve database performance.
    - pjmlp 3y ago
      I assume PL/pg is not behind the curve in relation to PL/SQL and T-SQL for UDTs.
  - doctor_eval 3y ago
    > DB performance, comes from indexes , table partitioning and in-memory tables and to compile query execution plans, so you save some time the very first you run a procedure The network round trip to the database can also be a pretty significant performant penalty, especially when iterating over large sets.
- samanator 3y ago
  A crucial facet of optimization for computational triggers in PostgreSQL pertains to the implementation of event triggers, which enable operations to be executed in bulk (per DML statement) rather than on a per-row basis. It appears that, at present, PL/Rust has not incorporated support for event triggers. According to the documentation: > Event Triggers and DO blocks are not (yet) supported by PL/Rust.
  - ttfkam 3y ago
    Event triggers fire on DDL changes, not DML. You're thinking of statement-level triggers. As far as event triggers and DO-blocks, that omission seems fine to me. Especially DO-blocks, which are essentially an inline code, one-off escape hatch in the middle of other SQL. Rust would not be helping any performance-sensitive critical paths in those cases.
    - doctor_eval 3y ago
      True, but it’s sometimes useful for experimenting, REPL style.
    - samanator 3y ago
      You're right! My mistake. I was thinking of statement triggers. https://www.postgresql.org/docs/current/sql-createtrigger.html https://www.postgresql.org/docs/current/sql-createtrigger.ht... > The REFERENCING option enables collection of transition relations I don't see any examples of statement triggers...
      - ttfkam 3y ago
        https://stackoverflow.com/a/72397774 https://stackoverflow.com/a/72397774 Note the use of new_table and old_table as aliases. Instead of single records in NEW and OLD, you can select against new and old sets of records.
        samanator 3y ago
        I understand, and I've used the old and new table aliases. But I meant that I don't see a way to use those in the PL/Rust docs.
- pjmlp 3y ago
  Stored procedures are compiled to native code in any respectfull RDMS (Oracle, SQL Server, DB2,...), and I assume same applies to PostgreSQL.
tempaccount420 3y ago
PLPSQL is an awful language for anything less than the highest level glue code
- cobythedog 3y ago
  Can you give some examples of why you think this? I'm sincerely curious as someone who uses PLPSQL nearly every day and knows it is not perfect, but surprised to hear it is "awful".
  - ttfkam 3y ago
    I concur. Pl/pgsql isn't exactly elegant to be sure, but if you're already in a set-oriented mindset but need to add a sprinkling of imperative logic, it's well suited to the job.
    - asah 3y ago
      no seriously, PL/pgsql is pretty horrible and obscure. But aside from subjective comments, there's very few algorithms available for it, approximately 0% of engineers know it and it's not taught in school, has little tooling compared with a first class programming language, you can't run pl/pgsql code outside of PostgreSQL, and (tell me when to stop) PL/PGSQL is fine for "a bit more than a SELECT statement" and for very simple algorithms of <50 LOC. Anything more and please use a first class language like plrust, plv8, etc.
      - ttfkam 3y ago
        The decision between pl/pgsql and something like plv8 isn't LOC. It's whether the solution best fits a set-oriented model or a procedural model. Both are valid, just different use cases. There are a lot of cases where plv8 will thrash back and forth between the internals of Postgres and C and its v8 engine. These are usually the cases where set theory dominates the solution space. On the flip side, if you're doing a lot of filter/map/reduce on large JSON payloads, plv8 is demonstrably better than pl/pgsql. Right tool. Right job.
      - 3y ago
zkirill 3y ago
Wouldn't it be easier to write and run unit tests in Rust?

> PL/Rust is a loadable procedural language that enables writing PostgreSQL functions in the Rust programming language Use to write PostgreSQL functions in Rust. Also > The top advantages of PL/Rust include writing natively-compiled functions to achieve the absolute best performance, access to Rust's large development ecosystem, and Rust's compile-time safety guarantees.

systems 3y ago
As someone who have done a lot of database development, none of these sound advantageous Using a text oriented language like Perl with a good regexp engine might DB performance, comes from indexes , table partitioning and in-memory tables and to compile query execution plans, so you save some time the very first you run a procedure
- ixfo 3y ago
  Doing in-database computation can be very advantageous for some applications, and writing those functions in Rust would be fantastic for some uses, not least for the library ecosystem. I did some work on video similarity search with in-DB search which would've certainly benefited.
- ttfkam 3y ago
  DB performance also comes from size efficiency of user-defined data types, user-defined operator functions (typically for use with those user-defined data types), etc. Smaller, more efficient types directly translate to less disk usage and smaller indexes, both of which measurably improve database performance.
  - pjmlp 3y ago
    I assume PL/pg is not behind the curve in relation to PL/SQL and T-SQL for UDTs.
- doctor_eval 3y ago
  > DB performance, comes from indexes , table partitioning and in-memory tables and to compile query execution plans, so you save some time the very first you run a procedure The network round trip to the database can also be a pretty significant performant penalty, especially when iterating over large sets.
samanator 3y ago
A crucial facet of optimization for computational triggers in PostgreSQL pertains to the implementation of event triggers, which enable operations to be executed in bulk (per DML statement) rather than on a per-row basis. It appears that, at present, PL/Rust has not incorporated support for event triggers. According to the documentation: > Event Triggers and DO blocks are not (yet) supported by PL/Rust.
- ttfkam 3y ago
  Event triggers fire on DDL changes, not DML. You're thinking of statement-level triggers. As far as event triggers and DO-blocks, that omission seems fine to me. Especially DO-blocks, which are essentially an inline code, one-off escape hatch in the middle of other SQL. Rust would not be helping any performance-sensitive critical paths in those cases.
  - doctor_eval 3y ago
    True, but it’s sometimes useful for experimenting, REPL style.
  - samanator 3y ago
    You're right! My mistake. I was thinking of statement triggers. https://www.postgresql.org/docs/current/sql-createtrigger.html https://www.postgresql.org/docs/current/sql-createtrigger.ht... > The REFERENCING option enables collection of transition relations I don't see any examples of statement triggers...
    - ttfkam 3y ago
      https://stackoverflow.com/a/72397774 https://stackoverflow.com/a/72397774 Note the use of new_table and old_table as aliases. Instead of single records in NEW and OLD, you can select against new and old sets of records.
      - samanator 3y ago
        I understand, and I've used the old and new table aliases. But I meant that I don't see a way to use those in the PL/Rust docs.
pjmlp 3y ago
Stored procedures are compiled to native code in any respectfull RDMS (Oracle, SQL Server, DB2,...), and I assume same applies to PostgreSQL.

tempaccount420 3y ago

PLPSQL is an awful language for anything less than the highest level glue code

cobythedog 3y ago
Can you give some examples of why you think this? I'm sincerely curious as someone who uses PLPSQL nearly every day and knows it is not perfect, but surprised to hear it is "awful".
- ttfkam 3y ago
  I concur. Pl/pgsql isn't exactly elegant to be sure, but if you're already in a set-oriented mindset but need to add a sprinkling of imperative logic, it's well suited to the job.
  - asah 3y ago
    no seriously, PL/pgsql is pretty horrible and obscure. But aside from subjective comments, there's very few algorithms available for it, approximately 0% of engineers know it and it's not taught in school, has little tooling compared with a first class programming language, you can't run pl/pgsql code outside of PostgreSQL, and (tell me when to stop) PL/PGSQL is fine for "a bit more than a SELECT statement" and for very simple algorithms of <50 LOC. Anything more and please use a first class language like plrust, plv8, etc.
    - ttfkam 3y ago
      The decision between pl/pgsql and something like plv8 isn't LOC. It's whether the solution best fits a set-oriented model or a procedural model. Both are valid, just different use cases. There are a lot of cases where plv8 will thrash back and forth between the internals of Postgres and C and its v8 engine. These are usually the cases where set theory dominates the solution space. On the flip side, if you're doing a lot of filter/map/reduce on large JSON payloads, plv8 is demonstrably better than pl/pgsql. Right tool. Right job.
    - 3y ago

zkirill 3y ago

Wouldn't it be easier to write and run unit tests in Rust?