DEV Community: GhostVaibhav

But, what is a graph database?

GhostVaibhav — Tue, 26 Sep 2023 17:59:58 +0000

Umm..., as the name suggests a graph database consists of a graph. Obviously! However, the actual data is stored in the vertices and their relationships are represented as edges. That is what forms the graph. This graph could be previewed in many databases including AgensGraph. Getting this graph preview for a database storing millions of vertices and billions of edges may not mean anything, as you mentally can't think about the scale of that data. While you can't, computers can! So, in this article, I will be discussing some advantages of these graph databases and their use cases.

1. Schema-less

As the title itself suggests, a graph database doesn't have a fixed schema. Since the graph databases store data as objects instead of inserting them into tables, the capacity is increased and various formats are accepted.

A large amount of data can be stored. Also, the data element can be both independent and dependent, allowing for better operability. With this, the graph databases are quite flexible in their design. For example, consider adding a new column to an existing table, you would need to know the underlying structure of the database, the foreign key, any constraints, views, etc. With graph databases, inserting a vertex is as simple as it can get. You can also easily connect as well as disconnect it from any number of vertices. Furthermore, these vertices can have single or multiple labels associated with them for even more granular control.

2. Query Processing Speed

The conventional databases need to execute a join for displaying relationships between any two tables. This is a very costly operation. Also, if we have to have more depth to it, like, we have to multiple join operations, the complexity would just multiply.

This is where the graph databases take over the conventional databases. Finding the relationships in a graph database is much simpler and faster in comparison since they are purpose-built for this kind of use case. The difference becomes more pronounced when executing deep-level joins.

3. Intuitive Modeling

The heading itself describes the whole problem. When we take a real-life problem, we have to model it in a whole new way just to fix it through conventional database systems. While this is doable, when the use cases increase, the complexity rises steeply. Graph databases excel in this field as well. Whatever the real-world problem may be, you will always be able to convert it into a graph database and represent it through a set of vertices and edges.

On an ending note, I would like to clarify that, graph databases are built for specific use cases. If your use case lies close to those, consider switching over to them, else, you can always fall back to the industry standard conventional databases.

This was all for the article. Until then, see ya!

From console to rows - the processing of a query in Postgres

GhostVaibhav — Mon, 25 Sep 2023 15:52:37 +0000

The journey of a query is quite exciting and complicated. As always, this will be a beginner-friendly post so everyone can follow. I would also attach some material that you can look after for an even more in-depth understanding of the topic.

Whenever you type a query in the console, it goes through the following steps for execution -

Parser
Analyzer
Rewriter
Planner
Executor

We will be looking at these from a high level.

1. Parser

For any language, the parser has only one functionality and that is to parse the tokens (or generate some meaning from the statements). That is exactly what the parser does in Postgres as well. It creates a parse tree from the query.

You might ask, what is a parse tree? Well, it is just a tree that contains several fields of a base statement. That base statement could be anything like SELECT, UPDATE, ALTER, etc. Let's take the SELECT statement as an example. For a SELECT statement, you have to specify a table in the FROM clause. This is stored in a list named List *fromClause. Similarly, other parameters are stored respectively.

A very important note to mention here is that the parser only checks for any syntactical mistakes and not semantic mistakes. Let's take an example.

Consider there is a table named students. Then, the following query would return an error in parsing stage -

SELECT * students;

But, the following query won't -

SELECT * FROM studentsz;

All the semantic checking happens in the Analyzer stage.

2. Analyzer

The main purpose of the analyzer is to semantically check the parse tree and generate the query tree. This structure also contains the metadata regarding the query. Each leaf forms a list or a tree that specifies the other values in that particular clause. It converts the tree from kind of a statement tree to a query tree. That's pretty much what it does. Let's move on to the next one.

3. Rewriter

Rewriter as the name suggests rewrites the query tree according to the rules defined in the pg_rules system catalog. This mechanism is complicated as well as interesting. So, if you want to further read, I will give some links at the end. VIEWS are generated and checked through the rewriter system.

4. Planner and Executor

The planner makes a query plan tree from the query tree and the executor executes it. The planner is a very complicated system on its own. It uses cost-based optimization for generating the query plan tree. Also, no planner hints are supported in Postgres. Again, this is a very vast topic that can be explored deeply. For a beginner, only the former information is sufficient to know how a query is executed.

In the end, I would like to add that these subsystems are no different than compilers. All of the processes that are performed in a compiler are also performed here.

For further reading, I have attached the materials below -

Thanks for reading and until then, see ya!

Memory behind Postgres

GhostVaibhav — Fri, 22 Sep 2023 04:18:17 +0000

Without diving a lot into the intricacies of the underlying architecture of Postgres, we'll be looking at it from the top.

Memory architecture in Postgres is classified into two broad categories:

Local Memory Area
Shared Memory Area

As the name suggests, the local memory area is used by backend processes for their use. While the shared memory area is shared between all the processes.

Now, let's talk about the local memory area.

Local Memory Area

Each backend process allocates a memory area for query processing.

These areas are further divided where their sizes are either fixed or variable. Some areas are discussed below -

Sub-Area	Description
work_mem	The executor uses this area for sorting tuples by `ORDER BY` and `DISTINCT` operations. It also uses it for joining tables by merge-join and hash-join operations.
maintenance_work_mem	This parameter is used for some kinds of maintenance operations (`VACUUM`, `REINDEX`).
temp_buffers	The executor uses this area for storing temporary tables.

Shared Memory Area

A shared memory area is allocated by a PostgreSQL server when it starts up.

These areas are also further divided into various sub-sections that are further discussed below -

Sub-Area	Description
Shared buffer pool	PostgreSQL loads pages within tables and indexes from persistent storage to a shared buffer pool, and then operates on them directly. In itself, this is a huge topic and can become very complicated if we delve into all the types and sizes of the buffers.
WAL buffer	PostgreSQL supports the WAL (Write ahead log) mechanism to ensure that no data is lost after a server failure. WAL data is a transaction log in PostgreSQL and the WAL buffer is a buffering area of the WAL data before writing it to a persistent storage.
Commit log	The commit log (CLOG) keeps the states of all transactions, and is part of the concurrency control mechanism. The commit log is allocated to the shared memory and used throughout transaction processing.

These are not the only areas where the memory is allocated, memory is allocated for the following as well -

Sub-areas for the various access control mechanisms
Sub-areas for the various background processes
Sub-areas for transaction processing, and others

This post was just a beginner-friendly way to introduce to the readers about the memory architecture of the Postgres server. Also this, in no way is complete and the following could be referred to for further reading -

https://www.youtube.com/watch?v=PM1rqdCavo0

Until then, thank you for your time and see ya!