DEV Community: Maruf13

How does RTE work?

Maruf13 — Mon, 12 Jun 2023 13:13:42 +0000

The RelTableEntry (RTE) data structure is commonly used in computer programming to represent a relationship between two entities in a relational database or other similar data models. It serves as a way to store and manage the connections or associations between different entities.

Here is a detailed explanation of how the RTE data structure works:

Purpose: The RTE is designed to capture the relationship between two entities in a database. For example, in a simple e-commerce system, you might have two entities: "Customers" and "Orders." The RTE would store the relationship between a specific customer and their corresponding orders.
Attributes: An RTE typically consists of attributes that describe the relationship between the entities. These attributes depend on the specific context and requirements of the system. In the e-commerce example, the RTE might contain attributes like "CustomerID," "OrderID," "Date," "Quantity," etc., to represent the customer's order.
Primary Keys: RTEs often use primary keys to uniquely identify each entry. In the e-commerce example, the primary key could be a combination of "CustomerID" and "OrderID" to ensure that each RTE represents a unique customer-order relationship.
Foreign Keys: The RTE also utilizes foreign keys to establish a connection with the corresponding entities. In the e-commerce example, the foreign keys would link the RTE to the specific customer and order entities by referencing their respective primary keys.
Data Integrity: The RTE helps enforce data integrity by providing referential integrity between entities. This means that an RTE cannot exist unless the associated entities it refers to also exist. In our example, an RTE representing a customer's order cannot exist unless both the customer and the order exist in the system.
Querying and Manipulation: The RTE allows for efficient querying and manipulation of relationship data. For example, you can retrieve all the orders placed by a particular customer by searching for RTEs with a specific "CustomerID." Similarly, you can modify or delete a specific RTE to update or remove a relationship.
Indexing: To improve performance, RTEs can be indexed based on their attributes. Indexing allows for faster searching and retrieval of relationship data. In our e-commerce example, you might create an index on the "CustomerID" attribute to speed up queries related to specific customers.
Storage: RTEs are typically stored in a database table or a similar data structure. Each row in the table represents an RTE entry, with columns corresponding to the attributes of the RTE. The database management system provides mechanisms for efficiently storing, managing, and querying the RTE data.

Overall, the RelTableEntry (RTE) data structure is a useful tool for representing and managing relationships between entities in a database or other data models. It enables efficient querying, data integrity enforcement, and facilitates the manipulation of relationship data.

`✔️ References:`

RelTableEntry (RTE): A Comprehensive Study

Maruf13 — Thu, 08 Jun 2023 04:34:19 +0000

RTE is a fundamental data structure used in relational database management systems (RDBMS). It represents a table entry or a record within a database table and contains metadata about the attributes (columns) of the table. This post explores the purpose, components, and significance of RTE in RDBMS.

`✔️ Purpose of RTE:`

RTE serves multiple purposes within the context of RDBMS. Its primary objectives include:
- Metadata Storage: RTE stores essential metadata about database tables, including attribute names, data types, constraints, and other properties.
- Query Optimization: RTE assists in query optimization by providing necessary information for cost estimation, join ordering, and access path selection.
- Data Management: RTE facilitates efficient data manipulation operations such as querying, inserting, updating, and deleting data, ensuring data integrity and consistency.

`✔️ Components of RTE:`

The RTE data structure typically comprises various components that collectively store information about a table and its attributes. The common components include:
- Table Name: The name of the table associated with the RTE.
- Attribute Information: RTE contains information about each attribute (column) of the table, including attribute name, data type, length, constraints, and indexing status.
- Access Permissions: RTE stores access permissions for the table, determining who can perform specific operations on it, such as read, write, or modify.
- Statistics: RTE may include statistical information about the table, such as cardinality, distribution, or histograms, which aids in query optimization.

`✔️ RTE in Query Planning and Execution:`

RTE plays a crucial role in the query planning and execution phases of an RDBMS. Its usage can be observed in various stages, including:

