PostgreSQL 12: Implementing K-Nearest Neighbor Space Partitioned Generalized Search Tree Indexes
K-nearest neighbor answers the question of “What is the closest match?”. PostgreSQL 12 can answer this question, and use indexes while doing it.
Posts
PostgreSQL 12: Foreign Keys and Partitioned Tables
/7 Comments/in 2ndQuadrant, Alvaro's PlanetPostgreSQL, PostgreSQL /by Álvaro HerreraNow that PostgreSQL 12 is out, we consider foreign keys to be fully compatible with partitioned tables. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly. Why do I point this out? Two reasons: first, when partitioned tables were first introduced in PostgreSQL 10, they […]
Replication configuration changes in PostgreSQL 12
/0 Comments/in Ian's PlanetPostgreSQL, repmgr /by Ian BarwickHistorically, PostgreSQL’s replication configuration has been managed via a configuration parameters stored in a dedicated configuration file, recovery.conf, which has been present in PostgreSQL since the introduction of archive recovery in PostgreSQL 8.0. One of the major changes in PostgreSQL 12, co-authored by 2ndQuadrant is the replacement of recovery.conf and the conversion of recovery.conf parameters […]
Webinar: New Features in Postgres 12 [Follow up]
/0 Comments/in 2ndQuadrant, PostgreSQL, Webinars /by Bilal IbrarThe new PostgreSQL 12 release is just around the corner and by popular demand, the team at 2ndQuadrant hosted “New Features in Postgres 12″ webinar. The 1 hour + long session, conducted by Peter Eisentraut (Core Team Member of the PostgreSQL Project), gave an in-depth preview of everything new in PostgreSQL 12, improvements to partitioning, generated […]
PostgreSQL 12: A Few Special-Case Performance Enhancements
/2 Comments/in 2ndQuadrant, John’s PlanetPostgreSQL /by John NaylorWith every new release of PostgreSQL, there are a range of performance enhancements. Some are system-wide and affect every user, but most are highly specific to a certain use case. In this post, I am going to briefly highlight three improvements in PG12 that speed up certain operations. 1. Minimal decompression of TOAST values TOAST […]
PostgreSQL 12: Partitioning is now faster
/5 Comments/in 2ndQuadrant, David's PlanetPostgreSQL /by David RowleyTable partitioning has been evolving since the feature was added to PostgreSQL in version 10. Version 11 saw some vast improvements, as I mentioned in a previous blog post. During the PostgreSQL 12 development cycle, there was a big focus on scaling partitioning to make it not only perform better, but perform better with a […]
PG Phriday: Postgres 12 Revs up Vacuum
/2 Comments/in Shaun's PlanetPostgreSQL /by Shaun ThomasAny long-time user of Postgres is likely familiar with VACUUM, the process that ensures old data tuples are identified and reused to prevent unchecked database bloat. This critical element of maintenance has slowly, but surely, undergone incremental enhancements with each subsequent release. Postgres 12 is no different in this regard. In fact, there are two […]
Partitioning enhancements in PostgreSQL 12
/3 Comments/in 2ndQuadrant, Kirk’s PlanetPostgreSQL /by Kirk RoybalDeclarative partitioning got some attention in the PostgreSQL 12 release, with some very handy features. There has been some pretty dramatic improvement in partition selection (especially when selecting from a few partitions out of a large set), referential integrity improvements, and introspection. In this article, we’re going to tackle the referential integrity improvement first. This […]
Generated columns in PostgreSQL 12
/4 Comments/in 2ndQuadrant, Kirk’s PlanetPostgreSQL /by Kirk RoybalComputed columns in PostgreSQL 12 are here, but still have a ways to go.
Optimizing storage of small tables in PostgreSQL 12
/0 Comments/in John’s PlanetPostgreSQL /by John NaylorThe problem If your database has a large number of small tables, you likely have a lot of wasted space. To demonstrate this, let’s create a table with a single record: create table foo (str text); insert into foo values (‘a’); VACUUM foo; Now let’s find out the path of the file containing our data, […]
