Working around schema drift in SQL Server

At Stack Overflow, our environment has multiple implementations of a largely – but not 100% – identical schema. (By “schema,” I mostly mean the set of tables within a database.) I sometimes need to retrieve or update data across a large number of sites. Let’s say, pulling or removing information from the Users table, and related tables, across all of our databases.

This gets complicated.

Because:

• Each of our Q & A sites has its own database, and each database has a table called Users (and some metadata is split out into a separate UserMetadata table).
• A few sites are special, like Area 51, where the Users table looks a little different (it is wider, because the UserMetadata split isn’t present here for historical reasons).
• A few others look like Q & A databases, like Chat. They also contain a table called Users, but here the table is used differently and the column structure is unique.
• Stack Overflow for Teams is more complex. A team’s tables are shared with others within a single database, with the boundary being a schema instead of a database. There is no table called dbo.Users, but a database may contain multiple Users tables, such as Team0001.Users, Team0002.Users, and so on. I’ll deal with this special case in a future post.

IntelliSense highlights a few of these differences straight away:

Schema has “drifted” in these and other cases, for example when features are only applicable to one site, team, or product. Features like Collectives, Saves, or Staging Ground can lead to new columns or even entire tables that are only present in some databases.

This is okay, and there are many ways to deal with drift – even drift by design. But that’s not the point of this post. Because even if I could convince everyone all of our databases should have identical schema, I can’t snap my fingers and make it happen overnight. In the meantime, I have to deal with these differences.

A fake example.

Imagine I want to collect a list of users with more than one answer and who mention groundhogs in the “About Me” section of their profile. In any Q & A database, I can issue the following query:

SELECT u.Id, u.AnswerCount
   FROM Users AS u
   INNER JOIN UserMetadata AS um
   ON u.Id = um.UserId
   WHERE um.AboutMe LIKE N'%groundhogs%'
   AND u.AnswerCount > 1;

That works fine across “normal” site databases.

But there are exceptions.

In the Area 51 database, that query fails. As mentioned above, there is no UserMetadata table, and AnswerCount is not aggregated anywhere. I have to write the query differently:

SELECT u.Id, AnswerCount = COUNT(p.Id)
   FROM Users AS u
   INNER JOIN Posts AS p
   ON u.Id = p.OwnerUserId
   AND p.PostTypeId = 2
   WHERE u.AboutMe LIKE N'%groundhogs%'
   GROUP BY u.Id
   HAVING COUNT(p.Id > 1);

In the Chat database, this query makes no sense, because there are no answers. So I want an easy way for this database to be skipped entirely.

The problem.

I don’t want to hard-code lists of databases that happen to support one version of the schema or another right now. This reminds me of browser detection techniques that rely on the user agent string the browser presents, rather than testing the required functionality. Picture maintaining a list of all possible user agent strings and keeping it up to date. This is simply unmanageable, and I don’t want to do it for database names, either. I want it to be dynamic, so I don’t have to update some list somewhere every time a new database gets added, existing schema drift gets corrected, or new schema drift appears. And sometimes new schema drift will appear, like with new features, as mentioned before. We know about this drift; it goes through PRs and reviews, and is what I call controlled drift. The technique I use relies on knowing the schemas are well controlled, and being aware of changes coming out. So if someone goes rogue and creates a new table in Area51 called dbo.MoreUserMetadata, and starts stuffing information about groundhogs in a random column there without anyone’s knowledge, that data will go unnoticed. That is one caveat to the solution I’ll propose.

A piece of the puzzle.

I have to repeat this process for a growing list of queries. So, in our central DBA database, I have an Actions table that I use to validate the metadata in each database before attempting to run a given query there.

USE DBA;
 GO

 CREATE TABLE dbo.Actions
 (
   ActionID     int,
   QueryText    nvarchar(max),
   CheckTable1  nvarchar(128),
   CheckColumn1 nvarchar(128),
   CheckTable2  nvarchar(128),
   CheckColumn2 nvarchar(128)
 );

I put these queries into the table (minus the statement terminator, since these will later be placed inside a common table expression):

INSERT dbo.Actions
 (
   ActionID,    QueryText, 
   CheckTable1, CheckColumn1, 
   CheckTable2, CheckColumn2
 )
 VALUES
 
   (1, N'SELECT u.Id, u.AnswerCount
         FROM Users AS u
         INNER JOIN UserMetadata AS um
         ON u.Id = um.UserId
         WHERE um.AboutMe LIKE N''%groundhogs%''
         AND u.AnswerCount > 1',
       N'Users',        N'AnswerCount',
       N'UserMetadata', N'AboutMe'),

   (2, N'SELECT u.Id, AnswerCount = COUNT(p.Id)
         FROM Users AS u
         INNER JOIN Posts AS p
         ON u.Id = p.OwnerUserId
         AND p.PostTypeId = 2
         WHERE u.AboutMe LIKE N''%groundhogs%''
         GROUP BY u.Id
         HAVING COUNT(p.Id) > 1',
       N'Users', N'AboutMe', 
       NULL,     NULL);

As I loop through each database, I build dynamic SQL that checks for the proper metadata before replacing tokens and executing the query. To demonstrate, first, let’s set up a few databases that meet our various criteria:

CREATE DATABASE StackOverflow;
 GO
 USE StackOverflow; -- only query 1 can run here
 GO
 CREATE TABLE dbo.Users(Id int, AnswerCount int);
 CREATE TABLE dbo.UserMetadata(UserId int, AboutMe nvarchar(max));
 INSERT dbo.Users VALUES(1,5),(2,0);
 INSERT dbo.UserMetadata VALUES(1,'I love groundhogs!');
 GO

 CREATE DATABASE Chat; -- neither query can run here
 GO
 USE Chat;
 GO
 CREATE TABLE dbo.Users(UserId int);
 INSERT dbo.Users VALUES(1),(2);
 GO

 CREATE DATABASE Area51; -- only query 2 can run here
 GO
 USE Area51;
 GO
 CREATE TABLE dbo.Users(Id int, AboutMe nvarchar(max));
 CREATE TABLE dbo.Posts(Id int, OwnerUserId int, PostTypeId int);
 INSERT dbo.Users VALUES(5,'I hate groundhogs!');
 INSERT dbo.Posts VALUES(1,5,2),(2,5,2);
 GO

I’ll use a cursor to step through each action within each database, and check if it has supporting metadata. I keep it flexible so that I can check one or two tables, and zero or one columns in each table. Why zero? For some queries, I know the columns exist in all databases, so I don’t need to check.

DECLARE @debug bit = 0; /* set to 1 to see output instead of execution */

 DECLARE @go        bit,       
         @actions   cursor, 
         @actionId  int,       
         @db        sysname,         
         @context   nvarchar(255),
         @checkSQL  nvarchar(max),
         @queryText nvarchar(max), 
         @t1        nvarchar(128), @c1 nvarchar(128), 
         @t2        nvarchar(128), @c2 nvarchar(128),
         @tCheck    nvarchar(255) = N'EXISTS (SELECT 1 FROM sys.tables AS t
                                      WHERE name = ',
         @cCheck    nvarchar(256) = N'EXISTS (SELECT 1 FROM sys.columns AS c
                                      WHERE c.object_id = t.object_id
                                      AND c.name = ',
         @params    nvarchar(max) = N'@t1 nvarchar(128), @c1 nvarchar(128), 
                                      @t2 nvarchar(128), @c2 nvarchar(128), 
                                      @go bit OUTPUT';

 /* temp table to hold the results from each database */
 DROP TABLE IF EXISTS #Results;
 CREATE TABLE #Results(ActionID int, Source nvarchar(255), UserId int, AnswerCount int);

 /* cursor to loop through each action for each database */
 SET @actions = cursor FOR 
 SELECT act.ActionID, db.name, act.QueryText, 
        act.CheckTable1, act.CheckColumn1, 
        act.CheckTable2, act.CheckColumn2
   FROM dbo.Actions AS act 
   CROSS JOIN sys.databases AS db
   WHERE db.state = 0 AND db.database_id > 4
   ORDER BY db.name, act.ActionID;

 OPEN @actions;
 FETCH @actions INTO @actionId, @db, @queryText, @t1, @c1, @t2, @c2;

 WHILE @@FETCH_STATUS <> -1
 BEGIN
   /* set the right database context, and construct the metadata 
      check depending on whether we need to validate one or two 
      tables and/or columns.

      The tables/columns are passed into the dynamic SQL based
      on what comes back from the Actions table. */

   SELECT @context  = QUOTENAME(@db) + N'.sys.sp_executesql',
          @checkSQL = N'IF ' + @tCheck + N'@t1'
           + CASE   WHEN @c1 IS NOT NULL THEN N' AND ' + @cCheck + N'@c1)' 
             ELSE N'' END + N')'
           + CASE   WHEN @t2 IS NOT NULL THEN N' AND ' + @tCheck + N'@t2'
             + CASE WHEN @c2 IS NOT NULL THEN N' AND ' + @cCheck + N'@c2)'
               ELSE N'' END + N')' 
             ELSE N'' END
           + N' SET @go = 1; ELSE SET @go = 0;';

   IF @debug = 1
   BEGIN
     PRINT CONCAT_WS(char(13), @db, 'Action:', @ActionID, 'CheckSQL:', @checkSQL);
     PRINT CONCAT('@t1/@c1: ',@t1, '.' + @c1, char(13), '@t2/@c2: ',@t2, '.' + @c2);
   END

   /* check if the metadata exists in the target database: */
   EXEC @context @checkSQL, @params, @t1, @c1, @t2, @c2, @go OUTPUT;
 
   IF @debug = 1
   BEGIN
     PRINT CONCAT('Should action #', @actionId, ' run here (', @db, ')? ', @go);
   END

   /* if it does, we're good to go! */
   IF @go = 1
   BEGIN
     /* wrap the query in a CTE so we can inject action/DB: */
     SET @queryText = N'WITH cte AS (' + @QueryText + ')
                        SELECT @ActionID, DB_NAME(), * FROM cte;';
 
     IF @debug = 1
     BEGIN
       PRINT CONCAT('Would have run:', char(13), @queryText);
     END
     ELSE
     BEGIN
       INSERT #Results(ActionID, Source, UserID, AnswerCount)
         EXEC @context @queryText, N'@ActionID int', @actionId;
     END
   END

   FETCH @actions INTO @actionId, @db, @queryText, @t1, @c1, @t2, @c2;
 END

 IF @debug = 0
 BEGIN
   SELECT ActionID, Source, UserID, AnswerCount FROM #Results;
 END

Results.

Even though I have very different schema across (many!) different databases, this technique allows me to pull a consolidated result without knowing which ones store the same data in slightly different ways:

If you are trying to get a handle on what the code does, the parameter @debug provides some insight. If you execute the script with @debug = 1, you’ll see this output instead of the results. This shows what code is produced and executed by sp_executesql:

Area51
 Action:
 1
 CheckSQL:
 IF EXISTS (SELECT 1 FROM sys.tables AS t
              WHERE name = @t1 AND EXISTS (SELECT 1 FROM sys.columns AS c
              WHERE c.object_id = t.object_id
              AND c.name = @c1)) AND EXISTS (SELECT 1 FROM sys.tables AS t
              WHERE name = @t2 AND EXISTS (SELECT 1 FROM sys.columns AS c
              WHERE c.object_id = t.object_id
              AND c.name = @c2)) SET @go = 1; ELSE SET @go = 0;
 @t1/@c1: Users.AnswerCount
 @t2/@c2: UserMetadata.AboutMe
 Should action #1 run here (Area51)? 0
 Area51
 Action:
 2
 CheckSQL:
 IF EXISTS (SELECT 1 FROM sys.tables AS t
              WHERE name = @t1 AND EXISTS (SELECT 1 FROM sys.columns AS c
              WHERE c.object_id = t.object_id
              AND c.name = @c1)) SET @go = 1; ELSE SET @go = 0;
 @t1/@c1: Users.AboutMe
 @t2/@c2: 
 Should action #2 run here (Area51)? 1
 Would have run:
 WITH cte AS (SELECT u.Id, AnswerCount = COUNT(p.Id)
         FROM Users AS u
         INNER JOIN Posts AS p
         ON u.Id = p.OwnerUserId
         AND p.PostTypeId = 2
         WHERE u.AboutMe LIKE N'%groundhogs%'
         GROUP BY u.Id
         HAVING COUNT(p.Id) > 1)
       SELECT @ActionID, DB_NAME(), * FROM cte;
 ... repeat for every database ...

