Citus Blog

Postgres under the covers in simplified terms is one giant append only log. When you insert a new record that gets appended, but the same happens for deletes and updates. For a delete a record is just flagged as invisible, it's not actually removed from disk immediately. For updates the old record is flagged as invisible and a new record is written. What then later happens is Postgres comes through and looks at all records that are invisible and actually frees up the disk storage. This process is known as vacuum.

There are a couple of key levels to VACUUM within Postgres:

• VACUUM ANALYZE - This one is commonly run when you've recently loaded data into your database and want Postgres to update it's statistics about the data
• VACUUM FULL - This will take a lock during the operation, but will scan the full table and reclaim all the space it can from dead tuples.

Keep reading

It’s a common misconception that high volume read-write workloads in PostgreSQL inevitably causes database inefficiency. We’ve heard of cases where users encounter slowdowns doing only a few hundred writes per second and turn to systems like Dynamo...

Keep reading