For the last couple years I have been testing out the ever-improving support for parallel query processing in PostgreSQL, particularly in conjunction with the PostGIS spatial extension. Spatial queries tend to be CPU-bound, so applying parallel processing is frequently a big win for us.
Initially, the results were pretty bad.
With PostgreSQL 10, it was possible to force some parallel queries by jimmying with global cost parameters, but nothing would execute in parallel out of the box.
With PostgreSQL 11, we got support for parallel aggregates, and those tended to parallelize in PostGIS right out of the box. However, parallel scans still required some manual alterations to PostGIS function costs, and parallel joins were basically impossible to force no matter what knobs you turned.
With PostgreSQL 12 and PostGIS 3, all that has changed. All standard query types now readily parallelize using our default costings. That means parallel execution of:
Parallel sequence scans,
Parallel aggregates, and
Parallel joins!!
TL;DR:
PostgreSQL 12 and PostGIS 3 have finally cracked the parallel spatial query execution problem, and all major queries execute in parallel without extraordinary interventions.
What Changed
With PostgreSQL 11, most parallelization worked, but only at much higher function costs than we could apply to PostGIS functions. With higher PostGIS function costs, other parts of PostGIS stopped working, so we were stuck in a Catch-22: improve costing and break common queries, or leave things working with non-parallel behaviour.
For PostgreSQL 12, the core team (in particular Tom Lane) provided us with a sophisticated new way to add spatial index functionality to our key functions. With that improvement in place, we were able to globally increase our function costs without breaking existing queries. That in turn has signalled the parallel query planning algorithms in PostgreSQL to parallelize spatial queries more aggressively.
Setup
In order to run these tests yourself, you will need:
PostgreSQL 12
PostGIS 3.0
You’ll also need a multi-core computer to see actual performance changes. I used a 4-core desktop for my tests, so I could expect 4x improvements at best.
We will work with the default configuration parameters and just mess with the max_parallel_workers_per_gather at run-time to turn parallelism on and off for comparison purposes.
When max_parallel_workers_per_gather is set to 0, parallel plans are not an option.
max_parallel_workers_per_gather sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Setting this value to 0 disables parallel query execution. Default 2.
Before running tests, make sure you have a handle on what your parameters are set to: I frequently found I accidentally tested with max_parallel_workers set to 1, which will result in two processes working: the leader process (which does real work when it is not coordinating) and one worker.
Boom! We get a 3-worker parallel plan and execution about 3x faster than the sequential plan. This query did not work out-of-the-box with PostgreSQL 11.
Right out of the box, we get a parallel plan! No amount of begging and pleading would get a parallel plan in PostgreSQL 11
Gather
(cost=1000.28..837378459.28 rows=5322553884 width=2579)
Workers Planned: 4
-> Nested Loop
(cost=0.28..305122070.88 rows=1330638471 width=2579)
-> Parallel Seq Scan on pts_100 pts
(cost=0.00..75328.50 rows=1738350 width=40)
-> Index Scan using pd_geom_idx on pd
(cost=0.28..175.41 rows=7 width=2539)
Index Cond: (geom && pts.geom)
Filter: st_intersects(geom, pts.geom)
The only quirk in this plan is that the nested loop join is being driven by the pts_100 table, which has 10 times the number of records as the pd table.
The plan for a query against the pt_10 table also returns a parallel plan, but with pd as the driving table.
Last month I was invited to give a keynote talk at FOSS4G North America in San Diego. I have been speaking about open source economics at FOSS4G conferences more-or-less every two years, since 2009, and I look forward to revisting the topic regularly: the topic is every-changing, just like the technology.
In 2009, the central pivot of thought about open source in the economy was professional open source firms in the Red Hat model. Since then we’ve taken a ride through a VC-backed “open core” bubble and are now grappling with an environment where the major cloud platforms are absorbing most of the value of open source while contributing back proportionally quite little.
What will the next two years hold? I dunno! But I have increasingly little faith that a good answer will emerge organically via market forces.
Every once in a while, someone comes to me and says:
Sure, it’s handy to use ST_AsGeoJSON to convert a geometry into a JSON equivalent, but all the web clients out there like to receive full GeoJSON Features and I end up writing boilerplate to convert database rows into GeoJSON. Also, the only solution I can find on the web is scary and complex. Why don’t you have a row_to_geojson function?
And the answer (still) is that working with rows is fiddly and I don’t really feel like it.
However! It turns out that, with the tools for JSON manipulation already in PostgreSQL and a little scripting it’s possible to make a passable function to do the work.
That’s actually all the information we need to create a GeoJSON feature, it just needs to be re-arranged. So let’s make a little utility function to re-arrange it.
CREATEORREPLACEFUNCTIONrowjsonb_to_geojson(rowjsonbJSONB,geom_columnTEXTDEFAULT'geom')RETURNSTEXTAS$$DECLAREjson_propsjsonb;json_geomjsonb;json_typejsonb;BEGINIFNOTrowjsonb?geom_columnTHENRAISEEXCEPTION'geometry column ''%'' is missing',geom_column;ENDIF;json_geom:=ST_AsGeoJSON((rowjsonb->>geom_column)::geometry)::jsonb;json_geom:=jsonb_build_object('geometry',json_geom);json_props:=jsonb_build_object('properties',rowjsonb-geom_column);json_type:=jsonb_build_object('type','Feature');return(json_type||json_geom||json_props)::text;END;$$LANGUAGE'plpgsql'IMMUTABLESTRICT;
Voila! Now we can turn any relation into a proper GeoJSON “Feature” with just one(ish) function call.
You might be wondering why I made my function take in a jsonb input instead of a record, for a perfect row_to_geojson analogue to row_to_json. The answer is, the PL/PgSQL planner caches types, including the materialized types of the record parameter, on the first evaluation, which makes it impossible to use the same function for multiple tables. This is “too bad (tm)” but fortunately it is an easy workaround to just change the input to jsonb using to_json() before calling our function.
TL;DR: There are some changes in PostgresSQL 12 that FDW authors might be surprised by! Super technical, not suitable for ordinary humans.
OK, so I decided to update my two favourite extension projects (pgsql-http and pgsql-ogr-fdw) yesterday to support PostgreSQL 12 (which is the version currently under development likely to be released in the fall).
Fixing up pgsql-http was pretty easy, involving just one internal function signature change.
Fixing up pgsql-ogr-fdw involved some time in the debugger wondering what had changed.
Your Slot is Empty
When processing an FDW insert/update/delete, your code is expected to take a TupleTableSlot as input and use the data in that slot to apply the insert/update/delete operation to your backend data store, whatever that may be (OGR in my case). The data lived in the tts_values array, and the null flags in tts_isnull.
In PostgreSQL 12, the slot arrives at your ExecInsert/ExecUpdate/ExecDelete callback function empty! The tts_values array is populated with Datum values of 0, yet the tts_isnull array is full of true values. There’s no data to pass back to the FDW source.
The short-term fix is just to force the slot to populate by calling slot_getallattrs, and then go on with your usual work. That’s what I did. A more future-proof way would be to use slot_getattr and only retrieve the attributes you need (assuming you don’t just need them all).
Your VarLena might have a Short Header
Varlena types are the variable size types, like text, bytea, and varchar. Varlena types store their length and some extra information in a header. The header is potentially either 4 bytes or 1 byte long. Practically it is almost always a 4 byte header. If you call the standard VARSIZE and VARDATA macros on a varlena, the macros assume a 4 byte header.
The assumption has always held (for me), but not any more!
I found that as of PostgreSQL 12, I’m getting back varchar data with a 1-byte header! Surprise!
The fix is to stop assuming a 4-byte header. If you want the size of the varlena data area, less the header, use VARSIZE_ANY_EXHDR instead of VARSIZE() - VARHDRSZ. If you want a pointer into the data area, use VARDATA_ANY instead of VARDATA. The “any” macros first test the header type, and then apply the appropriate macro.
I have no idea what commit caused short varlena headers to make a comeback, but it was fun figuring out what the heck was going on.
Map projection is a core feature of any spatial database, taking coordinates from one coordinate system and converting them to another, and PostGIS has depended on the Proj library for coordinate reprojection support for many years.
For most of those years, the Proj library has been extremely slow moving. New projection systems might be added from time to time, and some bugs fixed, but in general it was easy to ignore. How slow was development? So slow that the version number migrated into the name, and everyone just called it “Proj4”.
No more.
Starting a couple years ago, new developers started migrating into the project, and the pace of development picked up. Proj 5 in 2018 dramatically improved the plumbing in the difficult area of geodetic transformation, and promised to begin changing the API. Only a year later, here is Proj 6, with yet more huge infrastructural improvements, and the new API.
Some of this new work was funded via the GDALBarn project, so thanks go out to those sponsors who invested in this incredibly foundational library and GDAL maintainer Even Roualt.
For PostGIS that means we have to accomodate ourselves to the new API. Doing so not only makes it easier to track future releases, but gains us access to the fancy new plumbing in Proj.
For example, Proj 6 provides:
Late-binding coordinate operation capabilities, that takes metadata such as
area of use and accuracy into account… This can avoid in a
number of situations the past requirement of using WGS84 as a pivot system,
which could cause unneeded accuracy loss.
Or, put another way: more accurate results for reprojections that involve datum shifts.
Here’s a simple example, converting from an old NAD27/NGVD29 3D coordinate with height in feet, to a new NAD83/NAVD88 coordinate with height in metres.
Note that the height in NGVD29 is 100 feet, if converted directly to meters, it would be 30.48 metres. The transformed point is:
POINT Z (-100.0004058 40.000005894 30.748549546)
Hey look! The elevation is slightly higher! That’s because in addition to being run through a horizontal NAD27/NAD83 grid shift, the point has also been run through a vertical shift grid as well. The result is a more correct interpretation of the old height measurement in the new vertical system.
Astute PostGIS users will have long noted that PostGIS contains three sources of truth for coordinate references systems (CRS).
Within the spatial_ref_sys table there are columns:
The authname, authsrid that can be used, if you have an authority database, to lookup an authsrid and get a CRS. Well, Proj 6 now ships with such a database. So there’s one source of truth.
The srtext, a string representation of a CRS, in a standard ISO format. That’s two sources.
The proj4text, the old Proj string for the CRS. Until Proj 6, this was the only form of definition that the Proj library could consume, and hence the only source of truth that mattered to PostGIS. Now, it’s a third source of truth.
Knowing this, when you ask PostGIS to transform to an SRID, what will it do?
If there are non-NULL values in authname and authsrid ask Proj to return a CRS based on those entries.
If Proj fails, and there is a non-NULL srtext ask Proj to build a CRS using that text.
If Proj still fails, and there is a non-NULL proj4text ask Proj to build a CRS using that text.
In general, the best transforms will come by having Proj look-up the CRS in its own database, because then it can apply all the power of “late binding” to ensure the best transformation for each geometry. Hence we bias in favour of Proj lookups, then the quite detailed WKT format, and finally the old Proj format.
