DEV Community: Sadeed Ahmad

PostgreSQL: Space-Partitioned Generalized Search Tree

Sadeed Ahmad — Tue, 25 Jul 2023 11:54:00 +0000

PostgreSQL is an impressive open-source relational database management system that offers developers numerous advanced capabilities. PostgreSQL offers support for custom index types. These index types enable developers to create specialized indexes tailored to specific data types, resulting in improved query performance.

SP-GiST

Space-Partitioned Generalized Search Tree is a specialized index designed to effectively manage complex data types like geometric shapes, network addresses, and full-text documents. It extends the capabilities of the widely used GiST (Generalized Search Tree) index type in PostgreSQL, making it a versatile and powerful custom index solution.

Working Mechanism

SP-GiST divides the search space into separate regions, each with a label for efficient navigation. The split algorithm is used to divide the regions optimally, considering data distribution and size.

SP-GiST can handle complex data types, making it suitable for various applications. It's also highly customizable, allowing developers to adjust it according to their needs. Moreover, SP-GiST performs better with high-dimensional data, improving query efficiency and database performance.

Conclusion

SP-GiST is a useful custom index in PostgreSQL, specially designed to boost performance with complex data types. Its space partitioning technique and split algorithm make it efficient and easily adaptable. Developers can use SP-GiST to optimize their PostgreSQL databases for top-notch performance across various applications.

PostgreSQL indexing: Hash Index

Sadeed Ahmad — Tue, 25 Jul 2023 11:18:11 +0000

PostgreSQL offers a wide array of options for database indexing, hash indexing being one of them. In this article, we'll explore the fundamental theory and structure of hash indexing and its implementation in PostgreSQL.

A hash index is a database indexing method that utilizes a hash function to map keys to corresponding values. Hashing is a technique to associate a small number with a value of any data type to access data for situations where keys are relatively small and the data set remains static, in PostgreSQL.

Architecture of Hash Index

Meta Page is the initial page in the hash index and holds essential information about the index's contents. It also has Bucket Pages which are the primary pages of the index, where data is stored in the form of "hash code - TID" pairs. TID stands for "tuple identifier" and helps locate the actual data in the table. Overflow Pages have a structure similar to bucket pages and are used when a single page is insufficient to hold all the data for a specific bucket. Finally, Bitmap Pages maintain a record of overflow pages that are currently clear and can be reused for other buckets, improving the efficiency of the index.

Hash Index in PostgreSQL

Hash indexing in PostgreSQL uses the hash function to calculate an integer hash code for each value in the indexed column. At first, the hash index contains a small number of buckets, but this dynamically increases as the index grows to accommodate data efficiently. When inserting data into the hash index, the hash code determines the specific bucket where the data is stored. The data is organized as "hash code - TID" pairs, with the TID serving as a pointer to the actual data location in the table.

If different values produce the same hash code, PostgreSQL uses either open addressing or chaining. Open addressing involves searching for the next available bucket in a specific sequence until an empty one is found, while chaining stores multiple values in the same bucket or overflow pages. Bitmap pages are employed to manage the allocation of overflow pages effectively.

Hash indexing is most effective for static datasets with relatively small keys, providing rapid data access for read-intensive workloads in PostgreSQL.

Understanding AgensSQL Functionalities

Sadeed Ahmad — Mon, 24 Jul 2023 10:47:08 +0000

Graph databases are used for storing and performing queries on interconnected data with complex relationships between entities. Graph databases use nodes, edges, and properties to represent and store data.

AgensSQL adds these capabilities to PostgreSQL which allows the developers to use similar syntax to perform queries and operations on the data stored in a relational database.

Functionalities of AgensSQL

With the help of AgensSQL graph nodes and edges can be created in PostgreSQL tables. Furthermore, queries that are very similar to SQL can be utilized to retrieve specific patterns in the graph.
This lends us the additional capability to perform graph analytics and gain insights from the connected data and navigate through the relationships in it.

Practical Implementation of AgensSQL

Organizations can use AgensSQL effectively by implementing its capabilities to manage and analyze highly interconnected data efficiently.

