July 2019 Summaries

You can download and install the PostgreSQL version of the Chinook sample DB on a distributed SQL database like YugabyteDB with a replication factor of 3. YugabyteDB is a high performance distributed SQL database for global, internet-scale apps that gives you all the scalability characteristics of NoSQL without sacrificing ACID transactions or strong consistency. The Chinook data model represents a digital media store with 11 tables, indexes, primary and foreign key constraints, and over 15,000 rows of data. To get started, download and install YugabyteDB on your local machine, configure loopback addresses, create a 3 node cluster, enter the YSQL shell, and then build the Chinook database by downloading the Chinook scripts, creating the database, building the tables and objects, loading sample data into Chinook, and exploring the Chinook sample DB. With YugabyteDB, you can now start exploring the Chinook sample database and compare it with other databases like CockroachDB, Google Cloud Spanner, and MongoDB.

DBeaver is a free, multi-platform database management tool that supports various databases including YugabyteDB, allowing developers and analysts to connect, explore, query, and manage data with ease, featuring a connection and metadata browser, SQL query editor and executor, in-line data editor, entity relationship diagrams, and more.

** The SportsDB dataset is a sample dataset for sports data applications that can be used with PostgreSQL-compatible databases like YugabyteDB. It has over 100 tables and 80k rows of data, and has been ported to multiple platforms including MySQL, SQL Server, and PostgreSQL. To use the dataset on YugabyteDB, you need to download and install YugabyteDB, configure loopback addresses, create a 3-node cluster with a replication factor of 3, enter the YSQL shell, build the SportsDB database by downloading and installing the database scripts, and then explore the database by executing SQL queries. The SportsDB dataset can be used for exploring distributed SQL databases like YugabyteDB and PostgreSQL.

The PGExercises PostgreSQL tutorial is designed for use with a distributed SQL database such as YugabyteDB. The dataset used by the website consists of three tables and relationships that are modeled in an ER diagram. To run the exercises, users must first download and install YugabyteDB on their system, followed by downloading and installing the PGExercises database, which includes scripts to create tables and load sample data. Once installed, users can use the ysqlsh command to enter the PostgreSQL shell, where they can execute SQL queries and interact with the database. The tutorial covers 81 exercises across various topics, including simple SQL queries, JOINs, aggregations, and more, although some exercises have known issues that may affect their behavior on YugabyteDB.

YugabyteDB has released version 1.3.0, shipping with 24 new features, enhancements, and bug fixes, as well as open sourcing the entire core database code, which includes previously closed features like distributed backups, data encryption, and replicas. This release brings the company closer to its 2.0 release at the end of summer, where the YSQL API will become generally available. The YSQL API is a PostgreSQL-compatible, distributed SQL API that allows developers to build distributed SQL applications on top of YugabyteDB. Key features in this release include support for enabling DO statements, allowing SELECT from sequences, and adding support for setval(). The company also highlights new documentation, blogs, tutorials, and videos, as well as an open call for contributors. Additionally, YugabyteDB is hiring and will be attending several conferences and meetups over the next few months.

The Northwind database is a sample schema used for various tutorials and is now available in a PostgreSQL-compatible version on the YugabyteDB distributed SQL database. YugabyteDB offers high performance, scalability, and strong consistency similar to PostgreSQL without sacrificing ACID transactions. To get started with the Northwind database on YugabyteDB, one needs to download and install YugabyteDB, create a 3-node cluster, configure loopback addresses, enter the YSQL shell, download and install the Northwind sample database, build tables and objects, load sample data into the database, and explore the dataset. The resulting dataset consists of 14 tables with relationships showcased in an entity relationship diagram, containing suppliers, customers, employees, products, shippers, orders, and order details.

YugabyteDB is now 100% open source under the Apache 2.0 license, removing its closed-source features and making them available for free. This change eliminates the distinction between the Community Edition and Enterprise Edition, offering a single edition with no restrictions. The company also released its management software under a free-trial-only license from the Polyform Project. By adopting open source, YugabyteDB aims to fill the gap in PostgreSQL's support for dynamic cloud platforms, making it an attractive option for developers building microservices-based applications on multiple clouds. Industry experts note that this change is unusual, as many database and data infrastructure companies have shifted towards proprietary licenses in recent years. However, YugabyteDB believes that open source is the most successful approach to develop and distribute business-critical infrastructure software, promoting collaboration, rapid feedback loops, and high-quality reliability. The company has also decided to not only make YugabyteDB 100% OSS but also draw a clear line of separation between the OSS DB project and its commercial DBaaS offerings.

YugabyteDB version 1.3 is now available, with all enterprise features moved to open source under the Apache 2.0 license. This change aims to realize the company's vision of becoming the Oracle for the modern cloud. The new release includes significant updates such as distributed backups and restore, TLS encryption, data at rest encryption, read replicas, and other upcoming features like Change Data Capture and 2 Data Center Deployments. Yugabyte Platform, a commercial product offering an advanced private DB-as-a-Service, now has its source code available on GitHub under the Polyform Free Trial 1.0 license. Additionally, early access to Yugabyte Cloud, a fully-managed cloud offering on AWS and Google Cloud Platform, is now available for interested users. Other notable developments include YSQL's release candidacy from Beta 3 status, with plans for its general availability as part of YugabyteDB version 2.0, and ongoing work on ecosystem integrations such as GraphQL support and better support for Spring developers.

The latest update from YugabyteDB is now live, featuring 11 new enhancements and bug fixes in version 1.2.12, which brings the organization closer to its upcoming 2.0 release with a generally available YSQL API. The new API allows developers to visually inspect queries happening against the YSQL API, making it easier to debug and analyze SQL commands. Additionally, new documentation, blogs, tutorials, and videos have been released, covering various programming languages such as Java, NodeJS, Go, Python, Ruby, and more, as well as guides for contributing code to the project and getting started with YugabyteDB. The company also invites users to share their experiences with the database and provides resources for learning about its features and compatibility with other databases like CockroachDB and Google Cloud Spanner.

The post highlights the importance of relational data modeling with foreign keys in business-critical applications and provides a complete working example using YugabyteDB's YSQL API. Foreign keys enforce referential integrity constraints, which are essential for a relational database design. The implementation of primary and foreign key constraints is a technical challenge in a distributed SQL database due to the need to ensure low latency and high throughput while spanning multiple table shards across different nodes in the cluster. YugabyteDB 2.0 delivers production readiness for YSQL, supporting foreign key constraints and enabling developers to build scalable and maintainable applications without compromising data integrity. The post also discusses the challenges of enforcing foreign key constraints in a distributed SQL database and how YugabyteDB has addressed this issue with its support for serializable transactions since Version 1.2.0 and now also supports snapshot transactions from Version 2.0.3.