[postgis-users] Convert already loaded table into a spatial table (points)

[Paul Ramsey]

PgSQL uses MVCC
<http://en.wikipedia.org/wiki/Multiversion_concurrency_control> for
row-level transaction handling. You might thing that when you "update"
a row, new values are simply written on top of the old ones. They are
not. A new copy of the row is created, with a new timestamp.
Eventually, when no clients are looking at the old row anymore, it is
marked as deleted and eventually re-used. But in the case of a bulk
full-table update, you end up with a full copy of the table tacked on
the bottom as all the update copies get written. The extra overhead of
doing this seems to be worse than the overhead of the "create table as
..." approach, though I am not smart enough to tell you why.

Kevin has many experiences and useful tips on handling really large tables.

P.

On Thu, Aug 12, 2010 at 12:24 PM, Mauricio Miranda
<mmiranda at xoomcode.com> wrote:
> On Aug 12, 2010, at 15:56 PM, Paul Ramsey wrote:
>
>> Because of transaction handling, updating every row in a database is
>> the equivalent of a full load. It might actually take longer than the
>> initial load.
>>
>> I'd suggest something like
>>
>>  create newtable as select st_makepoint(x,y) as point, a1, a2, a2...
>> from oldtable
>>  drop oldtable
>>  alter newtable rename to oldtable
>>
>> P.
>>
>
>
> Sorry Paul but I got very surprised with you answer. I can't understand why to create all the records again is faster than update them.
>
> Can you please give some links to read about it?
>
> We are getting performance problems with some table updates and I guess that could be the reason.
>
> Thanks in advance.
>
> --
> Mauricio Miranda
> Software Engineering Manager
> http://www.xoomcode.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>