December 2019 Summaries

YugabyteDB is a distributed SQL database that supports JSONB data type, allowing for efficient updates and querying of large amounts of semi-structured data. To update a JSONB document in YugabyteDB, an update statement via the YSQL API can be used. The database does not have strict limits on object sizes, but keeping columns under 2MB can improve performance. YugabyteDB supports setting CPU and memory request limits for Kubernetes deployments using Helm charts or Operators. For multi-host, cloud deployments, options include Terraform, CloudFormation, and Kubernetes with Operators or Helm charts. New documentation, blogs, tutorials, and videos have been published, including guides on PostgreSQL table functions, row-level security, and change data capture. YugabyteDB is hiring for various positions, and the company is growing rapidly while seeking talented individuals to join its team.

YugabyteDB 2.0.8 is now live, shipping with 12 new enhancements and fixes, including improvements to the YSQL API and YCQL changes, as well as system updates and platform additions such as a Prometheus metrics export endpoint. The release also includes new documentation, blogs, tutorials, and videos covering topics like PostgreSQL table functions, row-level security, and change data capture. Additionally, the company is hiring for various positions and invites users to explore YugabyteDB features through their quickstart page.

`The PostgreSQL table function can be used to create user-defined functions that compute values that can then be used as an in list in a subquery. This allows for more flexibility and power when generating reports or performing other complex queries. The use cases presented include: 1) creating a parameterized view, 2) pretty-printed ad-hoc reports for administrators, 3) dynamic IN lists, and 4) compact syntax for bulk insert. These examples demonstrate the capabilities of PostgreSQL table functions in various scenarios, showcasing their potential to simplify code and improve performance.

Implementing PostgreSQL user-defined table functions in YugabyteDB allows for creating parameterized views, encapsulating the choice between different source tables while ensuring static SQL proof against SQL injection. The function can be defined using either `return next`, to return the next explicitly computed row, or `return query`, to return the entire result set of a select statement. User-defined table functions can be implemented in various languages, including PostgreSQL's `plpgsql` and `language sql`. The `return next` language is the most general, allowing for computing sets of values for each returned row explicitly, but it has limitations due to the fact that the entire result set is materialized before returning from the function. This behavior contrasts with Oracle database's pipelined table functions, which deliver results on demand. PostgreSQL table functions are most useful when delivering small, bounded result sets, making them suitable for various use cases such as parameterized views and encapsulating source tables while ensuring static SQL proof against SQL injection.

This series of posts explores the concept of table functions in PostgreSQL and their implementation in YugabyteDB, a distributed SQL database. Table functions are a type of function that returns a computed row set that depends on actual arguments invoked. They can be used in the from list of a subquery or in arbitrary combination with tables and views. Built-in SQL table functions include generate_series(), unnest(), and values(). User-defined table functions can also be implemented using return next or return query statements, allowing for more flexibility and customization. Understanding table functions is crucial for implementing business logic inside a database and can help optimize application design. The series covers the basics of table functions, their implementation in YugabyteDB, and provides examples to demonstrate their use cases.

The YugabyteDB community update discusses various topics such as cluster configuration, encryption at rest, and API compatibility. The community has been asking questions on how to configure clusters for different deployment models, how encryption works in YugabyteDB, and how compatible are YSQL and YCQL APIs with each other. Additionally, the update highlights new documentation, blogs, tutorials, and videos, including a recap of the 2019 Distributed SQL Summit. The update also mentions upcoming meetups and conferences, such as PostgreSQL Meetups and Distributed SQL Webinars, and provides resources for getting started with YugabyteDB, including a quickstart page and contact information for licensing and pricing inquiries.

YugabyteDB has released version 2.0.7, which includes almost 30 new enhancements and bug fixes, including improvements to the SQL API, transparent read restarts for general queries, row-level security, and support for instance profiles. The release also includes system improvements such as speedup data loading in Master Leader startup and cleanup of TIMED_OUT calls from /rpcz. Additionally, YugabyteDB has announced new documentation, blogs, tutorials, and videos, including a blog on PostgreSQL's Row-Level Security and a video on designing a change data capture architecture for a distributed SQL database. The company is also hiring for various positions, including software engineers, developer advocates, and senior technical writers.

YugabyteDB is a distributed SQL database that supports full 4-byte UTF8 encoding from the outset, unlike MySQL which only started supporting this in version 5.5.3 due to incorrect initial implementation. When a node cannot communicate with another node in a cluster, the UI marks the node as dead if it's the leader, but not otherwise. YugabyteDB generates self-signed certificates for SSL/TLS encryption and supports using native backup commands instead of EBS snapshots for backups. The platform has released new documentation, blogs, tutorials, and videos on topics such as PostgreSQL row-level security, change data capture, and distributed databases. YugabyteDB is hiring software engineers, developer advocates, and technical writers to join its growing team. Users can get started with YugabyteDB by visiting the quickstart page or contacting the company for licensing information.

In the latest YugabyteDB 2.0.6 release, PostgreSQL's row level security feature is supported out-of-the-box, allowing users to set policies that restrict access to specific rows of data in a table, with applications like customer or financial data being particularly useful for blocking access due to legal, liability, or compliance concerns. Users can create roles and grant privileges to those roles on tables, and then enable row level security on the table, creating policies that limit access to specific conditions such as employee_id values. This feature is available in YugabyteDB's SQL API and drivers, which are compatible with PostgreSQL wire protocol. With this feature, users can ensure data protection and compliance while developing applications using YugabyteDB.

The use of stored procedures in distributed SQL databases is still an emerging concept, with only two databases, YugabyteDB and Amazon Aurora, currently supporting them. However, the benefits of using stored procedures, such as correct storage and retrieval of business-critical data, robust security, and highly productive problem diagnosis and system maintainability, are unassailable when used in a monolithic SQL database. In contrast, a distributed SQL database like YugabyteDB can provide performance benefits by reducing client-server round trips for multi-statement business transactions, with the hard shell approach being faster than the bag of tables approach. The use of stored procedures also mitigates the risk of SQL injection attacks and provides security through the principle of least privilege. Additionally, the performance benefit increases with the number of SQL statements that implement a transaction. However, distributed SQL databases may have additional round trips between nodes in the cluster compared to monolithic SQL databases, which can affect performance. Overall, while the case for stored procedures is unassailable in monolithic SQL databases, its benefits are more nuanced in distributed SQL databases like YugabyteDB.