PostgreSQL Fill-Factor – Best Practices for Fixed-Width Columns

fill-factorpostgresql

We have a table where we are planning to update timestamp fields only.

In the first import these columns will be nulls then we will update them as the computation finished on these rows.

The question is; should we set a fill-factor lower than 100 for this table or PostgreSQL will allocate a fixed size for these columns so lowering the fill-factor makes no sense?

Best Answer

PostgreSQL never does in-place updates of the data, so it doesn't matter whether the records are fixed width or not when deciding whether the fillfactor should be 100. It always has to find new space for the new data, even if the new data is the same width or narrow than the old data. And if you set the fillfactor to 100 that new space will usually have to be in a different block.

Once you decide you do want to lower the fillfactor, then the fact that the width of the tuples will increase as you fill them out does mean you should lower it by more than you otherwise would.

Related Solutions

Postgresql – Fill factor for later bigserial + index

Assuming you are using COPY to populate the table I don't see a reason to create the table without the bigserial column.

Just create the table including the bigserial, but don't specify it in the COPY command. It will then automatically be filled with values during the import.

create table foobar (id bigserial, col1 text, col2 text);
copy foobar (col1, col2) 
   from 'foobar.txt'
   with (format csv, header false, delimiter ',');

That would remove steps 3,4 and 5.

I'm not sure what you mean with "range of bigserial" in step 6.

PostgreSQL Initial Database Size – SQL Server and PostgreSQL Fragmentation

No the only thing close to that is when you compile the server with the --with-segsize switch, this might help if your table is taking up more space than a gig and your file system can handle a single file being over a gig. If your inserting 20 gigs it will have to create 20 files if you don't use this switch. If your file system can handle a file over a gig you can just set it to a large value most likely see some benefit, worst case a small benefit.
Take a look at CLUSTER http://www.postgresql.org/docs/9.1/static/sql-cluster.htmland FILLFACTOR http://www.postgresql.org/docs/9.1/static/sql-createtable.html, http://www.postgresql.org/docs/9.1/static/sql-createindex.html

Note that FILLFACTOR can be applied to both tables and indexes.

Best Answer

Related Solutions

Postgresql – Fill factor for later bigserial + index

PostgreSQL Initial Database Size – SQL Server and PostgreSQL Fragmentation

Related Question