[OSM-dev] scaling

Steve Singer ssinger_pg at sympatico.ca
Tue Jan 11 03:31:34 GMT 2011


On Tue, 11 Jan 2011, Matt Amos wrote:

> it's possible to rewrite it so that it doesn't use temp tables, but
> that would be at the cost of more complex queries or higher memory
> usage. i presume that postgres doesn't currently allow it because it's
> technically non-trivial, but beyond that i have no idea what the level
> of difficulty is...
>
> i had a quick look on the postgres mailing lists and didn't spot
> anything obvious about it - is it worth bringing it up there and
> seeing what their take on it is?

There have been discussions on pgsql-hackers about adding temp tables to
replicas [1].  We aren't the only ones who would like that feature.  Temp
tables on replicas won't be in 9.1.

Rewriting queries so they don't use temp tables won't run into any memory 
limits.  When a query result exceeds work_mem Postgresql automatically 
spills to disk.

It might also be worth thinking about some of non built-in replication 
options (ie Slony or Londiste).  They do introduce some overhead to writes 
on the master but if we can offload enough reads you can see significant 
overal gains. Administration pain will go up but that comes with the 
teritory of clustering.


[1] - http://archives.postgresql.org/pgsql-hackers/2010-10/msg01208.php


>
> cheers,
>
> matt
>

Steve Singer




More information about the dev mailing list