May 2023 Summaries

DSS Europe 2023 Recap: An Event to Remember (Mark Your Calendar for Next Year!)` This year's Distributed SQL Summit (DSS) Europe event was co-located with KubeCon + CloudNativeCon Europe in Amsterdam, and it was a huge success. The event catered to the interests of attendees who were primarily focused on building modern microservices, and it addressed their key challenges through discussions and demonstrations centered around YugabyteDB's distributed SQL database. The audience participated in polls and quizzes, which helped tailor the content to their needs. The event featured demos showcasing YugabyteDB's capabilities, including its ability to eliminate data layer compromises, support multiple fault zones for resilience, and provide a migration solution called Voyager that supports various databases like PostgreSQL, Oracle, MySQL, and CSP databases. The audience seemed pleased with the efforts, and it was a great show for Yugabyte, solidifying the relevance of distributed SQL for operational microservices applications.

In PostgreSQL, indexing JSON documents can be similar to indexing relational table columns, allowing for efficient querying based on access patterns. To demonstrate this, a Pokémon GO Pokédex dataset is used, with each Pokémon stored as a row in a table containing relevant information in a JSONB column. Indexing techniques such as creating indexes on scalar values using `->>` and on arrays using GIN are explored, allowing for fast and scalable access to specific data within the JSON documents. Additionally, views can be created to encapsulate indexed expressions, ensuring consistency between the view definition and query logic. By indexing JSON keys and attributes based on access patterns, SQL databases can provide flexibility in optimizing for new read patterns without requiring changes to application code, making them suitable for semi-structured data storage.

The concept of partitioning and sharding in YugabyteDB is often misunderstood as two separate concepts, but they can actually be used together to create unique configurations such as row-level geo-partitioning. In YugabyteDB, every table and index is automatically sharded across the cluster, regardless of whether it's partitioned or not. This means that partitioning is treated at the query level, while sharding is at the storage level. Users can verify this by checking the tablets created in their cluster or using a specific `yb-admin` command to list all the tablets associated with a particular table.

YugabyteDB is a distributed SQL database that supports both colocated and non-colocated tables, which are useful for specific use cases. A non-colocated database has all tables distributed across the nodes in a cluster, with hash sharding applied by default and partition columns used to determine node placement. In contrast, a colocated database is created using a CREATE DATABASE command with the WITH COLOCATION = 'true' option, where all tables are colocated unless explicitly declared otherwise. Colocated tables are organized by the sort order of their primary key columns in one tablet only, while non-colocated or distributed tables can be hash sharded and also range sharded if needed. The choice between colocated and non-colocated tables depends on the specific use case and requirements of the application.

YugabyteDB 2.18 introduces new features designed to accelerate data modernization efforts with cloud-native capabilities, such as powering multi-region Kubernetes at scale and supporting a diverse set of workloads. The solution offers simplified operations through automation and intelligence added to its powerful self-managed DBaaS, YugabyteDB Anywhere, which includes enhancements like Performance Advisor, Terraform Provider, advanced backups and protection, and more.

To generate SQL scripts dynamically using PostgreSQL, you can use several features such as dollar-quoted string constants, format(), and catalog views to get metadata. You can replace outer quotes with $TAG$, build strings dynamically with format(), use %s for text, %L for single-quotes, %I for identifiers that may need double quotes, and %n$ for repeating arguments. Additionally, you can use \gexec to execute the result. The goal is not to execute arbitrary DDL statements in production but to create maintainable scripts free from hardcoded text and double quotes. You should also consider using a small additional where clause when querying from catalog views to create idempotent scripts that avoid executing existing statements again.

If a Node Becomes Unavailable, Does YugabyteDB Propagate Client Requests to Surviving Nodes? Or Do Clients Have to Handle That Retry Logic?` The availability of a node in a YugabyteDB cluster affects client requests. If clients are connected directly to the node, physical connections will be invalidated and connection pooling will adjust the minimum pool requirements for other nodes. Connection pooling manages new connections transparently to surviving nodes, allowing clients to retry failed transactions or set their own retry logic based on error codes. Passing multiple endpoints in a connection string (at least 2/3) helps create new client connections based on available endpoints, and having at least one endpoint per region is recommended for multi-region clusters.

YugabyteDB is a distributed database that requires understanding client connections and connection pooling for optimal performance, especially in high-concurrency workloads. The first query execution on a new connection experiences higher latency due to the metadata cache load into the catalog cache using RPC calls to the master server. Connection pooling can be used to create a set of physical connections from the client application to the database during application initialization, enabling the reuse of these connections for query executions and shifting the connection establishment and metadata retrieval phases to the connection pool startup. The optimal size of a connection pool depends on the application volume and can be achieved by making it as low as possible while using a fixed amount (min=max) of connections. YugabyteDB provides a robust client connection and connection pooling mechanism, with features such as leader affinity and t-server cache implementation to optimize performance.

The integration of YugabyteDB Change Data Capture (CDC) with Redpanda allows for seamless streaming of data from YugabyteDB to various sinks, leveraging Redpanda's Kafka API. Redpanda is built to support high-performance and low-latency applications, while YugabyteDB is a distributed SQL database designed for transactional apps. The integration uses the Debezium connector to capture changes in YugabyteDB and stream them to Redpanda, which then sends the data to various sinks such as databases, object storage, or other systems. This setup provides high-performance, low-latency, and optimized resource utilization, making it an attractive option for organizations looking to modernize their data infrastructure while reducing costs.

The Orafce extension for PostgreSQL provides a function called `dbms_random.string` which can be used to generate random strings of varying lengths, with options to specify different character sets such as printable characters, alphabetical characters, or uppercase letters. The function takes two parameters: the length of the string and the character set to use. For example, `dbms_random.string('P', 42)` would generate a random string of length 42 using only printable characters. The extension is compatible with PostgreSQL-compatible databases like YugabyteDB. However, there are some differences in behavior between Orafce and Oracle's built-in function for generating random strings, including the ability to return certain special characters like `\` and `~`. To work around this issue, a workaround has been implemented by making the function volatile instead of immutable, which is equivalent to making it deterministic in Oracle. Additionally, when creating the extension, the Orafce functions are deployed to schemas with the same name as the Oracle package they emulate. The Orafce extension provides many useful and convenient utility functions that can enhance the PostgreSQL ones.

When using YugabyteDB, the number of shards to break a table into depends on its size and activity level. For small tables under 1 GB, colocating them with larger tables can be beneficial, while medium-sized tables (single digit GBs to 100s of GB) should aim for 8 tablets per tserver, and large tables over 250 GBs should be split into more tablets to avoid hotspots and ensure equal resource usage. The goal is to keep each tablet under a certain size threshold, and enabling auto-splitting can help with this. Additionally, indexes are sharded in the same way as tables, and there are various resources available for learning more about YugabyteDB, including blog posts, videos, and training sessions.

YugabyteDB offers a comprehensive set of deployment and replication options for geo-distributed environments, including Single Region Multi-Zone Cluster, Multi-Region "Stretched" Clusters With Synchronous Replication, Multi-Region Clusters With UniDirectional Asynchronous Replication, Multi-Region Clusters With Bi-directional Asynchronous Replication, Geo-Partitioning With Data Pinning, and Read Replicas. These options enable businesses to build globally-distributed applications with low-latency data access, resilience to zone and regional cloud failures, and strong consistency of writes. YugabyteDB's deployment options allow customers to choose the right mix of deployment options to best fit their application's needs, whether it's in-region or cross-region requirements. The cluster's geo-partitioning feature ensures that data is stored in specific geographic locations based on partitioning rules, providing low latency access to data from users in the region while preserving transactional consistency semantics. Overall, YugabyteDB delivers a flexible and scalable solution for building globally-distributed applications with strong consistency and cloud freedom.

Flyway is an open-source database migration tool that facilitates Continuous Integration (CI) processes for any database platform. It uses a naming convention to refer to all Data Definition Language (DDL) and Data Manipulation Language (DML) changes made to the database, which are captured and versioned in a designated directory. Flyway supports multi-region deployments and can be used with YugabyteDB xCluster replication to handle data version control across multiple data centers. In an active-active deployment, both sides can perform reads and writes, while in an active-passive deployment, one cluster acts as the source and the other as the sink. By using dedicated Flyway instances for each region, database changes such as DMLs and DDLs can be easily propagated to other regions with minimal manual work on the replication setup. However, limitations exist, including the lack of automatic DDL replication and transactional replication, which are planned to be addressed in future enhancements.

There is no built-in tool for visualizing the data stored in your YugabyteDB cluster. However, you can utilize third-party tools such as pgAdmin, DBeaver, and ArcType that have adapters for YugabyteDB, allowing developers to work with existing tools and integrations while leveraging the benefits of YugabyteDB's compatibility with Postgres and Cassandra. This enables seamless integration with popular tools like Kafka, Spark, and Flyway, making it easier for developers to use and manage their data. The YugabyteDB team prioritized supporting these tools and integrations as part of their focus on working within the existing developer ecosystem.

Seven Multi-Region Deployment Best Practices` aims to provide a better understanding of how to deploy databases across multiple regions with reduced latency and improved performance. By following these best practices, developers can ensure their applications handle region outages without affecting customers, achieve low-latency reads, and improve overall system availability. The key strategies include reducing inter-region latency by configuring preferred leaders, enabling follower reads for read-heavy workloads, localizing data with tablespaces, using row-level geo-partitioning to store data in different regions, avoiding trips to the table with covering indexes, generating duplicate covering indexes across regions, and utilizing load balancing with smart drivers. By implementing these strategies, developers can create highly available services that provide low-latency reads and improve overall system performance.

The solution enables organizations to gain valuable insights into their Change Data Capture (CDC) data in near real-time, by streaming YugabyteDB CDC data into an Azure Synapse Analytics workspace. The reference architecture uses a pull-based model that reports changes from the database's Write-Ahead-Log (WAL), and involves components such as Debezium Connector, Azure Event Hubs, Azure Synapse Pipelines, ADLS Gen2 Storage account, and Azure Synapse workspace. To get started, prerequisites include having a YugabyteDB Anywhere database with CDC enabled, an Azure Event Hubs instance, and an Azure Synapse Analytics workspace with SQL Pools and Spark Pools. The process involves creating an Event Hubs namespace and Event Hubs, creating a YugabyteDB CDC Connector, configuring the connector to send data to Event Hubs, running the connector, pulling events from Event Hubs, and viewing the CDC data in Azure Synapse Dedicated SQL Pools or Spark Pools.

Customer Managed Key Encryption for Data at Rest in YugabyteDB Managed provides complete control over data security by allowing users to encrypt their data at rest using their own encryption keys, adding an extra layer of protection and meeting compliance requirements. This new feature enables users to manage their encryption keys, demonstrating compliance with data security regulations and providing greater control over the security of their data. The feature is easy to use, requiring just a few clicks in the user interface or via API, and can be integrated with AWS KMS for symmetric key management. With CMK encryption, users can take greater control over the security of their data in YugabyteDB Managed.