Complex Relationships can be modelled using nodes and edges, which represent entities and the connections between them enabling the developer to provide assistance in data driven analytics tasks, such as finding shortest paths, identifying patterns, and detecting anomalies within the graph data. AgensSQL can be used alongside traditional relational data in PostgreSQL to provide both graph and non-graph data handling and storing capabilities at one compact platform.

AgensSQL can be really helpful in finding connections and relevancy in data to monitor user preferences. It can also be utilized to detect patterns that indicate any fraudulent activity.
It can be integrated with machine learning libraries and tools to enhance data analysis and enable predictive modeling on graph data even on real time data.

Organizations should invest in training and upskilling their teams on graph database concepts and AgensSQL's specific features in accordance with their use case.

PostgreSQL: An Introduction to Indexes

Sadeed Ahmad — Sat, 15 Jul 2023 19:03:20 +0000

Indexes serve as auxiliary structures within a database, serving two main purposes: enhancing data retrieval speed and enforcing integrity rules. Lets explore indexes in PostgreSQL in detail, look into the different types of indexes available, explain why there is such diversity, and see how they can be utilized to optimize query performance.

Index Types

As of version 9.6, PostgreSQL offers six built-in types of indexes. All index types associate a specific key with the corresponding table rows that contain that key. Each row is uniquely identified by a tuple id (TID), which comprises the block number within the file and the row's position within that block.

Any operation performed on indexed data, such as inserting, deleting, or updating table rows, necessitates updating the indexes for that particular table within the same transaction.

Extensibility of Indexes

PostgreSQL has implemented an interface in its general indexing engine to facilitate the easy addition of new access methods to the system. The primary purpose of this interface is to obtain tuple ids (TIDs) from the access method and perform some tasks. It reads data from the appropriate versions of table rows and retrieve row versions using individual TIDs or in batches using a prebuilt bitmap. It determines the visibility of row versions for the current transaction, considering its isolation level.

The indexing engine is involved in query execution and is invoked based on a plan generated during the optimization stage. The optimizer evaluates various ways to execute the query, taking into account the capabilities of all potential access methods.

By using the indexing engine, PostgreSQL ensures uniform handling of different access methods while considering their specific characteristics. The primary scanning techniques are index scan and bitmap scan. In an index scan, TID values are sequentially returned until the last matching row is reached, and the indexing engine accesses the table rows indicated by these TIDs. On the other hand, in a bitmap scan, all TIDs matching the condition are initially returned, and a bitmap of row versions is constructed based on these TIDs before reading the corresponding row versions from the table. The choice between these scanning techniques depends on factors such as the number of retrieved rows and is determined by the optimizer.

PostgreSQL: GIST Indexes

Sadeed Ahmad — Thu, 13 Jul 2023 13:39:12 +0000

PostgreSQL, a widely used open-source relational database management system. One of its notable capabilities is its ability to handle a wide array of types of indexes, including the Generalized Search Tree (GIST) index for enhancing query performance within PostgreSQL.

GIST Indexes

GIST indexes are a specialized type which handle complex data types like geometric shapes, full-text search, and network addresses in PostgreSQL. They are structured as balanced search trees, capable of handling intricate data structures. GIST indexes relies on the dividing the data into overlapping rectangles or boxes which contain data points. These boxes are then organized in a hierarchical tree structure.

During query execution, PostgreSQL navigates through the GIST index tree to locate the relevant data at the root node and moves down the branches based on the query conditions enabling GIST indexes to effectively handle queries with efficiency and accuracy.

How GIST indexes improve accuracy

GIST indexes play a crucial role in enhancing query performance by enabling PostgreSQL to swiftly locate the relevant data as now PostgreSQL can efficiently use the index to boost the process of identification of the required data instead of having to scan the entire table. GIST indexes can enhance the performance of full-text search queries, streamline network address queries, and optimize queries involving multi-dimensional data. By utilizing GIST indexes, PostgreSQL can efficiently handle these complex queries and deliver results more promptly.

Conclusion

