> 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.
