A database is a collection of structured data that is stored in a computer system, and it can be hosted on-premises or in the cloud. As databases are designed to enable easy access to data, our resources are compiled here for smooth browsing of everything you need to know from database management systems to database languages.
Mastering System Design: A Comprehensive Guide to System Scaling for Millions, Part 2
You Can Shape Trend Reports: Participate in DZone Original Research + Enter the Prize Drawings!
ClickHouse is the fastest, most resource-efficient OLAP database which can query billions of rows in milliseconds and is trusted by thousands of companies for real-time analytics. Here are seven tips to help you spin up a production ClickHouse cluster and avoid the most common mistakes. Tip 1: Use Multiple Replicas While testing ClickHouse, it’s natural to deploy a configuration with only one host because you may not want to use additional resources or take on unnecessary expenses. There’s nothing wrong with this in a development or testing environment, but that can come at a cost if you want to use only one host in production. If there’s a failure and you only have one replica and a single host, you’re at risk of losing all your data. For production loads, you should use several hosts and replicate data across them. Not only does it ensure that data remains safe when a host fails, but also allows you to balance the user load on several hosts, which makes resource-intensive queries faster. Tip 2: Don’t Be Shy With RAM ClickHouse is fast, but its speed depends on available resources, especially RAM. You can see great performance when running a ClickHouse cluster with the minimum amount of RAM in a development or testing environment, but that may change when the load increases. In a production environment with a lot of simultaneous read and write operations, a lack of RAM will be more noticeable. If your ClickHouse cluster doesn’t have enough memory, it will be slower, and executing complex queries will take longer. On top of that, when ClickHouse is performing resource-intensive operations, it may compete with the OS itself for RAM, and that eventually leads to OOM, downtime, and data loss. Developers of ClickHouse recommend using at least 16 GB of RAM to ensure that the cluster is stable. You can opt for less memory, but only do so when you know that the load won’t be high. Tip 3: Think Twice When Choosing a Table Engine ClickHouse supports several table engines with different characteristics, but a MergeTree engine will most likely be ideal. Specialized tables are tailored for specific uses, but have limitations that may not be obvious at first glance. Log Family engines may seem ideal for logs, but they don’t support replication and their database size is limited. Table engines in the MergeTree family are the default choice, and they provide the core data capabilities that ClickHouse is known for. Unless you know for sure why you need a different table engine, use an engine from a MergeTree family, and it will cover most of your use cases. Tip 4: Don’t Use More Than Three Columns for the Primary Key Primary keys in ClickHouse don’t serve the same purpose as in traditional databases. They don’t ensure uniqueness, but instead define how data is stored and then retrieved. If you use all columns as the primary key, you may benefit from faster queries. Yet, ClickHouse performance doesn’t only depend on reading data, but on writing it, too. When the primary key contains many columns, the whole cluster slows down when data is written to it. The optimal size of the primary key in ClickHouse is two or three columns, so you can run faster queries but not slow down data inserts. When choosing the columns, think of the requests that will be made and go for columns that will often be selected in filters. Tip 5: Avoid Small Inserts When you insert data in ClickHouse, it first saves a part with this data to a disk. It then sorts this data, merges it, and inserts it into the right place in the database in the background. If you insert small chunks of data very often, ClickHouse will create a part for every small insert. It will slow down the whole cluster and you may get the “Too many parts” error. To insert data efficiently, add data in big chunks and avoid sending more than one insert statement per second. ClickHouse can insert a lot of data at a high pace — even 100K rows per second is okay — but it should be one bulk insert instead of multiple smaller ones. If your data comes in small portions, consider using an external system such as Managed Kafka for making batches of data. ClickHouse is well integrated with Kafka and can efficiently consume data from it. Tip 6: Think of How You Will Get Rid of Duplicate Data Primary keys in ClickHouse don’t ensure that data is unique. Unlike other databases, if you insert duplicate data in ClickHouse, it will be added as is. Thus, the best option would be to ensure that the data is unique before inserting it. You can do it, for example, in a stream processing application, like Apache Kafka. If it’s not possible, there are ways to deal with it when you run queries. One option is to use `argMax` to select only the last version of the duplicate row. You can also use the ReplacingMergeTree engine that removes duplicate entries by design. Finally, you can run `OPTIMIZE TABLE ... FINAL` to merge data parts, but that’s a resource-demanding operation, and you should only run it when you know it won’t affect the cluster performance. Tip 7: Don’t Create an Index for Every Column Just like with primary keys, you may want to use multiple indexes to improve performance. This may be the case when you query data with the filters that match an index, but overall it won’t help you make queries faster. At the same time, you’ll certainly experience the downsides of this strategy. Multiple indexes significantly slow down data inserts because ClickHouse will need to both write the data in the correct place and then update indexes. When you want to create indexes in a production cluster, select the columns that correlate with the primary key.
Detecting and optimizing slow SQL queries is a critical step in enhancing database performance during routine maintenance. Slow SQL queries not only impact user experience but can also become a source of system performance bottlenecks. This article will discuss how to check for running slow SQL queries and provide corresponding optimization strategies. 1. Using SQL Queries To Detect Slow SQL 1.1. Basic SQL Querying By running specific SQL queries, you can quickly identify high-cost, potentially slow-executing SQL statements. This provides a basis for further analysis and optimization. SQL dbaccess sysmaster -<<! select first 10 sqx_estcost, sqx_estrows, sqx_sessionid, sqx_sqlstatement from sysmaster:syssqexplain where 1=1 order by sqx_estcost desc; ! 1.2. Analyzing Query Output Analyze the query results to identify SQL statements with the highest estimated cost and row count. These statements are often the primary targets for optimization efforts. SQL sqx_estcost 2147483647 sqx_estrows 49 sqx_sessionid 51 sqx_sqlstatement select max(t3.tabid) as id from systables t1,systables t2,sys tables t3, systables t4,systables t5,systables t6 group by t 1.tabname,t2.tabname 1.3. Interpreting Output Understand the meaning of each field in the output, such as estimated cost (sqx_estcost), estimated rows (sqx_estrows), session ID (sqx_sessionid), and the SQL statement itself (sqx_sqlstatement). 2. Using Commands To Check Running Slow SQL 2.1. Check Continuously Running Threads (rstcb) Identify threads with a constant third column in the output, indicating ongoing execution. SQL onstat -g act -r 1 | egrep "sqlexec|threads" Output: SQL Running threads: 215 4a645178 470f33e8 1 running 8cpu sqlexec Running threads: 215 4a645178 470f33e8 1 running 8cpu sqlexec 2.2. View Thread Sessions Based on the previous output, inspect thread information. SQL onstat -u |grep 470f33e8 Output: SQL 470f33e8 ---P--- 51 gbasedbt - 0 0 1 5 0 2.3. Check Session Information From the previous step, examine session information and the executing SQL. SQL onstat -g ses 51 Output: SQL On-Line -- Up 14 days 19:53:19 -- 674664 Kbytes session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 51 gbasedbt - - 1486 dbhost1 1 221184 218648 off Program : /opt/gbase/bin/dbaccess tid name rstcb flags curstk status 215 sqlexec 470f33e8 ---P--- 10528 running- Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 51 V 4a745040 217088 1728 453 6 51*O0 V 4a788040 4096 808 1 1 name free used name free used overhead 0 6576 scb 0 144 opentable 0 9192 filetable 0 904 log 0 16536 temprec 0 22688 keys 0 176 ralloc 0 80024 gentcb 0 1616 ostcb 0 2968 sqscb 0 21064 sql 0 18952 hashfiletab 0 552 osenv 0 2768 sqtcb 0 9688 fragman 0 1240 shmblklist 0 22568 rsam_seqscan 0 992 sqscb info scb sqscb optofc pdqpriority optcompind directives 47b61290 4a735028 0 0 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 51 SELECT testdb LC Not Wait 0 0 9.24 Off Current statement name : unlcur Current SQL statement (2) : select max(t3.tabid) as id from systables t1,systables t2,systables t3, systables t4,systables t5,systables t6 group by t1.tabname,t2.tabname Last parsed SQL statement : select max(t3.tabid) as id from systables t1,systables t2,systables t3, systables t4,systables t5,systables t6 group by t1.tabname,t2.tabname By checking and analyzing running slow SQL queries, we can more accurately identify performance bottlenecks and take appropriate optimization measures. Whether using SQL queries or system commands, the key is to understand the output results and develop optimization strategies accordingly.
As a cornerstone of many web applications, MySQL is a robust and reliable database management system. However, achieving optimal performance in MySQL requires a strategic approach to configuration, indexing, and query optimization. In this blog post, we’ll explore key practices to enhance MySQL performance, ensuring your database operates at peak efficiency. 1. Fine-Tuning MySQL Configuration MySQL comes with a default configuration that is typically conservative to ensure it works on a wide range of systems. However, these settings might not be ideal for your specific workload. Here are some configuration parameters to adjust: InnoDB Buffer Pool Size For the InnoDB storage engine, the buffer pool is where data and indexes are cached. Increasing the buffer pool size can significantly improve performance. Ideally, set it to 70-80% of your available memory. MySQL innodb_buffer_pool_size = 12G -- Example for a 16GB RAM system Query Cache Size Query cache can store the text of a SELECT query and the corresponding result set. This can be beneficial for read-heavy workloads. However, it's essential to monitor and adjust according to your workload. MySQL query_cache_size = 256M query_cache_type = 1 Max Connections Increase the maximum number of connections to ensure your application can handle more simultaneous users. MySQL max_connections = 500 2. Effective Indexing Indexes are crucial for speeding up database queries. However, inefficient indexing can lead to slower performance. Here are some best practices: Use Indexes Wisely Index columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses. MySQL CREATE INDEX idx_user_email ON users (email); Avoid over-indexing: While indexes improve read performance, they can degrade write performance. Only index columns are necessary for query optimization. Composite indexes: For queries involving multiple columns, composite indexes can be highly efficient. MySQL CREATE INDEX idx_user_name_email ON users (name, email); 3. Optimizing Queries Writing efficient SQL queries is pivotal for performance. Here are some tips: Avoid SELECT Fetch only the columns you need to reduce the amount of data transferred and processed. MySQL SELECT name, email FROM users WHERE id = 1; Use EXPLAIN The EXPLAIN statement provides insight into how MySQL executes a query, helping identify potential bottlenecks. MySQL EXPLAIN SELECT name, email FROM users WHERE id = 1; Optimize JOINs Ensure that the columns used in JOIN conditions are indexed. MySQL SELECT u.name, o.order_date FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active'; 4. Regular Maintenance Regular maintenance can prevent performance degradation over time: Analyze and Optimize Tables Regularly use the ANALYZE TABLE and OPTIMIZE TABLE commands to update table statistics and reduce fragmentation. MySQL ANALYZE TABLE users; OPTIMIZE TABLE users; Monitor Slow Queries Use the slow query log to identify and optimize queries that take a long time to execute. MySQL SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 2; -- Log queries that take longer than 2 seconds 5. Leveraging Advanced Features MySQL offers advanced features that can further enhance performance: Partitioning Partition large tables to improve query performance and manageability. MySQL CREATE TABLE orders ( order_id INT, order_date DATE, ... ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024) ); Replication Use replication to distribute read load across multiple servers. MySQL -- Configure master and slave servers CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replicant', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 4; START SLAVE; Conclusion Optimizing MySQL performance requires a holistic approach, combining configuration tuning, efficient indexing, query optimization, and regular maintenance. By following these best practices, you can ensure your MySQL database operates efficiently, providing fast and reliable performance for your applications.
The AIDocumentLibraryChat project has been extended to include an image database that can be questioned for images. It uses the LLava model of Ollama, which can analyze images. The image search uses embeddings with the PGVector extension of PostgreSQL. Architecture The AIDocumentLibraryChat project has this architecture: The Angular front-end shows the upload and question features to the user. The Spring AI Backend adjusts the model's image size, uses the database to store the data/vectors, and creates the image descriptions with the LLava model of Ollama. The flow of image upload/analysis/storage looks like this: The image is uploaded with the front-end. The back-end resizes it to a format the LLava model can process. The LLava model then generates a description of the image based on the provided prompt. The resized image and the metadata are stored in a relational Table of PostgreSQL. The image description is then used to create Embeddings. The Embeddings are stored with the description in the PGVector database with metadata to find the corresponding row in the PostgreSQL Table. Then the image description and the resized image are shown in the frontend. The flow of image questions looks like this: The user can input the question in the front-end. The backend converts the question to Embeddings and searches the PGVector database for the nearest entry. The entry has the row ID of the image table with the image and the metadata. The image table data is queried combined with the description and shown to the user. Backend To run the PGVector database and the Ollama framework the files runPostgresql.sh and runOllama.sh contain Docker commands. The backend needs these entries in application-ollama.properties: Properties files # image processing spring.ai.ollama.chat.model=llava:34b-v1.6-q6_K spring.ai.ollama.chat.options.num-thread=8 spring.ai.ollama.chat.options.keep_alive=1s The application needs to be built with Ollama support (property: ‘useOllama’) and started with the ‘ollama’ profile and these properties need to be activated to enable the LLava model and set a useful keep_alive. The num_thread is only needed if Ollama does not select the right amount automatically. The Controller The ImageController contains the endpoints: Java @RestController @RequestMapping("rest/image") public class ImageController { ... @PostMapping("/query") public List<ImageDto> postImageQuery(@RequestParam("query") String query,@RequestParam("type") String type) { var result = this.imageService.queryImage(query); return result; } @PostMapping("/import") public ImageDto postImportImage(@RequestParam("query") String query, @RequestParam("type") String type, @RequestParam("file") MultipartFile imageQuery) { var result = this.imageService.importImage(this.imageMapper.map(imageQuery, query), this.imageMapper.map(imageQuery)); return result; } } The query endpoint contains the ‘postImageQuery(…)’ method that receives a form with the query and the image type and calls the ImageService to handle the request. The import endpoint contains the ‘postImportImage(…)’ method that receives a form with the query(prompt), the image type, and the file. The ImageMapper converts the form to the ImageQueryDto and the Image entity and calls the ImageService to handle the request. The Service The ImageService looks like this: Java @Service @Transactional public class ImageService { ... public ImageDto importImage(ImageQueryDto imageDto, Image image) { var resultData = this.createAIResult(imageDto); image.setImageContent(resultData.imageQueryDto().getImageContent()); var myImage = this.imageRepository.save(image); var aiDocument = new Document(resultData.answer()); aiDocument.getMetadata().put(MetaData.ID, myImage.getId().toString()); aiDocument.getMetadata().put(MetaData.DATATYPE, MetaData.DataType.IMAGE.toString()); this.documentVsRepository.add(List.of(aiDocument)); return new ImageDto(resultData.answer(), Base64.getEncoder().encodeToString(resultData.imageQueryDto() .getImageContent()), resultData.imageQueryDto().getImageType()); } public List<ImageDto> queryImage(String imageQuery) { var aiDocuments = this.documentVsRepository.retrieve(imageQuery, MetaData.DataType.IMAGE, this.resultSize.intValue()) .stream().filter(myDoc -> myDoc.getMetadata() .get(MetaData.DATATYPE).equals(DataType.IMAGE.toString())) .sorted((myDocA, myDocB) -> ((Float) myDocA.getMetadata().get(MetaData.DISTANCE)) .compareTo(((Float) myDocB.getMetadata().get(MetaData.DISTANCE)))) .toList(); var imageMap = this.imageRepository.findAllById( aiDocuments.stream().map(myDoc -> (String) myDoc.getMetadata().get(MetaData.ID)).map(myUuid -> UUID.fromString(myUuid)).toList()) .stream().collect(Collectors.toMap(myDoc -> myDoc.getId(), myDoc -> myDoc)); return imageMap.entrySet().stream().map(myEntry -> createImageContainer(aiDocuments, myEntry)) .sorted((containerA, containerB) -> containerA.distance().compareTo(containerB.distance())) .map(myContainer -> new ImageDto(myContainer.document().getContent(), Base64.getEncoder().encodeToString( myContainer.image().getImageContent()), myContainer.image().getImageType())).limit(this.resultSize) .toList(); } private ImageContainer createImageContainer(List<Document> aiDocuments, Entry<UUID, Image> myEntry) { return new ImageContainer( createIdFilteredStream(aiDocuments, myEntry) .findFirst().orElseThrow(), myEntry.getValue(), createIdFilteredStream(aiDocuments, myEntry).map(myDoc -> (Float) myDoc.getMetadata().get(MetaData.DISTANCE)) .findFirst().orElseThrow()); } private Stream<Document> createIdFilteredStream(List<Document> aiDocuments, Entry<UUID, Image> myEntry) { return aiDocuments.stream().filter(myDoc -> myEntry.getKey().toString() .equals((String) myDoc.getMetadata().get(MetaData.ID))); } private ResultData createAIResult(ImageQueryDto imageDto) { if (ImageType.JPEG.equals(imageDto.getImageType()) || ImageType.PNG.equals(imageDto.getImageType())) { imageDto = this.resizeImage(imageDto); } var prompt = new Prompt(new UserMessage(imageDto.getQuery(), List.of(new Media(MimeType.valueOf(imageDto.getImageType() .getMediaType()), imageDto.getImageContent())))); var response = this.chatClient.call(prompt); var resultData = new ResultData(response.getResult().getOutput().getContent(), imageDto); return resultData; } private ImageQueryDto resizeImage(ImageQueryDto imageDto) { ... } } In the ‘importImage(…)’ method the method ‘createAIResult(…)’ is called. It checks the image type and calls the ‘resizeImage(…)’ method to scale the image to a size that the LLava model supports. Then the Spring AI Prompt is created with the prompt text and the media with the image, media type, and the image byte array. Then the ‘chatClient’ calls the prompt and the response is returned in the ‘ResultData’ record with the description and the resized image. Then the resized image is added to the image entity and the entity is persisted. Now the AI document is created with the embeddings, description, and the image entity ID in the metadata. Then the ImageDto is created with the description, the resized image, and the image type and returned. In the ‘queryImage(…)’ method the Spring AI Documents with the lowest distances are retrieved and filtered for AI documents of image type in the metadata. The Documents are then sorted for the lowest distance. Then the image entities with the metadata IDs of the Spring AI Documents are loaded. That enables the creation of the ImageDtos with the matching documents and image entities. The image is provided as a Base64 encoded string. That enables the MediaType the easy display of the image in an IMG tag. To display a Base64 Png image you can use: ‘<img src=”data:image/png;base64,iVBORw0KG…” />’ Result The UI result looks like this: The application found the large airplane in the vector database using the embeddings. The second image was selected because of a similar sky. The search took only a fraction of a second. Conclusion The support of Spring AI and Ollama enables the use of the free LLava model. That makes the implementation of this image database easy. The LLava model generates good descriptions of the images that can be converted into embeddings for fast searching. Spring AI is missing support for the generate API endpoint, because of that the parameter ‘spring.ai.ollama.chat.options.keep_alive=1s’ is needed to avoid having old data in the context window. The LLava model needs GPU acceleration for productive use. The LLava is only used on import, which means the creation of the descriptions could be done asynchronously. The LLava model on a medium-powered Laptop runs on a CPU, for 5-10 minutes per image. Such a solution for image searching is a leap forward compared to previous implementations. With more GPUs or CPU support for AI such Image search solutions will become much more popular.
As the volume of data increases exponentially and queries become more complex, relationships become a critical component for data analysis. In turn, specialized solutions such as graph databases that explicitly optimize for relationships are needed. Other databases aren’t designed to be able to search and query data based on the intricate relationships found in complex data structures. Graph databases are optimized to handle connected data by modeling the information into a graph, which maps data through nodes and relationships. With this article, readers will traverse a beginner’s guide to graph databases, their terminologies, and comparisons with relational databases. They will also explore graph databases from cloud providers like AWS Neptune to open-source solutions. Additionally, this article can help develop a better understanding of how graph databases are useful for applications such as social network analysis, fraud detection, and many other areas. Readers will also learn how graph databases are used for applications like knowledge graph databases and social media analytics. What Is a Graph Database? A graph database is a purpose-built NoSQL database specializing in data structured in complex network relationships, where entities and their relationships have interconnections. Data is modeled using graph structures, and the essential elements of this structure are nodes, which represent entities, and edges, which represent the relationships between entities. The nodes and edges of a graph can all have attributes. Critical Components of Graph Databases Nodes These are the primary data elements representing entities such as people, businesses, accounts, or any other item you might find in a database. Each node can store a set of key-value pairs as properties. Edges Edges are the lines that connect nodes, defining their relationships. In addition to nodes, edges can also have properties – such as weight, type, or strength – that clarify their relationship. Properties Nodes and edges can each have properties that can be used to store metadata about those objects. These can include names, dates, or any other relevant descriptive attributes to a node or edge. How Graph Databases Store and Process Data In a graph database, nodes and relationships are considered first-class citizens — in contrast to relational databases, nodes are stored in tabular forms, and relationships are computed at query time. This lets graph databases treat the data relationships as having as much value as the data, which enables faster traversal of connected data. With their traversal algorithms, graph databases can explore the relationships between nodes and edges to answer complicated queries like the shortest path, fraud detection, or network analysis. Various graph-specific query languages – Neo4j’s Cypher and Tinkerpop’s Gremlin – enable these operations by focusing on pattern matching and deep-link analytics. Practical Applications and Benefits Graph databases shine in any application where the relationships between the data points are essential, such as web and social networks, recommendation engines, and a whole host of other apps where it’s necessary to know how deep and wide the relationships go. In areas such as fraud detection and network security, it’s essential to adjust and adapt dynamically; this is something graph databases do very well. In conclusion, graph databases offer a solid infrastructure for working with complex, highly connected data. They offer many advantages over relational databases regarding modeling relationships and the interactions between the data. Key Components and Terminology Nodes and Their Properties Nodes are the basic building blocks of a graph database. They typically represent some object or a specific instance, be it a person, place, or thing. For each node, we have a vertex in the graph structure. The node can also contain several properties (also called "labels" in the database context). Each of these properties is a key-value pair, where the value expands or further clarifies the object, and its content depends on the application of the graph database. Edges: Defining Relationships Edges, on the other hand, are the links that tie the nodes together. They are directional, so they can have a start node and an end node (thus defining the flow between one node and another). These edges also define the nature of the relationship—whether it is internalizational or social. Labels: Organizing Nodes The labels help group nodes that might have similarities (Person nodes, Company nodes, etc.) so that graph databases can retrieve sets of nodes more quickly. For example, in a social network analysis, Person and Company nodes might be grouped using labels. Relationships and Their Characteristics Relationships connect nodes, but they also have properties, such as strength, status, or duration, that can define how the relationship might differ between nodes. Graph Query Languages: Cypher and Gremlin Graph databases require unique particular languages to use their often complicated structure, and these languages differ from graph databases. Cypher, used with Neo4j, is a reasonably relatively pattern-based language. Gremlin, used with other graph databases, is more procedural and can traverse more complex graph structures. Both languages are expressive and powerful, capable of queries that would be veritable nightmares written in the languages used with traditional databases. Tools for Managing and Exploring Graph Data Neo4j offers a suite of tools designed to enhance the usability of graph databases: Neo4j Bloom: Explore graph data visually without using a graph query language. Neo4j Browser: A web-based application for executing Cypher queries and visualizing the results. Neo4j Data Importer and Neo4j Desktop: These tools for importing data into a Neo4j database and handling Neo4j database instances, respectively. Neo4j Ops Manager: Useful for managing multiple Neo4j instances to ensure that large-scale deployments can be managed and optimized. Neo4j Graph Data Science: This library is an extension of Neo4j that augments its capabilities, which are more commonly associated with data science. It enables sophisticated analytical tasks to be performed directly on graph data. Equipped with these fundamental components and tools, users can wield the power of graph databases to handle complex data and make knowledgeable decisions based on networked knowledge systems. Comparing Graph Databases With Other Databases While graph and relational databases are designed to store and help us make sense of data, they fundamentally differ in how they accomplish this. Graph databases are built on the foundation of nodes and edges, making them uniquely fitted for dealing with complex relationships between data points. That foundation’s core is structure, representing connected entities through nodes and their relationships through edges. Relational databases arrange data in ‘rows and columns’ – tables, whereas graph databases are ‘nodes and edges.’ This difference in structure makes such a direct comparison between the two kinds of databases compelling. Graph databases organize data in this way naturally, whereas it’s not as easy to represent relationships between certain types of data points in relational databases. After all, they were invented to deal with transactions (i.e., a series of swaps of ‘rows and columns’ between two sides, such as a payment or refund between a seller and a customer). Data Models and Scalability Graph databases store data in a graph with nodes, edges, and properties. They are instrumental in domains with complex relationships, such as social networks or recommendation engines. As an example of the opposite end of the spectrum, relational databases contain data in tables, which is well-suited for applications requiring high levels of data integrity (i.e., applications such as those involved in financial systems or managing customer relationships). Another benefit, for example, is their horizontal scalability: graph databases grow proportionally to their demands by adding more machines to a network instead of the vertical scalability (adding more oomph to an existing machine) typical for a relational database. Query Performance and Flexibility One reason is that graph databases are generally much faster at executing complex queries with deep relationships because they can traverse nodes and edges—unlike relational databases, which might have to perform lots of joins that could speed up or slow down depending on the size of the data set. In addition, graph databases excel in the ease with which the data model can be changed without severe consequences. As business requirements evolve and users learn more about how their data should interact, a graph database can be more readily adapted without costly redesigns. Though better suited for providing strong transactional guarantees or ACID compliance, relational databases are less adept at model adjustments. Use of Query Languages The different languages of query also reflect the distinct nature of these databases. Whereas graph databases tend to use a language tailored to the way a graph is traversed—such as Gremlin or Cypher—relational databases have long been managed and queried through SQL, a well-established language for structured data. Suitability for Different Data Types Relational databases are well suited for handling large datasets with a regular and relatively simple structure. In contrast, graph databases shine in environments where the structures are highly interconnected, and the relationships are as meaningful as the data. In conclusion, while graph and relational databases have pros and cons, which one to use depends on the application’s requirements. Graph databases are better for analyzing intricate and evolving relationships, which makes them ideal for modern applications that demand a detailed understanding of networked data. Advantages of Graph Databases Graph databases are renowned for their efficiency and flexibility, mainly when dealing with complex, interconnected data sets. Here are some of the key advantages they offer: High Performance and Real-Time Data Handling Performance is a huge advantage for graph databases. It comes from the ease, speed, and efficiency with which it can query linked data. Graph databases often beat relational databases at handling complex, connected data. They are well suited to continual, real-time updates and queries, unlike, e.g., Hadoop HDFS. Enhanced Data Integrity and Contextual Awareness Keeping these connections intact across channels and data formats, graph databases maintain rich data relationships and allow that data to be easily linked. This structure surfaces nuance in interactions humans could not otherwise discern, saving time and making the data more consumable. It gives users relevant insights to understand the data better and helps businesses make more informed decisions. Scalability and Flexibility Graph databases have been designed to scale well. They can accommodate the incessant expansion of the underlying data and the constant evolution of the data schema without downtime. They can also scale well in terms of the number of data sources they can link, and again, this linking can temporarily accommodate a continuous evolution of the schema without interrupting service. They are, therefore, particularly well-suited to environments in which rapid adaptation is essential. Advanced Query Capabilities These graphs-based systems can quickly run powerful recursive path queries to retrieve direct (‘one hop’) and indirect (‘two hops’ and ‘twenty hops’) connections, making running complex subgraph pattern-matching queries easy. Moreover, complex group-by-aggregate queries (such as Netflix’s tag aggregation) are also natively supported, allowing arbitrary degree flexibility in aggregating selective dimensions, such as in big-data setups with multiple dimensions, such as time series, demographics, or geographics. AI and Machine Learning Readiness The fact that graph databases naturally represent entities and inter-relations as a structured set of connections makes them especially well-suited for AI and machine-learning foundational infrastructures since they support fast real-time changes and rely on expressive, ergonomic declarative query languages that make deep-link traversal and scalability a simple matter – two features that are critical in the case of next-generation data analytics and inference. These advantages make graph databases a good fit for an organization that needs to manage and efficiently draw meaningful insights from dataset relationships. Everyday Use Cases for Graph Databases Graph databases are being used by more industries because they are particularly well-suited for handling complex connections between data and keeping the whole system fast. Let’s look at some of the most common uses for graph databases. Financial and Insurance Services The financial and insurance services sector increasingly uses graph databases to detect fraud and other risks; how these systems model business events and customer data as a graph allows them to detect fraud and suspicious links between various entities, and the technique of Entity Link Analysis takes this a step further, allowing the detection of potential fraud in the interactions between different kinds of entities. Infrastructure and Network Management Graph databases are well-suited for infrastructure mapping and keeping network inventories up to date. Serving up an interactive map of the network estate and performing network tracing algorithms to walk across the graph is straightforward. Likewise, it makes writing new algorithms to identify problematic dependencies, vulnerable bottlenecks, or higher-order latency issues much easier. Recommendation Systems Many companies – including major e-commerce giants like Amazon – use graph databases to power recommendation engines. These keep track of which products and services you’ve purchased and browsed in the past to suggest things you might like, improving the customer experience and engagement. Social Networking Platforms Social networks such as Facebook, Twitter, and LinkedIn all use graph databases to manage and query huge amounts of relational data concerning people, their relationships, and interactions. This makes them very good at quickly navigating across vast social networks, finding influential users, detecting communities, and identifying key players. Knowledge Graphs in Healthcare Healthcare organizations assemble critical knowledge about patient profiles, past ailments, and treatments in knowledge graphs, while graph queries implemented on graph databases identify patient patterns and trends. These can influence how treatments proceed positively and how patients fare. Complex Network Monitoring Graph databases are used to model and monitor complex network infrastructures, including telecommunications networks or end-to-end environments of clouds (data-center infrastructure including physical networking, storage, and virtualization). This application is undoubtedly crucial for the robustness and scalability of those systems and environments that form the essential backbone of the modern information infrastructure. Compliance and Governance Organizations also use graph databases to manage data related to compliance and governance, such as access controls, data retention policies, and audit trails, to ensure they can continue to meet high standards of data security and regulatory compliance. AI and Machine Learning Graph databases are also essential for developing artificial intelligence and machine learning applications. They allow developers to create standardized means of storing and querying data for applications such as natural language processing, computer vision, and advanced recommendation systems, which is essential for making AI applications more intelligent and responsive. Unraveling Financial Crimes Graphs provide a way to trace the structure of shell corporate entities that criminals use to launder money, studying whether the patterns of supplies to shell companies and cash flows from shell companies to other entities are suspicious. Such applications are helpful for law enforcement and regulatory agencies to unravel complex money laundering networks and fight against financial crime. Automotive Industry In the automotive industry, graph queries help analyze the relationships between tens of thousands of car parts, enabling real-time interactive analysis that has the potential to improve manufacturing and maintenance processes. Criminal Network Analysis In law enforcement, graph databases are used to identify criminal networks, address patterns, and identify critical links in criminal organizations to bring operations down efficiently from all sides. Data Lineage Tracking Graph technology can also track data lineage (the details of where an item of data, such as a fact or number, was created, how it was copied, and where it was used). This is important for auditing and verifying that data assets are not corrupted. This diverse array of applications underscores the versatility of graph databases and their utility in representing and managing complex, interconnected data across multiple diverse fields. Challenges and Considerations Graph databases are built around modeling structures in a specific domain, in a process resembling both knowledge or ontology engineering, and a practical challenge that can require specialized "graph data engineers." All these requirements point to important scalability issues and potentially limit the appeal of this technology to many beyond the opponents of a data web. Inconsistency of data across the system remains a critical issue since developing homogeneous systems that can maintain data consistency while maintaining flexibility and expressivity is challenging. While graph queries don’t require as much coding as SQL, paths for traversal across the data still have to be spelled out explicitly. This increases the effort needed to write queries and prevents graph queries from being as easily abstracted and reused as SQL code, impairing their generalization. Furthermore, because there isn’t a unified standard for capabilities or query languages, developers invent their own – a further step in API fragmentation. Another significant issue is knowing which machine is the best place to put that data, given all the subtle relationships between nodes, deciding that is crucial to performance but hard to do on the fly. As necessary, many existing graph database systems weren’t architected for today’s high volumes of data, so they can end up being performance bottlenecks. From a project management standpoint, failure to accurately capture and map business requirements to technical requirements often results in confusion and delay. Poor data quality, inadequate access to data sources, verbose data modeling, or time-consuming data modeling will magnify the pain of a graph data project. On the end-user side, asking people to learn new languages or skills in order to read some graphs could deter adoption, while the difficulty of sharing those graphs or collaborating on the analysis will eventually lower the range and impact of the insights. The Windows 95 interface had an excellent early advantage in the virtues of simplicity: we can tell the same story about graph technologies nowadays. Adopting this technology is also hindered when the analysis process is criticized as too time-consuming. From a technical perspective, managing large graphs by storing and querying complex structures presents more significant challenges. For example, the data must be distributed on a cluster of multiple machines, adding another level of complexity for developers. Data is typically sharded (split) into smaller parts and stored on various machines, coordinated by an "intelligent" virtual server managing access control and query across multiple shards. Choosing the Right Graph Database When selecting a graph database, it’s crucial to consider the queries’ complexity and the data’s interconnectedness. A well-chosen graph database can significantly enhance the performance and scalability of data-driven applications. Key Factors to Consider Native graph storage and processing: Opt for databases designed from the ground up to handle graph data structures. Property graphs and Graph Query Languages: Ensure the database supports robust graph query languages and can handle property graphs efficiently. Data ingestion and integration capabilities: The ability to seamlessly integrate and ingest data from various sources is vital for dynamic data environments. Development tools and graph visualization: Tools that facilitate development and allow intuitive graph visualizations to improve usability and insights. Graph data science and analytics: Databases with advanced analytics and data science capabilities can provide deeper insights. Support for OLTP, OLAP, and HTAP: Depending on the application, support for transactional (OLTP), analytical (OLAP), and hybrid (HTAP) processing may be necessary. ACID compliance and system durability: Essential for ensuring data integrity and reliability in transaction-heavy environments Scalability and performance: The database should scale vertically and horizontally to handle growing data loads. Enterprise security and privacy features: Robust security features are crucial to protect sensitive data and ensure privacy. Deployment flexibility: The database should match the organization’s deployment strategy, whether on-premises or cloud. Open-source foundation and community support: A strong community and open-source foundation can provide extensive support and flexibility. Business and technology partnerships: Partnerships can offer additional support and integration options, enhancing the database’s capabilities. Comparing Popular Graph Databases Dgraph: This is the most performant and scalable option for enterprise systems that need to handle massive amounts of fast-flowing data. Memgraph: An open-source, in-memory storage database with a query language specially designed for real-time data and analytics Neo4j: Offers a comprehensive graph data science library and is well-suited for static data storage and Java-oriented developers Each of these databases has its advantages: Memgraph is the strongest contender in the Python ecosystem (you can choose Python, C++, or Rust for your custom stored procedures), and Neo4j’s managed solution offers the most control over your deployment into the cloud (its AuraDB service provides a lot of power and flexibility). Community and Free Resources Memgraph has a free community edition and a paid enterprise edition, and Neo4j has a community "Labs" edition, a free enterprise trial, and hosting services. These are all great ways for developers to get their feet wet without investing upfront. In conclusion, choosing the proper graph database to use is contingent upon understanding the realities of your project well enough and the potential of the database to which you are selecting. If you bear this notion in mind, your organization will be using graph databases to their full potential to enhance its data infrastructure and insights. Conclusion Having navigated through the expansive realm of graph databases, the hope is that you now know not only the basics of these beautiful databases, from nodes to edges, from vertex storage to indexing, but also those of their applications across industries, including finance, government, and healthcare. This master guide comprehensively introduces graph databases, catering to sophomores and seniors in the database field. Now, every reader of this broad stratum is fully prepared to take the following steps in understanding how graph databases work, how they compare against traditional and non-relational databases, and where they are utilized in the real world. We have seen that choosing a graph database requires careful consideration of the project’s requirements and features. The reflections and difficulties highlighted the importance of correct implementation and the advantage of the graph database in changing our way of processing and looking at data. The graph databases’ complexity and power allow us to provide new insights and be more efficient in computation. In this way, new data management and analysis methods may be developed. References Graph Databases for Beginners How to choose a graph database: we compare 6 favorites What is A Graph Database? A Beginner's Guide Video: What is a graph database? (in 10 minutes) AWS: What Is a Graph Database? Neo4j: What is a graph database? Wikipedia: Graph database Geeks for Geeks: What is Graph Database – Introduction Memgraph: What is a Graph Database? Geeks for Geeks: Introduction to Graph Database on NoSQL Graph Databases: A Comprehensive Overview and Guide. Part1 Graph database concepts AWS: What’s the Difference Between a Graph Database and a Relational Database? Comparison of Relational Databases and Graph Databases Nebula Graph: Graph Database vs Relational Database: What to Choose? Graph database vs. relational database: Key differences The ultimate guide to graph databases Neo4j: Why Graph Databases? What is a Graph Database and What are the Benefits of Graph Databases What Are the Major Advantages of Using a Graph Database? Graph Databases for Beginners: Why Graph Technology Is the Future Understanding Graph Databases: Unleashing the Power of Connected Data in Data Science Use cases for graph databases 7 Graph Database Use Cases That Will Change Your Mind When Connected Data Matters Most 17 Use Cases for Graph Databases and Graph Analytics The Challenges of Working with a Graph Database Where the Path Leads: State of the Art and Challenges of Graph Database Systems 5 Reasons Graph Data Projects Fail 16 Things to Consider When Selecting the Right Graph Database How to Select a Graph Database: Best Practices at RoyalFlush Neo4j vs Memgraph - How to Choose a Graph Database?
Synopsis Many databases contain bitmaps stored as blobs or files: photos, document scans, medical images, etc. When these bitmaps are retrieved by various database clients and applications, it is sometimes desirable to uniquely watermark them as they are being retrieved, so that they can be identified later. In some cases, you may even want to make this watermark invisible. This kind of dynamic bitmap manipulation can easily be done by a programmable database proxy, without changing the persisted bitmaps. This approach has the following benefits: The watermark can be customized for each retrieval and can contain information about the date, time, user identity, IP address, etc. The image processing is done by the proxy, which puts no extra load on the database. This requires no changes to the database or to the database clients. The End Result Given a bitmap stored in a database, such as: a programmable database proxy can modify the bitmap on its way to the client to include a watermark containing any desired information, such as: How This Works The architecture is simple: instead of the normal connection between database clients and servers: the clients connect to the proxy, and the proxy connects to the server: The proxy can then manipulate the bitmaps as needed when they are retrieved. For instance, it can watermark only some bitmaps, or it can use different styles of watermarks, depending on the circumstances. The bitmaps stored in the database are completely unaffected: they are modified on the fly as they are forwarded to the clients. Advantages The clients and the database are blissfully unaware - this is completely transparent to them. Each image can be watermarked uniquely when it is retrieved (e.g., date/time, user name, IP address of client, etc.). No additional load is put on the database server(s). Disadvantages The system is more complex with the addition of the proxy. There will be (typically modest) an increase in latency, depending mostly on the size of the images, but this should be compared to the alternatives. Example Using a proxy, we can create a simple filter to add a watermark to certain bitmaps. If we assume that our database contains a table called images, with a column called bitmap of type blob or varbinary (depending on your database), we can create a result set filter in the proxy with the following parameter: Query pattern: regex:select.*from.*images.* and a bit of JavaScript code (which also uses the underlying Java engine): JavaScript // Get the value of the bitmap column as a byte stream let stream = context.packet.getJavaStream("bitmap"); if (stream === null) { return; } // The text to use as watermark const now = new Date(); const watermark = "Retrieved by " + context.connectionContext.userName + " on " + now.getFullYear() + "/" + (now.getMonth()+1) + "/" + now.getDate(); // Read the bitmap const ImageIO = Java.type("javax.imageio.ImageIO"); let img = ImageIO.read(stream); // Create the Graphics to draw the text let g = img.createGraphics(); const Color = Java.type("java.awt.Color"); g.setColor(new Color(255, 255, 0, 150)); const Font = Java.type("java.awt.Font"); g.setFont(new Font("sans-serif", Font.BOLD, 16)); // Draw the text at the bottom of the bitmap let textRect = textFont.getStringBounds(watermark, g.getFontRenderContext()); g.drawString(watermark, (img.getWidth() / 2) - (textRect.getWidth() / 2), img.getHeight() - (textRect.getHeight() / 2)); // Write the bitmap to the column value const ByteArrayOutputStream = Java.type("java.io.ByteArrayOutputStream"); let outStream = new ByteArrayOutputStream(); ImageIO.write(img, "png", outStream); context.packet.bitmap = outStream.toByteArray(); With this filter in place, bitmaps retrieved from this table will include a watermark containing the name of the database user, and a timestamp. The database is never affected: the bitmaps stored in the database are completely unchanged. They are modified on the fly as they are delivered to the client. Obviously, we can watermark bitmaps selectively, we can change the text of the watermark depending on any relevant factors, and we can play with fonts, colors, positioning, transparency, etc. See this example for details. Secret Watermarks In some cases, it might be desirable to mark the bitmaps in a way that is not visible to the naked eye. One trivial way to do this would be to edit the image's metadata, but if we need something more subtle, we can use steganography to distribute a secret message among the bitmap in a way that makes it difficult to detect. The example above can be modified to use the Adumbra library: // Get the value of the bitmap column as a byte stream let inStream = context.packet.getJavaStream("bitmap"); if (inStream === null) { return; } // The hidden message const now = new Date(); const message = "Retrieved by " + context.connectionContext.userName + " on " + now.getFullYear() + "/" + (now.getMonth()+1) + "/" + now.getDate(); const messageBytes = context.utils.getUTF8BytesForString(message); const keyBytes = context.utils.getUTF8BytesForString("This is my secret key"); // Hide the message in the bitmap const Encoder = Java.type("com.galliumdata.adumbra.Encoder"); const ByteArrayOutputStream = Java.type("java.io.ByteArrayOutputStream"); let outStream = new ByteArrayOutputStream(); let encoder = new Encoder(1); encoder.encode(inStream, outStream, "png", messageBytes, keyBytes); context.packet.bitmap = outStream.toByteArray(); With this in place, the modified bitmaps served to the clients will contain a secret watermark that will be difficult to detect, and almost impossible to extract without the secret key. What Else Can You Do With This? This watermarking technique can also be applied to documents other than bitmaps: Documents such as PDF and MS Word can be given some extra metadata on the fly, or they can be given a visible or invisible watermark - see this example for PDF documents. All text documents can be subtly marked using techniques such as altering spacing, spelling, layout, fonts and colors, zero-width characters, etc. All digital documents that can sustain minor changes without losing any significant meaning, such as bitmaps, audio files, and sample sets, can be altered in a similar way. In fact, entire data sets can be watermarked by subtly modifying some non-critical aspects of the data, making it possible to identify these datasets later on and know exactly their origin. This is beyond the scope of this article, but there are many ways to make data traceable back to its origin. Conclusion When you need to have a custom watermark for every retrieval of some bitmaps or documents from a database, the technique shown here is a solid approach that avoids any additional load on the database and requires no changes to the clients or servers.
In today's rapidly evolving technological landscape, it is crucial for any business or application to efficiently manage and utilize data. NoSQL databases have emerged as an alternative to traditional relational databases, offering flexibility, scalability, and performance advantages. These benefits become even more pronounced when combined with Java, a robust and widely-used programming language. This article explores three key benefits of understanding and learning NoSQL databases with Java, highlighting the polyglot philosophy and its efficiency in software architecture. Enhanced Flexibility and Scalability One significant benefit of NoSQL databases is their capability to handle various data models, such as key-value pairs, documents, wide-column stores, and graph databases. This flexibility enables developers to select the most suitable data model for their use case. When combined with Java, a language renowned for its portability and platform independence, the adaptability of NoSQL databases can be fully utilized. Improved Performance and Efficiency Performance is a crucial aspect of database management, and NoSQL databases excel in this area because of their distributed nature and optimized storage mechanisms. When developers combine these performance-enhancing features with Java, they can create applications that are not only efficient but also high-performing. Embracing the Polyglot Philosophy The polyglot philosophy in software development encourages using multiple languages, frameworks, and databases within a single application to take advantage of each one's strengths. Understanding and learning NoSQL databases with Java perfectly embodies this approach, offering several benefits for modern software architecture. Leveraging Eclipse JNoSQL for Success With NoSQL Databases and Java To fully utilize NoSQL databases with Java, developers can use Eclipse JNoSQL, a framework created to streamline the integration and management of NoSQL databases in Java applications. Eclipse JNoSQL supports over 30 databases and is aligned with Jakarta NoSQL and Jakarta Data specifications, providing a comprehensive solution for modern data handling needs. Eclipse JNoSQL: Bridging Java and NoSQL Databases Eclipse JNoSQL is a framework that simplifies the interaction between Java applications and NoSQL databases. With support for over 30 different NoSQL databases, Eclipse JNoSQL enables developers to work efficiently across various data stores without compromising flexibility or performance. Key features of Eclipse JNoSQL include: Support for Jakarta Data Query Language: This feature enhances the power and flexibility of querying across databases. Cursor pagination: Processes large datasets efficiently by utilizing cursor-based pagination rather than traditional offset-based pagination NoSQLRepository: Simplifies the creation and management of repository interfaces New column and document templates: Simplify data management with predefined templates Jakarta NoSQL and Jakarta Data Specifications Eclipse JNoSQL is designed to support Jakarta NoSQL and Jakarta Data specifications, standardizing and simplifying database interactions in Java applications. Jakarta NoSQL: This comprehensive framework offers a unified API and a set of powerful annotations, making it easier to work with various NoSQL data stores while maintaining flexibility and productivity. Jakarta Data: This specification provides an API for easier data access across different database types, enabling developers to create custom query methods on repository interfaces. Introducing Eclipse JNoSQL 1.1.1 The latest release, Eclipse JNoSQL 1.1.1, includes significant enhancements and new features, making it a valuable tool for Java developers working with NoSQL databases. Key updates include: Support to cursor pagination Support to Jakarta Data Query Fixes several bugs and enhances performance For more details, visit the Eclipse JNoSQL Release 1.1.1 notes. Practical Example: Java SE Application With Oracle NoSQL To illustrate the practical use of Eclipse JNoSQL, let's consider a Java SE application using Oracle NoSQL. This example showcases the effectiveness of cursor pagination and JDQL for querying. The first pagination method we will discuss is Cursor pagination, which offers a more efficient way to handle large datasets than traditional offset-based pagination. Below is a code snippet demonstrating cursor pagination with Oracle NoSQL. Java @Repository public interface BeerRepository extends OracleNoSQLRepository<Beer, String> { @Find @OrderBy("hop") CursoredPage<Beer> style(@By("style") String style, PageRequest pageRequest); @Query("From Beer where style = ?1") List<Beer> jpql(String style); } public class App4 { public static void main(String[] args) { var faker = new Faker(); try (SeContainer container = SeContainerInitializer.newInstance().initialize()) { BeerRepository repository = container.select(BeerRepository.class).get(); for (int index = 0; index < 100; index++) { Beer beer = Beer.of(faker); // repository.save(beer); } PageRequest pageRequest = PageRequest.ofSize(3); var page1 = repository.style("Stout", pageRequest); System.out.println("Page 1"); page1.forEach(System.out::println); PageRequest pageRequest2 = page1.nextPageRequest(); var page2 = repository.style("Stout", pageRequest2); System.out.println("Page 2"); page2.forEach(System.out::println); System.out.println("JDQL query: "); repository.jpql("Stout").forEach(System.out::println); } System.exit(0); } } In this example, BeerRepository efficiently retrieves and paginates data using cursor pagination. The style method employs cursor pagination, while the jpql method demonstrates a JDQL query. API Changes and Compatibility Breaks in Eclipse JNoSQL 1.1.1 The release of Eclipse JNoSQL 1.1.1 includes significant updates and enhancements aimed at improving functionality and aligning with the latest specifications. However, it's important to note that these changes may cause compatibility issues for developers, which need to be understood and addressed in their projects. 1. Annotations Moved to Jakarta NoSQL Specification Annotations like Embeddable and Inheritance were previously included in the Eclipse JNoSQL framework. In the latest version, however, they have been relocated to the Jakarta NoSQL specification to establish a more consistent approach across various NoSQL databases. As a result, developers will need to update their imports and references to these annotations. Java // Old import import org.jnosql.mapping.Embeddable; // New import import jakarta.nosql.Embeddable; The updated annotations can be accessed at the Jakarta NoSQL GitHub repository. 2. Unified Query Packages To simplify and unify the query APIs, SelectQuery and DeleteQuery have been consolidated into a single package. Consequently, specific query classes like DocumentQuery, DocumentDeleteQuery, ColumnQuery, and ColumnDeleteQuery have been removed. Impact: Any code using these removed classes will no longer compile and must be refactored to use the new unified classes. Solution: Refactor your code to use the new query classes in the org.eclipse.jnosql.communication.semistructured package. For example: Java // Old usage DocumentQuery query = DocumentQuery.select().from("collection").where("field").eq("value").build(); // New usage SelectQuery query = SelectQuery.select().from("collection").where("field").eq("value").build(); Similar adjustments will be needed for delete queries. 3. Migration of Templates Templates such as ColumnTemplate, KeyValueTemplate, and DocumentTemplate have been moved from the Jakarta Specification to Eclipse JNoSQL. Java // Old import import jakarta.nosql.document.DocumentTemplate; // New import import org.eclipse.jnosql.mapping.document.DocumentTemplate; 4. Default Query Language: Jakarta Data Query Language (JDQL) Another significant update in Eclipse JNoSQL 1.1.1 is the adoption of Jakarta Data Query Language (JDQL) as the default query language. JDQL provides a standardized way to define queries using annotations, making it simpler and more intuitive for developers. Conclusion The use of a NoSQL database is a powerful asset in modern applications. It allows software architects to employ polyglot persistence, utilizing the best persistence capability in each scenario. Eclipse JNoSQL assists Java developers in implementing these NoSQL capabilities into their applications.
In previous articles, we’ve discussed the basics of stream processing and how to choose a stream processing system. In this article, we’ll describe what a streaming database is, as it is the core component of a stream processing system. We'll also provide some commercially available solutions to make an informative choice if you need to choose one. Table of Contents Fundamentals of streaming databases Challenges in implementing streaming databases The architecture of streaming databases Examples of streaming databases Fundamentals of Streaming Databases Given the nature of stream processing that aims to manage data as a stream, engineers can’t rely on traditional databases to store data, and this is why we’re talking about streaming databases in this article. We can define a streaming database as a real-time data repository for storing, processing, and enhancing streams of data. So, the fundamental characteristic of streaming databases is their ability to manage data in motion, where events are captured and processed as they occur. Streaming databases (Image by Federico Trotta) So, unlike traditional databases that store static datasets and require periodic updates to process them, streaming databases embrace an event-driven model, reacting to data as it is generated. This allows organizations to extract actionable insights from live data, enabling timely decision-making and responsiveness to dynamic trends. One of the key distinctions between streaming and traditional databases lies in their treatment of time. In streaming databases, time is a critical dimension because data are not just static records but are associated with temporal attributes. Latency in streaming databases (Image by Federico Trotta) In particular, we can define the following: Event time: This refers to the time when an event or data point actually occurred in the real world. For example, if you are processing a stream of sensor data from devices measuring temperature, the event time would be the actual time when each temperature measurement was recorded by the sensors. Processing time: This refers to the time at which the event is processed within the stream processing system. For example, if there is a delay or latency in processing the temperature measurements after they are received, the processing time would be later than the event time. This temporal awareness facilitates the creation of time-based aggregations, allowing businesses to gain the right understanding of trends and patterns over time intervals, and handling out-of-order events. In fact, events may not always arrive at the processing system in the order in which they occurred in the real world for several reasons, like network delays, varying processing speeds, or other factors. So, by incorporating event time into the analysis, streaming databases can reorder events based on their actual occurrence in the real world. This means that the timestamps associated with each event can be used to align events in the correct temporal sequence, even if they arrive out of order. This ensures that analytical computations and aggregations reflect the temporal reality of the events, providing accurate insights into trends and patterns. Challenges in Implementing Streaming Databases While streaming databases offer a revolutionary approach to real-time data processing, their implementation can be challenging. Among the others, we can list the following challenges: Sheer Volume and Velocity of Streaming Data Real-time data streams, especially high-frequency ones common in applications like IoT and financial markets, generate a high volume of new data at a high velocity. So, streaming databases need to handle a continuous data stream efficiently, without sacrificing performance. Ensuring Data Consistency in Real-Time In traditional batch processing, consistency is achieved through periodic updates. In streaming databases, ensuring consistency across distributed systems in real time introduces complexities. Techniques such as event time processing, watermarking, and idempotent operations are employed to address these challenges but require careful implementation. Security and Privacy Concerns Streaming data often contains sensitive information, and processing it in real-time demands robust security measures. Encryption, authentication, and authorization mechanisms must be integrated into the streaming database architecture to safeguard data from unauthorized access and potential breaches. Moreover, compliance with data protection regulations adds an additional layer of complexity. Tooling and Integrations The diverse nature of streaming data sources and the variety of tools available demand thoughtful integration strategies. Compatibility with existing systems, ease of integration, and the ability to support different data formats and protocols become critical considerations. Need for Skilled Personnel As streaming databases are inherently tied to real-time analytics, the need for skilled personnel to develop, manage, and optimize these systems has to be taken into consideration. The scarcity of expertise in the field can delay the diffuse adoption of streaming databases, and organizations must invest in training and development to bridge this gap. Architecture of Streaming Databases The architecture of streaming databases is crafted to handle the intricacies of processing real-time data streams efficiently. At its core, this architecture embodies the principles of distributed computing, enabling scalability and responsiveness to the dynamic nature of streaming data. A fundamental aspect of streaming databases’ architecture is the ability to accommodate continuous, high-velocity data streams. This is achieved through a combination of data ingestion, processing, and storage components. Data ingestion in streaming databases (Image by Federico Trotta) The data ingestion layer is responsible for collecting and accepting data from various sources in real time. This may involve connectors to external systems, message queues, or direct API integrations. Once ingested, data is processed in the streaming layer, where it is analyzed, transformed, and enriched in near real-time. This layer often employs stream processing engines or frameworks that enable the execution of complex computations on the streaming data, allowing for the derivation of meaningful insights. Events in streaming databases (Image by Federico Trotta) Since they deal with real-time data, a hallmark of streaming database architecture is the event-driven paradigm. In fact, each data point is treated as an event, and the system reacts to these events in real-time. This temporal awareness is fundamental for time-based aggregations, and handling out-of-sequence events, facilitating a granular understanding of the temporal dynamics of the data. The schema design in streaming databases is also dynamic and flexible, allowing for the evolution of data structures over time. Unlike traditional databases with rigid schemas, in fact, streaming databases accommodate the fluid nature of streaming data, where the schema may change as new fields or attributes are introduced: this flexibility allows for the possibility of handling diverse data formats and adapting to the evolving requirements of streaming applications. Example of a Streaming Database Now, let’s introduce a couple of examples of commercially available streaming databases, to highlight their features and fields of application. RisingWave Image from the RisingWave website RisingWave is a distributed SQL streaming database that enables simple, efficient, and reliable streaming data processing. It consumes streaming data, performs incremental computations when new data comes in, and updates results dynamically. Since it is a distributed database, RisingWave embraces parallelization to meet the demands of scalability. By distributing the processing tasks across multiple nodes or clusters, in fact, it can effectively handle a high volume of incoming data streams concurrently. This distributed nature also ensures fault tolerance and resilience, as the system can continue to operate seamlessly, even in the presence of node failures. Also, RisingWave Database is an open-source distributed SQL streaming database designed for the cloud. In particular, it was designed as a distributed streaming database from scratch, not a bolt-on implementation based on another system. Image from the RisingWave website It also reduces the complexity of building stream-processing applications by allowing developers to express intricate stream-processing logic through cascaded materialized views. Furthermore, it allows users to persist data directly within the system, eliminating the need to deliver results to external databases for storage and query serving. Image from the RisingWave website The ease of the RisingWave database can be described as follows: Simple to learn: It uses PostgreSQL-style SQL, enabling users to dive into stream processing as they’d do with a PostgreSQL database. Simple to develop: As it operates as a relational database, developers can decompose stream processing logic into smaller, manageable, stacked materialized views, rather than dealing with extensive computational programs. Simple to integrate: With integrations to a diverse range of cloud systems and the PostgreSQL ecosystem, RisingWave has a rich and expansive ecosystem, making it straightforward to incorporate into existing infrastructures. Finally, RisingWave provides RisingWave cloud: a hosted service that brings the power to create a new cloud-hosted RisingWave cluster and get started on stream processing in minutes. Image from the RisingWave website Materialize Image taken from the Materialize website Materialize is a high-performance, SQL-based streaming data warehouse designed to offer real-time, incremental data processing with a strong emphasis on simplicity, efficiency, and reliability. Its architecture enables users to build complex, incremental data transformations and queries on top of streaming data with minimal latency. Since it is built for real-time data processing, Materialize leverages efficient incremental computation to ensure low-latency updates and queries. By processing only the changes in data rather than reprocessing entire data sets, it can handle high-throughput data streams with optimal performance. Materialize is designed to scale horizontally, distributing processing tasks across multiple nodes or clusters to manage a high volume of data streams concurrently. This nature also enhances fault tolerance and resilience, allowing the system to operate seamlessly even in the face of node failures. As an open-source streaming data warehouse, Materialize offers transparency and flexibility. It was built from the ground up to support real-time, incremental data processing, not as an add-on to an existing system. It significantly simplifies the development of stream-processing applications by allowing developers to express complex stream-processing logic through standard SQL queries. Developers, in fact, can directly persist data within the system, eliminating the need to move results to external databases for storage and query serving. Image taken from the Materialize website The ease of Materialize can be described as follows: Simple to learn: It uses PostgreSQL-compatible SQL, enabling developers to leverage their existing SQL skills for real-time stream processing without a steep learning curve. Simple to develop: Materialize allows users to write complex streaming queries using familiar SQL syntax. The system's ability to automatically maintain materialized views and handle the underlying complexities of stream processing means that developers can focus on business logic rather than the intricacies of data stream management, making the development phase easy. Simple to integrate: With support for a variety of data sources and sinks including Kafka and PostgreSQL, Materialize integrates seamlessly into different ecosystems, making the incorporation with existing infrastructure easy. Image taken from the Materialize website Finally, Materialize provides strong consistency and correctness guarantees, ensuring accurate and reliable query results even with concurrent data updates. This makes it an ideal solution for applications requiring timely insights and real-time analytics. Materialize's ability to deliver real-time, incremental processing of streaming data, combined with its ease of use and robust performance, positions it as a powerful tool for modern data-driven applications. Conclusions In this article, we’ve analyzed the need for streaming databases when processing streams of data, comparing them with traditional databases. We’ve also seen that the implementation of a streaming database in existing software environments presents challenges that need to be addressed, but available commercial solutions like RisingWave and Materialize overcome them. Note: This article has been co-authored by Federico Trotta and Karin Wolok.
Microsoft recently announced the introduction of vector search functionality in Azure Cosmos DB for MongoDB vCore. This feature enhances the capabilities of Cosmos DB by allowing developers to perform complex similarity searches on high-dimensional data, which is particularly useful in RAG-based applications, recommendation systems, image and document retrieval, and more. I am also participating in the Cosmos DB hackathon to explore more about how we can use this inside retrieval augmented generation. In this article, we will explore the details of this new functionality, its use cases, and provide a sample implementation using Python. What Is a Vector Store? A vector store (or vector database) is designed to store and manage vector embeddings. These embeddings are mathematical representations of data in a high-dimensional space. Each dimension corresponds to a feature of the data, and tens of thousands of dimensions might be used to represent sophisticated data. For example, words, phrases, entire documents, images, audio, and other types of data can all be vectorized. In simpler terms, vector embeddings are a list of numbers that can represent inside a multi-dimensional space for any complex data. Example Pen: [0.6715,0.5562,0.3566,0.9787] Now we can represent a pen inside a multi-dimensional space and then use vector search algorithms to perform a similarity search to retrieve the closest matching elements. How Does a Vector Index Work? In a vector store, vector search algorithms are used to index and query embeddings. Vector indexing is a technique used in ML and data analysis to efficiently search and retrieve information from large datasets. Some well-known algorithms include: Flat Indexing Hierarchical Navigable Small World (HNSW) Inverted File (IVF) Indexes Locality Sensitive Hashing (LSH) Indexes Vector search allows you to find similar items based on their data characteristics rather than exact matches on a property field. It’s useful for applications such as: Searching for similar text Finding related images Making recommendations Detecting anomalies Integrated Vector Database in Azure Cosmos DB for MongoDB vCore The Integrated Vector Database in Azure Cosmos DB for MongoDB vCore enables you to efficiently store, index, and query high-dimensional vector data directly within your Cosmos DB instance. Both transactional data and also vector embeddings are stored inside Cosmos DB together. This eliminates the need to transfer data to separate vector stores and incur additional costs. It works in 2 steps: 1. Vector Index Creation To perform a vector similarity search over vector properties in your documents, you’ll first need to create a vector index. This index allows efficient querying based on vector characteristics. 2. Vector Search Once your data is inserted into your Azure Cosmos DB for MongoDB vCore database and collection, and your vector index is defined, you can perform a vector similarity search against a targeted query vector. What Is Vector Search? Vector search, also known as similarity search, or nearest neighbor search, is a technique used to find objects that are similar to a given query object in a high-dimensional space. Unlike traditional search methods that rely on exact matches, vector search leverages the concept of distance between points in a vector space to find similar items. This is particularly useful for unstructured data like images, audio, and text embeddings. Benefits of Vector Search in Cosmos DB Efficient similarity searches: Enables fast and efficient searches on high-dimensional vectors, making it ideal for recommendation engines, image search, and natural language processing tasks Scalability: Leverages the scalability of Cosmos DB to handle large datasets and high query volumes. Flexibility: Integrates seamlessly with existing MongoDB APIs, allowing developers to use familiar tools and libraries. Use Cases Recommendation systems: Providing personalized recommendations based on user behavior and preferences Image and video retrieval: Searching for images or videos that are visually similar to a given input Natural Language Processing: Finding documents or text snippets that are semantically similar to a query text Anomaly Detection: Identifying unusual patterns in high-dimensional data Setting Up Vector Search in Cosmos DB Prerequisites An Azure account with an active subscription Azure Cosmos DB for MongoDB vCore configured for your workload Detailed Step-By-Step Guide and Sample Code Written in Python Create a Cosmos DB account: Navigate to the Azure portal. Search for Azure Cosmos DB and select the MongoDB (vCore) option. Follow the prompts to create your Cosmos DB account. Configure your database: Create a database and a collection where you’ll store your vectors. Ensure that the collection is appropriately indexed to support vector operations. Specifically, you’ll need to create an index on the vector field. Insert vectors into the collection: Vectors can be stored as arrays of numbers in your MongoDB documents. Set up your project: Create a new Python project (e.g., using Visual Studio or Visual Studio Code). Import necessary MongoDB and Azure/OpenAI modules Connect to the database using Mongo client. Inserting data: The code below shows how to insert order data from a local JSON file and insert embeddings into contentVector field. Generate vector embeddings by using the open AI getEmbeddings() method. Here is the full code for your reference: JavaScript const { MongoClient } = require('mongodb'); const { OpenAIClient, AzureKeyCredential} = require("@azure/openai"); // Set up the MongoDB client const dbClient = new MongoClient(process.env.AZURE_COSMOSDB_CONNECTION_STRING); // Set up the Azure OpenAI client const aoaiClient = new OpenAIClient("https://" + process.env.AZURE_OPENAI_API_INSTANCE_NAME + ".openai.azure.com/", new AzureKeyCredential(process.env.AZURE_OPENAI_API_KEY)); async function main() { try { await dbClient.connect(); console.log('Connected to MongoDB'); const db = dbClient.db('order_db'); // Load order data from a local json file console.log('Loading order data') const orderRawData = "<local json file>"; const orderData = (await (await fetch(orderRawData)).json()) .map(order => cleanData(order)); await insertDataAndGenerateEmbeddings(db, orderData); } catch (error) { console.error('An error occurred:', error); } finally { await dbClient.close(); } } // Insert data into the database and generate embeddings async function insertDataAndGenerateEmbeddings(db, data) { const orderCollection= db.collection('orders'); await orderCollection.deleteMany({}); var result = await orderCollection.bulkWrite( data.map(async (order) => ({ insertOne: { document: { ...order, contentVector: await generateEmbeddings(JSON.stringify(order)) } } })) ); console.log(`${result.insertedCount} orders inserted`); } // Generate embeddings async function generateEmbeddings(text) { const embeddings = await aoaiClient.getEmbeddings(embeddingsDeploymentName, text); await new Promise(resolve => setTimeout(resolve, 500)); // Rest period to avoid rate limiting on Azure OpenAI return embeddings.data[0].embedding; } Note: Remember to replace placeholders (Cosmos DB connection string, Azure OpenAI key, and endpoint) with actual values. Managing Costs To manage costs effectively when using vector search in Cosmos DB: Optimize indexes: Ensure that only necessary fields are indexed. Monitor usage: Use Azure Monitor to track and analyze usage patterns. Auto-scale: Configure auto-scaling to handle peak loads efficiently without over-provisioning resources. Data partitioning: Partition your data appropriately to ensure efficient querying and storage. Conclusion The introduction of vector search functionality in Azure Cosmos DB for MongoDB vCore opens up new possibilities for building advanced AI and machine learning applications. By leveraging this feature, developers can implement efficient similarity searches, enabling a wide range of applications from recommendation systems to anomaly detection. With the provided Python code examples, you can get started with integrating vector search into your Cosmos DB-based applications. For more detailed documentation, visit the Azure Cosmos DB documentation.
Relational Databases are the bedrock of any FinTech application, especially for OLTP (Online transaction Processing). This foundational component in any application architecture usually poses challenges around scaling as the business expands rapidly. So, it is imperative that all database activities are monitored closely in the production environment and issues like long-running queries are tracked and resolved. This article will explore the FinTech case study, which has built a Lending Platform. The company uses the MySQL database hosted in AWS as part of the AWS RDS service. It has multiple microservices using different database schemas hosted on the database instance. The MVP product offering was launched a few years back, and since then, they have been incorporating new features into the FinTech platform. We will cover commonly identified database issues and what was done to resolve these issues. Common Mistakes This section discusses common mistakes identified, the steps to resolve the issues, and additional guidelines. Using Database Views Inside Stored Procedures After it started seeing database growth, the major issue identified in the initial days with the MySQL database was that usage of views inside stored procedures resulted in long-running queries and full table scans. The pattern we saw with the developers was that they built multiple reusable views and used those in the stored procedures. The example below is a stored procedure invoking a view uv_consultations: MySQL CREATE PROCEDURE `usp_get_consultation`( in_dso_ref_id varchar(45) ) BEGIN select * from uv_consultations where dso_ref_id=in_dso_ref_id; END Here is what the view looks like: MySQL CREATE OR REPLACE VIEW uv_consultations AS SELECT c.id AS consultation_id, c.dso_ref_id AS dso_ref_id, c.pat_id AS pat_id, p.first_name AS pat_first_name, p.last_name AS pat_last_name, p.dso_pat_id AS dso_pat_id, p.dob AS pat_dob, COUNT(cn.id) AS notifs FROM ((((consultation c JOIN patient p ON ((c.pat_id = p.id))) LEFT JOIN application app ON ((c.id = app.consultation_id))) LEFT JOIN responsible_party rp ON ((app.primary_rp_id = rp.id))) LEFT JOIN consultation_notif cn ON (((cn.consultation_id = c.id) AND (cn.read = 0) AND (cn.hide = 0)))) GROUP BY c.id So, if we look at the view, it has multiple joins and a group by. It's a complex view query. MySQL executes views in two ways: view merging and view materialization. View Merging In this approach, MySQL merges the view query with the outer query in which the view has been referenced. The biggest advantage is that it uses underlying table indexes of the actual base table, improving the query timing. However, there are limitations to view merging. The query within the view must be a simple select statement with no aggregations, joins, sub-queries, or DISTINCT clauses. View Materialization When MySQL cannot perform a merge view, it defaults to View Materialization. In this approach, MySQL stores the view results in a temporary internal table and then uses the query on the view to query the internal table. The drawback is that it does not use base table indexes, causing the queries on the view to run slower. Views with GROUP BY, aggregation, DISTINCT, and complex joins trigger view materialization. In the above example, the view has aggregation and multiple joins, so the stored procedure's execution results in view materialization, which causes it to execute slowly. To mitigate this, refactored the stored procedure to use the direct complex SQL query and avoid the intermittent layer of views. Leading Wildcard String Comparison If we use the LIKE operator and the leading wild card search, e.g., LIKE ‘%AMOL’, then the query will not use the index on that column. Below is the sample query: MySQL SELECT COUNT(*) INTO v_ext_partner_service_count FROM loan_ext_payload WHERE loan_acct_id = v_loan_acct_id AND ext_partner_service LIKE '%CREDIT_PULL' For LIKE ‘CREDIT_PULL%’, MySQL uses the index efficiently as the indexes are structured in a way that this type of search makes them inherently faster. On the contrary, for the leading wildcard LIKE ‘%CREDIT_PULL’, MySQL execution engine looks at each entry in the index if it ends with ‘CREDIT_PULL.’ The index is optimized for prefixing (CREDIT_PULL% ), not suffixing (%CREDIT_PULL); its performance benefits are wiped out with leading wildcard string comparisons. The recommendation is to avoid using prefixed wild card string comparison or search. If this is unavoidable, use a full-text search. Using Functions as Part of the WHERE Clause Using functions for filtering records using a WHERE condition can harm query performance. For instance, using FIND_IN_SET() method as in the below query: MySQL SELECT code, code_type, message, message_es FROM locale_messages WHERE code_type = v_code_type AND FIND_IN_SET(code, v_codes) > 0; FIND_IN_SET returns the position of a string if it is present (as a substring) within a list of strings. Using FIND_IN_SET in the query causes a full table scan as it does not use the index. MySQL has to scan each record to evaluate the function, which could be very expensive from a computing perspective for larger tables. Similarly, mathematical, string, or date functions would have a similar side effect. Below are the examples: MySQL SELECT loan_id, loan_acct_id FROM loan_application WHERE YEAR(create_date) = 2024; MySQL SELECT loan_id, loan_acct_id FROM loan_application WHERE YEAR(create_date) = 2024; MySQL SELECT loan_id, loan_acct_id FROM loan_application WHERE ABS(merchant_discount_fee) > 5; If we cast columns as part of the where clause, it again leads to a full table scan, impacting the query performance. Example as below: MySQL SELECT loan_id, loan_acct_id FROM loan_application WHERE CAST(approval_datetime AS DATE) = '2024-06-01'; As a resolution for FIND_IN_SET, it enabled full-text search. For all other function-based where conditions, function-based indexes were created. Performing Deletes as Part of Regular Transactions Frequent delete operations as part of regular transaction processing can degrade database performance. Deleting rows from a table leads to index rebalancing. MySQL has to update the indexes on the columns for that table as it has to remove the index entries. For rollback and recovery, transaction logs are created for all delete operations. This can lead to rapid growth of transaction logs. The rows deleted in random order fragment tables and indexes degrade performance due to scattered reads. To mitigate regular delete operations, rows can be soft-deleted, and then, an offline batch job could perform a hard delete during non-peak hours. Non-peak hours allow the system to use its resources effectively. Batch or bulk deletes are more performant than the deletes span across regular intervals. Executing Multiple Inserts From the Application Instead of Batching If we have to insert multiple child rows for the parent record from the application layer, we will execute a stored procedure for each insert instead of batching it. Each call on insert needs to establish a database connection from the application layer and execute individual stored procedures for each record, which could be inefficient. To avoid multiple round trips to the database server, the multi-insert statement was built as below on the application layer and then sent to the database-stored procedure to execute it. MySQL INSERT INTO verif_user_attrib_result (id, application_id, user_attrib_id, doc_type_id, verif_result, verif_notes, verif_date, doc_upload_count) values(148712,146235,1,NULL,1,NULL,NULL,0), (148712,146235,2,NULL,1,NULL,NULL,0), (148712,146235,3,NULL,1,NULL,NULL,0), (148712,146235,4,NULL,-1,NULL,NULL,0); MySQL CREATE PROCEDURE p_verif_create_user_attrib_results ( IN v_user_attrib_result_multi_insert TEXT, out v_status int) BEGIN SET v_status = 1; SET @qry_user_attrib_result = v_user_attrib_result_multi_insert; PREPARE stmt_user_attrib_result from @qry_user_attrib_result; EXECUTE stmt_user_attrib_result; DEALLOCATE PREPARE stmt_user_attrib_result; SET v_status = 0; END Recommendations Connection Labels In the microservices world, where multiple microservices connect to different or the same schemas of the database server, monitoring the connection requests from the different microservices often becomes difficult. If there is an issue with connection pools, connection draining, or database performance issues, we cannot identify which connection belongs to which service. This is where program_name connection attributes come in handy. Java jdbc:mysql://db.fintech.com:3306/icwdev?cacheCallableStmts=true &callableStmtCacheSize=1000&connectionAttributes=program_name:loan-application-api This attribute labels every connection to the respective program name. This connection identifier helps segregate database issues with a specific service. Apart from diagnosing issues, it helps in enhanced monitoring. We can build query performance metrics and error rates for specific microservices. Purging Large Tables For large tables, we purge the data based on purging criteria. Utility tables managing user access tokens are periodically purged. To manage purging effectively, we implement partitioning on the tables. Partitioning is time-based Index Columns Used in Join We ensure that all columns listed in join conditions are indexed. By including index columns in join conditions, the database engine filters the data efficiently using indexed data, thus avoiding the entire table scan. Keep Transactions Smaller In high-concurrency applications, we must ensure smaller transactions for optimal database performance. Smaller transactions reduce the duration of data locking and minimize resource contention, which helps reduce resource contention, avoid deadlocks, and improve the transaction success rate. Miscellaneous Common Asks We need to ensure that UPDATE statements include a WHERE clause. At a FinTech organization, some complex stored procedures missed the WHERE clause, leading to unintended behaviors in the test environment. Strict no to “SELECT *” in queries. Conclusion We have covered common pitfalls and resolutions for fine-tuning the application and underlying MySQL database performance through the FinTech application case study. Here are some important suggestions: avoid using complex views in stored procedures, stay away from leading wildcard searches, monitor queries with full table scans, and take appropriate corrective actions. Perform delete operations during offline hours through the batch job. Avoid multiple roundtrips to the server for multi-inserts; instead, use batching. Define a purging strategy. Have indexes defined on all join columns, and finally, try to keep the transaction scope smaller. With these recommendations incorporated, we can provide optimal database performance and faster end-user applications. Continuous monitoring and proactive issue resolution help maintain consistent application performance and availability.
Abhishek Gupta
Principal Developer Advocate,
AWS
Artem Ervits
Principal Sales Engineer,
Snowflake
Otavio Santana
Award-winning Software Engineer and Architect,
OS Expert
Denis Magda
Head of DevRel,
Yugabyte