Re: INSERT performace.

Marc Mitchell wrote:
> We have a fresh database and have begun to observe performance degradation
> for INSERTs as a table went from empty to 100,000-ish rows. Initial
> INSERTs were sub second while after 30k rows, they were 1-3 seconds.
>
> Note that we have done NO performance tuning yet nor are there any indexes
> on the tables.

Can we have PostgreSQL version and OS information?

> My question is about the way Postgres manages the organizational structure
> of the base table data. From my Ingres background, I know that that
> product allows you to set the structure of the base data to be any of it's
> supported types (Hash, Heap, Btree, etc.) . And my experience tells me
> that this can greatly effect INSERT performance and must be properly
> managed. For example, you have to consider the allocation of data pages
> for hash and overflow and page fill factors for btrees to avoid excessive
> splits and balancing of index data pages. Is there anything like this in
> Postgres?

Data is stored in flat heap files in PostgreSQL. It is not like Ingres
where you can put a structure on the base table; it is always heap, and
you add indexes as you need them.

Off the top of my head I can't guess why the INSERT would get slower
because it is merely putting data on the end of the table.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026