From: Shane Ambler <pgsql@007Marketing.com>
To: Daniel van Ham Colchete <daniel.colchete@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: New to PostgreSQL, performance considerations
Date: Mon, 11 Dec 2006 10:22:08 +1030
Message-ID: <457C9DA8.1000403@007Marketing.com> (raw)
In-Reply-To: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com>
References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com>
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
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: pgsql@007Marketing.com, daniel.colchete@gmail.com
Subject: Re: New to PostgreSQL, performance considerations
In-Reply-To: <457C9DA8.1000403@007Marketing.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