Databases News, Latest Updates and Recent Announcements on Techminis

A naukri.com initiative

New

Home

Databases

Dbi-Services

280

Image Credit: Dbi-Services

PostgreSQL Conference Europe 2024 – Day 2

The second day of the PostgreSQL Conference Europe 2024 in Athens covered a range of technical sessions on topics like upgrading PostgreSQL, handling statistics, and using DBA skills in the cloud.
One session focused on upgrading PostgreSQL installations and highlighted the importance of logical replication and careful schema design for smooth upgrades.
Another session explored the inner workings of PostgreSQL's statistics collection, providing insights on optimizing query performance.
A talk from Microsoft announced the first iteration of PostgreSQL Query Store in Azure, offering performance tuning tools for PostgreSQL users.
The final session explained how PostgreSQL operators in Kubernetes can simplify database management in containerized environments, bridging the gap between traditional administration and cloud-native practices.

Read Full Article

16 Likes

Medium

362

The Comprehensive Guide to SQL: Unlocking the Power of Data

SQL is a standardized language used to manage and manipulate relational databases.
The SELECT statement allows you to retrieve data from one or more tables.
The WHERE clause filters records based on specific conditions.
JOINs allow you to combine rows from multiple tables based on related columns.

Read Full Article

21 Likes

Amazon

147

Image Credit: Amazon

Visualize vector embeddings stored in Amazon Aurora PostgreSQL and explore semantic similarities

Amazon Aurora PostgreSQL-Compatible Edition supports the pgvector extension that enables manipulating vector embeddings in relational databases.
Amazon Bedrock is a fully-managed AWS service that offers various foundation models (FMs), including Amazon Titan Text Embeddings.
Preparing dataset, generating vector embeddings, storing data and embedding in Aurora PostgreSQL database and importing required libraries are the steps to visualize vector embeddings and explore semantic similarities.
PCA is used to transform high-dimensional data into a lower-dimensional space, which enables visualizing the underlying structure of the data.
This article provides a step-by-step guide for preparing dataset, using Bedrock to generate vector embeddings, storing data and embedding in PostgreSQL table, performing dimensionality reduction with PCA technique and using the plotly package to plot a 3D Scatter Plot of Categories.
The resulting three-dimensional scatter plot shows items with similar meanings clustered closely in the embedding space. This proximity ensures that when we perform a semantic search on this dataset for a specific item, it returns products with similar semantics.
The integration of vector data types in Aurora PostgreSQL-Compatible opens up exciting possibilities for exploring semantic similarities and visualizing complex data structures.
By using techniques such as PCA, users can gain valuable insights into their data, uncover hidden patterns, and make informed decisions.
Clean up the resources used after the completion of tasks to avoid incurring charges.
The author of this article, Ravi Mathur, is a Sr. Solutions Architect at AWS who provides technical assistance and architectural guidance on various AWS services.

Read Full Article

8 Likes

Cloudblog

321

Image Credit: Cloudblog

What’s new in PostgreSQL 17, now available in Cloud SQL

Cloud SQL now supports PostgreSQL 17, offering a range of new features and enhancements in security, developer experience, performance, tooling, and observability.
PostgreSQL 17 introduces the MAINTAIN privilege, offering a more granular level of control over database maintenance tasks with predefined role called pg_maintain.
The MERGE command is a powerful addition that simplifies data manipulation and improves performance.
JSON_TABLE function introduces a more intuitive way to convert JSON data into a standard table format, unlike earlier methods.
PostgreSQL 17 introduces new, more efficient TidStore to store tuple IDs during VACUUM operations, reducing significantly memory consumption.
Improvement to the ReadBuffer API in PostgreSQL 17 introduces reading multiple consecutive blocks from disk into shared buffers with one system call, benefiting workloads that involve reading multiple blocks.
PostgreSQL 17 introduces the --filter option in pg_dump, pg_dumpall, pg_restore, providing more fine-grained control over what objects are included or excluded in the dump or restore operation.
The new pg_wait_events system view provides information about events causing processes to wait and identify performance bottlenecks and troubleshooting database issues.
The pg_stat_checkpointer system view provides information related to the checkpoint process, allowing you to monitor and analyze checkpoint performance.
Cloud SQL PostgreSQL 17 is available on Google Cloud, with significant advancements that streamline database operations and improve management capabilities.

Read Full Article

19 Likes

Dev

114

Image Credit: Dev

Pragma Autonomous Transaction in Oracle PL/SQL

