We are building a system that uses soft delete in database tables. We use Postgres as the datastore. Soft delete is not a preferred option at times. However, we need the soft delete option since we support revert to the deleted version.
Postgres supports partial indexes which we could use. Basically index only rows that are not soft deleted. These are rows that will be used for computation; indexing only those made sense.
We setup partial indexes; post deployment the CPU usage on the Postgres box hit the roof. We realised the issue was due to partial indexes after some analysis on the changes that went into the deployed package.
Following blog is good explanation of impact of using partial indexes and the fixes.
https://heap.io/blog/engineering/basic-performance-analysis-saved-us-millions
Postgres supports partial indexes which we could use. Basically index only rows that are not soft deleted. These are rows that will be used for computation; indexing only those made sense.
We setup partial indexes; post deployment the CPU usage on the Postgres box hit the roof. We realised the issue was due to partial indexes after some analysis on the changes that went into the deployed package.
Following blog is good explanation of impact of using partial indexes and the fixes.
https://heap.io/blog/engineering/basic-performance-analysis-saved-us-millions
No comments:
Post a Comment