I'm sorry I have to come back at this, but the solution the list gave helped,
but didn't fully solved my problems...
To sum up:
I have a simple table that has indexes on 2 integer columns.
Data is inserted very often (no updates, no deletes, just inserts): at
least 4000/5000 rows per second.
The usual set of tricks is to
increase shared_buffers,
checkpoint_segments, and checkpoint_timeout to cut down
That did it. Setting a much higher shared_buffers helped quite a lot.
Thank you everybody for your suggestions.
--
Sent via pgsql-general mailing list
Does it help to reindex the index at that point?
Didn't try; but I guess a reindex of such a big table
would block inserts for a long time... but I'll try
Bad. The partitioning code isn't designed to scale
beyond a few dozen partitions.
What kind of problems am I going to experience?
It's
On a few very narrow applications I've gotten good
performance in the
low hundreds. After that things fall apart
quickly.
Ehm... what exactly fall apart quickly means?
I can trade some select speed for insert speed...
I don't have experience with partitioning, if some of
you already know
The usual set of tricks is to
increase shared_buffers, checkpoint_segments, and checkpoint_timeout to cut
down
Uh, didn't know shared_buffers played a role in index insertion as well...
got to try that. Thank you.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
Hi,
I have a simple table that has indexes on 2 integer columns.
Data is inserted very often (no updates, no deletes, just inserts):
at least 4000/5000 rows per second.
The input for the 2 indexed columns is very random.
Everything is fine for the first 10-20M rows; after that, performance
gets
Leonardo F m_li...@yahoo.it writes:
I have a simple table that has indexes on 2 integer columns.
Data is inserted very often (no updates, no deletes, just inserts):
at least 4000/5000 rows per second.
The input for the 2 indexed columns is very random.
Everything is fine for the first 10-20M
On Mon, Apr 19, 2010 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Leonardo F m_li...@yahoo.it writes:
I have a simple table that has indexes on 2 integer columns.
Data is inserted very often (no updates, no deletes, just inserts):
at least 4000/5000 rows per second.
The input for the 2
Leonardo F wrote:
Is there anything else I can try to help postgres update those
index faster?
It sounds like your problem is that read/write activities on the indexes
are becoming increasingly large and random as more data is inserted.
There are two standard ways to improve on that: