Simulating Partitioned Tables in Redshift

Redshift does not support partitioned tables.

Time Series Tables

Time series tables provide a scalable way to handle time series data. Instead of storing all the data in a single table it is partitioned by timestamp in multiple tables. This helps ensure that the queries run fast and simplifies managing the retention of the time series data — old data can easily be deleted from Redshift and then retrieved from S3 if needed again - maintaining the hygiene of Redshift, improving performance.

Tables are partitioned with a view that unions them:

events_20170910
2017-09-10 01:00:00page_view
2017-09-10 02:01:00registration
2017-09-10 12:11:00click
events_20170911
2017-09-11 01:00:00purchase
2017-09-11 01:22:00click
2017-09-11 16:04:00page_view
events_20170912
2017-09-12 00:00:02page_view
2017-09-12 13:57:00click
2017-09-12 18:44:00click
CREATE VIEW events AS
SELECT * FROM events_20170910
UNION ALL
SELECT * FROM events_20170911
UNION ALL
SELECT * FROM events_20170912;