2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Giuseppe's PlanetPostgreSQL3 / BRIN for PostGIS: my story at the Code Sprint 2016 in Paris
2ndQuadrant Press

BRIN for PostGIS: my story at the Code Sprint 2016 in Paris

March 2, 2016/0 Comments/in Giuseppe's PlanetPostgreSQL /by 2ndQuadrant Press

Last week I was pleased to attend Code Sprint 2016 in Paris, organised by the OSGeo Foundation: it has been an opportunity for many GIS users from all over the world to get together, discuss and actively work on many open tasks of some of the major open source frameworks on GIS. PostGIS was obviously one of the projects involved in the Code Sprint.

25114331182_92bc5ebab3_z

Some background information first. A few months ago I started to work on adding BRIN support to the geospatial datatype available in PostGIS: I was able to develop a working prototype on my own.

However, it was thanks to this Code Sprint that I was finally able to complete the job. Code Sprint was the opportunity for me to know Julien and Ronan from Dalibo, who shared the same goals. We worked as a team and shared our skills and knowledge for the whole duration of the event, together we were able to produce a first version of a patch for PostGIS 2.3. The main features introduced with this patch are the definitions of two operator classes that include some operators that can benefit from block range indexing (BRIN), both for 2D and n-D cases. For now, just the “inclusion support” has been implemented: this means that just inclusion and/or intersection operations can benefit from BRINs.

Anyway, the final results surprised all of us: indexing is robust and promising. A simple test made on real geospatial data from the OpenStreet Map dataset has shown that BRINs, compared to GiST indexing, have:

  • execution times of the queries 5 times higher (CON);
  • indexes that are 1000 times smaller and that can be created 100 times faster (PRO).

This feature could be particularly useful in big geospatial data contexts, and this was the original motivation that prompted me to include BRIN support into PostGIS: for instance, start to use relational databases to manage LiDAR surveys. I have already talked about this case, and have also made a presentation at the FOSS4G.EU 2015 conference where I showed the limitation of PostgreSQL and its indexes with point clouds.

paris

On a personal note, I am enthusiastic for the obtained results, but, most importantly, I am extremely happy for the priceless experience. It’s been the first time I’ve taken part to a code sprint, and the atmosphere was really exciting, collaborative and constructive. Code Sprint gave me the opportunity to learn and improve. Moreover, it has been a good opportunity for me to share my knowledge of PostgreSQL with Ronan and Julien. Even though this is the first version of the patch, it is already robust. I must say that our joint everyday experience with PostgreSQL VLDBs allowed us to achieve our goal. I feel it is important that PostgreSQL developers and professional users – like us from 2ndQuadrant, for example – actively participate to the development of PostGIS.

Obviusly, I’d like to thank Julien and Ronan for their work on this patch. But I also want to thank both Àlvaro Herrera (that introduced the BRINs in PostgreSQL) and Emre Hesegeli (that has worked on BRINs too, in particular on the documentation) for the insightful conversations and email/chat exchanges about the topic. Many thanks to Vincent Picavet from OSLandia who invited me to attend, as well as all the staff that organised the event in Paris. “Grazie” also to 2ndQuadrant Italia that is self funding my research and development in this field.

Finally, I am sure you are really curious now to try this new feature! Be patient … we are polishing the patch, and then we will propose it to the PostGIS team (that has already seen some preliminary results in the Code Sprint in Paris). Our goal is to have it included in PostGIS 2.3.

I have already submitted this talk to the 5432…meetus! PostgreSQL conference that will be held in Milan at the end of June and the FOSS4G.NA geospatial conference that will be in Raleigh (North Carolina) in May! I hope I will be able to speak about how BRIN indexing performs with geospatial data in these occasions.

Tags: block range index, BRIN, codesprint2016, geospatial database, gis, indexes, lidar, paris, postgis, postgis 2.3, postgres, PostgreSQL
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Extended the call for papers for “5432…MeetUs!” And Barman 1.6.0 is out!
Scroll to top
×