pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedShould I generate strings in Postgres of Python?
2+ messages / 2 participants
[nested] [flat]
* Should I generate strings in Postgres of Python?
@ 2016-10-18 20:35 Bobby Mozumder <bmozumder@gmail.com>
2016-10-18 22:53 ` Re: Should I generate strings in Postgres of Python? Sam Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 2+ messages in thread
From: Bobby Mozumder @ 2016-10-18 20:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Should I generate strings in Postgres of Python?
2016-10-18 20:35 Should I generate strings in Postgres of Python? Bobby Mozumder <bmozumder@gmail.com>
@ 2016-10-18 22:53 ` Sam Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 2+ messages in thread
From: Sam Gendler @ 2016-10-18 22:53 UTC (permalink / raw)
To: Bobby Mozumder <bmozumder@gmail.com>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-10-18 22:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-18 20:35 Should I generate strings in Postgres of Python? Bobby Mozumder <bmozumder@gmail.com>
2016-10-18 22:53 ` Sam Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox