DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=LXoZ3tM//1etTS6phIuQBq+7XRY+NEpXtUDlIFZ36mGGfetiQdDQMHx4Xn/5QpmT4KOLg2LBfmlL14UTxy84IXPkUOchKNLbLAZBvZXFwMhWyAhcBibjeypYmgA+aPCZINzQkZxNUDmypsP6NfAxn6p3N2pFVOummbFHkJBcvSg=
Message-ID: <8a0c7af10612101715p6a1b00e6yc33b8ea0b95a683d@mail.gmail.com>
Date: Sun, 10 Dec 2006 23:15:31 -0200
From: "Daniel van Ham Colchete" <daniel.colchete@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: New to PostgreSQL, performance considerations
In-Reply-To: <457C9DA8.1000403@007Marketing.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com>
	<457C9DA8.1000403@007Marketing.com>

Shane,

really good answer man!!! It really helped me!

I can't wait to have time to test this partitions thing. This will
really solve a lot of my problems (and another one that I was
delaying).

Instead of making partitions over days/weeks I can tune it to the
seconds (timestamp) and try to find the best partition size for
performance. That partition thing is really good.

That server you sent me handles about 15 times more writes than I would have to.

Thank you very much for your answer.

Best regards,
Daniel Colchete

On 12/10/06, Shane Ambler <pgsql@007marketing.com> wrote:
> Daniel van Ham Colchete wrote:
> > although I've worked with databases for more than 7 years now, I'm
> > petty new to PostgreSQL.
>
> Same here.
>
> > I need a db that can handle something like 500 operations/sec
> > continuously. It's something like 250 writes/sec and 250 reads/sec. My
> > databases uses indexes.
>
> Taken from an email to the admin list about a week ago -
>
> Stats about the system:
> Postgres 8.1.4
> db size: 200+ GB
> Inheritance is used extremely heavily, so in figuring out what could
> cause a create to hang, it may be of interest to know that there are:
> 101,745 tables
> 314,821 indexes
> 1,569 views
> The last averages taken on the number of writes per hour on this
> database: ~3 million (this stat is a few weeks old)
>
> Machine info:
> OS: Solaris 10
> Sunfire X4100 XL
> 2x AMD Opteron Model 275 dual core procs
> 8GB of ram
>
>
> > Each table would have to handle 5 million rows/day. So I'm thinking
> > about creating different tables (clusters?) to different days to make
> > queries return faster. Am I right or there is no problem in having a
> > 150 million (one month) rows on a table?
>
> Sounds to me that a month might be on the large size for real fast
> response times - I would think of seperating weekly rather than daily.
>
> Start with
> http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
> then the next chapter explains using that to partition data into
> different tables dependant on specified criteria.
>
> You may be interested in tsearch2 which is in the contrib dir and adds
> full text indexing.
>
>
> --
>
> Shane Ambler
> pgSQL@007Marketing.com
>
> Get Sheeky @ http://Sheeky.Biz
>