DEV Community: Hrishikesh Mallick

Graph Database Breakthroughs

Hrishikesh Mallick — Sun, 14 May 2023 14:48:43 +0000

Graph databases are an 18th century concept with a host of modern applications.

Used for tasks as diverse as dating sites and fraud detection, graph technology works by looking at relationships, not just data. But the idea behind them – or, at least, their theoretical basis – is attributed to Swiss-born mathematician Leonhard Euler, in 1735.

For almost 300 years, graph theory remained a mostly academic pursuit. But graphs have turned out to be an ingenious way of dealing with large volumes of data, and especially complex relationships between data.

In recent years, technologists have taken graph theory and created the graph database, a type of database where connections, as well as data, are first class citizens.

Graph databases are designed to efficiently store and query connected data by using a node and relationship-based format, making them particularly equipped to solve problems when understanding those connections are critical.

One of the key advantages of graph databases is that they can mimic the way the human brain processes and understands associations. By representing data as nodes and relationships, graph databases provide a more intuitive and natural way of working with connected data.

By recording links between data, as well as data itself, graph-based systems can quickly mine information and identify trends, making them a powerful tool for real-time analytics, as well as for mapping social networks, supply chain patterns, or even crime waves.

As a graph database looks at connections and relationships – known as edges – it takes just minutes, or even seconds, to answer queries that might take days using a conventional database system.

Graph databases (and specialized versions called native RDF triplestores that embody reasoning power) show great promise in knowledge discovery, data management and analysis. They reveal simplicity within complexity. When combined with text mining, their value grows tremendously. As the database ecosystem continues to grow, as more and more connections are formed, as unstructured data multiplies with fury, the need to analyze text and structure results inside graph databases is becoming an essential part of the database ecosystem. Today, these combined technologies are available and not just reserved for the big search engines providers. It may be time for you to consider how to better store, manage, query and analyze your own data. Graph databases are the answer.

It seems that it is only a matter of time before other industries that deal with huge amounts of data such as banking and finance, pharmaceuticals, defense and intelligence will also be using graph databases. In fact, detecting crimes and identifying insurance fraud with the help of networks, relationships and entities with graph data is sure to be an interesting task.

Apache AGE has taken a new type of approach to implement Graph database with PostgreSQL underlying.
Apache AGE
Apache AGE Github

Graph Query processing

Hrishikesh Mallick — Tue, 09 May 2023 18:18:45 +0000

Graphs are data model abstractions that are becoming pervasive in several real-life applications and practical use cases. In these settings, users primarily focus on entities and their relationships, further enhanced with multiple labels and properties to form the so-called property graphs. The processing of property graphs relies on graph queries that are used to extract and manipulate entities and relationships.
Databases are widely used for structured and
complicated data management including string data,
stream data, video, images, trees, and graphs. Graph
data is more complicated and general structure and it is
widely used to picture combination of proteins and
compare their structure, relationship networks,
medicine design, social networks, road networks, video indexing, web information computer
vision, pattern detection, and chemical/biological
informatics. Searching graph to extract required
information is one of the main fields of graph mining. In most of the cases, value and applicability of a
graph data application depends on performance of its graph query. This is one of the key issues in graph
mining field. The preliminary graph query
techniques extracted all isomorph, super-graphs by a
graph query in the database. Clearly, this technique is not
so efficient and like isomorphism test, sequential scan of
each database graphs needs great processing and storage
resources. To accelerate graph query process, therefore,
we need to index the database.
Central to graph analytics, is the need to locate patterns
in dataset graphs. Informally, given a query graph, the system is called to identify which of the stored graphs in its dataset contain it (subgraph matching), or are contained in it (supergraph matching). This is a very costly operation
as it entails the NP-Complete problem of subgraph isomorphism and even its most popular solutions are computationally very expensive. This problem is exacerbated when dealing with datasets storing large numbers
of graphs, as the number of required subgraph isomorphism
tests grows. Furthermore, performance deteriorates significantly with increasing graph sizes.
It is extremely important to understand how a database stores the data at the storage layer. The native graph stores like Neo4j, TigerGraph etc., stores both nodes and relationships data in fixed size data structures. This is called index free adjacency, whereby, each node maintains reference to the adjacent nodes. This speeds up storage processing and the computational cost is O(1). In contrast the non native datastores normally use a NoSQL or RDBMS as the underlying datastore and the adjacency list is stored as a JSON file as a name value pair. In order to speed up graph traversal, global indexes are often used to link nodes together, resulting in greater computational cost O(logn). So long the dataset is small, the underlying graph technology is not going to matter much, but in case the number of nodes are very high (millions to billions) native graph data stores will outperform non natives in real time storing, querying and traversing the graph.

