3 ms·
> Archived Table > To prevent deleting data by mistake, we invent soft-delete pattern by having a is_deleted column. However, this brings extra complexities aro
by asah 3y ago
> Archived Table
> To prevent deleting data by mistake, we invent soft-delete pattern by having a is_deleted column. However, this brings extra complexities around foreign key, unique index enforcement. Ideally, PostgreSQL could allow users to configure an archived table. The removed data is moved to the archived table first and purged after a configured retention period. This simplifies application logic and data compliance work.
Partitioned tables works pretty well for this.
https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
- ako 3y agoAlso of interest, you can use the cluster command to reorder rows fysically on disc according to an index. This helps you cluster all active records, and cluster all deleted records. This way you can keep the records in one table, but more efficient querying as you need to read less database blocks.
- murkt 3y ago> Partitioned tables works pretty well for this. Pretty interesting idea. Do you have any stories or experience to share when going with this approach?
- asah 3y agoum, I've done it in production for years?