Netwrkengeer
IS-IT--Management
how does PostgreSQL compare to DB2, What makes it better or worse
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Compare Postgresql
- Thread starter Netwrkengeer
- Start date
- Status
Conceptually speaking, there is essentially nothing that DB2 has that PostgreSQL doesn't. By "conceptually" I mean the logical capabilities of a relational database. They will both have transactions, foreign keys, views, triggers, stored procedures, etc...
Now, the differences:
1. Pro-DB2
I'm fairly sure that the main areas where DB2 will differ from PostgreSQL are what the business usually refers to as "value added" features, such as an integrated GUI administration system, (possibly) a smoother install routine, and integrated add-on features such as replication, failover, clustering, etc... Also, of course, there is "official" corporate support for the software.
2. You Be the Judge
DB2 also of course (as most commercial DBMS's) now boasts a whole set of bundled tools for such stuff as XML, SOAP, development libraries, OLAP, etc... These things shouldn't rightfully be called part of the database, because they are application tools, rather than core database features. And of course special support for "complex" datatypes such as multimedia data, etc... I personally think most of these attempts are somewhat counter-productive, as they take the focus off of what should be the main job of the database: providing the proper internal constraints for managing data and providing a logically universal method to query and manipulate the data (the query language). Everything else belongs in application space. It has become a fad lately to bundle everything but the kitchen sink in with a database, when those tools can quite easily be applied separately, as the developer wishes. An example of the most ridiculous side of this is how all the vendors are now saying their databases are "Web-ready", as if it takes a special kind of database to handle data for the internet. (lol) Data is data! These are just marketing techniques designed to get business leaders to try and make the technical decisions they probably should leave to their engineers and developers. See my favorite database website for more opinions in this area:
3. Pro-PostgreSQL
ON the contrary from section 2, PostgreSQL supports all of the standard SQL-relational features of the "big business" databases, in a relatively clean and straightforward fashion. The primary download for PostgreSQL is around 9-10 MB, while any other big SQL database usually takes more than 100 MB to download just the base files. Yet it provides all the same relational database constraints. Go figure.
PostgreSQL also does a few extra things that many other databases don't, such as:
1. object-relational inheritance in table attributes. I don't think any other major database supports this feature. It provides for some very interesting programming possibilities that would take far too long to explain here.
2. Spatial (geometric) datatypes, as well as special operators and algorithms for doing queries based on spatial relationships. I believe DB2 has some of these features also, but I don't know if it is as complete as PostgreSQL's
3. User-defined datatypes and operators. With PostgreSQL you can define your own datatype that plays by your own rules. Thus, you can handle any type of "complex" data however you want. In fact, there is a company that has extended postgreSQL's spatial datatypes to include a full GIS package (
4. PostgreSQL is making serious advances on enterprise features such as replication, high availability, clustering, failover, etc... Spend some time at for more info.
5. The source code. Commercial vendors don't give you the source code to your database. With PostgreSQL, if there is really something you aren't sure about, you can (hire someone to) look at the source code to make sure. You can even re-write parts of it for your needs.
6. The source code is free, and not just in the GPL sense. Software that is distributed under the GPL prevents you from re-releasing any project that includes that code, without releasing the code itself, including any of your proprietary code that is included. PostgreSQL is released under the BSD license, which allows you to embed the code in your project without forcing you to release your source along with it. All you need to do is give the PostgreSQL team credit for the database. This is great for any business who might need to include an embedded database engine in a complex project they are releasing.
7. Commercial support? Go to click on "Support". Enough said.
As you can tell, I really like PostgreSQL. I have spent my time with MySQL, and while it makes the easy things easier, it makes the hard things harder. PostgreSQL makes the hard things easier, and in some cases makes the impossible... possible. I'm sure you can't go wrong with DB2, but you also can't go wrong with PostgreSQL, and while it may require a little more effort up front, in the end you won't regret it. -------------------------------------------
"Now, this might cause some discomfort..."
(
Now, the differences:
1. Pro-DB2
I'm fairly sure that the main areas where DB2 will differ from PostgreSQL are what the business usually refers to as "value added" features, such as an integrated GUI administration system, (possibly) a smoother install routine, and integrated add-on features such as replication, failover, clustering, etc... Also, of course, there is "official" corporate support for the software.
2. You Be the Judge
DB2 also of course (as most commercial DBMS's) now boasts a whole set of bundled tools for such stuff as XML, SOAP, development libraries, OLAP, etc... These things shouldn't rightfully be called part of the database, because they are application tools, rather than core database features. And of course special support for "complex" datatypes such as multimedia data, etc... I personally think most of these attempts are somewhat counter-productive, as they take the focus off of what should be the main job of the database: providing the proper internal constraints for managing data and providing a logically universal method to query and manipulate the data (the query language). Everything else belongs in application space. It has become a fad lately to bundle everything but the kitchen sink in with a database, when those tools can quite easily be applied separately, as the developer wishes. An example of the most ridiculous side of this is how all the vendors are now saying their databases are "Web-ready", as if it takes a special kind of database to handle data for the internet. (lol) Data is data! These are just marketing techniques designed to get business leaders to try and make the technical decisions they probably should leave to their engineers and developers. See my favorite database website for more opinions in this area:
3. Pro-PostgreSQL
ON the contrary from section 2, PostgreSQL supports all of the standard SQL-relational features of the "big business" databases, in a relatively clean and straightforward fashion. The primary download for PostgreSQL is around 9-10 MB, while any other big SQL database usually takes more than 100 MB to download just the base files. Yet it provides all the same relational database constraints. Go figure.
PostgreSQL also does a few extra things that many other databases don't, such as:
1. object-relational inheritance in table attributes. I don't think any other major database supports this feature. It provides for some very interesting programming possibilities that would take far too long to explain here.
2. Spatial (geometric) datatypes, as well as special operators and algorithms for doing queries based on spatial relationships. I believe DB2 has some of these features also, but I don't know if it is as complete as PostgreSQL's
3. User-defined datatypes and operators. With PostgreSQL you can define your own datatype that plays by your own rules. Thus, you can handle any type of "complex" data however you want. In fact, there is a company that has extended postgreSQL's spatial datatypes to include a full GIS package (
4. PostgreSQL is making serious advances on enterprise features such as replication, high availability, clustering, failover, etc... Spend some time at for more info.
5. The source code. Commercial vendors don't give you the source code to your database. With PostgreSQL, if there is really something you aren't sure about, you can (hire someone to) look at the source code to make sure. You can even re-write parts of it for your needs.
6. The source code is free, and not just in the GPL sense. Software that is distributed under the GPL prevents you from re-releasing any project that includes that code, without releasing the code itself, including any of your proprietary code that is included. PostgreSQL is released under the BSD license, which allows you to embed the code in your project without forcing you to release your source along with it. All you need to do is give the PostgreSQL team credit for the database. This is great for any business who might need to include an embedded database engine in a complex project they are releasing.
7. Commercial support? Go to click on "Support". Enough said.
As you can tell, I really like PostgreSQL. I have spent my time with MySQL, and while it makes the easy things easier, it makes the hard things harder. PostgreSQL makes the hard things easier, and in some cases makes the impossible... possible. I'm sure you can't go wrong with DB2, but you also can't go wrong with PostgreSQL, and while it may require a little more effort up front, in the end you won't regret it. -------------------------------------------
"Now, this might cause some discomfort..."
(
Oh, and one more plus for PostgreSQL: most of the commercial DB vendors have chosen to ignore FreeBSD as a platform, which I think is a mistake, since it is the most stable open source OS out there. PostgreSQL supports FreeBSD nicely, as it should, since they both get their research heritage from the same place-- U of C at Berkeley. -------------------------------------------
"Now, this might cause some discomfort..."
(
"Now, this might cause some discomfort..."
(
- Thread starter
- #4
Netwrkengeer
IS-IT--Management
Thank Rycamor, as always you have the best answers.
You should consider writing a book, your writing style and knowledge is consistent with a top technical author.. and you could always have alex help you fill in the Unix blanks..
Talk to you later
You should consider writing a book, your writing style and knowledge is consistent with a top technical author.. and you could always have alex help you fill in the Unix blanks..
Talk to you later
Thanks, Rob.
It is something we have been considering, but other projects are still occupying our time. Sometime soon, though, I think we will at least write a few online articles, and then see where that takes us. -------------------------------------------
"Now, this might cause some discomfort..."
(
It is something we have been considering, but other projects are still occupying our time. Sometime soon, though, I think we will at least write a few online articles, and then see where that takes us. -------------------------------------------
"Now, this might cause some discomfort..."
(
- Thread starter
- #6
Netwrkengeer
IS-IT--Management
Good luck with that, I really think you are well suited, I think you have a great writing style.. Anyway, I have another question, If I'm running a PostgreSQL DB on a freeBSD machine, can I export (replicate) the DB including the tables, Data and User-defined datatypes and operators to an AS400 running AIX.
Thanks
Thanks
Exporting and replicating are technically two different things. Replication assumes a constant streaming of data over to another database, so that the two are always in sync.
Exporting any postgreSQL database is pretty easy to do with "pg_dump". Be sure to read the pg_dump manual carefully, though, because there are a few options that can make a difference in certain areas. My standard usage to dump a complete database is
, but there are a few other options to consider, especially with a very large database containing BLOBs. Still, PostgreSQL's dump facility is very simple compared to many large databases. To create a database on the other server with the dumpfile, you can either pipe the dumped file directly to psql, or if you need more flexibility, you can use "pg_restore".
If you want to actually replicate your database onto the AIX machine, then you should definitely spend some time browsing around There is some amazing information buried in that site ;-). Unfortunately (or fortunately, depending on how you look at it), there is no one single integrated solution for PostgreSQL replication, but there are several third-party libraries. So, you are left with the task of evaluating which one will best meet your needs. Here are a few good links for replication:
(search this page for "replication"
(and
To be honest, I have not tested any of these solutions, so I can't offer you any help in deciding which to use.
-------------------------------------------
"Now, this might cause some discomfort..."
(
Exporting any postgreSQL database is pretty easy to do with "pg_dump". Be sure to read the pg_dump manual carefully, though, because there are a few options that can make a difference in certain areas. My standard usage to dump a complete database is
Code:
pg_dump -C -f /path/to/filename.sql databasenameIf you want to actually replicate your database onto the AIX machine, then you should definitely spend some time browsing around There is some amazing information buried in that site ;-). Unfortunately (or fortunately, depending on how you look at it), there is no one single integrated solution for PostgreSQL replication, but there are several third-party libraries. So, you are left with the task of evaluating which one will best meet your needs. Here are a few good links for replication:
(search this page for "replication"
(and
To be honest, I have not tested any of these solutions, so I can't offer you any help in deciding which to use.
-------------------------------------------
"Now, this might cause some discomfort..."
(
- Thread starter
- #8
Netwrkengeer
IS-IT--Management
Thanks your the best,
Talk to you later
Talk to you later
- Thread starter
- #9
Netwrkengeer
IS-IT--Management
Ok another Question, kind of off the subject but since I got you here..
If I run FreeBSD in Linux compatability mode, can I a specific software or task in Linux compatability mode or does Linux mode apply to every process running.
i.e. Can I run DB2 in Linux compatibility mode and apache web server in normal (freeBSD) mode..
If I run FreeBSD in Linux compatability mode, can I a specific software or task in Linux compatability mode or does Linux mode apply to every process running.
i.e. Can I run DB2 in Linux compatibility mode and apache web server in normal (freeBSD) mode..
jeanpierre
Technical User
That did not work for me - but I did not trouble shoot beyond a preliminary level.
Running FreeBSD in Linux compatibility mode does not in any way affect FreeBSD software itself. It is only intended as a wrapper for Linux binaries. For example, on one of my previous FreeBSD machines, I ran Java in Linux mode, since the Linux JDK was easier to get working at the time, but the rest of the processes were standard. Fortunately, there is almost no performance loss when running in Linux compatibility mode.
Sometimes it is a bit of a pain to get Linux stuff working in FreeBSD, though. Here is a good article on running DB2 on FreeBSD: and here is one on general Linux compatibility:
-------------------------------------------
"Now, this might cause some discomfort..."
(
Sometimes it is a bit of a pain to get Linux stuff working in FreeBSD, though. Here is a good article on running DB2 on FreeBSD: and here is one on general Linux compatibility:
-------------------------------------------
"Now, this might cause some discomfort..."
(
- Thread starter
- #12
Netwrkengeer
IS-IT--Management
Hey Rick,
Do you know if anyone has extended MySQL's spatial datatypes to include a full GIS package
Do you know if anyone has extended MySQL's spatial datatypes to include a full GIS package
<grin> You mean PostgreSQL's spatial datatypes, right? And yes. I put that link in my first post: -------------------------------------------
"Now, this might cause some discomfort..."
(
"Now, this might cause some discomfort..."
(
Oh, and speaking of spatial/geographic database applications, I just finished a small but fun project at (click on the maps). It maintains information about communication tower distances. -------------------------------------------
"Now, this might cause some discomfort..."
(
"Now, this might cause some discomfort..."
(
jeanpierre
Technical User
Rycamor - Great work at
And thanks for your always very educating posts. You know way more than my professor here at UBC.
And thanks for your always very educating posts. You know way more than my professor here at UBC.
- Thread starter
- #16
Netwrkengeer
IS-IT--Management
Rick,
How comfortable is it for a MySQL Designer, to switch to PostgreSQL, are their enough similarities between the 2 to make an easy transistion. Mentally? not mechanically.
How comfortable is it for a MySQL Designer, to switch to PostgreSQL, are their enough similarities between the 2 to make an easy transistion. Mentally? not mechanically.
The transition depends on your conceptual knowledge of relational databases. For example, there are things you have to do in MySQL that you wouldn't have to do in PostgreSQL. For the most part, though, the difference is that your horizons will be faaaar wider. In other words, your basic methods from MySQL would work with PostgreSQL, but you really would want to go beyond those methods, and learn to work with real data constraints, and abstraction methods.
Mechanically, there are differences, especially in the finer points of syntax for such things as date handling, type casting, string manipulation, etc... Basically, PostgreSQL runs circles around MySQL in all these areas, so the syntax is much richer, and a little more complicated.
See for a discussion on migrating from MySQL to PostgreSQL.
My recommendation: install PostgreSQL, PHP, and phpPgAdmin from phppgadmin.sourceforge.net, and start playing with it. Get a good book, such as "PostgreSQL Developer's Handbook", and start reading. Reading is a must with PostgreSQL. -------------------------------------------
"Now, this might cause some discomfort..."
(
Mechanically, there are differences, especially in the finer points of syntax for such things as date handling, type casting, string manipulation, etc... Basically, PostgreSQL runs circles around MySQL in all these areas, so the syntax is much richer, and a little more complicated.
See for a discussion on migrating from MySQL to PostgreSQL.
My recommendation: install PostgreSQL, PHP, and phpPgAdmin from phppgadmin.sourceforge.net, and start playing with it. Get a good book, such as "PostgreSQL Developer's Handbook", and start reading. Reading is a must with PostgreSQL. -------------------------------------------
"Now, this might cause some discomfort..."
(
- Thread starter
- #18
Netwrkengeer
IS-IT--Management
Thank you,
Is Ansi SQL an Aplication or a srandard. I talk to some people and they speak of ansi as an app, and some as if it is just a standard, so shoot me a little insight
Thanks
Is Ansi SQL an Aplication or a srandard. I talk to some people and they speak of ansi as an app, and some as if it is just a standard, so shoot me a little insight
Thanks
ANSI stands for American National Standards Institute. It is concerned with standards in all kinds of industries, not just computers.
So it is definitely not an spp. It is just the standardization of SQL decided on by the IT branch of ANSI. Every few years, various authorities on SQL get together and decide on the latest version of this standard. I believe ANSI SQL-93 was the last version, and there is another one on the way.
has more info, but the site seems to be down right now...
No SQL database complies 100% with ANSI standards. They all leave some things out, and add some proprietary methods of their own. -------------------------------------------
"Now, this might cause some discomfort..."
(
So it is definitely not an spp. It is just the standardization of SQL decided on by the IT branch of ANSI. Every few years, various authorities on SQL get together and decide on the latest version of this standard. I believe ANSI SQL-93 was the last version, and there is another one on the way.
has more info, but the site seems to be down right now...
No SQL database complies 100% with ANSI standards. They all leave some things out, and add some proprietary methods of their own. -------------------------------------------
"Now, this might cause some discomfort..."
(
- Thread starter
- #20
Netwrkengeer
IS-IT--Management
thank you again,
Next question. do you know of any software that I could integrate with Php or Java, AND MySQL/PostgreSQL, that would enable me to get Latitude/Longitude coordinates from street addresses in the USA.
WOW I Ask a lot of questions..
Next question. do you know of any software that I could integrate with Php or Java, AND MySQL/PostgreSQL, that would enable me to get Latitude/Longitude coordinates from street addresses in the USA.
WOW I Ask a lot of questions..
- Status
Similar threads
- Locked
- Question