8

I'm playing around with PLV8 to write trigger and stored procedures for PostgreSQL. So far I don't really see disadvantages compared to PLPGSQL. Especially if working with JSON it seems even smarter then PLPGSQL. Are there known disadvantages or limitations if using PLV8? Can PLV8 be a full replacement for PLPGSQL? It would be great if somebody could share his experience on this.

Rainer
  • 1,067
  • 2
  • 14
  • 29
  • The only (very small) disadvantage I can see: you explicitly need to install it for each and every database you create. PL/pgSQL is always available by default. But only **you** can decide whether it is a full replacement or not: because that completely depends on the requirements you have for a language. –  Jun 17 '15 at 13:45
  • thanks. I'm asking because I want to know if there are maybe issues I'm presently not thinking about. If I decide today to use PLV8 for a new project and after 3 months I have to learn there are limitations would be bad. – Rainer Jun 17 '15 at 13:48
  • 1
    `plpgsql` uses SQL datatypes natively, an advantage in ease-of-use that no other PL has. If you plan to use complex datatypes such as `hstore` or `ltree`, you'll have to deal with their text representation in plv8. But it supports arrays, composite types, setof,... which is pretty good already. – Daniel Vérité Jun 17 '15 at 15:22
  • oh thanks, that's a possible point! – Rainer Jun 17 '15 at 15:53

3 Answers3

17

The advantages and disadvantages of PLV8 are same as advantages and disadvantages of PLPerl, PLPython and other PL languages.

  • It is not integrated with PostgreSQL engine - the processing SQL statements result can be slower. PLpgSQL is fully integrated to PostgreSQL engine.
  • SQL is not integrated to language - isn't possible to do static analyse of embedded SQL. It is possible with PLpgSQL - see plpgsql_check.
  • Can do better expensive mathematical calculations, a manipulations with strings and arrays are usually faster than in PLpgSQL.
  • Can use libraries developed for languages - Perl - CPAN, ...
  • JavaScript, Perl, Python are generic languages - so any generic tasks are implemented there well.
  • PLpgSQL is mature language designed for manipulation with data in relation database environment. Almost all what developer needs for data work with data is there. The iteration over result, taking data from database needs less more readable code.

PLpgSQL is perfect language for data manipulation via SQL language. Other PL is better for anything else - IO, Network, special formatting, slow numeric calculations, ...

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • The mentioned kind of analysis is a point I wasn't aware, thanks Pavel. About performance .. I red some articles in the meantime and got the impression that in "normal" cases (e.g. typical e-commerce portals) there are only very very small performance differences. But for sure, there are special cases. So, thanks again! – Rainer Jun 19 '15 at 07:38
  • The performance - it depends - database size, query complexity, size of returned resultset, ... – Pavel Stehule Jun 19 '15 at 09:53
  • if i write a function that loops over 150000 rows with one column of 20-100 words to extract tags inside a plv8 function, will it block execution for everyone else while running on the table? – PirateApp Nov 16 '21 at 05:49
  • 1
    @PirateApp - it depends what do you do. Table is not an array. If you only do only read operations, then you will not block any other users. – Pavel Stehule Nov 16 '21 at 05:53
5

UPDATE: since PLV8 2.3.3 (2018-04-27) supports kill signals and works pretty good. Disadvantage - you have to build it yourself. We have not migrated our code to python as we had no issues for the last 3 years.

A little bit late, but you cannot kill query currently running plv8 script, the only way is to restart the whole postgresql server. It is a huge disadvantage and our team is thinking about migrating to PLpgPython.

suricactus
  • 1,210
  • 2
  • 15
  • 22
0

To list some more disadvantages of PLpgSQL, mostly in the area of dev velocity:

  • Syntax errors are difficult to decipher, of the form mismatched parentheses at or near ";". I'm not getting a line number or any useful context.
  • No lint, prettier, editor support or other useful utilities that most languages have.
  • Yet another language to learn with lots of idiosyncrasies.
  • Trying to create a JSON object to call an external API is very difficult and error-prone. Many specialized functions like json_build_object, json_build_array, etc. Instead of just {a: 2, b: 'foo', c} in JS.
Dave
  • 3,171
  • 1
  • 25
  • 23