A Pragma Autonomous Transaction in Oracle PL/SQL allows you to run a transaction independently of the main transaction.
You declare a block of code as an autonomous transaction using the following syntax: PRAGMA AUTONOMOUS_TRANSACTION;
Since it operates independently, you must explicitly commit or roll back the changes inside the autonomous transaction block.
An autonomous transaction lets you log some actions (like inserting or updating data) even if the main transaction fails.
Autonomous transactions in Oracle PL/SQL are typically used for logging, auditing, or performing operations that should not be affected by the success or failure of the main transaction.
Main Transaction and Autonomous Transaction are separate: Changes in the autonomous transaction (logging the message) are committed independently, even if the main transaction rolls back.
The main transaction attempts to insert a new employee into the employees table. It also logs the action using an autonomous transaction, which commits the log entry immediately, regardless of the main transaction's outcome.
When the main transaction rolls back due to an issue, the employee record for Michael Brown is not added, but the log entry remains in the audit_log table, illustrating the independent nature of autonomous transactions.
This is why autonomous transactions are often used for logging so you can record important information regardless of other transaction outcomes.
PRAGMA AUTONOMOUS_TRANSACTION; declares the procedure as an autonomous transaction.

Read Full Article

6 Likes

Dev

455

Image Credit: Dev

Indexes in SQL | Clustered and Non Clustered Index

Indexes in SQL are special data structures designed to optimize the speed of data retrieval operations from database tables.
There are two types of indexes in SQL: clustered index and non-clustered index.
A clustered index determines the physical order of data in a table, while a non-clustered index creates a separate structure with pointers to the physical data.
Clustered indexes are efficient for range queries and faster retrieval for queries filtering on the clustered index column, while non-clustered indexes improve performance for queries on non-primary key columns.

Read Full Article

27 Likes

Dev

128

Image Credit: Dev

Best Practices for Handling Sensitive Data in MySQL Databases

Protecting sensitive data is critical in the digital landscape, particularly from cyber threats.
MySQL offers TDE to encrypt sensitive data stored in the database.
TLS or SSL must be used to encrypt the data transmitted between the application and MySQL database.
Storing user passwords in plain text should be avoided, and strong hashing algorithms like bcrypt, Argon2, or PBKDF2 must be used.
MySQL allows the creation of roles with specific privileges to implement strict access controls.
Regular auditing of user permissions for appropriate and up-to-date access rights is essential.
The best practice for configuration must be followed to secure the MySQL installation.
The system credentials must be kept secure by using environment variables instead of storing sensitive configuration data in application code.
Data masking techniques must be utilized in the development or testing environments.
Encryption and secure storage practices must be implemented for backups.

Read Full Article

7 Likes

Dbi-Services

423

Image Credit: Dbi-Services

PostgreSQL Conference Europe 2024 – Day 1

Attending PostgreSQL Conference Europe 2024 in Athens is an incredible experience, full of enriching conversations and technical deep dives.
The atmosphere was welcoming, with everyone eager to exchange knowledge and showcase their passion for PostgreSQL.
Keynote highlighted the licensing structure of PostgreSQL, emphasizing its free and open-source nature.
Sessions covered topics like high concurrency, distributed snapshots, security attacks, and Patroni deployment patterns.

Read Full Article

25 Likes

Dev

317

Image Credit: Dev

Essential MySQL Operators and Their Applications

MySQL operators are key tools for developers, enabling precise data manipulation and analysis.
The guide introduces the most important MySQL operators and demonstrates how to use them with practical examples.
Operators covered include :=, >>, BETWEEN X and Y, IS | IS NOT, RLIKE, and SPACE().
Understanding MySQL operators is crucial for efficient database management and query optimization.

Read Full Article

19 Likes

Medium

404

Image Credit: Medium

Unlocking the Power of SQL: The Language of Databases

SQL is a powerful language for managing data in databases like Oracle, MySQL, and SQL Server.
SQL has an intuitive syntax and is standardized, making it accessible and consistent across multiple databases.
It allows for filtering, sorting, grouping, and aggregating data, ensuring data integrity and efficient management of large datasets.
SQL has built-in functions for summarizing and analyzing data, making it essential for data analysts.

Read Full Article

24 Likes

Medium

105

Image Credit: Medium

Save Time by Creating Views in SQL

Creating views in SQL can save time for programmers and allow for faster data access and organization.
Views are pre-formatted queries that can be saved and called, ultimately eliminating the need to re-write code.
While not necessary for every query written, views should be created for common logic and frequently accessed queries.
Views can be joined together to encapsulate logic or create tables for tens or hundreds of millions of rows.
Views are not inherently fast, but can be optimized and used to pull the most recent data.
Going through views daily can help catch mistakes and improve logic which ultimately speeds up data analysis.
By creating layers and reusable code, programmers can focus on data quality and analysis rather than figuring out how to pull the data repeatedly.
Creating a fact layer that uses the cleanest views that programmers have created can serve as a single source of truth.
In the end, organizing and creating views will improve proficiency and make data analysis a smoother and more efficient process for all involved.
These views can be beneficial to data scientists, data analysts and data engineers creating pipelines for everyone involved in the data management process.

Read Full Article

6 Likes

Amazon

321

Image Credit: Amazon

Evaluating the right fit for your Amazon Aurora workloads: provisioned or Serverless v2

Amazon Aurora is a managed, cloud-native relational database service with MySQL and PostgreSQL compatibility, designed to offer the performance and availability of commercial databases at a lower cost.
Aurora uses distributed architecture that separates compute capacity from storage, providing up to 128 TB of storage, high performance, and 99.99% availability.
Important concepts of Aurora provisioned and Aurora Serverless v2 databases include clusters and instances, advantages of this architecture, and capacity and pricing.
Aurora Serverless v2 scales the size of an Aurora instance based on workload and enables a richer feature compatibility with provisioned instances.
Capacity of Aurora Serverless v2 is defined by Aurora Capacity Unit (ACU), and pricing is based on ACU-hours, which ensures the most precise fit of resources to your workload.
Aurora Serverless v2 is available in all regions where Amazon Aurora is available, and the version support is similar to provisioned instances.
Aurora Serverless v2 ensures feature compatibility and upgrades are the same as for provisioned instances.
When planning for performance with Aurora Serverless v2, you will need to consider additional factors like minimum ACU, rate of instance scale, and buffer pool capacity, among others.
Amazon Aurora provides a cluster endpoint which always points to the current writer node in the cluster, and Aurora Global Database offers the ability to maintain a live copy of your database cluster in up to five additional AWS Regions.
Provisioned instances and Aurora Serverless v2 instances are complementary; choose which one is the best fit for your workload and validate these assumptions to optimize your database solutions on AWS.

Read Full Article

19 Likes

VentureBeat

340

Differentiable Adaptive Merging is accelerating SLMs for enterprises

Differentiable Adaptive Merging (DAM) is offering a new approach to model merging, addressing the complexity of the process.
Arcee, a company specializing in small language models, is leading research on DAM.
DAM adjusts the contribution of each model through scaling coefficients and optimizes the merge process using established machine learning techniques.
DAM performs competitively with existing methods and offers efficient merging of specialized models.

Read Full Article

20 Likes

The New Stack

349

Image Credit: The New Stack

How To Stop Worrying and Start Loving Databases on Kubernetes

Kubernetes has transformed deployment and management for cloud native applications. However, many teams still hesitate when running databases on Kubernetes due to concerns about storage persistence, data integrity and operational complexity.
Kubernetes uses the term 'Operator Pattern' to define an algorithm for managing stateful workloads. Kubernetes Operators are codebases that encapsulate operational knowledge into automation tasks that manage stateful deployments on Kubernetes.
When choosing a Kubernetes Operator, essential factors to consider include: backups, monitoring, disaster recovery, high availability and connection scaling.
When it comes to cloud native principles, storage architecture, disk storage solutions and object storage should be considered for backups and transaction logs.
Kubernetes' strength is scaling applications effortlessly. Modern Kubernetes database operators enable vertical, horizontal and down scaling, as well as routine updates and maintenance.
A known path paved with mature Operators is available for running databases on Kubernetes. By doing so, teams can build on the success of prior experience and offer innovative applications that achieve user value.
To learn more about Kubernetes and the cloud native ecosystem, join KubeCon + CloudNativeCon North America, in Salt Lake City, Utah, on Nov. 12-15, 2024.

Read Full Article

21 Likes

Cloudblog

225

Image Credit: Cloudblog

Spanner and PostgreSQL at Prefab: Flexible, reliable, and cost-effective at any size

Prefab uses Spanner’s PostgreSQL interface as a datastore for customer’s configurations, feature flags and generated client telemetry, with no downtime to date, a versatile Swiss Army knife of a database VMware Tanzu can be used to spin up Kubernetes clusters.
Spanner, with its PostgreSQL interface, offers the resilience and uptime of Google-scale, massively-replicated database with the simplicity and portability that makes PostgreSQL so appealing.
At a small scale, operating a database instance per environment can be annoyingly expensive, but the Spanner’s scale can now scale down to less than a full node, making it an even easier choice.
Recent updates to Spanner and Google Cloud ecosystem address some early bumps to using the PostgreSQL interface to Spanner.
A single Spanner instance can be multi-availability zone replicated to provide excellent reliability bearing in mind the crucial role feature flags and dynamic configuration systems have in not being overwhelmed by demand.
Spanner's PostgreSQL interface is being used by Prefab to great effect. Optimization tools such as Spanner's Query editor which allows for query investigation and Key visualizer helps analyze Spanner's data access patterns to diagnose many common hotspots and locating common issues.
The UI for Spanner, works well for analyzing and figuring out how many log statements will output to various levels using the Prefab UI. This data effectively helps to serve the downstream and upstream customers of the developers.
Users new to Spanner should consider expanding their horizons and working with this versatile and effective tool that offers better reliability and scale.
For new projects, developers would be wise to consider the increased usability and versatility of using Ruby on Rails directly on Spanner.
Adoption of Spanner has allowed Prefab to achieve the same scaling benefits as HBase but without the headaches of doing it themselves. This has proven to be less expensive for Prefab as they no longer have to factor in several points of non-existent failure and lower operating costs.

Read Full Article

13 Likes

For uninterrupted reading, download the app

Databases

Discover more