I’ll follow up soon with details on some of the more special cases.

The post Working around schema drift in SQL Server appeared first on Simple Talk.

from Simple Talk https://ift.tt/fg894lc
via

Database Concurrency in PostgreSQL

Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability – https://en.wikipedia.org/wiki/ACID) properties.

Concurrency Techniques

Broadly there are three concurrency techniques available for any database management system, pessimistic, optimistic, and multi-valued concurrency control (MVCC). In this section, I will introduce the techniques.

Pessimistic Locking

This concurrency control technique is used in database systems to handle concurrent access to shared data. It takes a cautious approach by assuming that conflicts between transactions are likely to occur, and it prevents conflicts by acquiring locks on database objects (rows or tables). Pessimistic locking ensures exclusive access to data, but it can lead to increased blocking and reduced concurrency compared to optimistic locking approaches.

An example of pessimistic locking is Strict Two-Phase Locking (2PL) (https://en.wikipedia.org/wiki/Two-phase_locking) and it ensures that concurrent transactions acquire and release locks in a strict and consistent manner, preventing conflicts and maintaining the data integrity. Strict 2PL consists of two distinct phases: the lock growing phase and the lock shrinking phase. In the lock growing phase, a transaction acquires locks on database objects before accessing or modifying them. These locks can be either shared (read) locks or exclusive (write) locks based on the transaction type. In the lock release phase, a transaction releases the locks it holds on database objects. This typically happens at the end of the transaction (a commit or a rollback).

The pros of this technique are, simple to implement and guaranteed consistency and integrity of data. Whereas the cons are, high lock contention such as lock-waits, lock escalations leading to performance bottlenecks.

Optimistic Locking

This concurrency control technique takes an optimistic approach by assuming that conflicts between transactions are rare, and it allows transactions to proceed without acquiring locks on database objects during the execution of the entire transaction. Conflicts are validated, detected and resolved only at the time of committing the transaction.

The pros of this technique are, increased concurrency, reduced lock overhead leading to linear high scalability. Whereas the cons are, high abort rate due to detection of conflict at commit time, and data integrity challenges.

Multi Version Concurrency Control (MVCC)

This concurrency control technique is used in database systems to allow concurrent access to shared database objects while maintaining data consistency and isolation. MVCC provides each transaction with a consistent snapshot of the data as it existed at the start of the transaction, even when other transactions modify the data concurrently.

MVCC works very well within PostgreSQL for read operations. When it comes to updating the data, PostgreSQL would still acquire locks (which is pessimistic locking) at row level to ensure data consistency and prevent conflict between concurrent transactions. PostgreSQL provides various types of locks to manage concurrency and ensure data consistency in multi-user environments. These locks can be classified into several categories based on their scope and purpose. Below are the most common types of locks available (reference: https://ift.tt/voIa8Dx

• Row Level/Tuple Level Locks
• Page Level Locks
• Page Level Locks
• Advisory Locks

PostgreSQL engine handles lock management automatically in the background for most common operations such as update and delete. The database engine acquires and releases locks based on the SQL statements being executed, the transaction isolation level and the volume of data. We will be discussing these in detail in the next article – Deep dive into PostgreSQL locks and deadlocks.

Now that we know about the concurrency control aspects of databases. Let’s dive deep into the building blocks of MVCC specific to PostgreSQL.

Building Blocks of MVCC in PostgreSQL

There are 6 key building blocks of MVCC which are very important to understand how concurrency controls works within PostgreSQL:

• Transaction IDs
• Versioning of Tuple
• Visibility Check Rules
• Read Consistency
• Write Operations
• Garbage Collection

In this section I will introduce each of these topics to help make the basics clear.

Transaction IDs (XIDs):

Each transaction is assigned a unique transaction identifier (ID) called a Transaction ID (XID). It is a 32-bit (4 bytes) value that uniquely identifies a transaction within a PostgreSQL database cluster. It is automatically generated by the transaction manager when a transaction begins, and it remains associated with the transaction until it either commits or rollbacks the transaction.

With 2^ (32 -1), we can have up to 2,147,483,648 transaction IDs in the cluster. Upon reaching this limit, we will experience a wraparound and transaction IDs will be reused.

Note:

Transaction ID 0 (zero) is reserved, 1 is used as bootstrap transaction id during the cluster initialization phase and 2 is used as frozen transaction id.

The built-in function pg_current_xact_id() returns the current transaction ID (XID) of the calling session. This call by itself creates a new transaction when you call it for the first time within the unit of work. For example:

The PostgreSQL transaction manager allocates XID when pg_current_xact_id () is called or an UPDATE, DELETE, INSERT operation is executed. However, a BEGIN transaction or a SELECT operation will not allocate a transaction ID. The built-in function pg_current_xact_id_if_assigned () can be used to return the current transaction ID or NULL if XID is not assigned to the transaction.

A Word About 8-byte Transaction ID (xid8) Enhancement in PostgreSQL

There were enhancements made to the code since PostgreSQL 13 to have a 64-bit (8 bytes) transaction IDs leading up to 9,223,372,036,854,775,808 (9223372 billion transaction IDs compared to 2.2 billion transaction IDs) that does not wrap around during the life of an installation. The following data structures have been modified to make use of 8-byte transaction ID (xid8):

• PostgreSQL system catalog
• Functions and procedures
• Backend worker processes which deal with SQL queries
• PostgreSQL utilities
• Replication slots and VACUUM

However, the challenge is to convert an existing XMIN and XMAX data structure from xid4 to xid8 on a running database makes it difficult to perform an in-place conversion. Please look out for more details about xid8 in the future article.

Versioning of Tuples

In PostgreSQL, each tuple (row) in a database table contains two transaction ID (XID) fields known as the xmin and xmax. These fields represent the minimum and maximum transaction IDs that are permitted to see or access a specific row. They play a crucial role in the Multi-Version Concurrency Control (MVCC) mechanism for maintaining data visibility and consistency.

Below are the characteristics of xmin and xmax fields:

xmin (Transaction ID of the creating transaction):

• The xmin field stores the Transaction ID (XID) of the transaction that created the row version.
• It indicates the minimum Transaction ID that is allowed to see the row version.
• Any transaction with a Transaction ID lower than xmin can see and access the row version.
• xmin is set when a new row version is inserted or created.

xmax (Transaction ID of the deleting transaction):

• The xmax field stores the Transaction ID (XID) of the transaction that deleted or marked the row version as deleted.
• It indicates the maximum Transaction ID that is allowed to see the row version.
• Only transactions with a Transaction ID lower than xmax can see the row version.
• If xmax is set to infinity (represented as 0), it means the row version is currently not deleted or marked as deleted by any transaction.

Let’s take an example and see how versioning is maintained in a table.

In this example, xmin for the first tuple is 609167. It is the XID of the transaction which inserted this tuple. The xmax column value is set to 0, which means the tuple is active (validity is set to infinity) and there was no update or delete performed on this tuple.

The column ctid is a system column that represents the physical location or address of a row within a table. It consists of two parts: block number and tuple index. The block number identifies the disk block where the row is stored, and tuple index identifies the position of the row within the block.

Figure 1 illustrates a PostgreSQL tuple versioning with a simple example. The session #1 read tuples from table account_info, where xmax was set to infinity (zero) – that means no modifications were made to tuples and were active versions. You will also notice, the ctid is set to (0,1) (0,2) that means both the tuples are stored sequentially in block 0 and this table has only 2 tuples. When session #2 updates the account_type for account=1 tuple, xmin is changed from txid 609167 to 609169 to reflect the transaction performing the update. You will also notice the change in ctid from (0,1) (0,2) to (0,2) (0,3) that means we have 3 tuples in the table stored in block 0. Why do we have 3 tuples in the table when we only see 2 tuples? That’s because PostgreSQL has retained the older version of the tuple for 2 reasons, (1) – there is an open transaction which reads the earlier version of the tuple which is valid between txid 609167 and 609169 and (2) – the recent change is not committed yet from session #2.

Figure 1: PostgreSQL Versioning Example

The pageinspect module in PostgreSQL provides a set of functions that allow you to inspect and analyze the contents of database pages at a low level. This module is particularly useful for debugging, troubleshooting, and understanding the internal structure of PostgreSQL database pages. For example, we can inspect the table account_info to extract all 3 tuples details (2 active tuples and 1 dead tuple).

Visibility Check Rules

In PostgreSQL’s Multi-Version Concurrency Control (MVCC) mechanism, visibility check rules are used to determine which data versions are visible to a transaction’s snapshot. These rules ensure that each transaction sees a consistent snapshot of the database as of its start time.

The Visibility Check Rules in PostgreSQL are as follows:

1. Transaction ID (XID) Comparison: Each data version in PostgreSQL has an associated Transaction ID (XID) indicating the transaction that created or modified it. To determine if a data version is visible to a transaction’s snapshot, the following rule is applied:

xmin <= pg_current_xact_id () 
     AND (xmax = 0 OR pg_current_xact_id () < xmax)

• If the xmin of the data version is lower than the transaction’s XID, it is considered committed before the transaction’s start time and visible to the transaction.
• If the xmin of the data version is greater than or equal to the transaction’s XID, it is considered not yet committed or modified after the transaction’s start time and not visible to the transaction. However, a tuple will be visible even when xmin > pg_current_xact_id ()with READ COMMITTED isolation level. For example:

In this example, txid 609176 is still able to view committed data from txid 609177 which is a future transaction id. In PostgreSQL, when a transaction in READ COMMITTED isolation level starts a new query, it takes a new snapshot of the database state. This snapshot represents the committed state of the database at the point in time the query starts. This is the reason for allowing tuples to be visible with xmin > pg_current_xact_id ().

2. Snapshot Transaction ID Range: A transaction’s snapshot includes a transaction ID range that determines the XIDs of the data versions visible to the transaction. For example, you can use pg_current_snapshot () snapshot information function to list the snapshots transaction ID range.

mvcc=# begin;
BEGIN
mvcc=*# update account_info 
set account_type = 'Checking' 
where account = 2;
UPDATE 1
mvcc=*# select pg_current_snapshot();
 pg_current_snapshot
---------------------
 609172:609172:
(1 row)
mvcc=*# select pg_last_committed_xact ();
           pg_last_committed_xact
--------------------------------------------
 (609169,"2023-07-09 01:12:03.883742+00",0)
(1 row)

The snapshot includes the transaction’s own XID and may include a range of other XID values based on the chosen isolation level.

• Read Committed: A transaction’s snapshot includes its own XID only, allowing it to see only committed data versions that existed before its start time.
• Repeatable Read: A transaction’s snapshot includes its own XID and all XID values that were already committed at its start time, allowing it to see a consistent snapshot of the data throughout the transaction’s duration.
• Serializable: A transaction’s snapshot includes a range of XID values up to the transaction’s XID, allowing it to see a consistent snapshot and preventing conflicts with concurrent transactions modifying the same data.

3. Visibility Information: Each data version in PostgreSQL has visibility information associated with it, including the minimum XID (xmin) and the maximum XID (xmax) that are allowed to see the version. These values are used to determine visibility based on the transaction’s snapshot.

• If the transaction’s XID is greater than xmin, the data version is visible to the transaction.
• If the transaction’s XID is greater than or equal to xmax, the data version is not visible to the transaction.
• If xmin is less than the transaction’s XID and xmax is greater than the transaction’s XID, additional checks may be performed to handle special cases, such as in-progress transactions or locks have been held at tuple level.

By applying these visibility check rules, PostgreSQL ensures that each transaction sees a consistent snapshot of the data based on its start time and isolation level. This allows for concurrent access to the database while maintaining read consistency and transaction isolation.

Read Consistency

The read consistency refers to the guarantee that a transaction sees a consistent snapshot of the database as of the transaction’s start time, regardless of concurrent changes made by other transactions. Read consistency ensures that the data accessed by a transaction remains stable and consistent throughout its execution.

Let’s take a closer look at read phenomena, also known as anomalies, are undesirable behaviours that can occur in concurrent database transactions when multiple transactions are reading and modifying the same data concurrently. These phenomena can result in inconsistent or unexpected results if proper isolation and concurrency control measures are not in place.

Dirty Read – This phenomenon can occur during concurrent database transactions. It happens when one transaction reads data that has been modified by another transaction that has not yet been committed. In other words, a transaction reads uncommitted or dirty data. This is completely prevented in PostgreSQL (however this is still possible in other relational database systems such as SQL Server and Db2) and we cannot read uncommitted data.

Non-Repeatable Read – This phenomenon can occur when a transaction reads the same row or tuple multiple times during its execution, but the data values change or vanishes (is deleted) between the reads due to concurrent modifications by other transactions.

Phantom Read – This occurs when a transaction retrieves a set of rows based on a condition, and between consecutive reads, another transaction inserts that satisfy the same condition. As a result, the second read includes additional rows or misses previously retrieved rows, leading to an inconsistent result set.

Serialization Anomaly: This occurs when the outcome of executing a group of transactions concurrently is inconsistent with the outcome of executing the same transactions sequentially in all possible orderings. In other words, the final result of a set of concurrent transactions is not equivalent to any possible serial execution of those transactions.

In PostgreSQL, various transaction isolation levels are available to control the level of concurrency and consistency in database transactions. Each isolation level defines the visibility and locking behavior for concurrent transactions and PostgreSQL supports the following isolation levels:

1. Read Committed (default):

• Provides read consistency by ensuring that a transaction only sees data that has been committed at the time the query begins.
• Prevents dirty reads by requiring data to be committed before it becomes visible to other transactions.
• Allows non-repeatable reads and phantom reads, as concurrent transactions may modify the data between reads.
• Offers a good balance between consistency and concurrency and is suitable for many applications.

2. Repeatable Read:

• Provides a higher level of read consistency than Read Committed.
• Ensures that a transaction sees a consistent snapshot of the database as of the transaction’s start time.
• Prevents dirty reads and non-repeatable reads by acquiring read locks on accessed data, preventing concurrent modifications.
• Allows phantom reads, as concurrent transactions may insert new rows that match the query criteria.
• Provides a stronger guarantee of data consistency but can result in increased concurrency issues due to acquired locks.

3. Serializable:

• Provides the highest level of isolation and guarantees serializability of transactions.
• Ensures that concurrent transactions appear as if they were executed serially, without any concurrency anomalies.
• Prevents dirty reads, non-repeatable reads, and phantom reads.
• May result in increased locking and potential serialization conflicts, leading to more blocking and reduced concurrency.

Table 1 shows the phenomenon which can occur in a specific isolation level. Even though PostgreSQL allows setting the isolation level to uncommitted, it behaves exactly like read committed.

Isolation Level/Phenomenon	Dirty Read	Non-Repeatable Read	Phantom Read	Serialization Anomaly
READ COMMITTED	Not Possible	Possible	Possible	Possible
REPEATABLE READ	Not Possible	Not Possible	Not Possible	Possible
SERIALIZABLE	Not Possible	Not Possible	Not Possible	Not Possible

Table 1: Transaction Isolation Levels and Read Phenomenon

Let’s look at the Read Committed isolation level and its behavior. Figure 2 illustrates how the dirty read behavior would work. PostgreSQL always reads a committed version of the tuple and dirty reads are not possible. As a side note, you can set the transaction isolation at transactional level or at the database level.

Note: The default isolation level set at the database level is read committed. You can use ALTER DATABASE command to make the change as shown below:

ALTER DATABASE ${dbname} 
   SET DEFAULT_TRANSACTION_ISOLATION TO 'repeatable read';

 

Figure 2: Dirty Read phenomenon is not a possibility in PostgreSQL

Figure 3 illustrates the possibility of having non-repeatable read and phantom phenomenon when the isolation level set to READ COMMITTED.

 

Figure 3: Non-Repeatable read and Phantom read phenomenon are possible in PostgreSQL

Let’s look at the Repeatable Read isolation level and its behavior. Figure 4 showcases how PostgreSQL resolves the non-repeatable read and phantom read anomalies. The serialization anomaly can still occur based on how the application is designed.

 

Figure 4: Repeatable Read Isolation Level Operations

What happens if an update is attempted to the same tuple from session #2? Let’s try.

mvcc=*# update ledger set amount = 2200 where accno = 1;
ERROR:  could not serialize access due to concurrent update
mvcc=!# select pg_current_snapshot ();
ERROR:  current transaction is aborted, commands ignored until end of transaction block
mvcc=!# rollback;
ROLLBACK

The error message indicates a serialization failure during concurrent transactions. It occurs when two or more transactions attempt to modify the same data simultaneously, resulting in conflicts that violate the strict serializability guarantee.

 

Figure 5: Serializable Isolation Level Operations

In PostgreSQL’s SERIALIZABLE isolation level, transactions are executed as if they were running in a serial order, which ensures data consistency. To maintain this strict serializability, PostgreSQL may identify a transaction as a “pivot” when conflicts occur with other concurrent transactions.

A “pivot” transaction is one that serves as a reference point for the serialization process. When conflicts arise, PostgreSQL may choose one transaction as the pivot and abort the other conflicting transactions to preserve consistency. The error message indicates that the transaction being canceled was identified as the pivot during the commit attempt.

For more information, please refer to https://wiki.postgresql.org/wiki/Serializable.

Write Operations

In PostgreSQL databases, an INSERT is simple and no different than other databases. The background writer and WAL writer processes handle the data write operation from shared buffers to the transaction log file and the data pages.

The interest is more in UPDATE and DELETE operations. Let’s take a look at UPDATE operation. When an UPDATE operation in PostgreSQL modifies a row, it follows a multi-version concurrency control (MVCC) mechanism. PostgreSQL will perform a delete and insert internally instead of modifying the existing row in place. This is done to ensure data consistency and handle concurrency control effectively by writers not blocking readers and readers not blocking the writers.

Every UPDATE operation performs pseudo delete, i.e., the original row is marked for deletion, creating a new version of the row with a delete flag. And an insert, a new row is inserted with the updated values, creating a new version of the row.

For simplicity, let’s take an UPDATE example with the READ COMMITTED (default) isolation level. In the example below, either of sessions can start the execution first or can execute both the sessions concurrently.

 

The pageinspect module functions can be used to analyze data at page level for both table and indexes. In the case of a DELETE operation, the xmax will be updated with the transaction id which executes the DELETE and marks the tuple as dead.

Garbage Collection

In PostgreSQL, garbage collection refers to the process of reclaiming disk space occupied by deleted or obsolete data. When data is deleted or updated in PostgreSQL, it is not immediately removed from the disk. Instead, it is marked as eligible for garbage collection, and the actual disk space is reclaimed later by the autovacuum process.

Based on our example, we have 4 live tuples and 12 dead tuples, these dead tuples can be cleaned using VACUUM. The VACUUM command is used to perform a table vacuum (data reorganization). It reclaims disk space by physically reorganizing the table and its associated indexes, and it can be more resource-intensive compared to regular VACUUM operations.

mvcc=# VACUUM VERBOSE ledger;
INFO:  vacuuming "mvcc.public.ledger"
INFO:  finished vacuuming "mvcc.public.ledger": index scans: 1
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 12 removed, 4 remain, 0 are dead but not yet removable
removable cutoff: 609251, which was 0 XIDs old when operation ended
index scan needed: 1 pages from table (100.00% of total) had 12 dead item identifiers removed
index "ledger_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 11 hits, 0 misses, 0 dirtied
WAL usage: 4 records, 1 full page images, 8480 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

However, the dead tuple count is still showing as 12 in the catalog. This is just the system catalog information and we will have to execute the ANALYZE command to update table statistics, including information about the distribution of data, column histograms, and correlation between columns. These statistics are used by the query optimizer to generate efficient execution plans

Summary:

MVCC (Multi-Version Concurrency Control) is a concurrency control mechanism used in PostgreSQL to handle concurrent access to data. It allows multiple transactions to read and write data concurrently while maintaining transaction isolation and ensuring consistency. In this article we learnt MVCC specifics and the building blocks of MVCC in detail. We will dive deep into each section such as VACUUM internals, storage internals, index B tree internal and locking internals in the future articles.

 

The post Database Concurrency in PostgreSQL appeared first on Simple Talk.

from Simple Talk https://ift.tt/4jq15EB
via

Going to a conference where hardly anybody knows my name

On Friday and Saturday August 11 and 12, I am going to do something I haven’t done in a long time. I am going to a conference where I may not know anyone (I did learn about the conference from one of the people that run it who was also at SQL Saturday Baton Rouge.) I am going to Powershell On The River in Chattanooga, TN and I am pretty excited to go and learn some more about PowerShell.

They have a first day of 1/2 day sessions, starting with the one I am excited about “Develop Your First Module.” I am not a newbie at PowerShell (though I haven’t used it seriously in a few years), but I am not even going to pretend I am 100% sure what defines a module! The day will hopefully progress to a session called “1up PowerShell for Extra Life” which looks like a nice coverage of all the basic topics I need. 

The only time I will not be happy about this is that the first session starts at 9:00 AM, and I will need to leave at 7;30 or so to be guaranteed to get there on time. I am not a morning person.

Saturday is a more typical conference set up with hour long sessions that are largely about PowerShell and a few other computing topics.

Of course, my reason for going to this conference is only partially so I can write better PowerShell. I want to meet some of this community and maybe hand out a business card or two.

If you know me (and especially if you have ever been exhorted by me to write), you will know two things are true. First, I am really introverted. Two, while I am less introverted once I have met people, I am not pushy. 

So going to a conference where I don’t know anyone is going to be an adventure. The last time I remember doing this was in the early 2000’s when I first started going to PASS. It took me a while to make the contacts and friends I have today. So who knows what will come of this weekend. In any case I hope to find new people’s blogs to read and some Powershell skills.

If you want to write about Powershell (or data, or programming…), check out the details on the About Simple Talk page. 

The post Going to a conference where hardly anybody knows my name appeared first on Simple Talk.

from Simple Talk https://ift.tt/7jTupoB
via

A Beginners Guide to MySQL Replication Part 4: Using GTID-based Replication

Welcome back to another replication series! As a quick reminder, we explored various methods of using MySQL’s replication capabilities in our previous discussions. Initially, we employed the traditional binary-log-based replication approach to set up our replication servers. This involved tracking the binary log file and its positions to facilitate replication.

In this article, we will dive into a more recent and acceptable approach to creating replication – using the Global Transaction Identifiers (GTID) based replication.

WHY Global Transaction Identifiers (GTIDs)?

GTIDs (Global Transaction Identifiers) provide a way to uniquely identify and track transactions across a distributed database environment. Each server assigns a unique GTID to every transaction, which greatly makes managing and coordinating data replication between multiple MySQL servers super easy. Here are some reasons why we use GTIDs in MySQL:

1. Unique Identification

GTIDs offer a distinct and globally unique identification for each transaction within a distributed database environment, as long as the transaction was recorded in the binary log. If a transaction is not written to the binary log, such as a read-only transaction, the system won’t assign it a GTID. This uniqueness guarantees that every transaction has a one-of-a-kind identifier, simplifying the process of monitoring and handling data modifications.

2. Data Consistency

GTIDs play a vital role in upholding data consistency across distributed databases. It ensures that a transaction committed on the source server cannot be duplicated on the replica, guaranteeing that transactions are applied in the precise order they were executed. As a result, data synchronization remains accurate and consistent across all servers involved.

3. Replication-made-easy

 GTIDs make the replication process between MySQL servers much simpler. With GTID-based replication, you don’t need to manage complex binary log positions; servers can easily identify which transactions have been applied and which are pending replication.

4. Failure and Recovery

 In case of server failures or crashes, GTIDs help ensure that data is consistently replicated. After a failure is resolved, the replication process can automatically resume from the last successfully applied GTID. This helps prevent data inconsistencies.

5. Easy to track

GTID-based replication simplifies tracking the source server of any transaction. By assigning a unique GTID to each transaction, which includes the source server’s unique identifier (server UUID) and the transaction sequence number, we can easily extract and decode the GTID from the binary log of a GTID-based transaction, thereby determining the original server. This capability is invaluable for auditing, troubleshooting, and maintaining data integrity in distributed database environments.

Configuring Replication with GTIDs

Configuring replication with GTIDs in MySQL involves several steps. Below is a step-by-step guide to help you set up GTID-based replication:

1. Configure the source server

To configure each MySQL server that would be participating in the replication setup, you must assign a unique server ID, set up a binary log, enable GTID mode, and create a replication user on the source server. Here’s what you need to do:

i. Assign a unique server ID: If the server ID is not assigned or you want to change it, you can do so by adding the following line in my.cnf or my.ini file under the [mysqld] section:

server_id = YOUR_UNIQUE_SERVER_UUID

Replace YOUR_UNIQUE_SERVER_UUID with a universally unique identifier (UUID).

ii. Enable GTID Mode: To start using GTIDs, you must enable GTID mode in the MySQL configuration file (my.cnf or my.ini). Add the following line under the [mysqld] section:

gtid_mode = ON
enforce-gtid-consistency=ON
--skip-slave-start=ON

The option --skip-slave-start=ON is employed to delay the start of replication until the replica is fully configured. Save the configuration file and restart the MySQL server.

iii. Binary Logging: Ensure that binary logging is enabled on the source server. This is necessary for GTID-based replication to function properly.

log_bin = /path/to/binary/log/file

iv. Create Replication User: As part of the configuration, you need to create a dedicated user on the source server that the replica servers will use to connect and replicate data. Grant the necessary privileges to this user using the following SQL command:

CREATE USER 'replication_user'@'replica_host' 
IDENTIFIED BY 'your_password'; 

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_host'; 

FLUSH PRIVILEGES;

Replace 'replication_user', 'replica_host', and 'your_password' with appropriate values. The FLUSH PRIVILEGES command is used to ensure that the server reloads the grant tables after granting the necessary privileges to the new user. This is necessary because the changes to the user privileges are stored in the MySQL grant tables, and these tables are cached in memory for performance reasons.

2. Configure the replica server

To configure the replica server using a GTID-based transaction, a unique server ID is required, else, the replication wouldn’t function properly. Here’s what we need to do:

i. Set Server UUID: If the server UUID of the replica server is different from the source server, you need to set it. Add the following line to my.cnf or my.ini file under the [mysqld] section:

server_uuid = YOUR_UNIQUE_SERVER_UUID

Replace YOUR_UNIQUE_SERVER_UUID with a universally unique identifier (UUID).

ii. Enable GTID Mode: Open the MySQL configuration file (my.cnf or my.ini) on the replica server, and under the [mysqld] section, add the following line to enable GTID mode:

gtid_mode = ON
enforce-gtid-consistency=ON
log-replica-updates=ON

iii. Binary Log: Replica servers are allowed to use GTID without configuring binary logs. If you wish to continue without enabling this, you can use the --skip-log-bin and --log-replica-updates=OFF options. However, to ensure that the binary log is enabled on the replica server. Add the following line to my.cnf or my.ini file:

log_bin = /path/to/binary/log/file

Replace /path/to/binary/log/file with the desired location for storing binary logs.

iv. Restart MySQL Server: Save the changes made to the configuration file and restart the MySQL server on the replica to apply the changes.

3. Start replication on the replica server

Once the source and replica servers are configured, we can start the replication process on the replica server. Connect to the replica server’s MySQL console and issue the following command:

--For MySQL versions lesser than 8.0.23

CHANGE MASTER TO MASTER_HOST = 'source_server_ip',
MASTER_PORT = source_server_port, MASTER_USER = 'replication_user', 
MASTER_PASSWORD = 'your_password', MASTER_AUTO_POSITION = 1, 
master_info_repository=TABLE, relay_log_info_repository=TABLE ;


--For MySQL version 8.0.23 or greater 

CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'source_server_ip', 
SOURCE_PORT = source_server_port, SOURCE_USER = 'replication_user',
SOURCE_PASSWORD = 'your_password', SOURCE_AUTO_POSITION = 1;

Replace 'source_server_ip', source_server_port, 'replication_user', and 'your_password' with the appropriate values.

The replica has a special place where it keeps important information needed to read and apply changes from the original server. This information is used by a specific part of the replica called the “applier metadata repository” to understand what changes it has already processed and what still needs to be done. This information includes things like file names and positions, which help the applier metadata repository keep track of where it is in the process of replicating data and is stored within the slave_relay_log_info table in the MySQL system schema. It’s like a road map to follow.

Before MySQL 8.0, if you wanted to use tables as replication metadata repositories, you had to explicitly set the master_info_repository and relay_log_info_repository options to TABLE during server startup.

Starting from MySQL 8.0, creating the replication metadata repositories as tables became the default behavior. As a result, you no longer need to set these system variables to use tables for the replication metadata repositories. However, it’s important to note that the use of master_info_repository and relay_log_info_repository system variables is deprecated in MySQL 8.0 and may be removed in future versions. So, it’s recommended to rely on the default table-based replication metadata repositories instead.

4. Backup Data

Before starting replication, it’s essential to ensure that the replica server has the same data as the source server. You can use tools like mysqldump or Percona XtraBackup to create a backup on the source server and restore it on the replica server.

5. Start replica server

After setting up replication parameters, you can start the replica server by executing the following command:

-- For MySQL versions lesser than 8.0.23
START SLAVE;

--For MySQL version 8.0.23 or greater
START REPLICA;

You can monitor the  replica server’s status using the following command:

-- For MySQL versions lesser than 8.0.23
SHOW SLAVE STATUS \G

--For MySQL version 8.0.23 or greater 
SHOW REPLICA STATUS;

Look for the Slave_IO_Running and Slave_SQL_Running status variables to ensure that both are set to Yes, indicating that the replication is running correctly.

Once you have completed these steps, your MySQL replication with GTIDs should be up and running! 

Configuring Multi-Source Replication using GTIDs

To configure a replication channel for each source using a GTID-based replication, on each MySQL server, we need to identify the source servers from which they will replicate data. This is typically done by specifying the source server’s connection details, including the server’s unique ID and the login credentials in the configuration file. Then, we can enable multi-source replication by specifying the following commands:

--For MySQL version 8.0.23 or less, the following command is applicable
--Replication path for source server 1 
CHANGE MASTER TO MASTER_HOST=”source_server1”,
MASTER_USER=”user_name”, MASTER_PASSWORD=”password”,
MASTER_AUTO_POSITION=1 FOR CHANNEL “source_server1”;

--Replication path for source server 2
CHANGE MASTER TO MASTER_HOST=”source_server2”,
MASTER_USER=”user_name”, MASTER_PASSWORD=”password”,
MASTER_AUTO_POSITION=1 FOR CHANNEL “source_server2”;

--Replication path for source server 3
CHANGE MASTER TO MASTER_HOST=”source_server3”,
MASTER_USER=”user_name”, MASTER_PASSWORD=”password”,
MASTER_AUTO_POSITION=1 FOR CHANNEL “source_server3”;

 

--For MySQL version 8.0.23 or greater, the following command is applicable 
--Replication path for source server 1 
CHANGE REPLICATION SOURCE TO SOURCE_HOST=”source_server1”, 
SOURCE_USER=”user_name”, SOURCE_PASSWORD=”password”,
SOURCE_AUTO_POSITION=1 FOR CHANNEL “source_server1”; 

--Replication path for source server 2 
CHANGE REPLICATION SOURCE TO SOURCE_HOST=”source_server2”,
SOURCE_USER=”user_name”, SOURCE_PASSWORD=”password”, 
SOURCE_AUTO_POSITION=1 FOR CHANNEL “source_server2”; 

--Replication path for source server 3 
CHANGE REPLICATION SOURCE TO SOURCE_HOST=”source_server3”,
SOURCE_USER=”user_name”, SOURCE_PASSWORD=”password”, 
SOURCE_AUTO_POSITION=1 FOR CHANNEL “source_server3”;

To replicate the database of our choice, this can be achieved using the CHANGE REPLICATION FILTER statement. 

--To replicate the database of our choice

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘database1.%’)
FOR CHANNEL “source_server1”;

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘database2.%’)
FOR CHANNEL “source_server2”;

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘database3.%’) 
FOR CHANNEL “source_server3”;

And this is how you configure multi-source replication in a GTID-based replication.

 

Drawbacks of GTID-based replication

Replication with GTID (Global Transaction Identifier) in MySQL offers several advantages, such as simplifying the configuration and improving reliability. However, there are certain restrictions and considerations to be aware of:

1. GTID Mode: Replication using GTID requires all servers in the replication topology to be running in GTID mode. Mixing GTID and non-GTID servers is not supported.

2. Server Version: GTID was introduced in MySQL 5.6.5. To use GTID-based replication, all servers involved must be running MySQL 5.6.5 or later versions.

3. GTID Consistency: For proper replication, the GTID consistency across all servers must be maintained. If GTIDs are manipulated manually, it can lead to data inconsistency and replication errors.
4. Temporary Tables: GTID-based replication does not support temporary tables on the slave. If you have temporary tables in your replication, you might need to restructure your schema.
5. Unsupported CREATE TABLE AND SELECT STATEMENTS: The CREATE TABLE AND SELECT statements are not supported when using the GTID-based replication topology due to inconsistencies with the statement and row replication formats.

 

Conclusion

With GTID, replication setup becomes more straightforward, as there is no need to deal with file and position values. It also enhances data integrity and reliability by supporting ROW-based replication, eliminating the issues associated with non-deterministic statements. However, implementing GTID-based replication requires careful planning and consideration of certain restrictions as mentioned earlier. It is essential to be aware of the limitations, especially concerning temporary tables,  to avoid unexpected challenges during deployment or changes in the replication topology.

