Thread: Default fill factor for tables?
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. In the create index page it mentions the default for indexes is 90%. Is tables simmilar/same?
"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 fillfactor. 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 fillfactor. 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 remainsthe 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 newcolumn 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 remainsthe 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/