OK, last post on this topic, I promise. I'm doing some math, and I think I'll have about 100 million rows in the table to deal with.
Given a table that size, I'd like to do the following math:
100 million rows / inserted rows per second = total seconds
Does anyone have a reasonable guess as to the inserts per second postgres is capable of these days on middle-of-the-road hardware? Any order of magnitude would be fine: 10, 100, 1000, 10,000.
Thank you all for your patience,
Carson
On Tue, Mar 13, 2012 at 8:24 PM, Carson Gross
<carsongross@gmail.com> wrote:
Got it.
Thank you, that's very helpful: we could delete quite a few of the rows before we did the operation and cut way down on the size of the table before we issue the update. Trimming the table size down seems obvious enough, but that's good confirmation that it will very much help. And there are quite a few indexes that I've discovered are useless, so dropping those will speed things up too.
Looking online I see that a query progress indicator is a commonly requested feature, but isn't yet implemented, so it sound like my best bet is to clone the db on similar hardware, take all the advice offered here, and just see how it performs.
Thanks to everyone for the feedback,
Carson
On Tue, Mar 13, 2012 at 6:32 PM, John R Pierce
<pierce@hogranch.com> wrote:
On 03/13/12 6:10 PM, Carson Gross wrote:
As a follow up, is the upgrade from integer to bigint violent? I assume so: it has to physically resize the column on disk, right?