From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Robert DiFalco <robert.difalco@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Number of Columns and Update
Date: Mon, 22 Dec 2014 23:40:13 +0200
Message-ID: <54988FBD.1050506@vmware.com> (raw)
In-Reply-To: <CAAXGW-xXFDWHEBJExt40bWD1+vOo6_UbytCUjEe625GqBXmxUA@mail.gmail.com>
References: <CAAXGW-xXFDWHEBJExt40bWD1+vOo6_UbytCUjEe625GqBXmxUA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 12/22/2014 10:53 PM, Robert DiFalco wrote:
> This may fall into the category of over-optimization but I've become
> curious.
>
> I have a user table with about 14 columns that are all 1:1 data - so they
> can't be normalized.
>
> When I insert a row all columns need to be set. But when I update, I
> sometimes only update 1-2 columns at a time. Does the number of columns
> impact update speed?
>
> For example:
> UPDATE users SET email = ? WHERE id = ?;
Yes, the number of columns in the table matters. The update is just as
expensive regardless of how many of the columns you update.
When a row is updated, PostgreSQL creates a new version of the whole
row. The new row version takes more space when the table has more
columns, leading to more bloating of the table, which generally slows
things down. In most applications the difference isn't big enough to
matter, but it can be significant if you have very wide rows, and you
update a lot.
PostgreSQL 9.4 made an improvement on this. In earlier versions, the new
row version was also included completely in the WAL record, which added
overhead. In 9.4, any columns at the beginning or end of the row that
are not modified are left out of the WAL record, as long as the new row
version is stored on the same page as the old one (which is common). For
updating a single column, or a few columns that are next to each other,
that's the same as saying that only the modified part of the row is
WAL-logged.
> I can easily break this up into logical tables like user_profile,
> user_credential, user_contact_info, user_summary, etc with each table only
> having 1-4 columns. But with the multiple tables I would often be joining
> them to bring back a collection of columns.
That would help with the above-mentioned issues, but dealing with
multiple tables certainly adds a lot of overhead too. Most likely you're
better off just having the single table, after all.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, robert.difalco@gmail.com
Subject: Re: Number of Columns and Update
In-Reply-To: <54988FBD.1050506@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox