Hi!!!
It'd make sense if most of your queries used one
partition or the
other, and not both. Kind of like Wikipedia's history/current tables,
Are you talking about MediaWiki 1.4 schema, that we got rid off back
in 2005?
Not in this case. You want to physically move the
data so you can
access fewer pages, not just create an index on it. PG doesn't move
data just because you create an index on it.
OK, for this type of queries, probably. You can also do 'CLUSTER', so
physical data is ordered by particular index. Not clustered index per
se, but still similar effects.
Reread my messages now that you realize you were
confused the first
time: "In MySQL, you could achieve the same thing through clustering,
however." MySQL clusters on the primary key. This is great, or at
least it would be great if it didn't mean MySQL locks the whole table
every time you use any DDL. Still no "create index concurrently",
right?
There're hacks that allow concurrent index building on partitions,
though that kind of eliminates one of major benefit of having
partitions (that indexes can be created separately without hitting too
many cache misses).
No online DDL, though. InnoDB plugin nowadays allows faster DDLs as it
can build indexes separately.
Though, production impact by online DDL is usually huge enough anyway,
so people are forced to do rolling upgrades.
It's not PostgreSQL's fault if you've got
buggy locales installed on
your system.
Well, does standard libc locale support cover all the language-
specific behaviors, that are supported by e.g. LDML?
Do note, that as DBA one doesn't care much, if it is RDBMS or OS that
has buggy locales, the end result matters.
Exactly my point. PostgreSQL does by default exactly
what Wikipedia
wants to do with respect to uniqueness matching.
We don't know what we want to do with respect to uniqueness matching,
actually.
Nobody has ever had serious thought about the topic.
You can still do it
the other way by making an index on an expression, but in the case of
Wikipedia, which is what I thought we were talking about, there's no
need for that.
Right, assuming locales are all perfect, PG behavior is very suitable
for direct reproduction of Wikimedia setup .
MySQL, on the other hand, doesn't give the option.
You either choose
binary, and lose the collation, or you drop the binary and have to
drop the unique key constraint.
Or build your own collation, using LDML, etc.
Maybe you were using a *broken* language-specific
collation? Do you
get the same error when you use a locale-aware sorting program, like
gnu sort?
See above. LDML is quite evolving, and basic OS collation support
isn't 'good enough'.
The thing is, I never claimed there was a silver
bullet. You asked
for *any reason* to use PostgreSQL. I gave one. Just one, because
that's all you asked for.
Fair enough :) Do note, the reason didn't exist before. That question
was more directed at the original guy suggesting it. :-)
I'm sure you could find someone to "own the
pain of maintaining
charsets server-side". Anyone ask Gerard M if he knows somebody? If
not, the WMF should hire someone, because the comment in the bug
submission is right on that it's just embarrassing for an encyclopedia
not to be able to sort things correctly. Just using a generic locale
would be orders of magnitude better than using a binary collation.
And you could keep the current hacks and kludges in place in each
language until a proper locale for that language is written.
Maybe. If WMF wants to support this in any way, I'm not against it :)
I don't have much time for this anyway.
Domas