Project 3: Architecting for Scale with Partition Pruning and FDW

You are the lead database architect for an IoT company that generates millions of sensor readings daily. Querying this massive, monolithic table has become unacceptably slow.

Your task is to implement declarative table partitioning by range (e.g., partitioning by month) to physically divide the data while maintaining a single logical table interface. Once partitioned, you must simulate a "hot/cold" storage tier architecture. You will spin up a second PostgreSQL instance, configure postgres_fdw (Foreign Data Wrapper), and move all partitions older than one year to this remote "cold" server.

You must write a series of time-series queries spanning both recent and historical data. Using EXPLAIN, you must prove two things: first, that the query planner is successfully executing "Partition Pruning" (skipping irrelevant local partitions), and second, that it is successfully pushing down the remote queries to the FDW rather than pulling the entire remote dataset into local memory to filter.

Rubric: