We have about 2 TB of data distributed across literally thousand of tables in PostgreSQL 9.4.9, and we think that we should be moving the historic data to a more report-oriented and efficient solution. Which is the proper solution is what troubles me, as I'd like it to be AWS based (perhaps RedShift, EMR or other) as our platform resides there, but even within that ecosystem, there are several approaches to be used. I'm aware that asking about the choice of a specific engine and/or solution is offtopic, but I'd certainly like a suggestion of what kind of tools to choose from, without necessarily implying the use of single one. As a team, we have no knowledge yet of the data warehouse architecture and, just a bit of the big data technologies, but we certainly have to study on those subjects.

  • Some of the more established MPP solutions in the cloud are RedShift, Snowflake, and SQL Data Warehouse. Honestly, I don't think 2TB is large enough to justify a MPP approach, as you really need to design your solution properly to effectively take advantage of a MPP solution. I suggest you optimize your Postgres solution or migrate to a different RDBMS if you find Postgres is no longer fitting your needs. – John Eisbrener Apr 3 '17 at 20:54

I think you should be thinking about what your end state is going to be.

I.e: "What is the requirement"?

The requirements should be driving the solution. Not the other way around.

  • What does the business want?

  • What reports do you need? What outputs?

  • Will your reporting solution benefit from aggregation? Would an OLAP cube be of benefit? Will you be transforming or denormalising the data?

  • Will your DW be fed from one system or many? If many: will it be application agnostic?

  • What display tools and technologies are expected? Any compatibility issues that might limit platform selection?

  • How long do your current reports take? And how fast do they need to be?. How fast is fast enough? What changed?

  • Can you produce the reports you need now with the data you have?

  • Are conventional reports still acceptable? Or does the business now need/want dynamic interactive tools? Reports? Dashboard? Exploration tools? Mobile?

  • How recent must the data be? Is yesterdays data processed overnight acceptable? Or do they need near real time?

  • Do they want analytical or operational data? What happened over the last 12 months or what happened this morning?

Once you have a clear requirement, it will allow you to shortlist viable solutions. Within your shortlist you can start to express preferences and pros and cons for the evaluation process.

  • Thanks @Peter for your answer, which seems pretty logical and, fortunately YES: I can answer all of the above questions. Actually, the search for such a system is based on the reporting that the business wants, which cannot be generated in time with the actual underlying db structure and data – Gonzalo Vasquez Apr 4 '17 at 12:38
  • As I'm familiar with RDBMS & OLAP technology thats what i would be leaning towards. Or at a minimum refactoring and/or aggregating data into a DW datamart so that you can get the performance you want. AFAIK big data is great for unstructured data but you already have yours in a relational db, so im not sure what you would gain. – Sir Swears-a-lot Apr 5 '17 at 9:16
  • So what is the target or goal that business has set? Is this just about performance? If you could make your existing reports run faster would that be acceptable? Does the business understand the difference between need and want? – Sir Swears-a-lot Apr 5 '17 at 9:20
  • Actually, aggregation and a bit of ETL would be needed to get better results. But BETTER (from the business viewpoint) and FASTER getting reports in PostgreSQL in our current database setup and, enabling our customers create their own reports seems like not really achievable. Please see a brief use case scenario on this PasteBin: pastebin.com/raw/LF6hZV9b – Gonzalo Vasquez Apr 5 '17 at 18:57
  • An OLAP cube may not be the best fit for this as its spatial data and isnt naturally aggregatable. Although meta data about each vehicle and trip is. I think this possible with an rdbms and some ETL. If you transform the data to remove redundant rows and reduce volume it could be workable. – Sir Swears-a-lot Apr 6 '17 at 0:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.