Re: questions about PG update performance

On Mon, Oct 26, 2015 at 10:52 AM, Kisung Kim wrote:

Because of the internal implementation of MVCC in PG

the update of a row is actually a insertion of a new version row.

So if the size of a row is huge, then it incurs some overhead compare to in-place update strategy.

Yeah, that's how an UPDATE in Postgres for MVCC usage. The xmax of the old row is updated, and a new row is inserted with an xmin equal to the previous xmax. So if you update tuple fields one by one the cost is going to be high.

 

Let's assume that a table has 200 columns, 

and a user updates one of the columns of one row in the table.

Then PG will rewrite the whole contents of the updated row

including the updated columns and not-updated columns.

When a table has a large number of columns, usually I would say that you have a normalization problem and such schemas could be split into a smaller set of tables, minimizing the UPDATE cost.
 

I'm not sure about the implementation of Oracle's update.

But if the Oracle can overwrite only the updated column,

the performance difference between Oracle and PG in that case may be significant.

I researched about this issues in mailing list and google.

But I've not found anything related to this issues.

What you are looking at here is columnar storage, Alvaro and 2nd Quadrant folks have been doing some work in this area recently:
http://www.postgresql.org/message-id/20150831225328.GM2912@alvherre.pgsql

Also, you may want to have a look at cstore_fdw: https://github.com/citusdata/cstore_fdw.

Regards,

--

Michael