Date: Wed, 8 Aug 2007 12:28:14 -0500
From: Decibel! <decibel@decibel.org>
To: Erik Jones <erik@myemma.com>
Cc: Mark Makarowsky <bedrockconstruction@yahoo.com>,
	pgsql-performance@postgresql.org
Subject: Re: Update table performance
Message-ID: <20070808172814.GJ20424@nasby.net>
References: <670773.87500.qm@web32209.mail.mud.yahoo.com>
	<1C6EEA75-603F-4B02-8127-920F17AC5411@myemma.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="z+pzSjdB7cqptWpS"
Content-Disposition: inline
In-Reply-To: <1C6EEA75-603F-4B02-8127-920F17AC5411@myemma.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


--z+pzSjdB7cqptWpS
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 07, 2007 at 08:46:20PM -0500, Erik Jones wrote:
> Vertical partitioning is where you split up your table on disk by =20
> columns, i.e on the vertical lines.  He quoted it because Postgres =20
> doesn't actually support it transparently but you can always fake it =20
> by splitting up your table.  For example, given the following table =20
> wherein column bar gets updated a lot but the others don't:
>=20
> create table foo (
> id	int 	not null,
> bar	int,
> baz 	int,
>=20
> primary key (id)
> );
>=20
> You could split it up like so:
>=20
> create table foo_a (
> id 	int,
> baz	int,
>=20
> primary key (id)
> );
>=20
> create table foo_b (
> foo_id	int,
> bar		int,
>=20
> foreign key foo_a_id (foo_id) references foo_a (id)
> );

FWIW, the cases where I've actually used this have been on much wider
tables, and a number of the attributes are in-frequently accessed. An
example would be if you keep snail-mail address info for users; you
probably don't use those fields very often, so they would be good
candidates for going into a second table.

When does it actually make sense to use this? When you do a *lot* with a
small number of fields in the table. In this example, perhaps you very
frequently need to look up either user_name or user_id, probably via
joins. Having a table with just name, id, perhaps password and a few
other fields might add up to 50 bytes per row (with overhead), while
address information by itself could easily be 50 bytes. So by pushing
that out to another table, you cut the size of the main table in half.
That means more efficient use of cache, faster seqscans, etc.

The case Erik is describing is more unique to PostgreSQL and how it
handles MVCC. In some cases, splitting a frequently updated row out to a
separate table might not gain as much once we get HOT, but it's still a
good tool to consider. Depending on what you're doing another useful
technique is to not update the field as often by logging updates to be
performed into a separate table and periodically processing that
information into the main table.
--=20
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--z+pzSjdB7cqptWpS
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGuf0udO30qud8SkgRAhn3AKCnfzZtX2m+EN61wiTwdhskfcERkwCgmCD1
vHZZfAFzvrAambk06fmpBAY=
=7QlD
-----END PGP SIGNATURE-----

--z+pzSjdB7cqptWpS--