Thread: Query in cache

Hi,

I am need help, about subject "Query cache in  Postgres".
how is it possible to put sql statements cached in postgres ?
I did some tests and I can not get even with improved tuning
parameters in the postgresql.conf.

Regards,

Rogerio Pereira  rogerio.pereira@riosoft.com.br Respeite o meio-ambiente, não desperdice papel, imprima somente o necessário. Esta mensagem, incluindo os seus anexos, contém informações confidenciais destinadas a indivíduo e propósito específicos e é protegida por lei. Caso você não seja o destinatário desta mensagem, deve apagá-la. É proibida a utilização, acesso, cópia ou divulgação não autorizada das informações presentes neste e-mail. As informações contidas nesta mensagem e em seus anexos são de responsabilidade de seu autor, não representando idéias, opiniões, pensamentos da Riosoft.

Rogerio Pereira <rogerio.pereira@riosoft.com.br> wrote:

> Hi,
>
> I am need help, about subject "Query cache in  Postgres".
> how is it possible to put sql statements cached in postgres ?
> I did some tests and I can not get even with improved tuning
> parameters in the postgresql.conf.

No, there isn't something like a query cache. But you can use
materialized views, since 9.3.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Mon, Nov 18, 2013 at 02:38:09PM -0200, Rogerio Pereira wrote:
> Hi,
>
> I am need help, about subject "Query cache in  Postgres".
> how is it possible to put sql statements cached in postgres ?
> I did some tests and I can not get even with improved tuning
> parameters in the postgresql.conf.
>
> Regards,
>

Hi Rogerio,

PostgreSQL does not have a query cache. I think you would need to
roll your own.

Regards,
Ken

2013/11/18 Rogerio Pereira <rogerio.pereira@riosoft.com.br>
> I am need help, about subject "Query cache in  Postgres".
> how is it possible to put sql statements cached in postgres ?
> I did some tests and I can not get even with improved tuning
> parameters in the postgresql.conf.

Are you talking about prepared statements or about query result caching?

If former then you need to look at the PREPARE for execute statement
[1], though it is probably implemented in your data adapter, for
example like it is in DBD::Pg [2]. Also take a look at the pre_prepare
module [3], that can conveniently be used with pgbouncer.

If later then there is an extension named pgmemcache [4] that will
allow you to interact with memcached directly from postgres, so you
could implement cashing in stored functions, for example. However my
advice is to use application level caching with memcached in this
case, not the database level one.

[1] http://www.postgresql.org/docs/9.3/static/sql-prepare.html
[2] http://search.cpan.org/dist/DBD-Pg/Pg.pm#prepare
[3] https://github.com/dimitri/preprepare
[4] https://github.com/ohmu/pgmemcache/

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

On Mon, Nov 18, 2013 at 04:36:20PM -0800, salah jubeh wrote:
> Hello,
>
> pgpool supports memcache.
> Regards
>
Very cool. I had missed that in the release announcement.

Regards,
Ken

On Mon, Nov 18, 2013 at 04:36:20PM -0800, salah jubeh wrote:

Hello,

pgpool supports memcache.
Regards

Very cool. I had missed that in the release announcement.

Regards,
Ken

Rogerio Pereira Dba | Tel. 55 (17) 3215-9199 | rogerio.pereira@riosoft.com.br Respeite o meio-ambiente, não desperdice papel, imprima somente o necessário. Esta mensagem, incluindo os seus anexos, contém informações confidenciais destinadas a indivíduo e propósito específicos e é protegida por lei. Caso você não seja o destinatário desta mensagem, deve apagá-la. É proibida a utilização, acesso, cópia ou divulgação não autorizada das informações presentes neste e-mail. As informações contidas nesta mensagem e em seus anexos são de responsabilidade de seu autor, não representando idéias, opiniões, pensamentos da Riosoft.

Hi,

On Wed, 2013-11-20 at 10:36 -0200, Rogerio Pereira wrote:

> I'm trying install pgmemcache and occurs error:
>
> pgmemcache.h:29:23: error: sasl/sasl.h: No such file or directory
>
> Exists solution exists to fix to problem?

You will need to install cyrus-sasl-devel package.

Still, I just uploaded the RPM package to yum repo:

http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/repoview/pgmemcache-93.html

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR