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