Re: Temporary Tables and Web Application

Hi,

Tim Tassonis wrote:
> Hi all
>
> I assume this is not an uncommon problem, but so far, I haven't been
> able to find a good answer to it.
>
> I've got a table that holds log entries and fills up very fast during
> the day, it gets approx. 25 million rows per day. I'm now building a web
> application using apache/mod_php where you can query the database and
> then should be able to page through the results.

you should be aware that PHP isnt the only scripting language with an
apache module and not neccessary the best choice among them.

> My idea was that whenever a user constructs a query, I create a
> temporary table holding the results and then page through this table,
> which should work very well in principle.

That means you are more or less constructing materialized views :-)
But if you hold the session anyway, then see below.

> But from what I've been able to find out, temporary tables live only in
> the Postgres Session they have been created in and are destroyed upon
> session descructuion.
>
> Now, with apache/php in a mpm environment, I have no guarantee that a
> user will get the same postgresql session for a subsequent request, thus
> he will not see the temporary table.

Thats the problem and if you have failover/loadbalancing situations,
even more so.

> Is there a way to create temporary tables in another way, so they are
> visible between sessions, or do I need to create real tables for my
> purpose? And is the perfomance penalty big for real tables, as they have
> been written to disk/read from disk?

To start with, you should avoid reconnecting to the database for every
request. Not only because of loosing the session context but also
to avoid connection overhead.

Usually this is done by connection pooling. You can then try to trac
user:connection relationship as much as possible thru the connection pool.

If you have that, there is actually no need for the temp tables. Instead
you can just use a regular cursor and scroll it as neccessary.

Almost all frameworks should give you reasonable pool implementations,
some additional memory caching on top of it and there are also a lot
of other methods to help you with that, for example pgpool and
pgbouncer.

Regards
Tino