pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Steve Wampler <swampler@noao.edu>
Cc: Hervé Piedvache <herve@elma.fr>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL clustering VS MySQL clustering
Date: 20 Jan 2005 11:44:20 -0500
Message-ID: <87vf9scaor.fsf@stark.xeocode.com> (raw)
In-Reply-To: <41EFCAD4.6040307@noao.edu>
References: <200501201503.31241.herve@elma.fr>
	<41EFC3C0.6080903@commandprompt.com>
	<200501201600.47583.herve@elma.fr>
	<41EFCAD4.6040307@noao.edu>

Steve Wampler <swampler@noao.edu> writes:

> Hervé Piedvache wrote:
> 
> > No ... as I have said ... how I'll manage a database getting a table of may
> > be 250 000 000 records ? I'll need incredible servers ... to get quick access
> > or index reading ... no ?
> 
> Probably by carefully partitioning their data.  I can't imagine anything
> being fast on a single table in 250,000,000 tuple range.  

Why are you all so psyched out by the size of the table? That's what indexes
are for.

The size of the table really isn't relevant here. The important thing is the
size of the working set. Ie, How many of those records are required to respond
to queries.

As long as you tune your application so every query can be satisfied by
reading a (very) limited number of those records and have indexes to speed
access to those records you can have quick response time even if you have
terabytes of raw data. 

I would start by looking at the plans for the queries you're running and
seeing if you have any queries that are reading more than hundred records or
so. If so then you have to optimize them or rethink your application design.
You might need to restructure your data so you don't have to scan too many
records for any query.

No clustering system is going to help you if your application requires reading
through too much data. If every query is designed to not have to read more
than a hundred or so records then there's no reason you can't have sub-100ms
response time even if you had terabytes of raw data.

If the problem is just that each individual query is fast but there's too many
coming for a single server then something like slony is all you need. It'll
spread the load over multiple machines. If you spread the load in an
intelligent way you can even concentrate each server on certain subsets of the
data. But that shouldn't even really be necessary, just a nice improvement.

-- 
greg




view thread (131+ messages)  latest in thread

Message-ID: <87vf9scaor.fsf@stark.xeocode.com>
Permalink:  ../87vf9scaor.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87vf9scaor.fsf@stark.xeocode.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-performance@postgresql.org
  Cc: gsstark@mit.edu, swampler@noao.edu, herve@elma.fr, jd@commandprompt.com
  Subject: Re: PostgreSQL clustering VS MySQL clustering
  In-Reply-To: <87vf9scaor.fsf@stark.xeocode.com>

* 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