Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: Thinking about PostgreSQL

    Hey guys, thanks for having a PostgreSQL forum.

    How fast and scalable is it in comparison with MySQL? Is there any large commercial companies that use PostgreSQL. How easy is it to learn compared to MySQL and if you were to recomend it to me, why would you do so.

    Thanks!
    -Sam

  2. #2
    Join Date
    Jun 2004
    Posts
    31
    Quote Originally Posted by samuraisam
    Hey guys, thanks for having a PostgreSQL forum.

    How fast and scalable is it in comparison with MySQL? Is there any large commercial companies that use PostgreSQL. How easy is it to learn compared to MySQL and if you were to recomend it to me, why would you do so.

    Thanks!
    -Sam
    Assuming this is not a troll, I'll throw in my 2c. I cannot comment on other companies, but ours has 35 people and maintains a 100+ GB Pg installation. We are migrating over Oracle apps and should push that number to 1+ TB within the next two years.

    While I have not worked with MySQL very much, I have worked with associates who have used both.

    The difference seems to boil down to your expectations from a database. Think of the differences like two sound systems. One of them is mono only (MySQL) and gets very loud. The other (Pg) has 5.1 surround sound, a graphic equalizer and options to balance the speakers, and overload protection that makes sure the speakers do not get blown.

    If all you have used is the mono system, then you cannot imagine the need for the features of the 5.1 system. You would say, "Stereo? Overload protection? Who needs that? I listen to my CD just fine on my mono system and have never had a problem. In fact, my mono system is easier to set up, I don't have to monkey with all of those settings just to get a sound out of it."

    The advocates of the 5.1 system would tell you that the mono system isn't even a sound system if it lacks stereo, equalization, etc. The 5.1 guys are used to those features and will not begin to consider a system if it lacks them.

    As for performance, there are lots of benchmarks out there. Basically, if you use InnoDB in MySQL, it performs about the same as Pg.

    Hope that helps.

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    No, not a troll.

    -Sam

  4. #4
    Join Date
    Feb 2005
    Posts
    4
    Thanks for your reply.

    I think I'll definitly go with the Pg DB unless someone persuades me otherwise.

    Plus I'm a pretty big advocate of opensource.

    -Sam

  5. #5
    Join Date
    Feb 2005
    Location
    Duesseldorf, Germany
    Posts
    1
    Hi,

    I'm currently considering to give PG a try. Actually, I'm using MySql for about 2 years (on our very database intensive single community easyflirt.de [no advertising]).

    Unfortunately the application needs to do some heavy full table scans regulary (regarding to the voting system), and there is really no chance to improve the situation using indexes.

    So we're facing the problem with table-locking on MyIsam-Tables. We tried to use InnoDB (there's no table locking), but the speed for those queries, that locked the table, were so badly slow.... that we returned to MyIsam.

    Actually we're working "around" the problem having a database slave, that is only used for the "slow" queries. So the "real" production enviroment is not influenced by the table locks.

    So.. ok, that's my brief description about the situation. Now here my questions:

    1) Do you believe that InnoDb is about as fast/slow on table scans as PG, comparing to Mysql/MyIsam tables? If yes, I guess I would not invest the time for testing PG (it's more work than switching MyIsam to InnoDB and backwards.... and even those switching needs about 2 hours only for converting the data! In that time.... I would have to shut down the service).

    2) I really really really need Replication... so... the second thing that kept me from giving PG a try was the lack of an in-house replication feature. How reliable are the add-ons like Slony on that point?


    I would be very happy if some PG experts could give me some hints on my thoughts.

    Have a nice weekend,
    Christian
    Last edited by cszardenings; 02-18-05 at 17:28. Reason: spelling errors

  6. #6
    Join Date
    Jun 2004
    Posts
    31
    Quote Originally Posted by cszardenings
    Unfortunately the application needs to do some heavy full table scans regulary (regarding to the voting system), and there is really no chance to improve the situation using indexes.
    I find it difficult to believe that the data cannot be structured in a way that indexing will not help. If the application really needs a majority of the rows in a SELECT, then nothing will help. If the SELECT is only trying to find a small portion of the rows that meet a certain criteria, then indexing is (almost) always the answer.

    So we're facing the problem with table-locking on MyIsam-Tables. We tried to use InnoDB (there's no table locking), but the speed for those queries, that locked the table, were so badly slow.... that we returned to MyIsam.
    My experience tells me that table locks are (almost) always a disaster once an application scales up.

    1) Do you believe that InnoDb is about as fast/slow on table scans as PG, comparing to Mysql/MyIsam tables?
    Going out on a limb, I believe that that FTS (full table scans) are the problem, not the RDBMS that you use.

    2) I really really really need Replication... so... the second thing that kept me from giving PG a try was the lack of an in-house replication feature. How reliable are the add-ons like Slony on that point?
    A main feature of a transactional DB is that it gives each transaction its own "version" of the DB to work with. If 153 transactions are active against a DB, then the DB is maintaining the illusion of 153 different versions of the data, with each transaction making changes only to its "version" until a commit happens. The transaction functionality of Pg, along with minimal locking, is the main reason that Pg scales up so well, while MySQL falls apart under load.

    Actually replicating a transactional DB successfully is nearly impossible. Replicating a non-transactional DB is far, far simpler.

    I have played with SLONY but decided against it. There are only a few reasons for replication and most of those can be solved by better hardware and/or software.

    Hope this helps.

Posting Permissions

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