Thursday, August 18, 2016

PostgreSQL vs Hadoop

So one of the clients I do work with is moving a large database from PostgreSQL to Hadoop.  The reasons are sound -- volume and velocity are major issues for them, and PostgreSQL is not going away in their data center and in their industry there is a lot more Hadoop usage and tooling than there is PostgreSQL tooling for life science analytics (Hadoop is likely to replace both PostgreSQL and, hopefully, a massive amount of data on NFS).  However this has provided an opportunity to think about big data problems and solutions and their implications.  At the same time I have seen as many people moving from Hadoop to PostgreSQL as the other way around.  No, LedgerSMB will never likely use Hadoop as a backend.  It is definitely not the right solution to any of our problems.

Big data problems tend to fall into three categories, namely managing ever increasing volume of data, managing increasing velocity of data, and dealing with greater variety of data structure.  It's worth noting that these are categories of problems, not specific problems themselves, and the problems within the categories are sufficiently varied that there is no solution for everyone.  Moreover these solutions are hardly without their own significant costs.  All too often I have seen programs like Hadoop pushed as a general solution without attention to these costs and the result is usually something that is overly complex and hard to maintain, may be slow, and doesn't work very well.

So the first point worth noting is that big data solutions are specialist solutions, while relational database solutions for OLTP and analytics are generalist solutions.  Usually those who are smart start with the generalist solutions and move to the specialist solutions unless they know out of the box that the specialist solutions address a specific problem they know they have.  No, Hadoop does not make a great general ETL platform.....

One of the key things to note is that Hadoop is built to solve all three problems simultaneously.  This means that you effectively buy into a lot of other costs if you are trying to solve only one of the V problems with it.

The single largest cost comes from the solutions to the variety of data issues.  PostgreSQL and other relational data solutions provide very good guarantees on the data because they enforce a lack of variety.  You force a schema on write and if that is violated, you throw an error.  Hadoop enforces a schema on read, and so you can store data and then try to read it, and get a lot of null answers back because the data didn't fit your expectations.  Ouch.  But that's very helpful when trying to make sense of a lot of non-structured data.

Now, solutions to check out first if you are faced with volume and velocity problems include Postgres-XL and similar shard/clustering solutions but these really require good data partitioning criteria.  If your data set is highly interrelated, it may not be a good solution because cross-node joins are expensive.  Also you wouldn't use these for smallish datasets either, certainly not if they are under a TB since the complexity cost of these solutions is not lightly undertaken either.

Premature optimization is the root of all evil and big data solutions have their place.  However don't use them just because they are cool or new, or resume-building.  They are specialist tools and overuse creates more problems than underuse.

23 comments:

  1. PostgreSQL 10 roadmap from 2ndQuadrant on columnar indexes may make PostgreSQL suitable for big data:
    http://blog.2ndquadrant.com/postgresql-10-roadmap/

    ReplyDelete
    Replies
    1. Certainly for some kinds of big data. And anything that helps certain areas do better is a major win. Having index-oriented tables would also help with certain volume-related issues as well (and there has been talk about that for some time).

      Delete
    2. But one point worth noting is that "big data" is a bit of a buzzword and poorly defined topic. Columnar stores help certain kinds of aggregation. Index oriented tables help one tune certain kinds of access patterns. Those are important tools in dealing with data volume problems.

      But one of the things which has really been difficult in the deployment I work with that is moving to Hadoop is the fact that TOAST performance overhead is rather difficult to measure and in order to get adequate performance, certain things have had to move to non-1NF designs. Even when you are dealing with structured data, there are access pattern corners where one has to be rather imaginative to keep performance up.

      So it is a mistake to think that all volume problems are the same, or all velocity problems are the same. Once you get into TB of data, attention to detail and awareness of your specific issues become important.

      Today PostgreSQL can deal well with certain kinds of big data problems. And those are expanding. But we should be careful not to be like the folks who think Hadoop is the answer to everything ;-)

      Delete
  2. The CitusDB extension is an interesting alternative to Postgres-XL, and it's also not a fork. You're correct that if your data doesn't shard well you're still in trouble. Though, doesn't the same apply to hadoop? Or does it just use a hash of the entire row/document to partition?

    ReplyDelete
  3. thanks for shared wonderful information of giving best information.its more useful and more helpful. great doing keep sharing
    Hadoop Training in Chennai

    ReplyDelete
  4. Thanks for shared wonderful information of giving best information.its more useful and more helpful. great doing keep sharing...
    oracle-hrms training in chennai

    ReplyDelete
  5. we are taking this Android training with basic and advance concepts. We are the best training institute in chennai. We are having the training professionals with more than 10+ years experience. Android Training in Chennai |
    Android Training in Chennai |
    Android Training in Chennai |
    Android Training in Chennai |

    ReplyDelete
  6. really you have post an informative blog. it will be really helpful to many peoples. thank you for sharing this blog.
    java training in chennai

    ReplyDelete
  7. hi welcome to this blog. really you have post an informative blog. it will be really helpful to many peoples. thank you for sharing this blog.
    java training in chennai

    ReplyDelete
  8. hi welcome to this blog. really you have posted an informative blog. it will be really helpful to many peoples. thank you for sharing this blog.
    java training in chennai

    ReplyDelete
  9. This blog post is a great information. I like that our share this topic.We update our site has a lot of uses.Many useful interesting blogs found off here. Selenium Training in Chennai

    ReplyDelete
  10. Very Nice Blog I like the way you explained these things.
    Indias Fastest Local Search Engine
    CALL360
    Indias Leading Local Business Directory

    ReplyDelete
  11. GREEN WOMEN HOSTELGreen Women hostel is one of the leading Ladies hostel in Adyar and we serving an excellent service to Staying people, We create a home atmosphere, it is the best place for Working WomenOur hostel Surrounded around bus depot, hospital, atm, bank, medical Shop & 24 hours Security Facility

    ReplyDelete
  12. Thanks for sharing.Our Dot Net Training in Chennai centres are equipped with perfect environment to learn with all required facilities.More details about that course.......
    Hadoop Training in Chennai
    Android Training in Chennai

    ReplyDelete
  13. This is an awesome post. Really very informative and creative contents. These concept is a good way to enhance the knowledge. Excellent post. Our Digital Marketing Training is tailored for beginners who want to learn how to stand out digitally, whether it is for their own business or a personal brand.

    digital marketing training institute in chennai
    digital marketing course fee in chennai
    digital marketing course in velachery
    is digital marketing a good career
    best seo training center in chennai
    digital marketing company in chennai
    digital marketing agency in chennai
    top digital marketing companies in chennai

    ReplyDelete
  14. Thanks for sharing the best useful information to us its very useful info, i We at Colan Infotech Private Limited a
    Mobile apps development companies in chennai
    we always desire to solicit our customer's fruitful experience with us, we are the top notch
    Mobile App Development Company in chennai .

    ReplyDelete
  15. Thanks for the awesome share
    We at Colan Infotech Private Limited best web design company in chennai,is Situated in US and India,
    will provide you best service in qa testing services
    Design Services and Colan Infotech has a group of exceedingly dedicated, inventive and creative experts with an energy for delivering exciting ,
    helpful and stylish Web and Mobile Applications, We are one of the best software testing services company
    and of course we stepped in bangalore too we are best qa and testing services
    can provide quality assurance and testing services,
    we are the best among the software testing company india

    ReplyDelete
  16. Thanks for sharing this information article
    Hi we at Colan Infotech Private Limited , a company which is Situated in US and India, will provide you best service and our talented team will assure you best result and we are familiar with international markets, We work with customers in a wide variety of sectors. Our talented team can handle all the aspects of custom application development, we are the best among the dot net development companies in Chennai . asp .net web development company We have quite an extensive experience working with asp .net development services. we are the only asp.net web development company which offer custom services to a wide range of industries by exceeding our client’s expectations. You can even interact directly with the team regarding your project, just as you would with your in-house team. hire asp.net programmers to achieve your dream product.
    Custom application development company, asp.net development companies, asp .net web development company,Hire asp .net programmers,asp.net web development services,dot net development companies in chennai Hire asp .net programmers/ hire asp .net developer  . We are the best asp .net development company providing top notch asp .net development services Hire asp .net programmers/ hire asp .net developer  . We are the best asp .net development company providing top notch asp .net development services.

    ReplyDelete
  17. Thanks for sharing information article , Thanks for sharing useful information
    Hi we at Colan Infotech Private Limited , a company which is Situated in US and India, will provide you best java web service and our talented
    java application development. team will assure you best result and we are familiar with international markets, We work with customers in a wide variety of sectors. Our talented team can handle all the aspects of
    Java web application development,we are the best among the
    Java development company.
    We have quite an extensive experience working with
    java development services.
    we are the only Java application development company which offer custom services to a wide range of industries by exceeding our client’s expectations. You can even interact directly with the team regarding your project, just as you would with your in-house team.Our pro team will provide you the best
    java appliaction development services.
    We are best among the
    java development companies in Chennai,
    please review our customer feedbacks so that you may find a clue about us. If you want one stop solution for java development outsourcing, Colan infotech is the only stop you need to step in. Colan Infotech is the unique
    java web development company.were our team of unique
    java application developer
    were ranked top in
    java enterprise application development.

    ReplyDelete
  18. Thanks for sharing useful information article to us keep sharing this info,
    Hi We at Colan Infotech Private Limited a
    Mobile application development company in chennai,
    is Situated in US and India, will provide you best service in
    enterprise mobile app development company .
    and Colan Infotech has a group of exceedingly dedicated, inventive and creative experts with an energy for delivering exciting , helpful and stylish Web and Mobile Applications, We work with customers in a wide variety of sectors.
    We design all of our websites and applications using the responsive web design approach. Our talented team can handle all the aspects of mobility so we are rated as best service provider in
    Mobile apps development companies in chennai.

    We solidly trust that our customers start things out and there is not a viable alternative for quality of service.
    We offer custom services to a wide range of industries by exceeding our client’s expectations. You can even interact directly with the team regarding your project, just as you would with your in-house team. we always desire to solicit our customer's fruitful experience with us, we are the top notch
    Mobile App Development Company in chennai
    and mobile app development companies in Bangalore. We can provide best
    mobile app development chennai .
    We can provide cutting edge technology services in
    Mobile application development in chennai.
    Reach us for mobile app development chennai or just call us for best
    mobile app developers in chennai .

    ReplyDelete
  19. Finding the time and actual effort to create a superb article like this is great thing. I’ll learn many new stuff right here! Good luck for the next post buddy..
    PHP training in chennai

    ReplyDelete
  20. hello!!!
    Everyone wants to get unique place in the IT industry’s for that you need to upgrade your skills, your blog helps me improvise my skill set to get good career, keep sharing your thoughts with us.

    dot net training in chennai

    ReplyDelete