Those of us who come from the world of SQL Server are used to the Missing Index DMVs (sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats), which give us the CREATE INDEX command ready along with Avg User Impact and Equality/Inequality/Include columns. In PostgreSQL, the default… read more
How to do Performance Testing in PostgreSQL with pgbench
pgbench is one of the most useful tools for PostgreSQL. It is used for benchmarking and stress testing the database, simulating multiple simultaneous transactions (TPS). In this article we will see how we can set it up, run the… read more
How to see how much Indexes are used in PostgreSQL
In every database, indexes are essential for the speed of SELECT queries. However, each index we create has a cost, it takes up disk space, burdens memory and delays INSERT, UPDATE and DELETE operations, since the… read more
How memory works in PostgreSQL and what parameters do we configure?
In a previous article we have analyzed the architecture of Oracle Database (SGA/PGA). This time we will look at the memory architecture of PostgreSQL and the key parameters for optimal performance. The memory model of PostgreSQL differs significantly from that of… read more
What are WALs in PostgreSQL and how do we configure them?
In this article we will look at the mechanism that connects memory to disk and ensures that we will not lose data if the database goes down (e.g. due to power failure): Write-Ahead Logs (WAL) and Checkpoints. When we execute… read more
What is MVCC and Vacuum in PostgreSQL database and how do we avoid Bloat?
In this article we will see how PostgreSQL manages concurrent access through MVCC, what Bloat is, what Data Churn is, what Vacuum is, and how to properly configure it with Autovacuum. What is… read more
How to free up reserved space from datafiles / tempfiles of an Oracle database
In this article we will see a script with which we can easily free up space from datafiles and tempfiles where it is not used in Oracle databases. More specifically, we will reduce the size of the datafiles so that… read more
How do we collect the actual execution plan from queries using Extended Event and how do we read its data
In an earlier article we saw how we activate the Query Store to monitor the performance of the queries and the plan they ran. But as we said, the Query Store has quite a performance impact, so we may want some lightweight profiling. In this... read more
How we collect deadlocked queries via Extended Event and how we read its data
In an earlier article we saw how we create an Extended Event to see queries with a long duration. In this article we will build Extended Event that records deadlocks. All we need to do is create it with a T-SQL command. After… read more
