Tom Swartz
Tom Swartz
By design, the out of the box configuration for PostgreSQL is defined to be a "Jack of All Trades, Master of None". The default configuration for PostgreSQL is fairly painstakingly chosen to ensure that it will run on every environment it is installed, meeting the lowest common denominator resources across most platforms. Because of this, it's always recommended that one of the first actions performed once an install of PostgreSQL is completed, would be to tune and configure some high-level se...
Read MoreKat Batuigas
Kat Batuigas
In our last blog post about pg_featureserv , we showed how it can publish spatial datasets and access them via simple web requests. In this post, we’re going to discuss how publishing PostgreSQL / PostGIS functions via pg_featureserv provides even more flexible access to your data. A powerful feature of PostgreSQL is the ability to create user-defined functions . Functions let you wrap complex logic within a simple interface: passing in arguments, and getting back a set of values as output...
Read MorePaul Ramsey
Paul Ramsey
In our previous posting on tile serving, we showed off how pg_tileserv can use database functions to generate tiles by querying existing tables with user parameters. We can also use functions to build geometry on the fly without input from tables. For example, hexagons! Hexagons are a favourite input for visualizations, because they have a nice symmetric shape and provide equal areas for summarization. A filling of the plane with hexagons is a hexagonal grid. Curiously, it's possible to add...
Read MorePaul Ramsey
Paul Ramsey
In my previous posting on tile serving , I demonstrated how pg_tileserv can publish spatial tables as dynamic vector tiles. Dynamic tiles , available as part of Crunchy Spatial , are generated in the database, on-the-fly, in response to a tile request. This allows for two kinds of dynamism to show up in the end user applications: • Changes in the underlying data show up in the generated tiles. • Changes in the tile request can show up in the generated tiles. Changes in the underlying da...
Read MoreMartin Davis
Martin Davis
In addition to viewing PostGIS spatial data as vector tiles using pg_tileserv , it is often necessary to access data features directly. This supports use cases such as: • display features at a point or in an area of interest • query features using spatial and/or attribute filters • retrieve features for use in a web application (for tabular or map display) • download spatial data for use in applications display features at a point or in an area of interest query features using spatial and/or...
Read MorePaul Ramsey
Paul Ramsey
Beautiful, responsive maps are best built using vector tiles , and PostgreSQL with PostGIS can produce vector tiles on-the-fly . However, to use vector tiles in a beautiful, responsive map, you need to be able to access those tiles over the HTTP web protocol, and you need to be able to request them using a standard XYZ tiled map URL . It's possible to write your own HTTP wrapper for the PostGIS vector tile generator, but you don't need to! pg_tileserv is a lightweight vector tile server s...
Read MorePaul Ramsey
Paul Ramsey
Let's put that on a map! PostGIS has so many cool features that it is possible to do full GIS analyses without ever leaving the SQL language, but... at the end of the day, you want to get those results, show that data, on the map. The Crunchy Data geospatial team has been thinking about how to bring PostGIS to the web, and we established some basic principles: • Use the database, as much as possible. • The database already has a user model and security model. • The database can already generat...
Read MoreKeith Fiske
Keith Fiske
PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+. Note these instructions do not cover migrating to PG10 since some key f...
Read MoreAndrew L'Ecuyer
Andrew L'Ecuyer
Crunchy Data recently released its latest version of the open source PostgreSQL Operator for Kubernetes , version 4.2. Among the various enhancements included within this release is support for Synchronous Replication within deployed PostgreSQL clusters. As discussed in our prior post , the PostgreSQL Operator 4.2 release introduces distributed consensus based high-availability . For workloads that are sensitive to transaction loss, the Crunchy PostgreSQL Operator supports PostgreSQL synchro...
Read MoreJonathan S. Katz
Jonathan S. Katz
It is important (understatement) that you take regularly scheduled backups of your PostgreSQL system as well as manage how many backups you have, which is known as "backup retention." These best practices ensure that you always have a recent backup of your database system to recover from in the event of a disaster (or use to clone a new copy of your database ) and that you don't run out of storage on your backup device or blow up your object storage bill (true story from a previous life, I ha...
Read More