From: Russell Smith <mr-russ@pws.com.au>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: TB-sized databases
Date: Fri, 30 Nov 2007 17:41:53 +1100
Message-ID: <474FB0B1.6070900@pws.com.au> (raw)
In-Reply-To: <1196270908.4246.1250.camel@ebony.site>
References: <4544e0330711260904g45d893a8ub1731e245aa8fc84@mail.gmail.com>
<474B1407.3090607@laotraesquina.com.ar>
<474B1B8C.1030505@gmail.com>
<4544e0330711271218h56ce4cc3p280a9fefff24583d@mail.gmail.com>
<1196197045.4246.1154.camel@ebony.site>
<474CA2FA.2020201@laotraesquina.com.ar>
<1196270908.4246.1250.camel@ebony.site>
Simon Riggs wrote:
> On Tue, 2007-11-27 at 18:06 -0500, Pablo Alcaraz wrote:
>
>> Simon Riggs wrote:
>>
>>> All of those responses have cooked up quite a few topics into one. Large
>>> databases might mean text warehouses, XML message stores, relational
>>> archives and fact-based business data warehouses.
>>>
>>> The main thing is that TB-sized databases are performance critical. So
>>> it all depends upon your workload really as to how well PostgreSQL, or
>>> another other RDBMS vendor can handle them.
>>>
>>>
>>> Anyway, my reason for replying to this thread is that I'm planning
>>> changes for PostgreSQL 8.4+ that will make allow us to get bigger and
>>> faster databases. If anybody has specific concerns then I'd like to hear
>>> them so I can consider those things in the planning stages
>>>
>> it would be nice to do something with selects so we can recover a rowset
>> on huge tables using a criteria with indexes without fall running a full
>> scan.
>>
>> In my opinion, by definition, a huge database sooner or later will have
>> tables far bigger than RAM available (same for their indexes). I think
>> the queries need to be solved using indexes enough smart to be fast on disk.
>>
>
> OK, I agree with this one.
>
> I'd thought that index-only plans were only for OLTP, but now I see they
> can also make a big difference with DW queries. So I'm very interested
> in this area now.
>
>
If that's true, then you want to get behind the work Gokulakannan
Somasundaram
(http://archives.postgresql.org/pgsql-hackers/2007-10/msg00220.php) has
done with relation to thick indexes. I would have thought that concept
particularly useful in DW. Only having to scan indexes on a number of
join tables would be a huge win for some of these types of queries.
My tiny point of view would say that is a much better investment than
setting up the proposed parameter. I can see the use of the parameter
though. Most of the complaints about indexes having visibility is about
update /delete contention. I would expect in a DW that those things
aren't in the critical path like they are in many other applications.
Especially with partitioning and previous partitions not getting may
updates, I would think there could be great benefit. I would think that
many of Pablo's requests up-thread would get significant performance
benefit from this type of index. But as I mentioned at the start,
that's my tiny point of view and I certainly don't have the resources to
direct what gets looked at for PostgreSQL.
Regards
Russell Smith
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-performance@postgresql.org
Cc: mr-russ@pws.com.au, simon@2ndquadrant.com
Subject: Re: TB-sized databases
In-Reply-To: <474FB0B1.6070900@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox