PostgreSQL – Checkpoint

What is Checkpoint?

Checkpoint periodically flushes all dirty buffers and creates checkpoint record in the WAL log, which is used for recovery.

The purpose of the checkpoint is to ensure that all the dirty buffers generated up to a certain point are sent to the disk. This will recycle the WAL up to that point. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Error: Canceling statement due to conflict with recovery

Sample Log Output:

Description:

When the standby server receives updates/deletes in the WAL stream that will result in invalidating data currently being accessed by a running query, this error will happen. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Monitoring Replication

What is replication lag? How can we monitor Replication lag? Replication lag does not occur in most setups; however, it is important to monitor the entire endpoints of replication to ensure that our data is safe.

What is Replication Lag? Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Health Check Scripts

Here are some scripts that you can use to check the health of your PostgreSQL DB servers.

Check Uptime

SELECT current_timestamp - pg_postmaster_start_time();

Monitor cache hit ratio

Tells how often your data is served from memory vs having to go to disk. 99% is a good metric for performance. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Using pgbench for Benchmark Testing

pgbench is a tool for testing the performance of a PostgreSQL database. It works by simulating a specified number of concurrent clients who are executing a series of SQL commands. You can use pgbench to measure the performance of your database and to compare the performance of different database configurations. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Analyzing Queries Using pg_stat_statements

pg_stat_statements tracks planning and execution statistics of all SQL statements executed by a server.

Find the top 10 time-consuming queries

SELECT 
  round(
    (
      100 * total_exec_time / sum(total_exec_time) OVER ()
    ):: numeric, 
    2
  ) percent, 
  round(total_exec_time :: numeric, 2) AS total, 
  calls, 
  round(mean_exec_time :: numeric, 2) AS mean, 
  substring(query, 1, 200) 
FROM 
  pg_stat_statements 
ORDER BY 
  total_exec_time DESC 
LIMIT 
  10;

Find the queries that are writing to the temp the most Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Backing up and restoring one or more tables using pg_dump and pg_restore

There are 2 types of backups in PostgreSQL:

  1. Logical
  2. Physical

Logical backups in PostgreSQL can be taken using pg_dump. A backup taken using pg_dump can exist in both a text file (human-readable script file) format or a custom format. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

The Future of Software Engineering: Trends and Predictions

In the rapidly changing realm of technology, software development remains a crucial field, constantly developing and adjusting to meet the requirements of the modern era. Constant progress in hardware and software technologies gives birth to new trends and methodologies, which are ceaselessly reshaping the landscape of software engineering. By glimpsing into the future, we can identify key patterns that hint at how this vibrant field might transform in the coming years. This article explores these trends and provides insights into the future of software engineering Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Backing Up and Restoring a Database Using pg_dump and pg_restore

There are 2 types of backups in PostgreSQL:

  1. Logical
  2. Physical

Logical backups in PostgreSQL can be taken using pg_dump. A backup taken using pg_dump can exist in both a text file (human-readable script file) format or a custom format. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

PostgreSQL – Shutting down PostgreSQL using different shutdown modes

We can choose to shutdown PostgreSQL in 3 modes:

  1. Smart mode
  2. Fast mode
  3. Immediate mode

You can use the flag -m to invoke PostgreSQL shutdown using a specific mode. Read More

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter