{"id":587,"date":"2026-08-17T13:02:33","date_gmt":"2026-08-17T13:02:33","guid":{"rendered":"https:\/\/abrarqasim.com\/blog\/postgres-stored-procedure-plx-what-it-actually-fixes\/"},"modified":"2026-08-17T13:02:33","modified_gmt":"2026-08-17T13:02:33","slug":"postgres-stored-procedure-plx-what-it-actually-fixes","status":"publish","type":"post","link":"https:\/\/abrarqasim.com\/blog\/postgres-stored-procedure-plx-what-it-actually-fixes\/","title":{"rendered":"Postgres Stored Procedures in PHP: What plx Actually Fixes"},"content":{"rendered":"

Confession: I have written maybe four stored procedures in my entire career, and two of them were mistakes I had to undo six months later. So when I saw that plx<\/a> had landed, an extension that lets you write a Postgres stored procedure in a PHP or Ruby or Go dialect instead of plpgsql, my first reaction was not excitement. It was suspicion.<\/p>\n

Because I have heard this pitch before. The reason people avoid database logic is supposedly the syntax. Learn DECLARE<\/code> and BEGIN<\/code> and the weird RETURN QUERY<\/code> thing, and suddenly you will move half your application into the database and everything gets faster.<\/p>\n

That has never been my problem with stored procedures. Not once. But I spent a Saturday morning reading the plx docs anyway, and I came out of it less dismissive than I went in. Not converted. Just less smug.<\/p>\n

What plx actually does<\/h2>\n

The mechanism is simpler than I expected. When you run CREATE FUNCTION<\/code> with a plx dialect, the extension transpiles your function body into plpgsql and stores that plpgsql in pg_proc.prosrc<\/code>. At runtime there is no PHP interpreter, no Ruby VM, nothing new loaded into the backend. Postgres runs its own plpgsql interpreter on the transpiled output, exactly as if you had written plpgsql by hand.<\/p>\n

The dialects shipping today, per the project repo<\/a>, cover Ruby, PHP, JavaScript, Python, TypeScript, Go, COBOL, Oracle PL\/SQL and T-SQL. The COBOL one made me laugh out loud, and then I thought about how many banks are quietly running COBOL right now and stopped laughing.<\/p>\n

The important detail is the timing. Translation happens once, at function creation, not per call and not per row. That kills the objection I would normally lead with, which is that a language shim in the hot path will cost you more than the syntax convenience is worth. Here there is no shim. There is a compiler that runs once and then gets out of the way.<\/p>\n

The plpgsql tax nobody talks about<\/h2>\n

Here is a small function that takes an order ID and marks it paid, with a guard against double payment. In plpgsql:<\/p>\n

CREATE OR REPLACE FUNCTION mark_paid(p_order_id bigint)\nRETURNS boolean AS $$\nDECLARE\n  v_status text;\nBEGIN\n  SELECT status INTO v_status\n    FROM orders WHERE id = p_order_id FOR UPDATE;\n\n  IF NOT FOUND THEN\n    RAISE EXCEPTION 'order % not found', p_order_id;\n  END IF;\n\n  IF v_status = 'paid' THEN\n    RETURN false;\n  END IF;\n\n  UPDATE orders SET status = 'paid', paid_at = now()\n   WHERE id = p_order_id;\n  RETURN true;\nEND;\n$$ LANGUAGE plpgsql;\n<\/code><\/pre>\n
And the same thing in the PHP dialect:<\/p>\n
CREATE OR REPLACE FUNCTION mark_paid(p_order_id bigint)\nRETURNS boolean AS $$\n  $status = null;\n\n  $status = query_one("SELECT status FROM orders\n                         WHERE id = $1 FOR UPDATE", $p_order_id);\n\n  if ($status === null) {\n    raise("order {$p_order_id} not found");\n  }\n\n  if ($status == 'paid') {\n    return false;\n  }\n\n  exec("UPDATE orders SET status = 'paid', paid_at = now()\n         WHERE id = $1", $p_order_id);\n  return true;\n$$ LANGUAGE plxphp;\n<\/code><\/pre>\n
Is the second one better? For me, barely. I can read both. But notice what actually changed: the DECLARE<\/code> block is gone, IF NOT FOUND<\/code> becomes a null check I already understand, and RETURN<\/code> behaves the way it does everywhere else in my day. Those are small wins that compound when a function is 80 lines instead of 20.<\/p>\n
What did not change is more interesting. The transaction semantics are identical. The locking is identical. The failure modes are identical. You are still writing plpgsql, you are just typing it in a costume.<\/p>\n
Where the abstraction leaks<\/h2>\n
This is the part that would bite me in production.<\/p>\n
The dialect is not PHP. It looks like PHP. It has PHP’s control flow and PHP’s string interpolation and PHP’s operators. It does not have Composer, it does not have your framework, it does not have array_map<\/code> unless somebody wrote a mapping for it, and it cannot open a socket. Every plpgsql statement type is reachable from every dialect, which is the design goal, but the reverse is not true. Your language’s standard library mostly is not there.<\/p>\n
I have watched this specific failure before with other transpiled languages. A developer who knows PHP well writes something idiomatic, hits a construct the transpiler does not cover, and now they have to learn plpgsql anyway, except under deadline pressure and while debugging a transpiler error message instead of a plpgsql error message. The abstraction that was supposed to save them a week of learning costs them a Tuesday afternoon of confusion.<\/p>\n
There is also the ordinary tooling problem. Your editor will syntax highlight that function body as SQL, or as PHP if you configure it, and it will be wrong either way. No linter understands it. Your test suite cannot import it. Postgres stores the transpiled plpgsql, so when something breaks at 2am and you run \\sf mark_paid<\/code>, you get plpgsql back, not the PHP you wrote. Two representations, one of which is authoritative and is not the one in your repo.<\/p>\n
The performance argument is about round trips, not syntax<\/h2>\n
Whenever stored procedures come up, someone says they are faster. That is true in a narrow way and misleading in the general case, and the distinction matters more than which language you type them in.<\/p>\n
What a function in the database saves you is network round trips. If your application does a select, then a check in PHP, then an update, that is three trips across the wire plus three parse and plan cycles. On a socket to a database on the same box, each trip might cost you a quarter of a millisecond and nobody notices. On a managed database in another availability zone, you are paying one to three milliseconds each way, and a loop that does this five hundred times has just eaten a second and a half of wall clock doing nothing but waiting.<\/p>\n
That is the case where pushing logic down is a real, measurable win. Batch jobs. Import routines. Anything with a loop that touches the database on every iteration.<\/p>\n
What it does not do is make the individual queries faster. The planner does the same work either way. If your update is slow because it is missing an index, wrapping it in a function makes it slow inside a function. I have seen a team spend two weeks porting logic into plpgsql, watch the runtime drop by eleven percent, and then find the actual fix was a partial index somebody added in an afternoon.<\/p>\n
So the honest order of operations is: measure, fix the query plans, and only then ask whether the round trips are what is left. If they are, write the function. At that point the question of whether the body reads like PHP or plpgsql is a comfort preference, and comfort preferences are allowed to matter.<\/p>\n
The trust boundary is the real reason I keep logic in the app<\/h2>\n
None of that is why I avoid stored procedures, though. Here is the actual reason.<\/p>\n
Application code is easy to deploy and easy to roll back. A stored procedure is schema. It goes through migrations, it has to be versioned by hand, and rolling it back means writing another migration under pressure. That asymmetry is the whole argument. I can ship a bad service class and revert it in ninety seconds. Reverting a bad function that three other functions now call is an afternoon.<\/p>\n
The one place where I concede the point completely is data integrity that must hold regardless of which client is talking to the database. If you have a Laravel app, a Go worker, and an analyst with psql access all writing to the same table, then business rules living only in Laravel are a fiction. That is what constraints, triggers and functions are for. I wrote about a related version of this problem in my post on catching N+1 queries in Laravel<\/a>, where the fix also came down to being honest about which layer owns a rule.<\/p>\n
Postgres has always let you register additional procedural languages, and the CREATE LANGUAGE docs<\/a> spell out the trusted versus untrusted distinction that matters here. Trusted languages run in a sandbox with no filesystem or network access and can be used by ordinary users. Untrusted ones need superuser. plx functions end up as plpgsql, which is trusted, so you inherit that sandbox for free. That is a genuine security argument in its favour over, say, dropping to PL\/PythonU because you missed list comprehensions.<\/p>\n
Who this is actually for<\/h2>\n
I think I misjudged the audience on first read.<\/p>\n
plx is not aimed at me, a person who already knows plpgsql well enough and has decided against using it much. It is aimed at teams sitting on a large Oracle PL\/SQL or T-SQL codebase who need to move to Postgres and are staring at a rewrite estimate with a comma in it. The Oracle and T-SQL dialects are the real product. The Ruby and PHP ones are the demo that gets it on the front page.<\/p>\n
Seen that way it makes a lot of sense. A migration where you keep the source dialect and change the engine underneath is a very different project from a migration where you rewrite ten thousand lines of procedural logic by hand. Even a partial win there is worth real money.<\/p>\n
For a greenfield app in 2026, I would not reach for it. I would keep my business rules in the application, put constraints and a few triggers in the database for the invariants that must never break, and skip the extra layer.<\/p>\n
What I’d actually do this week<\/h2>\n
If you have Postgres running locally and twenty minutes, do this rather than reading more takes about it.<\/p>\n
Pick the ugliest piece of data logic in your app. The one with three queries and a loop and a comment that says “do not touch”. Write it as a plain plpgsql function first, not a plx one. Run it. Time it against the application version with EXPLAIN ANALYZE<\/code> on the underlying queries.<\/p>\n
You will learn one of two things. Either the round trips were the bottleneck and pushing the logic down is a real win, in which case the syntax question becomes worth having. Or the difference is noise, and you have just saved yourself from adding an extension to production for aesthetic reasons.<\/p>\n
The procedural language installation docs<\/a> are short and worth skimming first so you know what you are actually loading into the backend. I do a lot of this kind of “is the abstraction earning its keep” work on client codebases, and it shows up in most of the projects I take on<\/a>. The answer is more often no than people expect, and that is fine. Knowing why you said no is the useful part.<\/p>\n","protected":false},"excerpt":{"rendered":"
plx lets you write a Postgres stored procedure in PHP, Go or Ruby that transpiles to plpgsql. I tried it, and here is where the abstraction actually leaks.<\/p>\n","protected":false},"author":2,"featured_media":586,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"","rank_math_description":"plx lets you write a Postgres stored procedure in PHP, Go or Ruby that transpiles to plpgsql. I tried it, and here is where the abstraction actually leaks.","rank_math_focus_keyword":"postgres stored procedure","rank_math_canonical_url":"","rank_math_robots":"","footnotes":""},"categories":[237,52],"tags":[474,53,641,177,642],"class_list":["post-587","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-php","tag-databases","tag-php","tag-plpgsql","tag-postgres","tag-stored-procedures"],"_links":{"self":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/587","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/comments?post=587"}],"version-history":[{"count":0,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/posts\/587\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media\/586"}],"wp:attachment":[{"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/media?parent=587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/categories?post=587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/abrarqasim.com\/blog\/wp-json\/wp\/v2\/tags?post=587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}