PostGIS and GeoServer - alternative solution to serve combined spatial and time tables with high resolution data

Question

This is the follow up of this other question of mine, although you don't really need to see it to understand the problem.

I am trying to figure out what would be the best (maybe most usual) way of serving large tables that have both a spatial and a time dimension.

First of all: what I have

I have two tables (not the actual that I have, but you'll get the point):

1. precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).

Column  |         Type         
--------+----------------------
 id     | varchar
 date   | date
 start  | time
 end    | time
 freq   | varchar
 prec   | float(4)
(2,481,069 rows)

2. areas: store multipolygon areas for all Europe down to municipality resolution.

Column  |         Type         
--------+----------------------
 id     | varchar
 geom   | geometry
(162,573 rows)

What I need

I am developing an app that lets the user select a day, or a time in one day and shows in a (OpenLayers) map the areas with the amount of precipitation for that selected time.

I am using GeoServer to serve the data with CQL_FILTERs to pick the selected time (see other sections to understand how the data is built).

What I was doing

I created one table view for daily frequency precipitation and one for hourly frequency precipitation, like so:

create or replace view daily_prec
as select * from
    (select
        id,
        "date",
        "start",
        "end",
        sum(prec) as prec,
    from precipitaion
    where freq = 'daily'
    group by "date", "start", "end", id) as prec,
    areas.geom
where prec.id = areas.id;

For the daily_prec I have 4,634,430 rows, and much more for the hourly_prec.

Serving these two views as WMS layers through GeoServer was slow, as all getMap and other requests took more than 1 sec to arrive, so...

...what I am doing now

I created materialized views so that I could create index and spatial index on the tables.

Now, the requests arrive much faster, and I am happy with the overall performance.

The problems are:

1. they require a lot of disk space (a single materialized view table can take up 60GB)
2. they are very slow to be created (they take about 40 min).

Is there a better approach?

Given that my data needs frequent updates (also the historical data might change as the way it is calculated might vary because we are in a testing phase), and preparing tiles probably is not the way to go (right now at least), would you recommend a different approach for such situation?

Would you recommend using a different type of database and/or service?

I am using PosgreSQL 10 and PostGIS 2.5.

Answer 1

I would make sure my tables were indexed by dates and times and check they are used by the query planner. Running GeoServer with logging turned upto GEOTOOLS-DEV will show you the exact query being sent to the database. Make sure that GeoServer knows that there is a unique "primary key" in your view as that being missing can prevent some speed ups from being applied.

Then I would try to make sure that reading from the disk was as fast as possible by clustering on date and partitioning the table.

Partitioning can provide several benefits:

• Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.

• When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table.

• Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. Doing ALTER TABLE DETACH PARTITION or dropping an individual partition using DROP TABLE is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.

• Seldom-used data can be migrated to cheaper and slower storage media.

• The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

Finally, I would speed up map panning and zooming by caching tiles both in the browser and at the server (even if only for a few minutes before expiring or overwriting in a LRU cache) - GeoServer/GeoWebCache will manage this best if you use the TIME dimension parameter rather than an ad hoc CQL query. You need to specify TIME as your filter and .* as the regular expression.