When used correctly and in suitable scenarios, GTID-based replication proves to be a valuable tool for managing replication environments, offering greater confidence in data synchronization and replication reliability. For additional information on GTID-based replication, I highly suggest you visit the MySQL official documentation.

 

The post A Beginners Guide to MySQL Replication Part 4: Using GTID-based Replication appeared first on Simple Talk.

from Simple Talk https://ift.tt/I3BVgEm
via

T-SQL Tuesday #165 – My Experience with Database Job Titles

They mean very little.

More on that after nothing that this is part of T-SQL Tuesday, and you can read the original invitation by clicking on the T-SQL Tuesday 165 invitation or the following images. I almost always forget that this is coming and today is no different. But I wanted to chime in here.

By “very little”, I don’t mean that if you take a job as a DBA, you will likely be repurposed to wash dishes for the CEO’s birthday party. What I mean is that many of the definitions of a DBA and other database professionals have a lot of overlap in their definitions.

For example, as I looked around for formal definitions, I found a few interesting ones:

Oracle.com stated:

A database administrator, or DBA, is responsible for maintaining, securing, and operating databases and also ensures that data is correctly stored and retrieved.

This is the start of a good definition. But that definition is very, very wide. In fact, later in the same page, it lists different types of DBAs, which include system, database architects, analysts, modelers, application, task-oriented…

Craig S Mullins on TechTarget said:

