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
