agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Query caching (with 8.3)
Date: Wed, 18 Dec 2013 10:45:37 +0200
Message-ID: <52B160B1.7000600@matrix.gatewaynet.com> (raw)
In-Reply-To: <CAL_0b1u12V1xGpUGWVH=e5LTWiXz0+MnSS3kG+qfKJfarR0q_g@mail.gmail.com>
References: <52AEDCF0.8000606@matrix.gatewaynet.com>
<CAL_0b1u12V1xGpUGWVH=e5LTWiXz0+MnSS3kG+qfKJfarR0q_g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (8+ messages)
Message-ID: <52B160B1.7000600@matrix.gatewaynet.com>
Permalink: ../52B160B1.7000600@matrix.gatewaynet.com/
Also on: postgresql.org/message-id/52B160B1.7000600@matrix.gatewaynet.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, gray.ru@gmail.com
Subject: Re: Query caching (with 8.3)
In-Reply-To: <52B160B1.7000600@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox