Skip site navigation (1) Skip section navigation (2)

Re: Large number of tables slow insert

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Matthew Wakeling" <matthew(at)flymine(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Large number of tables slow insert
Date: 2008-08-26 15:29:15
Message-ID: dcc563d10808260829s6d397b7egd364589c9c5b16b1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Aug 26, 2008 at 6:50 AM, Matthew Wakeling <matthew(at)flymine(dot)org> wrote:
> On Sat, 23 Aug 2008, Loic Petit wrote:
>>
>> I use Postgresql 8.3.1-1 to store a lot of data coming from a large amount
>> of sensors. In order to have good
>> performances on querying by timestamp on each sensor, I partitionned my
>> measures table for each sensor. Thus I create
>> a lot of tables.
>
> As far as I can see, you are having performance problems as a direct result
> of this design decision, so it may be wise to reconsider. If you have an
> index on both the sensor identifier and the timestamp, it should perform
> reasonably well. It would scale a lot better with thousands of sensors too.

Properly partitioned, I'd expect one big table to outperform 3,000
sparsely populated tables.

In response to

pgsql-performance by date

Next:From: hubert depesz lubaczewskiDate: 2008-08-26 16:14:27
Subject: Re: Autovacuum does not stay turned off
Previous:From: Jerry ChamplinDate: 2008-08-26 15:27:48
Subject: Autovacuum does not stay turned off

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group