DEV Community: Hannan2910

Apache AGE: Unique Use Cases

Hannan2910 — Sat, 30 Sep 2023 16:21:07 +0000

Introduction

Imagine merging the advantages of graph databases with those of regular databases. Apache AGE performs that. With Apache AGE, a popular PostgreSQL extension, users can explore data relationships in greater depth than previously. Here are some reasons why it will revolutionise the database industry.
Here are some distinct use cases that showcase its power:

Route Optimization

Apache AGE can be used by city planners to improve the flow of public transport. Planners can create routes that shorten travel times and ease traffic congestion by examining the connections between bus stops, train stations, and traffic patterns.

Genome Sequencing Analysis

Biotechnologists can use Apache AGE to comprehend the complex connections between several genes. By tracing these relationships, scientists can identify the genes that cause particular conditions or features.

Analysis in CRM Systems

Businesses with complex client relationships, such as B2B enterprises, can utilise Apache AGE to visualise the web of connections between various stakeholders, decision-makers, and organisations. This can aid sales teams in better strategizing.

Multilayered Security Breach Analysis

Security companies can utilise Apache AGE to track a security breach's route through many systems. Experts can more accurately predict the attacker's next step and strengthen defences by comprehending the connections between penetrated nodes.

Data Linking in Archives

By connecting similar papers, images, and artifacts from many eras, historians and archivists can use Apache AGE to provide a more thorough understanding of historical events.

Interconnected IoT Device Analysis

Apache AGE can assist in examining the connections between various IoT devices as smart homes and cities grow more common. This can help with energy consumption optimisation, anomaly detection, and device failure prediction.

Ecosystem Analysis

An ecosystem's relationships can be mapped out by environmentalists to better understand food chains, breeding cycles, and the effects of environmental factors like climate change.

Conclusion

The resilience of PostgreSQL and the graph capabilities of Apache AGE work together to provide a special approach to problems involving complex data relationships. The potential applications range widely and include everything from route planning to environmental investigations.

AGE: Coexistence of Graph and Relational Databases

Hannan2910 — Wed, 27 Sep 2023 15:22:51 +0000

Introduction

AGE acts as a link between two different worlds contained within a same database system. This blog examines the intriguing qualities of AGE and how it can transform the way we manage and get meaning from intricate data interactions. With AGE, you can combine the comfort of relational databases with the strength of graph databases to provide a unified and flexible solution for your data management needs.

AGE was created to give PostgreSQL, one of the most popular relational database management systems (RDBMS), access to graph databases' features. Lets see how we can use full power of AGE and Postgres to make a database which can handle both relational tables and graphs at the same time.

Unified Data Management

Through the usage of openCypher, a widely used graph query language, and ANSI SQL for structured data, users of AGE are able to quickly manage both relational and graph data models within a single database. These two technologies are seamlessly merged into a single database.

The Strength of PostgreSQL

AGE is based on the robust PostgreSQL architecture and benefits from its power and feature set. This includes support for multi-version concurrency control (MVCC), stored procedures, triggers, restrictions, and more. ACID (Atomicity, Consistency, Isolation, Durability) transactions are also included. To guarantee data confidentiality and integrity, AGE makes use of PostgreSQL's well-established dependability.

Effortless Integration

Making the switch to AGE is simple, especially for users who are accustomed to relational databases. No major reconfiguration or difficult data migrations are required. A hassle-free deployment of graph database capabilities is made possible by AGE, which effortlessly incorporates graph data analytics into your current PostgreSQL system.

Conclusion

Inshort not only can you use AGE for converting postgres into a graph database you can also use the full funcionality of postgreSQL without compromising any database or relations.

How to Shard your Database using Citus

Hannan2910 — Fri, 15 Sep 2023 13:50:49 +0000

Introduction

In previous blog we learned about sharding and its types, todays blog will be rather simple but technical as we will learn how to apply both types of sharding on our databases.

Schema-Based Sharding

By turning on the citus.enable_schema_based_sharding parameter, you can immediately begin using schema-based sharding. Once enabled, any schema you produce will be a "distributed schema".

-- Enable schema-based sharding!
set citus.enable_schema_based_sharding to on;

-- Create distributed schemas for two of my tenants
create schema tenant1;
create schema tenant2;

-- See my distributed schemas
select * from citus_schemas;

schema_name  | colocation_id | schema_size | schema_owner
-------------+---------------+-------------+--------------
 tenant1     |             3 | 0 bytes     | marco
 tenant2     |             4 | 0 bytes     | marco
(1 row)

It is now automatic for any table generated in the tenant1 or tenant2 schema to become a "single shard" table that is "co-located" with all other tables in the schema. Single shard tables in Citus lack a distribution column in contrast to hash-distributed tables. Each table only has one shard, and all shards belonging to the same schema reside on the same node.

-- Create single shard tables in schema tenant1 (automatically co-located)
set search_path to tenant1;
create table note_categories (
   category_id bigserial primary key,
   category_name text not null);

create table notes (
   note_id bigserial primary key,
   category_id bigint references note_categories (category_id),
   message text not null);

-- Create single shard tables in schema tenant2 (automatically co-located)
set search_path to tenant2;
create table note_categories (
   category_id bigserial primary key,
   category_name text not null);

create table notes (
   note_id bigserial primary key,
   category_id bigint references note_categories (category_id),
   message text not null);

You can see the tables where they are in the citus_shards.

select table_name, shardid, colocation_id, nodename, nodeport, shard_size 
from citus_shards where citus_table_type = 'schema';


       table_name       | shardid | colocation_id | nodename  | nodeport | shard_size
------------------------+---------+---------------+-----------+----------+------------
tenant1.note_categories |  102310 |             3 | wrk1.host |     5432 |      16384
tenant1.notes           |  102311 |             3 | wrk1.host |     5432 |      16384
tenant2.note_categories |  102312 |             4 | wrk2.host |     5432 |      16384
tenant2.notes           |  102313 |             4 | wrk2.host |     5432 |      16384
(4 rows)

Row-Based Sharding

In Row-Based Sharding you need to call distinct citus function to disrtibute your tables in your schema. after creating your citus cluster you use the follwing to distribute your table
The create_distributed_table UDF will transparently shard your table locally or across the worker nodes:

CREATE TABLE events (
  device_id bigint,
  event_id bigserial,
  event_time timestamptz default now(),
  data jsonb not null,
  PRIMARY KEY (device_id, event_id)
);

-- distribute the events table across shards placed locally or on the worker nodes
SELECT create_distributed_table('events', 'device_id');

To enable high performance distributed joins and foreign keys between distributed tables, distributed tables with the same distribution column can be co-located. The co-location of distributed tables is determined by the type of the distribution column by default, but you can specify co-location specifically using the colocate_with option in the create_distributed_table function.

CREATE TABLE devices (
  device_id bigint primary key,
  device_name text,
  device_type_id int
);
CREATE INDEX ON devices (device_type_id);

-- co-locate the devices table with the events table
SELECT create_distributed_table('devices', 'device_id', colocate_with := 'events');

You can use create_reference_table to replicate a table across all cluster nodes when you require quick joins or foreign keys without the distribution column.


CREATE TABLE device_types (
  device_type_id int primary key,
  device_type_name text not null unique
);

-- replicate the table across all nodes to enable foreign keys and joins on any column
SELECT create_reference_table('device_types')

Conclusion

Now we have learned how to set up a distributed realational database using citus. I hope this helps in understanding the basic use of the exteansion. Thats all for this time.

Sharding in Citus & it's types

Hannan2910 — Thu, 14 Sep 2023 17:53:14 +0000

Introduction

High-performance and scalable databases are essential in the constantly changing world of application development. Traditional monolithic databases frequently fail to meet the requirements of contemporary applications, particularly those that are expanding quickly. Database sharding, a technology that distributes data across numerous servers to enhance performance and scalability, is one creative response to this problem. Row-Based Sharding and Schema-Based Sharding are two common sharding techniques that will be discussed in this article. We'll also go over how to switch to Citus, a robust PostgreSQL extension that makes sharding easy, and how to choose the best sharding strategy for your application.

Sharding in a Nutshell

Database sharding is a technique for managing and horizontally splitting data to improve database performance. The data is split up into smaller, easier to handle chunks known as "shards" via sharding rather than depending on a single, enormous database server. The workload is distributed among the individual servers or nodes where each shard resides, enhancing query performance.

Row-Based Sharding

Row-Based Sharding, sometimes referred to as Horizontal Sharding, is a sharding method that divides data according to the value of a particular column, usually the main key. This technique makes sure that data is distributed evenly among shards and that each shard has about the same amount of data. Applications dealing with very big databases, such IoT or time series data, are well suited for row-based sharding. To ensure effective data dissemination, however, extra data modelling processes, such as adding a "tenant ID" column to all tables, may be necessary.

Schema-Based Sharding

Schema-based sharding differs in that it organises tables from the same schema on the same node. This approach reduces network cost by allowing a single node to perform queries and transactions involving a single schema effectively. The system can scale out without difficulty because distinct schemas can live on different nodes. Schema-Based Sharding has the fewest limits on data modelling and is a great option for applications where distinct components of the data model can reside in multiple schemas. However, only tables that are part of the same schema should be involved in joins and foreign keys.

Choosing the Right Sharding Model

The choice between Row-Based Sharding and Schema-Based Sharding depends on your specific application requirements:

Row-Based Sharding: Opt for this model if you have a smaller number of large tenants (B2B) and are comfortable with additional data modeling steps. It's suitable for scenarios where very large tables, parallel distributed queries, and distributed DML (Data Manipulation Language) are required.
Schema-Based Sharding: Consider this approach if your application naturally divides into distinct groups of tables, such as multi-tenant applications (schema per tenant), microservices (schema per microservice), or vertical partitioning (grouping related tables into schemas). Schema-Based Sharding offers ease of use and is ideal for applications with a very large number of small tenants (B2C).

Conclusion

In conclusion, Citus Schema-Based Sharding offers a powerful solution for scaling and optimizing your database. Whether you opt for Row-Based or Schema-Based Sharding, the key is to align the sharding model with your application's specific needs. With Citus, you can seamlessly transition to a distributed database environment and harness the scalability and performance your application demands to thrive in today's competitive landscape.

Installing Citus From Source Code

Hannan2910 — Sat, 02 Sep 2023 07:26:15 +0000

Introduction

In this blog we will be installing citus on postgresql. You can follow the official documentation but if you have complied postgresql through its source code this tutorial can help you install citus on it easily.

Step 1: Installing PostgreSQL

Before installing citus we need a compatible version of postgresql. For this purpose we will install postgresql 15 from source code.

Creating directories to install postgresql

mkdir citus_installation
cd citus_installation
mkdir pg
cd pg

Installing dependencies for postgreSQL

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison

Downloading and unpacking PostgreSQL 15

wget https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.gz && tar -xvf postgresql-15.3.tar.gz && rm -f postgresql-15.3.tar.gz

Installation

Next, we will proceed with the installation of "pg". The "--prefix" option specifies the directory path where we want to install "psql". In this instance, we will be installing it in the current directory using "pwd".

cd postgresql-15.3

# configure by setting flags
./configure --enable-debug --enable-cassert --prefix=$(pwd) CFLAGS="-ggdb -Og -fno-omit-frame-pointer"

# now install
make install

# go back
cd ../../

Step 2: Installing Citus

Now we will install citus on our PostgreSQL. This is a rather easy and smooth process.

Dependencies:

Before installing citus you need to install all the build dependecies for citus

sudo apt-get install -y postgresql-server-dev-15 postgresql-15 \
                        autoconf flex git libcurl4-gnutls-dev 
libicu-dev \
                        libkrb5-dev liblz4-dev libpam0g-dev libreadline-dev \
                        libselinux1-dev libssl-dev libxslt1-dev libzstd-dev \
                        make uuid-dev

