Re: New to PostgreSQL, performance considerations

[Shane Ambler <pgsql(at)007Marketing(dot)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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz