Обсуждение: Database Selection
Hello all, I created two web database applications - first one is the company news system (as part of the company web pages), second one is the company main data system (part of the company pages, too). All (unfortunately) the M$ environment - 2003 server. The first app is managed from the web interface only, the second app is managed by database client win32 app (created by PowerBuilder, std client-server approach, ODBC). For the second app I need to create the web presentation interface now (no data modifications, heavy sql queries :-( ). The first app contains only small amount of data (10 database tables, max 100 rows in the largest table) but the second app contains large number of tables, more than 100000 rows in major part of tables, very sensitive company data, requires sophisticated and intelligent db engine strategies for queries, ... For the first app I wanted to use MySQL, for the second one I wanted the Sybase SQL Anywhere. But now I am in doubt. I'm playing with the idea to substitute the Sybase SQL Anywhere by the PostgreSQL or MySQL. I need all the SQL decorations (stored procedures, stored functions, triggers, declarative referential integrity (foreign keys), ...) I'm playing with the idea to unify the database environment (use only one db engine and two databases, not two different db engines for two databases), too. Is the MySQL or (preferably-IMHO) PostgreSQL database engine stable, robust, safe, ..... in order to use it for these company very sensitive data, for heavy data mining app, ... ? Is it safe to unify the database engine types and use only one engine for these two apps/dbs? Can I rely on the MySQL and/or PostgreSQL engines? Do you suggest me to use (one of/both) these two db engines for the main company data presentation database? Best Regards IvoD
If you look through this mailing list, the PostgreSQL website, and Google reviews of PostgreSQL, I think you will gain a very positive appreciation of PostgreSQL. It is stable, robust, and well-supported. It is being used in many mission-critical applications. Most recently, I've seen mention that it is being used by the Wisconsin court of appeals and I believe I saw a European government had selected it for their census data (perhaps the Netherlands?).
Michael Schmidt
On 24/4/2006 16:59, "IvoD" <gordion@quick.cz> wrote: > For the first app I wanted to use MySQL, for the second one I wanted > the Sybase SQL Anywhere. But now I am in doubt. I'm playing with the > idea to substitute the Sybase SQL Anywhere by the PostgreSQL or MySQL. > I need all the SQL decorations (stored procedures, stored functions, > triggers, declarative referential integrity (foreign > keys), ...) I'm playing with the idea to unify the database environment > (use only one db engine and two databases, not two different db engines > for two databases), too. If you want the full SQL decorations then you will want PostgreSQL. The newest version of MySQL has started to implement stored proc's - as this is a new addition to MySQL I wouldn't count on full support just yet. > Is the MySQL or (preferably-IMHO) PostgreSQL database engine stable, > robust, safe, ..... in order to use it for these company very sensitive > data, for heavy data mining app, ... ? Is it safe to unify the database > engine types and use only one engine for these two apps/dbs? Can I rely > on the MySQL and/or PostgreSQL engines? Do you suggest me to use (one > of/both) these two db engines for the main company data presentation > database? > PostgreSQL is considered stable and is used in production environments. If you look at the history of PostgreSQL you will find that the development at Berkley started with Ingres and after the code was used to start Relational Technologies/Ingres Corporation the Postgres project was born. A later version of Postgres was used by Illustra Information Technologies which later merged into Informix and is now owned by IBM. This gives you some some indication of the quality - two commercial databases have been started with PostgreSQL code.
Shane Ambler <pgsql@007Marketing.com> writes:
> If you look at the history of PostgreSQL you will find that the development
> at Berkley started with Ingres and after the code was used to start
> Relational Technologies/Ingres Corporation the Postgres project was born.
> A later version of Postgres was used by Illustra Information Technologies
> which later merged into Informix and is now owned by IBM.
> This gives you some some indication of the quality - two commercial
> databases have been started with PostgreSQL code.
Actually that's a misstatement --- AFAIK, Stonebraker and crew started
from scratch when they wrote Postgres, because they wanted to experiment
with a new system design based on what they'd learned while writing
Ingres. So there's no code shared between Ingres and Postgres, and
probably not much design commonality either, other than having sprung
from largely the same group of people.
Illustra/Informix, on the other hand, is indeed a fork of Postgres.
I don't know how similar that code base now is to ours, though. There's
been enough time for pretty substantial divergence on both sides of the
fork.
regards, tom lane
On 25/4/2006 6:47, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Shane Ambler <pgsql@007Marketing.com> writes: >> If you look at the history of PostgreSQL you will find that the development >> at Berkley started with Ingres and after the code was used to start >> Relational Technologies/Ingres Corporation the Postgres project was born. >> A later version of Postgres was used by Illustra Information Technologies >> which later merged into Informix and is now owned by IBM. >> This gives you some some indication of the quality - two commercial >> databases have been started with PostgreSQL code. > > Actually that's a misstatement --- AFAIK, Stonebraker and crew started > from scratch when they wrote Postgres, because they wanted to experiment > with a new system design based on what they'd learned while writing > Ingres. So there's no code shared between Ingres and Postgres, and > probably not much design commonality either, other than having sprung > from largely the same group of people. Bruce Momjiam say's "PostgreSQL's ancestor was Ingres" but I haven't found anything concrete one way or the other on whether Postgres started from scratch or from Ingres code (the name comes from 'post' 'gres' - after Ingres) > Illustra/Informix, on the other hand, is indeed a fork of Postgres. > I don't know how similar that code base now is to ours, though. There's > been enough time for pretty substantial divergence on both sides of the > fork. True but if the early code was good enough to make a commercial product (regarded as one of the first commercially successful relational databases) then it gives you some indication of the quality the project started from. Starting from a bad bug ridden beginning could carry problems through to today's version.
> > Bruce Momjiam say's "PostgreSQL's ancestor was Ingres" but I haven't found > anything concrete one way or the other on whether Postgres started from > scratch or from Ingres code > (the name comes from 'post' 'gres' - after Ingres) > My understanding is that we started from Ingres in the sense that we are derived from ideas that are better then Ingres, thus Postgres... I do not believe we share any code. Joshua D. Drake > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Some food for thought: http://sql-info.de/mysql/gotchas.html Something else to consider is that there's a number of options open to you for getting commercial PostgreSQL support should you want it. AFAIK you're pretty much limited to Sybase or MySQL when it comes to support for their products. Plus the support on these mailing lists is absulutely top-notch. -- 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
Jim C. Nasby wrote: > Some food for thought: http://sql-info.de/mysql/gotchas.html > > Something else to consider is that there's a number of options open to > you for getting commercial PostgreSQL support should you want it. AFAIK > you're pretty much limited to Sybase or MySQL when it comes to support > for their products. Plus the support on these mailing lists is > absulutely top-notch. ---------- But the spelling isn't. :-) {Couldn't resist.} -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Mon, Apr 24, 2006 at 07:15:12PM -0400, Bruce Momjian wrote: > Jim C. Nasby wrote: > > Some food for thought: http://sql-info.de/mysql/gotchas.html > > > > Something else to consider is that there's a number of options open to > > you for getting commercial PostgreSQL support should you want it. AFAIK > > you're pretty much limited to Sybase or MySQL when it comes to support > > for their products. Plus the support on these mailing lists is > > absulutely top-notch. > ---------- > > But the spelling isn't. :-) {Couldn't resist.} Befour scool i culd not speel enjuneer, now I are one! -- 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
Shane Ambler <pgsql@007Marketing.com> writes:
> On 25/4/2006 6:47, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> Actually that's a misstatement --- AFAIK, Stonebraker and crew started
>> from scratch when they wrote Postgres,
> Bruce Momjiam say's "PostgreSQL's ancestor was Ingres" but I haven't found
> anything concrete one way or the other on whether Postgres started from
> scratch or from Ingres code
If you want it from the horse's mouth, the appropriate place to look
is in the Berkeley database group's papers:
http://db.cs.berkeley.edu//papers/
I found relevant comments in
ERL-M85-95 The design of POSTGRES.
ERL-M90-34 The implementation of POSTGRES.
The latter paper in particular makes it clear that not only did Postgres
code start from scratch, but they threw things away and rewrote a
number of times after that.
regards, tom lane
I read many web pages about both PostgreSQL and MySQL, I read also "case studies" at pg web, but I prefer opinions of real users :-) I installed both db engines on my PC three weeks ago and now I test it. But I'm sure there should be "features" that I am not able to catch (e.g. MIN() function speed problems in previous pg versions) and that are not fixed yet. And therefore I ask all you - real users - about real experience and real enterprise applications. And last but not least - I must run db engine on Win platform (not Linux) and all the "success stories" assume Linux platform. So does somebody here know some good experience of "enterprise app" on M$ Win platform?
My "sixth sense" tells me that PostgreSQL is better than MySQL, therefore for main app I prefer PostgreSQL; but I am in doubt to run only one db engine for two databases. But my "inner space" tells me to separate newsgroups system and company data system and run two different db engines - in light of security (although only one db engine looks promissing).
On Tue, 2006-04-25 at 01:26, IvoD wrote: > I read many web pages about both PostgreSQL and MySQL, I read also > "case studies" at pg web, but I prefer opinions of real users :-) I > installed both db engines on my PC three weeks ago and now I test it. > But I'm sure there should be "features" that I am not able to catch > (e.g. MIN() function speed problems in previous pg versions) and that > are not fixed yet. And therefore I ask all you - real users - about > real experience and real enterprise applications. And last but not > least - I must run db engine on Win platform (not Linux) and all the > "success stories" assume Linux platform. So does somebody here know > some good experience of "enterprise app" on M$ Win platform? > My "sixth sense" tells me that PostgreSQL is better than MySQL, > therefore for main app I prefer PostgreSQL; but I am in doubt to run > only one db engine for two databases. But my "inner space" tells me to > separate newsgroups system and company data system and run two > different db engines - in light of security (although only one db > engine looks promissing). I've combined your two posts here into one for easy answering. MySQL was originally just a SQL front end to isam files. While much has been done to it over time, it's roots still show, and in ways that I personally don't really like. For instance, way back when, in order to make it easy to import schema from real databases like Oracle, MySQL swallowed but ignored column level constraint syntax. So, creating a table like: create child_table (i1 int, parent_id int references parent(id)); resulted in no error, but NO foreign key either. To me, that's the worst possible failure mode, a silent one. This philosophy still exists today. While MySQL supports foreign key constraints via innodb tables, it only supports the syntax in a table level format (i.e. (i1 int, parent_id int, foreign key ....)) and if you give it to mysql in a column level, it ignores it but produces no error. The philosophy of PostgreSQL is the polar opposite. If something doesn't work right, PostgreSQL throws and error and refuses to proceed, expecting you to take care of the problem NOW. Which is better? I prefer the postgresql way, because the mysql way leads to madness. Imagine thinking you've got FKs when you don't, and finding out 2 years down the road that all your data is incoherent because your database tricked you into thinking it was doing FK when it wasn't. About the security thing. Security is a process, and you won't get it from using two different database engines. There are other considerations. You can run multiple versions of PostgreSQL on the same box if that's what you need. Each needs to use a different tcp/ip port. Creating two separate databases within a single PostgreSQL server is the way I'd do it. That way they're both on the same port, and use the same shared memory, but for all intents and purposes, they are separate databases. Note that you can edit the pg_hba.conf file to allow only certain users to connect to one db or another. I wouldn't pick MySQL or PostgreSQL or both based on the security issue. You could just as easily run both on separate boxes for REAL security anyway.
IvoD wrote:
> real experience and real enterprise applications. And last but not
> least - I must run db engine on Win platform (not Linux) and all the
> "success stories" assume Linux platform. So does somebody here know
Well, for one thing... I have some experience with MySQL in that
respect, and I know that migrating MySQL on Windows to MySQL on Linux
(or a UNIX) causes trouble.
MySQL stores its' tables as files on the file system, which is case
insensitive on Windows and case sensitive on UNIX. If you didn't take a
lot of care to use the same case in your queries and your table
definitions, your queries will stop working once you migrate to UNIX...
With respect to PostgreSQL on Windows, utf-8 encoding isn't natively
supported on that platform, so you better don't use that for your
database encodings. A fix is in the works, if I understand correctly,
but AFAIK it isn't there yet.
Mind though that I rarely use Windows; I use it almost exclusively for
playing games. I am biased ;)
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
smarlowe@g2switchworks.com (Scott Marlowe) writes:
> About the security thing. Security is a process, and you won't get
> it from using two different database engines.
I'd argue that security is an "emergent property" which is either
supported by or undermined by particular
facts/features/configurations.
It's not something you can have; instead, conditions may either:
a) Leave you vulnerable to particular attacks, or
b) Protect you from particular attacks.
"Being secure" means that you have done an analysis of some set of
attacks and relevant vulnerabilities, and verified that your
conditions provide protection against those attacks.
Having multiple databases around would protect certain
vulnerabilities; whether they are *relevant* is a whole other
matter.
The notion of having a mental model of what security is, that's
something I'd consider vitally important. If you can't articulate
some sort of model that involves the notions of:
- Attacks, vulnerabilities, and protection against such
- Having some classification of kinds of possible attacks
then I don't think it's possible to articulate that there is any
resultant security.
You might be secure, for some definition thereof, but if you can't
articulate that definition...
--
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/security.html
Friends help you move. Real friends help you move bodies.
On Mon, Apr 24, 2006 at 10:46:50PM -0700, IvoD wrote: > My "sixth sense" tells me that PostgreSQL is better than MySQL, > therefore for main app I prefer PostgreSQL; but I am in doubt to run > only one db engine for two databases. But my "inner space" tells me to > separate newsgroups system and company data system and run two > different db engines - in light of security (although only one db > engine looks promissing). Since I haven't seen it mentioned, google for 'mysql gotchas'. About 100 reasons not to use MySQL (granted, there's some PostgreSQL gotchas as well, but there's far fewer and they're nowhere near as serious). -- 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 Tuesday 25 April 2006 01:46, IvoD wrote:
> My "sixth sense" tells me that PostgreSQL is better than MySQL,
> therefore for main app I prefer PostgreSQL; but I am in doubt to run
> only one db engine for two databases. But my "inner space" tells me to
> separate newsgroups system and company data system and run two
> different db engines - in light of security (although only one db
> engine looks promissing).
>
This seems pretty illogical if you follow that line of thinking out a little
further. Would you expect it to be more secure if you ran one system on
linux, apache, mod_foo and the other on bsd, lighty, and mod_bar ? Best to
through your support behind one complete system (sounds like postgresql from
previous postings) that can do the job and become an expert in making that
system secure.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Centuries ago, Nostradamus foresaw when "IvoD" <gordion@quick.cz> would write: >> smarl...@g2switchworks.com (Scott Marlowe) writes: >> > About the security thing. Security is a process, and you won't get >> > it from using two different database engines. >> >> I'd argue that security is an "emergent property" which is either >> supported by or undermined by particular >> facts/features/configurations. > > I had other "security" aspect on my mind - one half of the > newsgroups data will be accessible from public part of web pages, > second part and the whole company data system will be accessible > from private part of web pages; newsgroups database must have > read/write web access, company database will have read only web > access and read/write access from 3 specific IPs. > > Lets assume two databases+two database engines: > If somebody hacks the newsgroups database and gets the read/write > access then he cannot access data from the company database (different > engine, different engine type). Let's assume two databases of two "styles"... The administrator is unlikely to have a comprehensive understanding of the security models of either, and will likely be forced into using a security model that is something of a "lowest common denominator," taking on weaknesses of both, unable to take advantage of either's strengths, and being vulnerable because they don't have time to understand how to comprehensively protect both systems. In effect, by dividing your attention, you present attackers with an additional vulnerability. > And now lets assume two databases+one database engine: If somebody > hacks the newsgroups database and gets the read/write access then he > could switch database under the same hacked access and get the > read/write access to company data (if somebody gets access to > protected database through (at least) the "only local > access+login+password" restrictions then I must expect he knows how > to switch (hack) to any connected database under the same engine). You're essentially making the "monoculture" argument; if one fails, they all do. Unfortunately, that only involves some subset of the threats. > That is why I wanted to separate two databases using two different > database engines (in order to increase the standard security covered by > other security rules) Fallacy: You can't "increase" security, and there's no such thing as "standard security." What you can do is to use techniques that may (or may not) protect certain vulnerabilities, whilst potentially exposing others. > But this idea is maybee too paranoiac and disadvantages of two > different engines exceed the security benefits (maybe hypothetic) Security is about protecting against attacks. If there are unmeasurable attacks, then measuring vulnerability or the implications of attempts at protective measures may also not be measurable. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://linuxfinances.info/info/x.html "But what....is it good for?" -- Engineer at the Advanced Computing Systems Division of IBM about the microchip. 1968
After takin a swig o' Arrakan spice grog, xzilla@users.sourceforge.net (Robert Treat) belched out: > On Tuesday 25 April 2006 01:46, IvoD wrote: >> My "sixth sense" tells me that PostgreSQL is better than MySQL, >> therefore for main app I prefer PostgreSQL; but I am in doubt to >> run only one db engine for two databases. But my "inner space" >> tells me to separate newsgroups system and company data system and >> run two different db engines - in light of security (although only >> one db engine looks promissing). > > This seems pretty illogical if you follow that line of thinking out > a little further. Would you expect it to be more secure if you ran > one system on linux, apache, mod_foo and the other on bsd, lighty, > and mod_bar ? Best to through your support behind one complete > system (sounds like postgresql from previous postings) that can do > the job and become an expert in making that system secure. I approve of the "avoid monoculture" notion, in some ways. After all, it *is* an enormous problem that the world is filled with Windows systems that run the same kernels, most of the same drivers, the same MS Office, the same Outlook, and which hence are vulnerable to the varied array of Things That Attack Windows. And "script kiddies" have had a history of analyzing what vulnerabilities Red Hat left in their distribution. A monoculture of Red Hat 7.2 systems isn't all that secure, either. I think we'd be in a "better world," computing-wise, if we had some better diversity of the sort where we had a multiplicity of platforms. MVS, VMS, OS/2, Unix, all have been been meaningful to that end. But I'm not at all sure that security is enhanced simply in the fact of having multiple flavours of databases in the environment. I can only see that being of material assistance once you have gotten to the point where it *isn't* more useful to secure what you already have than it is to introduce brand new components you need to learn to secure. -- let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];; http://cbbrowne.com/info/wp.html If men can run the world, why can't they stop wearing neckties? How intelligent is it to start the day by tying a little noose around your neck? --Linda Ellerbee
All opinions here sound good so I decided to use only one db engine (PostgreSQL 8.1) and two databases; now I must tune my database generator parameters (I use PowerDesigner by Sybase) in order to work-arround the pg's identifier case sensitivity (newsgroups app (php scripts) doesn't use quotes (developed against MySQL) but the PowerBuilder app for company data uses quotes and mixed case of identifiers (developed against Sybase SQL Anywhere)). Thank you all for sharing all your experience and your time. Ivo
Yes, I read this opinion that MySQL is only sql interface to filesystem :-) I plan to use win1250 encoding because this is native czech windows encoding (I do not understand why M$ invents the wheel and invents new code pages (cp1250) although the code page latin2 was here all the time). BTW czech windows uses cp1250 for window app and cp852 for console app. So if I write C++ console app in BCB and printf czech text then I see garbage (do you know another "OS" that by nature uses two code pages for its two app groups? Horrible)
> smarl...@g2switchworks.com (Scott Marlowe) writes: > > About the security thing. Security is a process, and you won't get > > it from using two different database engines. > > I'd argue that security is an "emergent property" which is either > supported by or undermined by particular facts/features/configurations. > I had other "security" aspect on my mind - one half of the newsgroups data will be accessible from public part of web pages, second part and the whole company data system will be accessible from private part of web pages; newsgroups database must have read/write web access, company database will have read only web access and read/write access from 3 specific IPs. Lets assume two databases+two database engines: If somebody hacks the newsgroups database and gets the read/write access then he cannot access data from the company database (different engine, different engine type). And now lets assume two databases+one database engine: If somebody hacks the newsgroups database and gets the read/write access then he could switch database under the same hacked access and get the read/write access to company data (if somebody gets access to protected database through (at least) the "only local access+login+password" restrictions then I must expect he knows how to switch (hack) to any connected database under the same engine). That is why I wanted to separate two databases using two different database engines (in order to increase the standard security covered by other security rules) But this idea is maybee too paranoiac and disadvantages of two different engines exceed the security benefits (maybe hypothetic)