Google Cloud SQL for PostgreSQL 13

Google Cloud SQL for PostgreSQL 13

We explore Cloud SQL PostgreSQL 13. A fully-managed instance of the world’s most advanced open-source relational database

We explore Cloud SQL PostgreSQL 13. A fully-managed instance of the world’s most advanced open-source relational database

New to PostgreSQL?

PostgreSQL is a powerful, open-source object-relational database system. It shares many commonalities with other relational database platforms, including Microsoft SQL Server and MySQL.

New to Google Cloud SQL?

Part of the Google Cloud Platform ecosystem, Cloud SQL is a fully-managed database service designed to make it easy to set up, manage, and administer relational databases on Google Cloud Platform. At the time of writing, Cloud SQL supports MySQL, PostgreSQL, and SQL Server.

Did you know?

PostgreSQL started out in life in 1986 as a military-sponsored project, codenamed POSTGRES.

Developed at the University of California at Berkeley, the project received significant backing from Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. Excerpt from A Brief History of PostgreSQL

PostgreSQL 13

This much-anticipated release appears to be heavily centered around scalability, helping the platform to cope with ever-increasing storage volumes.

PostgreSQL 13 includes significant improvements to its indexing and lookup system that benefit large databases, including space savings and performance gains for indexes, faster response times for queries that use aggregates or partitions, better query planning when using enhanced statistics, and more. PostgreSQL 13 release note

You can really tell from this, that the engineers at PostgreSQL are going after addressing some of the challenges with query performance and storage size that users with large databases will be facing. And it’s good timing too, as we see more and more clients managing databases that now fall into this “large” category as they ingest more and more data.

Table index optimization

Building on work from the previous PostgreSQL release, PostgreSQL 13 can efficiently handle [duplicate data in B-tree indexes_](, the standard database index. This lowers the overall space usage that B-tree indexes require while improving overall query performance._ PostgreSQL 13 release note

We think this will benefit many users of the platform, as B-tree index duplicates are very common in any relational database. Duplicate data occurs when multiple leaf-nodes in the index (leaf-nodes point to a physical row in a table) contain the same value as another leaf-node and both point to the same physical row.

Duplicates include NULL values, so if you have an index that covers a sparsely populated column, you should see some good benefits from this change.

Deduplication is enabled by default but can be disabled. We note the process is managed lazily, and therefore should not impact DML performance.

A more intelligent query engine

PostgreSQL 13 introduces incremental sorting, where sorted data from an earlier step in a query can accelerate sorting at a later step. Additionally, PostgreSQL can now use the extended statistics system (accessed via CREATE STATISTICS) to create improved plans for queries with OR clauses and IN/ANY lookups over lists. PostgreSQL 13 release note

Extended statistics

This is going to be a useful tool to have in the box for solving individual queries that are running slowly. Historically, database developers were very much at the mercy of the PostgreSQL optimiser; whilst it’s easy to view execution plans, it was often difficult to prevent the optimiser from making a “mistake” (for example, in underestimating the number of rows returned from an operation). And these can lead to slow running queries that are difficult to tune.

Extended statistics allow database developers and administrators to provide their own statistics, to complement the statistics built offline and served to the optimiser. For example, you can indicate which columns in a table are highly correlated (that is, generally have a 1–1 mapping, such as an employee id and their employee title id).

postgresql database data-science cloud developer

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Applications Of Data Science On 3D Imagery Data

The agenda of the talk included an introduction to 3D data, its applications and case studies, 3D data alignment and more.

50 Data Science Jobs That Opened Just Last Week

Data Science and Analytics market evolves to adapt to the constantly changing economic and business environments. Our latest survey report suggests that as the overall Data Science and Analytics market evolves to adapt to the constantly changing economic and business environments, data scientists and AI practitioners should be aware of the skills and tools that the broader community is working on. A good grip in these skills will further help data science enthusiasts to get the best jobs that various industries in their data science functions are offering.

Multi-cloud Spending: 8 Tips To Lower Cost

Mismanagement of multi-cloud expense costs an arm and leg to business and its management has become a major pain point. Here we break down some crucial tips to take some of the management challenges off your plate and help you optimize your cloud spend.

Data Science Course in Dallas

Become a data analysis expert using the R programming language in this [data science]( "data science") certification training in Dallas, TX. You will master data...

32 Data Sets to Uplift your Skills in Data Science | Data Sets

Need a data set to practice with? Data Science Dojo has created an archive of 32 data sets for you to use to practice and improve your skills as a data scientist.