4 ms·

I've been hearing bad things about MySQL, so I've been avoiding it as of late. So far my experience has been subpar. PostgreSQL is pedantic with data insertio

by pippy 14y ago

I've been hearing bad things about MySQL, so I've been avoiding it as of late. So far my experience has been subpar. PostgreSQL is pedantic with data insertion, almost to a fault. This costs me development time. (Also I have no idea what my users will do, and I'd rather have faulty data inserted than none at all. If it's for a client asking about a product, this could cost money). Yet purists claim this is a great feature. It's also about twice as slow as MySQL (admittedly this is likely due to the maturity of the environment I'm working in). I personally like PostgreSQL. However I see it more as a guilty nerd pleasure rather than a development time effective solution. MSSQL is very nice, and my experience has been the best. Microsoft's tools are top quality. You'll find yourself very productive; creating advanced SQL views, mirroring, and snapshots. However MSSQL reeks of vender lockin, I had to virtualise the MSSQL tools, and getting the drivers to work on Linux took almsot two days of googling. Despite ease of use, the vendor lockin doesn't make MSSQL worth it.

deleted 14y ago
[deleted]
sadmysqluser 14y ago
If you would rather have faulty data than none when something's wrong with your application, then MySQL is your DB of choice.
- jeffdavis 14y ago
  I disagree that MySQL is a better choice when you have "faulty" data: * In postgres, you have PL/python (or PL/V8JS, or perl, or many other languages) to help you through the mess. For instance, you could write a canonicalization function to help you put the data into a more-queryable form. You can then even index on that function. * Powerful triggers might help with post-processing, or putting data into some queue of "bad data" that needs to be cleaned up later. Maybe by doing so, you realize that the data isn't "bad", your schema just needs to be updated to reflect new interesting cases. * You can pull data in from remote sources with foreign data wrappers, which might be necessary to clean the data up properly (e.g. one extra join against the company LDAP directory using the email might be able to canonicalize those employee names). * You can catch errors using subtransactions and have a different processing path for data that doesn't fit in the schema. Maybe some of these features exist in MySQL (I haven't been a real user since around 2003, aside from a bit of administration). But in postgres, these features all work together seamlessly along with all of the other features in postgres to make it all work nicely and without a pile of caveats. And that matters a lot when trying to wrangle strange data.
  - sadmysqluser 14y ago
    If you read pippy's statement carefully Also I have no idea what my users will do, and I'd rather have faulty data inserted than none at all. you'll see we're in agreement - PostgreSQL's design and feature set makes it clearly superior to MySQL in preventing the spread of faulty data. But pippy would rather a garbage database rather than be slowed down by preventive measures. It's costing him development time after all... By the way - thanks for all the great work on PosgreSQL range types.
sergiosgc 14y ago
I don't know where the "twice as slow" number for postgresql comes from. My the experience is different. Wildly different. For simple "keystore" loads, both are at the same performance class. For anything more complicated, typical relational database scenarios, mysql folds and is one or two orders of magnitude slower. The query planner's inability to push restrictions down the tree into subqueries alone prevents many typical real world queries. The way I see it, mysql is trapped between NoSQL and proper relational databases
jeffdavis 14y ago
"PostgreSQL is pedantic with data insertion, almost to a fault." The PostgreSQL philosophy isn't about being "pedantic", but it is very different from MySQL. I assume that you have much more experience with MySQL; maybe you are trying too hard to use postgres in the mysql way rather than the postgres way? Personally, I don't think it's a good idea to migrate usually for this reason. The entire project development always has lots of built-in assumptions about how the DB will be used, so the new system is almost never quite the right fit. It can only work in trivial cases, or when you have the right expectations.
- pippy 14y ago
  Yes I've been using MySQL in the PostgeSQL way. I learned to use databases using oracle databases, which is similar to MySQL.
  - jacques_chester 14y ago
    > oracle databases, which is similar to MySQL. How did you reach that conclusion?
twerquie 14y ago
> PostgreSQL is pedantic with data insertion Better to deal with the exceptions up-front, than to discover months down the line that your database has been silently chopping long strings, silently casting types and coercing values into useless junk.
- jeffdavis 14y ago
  "Better to deal with the exceptions up-front" That may be, but when an application developer is developing, they primarily need to get data into the database. That may be right or wrong[1], but it has a big impact on the adoption of particular database systems or practices. And it's somewhat irrelevant to this discussion, because there are many ways you can feed the data you want into postgres (like declaring columns BYTEA, TEXT, or HSTORE), no matter how "bad" it is. I would argue that it's much easier to deal with "bad" data in postgres than mysql because you have so much flexibility (e.g. write a function inside of postgres in PL/python to get some meaning out of the ill-specified data; or write some triggers to do post-processing). [1] I personally think that a misalignment of incentives is responsible for the widespread focus on the ease of writing into a database without considering the utility of the data once it's in there.

by pippy 14y ago

deleted 14y ago

[deleted]

sadmysqluser 14y ago

If you would rather have faulty data than none when something's wrong with your application, then MySQL is your DB of choice.

jeffdavis 14y ago
I disagree that MySQL is a better choice when you have "faulty" data: * In postgres, you have PL/python (or PL/V8JS, or perl, or many other languages) to help you through the mess. For instance, you could write a canonicalization function to help you put the data into a more-queryable form. You can then even index on that function. * Powerful triggers might help with post-processing, or putting data into some queue of "bad data" that needs to be cleaned up later. Maybe by doing so, you realize that the data isn't "bad", your schema just needs to be updated to reflect new interesting cases. * You can pull data in from remote sources with foreign data wrappers, which might be necessary to clean the data up properly (e.g. one extra join against the company LDAP directory using the email might be able to canonicalize those employee names). * You can catch errors using subtransactions and have a different processing path for data that doesn't fit in the schema. Maybe some of these features exist in MySQL (I haven't been a real user since around 2003, aside from a bit of administration). But in postgres, these features all work together seamlessly along with all of the other features in postgres to make it all work nicely and without a pile of caveats. And that matters a lot when trying to wrangle strange data.
- sadmysqluser 14y ago
  If you read pippy's statement carefully Also I have no idea what my users will do, and I'd rather have faulty data inserted than none at all. you'll see we're in agreement - PostgreSQL's design and feature set makes it clearly superior to MySQL in preventing the spread of faulty data. But pippy would rather a garbage database rather than be slowed down by preventive measures. It's costing him development time after all... By the way - thanks for all the great work on PosgreSQL range types.

sergiosgc 14y ago

I don't know where the "twice as slow" number for postgresql comes from. My the experience is different. Wildly different. For simple "keystore" loads, both are at the same performance class. For anything more complicated, typical relational database scenarios, mysql folds and is one or two orders of magnitude slower. The query planner's inability to push restrictions down the tree into subqueries alone prevents many typical real world queries. The way I see it, mysql is trapped between NoSQL and proper relational databases

jeffdavis 14y ago

"PostgreSQL is pedantic with data insertion, almost to a fault." The PostgreSQL philosophy isn't about being "pedantic", but it is very different from MySQL. I assume that you have much more experience with MySQL; maybe you are trying too hard to use postgres in the mysql way rather than the postgres way? Personally, I don't think it's a good idea to migrate usually for this reason. The entire project development always has lots of built-in assumptions about how the DB will be used, so the new system is almost never quite the right fit. It can only work in trivial cases, or when you have the right expectations.

pippy 14y ago
Yes I've been using MySQL in the PostgeSQL way. I learned to use databases using oracle databases, which is similar to MySQL.
- jacques_chester 14y ago
  > oracle databases, which is similar to MySQL. How did you reach that conclusion?

twerquie 14y ago

> PostgreSQL is pedantic with data insertion Better to deal with the exceptions up-front, than to discover months down the line that your database has been silently chopping long strings, silently casting types and coercing values into useless junk.

jeffdavis 14y ago
"Better to deal with the exceptions up-front" That may be, but when an application developer is developing, they primarily need to get data into the database. That may be right or wrong[1], but it has a big impact on the adoption of particular database systems or practices. And it's somewhat irrelevant to this discussion, because there are many ways you can feed the data you want into postgres (like declaring columns BYTEA, TEXT, or HSTORE), no matter how "bad" it is. I would argue that it's much easier to deal with "bad" data in postgres than mysql because you have so much flexibility (e.g. write a function inside of postgres in PL/python to get some meaning out of the ill-specified data; or write some triggers to do post-processing). [1] I personally think that a misalignment of incentives is responsible for the widespread focus on the ease of writing into a database without considering the utility of the data once it's in there.