GIST indexes in PostgreSQL offer substantial benefits for optimizing query performance involving complex data types and multi-dimensional data. With their ability to divide data into overlapping rectangles or boxes and establish a hierarchical tree structure, GIST indexes enable PostgreSQL to efficiently locate the necessary data without the need for complete table scans. By leveraging GIST indexes, you can significantly enhance query performance, maximize performance gains and optimize your database operations.

AgensSQL: An All-New Relational DBMS

Sadeed Ahmad — Tue, 11 Jul 2023 20:29:05 +0000

AgensSQL is a robust DBMS that is primarily based on PostgreSQL and it provides a wide range of effective data management functionalities. It empowers users by offering easy access to efficient data management capabilities. AgensSQL incorporates various features to secure the user data in the best way possible. These measures are in place to safeguard valuable information and maintain its confidentiality.

Additionally, AgensSQL presents an array of innovative features as an upgrade on its predecessors.

Enhanced Security

The protection of user data is a key focus for AgensSQL. By implementing data redaction, password policies: 1. user password policy reinforces login security 2. excessive failed login attempts lock an account 3. define rules for password complexity, and auditing capabilities, AgensSQL ensures that your valuable information remains safe and secure.

Data Sharding

AgensSQL's data sharding works great with large datasets. It allows for the distribution of data across multiple databases, enabling efficient management and analysis of huge amounts of information. This feature proves beneficial in handling the challenges posed by handling substantial data volumes.

High Availability

The Agens Enterprise Package encompasses Agens HA Manager (AHM), which guarantees uninterrupted operations. AHM is equipped with failover, failback, and load balancing capabilities, ensuring maximum availability and minimizing downtime allowing seamless transition and distribution of workloads

User-Friendly Management

Agens Enterprise Manager (AEM) provides a simplified approach to various tasks and streamlines the management of databases by offering a user-friendly interface that simplifies operations such as backups, audits, SQL monitoring, performance optimization, and scheduling.

Optimizing The Efficiency of Vacuum Processing - Visibility Map

Sadeed Ahmad — Mon, 19 Jun 2023 14:23:33 +0000

The concept of a visibility map is a feature introduced in PostgreSQL to optimize the efficiency of vacuum processing. It tracks the visibility information of individual data pages within a table.

The main purpose of the visibility map is to reduce the need for scanning entire tables during the vacuum process. When vacuuming a table, one of the primary tasks is to identify and remove dead tuples (rows) from the table's data pages. However, scanning every page of a table can be a time-consuming and resource-intensive operation, especially for large tables. The visibility map addresses this challenge by storing a bit for each data page in a separate map file. This bit represents the visibility status of the tuples on the corresponding data page. It can have two states: "All Visible" or "Some Tuples May Be Invisible."

During vacuum processing, instead of scanning every data page, PostgreSQL first checks the visibility map to determine if a particular page has all visible tuples. If the visibility map indicates that all tuples on a page are visible to all transactions, PostgreSQL can skip the scanning of that page altogether. This optimization significantly reduces the amount of I/O and processing required during vacuuming.

Freeze Processing

Freeze processing in PostgreSQL offers two modes, known as lazy mode and eager mode, which are chosen based on specific conditions.

By default, freeze processing operates in lazy mode, where it scans only the pages containing dead tuples using the visibility map (VM) of the target tables. On the other hand, eager mode is triggered under certain circumstances. In eager mode, freeze processing scans all pages, regardless of whether they contain dead tuples or not.

Improving Freeze Processing in Eager Mode

Earlier versions of the eager mode in PostgreSQL were inefficient as they would scan all pages, even if all the tuples within a page were already frozen.

To address this inefficiency, improvements were made to the visibility map (VM) and freeze process in later versions. The enhanced VM now includes information about whether all tuples within a page are frozen or not. Consequently, when freeze processing is executed in eager mode, pages that solely consist of frozen tuples can be skipped, resulting in improved efficiency.

Vacuum Processing in PostgreSQL

Sadeed Ahmad — Mon, 19 Jun 2023 13:05:34 +0000