Graph computing or Graph databases are not new concepts. They had been there for the last 30 years or so. But, the last few years have seen exponential growth in interest around the topic especially after it has been established that the graphs are indeed the most efficient mechanism to solve some real life problems of the interconnected world viz. community detection, anomaly detection, fraud analytics, recommendations etc. Today, more than 7 of the top 10 retailers use graph databases to drive recommendations, promotions and streamlining logistics. Some of the largest insurers use graph databases to fight frauds and manage risks. Companies like Google and Facebook have built their businesses on top of some graph algorithms. So these algorithms will further improve and faster graph query processing may be anticipated.

Apache AGE is a modern PostgreSQL extension which allows Graph Database features and querying. Take a look at:
Apache AGE
Apache AGE Github

Data Modelling

Hrishikesh Mallick — Wed, 12 Apr 2023 12:06:42 +0000

Data modeling is defined as the central step in software engineering that involves evaluating all the data dependencies for the application, explicitly explaining (typically through visualizations) how the data be used by the software, and defining data objects that will be stored in a database for later use.

How Data Modeling Works?

Data modeling is the process of making a visual representation of all or part of an information system to show how different data points and organizational structures are linked. The goal is to explain the different types of data that are used and stored in the system, how different types of data are connected, how data can be grouped and organized, and what its formats and features are.

When making data models, business needs are taken into account. Rules and requirements are designed with the help of feedback from business stakeholders before they are added to the design of a new system or changed during an iteration of an existing one.

A data model is similar to a flowchart since it visually represents how data entities are related, their various attributes, and the nature of the data entities themselves.

Thanks to data models, data management and analytics teams can discover mistakes in development plans and describe the data requirements for apps, even before any code is created. As an alternative, data models can be produced by efforts to extract them from current systems through reverse engineering.

Data modeling is a crucial IT discipline for any organization. When building an app, it depicts 360-degree data dependencies and preempts bottlenecks. It helps maintain data-driven cloud services like e-commerce and provides better user experiences. It also keeps enterprise data repositories up-to-date so that you can extract the most valuable insights. By knowing the different types of data models, data modeling techniques, and best practices, one can unlock its full potential.

Data Modelling is very usefully implemeted in Apache AGE and AGE Viewer project:
Apache AGE
Apache AGE Github

Graph Databases vs NoSQL

Hrishikesh Mallick — Wed, 12 Apr 2023 11:25:34 +0000

NoSQL Databases:
NoSQL databases (aka "not only SQL") are non-tabular databases and store data differently than relational tables. NoSQL databases come in a variety of types based on their data model. The main types are document, key-value, wide-column, and graph. They provide flexible schemas and scale easily with large amounts of data and high user loads.

Most NoSQL systems are aggregate-oriented, grouping the data based on a particular criterion and the database type (such as document store, key-value pair, etc). This model provides only simple, limited operations and only forms one dedicated view of your data. Focusing on one aggregate at a time allows users to easily spread many chunks of data across a network of machines along the aggregate dimension (for instance, the Document in document databases), but that means that other projections and perspectives have to be computed by crunching or duplicating your data.

Graph Databases:
A graph in a graph database can be traversed along specific edge types or across the entire graph. In graph databases, traversing the joins or relationships is very fast because the relationships between nodes are not calculated at query times but are persisted in the database. Graph databases have advantages for use cases such as social networking, recommendation engines, and fraud detection, when you need to create relationships between data and quickly query these relationships.