Downloading Citus

After that you can clone the citus repo

git clone https://github.com/citusdata/citus.git
cd citus

Configuration And Installation

Then run the following commands to configure and install

PG_CONFIG=/home/yourusername/citus_installation/pg/postgresql-15.3/bin/pg_config ./configure
make
sudo make install

Setup in PostgreSQL

Then to add Citus to your local PostgreSQL database, add the following to postgresql.conf:

shared_preload_libraries = 'citus'

Conclusion:

Now you have successfully installed citus with a source code compiled postgreSQL. Have fun enjoying the power of scaling databases and using realtime analytics with PostgreSQL.

The Power of Docker: Benefits and Installation

Hannan2910 — Thu, 31 Aug 2023 08:01:07 +0000

Introduction

Docker has become a game-changing technology in the field of software development and deployment, revolutionising how programmes are created, delivered, and used. A platform for containerization called Docker offers a wide range of advantages that improve the effectiveness, consistency, and dependability of development and deployment procedures.

Docker

Docker is an open-source platform that enables developers to package an application and its dependencies into a standardized unit called a container. These containers are isolated environments that include everything needed to run the application, from code and runtime to system tools and libraries. This means that applications can be easily moved between different environments, ensuring consistent behavior regardless of where they are deployed.

Benefits of Docker

Isolation and Consistency: By encapsulating applications and their dependencies, containers make sure that they function consistently in various settings. Because the containerized programme acts consistently during development, testing, and production stages, the dreaded "it works on my machine" problem is eliminated.
Resource Efficiency: Docker containers share the host OS kernel, in contrast to traditional virtualization, which requires a separate operating system for each virtual machine. This significantly lowers overhead and resource usage, enabling more effective use of system resources.
Rapid Deployment: Docker containers may be set up or taken down in only a few seconds, making it possible to scale applications quickly. This flexibility is very useful given the current development environment's quick speed.
Rollbacks and version control: Docker containers are created from images, which are snapshots of a particular configuration. Version control, simple rollbacks to past states, and improved team cooperation are all made possible by this.
Microservices Architecture: Docker works nicely with microservices-based systems. Complex applications can be managed, scaled, and maintained more effectively with the independent containerization of each microservice.

Installation

We will install docker on linux in this blog

Step#1

Update and Install required dependencies.

sudo apt-get update
sudo apt-get install ca-certificates curl gnupg

Step#2

Add GPG key.

sudo install -m 0755 -d /etc/apt/keyrings
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
sudo chmod a+r /etc/apt/keyrings/docker.gpg

Step#3

Setup the docker repo on your machine.

echo \
  "deb [arch="$(dpkg --print-architecture)" signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu \
  "$(. /etc/os-release && echo "$VERSION_CODENAME")" stable" | \
  sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

Then update the apt package index.

sudo apt-get update

Now our setup is complete now we just need to install latest version of docker

sudo apt-get install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Install Check
To check if your docker has been successfully installed run this command.

sudo docker run hello-world

Apache AGE: Bridging Relational and Graph Database Worlds with PostgreSQL

Hannan2910 — Sat, 26 Aug 2023 09:37:32 +0000

Introduction

Understanding relationships within data is essential in today's data-centric environment. Enter graph databases, which were created with the explicit purpose of locating and mapping complicated relationships in huge datasets. Let's first understand the fundamental idea before delving into Apache AGE's role in this context.

Introduction to Graph Databases

In essence, graph databases store, map, and query relationships using graph topologies. They are made up of edges (relationships) and nodes (entities). Graph databases are superior in this area compared to conventional relational databases, which often struggle to map complex relationships without many joins. They provide direct links to neighbouring nodes, which speeds up and effectively queries relationships. This design is especially useful for use cases where comprehending and making use of data relationships are crucial, such as social networks, knowledge graphs, and recommendation systems.

What is Apache AGE?

This relational database system is converted into a powerful graph database using the Apache AGE PostgreSQL extension. It flawlessly combines the advantages of a graph data model with PostgreSQL's robustness. Developers familiar with the well-liked graph database, Neo4j, will find it especially useful because it uses the openCypher query language.

Why Choose Apache AGE?

Versatility: Within a single database system, Apache AGE offers both relational and graph data models, giving users the best of both worlds.
Open Source Advantage: Because AGE is one of the projects managed by the Apache Software Foundation, it benefits from a cooperative, open, and dynamic environment.
Performance: Expect efficient querying, especially in the face of big datasets, thanks to PostgreSQL's optimisation and Apache AGE's design.

Challenges and Considerations

While there is no denying the appeal of Apache AGE, it is important to keep in mind that there are several types of graph databases. It's critical to assess the precise requirements of your application and to balance the advantages of doing so with any potential learning curve.

Conclusion

In the world of databases, Apache AGE represents a big development. It expands the possibilities for data modelling by combining the benefits of relational and graph databases, ensuring that complex interactions are no longer a problem but rather a benefit. The answer, as usual, is to match technology to the specific requirements of your application.

Citus: A Brief Introduction

Hannan2910 — Thu, 17 Aug 2023 12:01:20 +0000

Introduction

Businesses are continuously looking for methods to streamline processes, improve performance, and make informed decisions in the constantly changing world of data management. Citus is one such unique database management tool that has been gaining popularity as a result of this pursuit. Citus provides a unique approach for handling enormous amounts of data while preserving great performance and scalability. We will explore the fundamentals of Citus in this blog , comprehending its architecture, advantages, and use cases.

The Basics of Citus

One of the most common relational database management systems, PostgreSQL, has an open-source addition called Citus. Citus' capacity to successfully distribute data and queries over several nodes by horizontally scaling out relational databases is what makes it unique. Citus can manage enormous datasets and heavy query loads that might otherwise overload conventional single-node databases as a result.

Key Concepts and Architecture

Citus uses the sharding approach to accomplish its scalability. Large datasets are divided into manageable chunks, or "shards," and then distributed among a number of physical or virtual computers, also known as "nodes," through the process of sharding. Queries are intelligently sent to the relevant nodes, enabling parallel processing and faster query response times. Each node stores a part of the data.
A coordinator node controls the distribution of queries to the appropriate shards and serves as the entry point for queries. When dealing with complex queries and huge datasets, the worker nodes considerably improve performance and responsiveness by storing the data and doing queries in parallel.

Benefits of Citus

Scalability: Citus allows for smooth horizontal scaling, enabling enterprises to handle increasing data and query demands without compromising performance.

Performance: Citus takes advantage of parallel processing to speed up query execution times by distributing queries across numerous nodes.

High Availability: Citus has built-in failover and replication techniques that make sure that data is still accessible even in the case of node failures.

Ease of Management: Despite Citus' complicated underlying architecture, PostgreSQL users can adopt Citus and take advantage of its features quite easily because of Citus' compatibility with PostgreSQL.

Use Cases

Analytics: Citus excels in workloads requiring efficient querying and analysis of huge volumes of data, such as data warehousing and analytical applications.
Multi-tenant Applications: Applications that manage and segregate their data well can use Citus to serve many clients or customers.
Time-Series Data: Businesses that deal with time-series data, such the Internet of Things, financial services, and monitoring apps, can successfully store and analyse their data by utilising Citus.

Conclusion

For businesses struggling with the difficulties presented by large datasets and demanding complicated queries, Citus provides an efficient solution. Applications across multiple sectors now have more solutions thanks to its capacity to maintain performance and availability while horizontally scaling PostgreSQL databases. Businesses may decide whether Citus is the best fit for their data management needs by knowing the fundamentals of the software and its architecture.

Exploring Different Types of Joins in PostgreSQL

Hannan2910 — Thu, 10 Aug 2023 06:22:21 +0000

Introduction

Understanding the technique of connecting tables is essential for getting useful insights out of databases. PostgreSQL provides a range of join methods for relational databases to effectively merge data from various tables. We'll explore joins in this blog article, learn about their different kinds, and use real-world examples to explain each.

Joins in PostgreSQL

Since joins allow us to merge data from many tables based on shared columns, they form the foundation of relational database systems. PostgreSQL, an innovative open-source RDBMS, offers various forms of joins, each having a distinct function. Let's explore a few of the most typical join types.

Inner Join

The most common join type is probably the inner join, often called an equijoin. Only entries with matching values in the designated columns of both tables are retrieved. Think about an imaginary online store where you want to retrieve orders along with customer data.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Left Join

With a left join, all data from the left table is retrieved along with any matches from the right table. The result will have NULL values for the columns of the right table if there is no match in the right table. For situations like fetching products along with their reviews, this kind of join is helpful.

SELECT products.product_name, reviews.review_text
FROM products
LEFT JOIN reviews ON products.product_id = reviews.product_id;

Right Join

A right join, on the other hand, obtains every record from the right table as well as any matches from the left table. The result will include NULL values for the columns of the left table if there is no match in the left table. Although less frequent, this kind of join can be advantageous in some circumstances.

SELECT employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Full Outer Join

With a full outer join, all data from both tables is retrieved, and blank spaces are filled with NULL if there is no match. When you want to see a complete picture of the data, including mismatched records from both tables, this join type comes in handy. Consider that you wish to examine both product sales and returns.

SELECT sales.product_name, returns.return_reason
FROM sales
FULL OUTER JOIN returns ON sales.product_id = returns.product_id;

Conclusion

Learning PostgreSQL's many join types gives a world of opportunities for searching and analysing data across multiple tables. Understanding and using the appropriate join type is an essential ability for any database worker, and whether you need to acquire related information, incorporate unmatched records, or build thorough reports.

A Beginner's Guide to Installing Git, Using GitHub, and Uploading Code via Terminal

Hannan2910 — Sun, 23 Jul 2023 09:27:15 +0000

Introduction

Git and GitHub are effective tools that facilitate collaboration, streamline code management, and help developers in effectively maintaining their projects. In this blog post, I'm going to take you through the steps needed for installing Git, creating a GitHub account, and utilising the terminal for uploading your code.

Step 1: Installing Git

Git is a distributed version control system that gives programmers the tools they need to effectively manage codebases, collaborate on projects, and track code changes. Multiple people can work at once, encouraging effective cooperation and version control.
First of all check for any previous installations of git o n your device using

git --version

If it is installed you will see your installed git version.
If not installed you can see which command fits your OS and use it appropreiately

For macOS:

Homebrew: If you have Homebrew installed, type the following command in your terminal

brew install git

You can also use Xcode command line tools to install git as such

xcode-select --install

For Linux:

sudo apt-get update
sudo apt-get install git

For Windows:

Download the Git installer from the official website (https://git-scm.com/download/win) and follow the installation wizard's instructions.

Step 2: Creating a github account

If you don't have a GitHub account yet, visit https://github.com and sign up for a free account.

Step 3: Configuring Git

The following commands should be entered in your terminal after opening it, replacing "Your Name" and "your.email@example.com" with your actual information:

git config --global user.name "Your Name"
git config --global user.email your.email@example.com

Step 4: Create a new repository on GitHub

Log in using your GitHub credentials.
In the top right corner, select "New repository" by clicking the "+" symbol.
Select whether you want your repository to be visible to the public or only to you and then click "Create repository."

Now open the directory where your code exists and open a terminal there.

cd /path/to/your/code

Initialize a new Git repository in this directory.

git init

Add your code files to the staging area

git add .

All of the files in the current directory are added by the dot (.). Replace the dot with the names of the files you want to add.

Commit the changes to your local repository with meaningful messages

git commit -m "Initial commit"

Now we need to connect your local git repo with te one you created in your github account. For that you need to copy the github repository url from the address bar. Now run the following command by placing the url in it.

git remote add origin <your_url>

Now finally push your code to the github repo using this command.

git push -u origin master

The -u option sets the upstream branch, so you can use git push in the future.

Conclusion

You've learned how to use the terminal to upload your code to GitHub, set up a GitHub account, and installed Git successfully.
Hope this helps you in your development journey.

Getting Started With PostgreSQL: Basic Querries (Part 2)

Hannan2910 — Sat, 22 Jul 2023 19:28:25 +0000

Introduction

In this blog post, we'll continue our investigation of various PostgreSQL queries, starting with the UPDATE query and moving on to the DELETE, WHERE, ORDER BY, and LIMIT and OFFSET clauses at the end. Let's get started with some real-world examples to help you understand.

UPDATE Query

A table's existing records can be easily changed using to the UPDATE query.
The age of the user "john_doe" is now 35.

UPDATE users
SET age = 35
WHERE username = 'john_doe';

DELETE Query

You can efficiently delete records from a table using the DELETE query if specific requirements are satisfied.

Let's delete the user with the username "john_doe" from the "users" table

DELETE FROM users
WHERE username = 'john_doe';

WHERE Clause

In SELECT, UPDATE, and DELETE queries, the WHERE clause applies a filter to the data depending on predefined criteria.

Let's retrieve users who are above the age of 25

SELECT * FROM users
WHERE age > 25;

ORDER BY Clause

You can use the ORDER BY clause to arrange the result set in either ascending or descending order according to one or more columns.

Let's retrieve users from the "users" table sorted by age in descending order

SELECT * FROM users
ORDER BY age DESC;

LIMIT and OFFSET Clauses

While the OFFSET clause skips a predetermined number of rows, the LIMIT clause limits the amount of rows returned in the result set.

Let's get the first three users from the "users" table.

SELECT * FROM users
LIMIT 3;

Use the OFFSET clause to return the following three users while skipping the first three rows.

SELECT * FROM users
LIMIT 3 OFFSET 3;

Conclusion

Now that you've learnt about the fundamental PostgreSQL queries UPDATE to LIMIT and OFFSET. You may manage your PostgreSQL database effectively by manipulating data, filtering outcomes, and using these queries. Your ability to handle challenging data tasks will increase as you explore deeper into the world of databases and find even more powerful queries and features.Good Luck!

Debugging Apache AGE source code using VS Code

Hannan2910 — Fri, 21 Jul 2023 17:21:09 +0000

Hello, in this bog we will be learning how to add a debugger sowe can debug our apache AGE source code.

Today we will simply install the debugger and learn how to use it.

Prerequisites

AGE PostgreSQL Source code installed
PostgreSQL Source code installed (with debg enabled)
VSCode
GDB

Step 1: Starting your PostgreSQL server

First go to the directory where you postgreSQL is installed

cd posgresql-16beta1

Then initialize your DB cluster

bin/initdb demo

Start your server

bin/pg_ctl -D demo -l logfile start

Then create your database

bin/createdb demodb

Then to access the database run

bin/psql demodb

Step 2: Getting backend pid

Using this command to check the backend process ID where postgresql server is running.

SELECT pg_backend_pid();

Step 3: Setting up AGE in VS Code

Open The AGE source code you have installed and then mainly install the

C/C++ Plugin
C/C++ Extension Pack Plugin

Both are from microsoft

After this press F5 and you can edit the launch.json file.

Add the path to your postgres like this in the file and save it

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "name": "(gdb) Attach",
            "type": "cppdbg",
            "request": "attach",
            "program": "/home/hannanaamir/age16/postgresql-16beta1/bin/postgres",
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                }
            ]
        }


    ]
}

Step4: Start Debugging

Now again press F5 and now paste the pid of your server

You have now successfully enabled the debugger

Now in your code you can setup breakpoints and then load AGE in the sever

CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

Now you can see how the code runs and behave you can also run further querries and see how it behaves with them. I hope this helps.

Extra

To help with postgreSQL and AGE installation you can follow this blog https://imranzaheer.hashnode.dev/install-age-psql-from-source