Query Parsing: During parsing, the RTE data structure is constructed based on the tables referenced in the query, capturing their metadata and attributes.
Query Optimization: RTE provides critical information used by the query optimizer to estimate costs, determine the most efficient join order, and select appropriate access paths.
Query Execution: During execution, RTE is used to access the relevant table's data, apply constraints and filters, and ensure data integrity while performing data manipulation operations.

`✔️ Significance of RTE:`

The significance of RTE lies in its contributions to efficient and effective database management. Some key aspects of its significance are:

Performance Optimization: RTE enables query optimization, leading to improved query execution times and resource utilization.
Data Integrity: RTE ensures data integrity by enforcing constraints and providing information on attribute types, lengths, and indexing.
Schema Management: RTE assists in managing the database schema by storing attribute metadata and access permissions for each table.
Access Control: RTE plays a vital role in access control, allowing administrators to define and enforce access permissions for different users or roles.

`✔️ RTE in Practice:`

RTE finds practical applications in various areas, including:

Relational Database Management Systems: RTE is a fundamental component of RDBMS, forming the backbone of data storage, retrieval, and management.
Query Optimization: RTE plays a crucial role in optimizing query performance, ensuring efficient execution of complex SQL queries.
Database Administration: RTE provides vital information for database administrators to manage schemas, optimize performance, and enforce security policies.
Data Warehousing and Analytics: RTE supports efficient data storage and retrieval in data warehousing and analytics environments, enhancing the performance of analytical queries.

`✔️ Limitations of RTE:`

Memory usage: RTE can consume a significant amount of memory, especially in large databases. This can be a problem for databases that are running on systems with limited memory.
Complexity: RTE can add some complexity to PostgreSQL applications. This is because developers need to be aware of how RTE works in order to use it effectively.
Overhead: RTE can add some overhead to PostgreSQL queries. This is because the PostgreSQL query planner needs to take RTE into account when planning queries.

RelTableEntry (RTE) is a fundamental data structure in relational database management systems. It captures metadata about table attributes, supports query optimization, and facilitates efficient data management operations. RTE's significance lies in its role in

`✔️ References:`

Methods of reading database Tables

Maruf13 — Sun, 28 May 2023 15:56:08 +0000

In this post, Two typical access methods, sequential scan and B-tree index scan, are outlined here:

`✔️ Sequential scan:`

All tuples in all pages are sequentially read by scanning all line pointers in each page. show below-

`✔️ B-tree index scan:`

An index file contains index tuples, each of which is composed of an index key and a TID pointing to the target heap tuple.

If the index tuple with the key that you are looking for has been found, PostgreSQL reads the desired heap tuple using the obtained TID value.

For example, In below image , TID value of the obtained index tuple is ‘(block = 7, Offset = 2)’. It means that the target heap tuple is 2nd tuple in the 7th page within the table, so PostgreSQL can read the desired heap tuple without unnecessary scanning in the pages.

`✔️ References:`

Join Operations In PostgreSQL

Maruf13 — Sat, 27 May 2023 17:00:15 +0000

In this post, we will discuss about various Join Operations in PostgreSQL. PostgreSQL supports three join operations: nested loop join, merge join and hash join.

Note that the three join methods supported by PostgreSQL can perform all join operations, not only INNER JOIN, but also LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN and so on:

`✔️ Nested loop join:`

The nested loop join is the most fundamental join operation, and it can be used in any join conditions. PostgreSQL supports the nested loop join and four variations of it. which are-:

Basic Nested loop join
Materialized Nested Loop Join
Indexed Nested Loop Join
With outer index scan

`✔️ Merge join:`

Unlike the nested loop join, merge join can be only used in natural joins and equi-joins.

The cost of the merge join is estimated by the initial_cost_mergejoin() and final_cost_mergejoin() functions.

As the exact cost estimation is complicated, it is omitted and only the runtime order of the merge join algorithm is shown. The start-up cost of the merge join is the sum of sorting costs of both inner and outer tables; Similar to the nested loop join, the merge join in PostgreSQL has Three variations.

Basic Merge Join
Materialized Merge Join
With outer index scan

`✔️ Hash join:`

Similar to the merge join, the hash join can be only used in natural joins and equi-joins. The hash join in PostgreSQL behaves differently depending on the sizes of the tables. If the target table is small enough (more precisely, the size of the inner table is 25% or less of work_mem), it will be a simple two-phase in-memory hash join; otherwise, the hybrid hash join is used with the skew method.

`✔️ References:`

Apache AGE Intro-2

Maruf13 — Tue, 16 May 2023 11:27:20 +0000

In this article, I have discussed about data types in Apache AGE. AGE uses a custom data type called agtype, which is the only data type returned by AGE. Below we have classified Simple Data Types in AGE:

`1. Null:`

In Cypher, null is used to represent missing or undefined values. Conceptually, null means ‘a missing unknown value’ and it is treated somewhat differently from other values. For example getting a property from a vertex that does not have said property produces null.
Not knowing two values does not imply that they are the same value. So the expression null = null yields null and not true.

SELECT *
FROM cypher('graph_name', $$
    RETURN NULL
$$) AS (null_result agtype);
A null will appear as an empty space.

Result:

null_result

`2. Integer:`

The integer type stores whole numbers, i.e. numbers without fractional components. Integer data type is a 64-bit field that stores values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Attempts to store values outside this range will result in an error.

The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally used only if disk space is at a premium. The bigint type is designed to be used when the range of the integer type is insufficient.

SELECT *
FROM cypher('graph_name', $$
    RETURN 1
$$) AS (int_result agtype);
Result:

int_result
1

`3. Float:`

The data type float is an inexact, variable-precision numeric type, conforming to the IEEE-754 Standard.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.

SELECT *
FROM cypher('graph_name', $$
    RETURN 1.0
$$) AS (float_result agtype);
Result:

float_result
1.0

`4. Numeric:`

The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with numeric values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on numeric values are very slow compared to the integer types, or to the floating-point type.

We use the following terms below: The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning “not-a-number”. Any operation on NaN yields another NaN. When writing this value as a constant in an SQL command, you must put quotes around it, for example UPDATE table SET x = ‘NaN’.

SELECT *
FROM cypher('graph_name', $$
    RETURN 1.0::numeric
$$) AS (numeric_result agtype);
Result:

numeric_result
1.0::numeric

`5. Bool:`

AGE provides the standard Cypher type boolean. The boolean type can have several states: “true”, “false”, and a third state, “unknown”, which is represented by the Agtype null value.

Boolean constants can be represented in Cypher queries by the keywords TRUE, FALSE, and NULL.

SELECT *
FROM cypher('graph_name', $$
    RETURN TRUE
$$) AS (boolean_result agtype);
Unlike Postgres, AGE’s boolean outputs as the full word, ie. true and false as opposed to t and f.

Result:

boolean_result

`6. String:`

Agtype strings String literals can contain the following escape sequences:

Escape Sequence Character
\t  Tab
\b  Backspace
\n  Newline
\r  Carriage Return
\f  Form Feed
\’    Single Quote
\”    Double Quote
\\  Backslash
\uXXXX  Unicode UTF-16 code point (4 hex digits must follow the \u)

Input/Output Format

SELECT *
FROM cypher('graph_name', $$
    RETURN 'This is a string'
$$) AS (string_result agtype);
Result:

string_result
“This is a string”

`✔️ References:`

PostgreSQL Query Processing

Maruf13 — Tue, 16 May 2023 11:01:21 +0000

In PostgreSQL, the parallel query uses multiple background worker processes. A backend process basically handles all queries issued by the connected client. This backend consists of five subsystems, as shown below:

`1. Parser:`

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

`2. Analyzer:`

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

`3. Rewriter:`

The rewriter transforms a query tree using the rules stored in the rule system if such rules exist.

`4. Planner:`

The planner generates the plan tree that can most effectively be executed from the query tree.

`5. Executor:`

The executor executes the query via accessing the tables and indexes in the order that was created by the plan tree.

`✔️ References:`

Apache AGE Intro

Maruf13 — Sun, 16 Apr 2023 19:00:11 +0000

Apache AGE is a PostgreSQL extension that provides graph database functionality. AGE is inspired by Bitnine’s fork of PostgreSQL 10, AgensGraph, which is a multi-model database. The goal of the project is to create single storage that can handle both relational and graph model data so that users can use standard ANSI SQL along with openCypher, the Graph query language.

`✔️ Graphs:`

A graph consists of a set of vertices/nodes and edges, which shows a map of properties. A node is independent block in the graph. An edge creates a directed connection between two vertices.

Create a Graph
To create a graph, use below function from ag_catalog namespace:

create_graph(graph_name); #Returns: void
Example:
SELECT * FROM ag_catalog.create_graph('graph_name');

Cypher Query Format
Cypher queries are-

cypher(graph_name, query_string, parameters) 
    #Return A SET OF records 
    #parameters is optional & by default NULL
Example:
SELECT * FROM cypher('graph_name', $$/* Cypher Query Here */ $$) 
AS (result1 agtype, result2 agtype);

Delete a Graph
To delete a graph, use the below function, form ag_catalog namespace:

drop_graph(graph_name, cascade); #Returns: void
Example:
SELECT * FROM ag_catalog.drop_graph('graph_name', true);

`✔️ References:`

Optimization Techniques in Apache AGE

Maruf13 — Sat, 15 Apr 2023 06:30:07 +0000

Apache AGE is a powerful tool for graph processing and analysis. Here are some techniques you can consider to optimize the performance of AGE and improve query performance:

`1. Schema Design:`

Optimizing your schema design is critical for good performance in any graph database, including AGE. Properly defining your vertex and edge properties, and organizing them in a way that reflects the nature of your data and queries, can greatly impact query performance.

`2. Indexing:`

Creating appropriate indexes on your graph data can significantly speed up query performance. AGE supports indexing on vertex and edge properties, and you can choose between different types of indexes, such as B-trees or GIN (Generalized Inverted Index), depending on your use case.

`3. Query Optimization:`

AGE supports various query optimization techniques, such as query planning and cost-based optimization. Make sure to optimize your queries to take advantage of these features, such as using the appropriate graph traversal methods, minimizing redundant calculations, and leveraging AGE's built-in query optimization capabilities.

`4. Data Loading:`

Efficiently loading data into AGE can also impact performance. Consider using batch loading techniques, such as bulk loading or parallel loading, to load data into AGE more efficiently and reduce overhead.

`5. Resource Allocation:`

Properly allocating system resources, such as CPU cores, memory, and storage, can have a significant impact on AGE's performance. Monitor and adjust resource allocations based on the size and complexity of your graph data, and consider leveraging distributed computing techniques if dealing with large-scale graphs.

`6. Hardware Optimization:`

Optimizing the hardware environment where AGE is running can also impact performance. Consider using high-performance storage systems, optimizing network configurations, and using hardware accelerators, such as GPUs, for certain graph processing tasks, if applicable.

`7. Avoiding Performance Pitfalls:`

There are some common pitfalls to avoid while optimizing AGE's performance. For example, avoiding excessively complex or deeply nested queries, avoiding unnecessary data duplication, and being mindful of memory usage can help improve performance.

`8. Monitoring and Profiling:`

Regularly monitoring and profiling the performance of AGE can help identify bottlenecks and areas for optimization. Leverage AGE's built-in monitoring tools, such as query profiling and system metrics, to gain insights into the performance of your graph database.

`9. Keep AGE Up-to-Date:`

AGE is an actively developed project, so make sure to keep your AGE installation up-to-date with the latest stable release to take advantage of bug fixes, performance improvements, and new features.

Optimizing AGE's performance requires a good understanding of your data, queries, and system environment. Experimenting with different optimization techniques and measuring their impact on query performance can help you identify the best strategies for your specific use case. Remember to thoroughly test any optimizations in a development or staging environment before deploying them in production.

Database Cluster Layout

