Обсуждение: Advantages of PostgreSQL over MySQL 5.0
Hello there, I'm a freelance Oracle Developer by trade (can almost hear the boos now ;o)), and am looking into developing my own Snowboarding-related website over the next few years. Anyway, I'm making some decisions now about the site architecture, and the database I'm going to need is obviously included. If my site works out, I'm expecting reasonably heavy traffic, so want a database that I'm confident can cope with it. It is out of the question for me to use Oracle, although I am a (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper route, and to that end I'm looking at either MySQL or PostgreSQL. Regarding MySQL, I've been put off by Oracle's recent purchase of InnoDB and realise this could badly impact the latest version of the MySQL database. I can almost hear Larry Ellison's laughter from here (allegedly)! I've also been put off by the heavy marketing propaganda on the MySQL website. Recently, I've been taking a look at PostgreSQL, and am very impressed by what I've read, although I've not yet investigated the database first-hand. To cut to the chase, I would be interested in anybody's feedback on the advantages that PostgreSQL has over MySQL. Also, I've recently read the "Inside MySQL 5.0" (marketing propaganda) document, and it makes the following claim: "With MySQL, customers across all industries are finding they can easily handle nearly every type of database workload, with performance and scalability outpacing every other open source rival. As Los Alamos lab (who uses MySQL to manage their terabyte data warehouse) said, "We chose MySQL over PostgreSQL primarily because it scales better and has embedded replication.".". If any PostgreSQL devotees on this group can comment on the above and its accuracy/inaccuracy, I'd really appreciate it. Thanks in advance. James
On Mar 22, 2006, at 6:06 AM, Jimbo1 wrote: > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > chose MySQL over PostgreSQL primarily because it scales better and has > embedded replication.".". > The "one size fits all" style replication. What if it doesn't suit your needs? And as for scalability, I have to doubt the knowledge of the person making that claim.... > If any PostgreSQL devotees on this group can comment on the above and > its accuracy/inaccuracy, I'd really appreciate it. Basically you need to decide what you need your DB to do, then decide what kind of licensing you want, then decide which product fits your bill. If all you're doing is simple store/fetch then mysql will do (heck, even SQLite will do). If you need complex things like triggers, foreign keys, etc. then think again about using mysql... even if they check off those as features, you need to evaluate their stability and speed.
Jimbo1 wrote: > > "With MySQL, customers across all industries are finding they can > easily handle nearly every type of database workload, with performance > and scalability outpacing every other open source rival. As Los Alamos > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > chose MySQL over PostgreSQL primarily because it scales better and has > embedded replication.".". > > If any PostgreSQL devotees on this group can comment on the above and > its accuracy/inaccuracy, I'd really appreciate it. > > That's exactly what it is "propoganda", I can think of two really high profile Postgresql installs that have recently been discussed: 1. The Wisconsin Court System, search the archives for a recent post about this. 2. The entire .org and .info domains are stored in a Postgresql database. I am sure there are many more. Postgresql is also much more flexible than Mysql, for example with Mysql you are restricted to using one proc langauge, while with PG you can choose from many that might fit your needs or solve a specific problem. Using PLperl for example you can easily write functions that send emails, connect to socket servers etc etc. Mysql also does not seem to have system tables that are accessible to the developer, you have to use their information schema or the odd SHOW syntax which is not flexible at all. Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql
Tony Caduto wrote: > Jimbo1 wrote: > >> >> "With MySQL, customers across all industries are finding ... >> >> If any PostgreSQL devotees on this group can comment on the above and >> its accuracy/inaccuracy, I'd really appreciate it. >> > > That's exactly what it is "propoganda", I can think of two really high > profile Postgresql installs that have recently been discussed: > > 1. The Wisconsin Court System, search the archives for a recent post > about this. > 2. The entire .org and .info domains are stored in a Postgresql database. > > I am sure there are many more. Whenever this kind of question comes up, I always enjoy re-reading (Caution: Flame War Impending!) the "MySQL Gotchas" at http://sql-info.de/mysql/gotchas.html even though that is getting dated. -- BMT
Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. If Oracle is out of the question, so is MySQL. The technology that makes MySQL even reasonably close to production OLTP quality is owned by Oracle ;) (Innodb and BDB). Sincerely, Joshua D. Drake
Jimbo1 wrote: >Hello there, > >I'm a freelance Oracle Developer by trade (can almost hear the boos now >;o)), and am looking into developing my own Snowboarding-related >website over the next few years. Anyway, I'm making some decisions now >about the site architecture, and the database I'm going to need is >obviously included. If my site works out, I'm expecting reasonably >heavy traffic, so want a database that I'm confident can cope with it. > >It is out of the question for me to use Oracle, although I am a >(biased) 'fan' of that RDBMS. I definitely need to go for a cheaper >route, and to that end I'm looking at either MySQL or PostgreSQL. > >Regarding MySQL, I've been put off by Oracle's recent purchase of >InnoDB and realise this could badly impact the latest version of the >MySQL database. I can almost hear Larry Ellison's laughter from here >(allegedly)! I've also been put off by the heavy marketing propaganda >on the MySQL website. > > I use Oracle at work and PostgreSQL for personal projects, and I think you'll find that PostgreSQL is the more feature-complete(or "Oracle-like") database. There are definitely situations Oracle comes out ahead, but for a website Idoubt you'll find them. Also, the syntax between the two is more closer to the standard (PostgreSQL is actually betterin this) than MySQL. Postgres has Pl/PgSQL, which is close enough PlSQL to not cause any problems. Jeff
On Wednesday 22 March 2006 03:06, Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. I've built many sites based on PostgreSQL. Originally,like most, I started with MySQL, but after I discovered PG in about 2000, I've switched all development to it, and have never looked back. I have "enterprise" systems developed with PostgreSQL with 500 users, 50 online at a time, > 100 database tables. Although the data sample is still not that impressive, (71 MB sql file with pg_dump) the database itself is quite complex, with multiple foreign keys in a single table being the norm. It's just been a dream. It's solid, reliable, and virtually always behaves as expected. My only caveat is that occasionally, you really have to watch the use of indexes. I had one query (nasty, with 7-8 tables involved in a combined inner->outer->inner join) that was taking some 20 seconds to execute. Just changing the order of some of the tables in the query, without logically changing the result at all, dropped that time down to < 50 ms! > Regarding MySQL, I've been put off by Oracle's recent purchase of > InnoDB and realise this could badly impact the latest version of the > MySQL database. I can almost hear Larry Ellison's laughter from here > (allegedly)! I've also been put off by the heavy marketing propaganda > on the MySQL website. Perhaps the single thing I most like about PostgreSQL is the feeling that "it can't be taken away from me". The license is sufficiently open, and the product is sufficiently stable, that I don't ever wonder if I'm "compliant" or "paid up", nor do I wonder if my growth will be particularly limited anywhere in the forseeable future. > "With MySQL, customers across all industries are finding they can > easily handle nearly every type of database workload, with performance > and scalability outpacing every other open source rival. As Los Alamos > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > chose MySQL over PostgreSQL primarily because it scales better and has > embedded replication.".". PostgreSQL has replication, as well. From what I've read, it's probably about on par with MySQL in terms of manageability and reliability. But, truthfully, having dealt with database replication, it's a PAIN IN THE ARSE and very unlikely worth it. In fact, systems that I've worked on that included replication are generally less reliable than those that simply do a dump/copy every hour or two, due to the increased management headaches and niggling problems that invariably seem to occur. Consider replication if the cost of a full-time DB Admin is justified by saving perhaps a few hours of uptime per year. If so, go for it. Be honest about it - most people grossly overestimate the actual cost of few hours of downtime every other year. > If any PostgreSQL devotees on this group can comment on the above and > its accuracy/inaccuracy, I'd really appreciate it. PG does constraints wonderfully. It's performance is midline with simple schemas. It handles very complex schemas wonderfully, and, with a little tuning, can make very effective use of memory to speed performance. My $0.02. Cheers! -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
> ...I can think of two really high > profile Postgresql installs that have recently been discussed: > > 1. The Wisconsin Court System, search the archives for a recent post > about this. > 2. The entire .org and .info domains are stored in a Postgresql database. > > I am sure there are many more. Yup, Sony Online Entertainment springs to mind. They just selected (and invested in) EnterpriseDB. EnterpriseDB is, at it's core, PostgreSQL but it has been altered in a number of ways - primarily to enhance Oracle compatibility so that PG can be more of a drop-in replacement for programs that require Oracle. From their press release: "EnterpriseDB Advanced Server is the clear open source database choice because of its PostgreSQL foundation, compatibility with Oracle and the support of the EnterpriseDB team,“ said Christopher Yates, vice president of technology at Sony Online Entertainment." Their whole press-release is at: http://www.enterprisedb.com/news_events/press_releases/03_20_06b.do Cheers, Steve
jd@commandprompt.com ("Joshua D. Drake") writes:
> Jimbo1 wrote:
>> Hello there,
>> I'm a freelance Oracle Developer by trade (can almost hear the boos
>> now
>> ;o)), and am looking into developing my own Snowboarding-related
>> website over the next few years. Anyway, I'm making some decisions now
>> about the site architecture, and the database I'm going to need is
>> obviously included. If my site works out, I'm expecting reasonably
>> heavy traffic, so want a database that I'm confident can cope with it.
>> It is out of the question for me to use Oracle, although I am a
>> (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper
>> route, and to that end I'm looking at either MySQL or PostgreSQL.
>
> If Oracle is out of the question, so is MySQL. The technology that
> makes MySQL even reasonably close to production OLTP quality is
> owned by Oracle ;) (Innodb and BDB).
Combine that with the consideration that there is a real paucity of
performance results involving the "production OLTP quality"
subsystems.
The traditional claims concerning MySQL being "way faster" relate to
its use with the "definitely not production OLTP quality" MyISAM
engine.
And it's pretty needful to be even a bit more specific than that...
MySQL will be way faster than anything else if you have applications
designed to specifically harness its strengths, namely...
- Single user doing a stream of MyISAM updates
(If there are multiple connections, they quickly "butt heads"
on MyISAM table locks, so that >1 user suffers *badly*)
- Performing heavy loads involving often creating fresh connections
for each query, and submitting small, fairly trivial, select-one-row
query requests
If any of the assumptions there change, such as:
- Having multiple concurrent updating processes, or
- Submitting complex queries,
the "advantage" can pretty quickly evaporate.
PostgreSQL is likely to be way slower if you submit streams of little
queries, each an independent transaction...
- If multiple queries group into a single transaction, some "slowness"
will go away;
- The more complex the queries, the likelier that the sophisticated
query planner and optimizer in PostgreSQL will win out;
- The larger the number of concurrent update processes, the greater
the likelihood that MVCC allows PostgreSQL to chug along at speed
whilst the MyISAM table locks drag things to a halt...
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/lisp.html
Rules of the Evil Overlord #69. "All midwives will be banned from the
realm. All babies will be delivered at state-approved
hospitals. Orphans will be placed in foster-homes, not abandoned in
the woods to be raised by creatures of the wild."
<http://www.eviloverlord.com/>
Jimbo1 wrote: > Hello there, > > Regarding MySQL Hi Jimbo, As I'm sure you've asked the same question of the MySQL folks, can you tell us what they've said about "us"? I guess it's not just idle curiosity (90% though), but it might give us some pointers about how to improve either our marketing, implementation or both. Cheers Michael PS For my tuppeny ha'penny's worth, and having used both MySQL and PGSQL for a web-app I'm developing, I've been very impressed with PGSQL since making the switch from MySQL (4.0.15).
On Wed, 2006-22-03 at 11:34 -0800, Benjamin Smith wrote: > On Wednesday 22 March 2006 03:06, Jimbo1 wrote: > > Hello there, > > > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > > ;o)), and am looking into developing my own Snowboarding-related > > website over the next few years. Anyway, I'm making some decisions now > > about the site architecture, and the database I'm going to need is > > obviously included. If my site works out, I'm expecting reasonably > > heavy traffic, so want a database that I'm confident can cope with it. > > I've built many sites based on PostgreSQL. Originally,like most, I started > with MySQL, but after I discovered PG in about 2000, I've switched all > development to it, and have never looked back. I have "enterprise" systems > developed with PostgreSQL with 500 users, 50 online at a time, > 100 database > tables. Although the data sample is still not that impressive, (71 MB sql > file with pg_dump) the database itself is quite complex, with multiple > foreign keys in a single table being the norm. > > It's just been a dream. It's solid, reliable, and virtually always behaves as > expected. > > My only caveat is that occasionally, you really have to watch the use of > indexes. I had one query (nasty, with 7-8 tables involved in a combined > inner->outer->inner join) that was taking some 20 seconds to execute. Just > changing the order of some of the tables in the query, without logically > changing the result at all, dropped that time down to < 50 ms! > > > Regarding MySQL, I've been put off by Oracle's recent purchase of > > InnoDB and realise this could badly impact the latest version of the > > MySQL database. I can almost hear Larry Ellison's laughter from here > > (allegedly)! I've also been put off by the heavy marketing propaganda > > on the MySQL website. > > Perhaps the single thing I most like about PostgreSQL is the feeling that "it > can't be taken away from me". The license is sufficiently open, and the > product is sufficiently stable, that I don't ever wonder if I'm "compliant" > or "paid up", nor do I wonder if my growth will be particularly limited > anywhere in the forseeable future. > > > "With MySQL, customers across all industries are finding they can > > easily handle nearly every type of database workload, with performance > > and scalability outpacing every other open source rival. As Los Alamos > > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > > chose MySQL over PostgreSQL primarily because it scales better and has > > embedded replication.".". > > PostgreSQL has replication, as well. From what I've read, it's probably about > on par with MySQL in terms of manageability and reliability. > > But, truthfully, having dealt with database replication, it's a PAIN IN THE > ARSE and very unlikely worth it. In fact, systems that I've worked on that > included replication are generally less reliable than those that simply do a > dump/copy every hour or two, due to the increased management headaches and > niggling problems that invariably seem to occur. > > Consider replication if the cost of a full-time DB Admin is justified by > saving perhaps a few hours of uptime per year. If so, go for it. Be honest > about it - most people grossly overestimate the actual cost of few hours of > downtime every other year. You can dump a running DB. Unless you have a hardware failure you should not require any down time. I have been running a PG database that is backed up every day, and has been been running for 5 years with one 5 min interruption when the server was moved to a new rack. I am in the process of building a replacement machine, because the hardware is bound to fail sometime, and it will be nice to upgrade the OS. Unfortunately the base program I heavily customized to put the collected data directly into PostgreSQL is no longer maintained, so I need to build a whole new management and client interface system around the new program, that has native support for PostgreSQL but uses a significantly different table system. > > > If any PostgreSQL devotees on this group can comment on the above and > > its accuracy/inaccuracy, I'd really appreciate it. > > PG does constraints wonderfully. It's performance is midline with simple > schemas. It handles very complex schemas wonderfully, and, with a little > tuning, can make very effective use of memory to speed performance. MySQL also does not properly Support NULL, has glaring errors in the scope of some data types and does not have robust support for many of the data types I use on a regular basis. If you are only interested in varchar and blobs MySQL may have a small advantage. If you need proper support for NULL and/or robust data types with proper scope handling, MySQL would not serve you well. The performance and features of MySQL also depend on the type of table you use and if your needs change you need to dump/drop/create/restore with the type of table you require later on. PostgreSQL has one table type and has well developed locking mechanisms that allow a table to be dumped even when in use, where MySQL locks the whole table while it is being dumped. I have used both DB's depending on the requirements of the project, but by far prefer PostgreSQL for anything that is not overtly simple. I find that with MySQL the programmer has to take more precautions to ensure proper variable scope and NULL handling work as expected, or weird bugs crop up once in a while.
On Wed, 2006-03-22 at 05:06, Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. We use both where I work, for different things. We also use Oracle for our transactional engine. I submit bug reports for both PostgreSQL and MySQL. I've asked for help for both databases. The PostgreSQL users and developers are unbelievably knowledgeable and helpful. When I first started using PostgreSQL I found something that seemed odd, in that functional indexes couldn't accept constant arguments, only column names (this was with 6.5 or 7.0 or something like that) and within a few hours Peter Eisenstraut (I'm sure I spelled his name wrong there) had posted a work around for me. I've found a few bugs here and there. They've all resulted in changes being made anywhere from very fast for serious bugs, to being pipelined into the process for the next major release. I've never had that kind of very fast response from MySQL. If feels like those guys really have their hands full meeting all the requirements for the next version to pay much attention to the smaller bugs. For instance, this DDL in MySQL: mysql> create table a (x int primary key) engine innodb; Query OK, 0 rows affected (0.13 sec) mysql> create table b (y int references a) engine innodb; Query OK, 0 rows affected (0.07 sec) mysql> insert into b values (10); Query OK, 1 row affected (0.07 sec) Executes without a warning or an error. Now, I shouldn't be able to insert anything in b that's not referencing an entry in a. and I used innodb tables. and I used ansi SQL, and I got no errors. So how come my data's incoherent three seconds after creating the tables the way the spec says should work? Simple. MySQL only implements foreign keys if you do them this way: create table b (y int, foreign key (y) references a(x)) engine innodb; I.e. you have to declare fk constraints outside the column defs. Why? Because innodb tables and transactions in general are kind of like red-headed step-children, I guess. It's not gonna change. I files a bug report on it and the answer was basically "yep, that's how it's supposed to work". This flies in the face of MySQL's claims that they are striving towards SQL specification compliance. In closing, I found that with PostgreSQL I had to invest a fair bit of time learning how databases were supposed to work, and the more I used it, the happier and more rewarded I became. With MySQL, it's really easy to get started. It's over time you start thinking "maybe this thing isn't quite right in the head" with some of the odd behaviour like the one I listed above. And it's just one of many. that said, for content management, MySQL is a GREAT database. It has collation per column, something PostgreSQL only gets about halfway to right now with collation classes. OTOH, when that gets added to PostgreSQL, it will likely be done very well. As for the load handling, anyone who benchmarks a database with a single thread (which is what a LOT of people do, sadly) should never be allowed to declare any database fast. A database that can do exactly one thing at once fast is not really all that interesting. A database that can stay on its feet with 1000+ users hammering away updating and inserting and selecting and deleting is impressive as hell. And PostgreSQL is quickly moving in that direction. The whole "built in replication" argument smacks of intellectual laziness to me. Read the bug reports on mysql.com about it. There are scads of reports of it just stopping for no good reason. And how do you bring it back online? Simple. shut down your entire application, disconnect all users, and FILE COPY everything to the slave server. Restart replication, and then reconnect all your users. This is enterprise quality? I hope they come up with a better way than that. With Slony, I can add and remove replicated sets on the fly, on a living database system, and the user never even notices. I can use Point in time Recovery in Postgresql, a feature MySQL currently lacks, and one I consider more useful than real time replication for distaster recovery anyway. After all, replication can't save you from "delete from tablename" with no where clause. Again, I can set up PITR replication on the fly, without shutting down the master database for even a second. Lastly, try creating a test app of some kind that runs a lot of queries (read and write both) on MySQL and PostgreSQL. Turn it on, let it settle and place a heavy load on both machines. Pull the plug. Assuming PostgreSQL is running on proper hardware (SCSI drives, RAID controller with BBU, etc) it will come right back up. MySQL, if it's running on myisam files, will be trashed. You may spend quite alot of time getting it back up, and it will be missing data, and if you have related tables, you will most certainly have orphans now. Innodb tables should survive. But will they still be in MySQL in a year? who knows? I suggest someone put rubber on the road there and test both, including possibly destructive tests like the power plug being pulled several times. See how they both handle a simulation of what load you think you'll have. See how much faster each gets when you move it from a single CPU machine with 512 Meg ram and a single hard drive to an 8 way Opteron with 16 gigs ram and a 12 drive RAID 1+0 drive set. Now is the time to do it, before you've "married" your company to one or the other, and found it wanting. Waving your hands around magically declaring one better than the other gets you no where.
I see a titanic advantage of PostgreSQL over MySQL: the license. http://www.postgresql.org/about/licence http://www.mysql.com/company/legal/licensing/commercial-license.html Would you like to use the database for commercial purposes? To my way of thinking, the Berkeley style license is the best of all worlds. Other than that, the Oracle/Sleepycat stuff is a little worrying. But both products have advantages and disadvantages. If you build a standards based interface to the database, then you can always switch from one to the other {or some other DB altogether} with relative ease. You'll find rabid fans in both camps, of course. I'm a PostgreSQL fan, so anything I say should also be taken with a grain of salt. Simply put, I like PostgreSQL better.
Chis Browne wrote: > PostgreSQL is likely to be way slower if you submit streams of little > queries, each an independent transaction... When I get around to it I plan on debunking this ;). I recently did extensive internal benchmarking of mysql 5.0 vs. postgresql 8.1 and it's victories across the board with only a couple of exceptions, and I have benchmarks to prove it. Im summary: 1. mysql is faster when it's query cache hit ratio is high 2. mysql opens connections much faster than pg, which is why we use pgpool 3. mysql sometimes wins where mvcc delete + insert can be kind of a pain (*much* rarer than commonly thought) While 'out of the box' postgresql is slower at select a,b,c from t where k type queries wrt mysql, the performance advantage is completely negated if you run those queries via prepared statements. In postgresql, queries executed over the parameterized/prepared C api are particularly fast...as much as a 70% speed reduction over vanilla PQexec. Now, the lower level API and prepared statements are not available for all applications, but when used they provide extremely low-latency access. Also, 1. pg can read off large result sets (50k records +) from the cache much faster than mysql 2. pg has a generally better query optimizer, altough here and there mysql scores a win 3. many other advantages you are already quite familiar with, mvcc, etc. Basically, I am saying that the proverbial bread and butter queries are not necessarily faster on mysql, just easier to get running fast, if that makes sense. Now, I'm not trying to bash mysql (I was in fact, quite impressed with 5.0) generally, but I really think the claim that it is faster for a broad array of tasks is highly dubious. pg just requires a little bit more specialized knowledge to get running up to its level in some cases. Merlin
On Wed, 2006-03-22 at 16:36 -0500, Merlin Moncure wrote: > Chis Browne wrote: > > PostgreSQL is likely to be way slower if you submit streams of little > > queries, each an independent transaction... > > When I get around to it I plan on debunking this ;). I recently did > extensive internal benchmarking of mysql 5.0 vs. postgresql 8.1 and > it's victories across the board with only a couple of exceptions, and > I have benchmarks to prove it. Im summary: > > 1. mysql is faster when it's query cache hit ratio is high > 2. mysql opens connections much faster than pg, which is why we use pgpool > 3. mysql sometimes wins where mvcc delete + insert can be kind of a > pain (*much* rarer than commonly thought) > > While 'out of the box' postgresql is slower at select a,b,c from t > where k type queries wrt mysql, the performance advantage is > completely negated if you run those queries via prepared statements. > > In postgresql, queries executed over the parameterized/prepared C api > are particularly fast...as much as a 70% speed reduction over vanilla > PQexec. Now, the lower level API and prepared statements are not > available for all applications, but when used they provide extremely > low-latency access. > > Also, > 1. pg can read off large result sets (50k records +) from the cache > much faster than mysql > 2. pg has a generally better query optimizer, altough here and there > mysql scores a win > 3. many other advantages you are already quite familiar with, mvcc, etc. > > Basically, I am saying that the proverbial bread and butter queries > are not necessarily faster on mysql, just easier to get running fast, > if that makes sense. Now, I'm not trying to bash mysql (I was in > fact, quite impressed with 5.0) generally, but I really think the > claim that it is faster for a broad array of tasks is highly dubious. > pg just requires a little bit more specialized knowledge to get > running up to its level in some cases. > Out of interest, what MySQL table type did you compare against? I personally would be much more interested in such a comparison with InnoDB tables than MyISAM. -- Russ
"Jimbo1" <jamestheboarder@googlemail.com> writes: > Not yet asked them, but will pop the question over the next week. ;o) Careful about "popping the question"... While good marriages have come from that, so also have been some bad ones :-). -- (reverse (concatenate 'string "gro.gultn" "@" "enworbbc")) http://www.ntlug.org/~cbbrowne/lsf.html One good turn gets most of the blankets.
Hi James : I am mysql user for 5 years. An a this time i am trying to go out from mysql. What i am living mysql? Becouse my php bases systems require some features that mysql is inplementing in the vercion 5.0, like store procedures and functions, trrigers, transaction, views, and some others features. Dou you now how to lock a counter table to users have no access to this table mysql ? Table two field, identerprise and counter Mysql, to lock write, you have to lock entire table for write. LOCK TABLES co_nro_notas WRITE. SELECT counter FROM co_nro_notas WHERE identerprise = 1 Update co_nro_notas SET counter = counter + 1 UNLOCK TABLES This is a problem when do you have two or more enterprise runing in the same database, an each with more than 10 users. In postgresql each enterprise have your own record, and eachone can lock his record. You use a SELECT with a FOR UPDATE. Try postgresql, is better than mysql. I am dice to migrate all my systems to postgresql. PD: I try postgresql in 2002, but a this time postgresql does not have windows vercion, this is the reason for use mysql, have not other reason. Alejandro M.S. Porto Alegre Brasil -->-----Mensagem original----- -->De: pgsql-general-owner@postgresql.org -->[mailto:pgsql-general-owner@postgresql.org] Em nome de Jimbo1 -->Enviada em: quarta-feira, 22 de março de 2006 08:06 -->Para: pgsql-general@postgresql.org -->Assunto: [GENERAL] Advantages of PostgreSQL over MySQL 5.0 --> --> -->Hello there, --> -->I'm a freelance Oracle Developer by trade (can almost hear -->the boos now ;o)), and am looking into developing my own -->Snowboarding-related website over the next few years. -->Anyway, I'm making some decisions now about the site -->architecture, and the database I'm going to need is -->obviously included. If my site works out, I'm expecting -->reasonably heavy traffic, so want a database that I'm -->confident can cope with it. --> -->It is out of the question for me to use Oracle, although I am a -->(biased) 'fan' of that RDBMS. I definitely need to go for a -->cheaper route, and to that end I'm looking at either MySQL -->or PostgreSQL. --> -->Regarding MySQL, I've been put off by Oracle's recent -->purchase of InnoDB and realise this could badly impact the -->latest version of the MySQL database. I can almost hear -->Larry Ellison's laughter from here (allegedly)! I've also -->been put off by the heavy marketing propaganda on the MySQL website. --> -->Recently, I've been taking a look at PostgreSQL, and am very -->impressed by what I've read, although I've not yet -->investigated the database first-hand. To cut to the chase, I -->would be interested in anybody's feedback on the advantages -->that PostgreSQL has over MySQL. --> -->Also, I've recently read the "Inside MySQL 5.0" (marketing -->propaganda) document, and it makes the following claim: --> -->"With MySQL, customers across all industries are finding -->they can easily handle nearly every type of database -->workload, with performance and scalability outpacing every -->other open source rival. As Los Alamos lab (who uses MySQL -->to manage their terabyte data warehouse) said, "We chose -->MySQL over PostgreSQL primarily because it scales better and -->has embedded replication.".". --> -->If any PostgreSQL devotees on this group can comment on the -->above and its accuracy/inaccuracy, I'd really appreciate it. --> -->Thanks in advance. --> -->James --> --> -->---------------------------(end of -->broadcast)--------------------------- -->TIP 6: explain analyze is your friend --> --> -->-- -->No virus found in this incoming message. -->Checked by AVG Free Edition. -->Version: 7.1.385 / Virus Database: 268.2.6/287 - Release -->Date: 21/3/2006 --> --> -->-- -->No virus found in this incoming message. -->Checked by AVG Free Edition. -->Version: 7.1.385 / Virus Database: 268.2.6/287 - Release -->Date: 21/3/2006 --> --> -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/287 - Release Date: 21/3/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/287 - Release Date: 21/3/2006
>As I'm sure you've asked the same question of the MySQL folks, can you tell >us what they've said about "us"? I guess it's not just idle curiosity (90% >though), but it might give us some pointers about how to improve either our >marketing, implementation or both. Not yet asked them, but will pop the question over the next week. ;o)
> > Hello there, > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. One aspect you might consider is that Postgres has an OGC SFS compliant extension, PostGIS, wich is comparable to (or better than :-) Oracle Spatial. MySQL is in the process of developing such a capability, but the current implementation is incomplete and will frequently return wrong answers. Not really buggy (the reasons are clearly documented), but the stage of development is such that I don't think it should have been released as other than alpha software. The ease of integrating web map server applications using data from Postgres/PostGIS tables may be useful for such a site, where maps, road information, photos accessed by clicking on a map, etc might be useful. Applications such as mapserver can treat PostGIS enabled Postgres tables as map layers, and allow spatial queries etc to be carried out as well as normal non-spatial queries. Cheers, Brent Wood
On Mar 22, 2006, at 10:08 PM, Scott Marlowe wrote: > Now, I shouldn't be able to insert anything in b that's not > referencing > an entry in a. and I used innodb tables. and I used ansi SQL, and I > got no errors. So how come my data's incoherent three seconds after > creating the tables the way the spec says should work? Simple. MySQL > only implements foreign keys if you do them this way: Good lord, is that still true in 5.0?? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Thu, 2006-03-23 at 12:17, Jim Nasby wrote: > On Mar 22, 2006, at 10:08 PM, Scott Marlowe wrote: > > Now, I shouldn't be able to insert anything in b that's not > > referencing > > an entry in a. and I used innodb tables. and I used ansi SQL, and I > > got no errors. So how come my data's incoherent three seconds after > > creating the tables the way the spec says should work? Simple. MySQL > > only implements foreign keys if you do them this way: Yep. I filed the bug report on it. http://bugs.mysql.com/bug.php?id=13301
smarlowe@g2switchworks.com (Scott Marlowe) writes: > http://bugs.mysql.com/bug.php?id=13301 And as Heikki Tuuri is no longer with them, I'll bet that doesn't get changed any time soon... -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://cbbrowne.com/info/finances.html Rules of the Evil Overlord #68. "I will spare someone who saved my life sometime in the past. This is only reasonable as it encourages others to do so. However, the offer is good one time only. If they want me to spare them again, they'd better save my life again." <http://www.eviloverlord.com/>
""Merlin Moncure"" <mmoncure@gmail.com> wrote > > In postgresql, queries executed over the parameterized/prepared C api > are particularly fast...as much as a 70% speed reduction over vanilla > PQexec. Does it mean 70% time is spent on planning? I am a little bit interested in this number. Can you specify what kind of queries or give some testing numbers. By the way, if so, we can do it by PREPARE statement in SQL. Regards, Qingqing
Scott Marlowe wrote: > On Thu, 2006-03-23 at 12:17, Jim Nasby wrote: >> On Mar 22, 2006, at 10:08 PM, Scott Marlowe wrote: >>> Now, I shouldn't be able to insert anything in b that's not >>> referencing >>> an entry in a. and I used innodb tables. and I used ansi SQL, and I >>> got no errors. So how come my data's incoherent three seconds after >>> creating the tables the way the spec says should work? Simple. MySQL >>> only implements foreign keys if you do them this way: > > Yep. I filed the bug report on it. > > http://bugs.mysql.com/bug.php?id=13301 > from the response: > Years ago, to help porting applications from other database brands to > MySQL, MySQL was made to accept the syntax even though no real > constraints were created. i hope postgresql will never "help" me this way. gabor
On Thu, Mar 23, 2006 at 12:24:18PM -0600, Scott Marlowe wrote: > On Thu, 2006-03-23 at 12:17, Jim Nasby wrote: > > On Mar 22, 2006, at 10:08 PM, Scott Marlowe wrote: > > > Now, I shouldn't be able to insert anything in b that's not > > > referencing > > > an entry in a. and I used innodb tables. and I used ansi SQL, and I > > > got no errors. So how come my data's incoherent three seconds after > > > creating the tables the way the spec says should work? Simple. MySQL > > > only implements foreign keys if you do them this way: > > Yep. I filed the bug report on it. > > http://bugs.mysql.com/bug.php?id=13301 Submitted to the gotchas page... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, 2006-03-24 at 02:51, Gábor Farkas wrote: > Scott Marlowe wrote: > > On Thu, 2006-03-23 at 12:17, Jim Nasby wrote: > >> On Mar 22, 2006, at 10:08 PM, Scott Marlowe wrote: > >>> Now, I shouldn't be able to insert anything in b that's not > >>> referencing > >>> an entry in a. and I used innodb tables. and I used ansi SQL, and I > >>> got no errors. So how come my data's incoherent three seconds after > >>> creating the tables the way the spec says should work? Simple. MySQL > >>> only implements foreign keys if you do them this way: > > > > Yep. I filed the bug report on it. > > > > http://bugs.mysql.com/bug.php?id=13301 > > > > from the response: > > > Years ago, to help porting applications from other database brands to > > MySQL, MySQL was made to accept the syntax even though no real > > constraints were created. > i hope postgresql will never "help" me this way. No kidding. What bothers me so much about this failure is that there's not way in the current version to change this behaviour. Everytime there's a MySQL versus PostgreSQL flamefest on Slashdot or elsewhere, I see the MySQL folks chiming in with "but the -ansi switch fixes all those problems" It doesn't, and there are many other things I've found that the -ansi switch doesn't fix. I really really really wish they'd make a version that followed the ANSI standard more closely, then had a "-compatv4" and "-compatv3" switch to make it behave like the older MySQL flavors. This defaulting to running like an old version, with all its issues is one thing that makes MySQL so unnattractive to use. That and the fact that if you've got a problem, the standard answer nowadays is "buy a support contract". ugh.
On Fri, Mar 24, 2006 at 10:32:42AM -0600, Scott Marlowe wrote: > > > http://bugs.mysql.com/bug.php?id=13301 > > > > > > > from the response: > > > > > Years ago, to help porting applications from other database brands to > > > MySQL, MySQL was made to accept the syntax even though no real > > > constraints were created. > > > i hope postgresql will never "help" me this way. > > No kidding. What bothers me so much about this failure is that there's > not way in the current version to change this behaviour. Everytime > there's a MySQL versus PostgreSQL flamefest on Slashdot or elsewhere, I > see the MySQL folks chiming in with "but the -ansi switch fixes all > those problems" > > It doesn't, and there are many other things I've found that the -ansi > switch doesn't fix. Got a list? I'd love to have it as ammo, and I'm sure that Ian at MySQL Gotchas would love to have it too. > I really really really wish they'd make a version that followed the ANSI > standard more closely, then had a "-compatv4" and "-compatv3" switch to > make it behave like the older MySQL flavors. > > This defaulting to running like an old version, with all its issues is > one thing that makes MySQL so unnattractive to use. That and the fact > that if you've got a problem, the standard answer nowadays is "buy a > support contract". ugh. Happen to have any examples of that as well? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, 2006-03-24 at 13:55, Jim C. Nasby wrote: > On Fri, Mar 24, 2006 at 10:32:42AM -0600, Scott Marlowe wrote: > > > > http://bugs.mysql.com/bug.php?id=13301 > > > > > > > > > > from the response: > > > > > > > Years ago, to help porting applications from other database brands to > > > > MySQL, MySQL was made to accept the syntax even though no real > > > > constraints were created. > > > > > i hope postgresql will never "help" me this way. > > > > No kidding. What bothers me so much about this failure is that there's > > not way in the current version to change this behaviour. Everytime > > there's a MySQL versus PostgreSQL flamefest on Slashdot or elsewhere, I > > see the MySQL folks chiming in with "but the -ansi switch fixes all > > those problems" > > > > It doesn't, and there are many other things I've found that the -ansi > > switch doesn't fix. > > Got a list? I'd love to have it as ammo, and I'm sure that Ian at MySQL > Gotchas would love to have it too. Actually, it's probably true for more than half the things on the mysql gotchas page. I haven't looked them over in a while, as after discovering 3 or 4 things you just couldn't fix with the -ansi switch I kinda gave up on MySQL as anything other than a simple text storage engine. While I think it's a pretty decent storage system for text documents with minimum needs for ref integrity, for anything else it's the most frustrating database in the world, so it's hard to get motivated. > > I really really really wish they'd make a version that followed the ANSI > > standard more closely, then had a "-compatv4" and "-compatv3" switch to > > make it behave like the older MySQL flavors. > > > > This defaulting to running like an old version, with all its issues is > > one thing that makes MySQL so unnattractive to use. That and the fact > > that if you've got a problem, the standard answer nowadays is "buy a > > support contract". ugh. > > Happen to have any examples of that as well? Only my most recent personal experience, when I was explaining to the guy from MySQL how frustrating it was that installing MySQL broke my build of PHP and meant I had to use the mysqli libs, not the mysql ones. The answer from the guy at MySQL was that the standard fix was to buy the commercial version, which is generally an older, stabler version. But I'm not going to pay money to see if MAYBE, just maybe, that version is better. They certainly haven't won me over with the GPL / Free version of the database, so why should I have any confidence of them doing it for money. MySQL feels less and less like Free Software every time I find a problem with it, and more and more like dealing with Oracle's morass of tech support layers to get an answer or a fix.
On Fri, 2006-24-03 at 14:53 -0600, Scott Marlowe wrote: ...snip... > Only my most recent personal experience, when I was explaining to the > guy from MySQL how frustrating it was that installing MySQL broke my > build of PHP and meant I had to use the mysqli libs, not the mysql > ones. The answer from the guy at MySQL was that the standard fix was to > buy the commercial version, which is generally an older, stabler > version. So is this older more stable version ANSI compliant, or is it broken like the free version?
Hello, I have with great interrest been following this thread. We have a (small) flame war in house about this and I'm very happy about all the arguments I have seen. I'm a long time user of PostgreSQL (which possibly makes me a bit biased ;-) ) and I think it's great. I'm not a big database expert, but I try to make things as good and standard as I can. In this respect I have 3 questions: 1) I wonder that no one has mentioned anything about security issues in those two. I know that I'm a novice and that I didn't use MySql very much, but it seems to me that the PostgreSQL security is much better than MySql !? 2) I don't know the latest SQL standard (I did say I'm a novice), but how close to some standard is embedded SQL in C in PostgreSQL, Oracle, and MySql ? 3) We are using mambo (the homepage management system), which is based on MySql. I would love to make it use PostgreSQL instead (to ease maintenance, backup, administration, etc.) Does anyone know where to get help/info on this ? Thanks for a good product and a good discusion, Leif On Wed, 22 Mar 2006, Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. > > Regarding MySQL, I've been put off by Oracle's recent purchase of > InnoDB and realise this could badly impact the latest version of the > MySQL database. I can almost hear Larry Ellison's laughter from here > (allegedly)! I've also been put off by the heavy marketing propaganda > on the MySQL website. > > Recently, I've been taking a look at PostgreSQL, and am very impressed > by what I've read, although I've not yet investigated the database > first-hand. To cut to the chase, I would be interested in anybody's > feedback on the advantages that PostgreSQL has over MySQL. > > Also, I've recently read the "Inside MySQL 5.0" (marketing propaganda) > document, and it makes the following claim: > > "With MySQL, customers across all industries are finding they can > easily handle nearly every type of database workload, with performance > and scalability outpacing every other open source rival. As Los Alamos > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > chose MySQL over PostgreSQL primarily because it scales better and has > embedded replication.".". > > If any PostgreSQL devotees on this group can comment on the above and > its accuracy/inaccuracy, I'd really appreciate it. > > Thanks in advance. > > James > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Sat, Mar 25, 2006 at 14:30:54 +0100, Leif Jensen <leif@crysberg.dk> wrote: > > 1) I wonder that no one has mentioned anything about security issues in > those two. I know that I'm a novice and that I didn't use MySql very much, > but it seems to me that the PostgreSQL security is much better than MySql > !? This may be because you are supposed to limit access to your database servers such that unauthorized users don't get direct access to them easily. So you don't have the same level of concern that you do for services generally exposed to the whole internet, such as for web and mail servers.
Leif Jensen wrote: > Hello, > > I have with great interrest been following this thread. We have a >(small) flame war in house about this and I'm very happy about all the >arguments I have seen. I'm a long time user of PostgreSQL (which possibly >makes me a bit biased ;-) ) and I think it's great. I'm not a big database >expert, but I try to make things as good and standard as I can. > > In this respect I have 3 questions: > >1) I wonder that no one has mentioned anything about security issues in >those two. I know that I'm a novice and that I didn't use MySql very much, >but it seems to me that the PostgreSQL security is much better than MySql >!? > > > Most people on the list only grudgingly use MySQL and so most are not so well aware of the limitations of MySQL's security model. MySQL has no concept of group memberships or group permissions (or the more complex role permissions). The permissions are simply at the level of the individual user. When I have coded complex apps on MySQL, I have sometimes found it necessary to emulate this level of permission so that the permissions can be "compiled" down to individual permissions on the tables. It is a real pain sometimes. Best Wishes, Chris Travers Metatron Technology Consulting
Вложения
On Saturday 25 March 2006 08:30, Leif Jensen wrote:
> Hello,
>
> I have with great interrest been following this thread. We have a
> (small) flame war in house about this and I'm very happy about all the
> arguments I have seen. I'm a long time user of PostgreSQL (which possibly
> makes me a bit biased ;-) ) and I think it's great. I'm not a big database
> expert, but I try to make things as good and standard as I can.
>
> In this respect I have 3 questions:
>
> 1) I wonder that no one has mentioned anything about security issues in
> those two. I know that I'm a novice and that I didn't use MySql very much,
> but it seems to me that the PostgreSQL security is much better than MySql
> !?
>
mysql's various user permissions / connection tables are often seen as being
more featurefull than postgresql pg_hba system, due to its closer likeness to
using sql, potentially simpler syntax, and ability to use remote admin tools.
That said some people also consider the mysql system an abomination, and much
proffer the internal user/group management you cando with sql compliant roles
that postgresql has. I think generally it is a wash, but the one important
point I think is that alot of mysql installation run as root, so any exploits
mysql has are potentially root level, which is something you don't have to
worry about in postgresql. This is more of a culture thing though than an
actual software issue.
> 3) We are using mambo (the homepage management system), which is based on
> MySql. I would love to make it use PostgreSQL instead (to ease
> maintenance, backup, administration, etc.) Does anyone know where to get
> help/info on this ?
>
Are you using mambo proper or possibly joomla? I think the Mambo developers
may be anti-postgresql, but Joomla should have decent postgresql support,
with the main person behind that being Mitch Pirtle of JamboWorks. You might
want to track him down.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Sun, 2006-03-26 at 16:00, Robert Treat wrote: > mysql's various user permissions / connection tables are often seen as being > more featurefull than postgresql pg_hba system, due to its closer likeness to > using sql, potentially simpler syntax, and ability to use remote admin tools. > That said some people also consider the mysql system an abomination, and much > proffer the internal user/group management you cando with sql compliant roles > that postgresql has. I think generally it is a wash, but the one important > point I think is that alot of mysql installation run as root, so any exploits > mysql has are potentially root level, which is something you don't have to > worry about in postgresql. This is more of a culture thing though than an > actual software issue. Well, first and foremost, most mysql installations no longer run as root. That was once a very real problem, but the mysql_safe script does much the same thing apache does, i.e. start a master daemon that then starts the children under another account with limited access. The MySQL security setup is kind of designed to be simple and easy to use. It allows all kinds of fun things like "grant select on * to whomever" which seems really great. Until you realize that you'll be doing that over and over, again and again, your whole life, because, as mentioned before, there are no groups. With PostgreSQL, you have the harder time of having to iterate over all the tables you want to grant access to, but since you can do this on a group level, you only ever have to do that once. Then, you can simply add / remove users from that group as needs be. From a database / normalization perspective, this is far superior. But, if you're used to the way MySQL does things, PostgreSQL seems horrific at first glance, but you soon realize that this is a better way. OTOH, if you're used to doing it the PostgreSQL way, MySQL seems horrific at first glance, and never really stops seeming horrific.
On Fri, 2006-03-24 at 17:08, Guy Fraser wrote: > On Fri, 2006-24-03 at 14:53 -0600, Scott Marlowe wrote: > ...snip... > > Only my most recent personal experience, when I was explaining to the > > guy from MySQL how frustrating it was that installing MySQL broke my > > build of PHP and meant I had to use the mysqli libs, not the mysql > > ones. The answer from the guy at MySQL was that the standard fix was to > > buy the commercial version, which is generally an older, stabler > > version. > > So is this older more stable version ANSI compliant, or is it broken > like the free version? It's basically just like the free version. It's just an older flavor. It would likely be 5.0.12 or whatever the first "release" flavor of 5.0 was, and with only security patches applied since then. While that may make the problems with newer versions go away, it certainly won't fix lingering issues that the newer versions have taken care of. I much prefer the PostgreSQL way of doing it. Get the latest patch level / sub-version, it's pretty much the best.
Scott Marlowe wrote: >On Sun, 2006-03-26 at 16:00, Robert Treat wrote: > > > >>mysql's various user permissions / connection tables are often seen as being >>more featurefull than postgresql pg_hba system, due to its closer likeness to >>using sql, potentially simpler syntax, and ability to use remote admin tools. >>That said some people also consider the mysql system an abomination, and much >>proffer the internal user/group management you cando with sql compliant roles >>that postgresql has. I think generally it is a wash, but the one important >>point I think is that alot of mysql installation run as root, so any exploits >>mysql has are potentially root level, which is something you don't have to >>worry about in postgresql. This is more of a culture thing though than an >>actual software issue. >> >> > > >The MySQL security setup is kind of designed to be simple and easy to >use. It allows all kinds of fun things like "grant select on * to >whomever" which seems really great. Until you realize that you'll be >doing that over and over, again and again, your whole life, because, as >mentioned before, there are no groups. > > And their idea of host-based authentication leads to some very odd issues occasionally. If Joe logs in from computer1.mydomain make him use this password, but if he logs in from computer2.mydomain, make him use that password and give him different permissions.... From the MySQL Manual: "A MySQL account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account also has a password." Also from the manual: " MySQL usernames can be up to a maximum of 16 characters long. This limit is hard-coded in the MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the |mysql| database /does not work/." In PostgreSQL, usernames are defined as a type that allows for up to 63 characters. Evidently the fact that there is a mention in the MySQL manual about the limit and the inability to modify the table definition is something people have run up against, but not in PostgreSQL ;-) Out of curiosity, how hard would it be to change the default maximum length on the name type in PostgreSQL? I would assume that it would be easier than in MySQL, where both the client and the server need to be modified. Finally, from the MySQL manual a statement that really alarms me: " MySQL encrypts passwords using its own algorithm." Yet nowhere (outside of reading the code) can I actually find out what that algorythm is. Way to make you feel secure. >With PostgreSQL, you have the harder time of having to iterate over all >the tables you want to grant access to, but since you can do this on a >group level, you only ever have to do that once. Then, you can simply >add / remove users from that group as needs be. From a database / >normalization perspective, this is far superior. But, if you're used to >the way MySQL does things, PostgreSQL seems horrific at first glance, >but you soon realize that this is a better way. > > There ought to be a simple way to create a PLPGSQL function that would do this for you. I just haven't been motivated to do it which gives you an idea of how seldom the problem actually comes up. >OTOH, if you're used to doing it the PostgreSQL way, MySQL seems >horrific at first glance, and never really stops seeming horrific. > > Well said. I would change that to "once you are used to doing it the PostgreSQL way...." My main CRM app (HERMES) used to support MySQL and I wrote a few hundred lines of PHP code to manage permissions so that I could emulate groups. Best Wishes, Chris Travers Metatron Technology Consulting
Вложения
Chris Travers <chris@verkiel.metatrontech.com> writes:
> Out of curiosity, how hard would it be to change the default maximum
> length on the name type in PostgreSQL?
Change NAMEDATALEN in postgres_ext.h, recompile, re-initdb.
I'm not sure why we still define it in postgres_ext.h, because it hasn't
been part of the client API for a long time --- any client code still
depending on it is pretty broken IMHO. There have been periodic
discussions about changing the default value to 128, which is what the
SQL spec says it should be, but we haven't done so because of worries
about bloating the system catalogs.
> There ought to be a simple way to create a PLPGSQL function that would
> do this for you.
People have posted examples a couple of times ... check the archives.
regards, tom lane
On Sat, 2006-25-03 at 10:11 -0800, Chris Travers wrote: > Leif Jensen wrote: > > > Hello, > > > > I have with great interrest been following this thread. We have a > >(small) flame war in house about this and I'm very happy about all the > >arguments I have seen. I'm a long time user of PostgreSQL (which possibly > >makes me a bit biased ;-) ) and I think it's great. I'm not a big database > >expert, but I try to make things as good and standard as I can. > > > > In this respect I have 3 questions: > > > >1) I wonder that no one has mentioned anything about security issues in > >those two. I know that I'm a novice and that I didn't use MySql very much, > >but it seems to me that the PostgreSQL security is much better than MySql > >!? > > > > > > > Most people on the list only grudgingly use MySQL and so most are not so > well aware of the limitations of MySQL's security model. > > MySQL has no concept of group memberships or group permissions (or the > more complex role permissions). The permissions are simply at the level > of the individual user. When I have coded complex apps on MySQL, I have > sometimes found it necessary to emulate this level of permission so that > the permissions can be "compiled" down to individual permissions on the > tables. It is a real pain sometimes. One thing that MySQL does have over PostgreSQL is column level permissions. I rarely need them and similar effects can be achieved joining data from tables with different permissions.
To go slightly OT, I have the current displeasure of becomming acquainted with Sybase which has ideas about being flexible with standards (and sanity) much like MySQL. The first of the two intentional "helpful" features I have come across so far is that inserting (or updating) char/varchar columns with strings that are too long silently results in a truncated string. The second is that in where clauses, NULL = NULL is true. (waits for you to get up off the floor) Apparently it didn't use to be like that, but the "feature" was added due to customer requests... What's worse is that Sybase is not even internally consistent - FALSE = FALSE in the join clause does not cause a match. Truly horrible. Of course there are runtime parameters you can set to get more ansi-ish behaviour, but by the time you realise this, there may already production code that relies on the behaviour...