Is creating a new table better than partitioning for my use case?

Question

Problem: i have new data that is being generated monthly. When this new data becomes available, my existing rows in my table becomes stale and I have to delete all of them.

I am considering two options:

1. Have one table and insert new data when they become available. Have an extra column, inserted_at and partition the data based this column. After all the new data isinserted, delete the stale data. Have additional logic in my server to determine which rows to use when there are two rows for the same id

2. Have 1 table per month. When new data is available, create a new table and COPY the data to this table. My server logic will use the new table on a specified date every month (call this rollover date). Truncate old table afterwards.

I have a strong preference for 2 because:

1. it is very simple.
2. My teammates can easily check the newly created table for errors before the rollover date.
3. approach 1 requires specialized logic.

What are your thoughts on this matter?

Do you have any opinion on why approach 1 is better?

PS: I am using postgres on AWS RDS.

Answer 1

I would recommend having a second schema that has the same tables, say appschema and appschema_shadow.

Then you insert the new data in the tables in schema appschema_shadow. At the time you want to switch, you terminate all client applications and rename the schemas:

BEGIN;
ALTER SCHEMA appschema RENAME TO swap;
ALTER SCHEMA appschema_shadow RENAME TO appschema;
ALTER SCHEMA swap RENAME TO appschema_shadow;
COMMIT;