Knowledge Hub

Query Optimization means to find the fastest route to execute the query. During this process, multiple execution plans are generated and at the end the best one is executed.

Steps involved in this process.

• Query
• Parse & Translator
• Relational Algebra
• Optimizer
• Execution Plan
• Evaluation Engine
• Query Output

The core capability of Query Optimization is to search for an execution plan which can find the least time required for query evaluation.

Phases of Query Processing

• Parsing & Translation
• Optimization
• Evaluation

Example:

We normally submit queries in SQL format. In the first phase of Parsing & Translation, SQL query is translated in an internal form where the parser validates the syntax of the query and the relations of the query in the database and so on. Then it is converted into a relational algebra expression, something like below.

Query:
Select col1 from table1 where col1=’XYZ’

Relational Algebra Expression:

An Index is used to enhance the performance of database queries on tables. We can create as many indexes as possible we need or want on a particular table. Please note, in contrast, having more than required indexes can decrease performance as well.

Normally, an index is created when the database starts growing.

Why are indexes needed?

A famous example for better understanding; imagine walking into the Library of Congress and be given the task of finding a specific publication within 10 minutes. Would you be able to complete this task within the given time frame? The Library of Congress is considered the largest library in the world and it houses approximately 170 million items. Now, if you are a smart chap like me 😊, the first thing we would do is ask for access to the library’s index (which can be manual list or a list in a computer system but a list of names and locations of each book in the library) because indexes contain all the necessary information needed to access items quickly and efficiently. In the same manner, a database index contains all the necessary information to access data quickly and efficiently.

Partitioning is a data organization scheme in which table data is divided across multiple storage objects called data partitions according to values, in one or more table columns. Each data partition is stored separately. It consists of its data, indexes, configuration files, and transaction logs. For example, there is a Salary table with 10 years of data. It can be partitioned yearly. At the backend, at the storage level, this table will be divided into 10 smaller datasets i.e.,

1 for each year. Keynote here is, all partitions will be stored wherever the empty slots are, maybe 4 partitions including years 2 & 3 got stored in 1 node of 10 nodes-cluster. This is called Shared-Architecture. Now, a query for years 2 & 3 will go and hit only two partitions making it much faster but as both partitions were on 1 node so one node processing is used.

Partitioning Techniques

• Single-level Partitioning
  • Hash Partitioning
  • Range Partitioning
  • List Partitioning
• Composite Partitioning
  • Range–Range Partitioning
  • Range–Hash Partitioning
  • Range–List Partitioning
  • List–Range Partitioning
  • List–Hash Partitioning
  • List–List Partitioning

Sharding

We know what Partitioning is. Sharding has similar features with some architectural differences. It also divides tables into multiple smaller datasets but in contrast with Partitions, Sharding stores each Shard on one dedicated node, so it’s called Shared-nothing Architecture. Advantages of Sharding is, referring to the same example from the topic on Partitioning, when the query runs for Year 2 & 3, each node with each Shard will process each dataset individually so will be much faster as compared to standard Partitioning where many partitions can reside on one node in Shared Architecture.

Sharding Architectures

• Key Based: It is also known as hash based Sharding, which involves using a value taken from newly written data and plugging it into a hash function to determine which shard the data should go to.
• Range Based: It involves Sharding data based on ranges of a given value.
• Directory Based: One must create and maintain a lookup table that uses a shard key to keep track of which shard holds which data.

Normally, ACID and Insert/Update/Delete are mixed, but they have different functions and at the same time work together.

There are 4 traits of ACID i.e., atomicity, consistency, isolation, and durability.

• Atomicity: It makes sure data is completely written in a destination with no partial data left behind.
• Consistency: It makes sure that once an application performs an operation, the results of that operation are visible to every next query. For example, sometimes a user is reading a particular data and at the same time it might get updated or even may get deleted. This should not impact the user query consistency.
• Isolation: An incomplete operation by one user does not cause unexpected side effects for other users.
• Durability: Once an operation is completed, it will be preserved even in the event of the machine or system failure. For example, in databases there is a concept of Transaction Log which holds every query statement run in the database so in case data is lost, it can still be reproducible.

To start with, ACID and BaSE both are Data Consistency models which compete on a use case requirement basis. In the previous topic, we discussed ACID, let’s discuss BaSE. What is BaSE? Never heard about it right 😊.

The basic difference between ACID and BaSE models is the way they deal with this limitation.

• The ACID model provides a consistent system.
• The BASE model provides high availability.

‘ACID is about Atomicity, Consistency, Isolation and Durability.’

‘BaSE is about Basically Availability, Soft State and Eventually Consistent.’

There are 3 traits of BaSE i.e., basically available, soft state and eventually consistent.

• Basically Available: The system is guaranteed to be available in the event of failure.
• Soft State: Due to the lack of immediate consistency, data values may change over time. The BaSE model breaks off with the concept of a database that enforces its consistency, delegating that responsibility to developers.
• Eventually Consistent: The fact that BASE does not enforce immediate consistency does not mean that it never achieves it. However, until it does, data reads are still possible (even though they might not reflect reality).

Starting with DevOps, we all know Waterfall and Agile methodologies. We won’t go into details of these methodologies but for audience comfort, DevOps is mostly about operationalizing Agile processes where a set of practices combines software development (Dev) and IT Operations (Ops) to make i.e., DevOps.

‘DevOps is a physical face Agile.’

‘DevOps includes Plan, Create, Verify, Package, Release, Configure, Monitor, and Version Control.’

In DevOps, activities are mainly executed based on use cases, user stories and tasks, there are also other practices conducted by different organizations.

Engineers in DevOps independently accomplish tasks (for example, deploying code or provisioning infrastructure) that normally would have required assistance from other teams. Responsibilities are shared by all team members.

Please note, when we say Engineers in DevOps independently accomplish all tasks, it can only happen if most of the activities are automated from provisioning of resources, services, VMs, compute, memory, testing, integration testing, training, deployment etc.

You must be confused between DevOps and CI/ CD.

‘CI stands for Continuous Integration and CD stands for Continuous Delivery/ Deployment.’

We know, in development and implement life cycle there are multiple steps involved e.g., development of code, packaging it, testing it, and then deploying in production.

The challenge in traditional approach is, once development is completed rest of the steps in above cycle were very time consuming as there are a lot of manual processes involve plus dependencies on many departments for review, approval, and deployment manually. Due to manual processes, there was always risk of human errors. Many a times, we have seen one deployment can affect other’s code as well. For example, there are 5 deployments conducted at the same time, 4 deployments went smooth but 5th one impact last 4 as well. We won’t go into details but trust me, I have personally seen the above incidents many times. OK OK OK, yes once me deployment screwed other deployments as well lolz 😊. The point is whenever there is human involvement, errors are inevitable.

DevSecOps is nothing less DevOps but with addition step of security.

Due to the quick and fast deployment cycles, security has become an alarming factor in DevOps, so security has been included right before the creation process and therefore DevSecOps (development, security, and operations) came onto the surface.

DataOps follows the same principles just like DevOps/ DevSecOps but here it has few more elements related to Data domain.

DataOps focuses on data related aspects of operations. It makes sure data is properly integrated with source and target, its flowing-in using automated and best practices data pipelines, its highly secured, it’s of high quality etc.

DevOps doesn’t care whether its data project or mobile application project or any other technology project, but DataOps focuses on Data like where it is coming and where it will go, why it is coming and how it will be used, how much it costed and how much it will monetize etc.

As mentioned above, DataOps includes all the steps or processes involved in DevOps/ DevSecOps but at the same time it also introduces a few additional steps.

DataOps Process Flow

• Regular Integration
• Regular Development
• Regular Build
• Regular Testing
• Regular Deployment
• Regular Delivery/ Run
• Regular Monitoring and Feedback