Hacker News Reader

Comment by fauigerzigerk | Hacker News Reader

There are a few pain points with postgres that have been holding it back. Lacking Windows support has been mentioned (that's in the past now fortunately). Incredibly cumbersome (you could say broken) unicode support makes postgres difficult to use in not exclusively english speaking environments. And mysql is faster for simple things.

On the other hand postgres is way faster for complex queries and its support for SQL features is second to none.

View on HN

I think you are mistaken about Unicode. It is much more mature on postgres. UTF8 was already offered on Postgres 7.3 while MySQL only managed to get it working in version 4.1.

Please see my other comment in this thread. There's the LIKE + indexes issue and there's the fact that postgres supports only one character set for the entire database, whereas mysql lets you choose on a per column basis.

Is that true about the broken Unicode support? I was going to try Postgres asap, but this would be a dealbreaker. I can hardly imagine it to be the case, though?

I don't really know what that comment was about. I've been using Postgres with a thoroughly-Unicode application stack (Django) for quite some time and never had a problem...

Which locale did you use to initialise your environment? It may well be that you haven't run into the LIKE + indexes issue or didn't notice it because the table didn't have a lot of data in it.

By the way, do you realise that Django's ORM does not support optimistic locking in a transactionally safe way?

"It may well be that you haven't run into the LIKE + indexes issue or didn't notice it because the table didn't have a lot of data in it."

I dunno, the backups (pgdump -> bzip), last time I looked at one, were over 6GB, so I'd say there's some data in there. I've just never seen Unicode-related issues.

"By the way, do you realise that Django's ORM does not support optimistic locking in a transactionally safe way?"

It doesn't really expose locking, period; consult the many threads on the dev list to find out why.

The problem is that for locales other than the "C" locale the LIKE operator will not use indexes unless you explicitly tell postgres to use text_pattern_ops. However, if you do that other comparison operators will not use that index. Effectively, what you have to do is to use two indexes on the same column, one using text_pattern_ops and one using the default operator class.

I know this is not a bug so it's debatable whether it should be called broken. Let me call it a broken design.

[Edit] And there's another workaround that allows you to avoid using two indexes. You can use text_pattern_ops and use regular expressions for all comparisons, even for equality. This solution may have other performance drawbacks. I'm not sure.

Trying to understand the issue: I suppose text_pattern_ops is a special kind of index optimized for LIKE searches? Then why should other queries use that index? Serious question, since you call the design broken - but what would be the proper way to do it in your opinion?

Reply on news.ycombinator.com