Default fill factor for tables?

"Francisco Reyes" <lists@stringsutils.com> writes:

While reading http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html I discovered that tables can have a fill factor. However, the create table page doesn't mention what the default is.

100%

regards, tom lane

On Fri, Jul 11, 2008 at 9:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Francisco Reyes" <lists@stringsutils.com> writes:

While reading http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html I discovered that tables can have a fill factor. However, the create table page doesn't mention what the default is.

100%

Is there any way to set a different default fill factor?

On Fri, Jul 11, 2008 at 10:27 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Is there any way to set a different default fill factor?

ALTER TABLE Yourtable
SET ( FILLFACTOR = 50 );

It take a bit of back tracking on the storage_parameter section:
http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html <-
see storage_parameter
http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html <-
see storage_parameter
http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
<- see FILLFACTOR

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Fri, Jul 11, 2008 at 12:12 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Fri, Jul 11, 2008 at 10:27 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Is there any way to set a different default fill factor?

ALTER TABLE Yourtable
SET ( FILLFACTOR = 50 );

Hehe. I know how to do that. I mean the default fill factor for a
database / user for tables / indexes.. Like setting search_path for a
user.

alter database mydb set indexfillfactor=50;
alter user me set tablefillfactor=75;

so that newly created indexes in the db mydb have fillfactor of 50 and
tables created by me are at 75.

On Fri, 2008-07-11 at 12:52 -0600, Scott Marlowe wrote:

On Fri, Jul 11, 2008 at 12:12 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Fri, Jul 11, 2008 at 10:27 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Is there any way to set a different default fill factor?

ALTER TABLE Yourtable
SET ( FILLFACTOR = 50 );

Hehe. I know how to do that. I mean the default fill factor for a
database / user for tables / indexes.. Like setting search_path for a
user.

I think the reasoning was/is that setting it away from the default is
only sensible in certain table-specific cases, so such a setting would
more likely result in a general drop in performance.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Is there any way to set a different default fill factor?

ALTER TABLE Yourtable
SET ( FILLFACTOR = 50 );

Hehe. I know how to do that. I mean the default fill factor for a
database / user for tables / indexes.. Like setting search_path for a
user.

alter database mydb set indexfillfactor=50;
alter user me set tablefillfactor=75;

so that newly created indexes in the db mydb have fillfactor of 50 and
tables created by me are at 75.

Why would you set the fillfactor to anything other than 100 for a
PostgreSQL table?

http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html

I thought an updated record always got a new row in a table. Setting a
lower fillfactor for a table suggests that PostgreSQL behaves like
Oracle in terms of filling blocks up and having chained rows.

Jon

Roberts, Jon escribi�:

Why would you set the fillfactor to anything other than 100 for a
PostgreSQL table?

To favor HOT updates.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Jul 11, 2008 at 2:10 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2008-07-11 at 12:52 -0600, Scott Marlowe wrote:

On Fri, Jul 11, 2008 at 12:12 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Fri, Jul 11, 2008 at 10:27 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Is there any way to set a different default fill factor?

ALTER TABLE Yourtable
SET ( FILLFACTOR = 50 );

Hehe. I know how to do that. I mean the default fill factor for a
database / user for tables / indexes.. Like setting search_path for a
user.

I think the reasoning was/is that setting it away from the default is
only sensible in certain table-specific cases, so such a setting would
more likely result in a general drop in performance.

I would kindly disagree. I'm looking at a project where HOT updates
are going to be a real performance enhancement, but I'll have to
create a hundred or so tables ALL with fillfactor tacked on the end.

Imagine dumping a db, setting the new target DB to default fill
factors and then reloading all the data from the dump. No need to
edit the dump file or a schema dump etc... I could definitely see a
lot of use cases for a default fill factor.

Roberts, Jon escribió:

Why would you set the fillfactor to anything other than 100 for a
PostgreSQL table?