A database administrator (DBA) is the information technician responsible for directing and performing all activities related to maintaining a successful database environment. A DBA makes sure an organization’s databases and related applications operate functionally and efficiently.

I like this description, and reading the entire article, you can see a very optimistic definition of what DBAs do.

When adopting a new DBMS, the DBA is responsible for designing, implementing and maintaining the database system. Often, that includes installing the DBMS and setting up the IT infrastructure to allow applications to access databases.

Of course, if this were true, so many DBA types wouldn’t spend so much energy complaining about how developers make their own databases and, too often, how our database servers are running on hardware more realistically fit for a file server. If more of us had proper database server hardware, the move to the cloud database would have happened less quickly. Still, we surrendered to get the hardware we begged for.

So, what of a title like Database Reliability Engineer? Gitlab had this to say about their use of the term:

Database Reliability Engineers (DBRE) are responsible for keeping database systems that support all user-facing services (most notably GitLab.com) and many other GitLab production systems running smoothly 24/7/365.

Digging into the definition, though, it sounds more like what a DBA really did in my world. Maintained the server, made sure it didn’t crash (and if it did that you could get it back up and running as fast as possible), and provided support for teams to make sure they wrote decent code:

Provide database expertise to engineering teams (for example through reviews of database migrations, queries and performance optimizations).

Of course, telling the engineering teams that their queries are not great is always a fun use of an afternoon.

Before coming to Redgate, my title was Data Architect for the past 15 or so years.

Coursera.org says a data architect is:

IT professionals who leverage their computer science and design skills to review and analyze the data infrastructure of an organization, plan future databases, and implement solutions to store and manage data for organizations and their users.

I mean, I did some of that, especially designing most of the new databases for the organization. But if I am honest, most of my career was spent doing that last thing. “Implement solutions.” This, I am pretty sure, translates to “writing database and ETL code.” In their first bullet point for defining the tasks, it states: “Translating business requirements into databases, data warehouses, and data streams.“

In my experience (which was not on a vast number of teams, admittedly), once you start doing something practical on any regular basis, it is hard to do a lot of the higher-level planning kinds of tasks that don’t move current processes forward. Think of any task you do now that wasn’t part of your original job title, and how you acquired it. Same thing.

So many titles, so very similar

In the T-SQL Tuesday host blog, there is a list of titles, DBA, Database Engineer, Database Reliability Engineer, Data Architect, Database Architect, Data Warehouse Architect, Data Warehouse Engineer, that all could mean kind of the same thing, depending on just how large of an organization you work with and what they think it means. If you work for a company that actually has one of each, the distinctions would likely be noticeable. You will also be in a very huge organization!

But even in SQL Kitty’s entry (AKA Josephine Bush, newly minted Microsoft MVP!) if you read the list presented there is so much overlap in her definitions, just like the one’s I have quoted.

Quick Aside: On my drive to and from SQL Saturday Baton Rouge the other day, I listened to the Primary Phase of the Hitchhiker’s Guide to the Galaxy Radio Series. Why I mention this will become apparent if you have listened to or read the book. If not, I will simply warn you in advance that dingo’s kidneys are not at all a pleasant thing.

While my personal experience on teams was not widespread, I have met and talked to a lot of people about what they do over the years, and most titles that people have are a load of dingo’s kidneys. In general, most titles are largely concerned with the movement of small green pieces of paper, which is odd because the pieces of paper don’t have as much to do with the database operations as things like normalization and testing. Titles are very often picked to position an employee with an associated salary.

One Distinctive Data Title

Really the one data-oriented title that has come out in recent years that really stands out as something different has been Data Scientist. To me, this is a pure coding and math-based profession, doing the kind of analysis that most data professionals only dream about as they are mucking around in the data trying to find out why Zaphod’s Gargleblaster was 2.35 Alturian dollars instead of 2.33 (ah, rounding issues). When the database systems crashes, all of the other folks with data in their title will be involved because they work below the surface of the database and infrastructure.

Techopedia defined a data scientist as:

A data scientist is an individual, organization, or application that performs statistical analysis, data mining, and retrieval processes on a large amount of data to identify trends, figures and other relevant information.

Skipping past that “or application” part that sounds ominously like SkyNet, the focus is on working with the data and using math to find real information from it. When I first heard about the concept of a data scientist, I was really interested. Then I realized you probably needed to really understand statistical analysis… and I don’t.

Conclusion

If you are applying for a job with any of these titles… look at what they want you to actually do. Twice. Then ask. There is a chance that what Company X means by Database Administrator and what someone else at Company X means by the same term. Branch out into other positions, and the Database Reliability Engineer could be simply that, making sure the database systems are reliable.

Just remember, if your full-time job is handling the reliability of any system, one of two things is true. The system is a mess. You won’t end up actually doing that full-time if you are good at it.

 

The post T-SQL Tuesday #165 – My Experience with Database Job Titles appeared first on Simple Talk.

from Simple Talk https://ift.tt/2XdZPGy
via

What Is an Execution Plan and How to Find It in PostgreSQL

In the last blog (When PostgreSQL Parameter Tuning is not the Answer), we compared several execution plans for a SQL statement as we made changes to parameters and indexes. Still, there was no mention of what an execution plan is, how one can obtain an execution plan for a query, and how to interpret the result. In this blog, we will take a deep dive into this topic.

Why we need an execution plan?

Most likely, if you ever worked with any relational DBMS, you heard the term execution plan, and moreover, you might have seen some of them. But have you ever thought about why you might need an execution plan for a SQL statement? Why you never need to (or get to) produce an execution plan for your C or Java program? The reason is that SQL is a declarative language. That means that when we write a SQL statement, we describe the result we want to get, but we don’t specify how that result should be obtained.

By contrast, in an imperative language (like C), we specify what to do to obtain a desired result—that is, the sequence of steps that should be executed. Sometimes it may not look like it when using modern languages, but once compiled they generally do the steps you program them to.

Note: If you want to execute the samples, in the last section of the article is an appendix with the instructions for preparing the demo.

What is an execution plan?

So, we just sent a SQL statement for PostgreSQL to execute it. Let’s use the same statement we used to illustrate the previous blog:

SELECT f.flight_no,
       f.actual_departure,
       count(passenger_id) passengers
  FROM postgres_air.flight f
       JOIN postgres_air.booking_leg bl 
          ON bl.flight_id = f.flight_id
       JOIN postgres_air.passenger p 
        ON p.booking_id=bl.booking_id
 WHERE f.departure_airport = 'JFK'
   AND f.arrival_airport = 'ORD'
   AND f.actual_departure BETWEEN
        '2023-08-08' and '2023-08-12'
GROUP BY f.flight_id, f.actual_departure;

We sent this to PostgreSQL to execute – what happens next?

To produce query results, PostgreSQL performs the following steps:

• Compile and transform a SQL statement into an expression consisting of high-level logical operations, known as a logical plan.
• Optimize the logical plan and convert it into an execution plan.
• Execute (interpret) the plan and return results.

Compiling a SQL query is similar to compiling code written in an imperative language. The source code is parsed, and an internal representation is generated. However, the compilation of SQL statements has two essential differences.

First, in an imperative language, the definitions of identifiers are usually included in the source code, while definitions of objects referenced in SQL queries are mostly stored in the database. Consequently, the meaning of a query depends on the database structure: different database servers can interpret the same query differently.

Second, the output of an imperative language compiler is usually (almost) executable code, such as byte code for a Java virtual machine. In contrast, the output of a query compiler is an expression consisting of high-level operations that remain declarative—they do not give any instruction on how to obtain the required output. A possible order of operations is specified at this point, but not the manner of executing those operations. The logical plan for the SQL statement presented above is shown below:

project f.flight_no,  f.actual_departure, count(p.passenger_id)[] (
   group [f.flight_no, f.actual_departure] (
      filter [f.departure_airport = 'JFK'] (
         filter [f.arrival_airport = 'ORD'] (
            filter [f.actual_departure >='2023-08-08'](
               filter [f.actual_departure <='2023-08-12' ] (
                  join [bl.flight_id = f.flight_id] (
                     access (flights f),
                     join(bl.booking_id=p.booking_id (
                     access (booking_leg bl),
                     access (passenger p)
                     ))))))))

Here, project stands for relational operation projection, which reduces the number of columns in the output; filter stands for relational operation filter, which applies selection criteria, join stands for join operation, access identifies that we need to read data from database tables. Note, that the representation above is a “humanized” version of PostgreSQL internal representation of the logical plan, and there is no command that would allow you to actually “see” it.

The instructions on how to execute the query appear at the next phase of query processing, optimization. An optimizer performs two kinds of transformations: it replaces logical operations with their execution algorithms and possibly changes the logical expression structure by changing the order in which logical operations will be executed.

Then, it tries to find a logical plan and physical operations that minimize required resources, including execution time. In this blog, we won’t go into details of how these transformations are done and how the optimizer decides which plan is the best, but we will look at some examples in subsequent blogs. The only thing we need to know now is that the output of the optimizer is an expression containing physical operations. This expression is called a (physical) execution plan. For that reason, the PostgreSQL optimizer is often called the query planner. There are multiple ways you can obtain the physical execution plan for a query, and they will be described later in this blog.

Finally, the query execution plan is interpreted by the query execution engine, frequently referred to as the executor in the PostgreSQL community, and output is returned to the client application.

How is an execution plan built?

The job of the optimizer is to build the best possible physical plan that implements a given logical plan on the server it is executing on. This is a complex process: sometimes, a complex logical operation is replaced with multiple physical operations, or several logical operations are merged into a single physical operation.

To build a plan, the optimizer uses transformation rules, heuristics, and cost-based optimization algorithms. A rule converts a plan into another plan with better cost. For example, filter and project operations reduce the size of the dataset and therefore should be executed as early as possible; a rule might reorder operations so that filter and project operations are executed sooner.

An optimization algorithm chooses the plan with the lowest cost estimate. However, the number of possible plans (called the plan space) for a query containing several operations is huge—far too large for the algorithm to consider every single possible plan, so heuristics are used to reduce the number of plans evaluated by the optimizer.

How to obtain the execution plan?

There are multiple ways to find out the execution plan of any SQL statement, not only SELECT, but also INSERT, UPDATE and DELETE.

The easiest way is to run EXPLAIN command and pass the SQL statement to it:

EXPLAIN SELECT f.flight_no,
       f.actual_departure,
       count(passenger_id) passengers
  FROM postgres_air.flight f
       JOIN postgres_air.booking_leg bl 
          ON bl.flight_id = f.flight_id
       JOIN postgres_air.passenger p 
        ON p.booking_id=bl.booking_id
 WHERE f.departure_airport = 'JFK'
   AND f.arrival_airport = 'ORD'
   AND f.actual_departure BETWEEN
        '2023-08-08' and '2023-08-12'
GROUP BY f.flight_id, f.actual_departure;

The result will be a projected execution plan. Remember that in contrast to Oracle or MS SQL Server, PostgreSQL query planner always produces the execution plan right before the execution itself based on many factors, including the current database statistics. (Other RDBMS platforms may reuse previously calculated plans which can have negative, and positive, effects.)

When you execute EXPLAIN PostgreSQL projects the costs of the operations based on the available statistics, which might be not current (or at least, may be in a different state when you actually execute your query to get back data.) Still, running EXPLAIN command would give you a good idea of how your query will be executed.

Another version of the EXPLAIN command is EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT f.flight_no,
       f.actual_departure,
       count(passenger_id) passengers
  FROM postgres_air.flight f
       JOIN postgres_air.booking_leg bl 
          ON bl.flight_id = f.flight_id
       JOIN postgres_air.passenger p 
        ON p.booking_id=bl.booking_id
 WHERE f.departure_airport = 'JFK'
   AND f.arrival_airport = 'ORD'
   AND f.actual_departure BETWEEN
        '2023-08-08' and '2023-08-12'
GROUP BY f.flight_id, f.actual_departure;

This command produces an execution plan and executes the statement (but does not output the execution results). This allows us to see not only the estimated statistics, but how long the execution took in reality and which original estimates were not right.

Finally, we can run EXPLAIN command with multiple parameters, which are listed below:

• ANALYZE [ Boolean ] – includes actual execution.
• VERBOSE [ Boolean ] – provides more details about plan steps.
• COSTS [ Boolean ] The COSTS parameter defaults to TRUE, including actual when ANALYZE is included– displays projected and actual cost of each plan node (operation in the query).
• SETTINGS [ Boolean ] – list of modified configuration parameters.
• BUFFERS [ Boolean ] – Only used when ANALYZE is included, shows the usage of the shared buffers.
• WAL [ Boolean ] – Only used when ANALYZE is included, shows the effect of WAL (Write ahead logging).
• TIMING [ Boolean ] – Only used when ANALYZE is included, – shows execution time for each node of the query plan.
• SUMMARY [ Boolean ] – default TRUE when ANALYZE]- prints the summary
• FORMAT { TEXT | XML | JSON | YAML } – execution plan output format

