4 ms·
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). Incre
by fauigerzigerk 18y ago
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.
- Tichy 18y agoIs 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?
- ubernostrum 18y agoI 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...
- fauigerzigerk 18y agoWhich 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?
- ubernostrum 18y ago"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.
- fauigerzigerk 18y agoThe 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.
- Tichy 17y agoTrying 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?
- 1gor 18y agoI 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.
- fauigerzigerk 18y agoPlease 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.