Graph databases are purpose-built to store and navigate relationships. Relationships are first-class citizens in graph databases, and most of the value of graph databases is derived from these relationships. Graph databases use nodes to store data entities, and edges to store relationships between entities. An edge always has a start node, end node, type, and direction, and an edge can describe parent-child relationships, actions, ownership, and the like. There is no limit to the number and kind of relationships a node can have.

Graph databases, on the other hand, handle fine-grained networks of information, providing any perspective on your data that fits your use case. The well-known and trusted transactional guarantees from relational systems also protect updates of the graph data in Neo4j, conforming to ACID standards.

Want to extract all the benefits of PostgreSQL along with Graph features look at:
Apache AGE
Apache AGE Github

When to use Graph Databases?

Hrishikesh Mallick — Wed, 05 Apr 2023 18:56:01 +0000

Being multi-relational by nature, Graph Databases are opposed to relational ones, which have loosely affiliated tables.

Navigating relationships is quick because they are already stored in the database and don’t need to be calculated. Data is saved in the same way that thoughts are scribbled on board.

The information is stored, thus letting you conceive and use it in a variety of ways. In contrast, relational databases can’t handle the quantity, pace, and diversity of current data spreading.

Acknowledging ways for NoSQL databases to address these issues is merely the first step in determining which database is best for your business.

Considering the fact that there are a variety of database approaches, and available tools for each of them, searching for the right solution could be tough.

But if the ranking by the types of databases could somehow help you to decide, you can find it here.

Graph databases are anticipated to surpass other types of databases, especially the still-dominant relational database. Even though they are not suitable for each and every app idea.

A graph database is suitable for any environment that enables social networking. This concept may be applied to any project where you’d have to find out people’s behaviour in order to suggest new offerings for them.

Besides, these types of databases enable to define the shortest route between entities.

Hence, they are commonly used for social networks, platforms with communities, logistics, and spatial dimensions (for example, in real-time maps, flight booking platforms, or Transportation Management Systems).

Tech giants like Google, Facebook, LinkedIn and PayPal all tapped into the power of graph databases to create booming businesses. Their secret? They each used graph database technology to harness the power of data connections.

A graph database is purpose-built to handle highly connected data, and the increase in the volume and connectedness of today’s data presents a tremendous opportunity for sustained competitive advantage.

References:
Apache AGE
Apache AGE Github

Graph Databases vs Relational Databases

Hrishikesh Mallick — Mon, 03 Apr 2023 06:19:53 +0000

Graph Databases:

The main components of a property graph database are as follows:

Node: also known as a vertex in graph theory – the main data element from which graphs are constructed
Relationship: also known as an edge in graph theory – a link between two nodes. It will have direction and a type. A node without relationships is permitted, a relationship without two nodes is not permitted
Label: Defines a node category, a node can have more than one
Property: Enriches a node or relationship, no need for nulls!

Relational Database:

Data is stored in tables within a well-defined schema.
Each row in the table is a discrete entity of data. One of these elements in the row is typically used to define its uniqueness: the primary key. It could be a unique ID or maybe something like a social security number for a person.

We then go through a process called normalization to reduce data repetition. In normalization, we’re moving references, something like an address for a person, into another table. So we get a reference from the row representing the entity to the row representing the address for that person.

If, for example, somebody changes their address, you wouldn’t want multiple versions of that person’s addresses everywhere and have to try and remember all the different instances of where that person’s addresses exist. Normalization makes sure you have one version of the data, so you can make the updates in one place.

What are the differences between a graph database and a relational database?
As we described above, graph databases and relational databases have different ways of storing and retrieving data.

Data modeling Relational databases are dependent on tables, and use a predefined schema to organize data. Within a relational database, entities are the focus of the data model.

A graph database on the other hand, stores data as nodes and edges. The data model allows for a flexible schema and the ability to easily traverse and query relationships, making it well suited for data that has complex relationships. Indeed, relationships are the star of the show in a graph data model.

Query language
Query languages for the two types of databases also differ. Relational databases use SQL (Structured Query Language) to query the data, while graph databases use a specific query language such as Gremlin or Cypher. Graph query languages tend to be faster to write than SQL.
Performance
Graph databases offer some key benefits over relational databases depending on your use case. Because of their flexible data model, they are more scalable than relational databases. Their more effective indexing also means they tend to offer a better performance with faster querying.

References:
Apache AGE
Apache AGE Github

PostgreSQL Inheritance

Hrishikesh Mallick — Thu, 30 Mar 2023 14:50:27 +0000

PostgreSQL has table inheritance feature. This concept is from object-oriented databases. In PostgresSQL a table can inherit from one or more other tables. Child table inherits column(s) along with all check constraints and not null constraints from one or more parent tables.
Inheritance allows a table to inherit some of its column attributes from one or more other tables, creating a parent-child relationship. This causes the child table to have each of the same columns and constraints as its inherited table (or tables), as well as its own defined columns.
When performing a query on an inherited table, the query can be instructed to retrieve either all rows of a table and its descendants, or just the rows in the parent table itself. The child table, on the other hand, will never return rows from its parent.

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- (in ft)
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

In this case, a row of capitals inherits all attributes (name, population, and altitude) from its parent, cities. The type of the attribute name is text, a native PostgreSQL type for variable length ASCII strings. The type of the attribute population is float, a native PostgreSQL type for double precision floating-point numbers. State capitals have an extra attribute, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables, and a query can reference either all rows of a table or all rows of a table plus all of its descendants.

Table inheritance is, to be sure, a power tool and thus something to use only when it brings an overall reduction in complexity to the design. Moreover, the current documentation doesn't provide a lot of guidance regarding what the tool actually helps with and where are the performance costs and because inheritance sits orthogonal to relational design, working this out individually is very difficult.

When a table is queried, by default, all child tables are also queried and their results appended to the result. Because of exclusion constraint processing, this takes out an ACCESS SHARE lock on all child tables at planning time. All rows are cast back to the type of the table target (in other words you get the columns of the table you queried).

Inheritance can be extended to use in a lot of places as per the user's requirement and thus is one of the most powerful features of PostgreSQL.

Want to extract all these type of benefits of PostgreSQL along with Graph Database features look at:
Apache AGE
Apache AGE Github

PostgreSQL special features

Hrishikesh Mallick — Thu, 30 Mar 2023 11:00:17 +0000

Point-in-time recovery

PostgreSQL enables developers to use PITR (Point-In-Time Recovery) to restore databases to a specific moment in time when running data recovery initiatives. Because PostgreSQL maintains a write ahead log (WAL) at all times, it logs every database change. This makes it easy to restore file systems back to a stable starting point.

Third-party tools like pgBackRest (link resides outside ibm.com) make this easier to do and more reliable; most cloud-managed PostgreSQL services will handle this for you automatically.

Stored procedures

PostgreSQL features built-in support for multiple procedural languages, giving developers the ability to create custom subroutines called stored procedures. These procedures can be created and called on a given database. With the use of extensions, procedural languages can also be used for development in many other programming languages, including Perl, Python, JavaScript, and Ruby.

Deep language support

PostgreSQL is one of the most flexible databases for developers due to its compatibility and support of multiple programming languages. Popular coding languages such as Python, JavaScript, C/C++, Ruby, and others offer mature support for PostgreSQL, letting developers perform database tasks in whichever language they are proficient in without generating system conflicts.

Table Inheritance

Want to extract all the benefits of PostgreSQL along with Graph Database features look at:
Apache AGE
Apache AGE Github

Use Cases of Graph Databases (Part 2)

Hrishikesh Mallick — Mon, 13 Mar 2023 14:41:15 +0000

Impact on Supply Chains:

Among the lasting effects of the coronavirus pandemic has been the realization that global supply chains can be alarmingly fragile. With or without disruption, manufacturers are acutely aware of how complicated many supply chains are to maintain and optimize.

Consider the day-to-day challenges faced by auto manufacturers. The first requirement is to accurately forecast customer demand to determine the number and types of parts to order — down to the various models and options buyers are expected to choose. Those predictions need to sync with the availability of parts from hundreds of suppliers, along with estimates of manufacturing efficiency and supplier risk.

Jaguar Land Rover (JLR) chose a graph database solution because it could span the many data silos that needed to be tapped for supply chain analysis — and explore the matrices of relationships among data elements. The primary goals were to increase the average profit per unit sold and to reduce aged inventory, along with minimizing the effects of supplier disruption. Some key supply-chain planning queries at JLR now take 45 minutes as opposed to weeks and, more importantly, management can answer questions it never had the opportunity to ask before.