As an example, you could use the following:

EXPLAIN ( ANALYZE, VERBOSE, FORMAT XML )
SELECT…

And get the physical execution plan, with verbose details, in an XML format. You can find more information on the SQL Explain page of the PostgreSQL documentation. But EXPLAIN and EXPLAIN ANALYZE will be the two command you will typically use the most.

How to read an execution plan?

Now, let’s finally get back to the execution plans we analyzed in the previous blog. These execution plans were produced using command EXPLAIN (ANALYSE, BUFFERS, TIMING). Since in this blog, we are focusing on understanding the execution plan itself, we will produce a more compact version of that plan, using EXPLAIN command with no extra parameters.

Figure 1. The execution plan.

An execution plan is presented as a tree of physical operations. In this tree, nodes represent operations, and arrows point to operands. Looking at Figure 1, it might be not quite clear why it represents a tree.

There are multiple tools, including pgAdmin, which can generate a graphical representation of an execution plan. Figure 2 illustrates possible output. A more compact presentation, also produced by pgAdmin, is presented in Figure 3.

(In pgAdmin, the tree output can be obtained in the query editor by clicking on the Explain or Explain Analyze menu icons in the editor. It will output the text of the query plan and the graph. There is also a menu to choose which settings you wish to apply.)

Figure 2.  The Graphical output subtab of Explain

Figure 3.  More compact presentation found in the Analysis subtab of explain.

Now, let’s get back to the actual output of the EXPLAIN command, shown in Figure 1. It shows each node of the tree on a separate line starting with ->, with the depth of the node represented by the offset. Subtrees are placed after their parent node. Some operations are represented with two lines.

The execution of a plan starts from the leaves and ends at the root. This means that the operation that is executed first will be on the line that has the rightmost offset. Of course, a plan may contain several leaf nodes that are executed independently. As soon as an operation produces an output row, this row is pushed to the next operation. Thus, there is no need to store intermediate results between operations.

In Figure 1, execution starts from the last line, accessing the table flight using the index on the departure_airport column. Since several filters are applied to the table and only one of the filtering conditions is supported by the index, PostgreSQL performs a bitmap index scan. The engine accesses the index and compiles the list of blocks that could contain needed records. Then, it reads the actual blocks from the database using bitmap heap scan, and for each record extracted from the database, it rechecks that rows found via the index are current and applies filter operations for additional conditions for which we do not have indexes: arrival_airport and scheduled_departure.

The result is joined with the table booking_leg. PostgreSQL uses a sequential read to access this table and a hash join algorithm on condition bl.flight_id = f.flight_id.

Then, the table passenger is accessed via a sequential scan (since it doesn’t have any indexes), and once again, the hash join algorithm is used on the p.booking_id = bl.booking_id condition.

The last operation to be executed is grouping and calculating the aggregate function sum(). Sorting would produce the list of all flights which satisfy selection criteria (there will be four of them). Then, the count of all passengers on each flight is performed.

Understanding Execution Plans

Often, when we explain how to read execution plans in the manner described in the preceding text, our audience feels overwhelmed by the size of the execution plan for a relatively simple query, especially given that a more complex query can produce an execution plan of 100+ lines. Even the plan presented in Figure 1 might require some time to read. Sometimes, even when each and every single line of a plan can be interpreted, the question remains: “I have a query, and it is slow, and you tell me to look at the execution plan, and it is 100+ lines long. What should I do? Where should I start?”

The good news is that most of the time, you do not need to read the whole plan to understand what exactly makes the execution slow. In subsequent blogs we will address different aspects of query optimization and will learn how to interpret execution plans in each case. It may look daunting now, but once you understand a few of the primary operators, it will all start to be clearer.

Appendix – Setting up for the article series

For this series of articles, just like the previous, we will use a database that I have created for performance tuning examples: postgres_air database. If you want to repeat the experiments described in this article, download the latest version of this database (file postges_air_2023.backup) and restore it on a Postgres instance where you are going to run these experiments. We suggest that you do it on your personal device or any other instance where you have complete control of what’s going on, since you will need to restart the instance a couple of times during these experiments. We ran the examples on Postgres version 15.2, however, they will generally work the same at a minimum on versions 13 and 14.

The restore will create a schema postgres_air populated with data but without any indexes except for the ones which support primary/unique constraints. To be able to replicate the examples, you will need to create a couple of additional indexes:

SET search_path TO postgres_air;
 
CREATE INDEX flight_departure_airport ON
                   flight(departure_airport);
CREATE INDEX flight_arrival_airport ON
                   flight(arrival_airport);
 
CREATE INDEX flight_scheduled_departure ON 
                  flight  (scheduled_departure);

The post What Is an Execution Plan and How to Find It in PostgreSQL appeared first on Simple Talk.

from Simple Talk https://ift.tt/iuDvEl5
via

Bots Usage in Artificial Intelligence

The usage of bots in artificial intelligence (AI) has gained significant attention and importance in recent years. Bots, also known as chatbots or intelligent agents, are software applications designed to perform automated tasks or engage in conversations with humans; they are an integral part of AI systems, enabling interactions and delivering various functionalities across various industries and domains. A bot is a computer program or software application designed to perform specific tasks autonomously or interact with users through conversational interfaces (What is a bot?, n.d.). Bots can be programmed to execute various functions, from providing customer support and answering queries to automating repetitive tasks and gathering information. Artificial intelligence is a term for developing intelligent machines and systems capable of performing tasks traditionally performed by humans; it involves the simulation of human intelligence in devices, enabling them to learn, reason, understand natural language, perceive their environment, and make decisions (What Is Artificial Intelligence?, 2023). Overall, using bots in artificial intelligence has revolutionized various industries by enhancing customer experiences, streamlining processes, and leveraging data-driven insights. As technology advances, the capabilities of bots continue to expand, enabling more sophisticated interactions and driving innovation in AI applications.

Bots History

Bots and their evolution in the context of artificial intelligence have a rich history. The concept of bots can be traced back to the early days of computing. In the 1960s, Joseph Weizenbaum developed ELIZA, a program that could simulate conversations and mimic a Rogerian psychotherapist. ELIZA used pattern-matching techniques to respond to user inputs, creating the illusion of understanding (Jarow, 2023). In the following years, rule-based systems became famous for building bots; these systems used predefined rules and decision trees to process user inputs and generate appropriate responses. Although limited in their capabilities, rule-based bots found applications in customer service and information retrieval.

The rise of the internet and natural language processing (NLP) techniques led to the development of chatbots. In the late 1990s and early 2000s, chatbots like ALICE and SmarterChild gained popularity (Osuch, 2022). These bots utilized pattern matching, keyword recognition, and simple rule-based approaches to engage in text-based conversations. The advancement of machine learning and AI techniques in the last decade revolutionized the capabilities of bots; rather than relying solely on predefined rules, bots began to employ algorithms that could learn and adapt from data; this allowed for more sophisticated bots to understand context, generate more natural language responses, and provide personalized experiences. Virtual assistants such as Apple’s Siri, Amazon’s Alexa, Google Assistant, and Microsoft’s Cortana emerged as prominent examples of AI-powered bots; these assistants utilize various AI techniques, including natural language understanding, speech recognition, and machine learning to provide users with information, perform tasks, and control smart devices. With the rise of social media platforms, bots have become prevalent in online interactions. Social media bots can automate tasks, engage with users, and disseminate information, however, they have associated malicious activities, such as spreading misinformation and manipulating public opinion with themselves (Yazan Boshmaf).

Scholars have extensively explored the advancements in natural language processing and understanding that have contributed to the improved conversational abilities of bots, techniques such as deep learning, recurrent neural networks, and transformer models have been investigated and applied to enhance bot performance. Researchers have emphasized the importance of personalization and contextual understanding in bot interactions; by leveraging user data and employing reinforcement learning and contextual embeddings, bots can tailor responses to individual users and provide more relevant and meaningful interactions.

Scholars call for responsible bot development practices and integrating ethical considerations into the design and deployment process; studies also explored user experiences and perceptions of bots; usefulness, trust, transparency, and satisfaction influence user acceptance. Researchers have examined different design strategies, conversational styles, and user interfaces to enhance the user experience and foster positive user-bot interactions (Watson, 2022). There is ongoing research and debate surrounding the impact of bots on social media platforms and their potential to manipulate public opinion, spread misinformation, and influence political discourse (Yazan Boshmaf). Scholars continue to investigate detection techniques, countermeasures, and policy implications. AI Bots have immense potential in various industries; future research and practical applications should strive to address ethical concerns, enhance user experiences, and drive responsible and beneficial bot usage in artificial intelligence.

.

Types and Applications of Bots in Artificial Intelligence

There are multiple types of bots that are in use today in various industries. The following list will define some of the most important types.

• Chatbots: Chatbots are designed to interact with users through text or voice-based conversations; they can be found in various applications, including customer support, virtual assistants, and messaging platforms. Chatbots can use rule-based systems or machine-learning techniques to understand user queries and generate appropriate responses.
• Social Media Bots: Social media bots are designed to automate tasks and engage with users on social media platforms; they can perform various activities, such as posting content, liking or sharing posts, following users, and sending messages, while some social media bots serve legitimate purposes, others may be used for malicious activities, such as spreading spam or misinformation.
• Autonomous Bots: Autonomous bots, also known as intelligent agents, are designed to operate independently and make decisions based on their environment or predefined goals, these bots can perceive their surroundings, gather information, and take action to achieve specific objectives. Autonomous bots can be employed in diverse domains, including robotics, autonomous vehicles, and virtual environments.
• Recommendation Bots: Recommendation bots utilize AI algorithms to analyze user preferences, behavior, and historical data to provide personalized recommendations; these bots are commonly used in e-commerce, streaming platforms, and content delivery systems. Recommendation bots aim to enhance the user experience by suggesting products, movies, music, or articles tailored to individual interests.
• Trading Bots: Trading bots, also known as algorithmic trading bots or robo-advisors, use AI techniques to automate financial trading decisions; these bots analyze market data, monitor trends, and execute trades based on predefined strategies. Trading bots aim to optimize trading efficiency, reduce human error, and capitalize on market opportunities.
• Virtual Assistants: Virtual assistants, such as Apple’s Siri, Amazon’s Alexa, Google Assistant, and Microsoft’s Cortana, are AI-powered bots designed to provide users with information, perform tasks, and assist with various activities. Virtual assistants use voice recognition, natural language processing, and machine learning algorithms to understand user queries and execute commands.
• Gaming Bots: Gaming bots are AI agents designed to play games autonomously or assist human players; they can compete against human players or other bots, solve puzzles, and navigate game environments. Gaming bots can employ reinforcement learning and neural networks to learn and improve their gameplay strategies.
• Information Retrieval Bots: Information retrieval bots are designed to retrieve specific information from vast data sources; these bots can search databases, websites, or knowledge bases to provide users with relevant information based on their queries. Search engines, recommendation systems, and question-answering applications commonly use information retrieval bots.

It is important to note that these categories are not mutually exclusive, and there can be overlap between different types of bots. Additionally, the field of bot development and AI is continuously evolving, leading to the emergence of new types of bots with unique functionalities and applications.

Case Studies:

Bots have found applications in various industries, revolutionizing the way businesses operate and interact with customers; below are some case studies from different service industries,

Customer Service

Bank of America’s Erica – Bank of America introduced Erica, an AI-powered virtual assistant, to enhance customer service. Erica assists customers with account balance inquiries, transaction history, bill payments, and budgeting; by leveraging Erica, Bank of America reported increased customer engagement and a significant reduction in call center volume, leading to cost savings and improved customer satisfaction (SCHWARTZ, 2021).

Healthcare:

Babylon Health’s AI Chatbot – Babylon Health developed an AI chatbot that offers symptom checking and medical advice. The chatbot uses machine learning algorithms and a large medical database to provide users with personalized health assessments. In a study published in The Lancet, Babylon’s chatbot demonstrated a diagnostic accuracy comparable to human doctors in primary care settings, highlighting its potential for providing accessible healthcare services (Olsen, 2022).

Finance

Wealthfront’s Robo-Advisor – Wealthfront is an automated investment service that utilizes a robo-advisor. The platform collects user information, including financial goals and risk tolerance, and recommends customized investment portfolios. Wealthfront’s robo-advisor has attracted a significant user base and is known for its low-cost investment options and user-friendly interface (Tepper, 2023).

Marketing

Sephora’s Virtual Artist – Sephora, a cosmetics retailer, introduced the Virtual Artist bot, which uses augmented reality (AR) technology to allow users to try on different makeup products virtually; this bot analyzes facial features and applies makeup digitally, enabling customers to visualize different products before making a purchase. Sephora reported increased user engagement, higher conversion rates, and improved customer satisfaction after implementing the Virtual Artist bot (Rayome, 2018).

E-commerce

H&M’s Chatbot Stylist – H&M developed a chatbot stylist that assists customers with fashion advice and outfit suggestions. Users can interact with the chatbot through messaging platforms and receive personalized recommendations based on their preferences, style, and occasion; this chatbot enhances the shopping experience, increases customer engagement, and drives sales by providing tailored fashion guidance (Global, 2021).

Travel and Hospitality

Marriott International’s Chatbots – Marriott International implemented chatbots across multiple channels to enhance the guest experience. Guests can use the chatbots to make room reservations, request services, and obtain information about hotel amenities; these chatbots provide real-time responses, improve efficiency in handling guest inquiries, and contribute to higher guest satisfaction scores (Schick, 2017)

These examples highlight the positive impact of bots in various applications; they showcase how bots can streamline processes, improve customer experiences, and deliver personalized services, ultimately driving business growth and customer satisfaction.

Advantages of Bot Usage

Bots can automate repetitive and mundane tasks, freeing up human resources to focus on more complex and value-added activities; they can handle large volumes of requests simultaneously, providing quick and consistent responses; this increased efficiency leads to improved productivity and reduced turnaround times. Bots can offer cost savings by reducing the need for extensive human labor; once developed and deployed, bots can handle tasks 24/7 without the need for breaks or overtime pay.

Cost-effectiveness is particularly beneficial in customer service, where bots can handle routine inquiries, reducing the load on human support agents. Bots can quickly scale their operations to handle high volumes of requests without significant additional resources. Whether customer inquiries, order processing, or data analysis, bots can handle increased workloads without experiencing fatigue or performance degradation (Linda Erlenhov, 2020). Bots also can provide consistent and accurate responses, ensuring a uniform customer experience; they can access and analyze vast amounts of data quickly, leading to more accurate and relevant information retrieval and decision-making; this reliability is particularly valuable in applications like healthcare diagnosis or financial analysis.

Limitations and Challenges

Bots raise ethical concerns, particularly in data privacy, transparency, and accountability. Care must be taken to ensure that bots respect user privacy, handle personal data responsibly, and adhere to legal and ethical guidelines; transparency in bot interactions is crucial to maintain trust and avoid deceptive practices (MAIRIELI WESSEL, 2021).

Bots often require access to user data to deliver personalized experiences and perform their tasks effectively, however, this raises privacy concerns as users may be hesitant to share sensitive information with bots (Zeineb Safi, 2020).

Implementing robust security measures and obtaining user consent to address these privacy concerns is essential. Bots learn from data, and if the training data contains biases or reflects societal prejudices, the bots may unintentionally amplify those biases in their responses. Bias detection and mitigation techniques are necessary to ensure fair and unbiased outcomes when deploying bots, especially in sensitive domains like hiring or decision-making.

Editor note: For more information about overcoming bias, check out Yifei Wang’s “Beyond Personalization, Overcoming Bias in Recommender Systems”:

Risks and Drawbacks of Overreliance on Bots

Overreliance on bots may result in a loss of human touch and personalized interactions; some customers prefer human assistance, especially in complex or emotionally sensitive situations, and striking the right balance between bot automation and human support is crucial to meet diverse customer needs. While bots have advanced significantly, they may still struggle with understanding complex or ambiguous queries and context. They may provide inaccurate or incomplete responses, leading to frustration for users. Human intervention may be required to handle such situations, which can limit the full automation potential. Bots operate based on predefined rules or learned patterns; they may struggle with handling unforeseen scenarios or adapting to rapidly changing circumstances. situations requiring creativity, intuition, or complex decision-making may require human judgment and intervention.

It is essential to recognize these limitations and address them through continuous improvement, responsible development practices, and ongoing monitoring of bot performance to ensure their practical and ethical usage in AI applications.

Technical Aspects and Development of Bots

In bot development, various underlying technologies and algorithms are used to enable effective communication and decision-making. Two key components in bot development are natural language processing (NLP) and machine learning (ML).

• Natural Language Processing: NLP begins with tokenization, where sentences or paragraphs are divided into smaller units called tokens, such as words or sub-words. Tokenization helps in breaking down the text into manageable components for analysis; the part-of-speech tagging technique assigns grammatical labels (such as nouns, verbs, and adjectives) to each token, aiding in understanding the syntactic structure of a sentence.

  Named Entity Recognition (NER) identifies and classifies named entities, such as person names, locations, organizations, or dates, within the text; it helps in extracting meaningful information from unstructured data (Gruetzemacher, 2022). Sentiment analysis determines the emotional tone or sentiment expressed in a piece of text; it is used to gauge the sentiment as positive, negative, or neutral, which can be valuable in customer feedback analysis and social media monitoring.

• Machine Learning: Supervised learning involves training a model on labeled data, where inputs are associated with corresponding outputs. In bot development, supervised learning algorithms can be used for tasks like intent classification (identifying the purpose of a user’s query) and named entity recognition (identifying specific entities within a sentence).

  Unsupervised learning algorithms are used when labeled data is not available; they discover patterns or structures in the data without explicit guidance; for example, clustering algorithms can be applied to group similar user queries together, aiding in organizing data or identifying user segments (Linda Erlenhov, 2020).

  Reinforcement learning involves training an agent to interact with an environment and learn optimal actions based on rewards and penalties; it can be utilized to develop bots capable of learning from user interactions and improving their decision-making over time. Deep learning uses artificial neural networks with multiple layers to learn complex patterns from data.

  Convolutional Neural Networks (CNNs) are commonly used for tasks involving image analysis, while Recurrent Neural Networks (RNNs) and Transformers are effective for sequential data, such as text or speech. Deep learning has significantly advanced various aspects of bot development, including language understanding, dialogue generation, and image recognition.

These underlying technologies and algorithms form the foundation of bot development, enabling bots to understand user inputs, extract relevant information, and generate appropriate responses. Combining NLP and ML techniques allows bots to communicate effectively and interact intelligently with users.

Building and training bots:

Building and training bots involve several key steps; data collection, preprocessing, and model selection. Below are the main steps involved:

1. Define Bot Objectives: Clearly define the objectives and purpose of the bot. Determine the specific tasks it needs to perform, such as answering FAQs, providing recommendations, or handling transactions.
2. Data Collection: Gather relevant data to train and develop the bot, including historical customer interactions, user queries, or publicly available datasets. Data can be collected from websites, social media platforms, customer support logs, or existing databases.
3. Data Preprocessing: Clean the collected data to remove noise, errors, and redundant information; this involves tasks such as removing duplicate entries, handling missing values, and correcting inconsistencies. Transform the raw text data into a format suitable for analysis; steps may include tokenization (breaking text into words or sub-words), removing stop words, handling special characters, and normalizing the text by applying techniques like stemming or lemmatization (Gaddam, 2017).
4. Data Annotation (Optional): In supervised learning scenarios where labeled data is required, data annotation is necessary. Experts or annotators label the data with relevant tags or classes, indicating the intent, sentiment, or named entities in the text. Annotation can be done manually or with the help of automated tools.
5. Model Selection: Select appropriate models and algorithms based on the bot’s objectives and the nature of the task; this involves choosing the appropriate machine learning algorithms, neural network architectures, or pre-trained models that best suit the bot’s requirements, consider factors such as the complexity of the task, available computational resources, and the size and quality of the dataset.
6. Training the Bot: Divide the preprocessed data into training, validation, and test sets; the training set is used to train the bot, the validation set helps optimize model parameters, and the test set evaluates the final performance of the trained bot. Extract relevant features from the data to represent the input to the model effectively; this may involve techniques like bag-of-words, TF-IDF (Term Frequency-Inverse Document Frequency), or word embeddings to capture semantic meaning. Train the selected model using the training data; this involves feeding the input features into the model, adjusting its parameters, and optimizing the loss function to minimize errors or maximize performance metrics. Assess the performance of the trained model using the validation set. Evaluate metrics such as accuracy, precision, recall, F1 score, or area under the curve (AUC) to gauge the performance of the model (Gaddam, 2017).
7. Model Fine-Tuning and Iteration: Analyze the model’s performance and fine-tune hyperparameters, architecture, or preprocessing steps if necessary; this iterative process helps optimize the bot’s performance and ensures it meets the desired objectives.
8. Deployment and Testing: Once the model is trained and validated, deploy the bot in a production environment, and test its functionality, performance, and integration with other systems. Monitor and collect user feedback to identify areas for improvement (Gaddam, 2017).

Throughout the development process, it is essential to maintain documentation, perform version control, and adhere to ethical guidelines, ensuring transparency, fairness, and privacy considerations are addressed; by following these key steps, developers can build and train bots that effectively understand user inputs, provide accurate responses, and deliver a seamless user experience (Linda Erlenhov, 2020).

Ethical and Legal Implications

Using bots in various applications raises important ethical considerations that need to be addressed to ensure responsible and fair deployment. Bots should strive to be transparent about their nature and capabilities; users should be informed that they are interacting with a bot rather than a human, which fosters trust and sets appropriate expectations. Additionally, when bots make decisions or provide responses, they should offer explanations or justifications to the user, helping them understand the underlying processes and building user confidence.

Bot developers and organizations deploying bots should take responsibility for the actions and decisions of their bots; this includes addressing issues such as the accuracy and reliability of the information provided by bots, ensuring compliance with legal and regulatory requirements, and taking measures to prevent the misuse of bots for harmful or malicious purposes (Evan Nadel, 2019).

Bots can inadvertently perpetuate biases present in the data they are trained on, leading to biased or unfair outcomes; bias can occur in various forms, including racial, gender, or socio-economic biases; it is crucial to mitigate such biases during bot development by carefully curating training data, using diverse datasets, and regularly auditing and monitoring the performance of bots to identify and rectify any bias-related issues. Bots often process and store user data, making privacy and data protection critical concerns; organizations must ensure that appropriate data protection measures are in place, including obtaining user consent, anonymizing or pseudonymizing data when possible, securely storing and transmitting data, and adhering to applicable privacy regulations (Zakir, n.d.).

Users should have clear information about how bots collect, store, and use their data. Organizations should seek user consent for data collection and provide options for users to control the extent of data sharing and the use of their personal information; bots should not engage in deceptive or manipulative behaviors that exploit users’ vulnerabilities or trust. Developers should adhere to ethical guidelines that prevent bots from engaging in activities that could harm users or manipulate their behavior; continuous monitoring and evaluation of both performance and user feedback are essential to identify and address ethical concerns that may arise during the deployment of bots. Regular audits and assessments can help detect and rectify ethical issues, ensuring that bots align with ethical standards and user expectations.

Addressing these ethical considerations requires a combination of technical measures, organizational policies, and regulatory frameworks. Bot developers and organizations must be proactive in designing and deploying bots that prioritize transparency, fairness, and user well-being, fostering trust and responsible use of AI technologies.

Legal frameworks and regulations:

Legal frameworks and regulations governing bot development and deployment vary across different jurisdictions.

Privacy Laws and Data Protection: General Data Protection Regulation (GDPR), applicable in the European Union (EU) and European Economic Area (EEA), sets standards for the collection, storage, and processing of personal data. Bots that handle user data must comply with GDPR requirements, including obtaining user consent, ensuring data security, providing transparency about data usage, and offering users the right to access, rectify, and erase their data. California Consumer Privacy Act (CCPA) imposes obligations on businesses that collect or sell personal information of California residents; bots operating in California or handling data of California residents must comply with CCPA requirements, which include disclosing data collection practices, granting users the right to opt-out of data sales, and providing mechanisms for users to request access to and deletion of their personal information (Evan Nadel, 2019). Many countries have their own data protection laws, such as the Personal Information Protection and Electronic Documents Act (PIPEDA) in Canada and the Personal Data Protection Act (PDPA) in Singapore; these laws define rules for handling personal data and may impose specific requirements on bot developers and operators.

Intellectual Property Rights: Bot developers must respect copyright and trademark laws when designing bots; unauthorized use of copyrighted content, logos, or trademarks can lead to legal implications; bots should be developed to avoid infringing upon intellectual property rights (Zakir, n.d.).

Consumer Protection: Bots must comply with laws that prohibit unfair or deceptive practices in consumer interactions; bots should not engage in misleading behaviors, false advertising, or misrepresentation that could harm consumers (Subhajit Basu, 2018).

Sector-Specific Regulations:

Financial Industry: Bots used in financial services may need to comply with specific regulations such as the Know Your Customer (KYC) and Anti-Money Laundering (AML) requirements. Regulations like the Payment Services Directive (PSD2) in the EU also govern the use of bots in financial transactions and require specific security measures.

Healthcare: Bots used in healthcare must comply with regulations such as the Health Insurance Portability and Accountability Act (HIPAA) in the United States, which governs the privacy and security of patient’s health information.

Liability and Accountability:

Legal responsibility for the actions of bots can be complex, depending on the jurisdiction; liability may fall on the bot developer, the organization deploying the bot, or both; clear contractual agreements and terms of service can help define the responsibilities and limitations of liability. Both developers and organizations must consult with legal professionals and stay updated on the relevant laws and regulations in the jurisdictions where their bots are developed or deployed (Pavel P Baranov, 2019). Compliance with applicable legal frameworks helps ensure the responsible and lawful development and deployment of bots while protecting the rights and interests of users and consumers.

Future Directions and Challenges:

The field of bot usage in artificial intelligence is evolving rapidly, and several emerging trends and potential future developments are shaping its trajectory. Key areas to focus on,

Conversational AI Advancements: Conversational AI aims to develop more human-like and natural interactions between bots and users; future developments may include improved language understanding, context-aware responses, and the ability to handle complex dialogues, emotions, and sarcasm. Advancements in language models, such as incorporating world knowledge and commonsense reasoning, could contribute to more sophisticated and engaging conversations.

• Multimodal Interactions: Bots that can process and generate multiple modalities like text, speech, images, and videos are gaining traction; future developments may involve enhancing bots’ ability to understand and generate responses using different modalities simultaneously, enabling more immersive and interactive experiences.
• Personalization and User Adaptation: The future of bot development will likely focus on personalization and user adaptation; bots may leverage user preferences, historical interactions, and contextual information to deliver tailored and personalized experiences. Advanced user modeling techniques and reinforcement learning approaches could enable bots to adapt their behavior and responses based on individual user characteristics and preferences (Yazan Boshmaf).
• Integration with IoT and Smart Devices: Bots are expected to play a significant role in the Internet of Things (IoT) ecosystem, where they can interact with and control various smart devices. Integration with IoT devices like smart home assistants, wearables, and connected appliances could enable bots to perform tasks such as home automation, health monitoring, and personalized recommendations.
• Enhanced Bot Collaboration: Bots that collaborate with each other to solve complex tasks or provide comprehensive services hold promise; future developments may involve creating bot ecosystems where specialized bots work together, leveraging their unique capabilities and knowledge, to provide more comprehensive and seamless user experiences (Watson, 2022).
• Ethical and Responsible AI: As the use of bots expands, there will be a growing emphasis on ethical and responsible AI practices; future developments may include the integration of fairness, interpretability, and explainability techniques into bot systems to ensure transparency, reduce biases, and enable users to understand the basis of both decisions.
• Augmented Intelligence: The future of bot usage may involve augmenting human intelligence with bot capabilities; bots can assist humans in tasks by providing real-time information, automating repetitive processes, and offering decision support. Augmented intelligence systems that combine the strengths of humans and bots have the potential to enhance productivity and problem-solving across various domains significantly.
• Social and Emotional Intelligence: Advancements in understanding social cues, emotions, and empathy could lead to the development of bots that exhibit greater social and emotional intelligence, bots with emotional understanding capabilities could offer empathetic responses, provide mental health support, or assist in social contexts where emotional intelligence is essential.

These emerging trends and potential future developments in bot usage indicate a shift toward more sophisticated, personalized, and socially aware bot systems. As AI technologies continue to advance, these developments have the potential to reshape various industries, transform user experiences, and enable new applications for bots in diverse domains.

Conclusion

Bots play a significant role in artificial intelligence by automating tasks, providing information, and engaging with users. The advantages of using bots in AI include increased efficiency, cost-effectiveness, scalability, and round-the-clock availability. Bots have limitations and challenges, including ethical considerations, privacy concerns, potential biases, and the risk of overreliance on bots in certain contexts.

Future research should focus on improving bot-human interaction, enhancing natural language understanding, and developing more human-like and contextually aware responses. Challenges like ethical consideration and data privacy need to be addressed at the forefront of both developments to ensure responsible and fair deployment. Collaborative research efforts and interdisciplinary approaches are crucial for advancing the field of bot development and deploying bots in a manner that aligns with user needs, societal values, and ethical standards.

Overall, bots have emerged as powerful tools in the field of artificial intelligence, with the potential to revolutionize various industries and enhance user experiences. Continued research and responsible deployment of bots can unlock new opportunities, drive innovation, and address challenges to maximize their positive impact.

---

Bibliography

Evan Nadel, N. P. (2019, November). Data Collection & Management, Professional Perspective – Legal Implications of Using AI, Biometrics, or Bots in the Workplace. Opgehaald van https://www.bloomberglaw.com/external/document/XEEJ0U5S000000/data-collection-management-professional-perspective-legal-implic

Gaddam, K. (2017). Building Bots with Microsoft Bot Framework. Packt Publishing Chatbots .

Global, M. O. (2021, March 11). Conversational AI in eCommerce: 9 of the Most Successful Chatbot Examples. Opgehaald van https://masterofcodeglobal.medium.com/conversational-ai-in-ecommerce-9-of-the-most-successful-chatbot-examples-89bc5e1569b3

Gruetzemacher, R. (2022, April 19). AI And Machine Learning. Opgehaald van https://hbr.org/2022/04/the-power-of-natural-language-processing

How does an AI chatbot work? (sd). Opgehaald van https://powervirtualagents.microsoft.com/en-us/ai-chatbot/

Jarow, O. (2023, March 5). How the first chatbot predicted the dangers of AI more than 50 years ago. Opgehaald van https://www.vox.com/future-perfect/23617185/ai-chatbots-eliza-chatgpt-bing-sydney-artificial-intelligence-history

Linda Erlenhov, F. G. (2020). An Empirical Study of Bots in Software Development: Characteristics and Challenges from a Practitioner’s Perspective. ACM Joint European Software Engineering Conference and Symposium on the Foundations of Software Engineering (ESEC/FSE). arXiv:2005.13969.

MAIRIELI WESSEL, I. W. (2021). Don’t Disturb Me: Challenges of Interacting with Software Bots on Open Source Software Projects. J. ACM, Vol. 37, No. 4, Article 111. Publication date: August 2021.

Mondal, A. (2023, June 26). Complete Guide to Build Your AI Chatbot with NLP in Python. Opgehaald van https://www.analyticsvidhya.com/blog/2021/10/complete-guide-to-build-your-ai-chatbot-with-nlp-in-python/

Olsen, E. (2022, March 31). Babylon’s AI-enabled symptom checker added to recently acquired Higi’s app. Opgehaald van https://www.mobihealthnews.com/news/babylons-ai-enabled-symptom-checker-added-recently-acquired-higis-app

Osuch, W. (2022, April 19). Chatbots: A Brief History Part I – 1960s to 1990s. Opgehaald van https://www.botsplash.com/post/chatbots-a-brief-history

Pavel P Baranov, A. Y. (2019, 12 27). Problems of Legal Regulation of Robotics and Artificial Intelligence from the Psychological Perspective. Opgehaald van https://files.eric.ed.gov/fulltext/EJ1280119.pdf

Rayome, A. D. (2018, February 15). How Sephora is leveraging AR and AI to transform retail and help customers buy cosmetics. Opgehaald van https://www.techrepublic.com/article/how-sephora-is-leveraging-ar-and-ai-to-transform-retail-and-help-customers-buy-cosmetics/

Salton, G. (1966). ELIZA—a computer program for the study of natural language communication between man and machine. Communications of the ACM.

Schick, S. (2017, November 8). Marriott makes more room for chatbots to enhance guest experiences. Opgehaald van https://www.marketingdive.com/news/marriott-makes-more-room-for-chatbots-to-enhance-guest-experiences/510117/

SCHWARTZ, E. H. (2021, April 21). Bank of America’s Virtual Assistant Erica Explodes in Popularity. Opgehaald van https://voicebot.ai/2021/04/21/bank-of-americas-virtual-assistant-erica-explodes-in-popularity/

Subhajit Basu, A. O. (2018, May 8). Legal framework for small autonomous agricultural robots. Opgehaald van https://link.springer.com/article/10.1007/s00146-018-0846-4

Tepper, T. (2023, March 24). Wealthfront Review 2023. Opgehaald van https://www.forbes.com/

Watson, I. (2022, September 22). The ultimate guide to machine-learning chatbots and conversational AI. Opgehaald van https://www.ibm.com/watson-advertising/thought-leadership/machine-learning-chatbot

Weizenbaum, J. (1966, January). ELIZA–A Computer Program For the Study of Natural Language Communication Between Man and Machine. Opgehaald van https://redirect.cs.umbc.edu/courses/331/papers/eliza.html

What is a bot? (sd). Opgehaald van https://ift.tt/SqZy3Vo

What Is Artificial Intelligence? (2023, January 5). Opgehaald van https://www.indianai.in/what-is-artificial-intelligence/

Yazan Boshmaf, I. M. (sd). Key Challenges in Defending Against Malicious Socialbots. University of British Columbia, Vancouver, Canada.

Zakir, T. (sd). Opgehaald van ChatGPT: The Bot That Needs a Chat About Regulation: https://www.nyujlb.org/single-post/chatgpt-the-bot-that-needs-a-chat-about-regulation

Zeineb Safi, A. A.-A. (2020). Technical Aspects of Developing Chatbots for Medical Applications: Scoping Review. Journal of Medical Internet Research.

 

The post Bots Usage in Artificial Intelligence appeared first on Simple Talk.

from Simple Talk https://ift.tt/jZWBVdl
via