Re: Query caching (with 8.3)

On 17/12/2013 22:26, Sergey Konoplev wrote:
> You can try to increase work_mem first, because if the returning data set is big enough it might start working with your disk drive, that might cause to significant slowdowns. Another thing is that,
> IIRC, there were no plan caching for RETURN QUERY in PL/PgSQL, so try to rewrite it like FOR ... LOOP RETURN NEXT ... END LOOP. IMHO, these are the only non-quirky ways to improve things. ps.

Thanx, good to know that.

>> Lazy replication solution.
>> Since you mention it, this is installed on about 90 vessels at sea, and if
>> we assume 3000 EUR (tickets only) for a
>> trained person to get on board and perform the upgrade, this amounts to
>> 270,000 EUR.
> Wow, I just wonder how do you guys manage to support/maintain these DB
> servers then?

We periodically (daily) have partial backups of data which reside only on the vessel side. In other words,
we back up only data which do not exist in the master site.
In case of disaster we prepare a new vessel database, and then incrementally run the local restore
created from the periodic local backup mentioned above.

Taking into account that during the last 10 years, this has happened about 2-3 times,
i'd say the cost is hard to justify. If/when we upgrade, it would be to improve performance,
mainly, along the rest of obvious benefits, <joking> and not because some bad governmental
agency would want to hack the vessels systems.... (we work for governments in the first place,
they have much more civil and simple ways to get our data) </joking>

Anyway, thing is, PostgreSQL 8.3 has been performing like a real beast, and i think it could be used
as a case for advertising its long term stability, in a almost military environment (vibrations, etc...),
and most importantly 99.99% unmanned.

--
Achilleas Mantzios