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 https://github.com/pgaudit/pgaudit extension […]