Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37597=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VtClJ-0008MT-MU
 for pgsql-sql@arkaria.postgresql.org; Wed, 18 Dec 2013 08:45:49 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37597=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VtClJ-0005Bz-5R
 for pgsql-sql@arkaria.postgresql.org; Wed, 18 Dec 2013 08:45:49 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <achill@matrix.gatewaynet.com>) id 1VtClI-0005Bt-EG
 for pgsql-sql@postgresql.org; Wed, 18 Dec 2013 08:45:48 +0000
Received: from adsltrust.ath.forthnet.gr ([194.219.204.174]
 helo=smadev.internal.net)
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <achill@matrix.gatewaynet.com>) id 1VtClB-0006lr-GE
 for pgsql-sql@postgresql.org; Wed, 18 Dec 2013 08:45:47 +0000
Received: from smadev.internal.net (smadev [10.9.200.131])
 by smadev.internal.net (8.14.7/8.14.7) with ESMTP id rBI8jbQH048634;
 Wed, 18 Dec 2013 10:45:37 +0200 (EET)
 (envelope-from achill@matrix.gatewaynet.com)
Message-ID: <52B160B1.7000600@matrix.gatewaynet.com>
Date: Wed, 18 Dec 2013 10:45:37 +0200
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64;
 rv:24.0) Gecko/20100101 Thunderbird/24.0.1
MIME-Version: 1.0
To: Sergey Konoplev <gray.ru@gmail.com>
CC: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Query caching (with 8.3)
References: <52AEDCF0.8000606@matrix.gatewaynet.com>
 <CAL_0b1u12V1xGpUGWVH=e5LTWiXz0+MnSS3kG+qfKJfarR0q_g@mail.gmail.com>
In-Reply-To: 
 <CAL_0b1u12V1xGpUGWVH=e5LTWiXz0+MnSS3kG+qfKJfarR0q_g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: 0.0 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql