Best Practice question - how would you solve this implementation 300M rows in one table, 2 other related tables much smaller

[Zoltan Nemeth]

I'm learning as I go here, and I'm having trouble taking generic instructions and translating them to my current situation, so I'm hoping the community can give me some pointers on how I should approach this.

I have 3 tables all stored in a PostgreSQL database:

Table A - 300M rows of data (each row is a login event for a user)

Table B - 800K rows (user details, each row is info for a specific user)

Table C - 3M rows (same as Table B but a different group of users)

I use Spotfire Analyst on my desktop as my tool. I need to analyze table A and summarize the rows a couple different ways, but before I can summarize it I have to join Table A with data from both Table B and Table C. Each row in table A needs information from EITHER B or C, but not both, and this information needs to be available before I can summarize (ie. I need location information from B/C so I can tally up data per region;each user in Table A might be in EITHER B or C).

I've setup information links for all 3 tables, and currently this is what I do:

- Add table A to an analysis

- Add table B to that analysis

- Add rows to Table B from C

- Add columns to A from the newly expanded table B

- work with this massive dataset to create my summarized data

 

This dxp file takes about 2.5 hours to open, and anytime I change one thing it takes a few minutes for Tibco to respond. My local PC is a powerhouse (i9-9900 CPU, 64GB ram, SSD drive, etc) and I don't max out any resources.

Ideally, I would like to do the joins in Information Designer so I can then do some pivots on-load, but I'm not sure if that's the best approach, or if there are better ways to do it. Assume I don't have the resources to create new views on the Postgres server directly.

 

Thanks in advance for any tips/tricks/advice.