Vacuum processing in PostgreSQL ensures the smooth operation of the database system. Its two primary tasks are removing dead tuples and freezing transaction IDs. For the removal of dead tuples, vacuum processing has two modes: Concurrent VACUUM and Full VACUUM.

Concurrent VACUUM, commonly referred to as VACUUM, eliminates dead tuples on a per-page basis, allowing other transactions to read the table concurrently. On the other hand, Full VACUUM removes dead tuples and performs defragmentation for the entire file, but during this process, other transactions are unable to access the tables.

Despite its significance in PostgreSQL, the advancement of vacuum processing has been relatively slow compared to other functionalities. Due to the requirement of scanning entire tables, vacuum processing can be resource-intensive.

First Block

This section of the process involves performing freeze processing and removing index tuples that point to dead tuples. At first, PostgreSQL scans the target table to create a list of dead tuples and potentially freeze old tuples. This list is saved in local memory. PostgreSQL uses the dead tuple list to remove corresponding index tuples once the scanning is complete. This step is called the "cleanup stage" and is resource-intensive.

Second Block

This block removes dead tuples and updates both the FSM and VM on a page-by-page basis.

Third Block

After the deletion of indexes, the third block carries out the cleanup process, that updates the relevant statistics and system.

After post-processing the process is considered to be completed.

Network Address Data Types in PostgreSQL - Part 2

Sadeed Ahmad — Wed, 14 Jun 2023 09:20:01 +0000

In continuation of the last blog, further data types for storing IPv4, IPv6, and MAC addresses in PostgreSQL are discussed. Rather than utilizing basic text types, it is better to use these specific types for storing network addresses.

macaddr

The macaddr type is designed to store MAC addresses also known as Media Access Control addresses, are identifiers assigned to network interfaces.

The macaddr type in PostgreSQL allows you to store MAC addresses in a compact manner. The standard format of six groups of two hexadecimal digits is followed, separated by colons or hyphens. For example, a MAC address may look like "08:00:2b:04:02:05" or "08-00-2b-01-02-03".

The macaddr type provides several benefits. First off, it ensures that the stored values are in the correct format. This helps prevent input errors when working with MAC addresses. It also provides specialized operators and functions for manipulating and comparing MAC addresses, making it easier to perform operations such as sorting and searching based on MAC addresses.

MAC addresses are commonly associated with Ethernet networks. They can also be used for other purposes, such as in Bluetooth devices and Wi-Fi networks.

macaddr8

An extension of the macaddr type in PostgreSQL macaddr8 was introduced in PostgreSQL version 10 to address the limitation of the original macaddr type, which only supported 48-bit MAC addresses.

It is designed to store 64-bit MAC addresses which provides a larger address space to accommodate more unique identifiers. The format is similar to macaddr, with eight groups of two hexadecimal digits. For example, "00:0a:95:9d:28:12:23:52" or "00-0A-45-9D-64-12-23-22".

By using macaddr8, PostgreSQL allows for the storage, manipulation, and comparison of 64-bit MAC addresses. It offers ensuring data integrity and providing specialized operators and functions for working with MAC addresses.

To convert a traditional 48 bit MAC address in EUI-48 format to modified EUI-64 format to be included as the host portion of an IPv6 address, use macaddr8_set7bit as shown:

Network Address Data Types in PostgreSQL - Part 1

Sadeed Ahmad — Wed, 14 Jun 2023 08:44:45 +0000

PostgreSQL provides multiple data types for storing IPv4, IPv6, and MAC addresses. Instead of using basic text types, it is better to use these specific types for storing network addresses. The reason being the fact that these data types provide error validation during input and offer dedicated operators and functions for manipulating them.

inet

The inet type can store both IPv4 and IPv6 host addresses, along with their subnets, within a single field. The subnet is defined by the number of network address bits included in the host address, "netmask."

In the case of IPv4 addresses with a netmask of 32, it signifies a single host rather than a subnet. For IPv6, the address length is fixed at 128 bits, resulting in a unique host address when all 128 bits are specified.

It is recommended to use the cidr type instead of inet if you want to store network addresses and not individual hosts.

cidr