Maruf13 — Tue, 28 Mar 2023 21:22:20 +0000

In this post, we will discuss the Cluster Database Layout of PostgreSQL.

The layout of database cluster has been shown below. Main files and subdirectories in a part of the document have been listed here:

In here, demo is a database cluster. Under the Database Cluster it has 6 files which are:

pg_hba.conf: Used for PosgreSQL's client authentication.
pg_ident.conf: Used for controlling PostgreSQL's user name mapping
PG_VERSION: Containing version number of PostgreSQL
postgresql.conf: Used for settin configuration parameters
postgresql.auto.conf: In ALTER SYSTEM, it is stored configuration parameters.
postmaster.opts: Record the last command line options for the server.

In here, demo has 17 directories which are:

base: Subdirectory containing per-database subdirectories. A database is a subdirectory under the base subdirectory; and the database directory names are identical to the respective OIDs. For example, when the OID of the database is 16384, its subdirectory name is 16384.
global: It contains cluster-wide tables, such as pg_database and pg_control.
pg_commit_ts: Subdirectory containing transaction commit timestamp data. Version 9.5 or later.
pg_dynshmem:Subdirectory containing files used by the dynamic shared memory subsystem. Version 9.4 or later.
pg_logical:Subdirectory containing status data for logical decoding. Version 9.4 or later.
pg_multixact:Subdirectory containing multitransaction status data (used for shared row locks)
pg_notify:Subdirectory containing LISTEN/NOTIFY status data
pg_replslot:Subdirectory containing replication slot data. Version 9.4 or later.
pg_serial:Subdirectory containing information about committed serializable transactions (version 9.1 or later)
pg_snapshots:Subdirectory containing exported snapshots (version 9.2 or later). The PostgreSQL's function pg_export_snapshot creates a snapshot information file in this subdirectory.
pg_stat:Subdirectory containing permanent files for the statistics subsystem.
pg_stat_tmp:Subdirectory containing temporary files for the statistics subsystem.
pg_subtrans: Containing subtransaction status data
pg_tblspc: Containing symbolic links to tablespaces
pg_twophase: Containing state files for prepared transactions
pg_wal: Containing WAL (Write Ahead Logging) segment files. It is renamed from pg_xlog in Version 10.
pg_xact: Containing transaction commit state data. It is renamed from pg_clog in Version 10.

`✔️ References:`

PostgreSQL Process Architecture

Maruf13 — Mon, 06 Mar 2023 23:00:26 +0000

In this post, we will discuss the Process Architecture of PostgreSQL. PostgreSQL is a client/server type relational database management system(RDMS) with the multi-process architecture and runs on a single host.

A collection of multiple processes cooperatively managing one database cluster is usually referred to as a 'PostgreSQL server', and its process tree will be show below figure:

`✔️ Postgres server process:`

It is a parent of all processes related to a database cluster management. Previously, it was called ‘postmaster’.
By executing the pg_ctl utility with start option, a postgres server process starts up.

Then, it allocates a shared memory area in memory, starts various background processes, starts replication associated processes and background worker processes if necessary, and waits for connection requests from clients.
After receiving a connection request from a client, it starts a backend process. After that, Backend process handles all queries issued by the connected client.
A postgres server process work with one network port, the default port is 5432. Although more than one PostgreSQL server can be run on the same host, then port number can be set as 5432, 5433 etc.

`✔️ Backend process:`

A backend process, which is also called postgres, is started by the postgres server process and handles all queries issued by one connected client.
It communicates with the client by a single TCP connection, and terminates when the client gets disconnected.
As it is allowed to operate only one database, you have to specify a database you want to use explicitly when connecting to a PostgreSQL server.
PostgreSQL allows multiple clients to connect simultaneously; the configuration parameter max_connections controls the maximum number of the clients (default is 100).
If many clients repeat the connection and disconnection with a PostgreSQL server, it increases both costs of establishing connections and of creating backend processes because PostgreSQL has not implemented a native connection pooling feature.
Such circumstance has a negative effect on the performance of database server. To deal with such a case, a pooling middleware (either pgbouncer or pgpool-II) is usually used.

