Tuesday, January 11
PostgreSQL
It's not a database, it's a toolkit for building databases.
When evaluating a server for mee.nu, it really came down to MySQL or PostgreSQL. MySQL won because of the difference in how you set up full-text indexing.
With MySQL, you put a full-text index on your field, then you use a MATCH ... AGAINST queury.
With PostgreSQL (at the time), you created an extra field in your table to hold the positional terms vector, which you populated by running one of a number of functions over your text field(s) depending on your precise needs, then you created either a GiST or GIN index over the vector, taking into account the tradeoffs of size, indexing time, and query time. Then you used one of a number of weird comparators like && or <@ on your search string to run your query.
Advantage of MySQL: You create the index. You're done.
Disadvantage of MySQL: You have a choice of full-text indexes or concurrency. Four years after I first did this evaluation, this is still true. If you have a full-text index in MySQL (or the MariaDB fork), then a single slow select will lock out all inserts, updates, and deletes on that table. And vice-versa. There are alternate table types that don't have those concurrency issues. They don't support full-text indexes.
Advantage of PostgreSQL: You can do it however you want, and you don't lose other functionality in the process.
Disadvantage of PostgreSQL: You have to work out how you want to do it. Also, at the time, you had to duplicate all the text in a separate field first, and then create the index. This has now been fixed in a very powerful and general way: You can create an index based not on a field, but on some function run against that field (or any combination of fields).
PostgreSQL seems to be very good at providing powerful, generalised solutions.
MySQL is very good at providing solutions that keep 95% of the people happy and are easy to use.
What PostgreSQL needs is some syntactic sugar. What MySQL needs is a whole new storage engine. I'm making no bets on who gets there first.
Comments are disabled.
Post is locked.
It's not a database, it's a toolkit for building databases.
When evaluating a server for mee.nu, it really came down to MySQL or PostgreSQL. MySQL won because of the difference in how you set up full-text indexing.
With MySQL, you put a full-text index on your field, then you use a MATCH ... AGAINST queury.
With PostgreSQL (at the time), you created an extra field in your table to hold the positional terms vector, which you populated by running one of a number of functions over your text field(s) depending on your precise needs, then you created either a GiST or GIN index over the vector, taking into account the tradeoffs of size, indexing time, and query time. Then you used one of a number of weird comparators like && or <@ on your search string to run your query.
Advantage of MySQL: You create the index. You're done.
Disadvantage of MySQL: You have a choice of full-text indexes or concurrency. Four years after I first did this evaluation, this is still true. If you have a full-text index in MySQL (or the MariaDB fork), then a single slow select will lock out all inserts, updates, and deletes on that table. And vice-versa. There are alternate table types that don't have those concurrency issues. They don't support full-text indexes.
Advantage of PostgreSQL: You can do it however you want, and you don't lose other functionality in the process.
Disadvantage of PostgreSQL: You have to work out how you want to do it. Also, at the time, you had to duplicate all the text in a separate field first, and then create the index. This has now been fixed in a very powerful and general way: You can create an index based not on a field, but on some function run against that field (or any combination of fields).
PostgreSQL seems to be very good at providing powerful, generalised solutions.
MySQL is very good at providing solutions that keep 95% of the people happy and are easy to use.
What PostgreSQL needs is some syntactic sugar. What MySQL needs is a whole new storage engine. I'm making no bets on who gets there first.
Posted by: Pixy Misa at
04:59 PM
| Comments (4)
| Add Comment
| Trackbacks (Suck)
Post contains 364 words, total size 2 kb.
1
So....hows the weather where you are?
I know the floods aere way north of you but Australia seems to be harmonizing its level of crazy weather with its quotas of poisonous things and mice.
I know the floods aere way north of you but Australia seems to be harmonizing its level of crazy weather with its quotas of poisonous things and mice.
Posted by: The Brickmuppet at Thursday, January 13 2011 06:14 AM (EJaOX)
2
It's been alternately rainy and oppressively humid here in Sydney, but at least we're not under 30' of water.
Posted by: Pixy Misa at Thursday, January 13 2011 09:55 AM (PiXy!)
3
Last I looked, didn't MySQL have umpteen storage engines ?
Make that 30' of fast moving water.
Posted by: Andrew Fong at Tuesday, January 18 2011 01:22 PM (cB03i)
47kb generated in CPU 0.0137, elapsed 0.106 seconds.
56 queries taking 0.0963 seconds, 329 records returned.
Powered by Minx 1.1.6c-pink.
56 queries taking 0.0963 seconds, 329 records returned.
Powered by Minx 1.1.6c-pink.
