Thread

1. Should I generate strings in Postgres of Python?

   Bobby Mozumder <bmozumder@gmail.com> — 2016-10-18T20:35:20Z

   How fast is Postgres's string concatenation in comparison to the various Python string concatenation?  I'm using PREPARE statements for my SELECT queries for my web server.
   
   I'm wondering if I should just generate my JSON API (or even HTML) strings in Postgres directly, instead of in Python.  This would involve a few IF-THEN-ELSE (in Python) which I convert to CASE-WHEN (in Postgres) as well.
   
   I’m not sure about the internals of Postgres and how it compares speedwise to the Python bytecode interpreter (and future JIT compilers like PyPy).  Is Postgres generating bytecode and interpreting that for string concatenation & Case statements?
   
   -bobby
   
   

2. Re: Should I generate strings in Postgres of Python?

   Samuel Gendler <sgendler@ideasculptor.com> — 2016-10-18T22:53:54Z

   This strikes me as something that shouldn't matter in the vast majority of
   applications. Putting a bunch of logic for rendering an
   application-specific format of your data in prepared statements or stored
   procedures in your database violates the separation of concerns that most
   folks like to maintain between the layers of an application.
   
   The difference in string concatenation performance is unlikely to be a
   significant proportion of total request latency unless you are generating
   very long strings from very short components very inefficiently (appending
   in a loop with immutable strings, for example). Otherwise, waiting on disk
   and network are likely to be a far higher percentage of total request
   latency than string concatenation. Additionally, it is usually vastly
   cheaper to scale your application layer horizontally than it is to scale a
   database, so even if the application logic is slightly slower, it will
   usually be cheaper to throw more compute horsepower at the application
   layer if/when latency starts to become a problem unless latency is
   problematic even when serving only a single request at a time.
   
   Use your database to store data and your application to render the data in
   an application-specific manner.  That way, if you end up with multiple
   applications requiring different representations, you don't have to
   accommodate both in your data storage and retrieval layer.
   
   If your rendering code is a significant percentage of total latency,
   consider caching the rendered results rather than moving the rendering
   logic into your data storage layer - which is unlikely to be significantly
   faster, anyway. Most mature languages/environments do basic string
   manipulation pretty efficiently when left to their own devices.
   
   On Tue, Oct 18, 2016 at 1:35 PM, Bobby Mozumder <bmozumder@gmail.com> wrote:
   
   > How fast is Postgres's string concatenation in comparison to the various
   > Python string concatenation?  I'm using PREPARE statements for my SELECT
   > queries for my web server.
   >
   > I'm wondering if I should just generate my JSON API (or even HTML) strings
   > in Postgres directly, instead of in Python.  This would involve a few
   > IF-THEN-ELSE (in Python) which I convert to CASE-WHEN (in Postgres) as well.
   >
   > I’m not sure about the internals of Postgres and how it compares speedwise
   > to the Python bytecode interpreter (and future JIT compilers like PyPy).
   > Is Postgres generating bytecode and interpreting that for string
   > concatenation & Case statements?
   >
   > -bobby
   >
   > --
   > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-performance
   >