It is sunday morning and I have been musing about some PostgreSQL issues. As
some of you are aware, my dot com, dot died, and I am working on a business
plan for a consulting company which, amongst other things, will feature
PostgreSQL. As I am working on the various aspects, some issue pop up about
PostgreSQL.
Please don't take any of these personally, they are only my observations, if
you say they are non issues I would rather just accept that we disagree than
get into a nasty fight. They *are* issues to a corporate acceptance, I have
been challenged by IT people about them.
(1) Major version upgrade. This is a hard one, having to dump out and restore a
database to go from 7.1 to 7.2 or 7.2 to 7.3 is really a hard sell. If a
customer has a very large database, this represents a large amount of
down-time. If they are running on an operating system with file-size
limitations it is not an easy task. It also means that they have to have
additional storage which amount to at least a copy of the whole database.
(2) Update behavior, the least recently updated (LRU) tuple order in storage is
a problem. To have performance degrade as it does from updates is hard to
explain to a customer, and quite honestly, tells me I can not recommend
PostgreSQL for an environment in which the primary behavior is updating data.
[Index] --> [Target]->[LRU]->[1]->[2]->[3]->[MRU]
update tbl set foo = x where bar = y
The most recently updated (MRU) tuple, becomes [4] and the new tuple becomes
the MRU tuple.
[Index] --> [Target]->[LRU]->[1]->[2]->[3]->[4]->[MRU]
The above represents what PostgreSQL seems to currently do. Correct me if I'm
wrong. (I would love to be wrong here.) If we break the list at the beginning
and put the MRU tuple right after the target tuple (target tuple is the one
which the index points to), say like this:
[Index] --> [Target]->[MRU]->[1]->[2]->[3]->[LRU]
update tbl set foo = x where bar = y
[Index] --> [Target]->[MRU]->[4]->[3]->[2]->[1]->[LRU]
Again, the MRU becomes [4] but, rather than scanning each obsolete tuple to
find the end, the target tuple's next value is the MRU.
If updates and deletes could be handled this way, that would limit the update
and select performance degradation between vacuums.