To favor HOT updates.

--

I can find very little information on hot updates but I found this: http://archives.postgresql.org/pgsql-patches/2006-11/msg00059.php

It states, "This design optimizies the updates when none of the index columns are modified and length of the tuple remains the same after update."

How can a row's length change? I think it must mean the size (in bytes) of the row remains the same.

If this is the constraint, then I still don't see the benefit. If the size can vary, I can see the benefit because the new column value may be larger than the old value thus needing the space.

Why isn't the hot update documented in these locations?
http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.3/interactive/sql-update.html
http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html

Jon

On Fri, 2008-07-11 at 14:51 -0600, Scott Marlowe wrote:

I would kindly disagree. I'm looking at a project where HOT updates
are going to be a real performance enhancement, but I'll have to
create a hundred or so tables ALL with fillfactor tacked on the end.

You clearly think that adjusting fillfactor helps in all cases with HOT.
I disagree with that, else would have pushed earlier for exactly what
you suggest. In fact, I've has this exact discussion previously.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, Jul 11, 2008 at 3:24 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2008-07-11 at 14:51 -0600, Scott Marlowe wrote:

I would kindly disagree. I'm looking at a project where HOT updates
are going to be a real performance enhancement, but I'll have to
create a hundred or so tables ALL with fillfactor tacked on the end.

You clearly think that adjusting fillfactor helps in all cases with HOT.
I disagree with that, else would have pushed earlier for exactly what
you suggest. In fact, I've has this exact discussion previously.

How odd, because that's clearly NOT what I said. In fact I used the
single "a" to describe the project I was looking at where having a
default table fill factor of < 100 would be very useful. OTOH, I have
stats databases that have only insert and drop child tables that would
not benefit from < 100 fill factor. For a heavily updated database,
where most of the updates will NOT be on indexed columns, as the ONE
project I'm looking at, a default fill factor would be quite a time
saver.

On Fri, 2008-07-11 at 15:25 -0600, Scott Marlowe wrote:

On Fri, Jul 11, 2008 at 3:24 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2008-07-11 at 14:51 -0600, Scott Marlowe wrote:

I would kindly disagree. I'm looking at a project where HOT updates
are going to be a real performance enhancement, but I'll have to
create a hundred or so tables ALL with fillfactor tacked on the end.

You clearly think that adjusting fillfactor helps in all cases with HOT.
I disagree with that, else would have pushed earlier for exactly what
you suggest. In fact, I've has this exact discussion previously.

How odd, because that's clearly NOT what I said. In fact I used the
single "a" to describe the project I was looking at where having a
default table fill factor of < 100 would be very useful. OTOH, I have
stats databases that have only insert and drop child tables that would
not benefit from < 100 fill factor. For a heavily updated database,
where most of the updates will NOT be on indexed columns, as the ONE
project I'm looking at, a default fill factor would be quite a time
saver.

I apologise if my phrasing sounded confrontational.

For specific workloads, tuning of particular tables can be effective,

I have not heard of evidence that setting fillfactor < 100 helps as an
across-the-board tuning measure on longer-term tests of performance.
Theoretically, it makes little sense, but current theory is not always
right. Until we have even hear-say evidence of benefit, introducing a
parameter would be inadvisable, IMHO. I will change that view in an
instant, with reasonable evidence.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, Jul 11, 2008 at 5:53 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2008-07-11 at 15:25 -0600, Scott Marlowe wrote:

On Fri, Jul 11, 2008 at 3:24 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2008-07-11 at 14:51 -0600, Scott Marlowe wrote:

I would kindly disagree. I'm looking at a project where HOT updates
are going to be a real performance enhancement, but I'll have to
create a hundred or so tables ALL with fillfactor tacked on the end.

You clearly think that adjusting fillfactor helps in all cases with HOT.
I disagree with that, else would have pushed earlier for exactly what
you suggest. In fact, I've has this exact discussion previously.

