[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