Do you mean I should use PREPARE?
Currently I use PHP to access the DB which use libpq. Is that cosidered a
fast call API ? if not, can you please refer me to the right info.
PHP pg_pconnect command open a persistent PostgreSQL connection. Is it
enough or I better use PgPool2 or something similar?
Considering the points above, will I be able to get such high QPS from
PostgreSQL ? If so, it will be my pleasure to dump Reddis and work solely
with PG :)
I suppose you already have a web server like lighttpd, zeus, or nginx,
using php as fastcgi, or apache behind a proxy ? In that case, since the
number of php processes is limited (usually to something like 2x your
number of cores), the number of postgres connections a web server
generates is limited, and you can do without pgpool and use pg_pconnect.
Be wary of the pg_pconnect bugs though (like if you restart pg, you also
have to restart php, I suppose you know that).
Here are some timings (Core 2 Q6600) for a simple SELECT on PK query :
using tcp (localhost)
218 Âs / query : pg_query
226 Âs / query : pg_query_params
143 Âs / query : pg_execute
using unix sockets
107 Âs / query : pg_query
122 Âs / query : pg_query_params
63 Âs / query : pg_execute
query inside plpgsql function
17 Âs / query
Don't use PDO, it is 2x-3x slower.
TCP overhead is quite large...
If you have a named prepared statement (created with PREPARE) use
pg_execute(), which is much faster than pg_query (for very simple queries).
Of course you need to prepare the statements... you can do that with
pg_pool which can execute a script upon connection initialization.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