The cidr type in PostgreSQL is designed to store specifications of IPv4 or IPv6 networks. The input and output formats follow the conventions of Classless Internet Domain Routing (CIDR). The format follows the pattern of "address/y," where "address" represents the lowest address of the network, expressed as an IPv4 or IPv6 address, and "y" indicates the number of bits in the netmask.

If the "y" value is not provided, it is calculated based on assumptions derived from the older classful network numbering system. However, the calculated value will always be large enough to encompass all the octets specified in the input. It is important to note that specifying network addresses with bits set to the right of the specified netmask is considered an error and should be avoided.

The important difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not.

The Internals of PostgreSQL: Query Processing - Part 2

Sadeed Ahmad — Mon, 15 May 2023 13:06:05 +0000

We will be discussing rest of the three subsystems in detail in this article.

Rewriter
Planner
Executor

Rewriter

The rewriter is responsible for implementing the rule system and modifying a query tree based on the rules stored in the pg_rules system catalog, if required.

Planner

After receiving the query tree from the rewriter, the planner in PostgreSQL undertakes the task of generating a plan tree that can be executed by the executor in the most efficient manner.
It's important to note that the planner in PostgreSQL exclusively relies on cost-based optimization and does not support rule-based optimization or hints.
A plan tree is composed of elements called plan nodes, and it is connected to the plantree list of the PlannedStmt structure. These elements are defined in plannodes.h.

Executor

The executor in PostgreSQL relies on information provided by each plan node to carry out the processing tasks. In the case of a single-table query, the executor processes the plan tree from the end to the root.

For instance, consider the plan tree illustrated for a simple plan tree above, consisting of a sort node followed by a sequential scan node. In this scenario, the executor scans the table "tbl_a" using a sequential scan and subsequently sorts the obtained result.
To interact with tables and indexes within the database cluster, the executor utilizes the buffer manager. During query processing, the executor makes use of pre-allocated memory areas such as temp_buffers and work_mem, and creates temporary files as necessary.
Moreover, PostgreSQL employs a concurrency control mechanism to ensure consistency and isolation of running transactions when accessing tuples.

The Internals of PostgreSQL: Query Processing - Part 1

Sadeed Ahmad — Mon, 15 May 2023 12:50:07 +0000

Query processing is a critical aspect of any database management system, and PostgreSQL excels in this domain with its efficient and versatile query processing capabilities. In this article, we delve into some valuable insights to gain a comprehensive understanding of PostgreSQL's query processing mechanisms.

Backend Subsystems

We cover the first two systems in this part 1.

Parser
The parser generates a parse tree from an SQL statement in plain text.

Analyzer
The analyzer carries out a semantic analysis of a parse tree and generates a query tree.

Parser

The parser in PostgreSQL is responsible for transforming an SQL statement written in plain text into a parse tree. This parse tree serves as a structured representation of the SQL statement, allowing subsequent subsystems to interpret and analyze it. The root node of the parse tree is the SelectStmt structure, defined in parsenodes.h.

The numbering of elements in the SELECT query and the corresponding parse tree elements are identical. For instance, the first item in the target list, which is the column 'id' of the table, is numbered (1), while the WHERE clause is numbered (4), and so on.
The parser is responsible for checking the syntax of the input query when generating a parse tree. Therefore, it only reports an error if there is a syntax error in the query.
However, the parser does not verify the semantics of the input query. For example, if the query contains a table name that does not exist, the parser does not report an error. The analyzer is responsible for performing semantic checks.

Analyzer

After the parser generates a parse tree, the analyzer performs a semantic analysis and produces a query tree.
The Query structure, which is defined in parsenodes.h, serves as the root of the query tree. This structure contains metadata about the query.

Here is a brief description of the query tree:
The targetlist is a list of columns that represent the output of the query. In this example, the targetlist consists of two columns: 'id' and 'data'. If the input query tree uses the '*' (asterisk) symbol, the analyzer/analyser will replace it with all of the columns explicitly.
The range table is a list of relations that are used in the query.
The join tree stores the FROM and WHERE clauses.
The sort clause is a list of SortGroupClause.