4 ms·
Many of the complaints I hear people make about the entire concept of an "RDBMS" (often then to motivate why the NoSQL solution they decided to start using is b
by saurik 14y ago
Many of the complaints I hear people make about the entire concept of an "RDBMS" (often then to motivate why the NoSQL solution they decided to start using is better) are actually MySQL-specific issues that do not affect PostgreSQL (or Oracle, or usually SQL Server; I only mention PostgreSQL, as you wanted a concrete experience); one key example is "if you want to change your schema, it requires locking the entire system and rewriting the table" <- no, as the schema is just metadata; you should be able to do these things under first-class transactions, and PostgreSQL supports this just fine.
- rosser 14y agoTo be fair, there are cases where schema changes in PostgreSQL require re-writing the table, too. Like, for example, when you change the data type of an existing column. Otherwise, you're good, though.
- kconor 14y agoTo be fair, if you want to change the datatype of data in a NoSQL system, you have to rewrite it as well.
- saurik 14y agoYes, but that case comes up less often and isn't what I see NoSQL people complaining about ;P. (Also, when I've listed that as an explicit caveat recently, I often get corrected that they changed the ramifications of that recently, and it only sometimes has to happen, so I figured I'd just explicitly list the situations where you clearly should never have needed to rewrite the table.) Generally, though, this is related to be able to do table changes under transaction locks: I have often enjoyed being able to, under an atomic transaction, replace a table with a view over that table, or make modifications to indexes and columns that I then rollback if there's a mistake.
- jeltz 14y agoActually that depends on which change you do. varchar(2) -> varchar(4) does not require a rewrite while text -> int and varchar(4) -> varchar(2) does.
- jacques_chester 14y agoOr "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others. Take, for example, this gem: > Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results. This describes just about every standard page-with-comments schema ever devised. It struck me with particular force because perhaps I've been unfairly blaming Wordpress for the failings of MySQL, vis-a-vis the atrocious performance of the Recent Comments Widget. I see horrible join performance on tables with about half a million rows, which is chicken feed. The query cache "solves" my problem, but it's amazing that it should be necessary for very trivial joins between 2 or 3 tables.
- toast0 14y agoIs it MySQLs fault that wordpress asks it to do something that's hard? LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it
- knightni 14y ago> LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away In fairness, that often depends upon the intelligence of the planner. A lot of sort-limit-offset queries can be reasonably easy and not generate too many rows if the planner works correctly and you have the right indexes. edit: Assuming you're mostly picking up early pages as opposed to getting late ones, anyway - which is usually the case for, say, a comment system.
- sergiosgc 14y agoI know WordPress quite well. Most of these queries can be very easily executed from indexes, pulling from the tables only the data that will be output. It's just that mysql chooses to do it the dumb way.