Improving the accuracy of fraud detection:

We’ve all witnessed the evolution of fraud detection through our bank, credit card and telecom companies. Early rule-based efforts tended to miss dubious transactions or flag innocent transactions as fraudulent. When the financial industry adopted graph databases to augment their AI/ML efforts, however, the accuracy of fraud detection improved noticeably.

Graph databases coupled with AI/ML improve the accuracy of fraud detection, reducing false positives and detecting anomalies that might otherwise be missed. Machine learning must draw on many different data types to model a customer’s normal behavior — location, device, payment type, authentication method and so on. Plus, what’s defined as normal behavior patterns must be adjusted on the fly in response to legitimate change. Graph databases support that dynamism and enable AI/ML to traverse customer interactions to identify significant variances.

Financial services giants JP Morgan Chase and Intuit have both adopted graph databases to boost their AI/ML fraud detection efforts. JP Morgan Chase uses a graph database to help protect more than 60 million households in the U.S. According to Intuit, graph-based machine learning has enabled the company to detect 50% more potential fraud events and has reduced false positives by approximately the same percentage.

Want to extract all the benefits of PostgreSQL along with Graph features look at:
Apache AGE
Apache AGE Github

Use Cases of Graph Database (Part 1)

Hrishikesh Mallick — Mon, 13 Mar 2023 14:32:59 +0000

Gaining all round information from Customer Data:

Interactions between companies and their customers or sales prospects tend to be complex, with many touchpoints. Ideally, these should yield sales strategies that continuously adapt to customer needs. Such 360-degree scenarios quickly incur many-to-many relationships that, using a relational database, would require laborious modeling and cumbersome table joins to yield actionable insights.

This is the sort of situation where a graph database shines. UnitedHealth Group (UHG), for example, has adopted a graph database to help improve the quality of care for over 26 million members while reducing costs. The largest healthcare company in the world by revenue, UHG uses a massive graph database to track more than 120 billion relationships among members, providers, claims, visits, prescriptions, procedures and more.

Producing great impact in the Finance Industry:

The exponential growth of data has been the biggest enabler of AI/ML, which requires large quantities of data to surface meaningful patterns and improve the accuracy of decision-making. Few industries are more data-intensive than financial services, but as with other industries, data originates from many different sources and typically ends up in relational database silos.

In bridging those silos, graph databases can help AI/ML deliver superior predictive analytics, risk management, fraud detection, anti-money laundering, insider-trading monitoring, automated recommendations for customers and more. Also, a graph database coupled with AI/ML can help ensure data is clean in the first place, reconciling anomalous differences in customer records and financial product attributes that could lead to inaccurate results.

Improving E-Commerce operations:

Retail ecommerce firms face growing competitive pressure to deliver better customer experiences built on accurate customer details and purchase histories. That foundation enables everything from dynamic pricing to product recommendations to personalized special offers, all of which draw on data accrued along the customer journey.

Graph databases can help in a number of ways. Consider the possible relationships — between customers and payment methods, customers and brands, products and return rates, promotions and sell-through rates, and a whole lot more. Say you wanted to run a query to determine which promotions were most effective for a certain product when pitched to a subset of customers defined as loyal. With a relational database that would take a long time, but a graph database can return the results with very little latency.

Want to extract all the benefits of PostgreSQL along with Graph Database features look at:
Apache AGE
Apache AGE Github

PostgreSQL Index Types

Hrishikesh Mallick — Mon, 27 Feb 2023 12:45:08 +0000

Postgres Index:

Indexes are their own data structures and they’re part of the Postgres data definition language (the DDL). They're stored on disk along with data tables and other objects.

There are five types of PostgreSQL indexes, which are also called methods, because they define the way each particular index handles its task. They also determine the syntax specifics. These five types are as follows:

B-tree indexes are the most common type of index and would be the default if you create an index and don’t specify the type. B-tree indexes are great for general purpose indexing on information you frequently query.
B-Tree is the default index type for the CREATE INDEX command in PostgreSQL. It is compatible with all data types, and it can be used, for instance, to retrieve NULL values and work with caching. B-Tree is the most common index type, suitable for most cases.
BRIN indexes are block range indexes, specially targeted at very large datasets where the data you’re searching is in blocks, like timestamps and date ranges. They are known to be very performant and space efficient.
BRIN (Block Range Index) applies to large tables where specific columns have a natural correlation with their physical location in the table. A block range is a group of pages that are physically adjacent in the table; BRIN indexes store summary information—page number along with minimum and maximum values—for each block range.
GIST indexes build a search tree inside your database and are most often used for spatial databases and full-text search use cases.
The GiST (Generalized Search Tree) index allows using the tree structure to index schemes for new data types—for instance, geometric data types and network address data. GiST is also useful if you have queries that are not indexable with B-Tree. It is applicable for full-text search as well.
GIN indexes are useful when you have multiple values in a single column which is very common when you’re storing array or json data.
GIN (Generalized Inverted Index) is suitable for mapping multiple values to one row. The most common case for applying the GIN index comprises operations with data types such as arrays, range types, JSON, and also for full-text search. Note that the GIN index will be slower for INSERT and UPDATE operations
Hash is a specific index type applied only if the equality condition = is being used in the query. It is called hash because it stores a 32-bit hash code derived from the indexed column value. Hash indexes are rarely used in PostgreSQL because it is necessary to rebuild them manually after crashes, and they can cause issues during transactions.

Want to extract all the benefits of PostgreSQL along with Graph features look at:

Apache AGE
Apache AGE Github

PostgreSQL Indexing

Hrishikesh Mallick — Sun, 26 Feb 2023 19:17:32 +0000

What are Indexes?

The role of database indexes is similar to the index section at the back of a book. A database index stores information on where a data row is located in a table so the database doesn't have to scan the entire table for information. When the database has a query to retrieve, it goes to the index first and then uses that information to retrieve the requested data. For example, if the names in a contact book are not alphabetized, you would have to go down every row and search through every name before you reach the specific phone number that you are searching for. An index speeds up the SELECT commands and WHERE phrases, performing data entry in the UPDATE and INSERT commands. Regardless of whether indexes are inserted or deleted, there is no impact on the information contained in the table. Indexes can be special in the same way that the UNIQUE limitation helps to avoid replica records in the field or set of fields for which the index exists.

Postgres Index:

Indexes are their own data structures and they’re part of the Postgres data definition language (the DDL). They're stored on disk along with data tables and other objects.

General Syntax:

The following statement is used to create an index on a particular table on the specified column or field.

CREATE INDEX index_name ON table_name (column_name);

What makes Indexing so useful?

Indexing doesn’t change the existing document/table it is being applied to (unless it is clustered indexing which I’ll discuss later in the blog), it instead creates a new data structure that has two blocks for every entry. These two blocks are the:

Data Field on which index is created
Pointer to the Row/Document where that data field is stored in Database(Address)

Testing Index Performance:

To test if indexes will begin to decrease query times, you can run a set of queries on your database, record the time it takes those queries to finish, and then begin creating indexes and rerunning your tests.

To do this, try using the EXPLAIN ANALYZE clause in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = 7;

The output will tell you which method of search from the query plan was chosen and how long the planning and execution of the query took.

Important Guidelines for PostgreSQL indexes:

The default Postgres index is a B-tree index.
You should always properly analyze your workload using query execution plans to determine the suitable Postgres index type.
Always create indexes on the most executed and costly queries. Avoid creating an index to satisfy a specific query.
As per best practice, always define a primary or unique key in a Postgres table. It automatically creates the B-tree index.
Avoid creating multiple indexes on a single column. It is better to look at which index is appropriate for your workload and drop the unnecessary indexes.
There is no specific limit on the number of indexes in the table; however, try to create the minimum indexes satisfying your workload.

Index Types:

B-tree indexes
Hash indexes
GIN indexes
BRIN
GiST Indexes

A detailed discussion on these indexes would be covered on the next article.

Want to extract all the benefits of PostgreSQL along with Graph features look at:

Apache AGE
Apache AGE Github