Re: Should I generate strings in Postgres of Python?

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>