Category: Databases

Performance Monitoring and Tuning in Relational Databases: With Observability Lagniappe

This is a continuation of my posts on relational databases, started here. Previous posts on this theme, “Data Modeling“, “Let’s Talk About Database Schema“, “The Exasperating Topic of Database Indexes“, “The Keys & Relationships of Relational Databases“, “Relational Database Query Optimization“, “Normalization in Relational Databases“, “Database (DB) Caching and DB Tertiary Caching“, “Security and Authentication in Relational Databases“, A Guide to Backup and Recovery Options for Relational Databases: Focusing on SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL, and Concurrency Control in Relational Databases.

In the ever-evolving landscape of software development, the performance of relational databases isn’t just a feature; it’s a critical component of system health that can dramatically influence user satisfaction and operational efficiency. We’re starting a deep dive into the world of performance monitoring and tuning for relational databases, uncovering practices essential for maintaining robustness and speed during database interactions in this post.

Continue reading “Performance Monitoring and Tuning in Relational Databases: With Observability Lagniappe” →

Concurrency Control in Relational Databases

This is a continuation of my posts on relational databases, started here. Previous posts on this theme, “Data Modeling“, “Let’s Talk About Database Schema“, “The Exasperating Topic of Database Indexes“, “The Keys & Relationships of Relational Databases“, “Relational Database Query Optimization“, “Normalization in Relational Databases“, “Database (DB) Caching and DB Tertiary Caching“, “Security and Authentication in Relational Databases“, and A Guide to Backup and Recovery Options for Relational Databases: Focusing on SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL.

Concurrency control in relational databases is a fundamental concept that ensures the integrity and consistency of data when multiple transactions are occurring simultaneously.

Real World Example

Imagine a bustling city where everyone is trying to get their tasks done efficiently and without stepping on each other’s toes. That’s what concurrency control is like in the world of relational databases. It’s all about managing the simultaneous operations on data in a way that ensures everyone gets their job done without causing chaos or data traffic jams.

In the realm of relational databases, transactions are like the citizens of our city. Each transaction wants to read, update, or delete data. Concurrency control is the set of traffic rules that governs how these transactions interact with each other. Without these rules, you’d have data inconsistencies, like two transactions trying to update the same piece of data at the same time, leading to conflicts and errors.

There are several methods to manage concurrency, each with its own pros and cons. Locking is like having traffic lights at intersections; it prevents conflicts by restricting access to data while a transaction is using it. However, just like traffic lights can cause delays, locking can lead to reduced database performance, especially if not managed well.

Another method is optimistic concurrency control. This is more like a roundabout, where transactions proceed assuming there won’t be conflicts, and if they do occur, the transactions are rolled back and retried. It’s efficient when conflicts are rare, but can be problematic when traffic is heavy.

Then there’s timestamp ordering, akin to giving each transaction a timestamp and ensuring they’re processed in that order. It’s like organizing our city’s tasks based on a schedule to avoid conflicts.

The choice of concurrency control depends heavily on the specific needs and characteristics of the database and its workload. Testing, understanding, and knowing what workloads demand and how the data interacts inbound and outbound, as well as internally to the database is key to determining the concurrency control paradigms to use.

Continue reading “Concurrency Control in Relational Databases” →

A Guide to Backup and Recovery Options for Relational Databases: Focusing on SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL

This is a continuation of my posts on relational databases, started here. Previous posts on this theme, “Data Modeling“, “Let’s Talk About Database Schema“, “The Exasperating Topic of Database Indexes“, “The Keys & Relationships of Relational Databases“, “Relational Database Query Optimization“, “Normalization in Relational Databases“, “Database (DB) Caching and DB Tertiary Caching“, and “Security and Authentication in Relational Databases“.

In the realm of data management, ensuring the safety and recoverability of data is paramount. Relational databases, being at the core of many business operations, require robust backup and recovery strategies. This article delves into the general concepts of backup and recovery in relational databases, followed by specific strategies for SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL.

General Concepts of Backup and Recovery in Relational Databases

Types of Backups

  1. Full Backup: Captures the entire database at a point in time.
  2. Incremental Backup: Records the changes since the last backup.
  3. Differential Backup: Stores data changed since the last full backup.
  4. Log Backup: Specific to databases that maintain transaction logs.

Recovery Models

  • Simple Recovery Model: Ideal for scenarios where data recovery to a specific point in time is not critical.
  • Full Recovery Model: Necessary for databases where data loss is unacceptable, allowing recovery to a specific point in time.
  • Bulk-Logged Recovery Model: A variation of the full recovery model, optimizing for bulk operations.

Considerations for Backup and Recovery

  • Recovery Point Objective (RPO): Determines how much data loss is acceptable.
  • Recovery Time Objective (RTO): The acceptable time to restore operations after a failure.
  • Storage Media: Choosing between on-site, off-site, or cloud storage.
  • Encryption and Security: Ensuring data is protected during and after the backup process.
  • Regular Testing: Regularly testing backups to ensure data integrity.

Specific Strategies for Each Database

Continue reading “A Guide to Backup and Recovery Options for Relational Databases: Focusing on SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL” →

Security and Authentication in Relational Databases

This is a continuation of my posts on relational databases, started here. Previous posts on this theme, “Data Modeling“, “Let’s Talk About Database Schema“, “The Exasperating Topic of Database Indexes“, “The Keys & Relationships of Relational Databases“, “Relational Database Query Optimization“, “Normalization in Relational Databases“, and “Database (DB) Caching and DB Tertiary Caching“.

Basic Security Architecture

Relational Database Management Systems (RDBMS) like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL are designed with robust security architectures. The core components typically include:

  1. Authentication: Verifying the identity of users accessing the database.
  2. Authorization: Determining what authenticated users are allowed to do.
  3. Access Control: Implementing permissions and roles to manage user access.
  4. Encryption: Protecting data at rest and in transit.
  5. Auditing and Logging: Tracking access and changes to the database for security and compliance.

Authentication Mechanisms

Authentication is the first line of defense in database security. It ensures that only legitimate users can access the database. The primary methods include:

  • Username and Password: The most common method, where users provide credentials that are verified against the database’s security system.
  • Integrated Security: Leveraging the operating system or network security (like Windows Authentication in SQL Server) for user validation.
  • Certificates and Keys: Using digital certificates or cryptographic keys for more secure authentication.

Database-Specific Security and Authentication

SQL Server

  • Windows Authentication: Integrates with Windows user accounts, providing a seamless and secure authentication mechanism.
  • SQL Server Authentication: Involves creating specific database users and passwords.
  • Roles and Permissions: SQL Server offers fixed server roles, fixed database roles, and user-defined roles for granular access control.
  • SSO Integration: Supports integration with Active Directory for single sign-on capabilities.

Oracle

  • Oracle Database Authentication: Users are authenticated by the database itself.
  • OS Authentication: Oracle can use credentials from the operating system.
  • Roles and Privileges: Oracle has a sophisticated role-based access control system, with predefined roles like DBA and user-defined roles.
  • Oracle Wallet: For storing and managing credentials, enhancing security for automated login processes.

MariaDB/MySQL

  • Standard Authentication: Username and password-based, with the option of using SHA256 for password encryption.
  • Pluggable Authentication: Supports external authentication methods like PAM (Pluggable Authentication Modules) or Windows native authentication.
  • Role-Based Access Control: Introduced in later versions, allowing for more flexible and manageable permissions.
  • SSL/TLS Encryption: For securing connections between the client and the server.

PostgreSQL

  • Role-Based Authentication: PostgreSQL uses roles to handle both authentication and authorization.
  • Password Authentication: Supports MD5 or SCRAM-SHA-256 for password encryption.
  • Peer Authentication: For local connections, relying on OS user credentials.
  • SSO Integration: Can integrate with external authentication systems like Kerberos.
Continue reading “Security and Authentication in Relational Databases” →

Java Time with Introspective GraphQL on Chaos Database AKA Pre- Refactor Prototype Mutating Database Spring Boot Java Hack App

With the previous work to get a testing environment built and running done (in Python), I was ready to get started on the GraphQL API as previously described. As a refresher, this description,

singular mission to build a GraphQL API against a Mongo database where the idea is, one could query the underlying collections, documents, and fields with the assumption that users would be adding or possibly removing said collections, documents, and fields as they needed.

My intent is to build with with a Java + Spring stack. Just like with the Python app in the previous post, the first thing I like to do is just get the baseline GraphQL API “Hello World” app up and running.

At the end of this post I’ll include/link the Github repository.

Phase 1: Getting the Initial GraphQL API Compiling & Running with a “Hello World”.

Prerequisites & Setup

  • The post previous to this “Fruit and Snakes: Frequent Mutative Mongo User Database with Python” I created the Mongo Database and setup the app that would create, every few seconds, new collections, documents, and other collateral to put into a Mongo database for the sole purpose of creating this GraphQL API.
  • I’ll be using Java 17 for this work, so to ensure the least risk of versioning issues, get Java 17. The same goes for Spring 3. I’ve shown my selections from the Spring Initializr (not using Intellij? Cool, get a start with the Spring Initializr Site) in the screenshots that follow.
Continue reading “Java Time with Introspective GraphQL on Chaos Database AKA Pre- Refactor Prototype Mutating Database Spring Boot Java Hack App” →