Postgres Partial Indexes

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

Computer Operation Times in Human Scale.

It's important to know the time scale of computers.In my experience programmers use random times for sleep, pause or retry - oh lets pause for a 1 second!! - thats small for us humans, thats a lot of time in Computer's time scale. 

Following table gives a sense of mapping from computer time scale to human time scale.

The table is from Peter Norvig's site (very popular): http://norvig.com/21-days.html#answers

The mapping is from Erik Meijer - Director of Engineering Facebook.

Operation	Timing in Computer Time Scale	Timing in Human Time Scale
execute typical instruction	1/1,000,000,000 sec = 1 nanosec	1 second
fetch from L1 cache memory	0.5 nanosec	0.5 seconds
branch misprediction	5 nanosec	5 seconds
fetch from L2 cache memory	7 nanosec	7 seconds
Mutex lock/unlock	25 nanosec	0.5 minutes
fetch from main memory	100 nanosec	1.5 minutes
send 2K bytes over 1Gbps network	20,000 nanosec	5.5 hours
read 1MB sequentially from memory	250,000 nanosec	3 days
fetch from new disk location (seek)	8,000,000 nanosec	13 weeks
read 1MB sequentially from disk	20,000,000 nanosec	6.5 months
send packet US to Europe and back	150 milliseconds = 150,000,000 nanosec	5 year