How odd, because that's clearly NOT what I said. In fact I used the
single "a" to describe the project I was looking at where having a
default table fill factor of < 100 would be very useful. OTOH, I have
stats databases that have only insert and drop child tables that would
not benefit from < 100 fill factor. For a heavily updated database,
where most of the updates will NOT be on indexed columns, as the ONE
project I'm looking at, a default fill factor would be quite a time
saver.

I apologise if my phrasing sounded confrontational.

For specific workloads, tuning of particular tables can be effective,

I have not heard of evidence that setting fillfactor < 100 helps as an
across-the-board tuning measure on longer-term tests of performance.
Theoretically, it makes little sense, but current theory is not always
right. Until we have even hear-say evidence of benefit, introducing a
parameter would be inadvisable, IMHO. I will change that view in an
instant, with reasonable evidence.

Ok, here's my scenario. We have a content management / calendaring /
social networking website. The tables in this db fall into two
categories, and that's either small lookup tables like a list of
states which are seldom updated, or fairly large tables with lots of
data that are updated constantly. There are literally several hundred
medium to large tables that are updated constantly. There are a dozen
or so lookup tables, which are small.

Now, if I had a default fill factor of 90%, I doubt you could detect a
performance slow down on the smaller tables. But I'm quite sure we'll
see a difference on the large heavily updated tables. Now, I've got
about 20Gigs of data to migrate from 8.1 to 8.3 and I'll be dumping
the schema and data separately for this one. So I can go in and edit
every single create table and create index portion of the schema to
set a fill factor. It would be so much easier to group my tables by
type and set a default fill factor.

It's not like there isn't already a default fill factor, there IS.
It's just hard coded into pgsql. I can't see where having a knob
exposed to change that would be a particularly bad thing. Can't be
more of a foot gun than work_mem.

On Sat, Jul 12, 2008 at 2:25 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:

--

I can find very little information on hot updates but I found this: http://archives.postgresql.org/pgsql-patches/2006-11/msg00059.php

It states, "This design optimizies the updates when none of the index columns are modified and length of the tuple remains the same after update."

How can a row's length change? I think it must mean the size (in bytes) of the row remains the same.

I bet you are looking at an old design. That has undergone many
changes and the current implementation does not have any restriction
about the row length changes. But a necessary condition is to have
enough free space in the block (and of course not changing any index
columns).

You can find the latest README in the source code under
src/backend/access/heap/README.HOT

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

I can find very little information on hot updates but I found this:

http://archives.postgresql.org/pgsql-patches/2006-11/msg00059.php

It states, "This design optimizies the updates when none of the

index

columns are modified and length of the tuple remains the same after
update."

How can a row's length change? I think it must mean the size (in

bytes)

of the row remains the same.

I bet you are looking at an old design. That has undergone many
changes and the current implementation does not have any restriction
about the row length changes. But a necessary condition is to have
enough free space in the block (and of course not changing any index
columns).

You can find the latest README in the source code under
src/backend/access/heap/README.HOT

I'm using 8.3.1 on Solaris and I just tried this:
CREATE TABLE test (a int) WITH (hot_update='true');

It fails with:
ERROR: unrecognized parameter "hot_update"

Is a hot update automatic in 8.3.x and that is why there isn't any
formal documentation other than what is in the source code?

Jon

I'm using 8.3.1 on Solaris and I just tried this:
CREATE TABLE test (a int) WITH (hot_update='true');

It fails with:
ERROR: unrecognized parameter "hot_update"

Is a hot update automatic in 8.3.x and that is why there isn't any
formal documentation other than what is in the source code?

It's always on (you can't turn it off).

If you want to look at the tech side of things check out:

http://people.planetpostgresql.org/devrim/index.php?/archives/117-Looking-for-a-presentation-regarding-HOT.html

--
Postgresql & php tutorials
http://www.designmagick.com/