Performance Tricks in Other Databases

I have been working exclusively with the Oracle database for over 15 years. Now I have jumped to a project that uses the Greenplum database. This is a distributed database with PostgresSQL underneath. We are using it to deal with massive amount of data that have a short timeframe for processing.


My new team has informed me that the first rule of thumb is to not do updates. We insert records only. We also do not join too many tables together at once either. Mostly just two tables are joined. Here is a trick they use. The got functions which use temporary tables. They join two tables and stick the result in one temp table. Then they join that temp table with other tables to create a second temp table.


This goes on and on with different functions. They build up a huge table with many columns (1000+) by continually joined two tables together and storing the result in temp tables. Apparently this is very fast in the Greenplum environment. It does not seem intuitive. But hey. If it works, why worry about it?