Anyone doing PostGreSQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Anyone doing PostGreSQL?

Post by Heavy »

With reference to this topic:

viewtopic.php?p=48025#48025

Has anyone experience from using PostGreSQL instead of mySQL?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

Not sure what you mean by referng to that thread? Need transactions?

There are no problems using pgSQL with PHP, your biggest problem will be in finding web hosts that has it, many do, but most use mysql 3.23 still...

If you do need transactions and other business functionality, pgSQL is very usable, just keep in mind it is not the same as MySQL.. Some books refer to it as MySQL is different from all the others, e.g. migrating from a commercial DB like Oracle to pgSQL is much easier than from MySQL.. Things like the Autoincrement columns are different (Sequences) and some stuff more strict in use of quotes and no-quotes depending on data type..
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Stoker wrote:Not sure what you mean by referng to that thread? Need transactions?
I use InnoDB because I need the rollback capability.
Stoker wrote:There are no problems using pgSQL with PHP, your biggest problem will be in finding web hosts that has it, many do, but most use mysql 3.23 still...
We host our own servers.
Stoker wrote:it is not the same as MySQL.. Some books refer to it as MySQL is different from all the others
Yes. That's part why I am interested. Since MySQL is so different, it might ne good to stick with pgSQL if you want less trouble when migrating between systems.
Stoker wrote:Things like the Autoincrement columns are different (Sequences) and some stuff more strict in use of quotes and no-quotes depending on data type..
Yes. This is the kind of response I wanted from my post.

I have never tested pgSQL myself, so I am asking the world instead.
Give me more Pros and Cons! :)
Last edited by Heavy on Tue Jul 08, 2003 12:26 pm, edited 1 time in total.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

The only cons I would say is that if you have used MySQL specific stuff, some of the SQL, and sometimes your logic, needs to be rewritten a bit.. pgsql is a lot more like the commercial rdbms'.. I would recommend getting a book on the subject of PostgreSQL or even perhaps one involving both.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I've used PostGreSQL almost exclusively (with minor integration with some existing MySQL databases). I've always been happy with it. Features that I've been relying on for over two years are beginning to appear now in MySQL, while PostGreSQL pushes forward.

The three cons I can think of:
1. Relearning the way you think about DB Design and Query Construction.. MySQL is not considered by many people to be a "real" SQL DBMS and so you will have significant relearning to do. (Often its learning to take advantage of features that you didn't know exist.)

2. Spending some time on the pgsql-preformance list. The default install of PostGreSQL is very conservative (a toy system in their words) to ensure that it will compile and build on a wide variety of old nd new hardware. Most modern hardware can support almost an order of magnitude increases in some of the settings which will greatly improve preformence. The people on the list are normally able to "tune" your parameters, schema, and queries so that they are faster than MySQL, but if you don't tune you will be much slower....

3. Case folding: PostGreSQL folds to everything to lower-case for legacy reasons, instead of the standard-require uppercase. All this really means is that you should not use mixed case names for tables,columns, databases, etc. PostGreSQL will correctly handle it and it will be portable. If you use mixed case or uppercase only for identifiers you'll need to always quote them in your PostGreSQL queries.

Pros
1. Faster (see point 2 above), More Robust, More grouned in SQL standards

2. Stronger integrity support (not just foreign keys)

3. Triggers and rule system allows for moving database functionality into the database and out of application code. These tools could/should be used to move your spread upward/downward code into the database where it belongs and out of the application.

4. Triggers and rules, combined with views allows you to create updateable views.

Note that most of the Pros (and overcoming the Cons) will require you to spend more time working as a DBA than you currently do. The trade off is that is should require less time as an application developer. Of course if you already are an accomplished PHP programmer, learning to use the full suite of DB tools may seem slow, but in the long run it should pay off. If you have multiple people working on the project, you'll quickly see what spitting off a person as a full-time (or primary) DBA is common.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

nielsene wrote:3. Triggers and rule system allows for moving database functionality into the database and out of application code. These tools could/should be used to move your spread upward/downward code into the database where it belongs and out of the application.
Fully agreed.
I have spent some time with MS SQL Server 2000 and I think that this spread thing and many other things I do in our system today with PHP & MySQL, is just a matter of stored procedures, as far as my own experience with M$ goes.
I don't know anything about Views and such things, so I guess I'd have to read a lot to get things done a better way.

Thanks.

I'd be very happy to recieve more opinions on this subject.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Triggers are what PostGreSQL calls a "strored procedure". Rules are what's used to let it know when to "fire" a trigger and if the firing is in place of the triggering action or in addition to. I'm not sure what other DB's call these two concepts, but that's the PostGreSQL terms are if that helps you understand these terms with your background from MS SQL Server.
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Do you have any experience from porting for example MySQL based PHP applications to pgSQL?

...or for that matter, porting MS SQL Server based applications to pgSQL. It says on the pgSQL homepage that it is pretty compatible and thus easy to port to.

The link refers to converting data. What about triggers and such. Does one have to rewrite such things completely?
Post Reply