Contact Info

Folow us on social

Running VACUUM FULL in Non-blocking Mode

OK, I got your attention. You’re probably thinking I must actually be referring to other tools like pg_repack. No I am not. I really am referring to running the VACUUM FULL command in non-blocking mode. WHAT!?#$%@! Here we go: Setup 2 psql session windows on the same screen. The 1st window shows the state of […]

Vacuum Those MVs!

On a particular project, Materialized Views (MVs) were being used quite extensively in the Apps that were being migrated from Oracle to PostgreSQL.  One thing I noticed was missing was VACUUM ANLYZE on these MVs after being created or refreshed.  It is extremely important to integrate VACUUM ANALYZE commands into this process.  Here is the […]

Postgres 11 partitioning

PostgreSQL supported table partitioning  implementation based on inheritance and triggers for over more than a decade now. However, the declarative partition support was added in Postgres 10 release in Oct 2017.  Since Postgres 10, Postgres  supports built-in declarative partitioning so it was easier to create partitions but you still need to manage trigger to update […]

Audit logging using JSONB in Postgres

Recently, someone reached out to me asking “what’s the best way to achieve database DML auditing in Postgres?” I have suggested a couple of options below to achieve DML auditing in Postgres: Audit using Postgres logs. Postgres allows to keep track of DML statements at database level by enabling auditing directly in the postgres logs. You can use extension […]