column updates

[Ben]

I'm designing a system where I'll be making frequent updates to rows, but
some columns will change far less frequently than others. All columns will
be read with equal frequency, though probably by means of a materialized
view. Updates will happen via a stored proc.

Which makes the most sense?

1. Blindly overwrite the value for all columns, even columns that haven't
changed.

2. Compare the current value and update each column at a time, leaving
unchanged columns alone.

3. Break out my tables more, so that infrequently updated columns are in
different tables.
I'm thinking that 1 and 2 are similar and less complex than 3, but might
result in more dead tuples. 2 and 3 will have to look up values before
updates can happen. I don't know if 2 has any advantage over 1. 1 is
certainly the simpliest.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

The number of dead tuples is the number of UPDATE statements you make.
You if you just do a straight UPDATE x set f1=x, f2=y, ... that will be
as optimal as you can get.

The only exception may be toasted fields, which would only apply if you
had fields with more than 2K of data or so...

Hope this helps,

On Fri, Oct 29, 2004 at 12:24:35PM -0700, Ben wrote:

I'm designing a system where I'll be making frequent updates to rows, but
some columns will change far less frequently than others. All columns will
be read with equal frequency, though probably by means of a materialized
view. Updates will happen via a stored proc.

Which makes the most sense?

1. Blindly overwrite the value for all columns, even columns that haven't
changed.

2. Compare the current value and update each column at a time, leaving
unchanged columns alone.

3. Break out my tables more, so that infrequently updated columns are in
different tables.


I'm thinking that 1 and 2 are similar and less complex than 3, but might
result in more dead tuples. 2 and 3 will have to look up values before
updates can happen. I don't know if 2 has any advantage over 1. 1 is
certainly the simpliest.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBgrv5Y5Twig3Ge+YRAmreAKCu3rBCgH7EnOHJMAbpB2 KgmaQfJACfc+6W
Sl00ea6BS4g0G1V2mMfo6I4=
=ZawN
-----END PGP SIGNATURE-----

[Ben]

Thanks. Good to hear that the easiest solution for me is also the fastest.

On Fri, 29 Oct 2004, Martijn van Oosterhout wrote:

The number of dead tuples is the number of UPDATE statements you make.
You if you just do a straight UPDATE x set f1=x, f2=y, ... that will be
as optimal as you can get.

The only exception may be toasted fields, which would only apply if you
had fields with more than 2K of data or so...

Hope this helps,

On Fri, Oct 29, 2004 at 12:24:35PM -0700, Ben wrote:

I'm designing a system where I'll be making frequent updates to rows, but
some columns will change far less frequently than others. All columns will
be read with equal frequency, though probably by means of a materialized
view. Updates will happen via a stored proc.

Which makes the most sense?

1. Blindly overwrite the value for all columns, even columns that haven't
changed.

2. Compare the current value and update each column at a time, leaving
unchanged columns alone.

3. Break out my tables more, so that infrequently updated columns are in
different tables.
I'm thinking that 1 and 2 are similar and less complex than 3, but might
result in more dead tuples. 2 and 3 will have to look up values before
updates can happen. I don't know if 2 has any advantage over 1. 1 is
certainly the simpliest.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/

Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org