`✔️ Background processes:`

Various background processes perform processes of each feature for database management. Below table shows a list of background processes.

`✔️ Replication associated processes:`

In the replication associated processes, they perform the streaming replication.
In Streaming Replication, three kinds of processes work cooperatively. A walsender process on the primary server sends WAL data to standby server; and then, a walreceiver and a startup processes on standby server receives and replays these data. A walsender and a walreceiver communicate using a single TCP connection.

`✔️ Background worker process:`

It can perform any processing implemented by users or to run user-supplied code in separate processes. Such processes are started, stopped and monitored by postgres, which permits them to have a lifetime linked to the server's status.
These processes are attached to PostgreSQL's shared memory area and have the option to connect to databases internally; they can also run multiple transactions serially, just like a regular client-connected server process.
Background workers can be initialized at the time that PostgreSQL is started by including the module name in shared_preload_libraries.
A module wishing to run a background worker can register it by calling RegisterBackgroundWorker(BackgroundWorker *worker) from its _PG_init() function.
Background workers can also be started after the system is up and running by calling RegisterDynamicBackgroundWorker function.

In the following figure, the details of the first three types of processes are shown:

The actual processes of a PostgreSQL server is shown below. In the following example, one postgres server process (pid is 9687), two backend processes (pids are 9697 and 9717) and the several background processes listed here:



postgres> pstree -p 9687

-+= 00001 root /sbin/launchd

 -+- 09687 postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

   |--= 09688 postgres postgres: logger process


   |--= 09690 postgres postgres: checkpointer process


   |--= 09691 postgres postgres: writer process


   |--= 09692 postgres postgres: wal writer process


   |--= 09693 postgres postgres: autovacuum launcher process


   |--= 09694 postgres postgres: archiver process


   |--= 09695 postgres postgres: stats collector process


   |--= 09697 postgres postgres: postgres sampledb 192.168.1.100(54924) idle


   --= 09717 postgres postgres: postgres sampledb 192.168.1.100(54964) idle in transaction

`✔️ References:`

Easy Installation of PostgreSQL, AGE & Age-viewer on Mac

Maruf13 — Sat, 25 Feb 2023 19:04:39 +0000

Follow below steps to install age correctly on macbook pro m1 or any other mac:

Create a folder where you want to store all source code. In this folder, you need 3 source code such as: (i) postgresql version 11/12 source code (ii) age source code (iii) age-viewer source code
(i) download postgresql from this link and store it on that folder.
(ii) download age from this link and store on that folder
(iii) download age-viewer from this link and store on that same folder.
goto postgresql folder & open terminal on it & run command like as:

./configure --enable-debug --enable-cassert --prefix=$(pwd) CFLAGS="-glldb -ggdb -Og -g3 -fno-omit-frame-pointer"
make install

those are the two command to install postgresql.

after that goto age folder & open terminal on it & run command like as:

sudo make PG_CONFIG=~/Your_base_folder_path/postgresql/bin/pg_config install
make PG_CONFIG=~/Your_base_folder_path/postgresql/bin/pg_config installcheck

goto postgresql folder & open terminal on it & run command like as:

bin/initdb demo

goto demo folder inside postgresql folder and open postgresql.conf file & we need add three words here such as:

port = 5432 /remove # from port number/

#search_path = 'ag_catalog,"$user", public' // addding 'ag_catalog' to this path

#shared_preload_libraries = 'age' //add age in inverted commas.

Now start the server and make a database named demodb

bin/pg_ctl -D demo -l logfile start

bin/createdb demodb

AGE added to postgresql successfully. Now we can enter in to pg_sql console to start testing.

bin/psql demodb

thats the process.

To install age-viewer, goto age-viewer folder and open terminal & run below command:

npm run setup

npm run start

then fill up the connection data like below:

url: localhost;

port: 5430;

username: sakibadnan; //use your username

# radom pass as password is not set for this user.
pass: 1234;

dbname: demodb;

References: