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 EisbrenerCommented Apr 3, 2017 at 20:54
1 Answer
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 Commented Apr 4, 2017 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. Commented Apr 5, 2017 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? Commented Apr 5, 2017 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 Commented Apr 5, 2017 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. Commented Apr 6, 2017 at 0:51