PostgreSQL is a robust, next-generation, Object-Relational DBMS (ORDBMS), derived from the Berkeley Postgres database management system. “PostgreSQL is one of the best-managed open source projects out there” a friend-in-the-know told me about 2 years ago, so today we feature a mini-interview with five members of the PostgreSQL team about their plans on the popular DB.
Interviewees are:
Bruce = Bruce Momjian, major PostgreSQL developer.
Neil = Neil Conway, major PostgreSQL developer.
Robert = Robert Treat, Advocacy volunteer and editor of the PostgreSQL Weekly News newsletter.
Josh = Josh Berkus, PostgreSQL Advocacy volunteer.
1. What’s the latest news regarding replication on PostgreSQL?
Bruce: We have several master-slave solutions, and are working on multi-master for the release after next.
Robert: Also, PostgreSQL Inc. has recently teamed up with Lanux Limited to offer a high-availability solution.
2. What about clustering, load balancing and nested transactions?
Bruce: We have none of those, but we might have nested transactions this summer. Clustering and load balancing are part of multi-master replication.
3. How do you view your competition in the free RDBMS market, that includes mSQL, mySQL, sapDB and Firebird? Which features does PostgreSQL still lacks that some of these DBs have and which features PostgreSQL has today that these DBs doesn’t?
Josh: You have me at a bit of a disadvantage, since I’m used to comparing PostgreSQL to proprietary-commercial databases, who I regard as our “real” competition. I don’t know that much about SAP-DB and Firebird beyond their literature on their websites.
The only features that we lack which some other FOSS databases have are the ones you mention in questions 1 & 2. Oh, and our native Windows version is due out this summer.
Fortunately, we have a *lot* of features that the other FOSS databases don’t have. These include:
— Very high level (90%?) of ANSI SQL 92/99 compliance and implementation, and getting better with each point release.
— SQL SCHEMA, which I believe that only SAP DB has among those mentioned.
— Extensive support of business logic building in the database, including Triggers, Rules, Functions, Views, and custom Constraints. While some of the other FOSS databases have some of these elements, none of them has the breadth and depth of support for all avenues of database automation and data integrity that PostgreSQL does. For example, I believe that we have the only RDBMS that allows writing functions and procedures in 7 programming languages.
— And, paramount: our built-in-the-database extensibility, including custom Aggregates, Operators, Data Types, Domains, Functions, and other elements of Stonebreaker-style ORDMBS extensibility. This seminal element of our design philosophy has already given us some community-contributed constructs that no other database, FOSS or commercial, has, such as our set of Networking data types and operators, or PL/R for advanced statistical analysis.
Robert: Don’t forget the PostGIS project for Open GIS implementation.
4. The most popular of your competitors, mySQL, surely moves very fast in advancements and version 5 is expected next year. What are the plans for the next big version of PostgreSQL and how do you plan on coupling their advancements?
Josh: In terms of features MySQL isn’t adding anything that PostgreSQL doesn’t already have, and hasn’t had for years. We had transaction support 10 years before MySQL, mainframe ports 2 years before MySQL, and support for all types of subselects for the last 2 years, which MySQL still doesn’t have working.
Robert: MySQL used to be faster than PostgreSQL across the board, but we’ve erased that difference and now it’s only faster in certain corner cases at lower load levels.
Josh: The only things that MySQL has that we don’t are ease of database admin for simple databases, which is something we’re gradually incorporating, and a native Windows port, which we’ll have this summer.
Oh, and they have a paid marketing department, which I suppose gives them a certain edge (*wry grin*).
5. What innovations this decade have to offer to the database technology in general? What kind of features that no RDBMS has today are you thinking that would be great to have in the future?
Neil: POSTGRES was originally conceived as an academic research project into ORDBMS technology, so we already have some experimental features that other database systems either don’t have, or have only recently implemented (for example, inheritance, query rewriting and user-defined data types). More recently, the PostgreSQL development team has been more focused on providing a better production-quality RDBMS implementation, rather than working on implementing additional experimental features. Once most of this work has been finished (and we’ve done a lot already), I think there will be more interest in adding additional exotic
features.
That said, there are also projects that use PostgreSQL for their own ends, to implement some interesting and often unique functionality. Two examples are the Telegraph project at Berkeley, and the PostGIS project.
Josh: Personally, I don’t expect any flashy new database features or theories in the next decade. We’ve had a SQL specification since 1989, but up until recently it was more of a “holy grail” and not something one expected to actually follow due to hardware and software limitations in databases. Now the tech has finally caught up, and we have a chance to really exercise all of the ideas contained in the relational model and the SQL spec, in order to build really good databases. It’s an era of refinement rather than one of sweeping innovation.
Now, I do expect that object and OODBMSs will refine themselves and develop an open standard within the next decade, so they can become commercially viable as an alternative approach to data storage. But I also tend to think that the creators of OODBMSs will find that they are useful for *very* different things than those for which RDBMSs have traditionally been used.
6. Are you planning a “native” Windows port for PostgreSQL (not through Cygwin that is)? If yes, are there plans for a C# programming interface?
Josh: The native Windows port is due this summer. While we’re unlikely to adopt C# for the database itself, I’d be very surprised if some member of our community *doesn’t* add C# as a function-writing language.
Robert: Plus we already have a .net data provider project for connecting to PostgreSQL via .Net.
I use PostgreSQL on a daily base and it is, imho, the best open-source database available (not flaming..) but i do
wish it was more coverd by various sites and books.
They mostly talk about MySQL wich is not as good and not free.
If you do a lot of transactions and do not have the knowledge or the money to implent Oracle or DB2 than PostgresSQL is your number one option.
I’m looking forwards to their windows port. After reading about Firebird, MySQL,and MSSQL comparitively, I am excited about something in the free market that competes at a higher level and will be available for windows. Not everyone can afford MSSQL server and my clients will be happy to hear that they have another option. Long live competition!
On a side note, regardless of their products power, they seem like cocky ass-holes. That wasn’t very professional of them… bad form!
They are right. If you ever used the other opensource databases, you’d know that they are a sad cludge compared the the niceness of postgres.
Even ms sql server pales when it comes to some postgres features(aka their stored procs are nicer..etc).
Firebird already has a full featured native win32 version and has full support for transactions/stored procedures/triggers/ etc etc.
We have ported MS SQL server apps with great success to Firebird.
The learning curve of FB compared to Postgress is alot less.
The FB procedural language is powerful and easy to learn and can be extented with UDF (user defined functions) that can be programmed in C,C++ or Pascal
another big plus for Firebird is the large amount of 3rd party support for Admin tools, one of the best is IB Expert http://www.ibexpert.com
Firebird also has JODBC,ODBC,AODB,.net drivers available.
Check out http://www.ibphoenix.com
It’s nice to see talk about PostgreSQL, which seems to go largely ignored between Firebird zealotry and MySQL usage (I’ve never met a MySQL zealot… I think people are too ashamed)
I remember a time when MySQL was the performer and postgres simply provided you more functionality, but it seems that postgres has caught up in speed with MySQL over the years. I hope it continues to advance and see more common and widespread use.
My mistake. I must not have seen the fact that firebird has a win32 port. Having a .NET provider is big plus for me!
When did they have this available? because I honestly don’t recall seeing it on their site.
@Taras: Regardless of right or wrong, it is unprofessional in my eyes to portray a “holier than thou” attitude in the business world. They could have brought up the same points about the differences in more tactful fashion. .02
One thing I have found a pain in the ass in Postgress is returning a result set from a stored proc.
In Firbird you just add output params and add a suspend statement. then in you dynamic SQL code on the client the stored proc is treated exactly like a table.
i.e.
CREATE PROCEDURE ADMIN_GET_USERS
RETURNS (THENAME VARCHAR(35))
AS
BEGIN
for select username from USERDATA into :THENAME do
SUSPEND;
END
select * from ADMIN_GET_USERS
Can Postgress do this? If it can maybe someone could show me how, as this is the big reason I don’t use Postgres.
Firebird has had a native win32 version for years.
They inherited from the Open Source version of Interbase in 2000.
I do like Postgress, but it is not as easy to use as Firebird. It also needs a first rate admin tool that is not done in VB.
Jeesus.. I must have been blind when I hit their site. Thanks again!
>I do like Postgress, but it is not as easy to use as
>Firebird. It also needs a first rate admin tool that is not
>done in VB.
Do you know pg_access i think they stopped development for it
but its still a nice tool. Quickly link tables add/delete and make forms and queries.
look also at:
http://www.jetools.com/products/databrowser/screenshots
http://ems-hitech.com/pgmanager/
>Robert: MySQL used to be faster than PostgreSQL across the >board, but we’ve erased that difference and now it’s only >faster in certain corner cases at lower load levels.
Does anyone have any links to some benchmarks that demonstrate this? Not that I don’t beleive Robert, but I would like some hard numbers to look at.
http://www.webtechniques.com/archives/2001/09/jepson/
http://www.osnews.com/story.php?news_id=735
And there was another one too, under freebsd, showing postgresql to hold better/faster than mysql under heavy load, while on cases without load, mysql was faster. Can’t find that url atm though.
This might be of interest as well: http://osdb.sourceforge.net/
Never used postgresql, but I was wondering if it can replicate to/and from MS SQL??? We recently got that working on mySQL 4, but it was neither easy nor straightforward.
besides postgres, with regards to db’s that run on win32, sapdb has a win32 port. although i never tried it on windows yet.
never tried firebird to, but that is a good alternative as well.
sapdb, firebird are good enterprise class db’s, but firebird is still beta.
It can as of 7.3.
Slightly different syntax, very similar in regards to design.
They can be written in plpgsql, sql and C as far as I know (possibly perl — but I’m unsure).
Firebird 1.5 is now at Release Canidate 1.
Firebird 1.02 is full production release NOT BETA
Rod,
do you have a simple example of how to do a stored proc that returns a result set and can be used from the client like a table?
I have been browsing the 7.3 docs but can’t find anything.
Also do you know of a good newsgroup to use for Postgres questions?
Old article, but some of the stuff there might still be in effect:
http://www.phpbuilder.com/columns/tim20001112.php3?page=1
They say they are working on easier administration for simple databases. Creating users and databases is already much simpler and clearer than in MySQL. What do they mean by ‘simpler’?
That article was flawed when it came out. MySQL published a refute with revised benchmarks shortly after that “article” was “published.” But Tim Purdue refused to answer their questions and refused to revise the article. Don’t get me wrong, I love Postgres, but I will never point someone to that article in support of it simply because of the fact that it’s complete and utter trash.
Glad to see the upcoming win32 port. Win32 is a huge (~90%) market – desktop and server. People just not interested in using cygwin version.
In addition, better get a better admin gui. These two factors will be the major push for postgresql.
I’ve been extremely pleased with the performance recently, coupled with the power of NT, im really impressed
Until Postgres hits a native Win32 port it doesn’t even have a chance of competing. Until then it is MySQL and Firbird. Firbird is probably the way I will go.
So, does PostgreSQL have embedded DB support? Anyone know of any OSDB that has (other than MySQL)?
Well, our servers run on freebsd & redhat, but our development machines are windows(most client apps we write are pure mfc code).
Well, I’ve gotten the cygwin version of postgres to work but it’s much too painful and is different on different versions of windows(xp vs 2000). So that’s not recommended. Can’t wait till they come out with a proper win32 port. However it’s really cool to have the same database & code run on cygwin/freebsd/linux.
Postgres is also higher perfomance for our database needs than MySQL. We also rely on stored procs a lot, so mysql isn’t even a candidate. Another nice thing about postgres is that it cares about your data. It bitches and curses at your if you enter data incorrectly. Mysql on the other hand will happily substitute your input with something else if it doesn’t understand.
Postgres is also bsd licensed which means it’s much cooler to work with for commercial people. Unfortunately we don’t do any postgres coding, we just use it.
pgaccess is a cool tool, but it’s buggy and all ofthe other postgres tools are rather crappy too, there was a commercial trial app that I used for a while. It was actually pretty quality, but the name escapes me.
I used to use pgexplorer ( http://www.pgexplorer.com ) but found that pgAdminII works GREAT with 7.3. The one thing as a former mysql user that I miss is the mysqladmin interface for managing the processes, show processlist, etc.
You can embed Firebird with you softwares install and it only adds about 3mb.
For people that need an easy to use GUI Admin tool, take a look at RHDB-Admin from RedHat. http://sources.redhat.com/rhdb/tools.html
I’ve found Postgres’ performance to be more than adequate (v 7.2.2 on FreeBSD). I did some stress testing with it and Jetty, it handled about 600,000 page hits in just over a day with the Jetty java process taking up most of the CPU time. This on a lowly single processor 450Mhz p3, 256 megs of ram, and 7200 EIDE HD.
After 10 minutes or so into the test, PostgreSQL’s CPU usage starting increasing and increasing and there were more pgsqls active (I used connection pooling). I did an “analyze (tablename)” and after about 3 seconds cpu usage for postgresql dropped considerably. On a second note, using the JIT-enabled JDK1.4 for Jetty had HUGE HUGE HUGE performance gains compared to the non-JIT 1.3, so if you are planning on running a JSP/Servlet-based server I highly recommend using 1.4+the server VM.
Check out SQLlite:
http://www.sqlite.org/
It’s a lightweight C library that’s designed to be mostly SQL syntax compatible with PostgreSQL, supports transactions, and has no license restrictions.
there is a even smaller embedded firebird database (700k)
http://prdownloads.sourceforge.net/firebird/Firebird-1.5.0.3253_RC1…
And you don’t need to install it . Ask on the lists about it ..
And now back to the article about postgresql:)
I like the fact you can write prcedures in 7 languages
(it’s cool
) We wish to have that in FirebirdSQL …and
an ebedded java vm (or .net !!??) to write the sp .
>>Now, I do expect that object and OODBMSs will refine >>themselves and develop an open standard within the next >>decade, so they can become commercially viable as an >>alternative approach to data storage
AND my god OODBMS are not good for storing DATA ,Yes they
are good for Caching objects but that’s it …
Read more here http://www.dbazine.com/pascal3.html
and here http://www.pgro.uk7.net/content_pre_2003.htm
Here is a good guide:
http://techdocs.postgresql.org/guides/SetReturningFunctions
Chris
Regarding the requests for more PostgreSQL support:
There is a weekly column PostgreSQL General Bits
at http://www.varlena.com/GeneralBits/ which
summarizes some of the threads from the pgsql-general
mailing list.
All of the mailing lists are active and full of helpful (and
polite!) people. The front door to the mailing lists is
http://archives.postgresql.org/
The http://www.postgresql.org site is a treasure trove for how to’s.
One of my favorite features of PostgreSQL is the RULE system, which I think takes a little explaining for those not familiar. Think of it this way:
The rule system is to SQL queries as Apache’s mod_rewrite is to HTTP requests.
This means that for any table or view in your system, you can make your query do other (or additional) stuff internally.
For example, every time someone does SELECT * FROM accounts, you might want to log that activity, so you define a rule that also does “INSERT into logtable VALUES(CURRENT_USER, CURRENT_DATE, ‘user selected from accounts’);”. This happens without affecting the user query in any way.
Or, for example, upon delete in a certain table, you could use a rule to insert the deleted row into a backup table. Rules can be used to make a view updateable in _any_ way you might want, including any number of base tables.
Now, you might argue “this is just a trigger in disguise”. There is a lot of overlap between what a trigger can handle and what a rule can handle, although AFAIK only a couple DBMSs allow you to create an ON SELECT trigger. The thing is, it is conceptually more elegant and simple to use rules, while triggers+procedures allow you to execute more arbitrarily complex operations. So I would argue that RULES are a better solution to many of the “business rules” that you would want to implement, while triggers and procedures can be used to handle things that are difficult with regular SQL.
So this leaves me with a question for the PostgreSQL team: in general, do rules take up less resources than triggers? As I understand it, rules make up a large part of PostgreSQL internal logic anyway, so it would seem to make sense that extensive use of rules might perform better than extensive use of triggers and procedures. Any comments, guys?
I got this when i was reading this article
Warning: Too many connections in /home/osnews/web/connect.php on line 2
Warning: MySQL Connection Failed: Too many connections in /home/osnews/web/connect.php on line 2
Could not connect to the Database
I suggest the “PostgreSQL Essential Reference” and the “Postgres: Developers Handbook”. For a complete list, see the following URL:
http://www.amazon.com/exec/obidos/search-handle-url/index=books&fie…
I think that buying very well-written books is a must, if you are serious about database programming. Personally, I haven’t had time to delve into my database programming project yet but the books that I mention, above, seemed to have been the best at the time that I purchased them.
I did two things to be sure of my purchase. I went to Barnes & Nobles and found several Postsgres books, two of which are the ones that I mentioned. I found the ones that I liked and wrote down the ISBN number and the price for all of them.
Then, I went home and went to the Amazon website and checked the reviews and cost. The reviews and my own recollection of the quality of the books, in combination with the cost information, helped me to settle on just two books, which are promptly purchased from Amazon because the cost was about 1/3 less than B&L, even when I included shipping from Amazon.
The reference book is for details about all of the PostgreSQL commands, etc. The Postgres: Developer’s Handbook is about using PostsgreSQL from a developer’s perspective — how to create database, writing queries, creating user types, transactions — everything Postgres. The two books give the reader a very good understanding of Postgres in general.
I have a third book, too, that I purchased a long time ago — the SQL 92 Handbook. It talks about the SQL standard. With these 3 books, I have everything that I need to do database programming with PostgreSQL.
I hope that this helps!
What I would love to see would be the ability to use Sybase/ MSSQL server Transact SQL or Oracle PL/SQL on PostGres ( others too).
Obviously they would be lower performance ( due to different performance characteristics and assumptions) but I don’t think there are any major disconnects in functionality…
Would this be easy or even possible?
There are a lot of existing systems out there, it would be good to make it as easy as possible to migrate.
>I got this when i was reading this article
Yeah, mySQL for some strange reason started taking a lot of CPU time this morning, and our admin is on vacations… We are waiting for him to restart the db…
This is crazy, the PostgreSQL ppl actually believe that their competition is commercial databases? When PostgreSQL’s WAL work is done and stabilized, and they have an equivlant operational mode to Oracle’s archivelog mode then they have taken the first step. This isn’t exactly new stuff for Oracle, IIRC arcivelog was introduced in Oracle V6 ~ 1988. After you get rolling forward your database down (with the tools to parse your logs too, so you know when to stop rolling forward the db) you have to address hot backups. Let’s face it the commercial guys are doing incremental hot backups with tools that ship with the DB for years, and vanilla hot backups have been around for a decade now. This makes PostgreSQL a non-starter for anyone with any valuable data. Transactions are not enough; you also have to have a coherent backup and recovery strategy to ensure your data’s integrity.
And hello from a fellow Sandgroper. Would you like to give a postgresql talk to SLPWA and/or PLUG sometime? http://slpwa.asn.au/ http://plug.linux.org.au/ (the latter is an XboX in O’Connor). Email me via the above website if interested.
how about Unicode support and internal fulltex search capabilities.. ?
This is crazy, the PostgreSQL ppl actually believe that their competition is commercial databases? When PostgreSQL’s WAL work is done and stabilized,
It is done. Has been for well over a year.
and they have an equivlant operational mode to Oracle’s archivelog mode then they have taken the first step.
There are working development models of this, it is called Point in Time recovery.
After you get rolling forward your database down (with the tools to parse your logs too, so you know when to stop rolling forward the db) you have to address hot backups.
pg_dump does hot backups, and quite nicely.
Let’s face it the commercial guys are doing incremental hot backups with tools that ship with the DB for years, and incremental backups will be handled by the PITR system.
and vanilla hot backups have been around for a decade now.
And Postgresql has had them about that long too.
This makes PostgreSQL a non-starter for anyone with any valuable data. No, it makes it a non-starter for people who can’t be bothered to implelent replication and a good backup schedule. Only a tiny percentage of all the biggest databases need the kind of PITR recovery you talk about, most get along fine with normal nightly backups and replication. In fact, about 99% of our Oracle instances at work are strictly backed up at night. Postgresql isn’t good enough for those 99%? I think it is.
Transactions are not enough; you also have to have a coherent backup and recovery strategy to ensure your data’s integrity.
Hey, what do ya know, we agree on something! 🙂
Postgresql has had unicode support for quite some time now. It’s very very popular in Japan, more so than any other OS database, mainly because of it’s early support for locales and multi-byte encoding. Prior to 7.2 you had to explicitly compile multi-byte in, but now it’s just there.
Full text indexing is still handled by an external package. There are actually two, tsearch and fts, and they each have their strengths and weaknesses.
Keep in mind, the fact that the full text searching is an “add on” is actually a good sign. It means you have a database that can be extended by users into spaces the main developers may not have time to go into, and tested and retested without waiting for it to get “integrated” into the core. It’s easy as heck to install either full text engine, and they’re quite fast.
Well is pg_dump atomic AND non-blocking (alter tablespace <whatever> begin backup; is atomic and non-blocking)? If it isn’t it’s worthless as a true hot backup solution. I really don’t like the idea of a global export tool being used as THE backup solution. You have to admit that using a sql export tool as the backup strategy is so 1980’s.
Now would you run oracle without archiving the redo logs and using exp as your only backup solution? I sure wouldn’t!
I would guess that 100% of your production oracle instances are in archivelog mode, but there are shops out there that do very dumb things every day. If not what do you do if someone makes an ad-hoc update (or more than one) with invalid data? Scrap all the transactions that have been made since your last backup? Now that sounds like a really progressive backup and recovery strategy
You know I was going to say that you could just mine the log and find the sql statement and see if you can write a sql statement that could fix the bad update(s), but if your not in archivelog mode it’s very possible that oracle overwrote the log that contained said sql statement so we are back to loosing all transactions sense your last backup. Nightly backups (hot or cold) are fine if you’re in archivelog mode. Just a little FYI, media failure is NOT the most common reason for a recovery.
Seriously, why would you pay for an oracle license if your data wasn’t important enough to run in archivelog mode? IMO you are using the wrong tool if this is acceptable.
BTW when I said incremental backups I wasn’t thinking about a PITR I was thinking of how RMAN does incremental and cumulative backups. Yea and if anybody does make a RMAN work-a-like for postgresql for the love of god don’t copy the user interface!
Also I know what the WAL stuff has been in PosgreSQL since 7.1 but it’s incomplete IMO because this work can not be leveraged to do a PITR at this time.
pg_dump is non-blocking, and provides a consistent view. Look it up, it’s quite good. 1980s tech or not, it’s a good backup tool, and it works, which is more than I can say for many of the commercial backup tools I’ve had to try and make work on other databases.
I know Oracle is the wrong tool for the job around here, and I’m trying to get them to use Postgresql where it makes the most sense, on large indexing systems. Almost all of what Oracle does where I work is indexing of large datasets, and the worse that could happen if the system crashed and burned would be a couple of days of indexing lost.
Funny thing is, if you go around the world, you’ll find Oracle doing this kind of brain dead stuff at high prices for the sole reason that it’s the tools the developers already know, not that it’s a good fit for the job.
My Point on PITR in Postgresql was that it will be BOTH a PITR system, as well as provide the incremental backups, since the data stream for both would be the same, just handled differently.