I don't think that - a default index generation for foreign key columns - would lead to serious problems.
It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not.
If they had taken the opposite decision, then we'd be restricted. There would be an index for each and every foreign key constraint. If someone wanted to have some foreign key columns not indexed, they'd be limited or forced.
Now there is only the additional burden of having to add indexes to match the foreign key constraints - for those who want to.
Personally, I almost always add respective indexes for a foreign key constraint. But often, the index does not match exactly the constraint. For example the Fk may be TABLE c (..., FOREIGN KEY (a_id) REFERENCES a (a_id) ) and the index might be single (a_id) or composite (a_id, b_id) because it helps other queries as well, not only those that use a_id.
Or I might have (more rare cases of) 2 foreign key constraints that can be covered but 1 index - eg.: FK (a_id) REFERENCES a (a_id), ... FK (a_id, b_id) REFERENCES b (a_id, b_id).
When creating an index, there are also many options, like the type of index (btree, hash, Gin, Gist and other exotic types), so we're free to choose what is best for our situation.
Also note that Oracle, SQL Server, DB2 and Firebird also do not automatically create indexes for FOREIGN KEY columns, so it's not like that PostgreSQL developers were alone in this. And I'm sure there were many discussions on the matter and they considered both options, I'm only speculating on whey they came to the decision. Looking through the old Postgres discussion lists may reveal more about the actual reasons they considered.
It isn't about encoding, it is about collation.
Consider this:
select * from (values ('1,z'),('1,2j'),('12q'),('13 apples')) foo(x)
order by x collate "en_US";
gives:
x
-----------
1,2j
12q
13 apples
1,z
If you index using that collation, how would you efficiently support a x like '1,%' query? Not all the things starting with '1,' are adjacent in the index. This is the problem that text_pattern_ops (or C collation) solves.
You can make a database with UTF8 encoding but C collation. This mean you don't have to sprinkle text_pattern_ops all over your indexes, and your index builds over text columns will be much faster (and any sort-merge joins you do). But you can still store non-ASCII characters, although they will sort funny.
Also, setting client_encoding won't make any difference. Is is the server's encoding and collation that matter, not the client's.
Best Answer
Why would it matter that the fillfactor is on a primary key? The index works the same way. If you
UPDATEan indexed row to a point to a new version of the row -- that is anUPDATEthat wasn't heap-only, or if you add a row you have to potentially split the page the index is stored on to make room for the addition.The point at which you have to do that is when the index's page is full. The question is after the pages are properly sized for the index, how much extra-space on those pages should be left for future operations? If the fillfactor was 100, every operation that wrote to the page would result in the page split. That would be super-slow.
So what fillfactor 90 does is leave a 10% gap. That's 10% more you can grow before you have to pay that price again. Only if you know you won't have to grow the index again would you want to set it to fillfactor 100 (and then
REINDEXand shrink down all the pages).