Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2016
    Posts
    3

    Unanswered: Postgres Cluster Solution

    We are looking to migrate our DB from oracle to postgresql DB. Current oracle setup is as follows:

    1. OLTP application with 95% writes
    2. Production is on a Oracle RAC cluster
    3. Most of the tables are partitioned

    What we are curreltly exploring is postgresXL cluster (equivalent of our Oracle RAC, so that we can create a 3 data node cluster with a coordinator and global transaction manager).
    Our idea is, we will scale this up as required if the POC is a success.

    In postgres XL cluster we are creating child tables for each partition and distributing the data in the 3 data nodes equally. There are triggers which will put the data in child tables (equivalent to oracle partitions)

    Are we on the right track? Is there an alternate solution we can try for our requirement? has anyone tried this before?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    An Oracle RAC is nowadays only used for high availability - typically not for horizontal scaling because RAC is still limited by the shared storage.

    Postgres XL has a completely different architecture has the storage is not shared between the nodes - so that would might actually scale better then RAC.

    Another option might be BDR (Bi-Directional Replication) which is less "invasive" then Postgres XL: http://2ndquadrant.com/en/resources/bdr/

    I don't know however how well that plays together with partitioning?

    How big (in terms of number of rows) are your tables? I wonder why "most" of them are partitioned.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2016
    Posts
    3
    Thanks for the reply.

    >>How big (in terms of number of rows) are your tables? I wonder why "most" of them are partitioned

    There are millions of rows in each partition - approx 10 million rows.

    High availability and performance is our main concern. Transactions are close to 2k per second.

    Is cluster the only solution or can we have a solution without postgres XL cluster approach?

    I thought BDR is a distributed DB solution. Hence didn't explore much on that.

    Ours is not a distributed DB?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Quote Originally Posted by pa1sas3 View Post
    I thought BDR is a distributed DB solution. Hence didn't explore much on that.
    While the original idea might have been to do this for database that are far apart, nothing prevents you from doing that with servers that are located next to each other. As BDR is just and extension to a "stock" Postgres, I would assume that it's a bit easier to setup.

    Note that partitioning is currently a lot less efficient in Postgres then it is in Oracle, has many more restrictions and is much more complicated to setup and maintain (Postgres 10 will improve partitioning but it still won't be as good as Oracle's as far as I can tell)

    If you really need partitioning it's probably worth looking at pg_partman (https://github.com/keithf4/pg_partman) which makes managing partitions a lot easier.

    I have no experience with either BDR or Postgres XL, but another way to increase performance might be to invest in a RAID array with (many!) high-end SSDs (SAS or PCIe) and then use a simple master/slave setup for high availability.

    I can imagine that that might be easier to manage then BDR or Postgres XL (which both impose some limitations on what you can do with Postgres).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Citus might also be another option: https://www.citusdata.com/blog/2016/...-and-tomorrow/
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

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