How do you deal with index fragmentation if your SQL server is working in high load environment with 24/7 workload without any maintenance window? What are the best practices for index rebuild and index reorganize? What is better? What is possible if you have only Standard Edition on some servers? But first, let's debunk few myths.

Myth 1. We use SSD (or super duper storage), so we should not care about the fragmentation. False. Index rebuild compactifies a table, with compression it makes it sometimes several times smaller, improving the cache hits ratio and overall performance (this happens even without compression).

Myth 2. Index rebuild shorten SSD lifespan. False. One extra write cycle is nothing for the modern SSDs. If your tempdb is on SSD/NVMe, it is under much harder stress than data disks.

Myth 3. On Enterprise Edition there is a good option: ONLINE=ON, so I just create a script with all tables and go ahead. False. There are tons of potential problems created by INDEX REBUILD even with ONLINE and RESUMABLE ON - so never run index rebuilds without controlling the process.

Finally, we will tackle the REBUILD vs REORGANIZE subject and what is possible to achieve if you have only Standard Edition.

We continue to follow the news in the world of PostgreSQL. The PostgreSQL 16 Release Candidate 1 was rolled out on August 31. If all is well, PostgreSQL 16 will officially release on September 14.

What has changed in the upcoming release after the April code freeze? What's getting into PostgreSQL 17 after the first commitfest? Read our latest review to find out!

Alright, folks! Get ready to dive into the world of MySQL table partitioning. In this guide, we'll cover why partitioning is so darn important for your MySQL tables. Plus, we'll dish out all the juicy benefits you can expect from partitioning a table. So, buckle up, and let's get started!

The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let’s take a look at some exciting new updates it introduced.

I hope that this review together with the previous articles in the series (2022-07, 2022-09, 2022-11, 2023-01) will give you a coherent idea of the new features of PostgreSQL 16.

We continue to follow the news of the PostgreSQL 16 release, and today, the results of the fourth CommitFest are on the table. Let's have a look.

If you missed the previous CommitFests, check out our reviews for 2022-07, 2022-09 and 2022-11.

This is a series of articles dedicated to the optimal choice between different systems on a real project or an architectural interview.

This topic seemed relevant to me because such tasks can be encountered both at work and at an interview for System Design Interview and you will have to choose between these two types of DBMS. I plunged into this issue and will tell you what and how. What is better in each case, what are the advantages and disadvantages of these systems and which one to choose, I will show with several examples at the end of the article.

SQL or NoSQL?

We continue to follow the news of the upcoming PostgreSQL 16. The third CommitFest concluded in early December. Let's look at the results.

If you missed the previous CommitFests, check out our reviews: 2022-07, 2022-09.

Here are the patches I want to talk about:

meson: a new source code build system
Documentation: a new chapter on transaction processing
psql: \d+ indicates foreign partitions in a partitioned table
psql: extended query protocol support
Predicate locks on materialized views
Tracking last scan time of indexes and tables
pg_buffercache: a new function pg_buffercache_summary
walsender displays the database name in the process status
Reducing the WAL overhead of freezing tuples
Reduced power consumption when idle
postgres_fdw: batch mode for COPY
Modernizing the GUC infrastructure
Hash index build optimization
MAINTAIN ― a new privilege for table maintenance
SET ROLE: better role change management
Support for file inclusion directives in pg_hba.conf and pg_ident.conf
Regular expressions support in pg_hba.conf

It's official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release.

Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code.

If you missed the July CommitFest, our previous article will get you up to speed in no time.

Here are the patches I want to talk about:

SYSTEM_USER function
Frozen pages/tuples information in autovacuum's server log
pg_stat_get_backend_idset returns the actual backend ID
Improved performance of ORDER BY / DISTINCT aggregates
Faster bulk-loading into partitioned tables
Optimized lookups in snapshots
Bidirectional logical replication
pg_auth_members: pg_auth_members: role membership granting management
pg_auth_members: role membership and privilege inheritance
pg_receivewal and pg_recvlogical can now handle SIGTERM

So far we've discussed query execution stages, statistics, and the two basic data access methods: Sequential scan and Index scan.

The next item on the list is join methods. This article will remind you what logical join types are out there, and then discuss one of three physical join methods, the Nested loop join. Additionally, we will check out the row memoization feature introduced in PostgreSQL 14.

August was a special month in PostgreSQL release cycle as the first CommitFest for the 16th PostgreSQL release was held.

Let's compile the server and check out the cool new stuff!

In the previous articles, we have covered query execution stages, statistics, sequential and index scan, and two of the three join methods: nested loop and hash join.

This last article of the series will cover the merge algorithm and sorting. I will also demonstrate how the three join methods compare against each other.

Queries in PostgreSQL. Hashing

So far we have covered query execution stages, statistics, sequential and index scan, and have moved on to joins.

Queries in PostgreSQL. Index scan

In previous articles we discussed query execution stages and statistics. Last time, I started on data access methods, namely Sequential scan. Today we will cover Index Scan.

In this article, we would like to compare the core mathematical bases of the two most popular theories and associative theory.

Queries in PostgreSQL. Sequential scan

In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of, and how it is executed.

In this article, I will demonstrate how the planner calculates execution costs. I will also discuss access methods and how they affect these costs, and use the sequential scan method as an illustration. Lastly, I will talk about parallel execution in PostgreSQL, how it works, and when to use it.

I will use several seemingly complicated math formulas later in the article. You don't have to memorize any of them to get to the bottom of how the planner works; they are merely there to show where I get my numbers from.

In the last article we reviewed the stages of query execution. Before we move on to plan node operations (data access and join methods), let's discuss the bread and butter of the cost optimizer: statistics.

Dive in to learn what types of statistics PostgreSQL collects when planning queries, and how they improve query cost assessment and execution times.

Hello! I'm kicking off another article series about the internals of PostgreSQL. This one will focus on query planning and execution mechanics.

In the first article we will split the query execution process into stages and discuss what exactly happens at each stage.

Deadlocks

Row-level locks

Organization

• A lock must be available somewhere in the shared memory of the server.
• The higher granularity of locks, the lower the contention among concurrent processes.
• On the other hand, the higher the granularity, the more of the memory is occupied by locks.