Fog Creek Software
Discussion Board




PostgresSQL database, whats your take?

I am currently looking into the possibility of replacing a mid-sized Oracle DB with PostgresSQL,  mid sized meaning a few tables are big (~2 million records), and some tables are considerably smaller (~200K records). The rest of the tables are small.

The userbase of this application is about 150 concurrent users. Is it even worth testing? :) Before Im testing this, I thought I'd ask here if anybody has experience with PostgresSQL doing this size of database.

Oppinions on stability, performance and experiences are most welcome.

Thanks.

Patrik
Monday, March 03, 2003

Just a warning, you might want to buy some
commercial support for PostgreSQL while you
learn the database. Because you might or
might not get timely responses from
dicussion lists (great resource). The books
on the market at the moment are a little
thin on the hard core stuff (and that's an
understatement).

But go for it... :D

Li-fan Chen
Monday, March 03, 2003

Have you considered MySQL? Although PostgresSQL has a richer, more standards-compliant model, MySQL beats it hands down in terms of popularity and, thus, support opportunities. Replication services in MySQL are blindingly simple whereas in Postgres they tend to be complicated.

Having said all of that, I had to keep using Microsoft SQL Server for my business because, frankly, MySQL didn't perform as well on identical hardware and Postgres was too complicated to manage. Our database is around 74GB. Not the largest, but certainly not tiny by any rational measurement.

In terms of management and robustness, if you can, stay with Oracle or Microsoft's SQL Server. There *is* a reason these products are expensive. They're VERY good at what they do.

David Geller
Monday, March 03, 2003

>Have you considered MySQL

Yes. Transaction support in MySQL is according to MySQL themselves "not as well tested" as PostgreSQL. Which is saying it does probably not work all of the time.

Also, PostgreSQL has a fairly Oracle like implementation of a plugin language for its stored procedures, called pg/plsql which has a close resemblence to PL/SQL to help simplify
porting the codebase.

Patrik
Monday, March 03, 2003

If you're evaluating Oracle replacements, you should also take a look at SAP-DB: http://www.sapdb.org/

It's a great replacement for Oracle, and can even run in an Oracle 7 compatibility mode.  The management tools are very professional, and the developer environment is very slick as well.  Works great on both Linux and Windows, and the JDBC driver seems very stable as well.

Features:
- Open Source (GPL/LGPL)
- All the RDBMS treats: Transactions, Stored procedures, SQL 92 compliance, row level locking, online backup, etc)
- JDBC, Perl DBI, PHP, ODBC, Python interfaces.

I'm not sure why it lacks the mindshare of other open source databases -- it's a very professional product.  Download it and check it out, the binary release is very easy to set up and get running.

Darren
Monday, March 03, 2003

PostgreSQL is about 30 % slower on average. A little slower on JOINs.

In case you decide to test PostgreSQL out, make sure you ask for help in specialized forums. There's a lot of difference between the standard configuration and optimized configurations, especially for such large databases.

TJ Haeser
Monday, March 03, 2003

I've run a prototype for a production system on Postgresql, and then deployed on Oracle.  The only reason it was depoyed on Oracle was, incidentally, a support issue for the client.

My observations: I like writing SQL in Postgres better than Oracle.  It's more standards compliant in a number of areas (date handling, preferring CASE to DECODE and COALESCE to NVL).

The DB itself consisted of a few tables, two with over 20 million rows (and one of those with a TEXT type - equiv to an Oracle CLOB), and a bunch more smaller ones.  Joins were being run between the various tables, and, when properly indexed, performance was very acceptable, albeit with a small number of concurrent users (but then, it was on very light hardware - a low end Sun Blade).

I do use Postgres in a number of other, smaller projects.  Probably the most consistent niggle is getting indicies to behave as I'd expect, since the optimiser's decision as to whether or not to use them are sometimes counter-intuitive.

In terms of management, it's all very easy, and Red Hat have been releasing various GUI tools for DBAs as part of their "Red Hat Database" product (Postgres in drag).  Something the would-be Postgres DBA may find a little frustrating compared to Oracle is the comparatively limited controll over where tables, indicies, and the like, are physically placed on disk, although this has improved in 7.3 and will doubtless improve in 7.4.

For porting pl/sql to pg/plsql, you should probably talk to the OpenACS folks ( www.openacs.org ), who have done a huge amount of work in this area.

As far as support: I recently made a stupid mistake with some SQL I couldn't get working.  I asked about it on the appropriate mailing list, and one of the core developers provided me with an answer.  This is fairly common, and not something you'll get with most commercial databases.  If you want pay for support, there are various organisations doing this - either Postgres specialists, or outfits like Red Hat.

Rodger Donaldson
Monday, March 03, 2003

Oh, one more thing; reliability: I've never had Postgres itself cause me any grief, and it's recovered from mid-transaction power loss with no DB corruption and no data loss.

Rodger Donaldson
Monday, March 03, 2003

Postgres is a great database compared to MySQL, it supports unions, views, stored procedures, you can put bits of perl into views, you can make your own functions, has transactions, subselects.. and so on.

In a nutshell it's light years ahead of mysql, and it's properly open source, whereas mysql is not free for commercial distribution.

The reason mysql is more popular I think is due to
a) it being already installed on a lot of ISPs hosting packages
b) a newbie database programmer will probably find mysql easier to install and not require all of the above features

Matthew Lock
Monday, March 03, 2003

Matthew, you forgot #3): Since it works like some sort of SQL interface to the file system, MySQL is lightning fast, which is great for Web stuff. Certainly NOT recommended to store critical data.

TJ Haeser
Tuesday, March 04, 2003

Sorry PostgreSQL, close but no cigar. PostgreSQL looked good until I found out that the current implementation lacks Oracle style packages for stored procedures. Packaging stored procedures means you can on a per session basis have global data in the package that can be shared among many stored procedures declared in that package.

Patrik
Tuesday, March 04, 2003

Patrik: "per session basis have global data
in the package "
If performance is not an issue I don't see
why the session can't share a table that
keeps these values.. it would come with
transaction protection for free.
If a postgresql geek must, he can always
create what we sql server geeks call an
extended stored procedure or oa_sp call.
Just write a c based stored procedure, have
it create the necessary shared memory data
structures, and keep the running reference
to that data structure somewhere in the
running server. You have to write concurrent
software and catch all the possible
exceptions in c so I don't recommend it.
It's not pretty, but what do you expect for
free.
It really depends on the problem I think.

If this feature is that crucial, I won't be
surprised if you can get the budget to get
it outsourced to some C/PostgreSQL geek.

Patrik, what else do you really need from
PostgreSQL but can't find? I am surprised
you only ran into one problem...
-- David

Li-fan Chen
Tuesday, March 04, 2003

David, this application has alot, and I mean alot of stored procedures. I have systems here with man years worth of stored procedure code. Unless packages become available its simply unmanageable to have thousands of stored procedures scattered around. I didnt even get to performance testing.

Even if this system (in the first post), given the fairly many users, would have run into performance problems, I dont know. But apart from lack of Oracle-type packages I found nothing that stops if not this, so smaller systems from Oracle to Postgres.

My evaluation ofcourse found differences between the two,  but nothing that can not be worked around. It the usual datatype/SQL syntax related stuff. Not a big deal.

>It's not pretty, but what do you expect for free.

Nothing really. Because I get what I pay for. Let me tell you, I may use Postgres for smaller systems, becuase I like the database, what I am saying is that is not yet up to par with the code-storing possibilities. But I will watch this closely, and I'll be the first to undertake a porting of Oracle code if packaging becomes available. Im not throwing Postgres away.

Patrik
Tuesday, March 04, 2003

I must be running a fever or something...

But apart from lack of Oracle-type packages I found nothing that stops if not this, so smaller systems from Oracle to Postgres.

should read

But apart from lack of Oracle-type packages I found nothing that stops porting if not this system, so smaller systems from Oracle to Postgres.

Sorry about the confusion.

Patrik
Tuesday, March 04, 2003

*  Recent Topics

*  Fog Creek Home