Tag: Performance Tuning

Modern artificial intelligence applications and Large Language Models are based on data called vectors (or embeddings). PostgreSQL, thanks to its flexibility, can be transformed into a full vector database with the help of pgvector.… read more

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

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

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

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

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

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