Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Location
    .nz
    Posts
    9

    Smile Unanswered: Should i be using PostgreSQL?

    Hi,

    I made a small site for a family friend basically to showcase his company's ceramic product ranges using PHP and mySQL.

    Currently 7 ranges & around 90 products total. Each product has various sizes and colors available. Database:


    range
    range_id
    range_name

    product
    product_id
    range_id
    product_name
    product_code
    product_image
    color_shown

    sizes
    product_id
    size_available

    colors
    product_id
    color_available


    The guts of the site is just a dynamic gallery which selects the product images and detail. The gallery has the typical 'next/previous' type function (6 per page).

    I'm not that happy with the speed of the site and have just found out that the host supports postgreSQL. I was wondering if you think i would have anything to gain by using it? I see it supports stored procedures which would in effect increase performance? also perhaps i would have more options regarding a more effeicient way to accomplish the 'next/previous' instead of using sql LIMIT function. more options for database paging? anyway i'd like your opinions?



  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    I would definitely recommend it. I've been using it for about a year, and now it is the only backend database I use on the sites I program.

  3. #3
    Join Date
    Dec 2002
    Posts
    65
    I highly recommend it as well. I used to use MySQL briefly before doing PostGres mostly because most freeware apps use it, however after switching to PostGres (which I do now for a living) I only use MySQL for small contracting jobs for people that want to go with cheaper hosting services that only have MySQL.

    These are my big things regarding PostGres over MySQL:
    foreign keys, subselects, unions. Those were the big things that saved me a few headaches when I first got into PostGres. I haven't really needed transactions too often (although MySQL now supports these) and I haven't gotten too much into stored procedures since I haven't needed them, but I have been learning a little pl/pgsql and it seems pretty handy.

    Like I said, I work with PostGres so I may be biased. BTW who is doing your hosting, most of the PG hosts I've seen are a bit pricier than the cheap hosting services than the free MySQL hosts.

    If all you're looking for is speed, and your queries aren't too complicated then you might not need postgres. A lot of people believe MySQL to be faster in smaller, less complicated queries.

    -b
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

  4. #4
    Join Date
    Jan 2003
    Location
    .nz
    Posts
    9
    I think I might try it on my machine and a seperate test version on the host or something although i agree that I most likely wouldn't be taking advantage of what is has to offer for this project. Host is webdrive.co.nz (new zealand host / pricing) & yes It is a bit more expensive than other hosts here.

    I don't think you would consider the SQL very complicated, a few selects with tables joins and this is about as complicated as it goes :

    Code:
    $sql = "SELECT p.potID, p.potName, p.image
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND c.collectionName = '$c' LIMIT $offset,$perPage";

    So using stored procedures for things like these is a no-no? I guess there would be no advantage noticeable in terms of performance through using a stored procedure with a query like this anyway.



    Thanks,

  5. #5
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    stored procedures

    No there is no need of stored procedures with this


    be aware of the following

    many data -> application does some operations on the data and returns it to the web

    if you use stored procedures in this way you don't have to get the data over TCPIP

    so the traffic is lower with stored procedures

    this is only one example
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  6. #6
    Join Date
    Dec 2002
    Posts
    65
    Here's a pretty good article for those interested in pl/pgsql for stored procedures. He goes into detail why (in this instance) it's good to store the logic within the DB vs the scripts accessing the DB and has code to look at as well.

    http://www.databasejournal.com/featu...le.php/1568461

    Quick update, it appears that an alpha of MySQL 4.1 was released today and it supports subqueries. http://www.mysql.com/press/release_2003_05.html

    -b
    Last edited by bcyde; 01-21-03 at 11:48.
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

  7. #7
    Join Date
    Dec 2002
    Posts
    65
    I'm not sure if this is off topic for this thread, but with the recent release of MySQL 4.1 which supports subselects and seeing its future roadmap I was wondering down the road what others will feel the distinguishing features will be between Postgres and MySQL say in a year besides syntax?

    Currently, I'd say newcomers come to Postgres because of its closer compliance to SQL standard, stability and its larger functionality base, however I am just wondering as MySQL gains some of the more prominent features that Postgres has over it what will draw newer users to Postgres over MySQL? Since MySQL is much more prevalent than Postgres meaning:
    -A lot easier to find hosting for MySQL from mainstream ISPs
    -More open source projects for MySQL
    -Larger support community (although I must say that the postgres community is very quick - Tom Lane, Bruce Momjian and the rest of the Postgres community are very helpful and do a great job)
    -More jobs centered around it

    Regardless of whatever becomes the database of choice, I believe the competitive spirit driving these two projects has been very beneficial to the development of both.

    -b
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

  8. #8
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Databases

    Mysql is more popular no doubt.

    But in my experioence if someone used mysql and goes to postgresql. They never change back.

    the problem of postgres was for ISPS the database had no good definition of user rights.

    ANd metnioning the jobs. Of course mysql has more job opportunities.

    Its the database for the web designer and so on.
    You must not know something special about this thing.

    If you want to use postgresql extensivelay you have to dig into it a little bit.

    That's why most of the comanys use postgres they hire a specialised postgres company and do not search an employee. this is much better for them.

    And my opinion is about mysql: This is no database. This is a web backend for me.
    But thats only my opinion. I'm not trying to convert someone to this opinion
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  9. #9
    Join Date
    Dec 2002
    Posts
    65
    Exactly, I'm not trying to convert anyone either, I fall exactly into the stereotype you mentioned. I have gone from MySQL and now to Postgres and have no intention of going back unless something drastic happens. Lucky for me I found a company looking for a Postgres person pretty soon after I first picked it up.
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

  10. #10
    Join Date
    Jan 2003
    Posts
    1

    Re: Should i be using PostgreSQL?

    quadrant6 looking at what your site is doing i think PostgreSQL would be total overkill. look into you host that's probably the reason for the poor performance...just my opinion here but you don't need the things PostgreSQL can do that MySQL can't (stored procedures, triggers, etc)


    Originally posted by quadrant6
    Hi,

    I made a small site for a family friend basically to showcase his company's ceramic product ranges using PHP and mySQL.

    Currently 7 ranges & around 90 products total. Each product has various sizes and colors available. Database:


    range
    range_id
    range_name

    product
    product_id
    range_id
    product_name
    product_code
    product_image
    color_shown

    sizes
    product_id
    size_available

    colors
    product_id
    color_available


    The guts of the site is just a dynamic gallery which selects the product images and detail. The gallery has the typical 'next/previous' type function (6 per page).

    I'm not that happy with the speed of the site and have just found out that the host supports postgreSQL. I was wondering if you think i would have anything to gain by using it? I see it supports stored procedures which would in effect increase performance? also perhaps i would have more options regarding a more effeicient way to accomplish the 'next/previous' instead of using sql LIMIT function. more options for database paging? anyway i'd like your opinions?



  11. #11
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Originally posted by bcyde
    Here's a pretty good article for those interested in pl/pgsql for stored procedures. He goes into detail why (in this instance) it's good to store the logic within the DB vs the scripts accessing the DB and has code to look at as well.

    http://www.databasejournal.com/featu...le.php/1568461
    I've been looking for articles on PostGreSQL stored procedures, and haven't been able to find much. The article referenced above talks about triggers, not stored procedures. I can find information about functions, too.

    Can anyone recommend a good article or book on the topic of stored procedures? When people refer to PostGreSQL stored procedures, are they really talking about PL/pgSQL functions?
    Bradley

  12. #12
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    Originally posted by bcyde
    I'm not sure if this is off topic for this thread, but with the recent release of MySQL 4.1 which supports subselects and seeing its future roadmap I was wondering down the road what others will feel the distinguishing features will be between Postgres and MySQL say in a year besides syntax?
    I think its going to be awhile (more than a couple years) before MySQL even comes close to supporting the relational and logical features that PostgreSQL has today, not to mention the features PostgreSQL will have tomorrow.

    Also, aside from its lesser features set, MySQL has some... er... really bad habits with your data, IMHO. For example, if you have a column defined as SMALLINT (up to 32767). If for some reason you try to insert a larger number such as 250000 into that column, MySQL will happily allow you to think you have done so, but will truncate the stored value to the max for that type, which is 32767. Wow.... hope nobody is using this for accounting software . Also, there is apparently some talk of INTs being stored internally as floats, and other such oddities.

    Other such things as CHAR not padding for spaces, etc... all speak to a rather arbitrary approach to many areas which are considered ironclad by other DBMS systems.

    What this all essentially boils down to is a serious question of software design philosophy: is a DBMS supposed to handle data integrity, or is the application responsible?

    I suppose I'm somewhat of an unfashionable developer because I actually like the idea of handling as much logic and integrity as possible in the database. And, I think the majority of developers out there haven't the faintest idea what a true DBMS is really capable of. And, even the best SQL systems are still a far cry from the possibilities of they truly implemented a relational approach to data management. (That's another discussion, but trust me... read C.J. Date and you will be surprised at what you thought was possible with data management.)

    Anyway, the list of real feature differences between MySQL and PostgreSQL stretches far further than simply transactions and foreign key constraints. In fact, a truly relational approach to data management takes the philosophy that you should be able to express abitrary declarative constraints over any and all parts of your database. In the absence of that, we have at least a somewhat decent work-around with triggers, views and domains. (Yes, the domain concept is an area with astounding benefits, which are mostly ignored by developers.)

    The true list of features you get with PostgreSQL that are still nowhere close to being available in MySQL is: (listed in what I consider order of importance)

    - Views
    - Domains (datatype aliases with user-defined constraints)
    - Triggers and RULEs
    - Stored procedures available in several languages
    - CHECK constraints for domains, tables and columns
    - User-defined types
    - User-defined operators
    (really, domains should be capable of complete freedom to handle everything done in the last 2 items, but modern SQL domain implementation is pretty basic, so if you want to get a little "closer to the metal", you can define datatypes of arbitrary complexity in C or other procedural languages)

    This feature set is pretty similar to what you would get with any of the more advanced DBMSs out there, such as Oracle, DB2, etc... although I think PostgreSQL has some of the best implementations of these concepts.

    And yes, PostgreSQL has subselects, like just about every other SQL system. IMHO, subselects are somewhat of a crutch for developers who didn't really learn how to write queries (and a crutch for a DBMS that doesn't support views...). Yes, I use subselects at times, usually for small one-time data movement tasks, but I tend to avoid making them a central part of an application, because there are usually better ways. Subselects are hardly the major feature that the MySQL marketing team seem to be touting.

    Now, once you deal with all the above, you realize that the only true attraction MySQL has is a claim to high performance (and easy setup, I suppose). Honestly, I don't know why developers get so hung up on DB performance, because the differences are usually insignificant, and I would gladly take bulletproof data integrity over speed any day. But, in my own (completely unscientific) tests, I have noticed that usually MySQL's performance is only marginally better when the queries are simple. Once you get into complex querying/updating/reporting, PostgreSQL powers past MySQL and many other competitors.

    ----------

    Note for bcrockett: there are plenty of articles on stored procedures in PostgreSQL. Triggers in PostgreSQL cannot be declared without them. IN PostgreSQL circles, they tend to use the more informal term "functions" when discussing procedures. Here are some links:

    http://www.postgresql.org/docs/7.3/i...er-server.html
    http://www.varlena.com/GeneralBits/
    http://techdocs.postgresql.org/

    Also, search the [SQL] section of the mail list archives at http://archives.postgresl.org/ and consider subscribing to the pgsql-sql list, because there are some great discussions in there.

    Another note: PostgreSQL is unusual in that it created a general interface for procedural languages, allowing for many languages to be turned into modules for PostgreSQL procedures. Here is the current list:

    1. SQL
    2. Pl/PgSQL
    3. PL/R -- for heavy-duty statistical computing (still in development)
    4. C -- when you really need nuts-n-bolts
    5. Java (still in development)
    6. Perl
    7. Python
    8. PHP (still in development)
    9. TCL
    Last edited by rycamor; 08-06-03 at 00:06.

  13. #13
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Note for bcrockett: there are plenty of articles on stored procedures in PostgreSQL. Triggers in PostgreSQL cannot be declared without them. IN PostgreSQL circles, they tend to use the more informal term "functions" when discussing procedures. Here are some links:...
    Thanks for those. I see in one of your links that 7.3 has set-returning functions - that's what I envision when I think of a stored procedure (as in MS SQL Server sp's). I'll bug my dba to move up from 7.2!
    Bradley

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •