September 2020 Summaries

Flyway is an open source database version control and migration tool that can be used with various databases, including YugabyteDB. Flyway provides a simple way to manage schema changes by using seven commands: Migrate, Clean, Info, Validate, Undo, Baseline, and Repair. In this tutorial, we installed a 3 node YugabyteDB cluster on Google Kubernetes Platform, created a Northwind sample database, and configured Flyway to work with YugabyteDB. We then created a project, configured the Flyway integration, created table migration, inserted data migration, and executed these migrations using the Maven plugin. With this setup, we have versioning control managed by Flyway for our YugabyteDB cluster.

IntelliJ IDEA can be used to develop software on top of YugabyteDB, a distributed SQL database that is compatible with PostgreSQL. To get started, install a 3-node YugabyteDB cluster on Google Kubernetes Platform using Helm 3, and then create a sample Northwind database. Configure IntelliJ to work with YugabyteDB by adding a PostgreSQL data source and setting the connection string. Test the integration by running a sample query and generating an EXPLAIN PLAN visualization. With this setup, developers can now develop against the Northwind database in IntelliJ.

Retool is a next-generation WSIWYG (what you see is what you get) SaaS-based tool for quickly building distributed SQL and React applications from existing data sources, utilizing pre-built components and custom JavaScript. It supports over 30 native integrations, including PostgreSQL, AWS S3, and Cassandra, and is compatible with YugabyteDB, a PostgreSQL-compatible database. By following a step-by-step tutorial, users can install a YugabyteDB cluster on Google Kubernetes Platform, create a sample Northwind database, configure Retool to connect to the database, build a simple React application for managing inventory, test the application, and access it from mobile devices. The process allows developers to quickly deploy applications from their existing data sources, providing a fast and efficient way to build and manage internal applications.

Justuno, a Conversion Rate Optimization (CRO) Platform provider, leveraged YugabyteDB to consolidate multiple databases, including Microsoft SQL Server, Cassandra, and CockroachDB. The company's original database was built on an on-premises SQL Server but grew to become a single point of failure as the customer base expanded. Justuno moved its profile system to Cassandra but found it unsuitable for large amounts of data per node. They then looked into migrating workloads from Microsoft SQL Server to CockroachDB before starting an initiative to rebuild their platform using cloud infrastructure, specifically Google Cloud. This led them to explore YugabyteDB as a high-performance and SQL feature-rich database that could handle massive reads and writes with low latency. With 12 nodes on GCP, Justuno achieved north of 20,000 ops per second while maintaining average latency of three milliseconds. The company consolidated its databases from five types to a single system, reducing costs and complexity. The Yugabyte Platform is an all-in-one solution with an intuitive interface that enables point-and-click administration. Justuno has formed a technical and business partnership with Yugabyte, which provides open-source features and more freedom and control.

YugabyteDB has made significant improvements in its documentation, including the addition of new content and updates to existing sections. The YSQL API documentation now includes client drivers for Python and provides information on installing and using extensions such as pg_stat_statements and the Server Programming Interface (spi) module. The Migrate section covers various aspects of migrating from other RDBMS to YugabyteDB, including data export and import processes. Additionally, new sections have been added to the Secure page, covering password authentication methods and enabling SCRAM-SHA-256 authentication. Other updates include caching DNS entries, fine-tuning cluster rebalancing, and improvements to the Cloud shells for YSQL and YCQL. The documentation continues to be updated and improved with contributions from the community, allowing users to file GitHub issues, contribute to the docs, and participate in the Slack community to get answers and share knowledge.

The Heroku Yugabyte Cloud add-on is now available in public beta, making it easy for developers to deploy a fully-managed distributed SQL database in the cloud. The add-on integrates seamlessly with Heroku and allows users to use YugabyteDB as their system-of-record database for any Heroku app. Additionally, the add-on includes Yugabyte Cloud Shell, a web-based terminal that provides access to a Linux shell and enables developers to manage their database instance from the command line. With this beta release, developers can easily deploy a sample GraphQL application built using Hasura on Heroku, powered by Yugabyte Cloud. The add-on is free of cost, but requires a credit card on file for any usage.

In this article, we explore various tips and tricks for PostgreSQL and Oracle DBAs when working with distributed SQL databases like YugabyteDB. We discuss functional equivalents to Oracle's NVL function in PostgreSQL and YugabyteDB, and how to create views, materialized views, and rollups in both databases. Additionally, we cover the use of EXPLAIN ANALYZE to analyze query performance, creating indexes on columns, and provide resources for further learning. The article emphasizes the importance of performance tuning tools like EXPLAIN and ANALYZE, especially with larger datasets and more complex queries.