0

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)
  1. 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.

  • 1
    The numbers don't match, maybe there are duplicates entries in areas? It sounds like a cross join is made where a 1:1 (or M:1) relation is expected. In the query you provided, there is no need for a Group By nor for a Sum, as all fields are either in the group by or in the filtering where clause. That being said, having the proper indexes and eventually tuning Postgres configuration are likely the solution. – JGH Sep 11 at 12:00
  • @JGH thanks for pointing that out. Actually, when I write "(not the actual that I have, but you'll get the point)" I also imply that some info (like the resulting number of rows) might not correspond to the example but to my actual tables, and yes, I have a situation in which indeed I need a GROUP BY statement (although I thought having the sum(prec) as prec column in the query would have required a GROUP BY statement). Anyway, thanks for confirming the solution, I appreciate your help. – umbe1987 Sep 11 at 12:14
  • I believe downvoting is fine, but it would be fair to explain why with a short comment in my humble opinion. I am always available to accept the reason (if at least there is one). – umbe1987 Sep 11 at 12:52
  • @JGH you were correct, there are duplicates in the areas! Thank you so much for making me discover this issue! – umbe1987 Sep 11 at 13:06
2

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.

| improve this answer | |
  • Thanks (again) Ian! I really appreciate all your help. I am surely try all your recommendations. One thing: as far as I am concerned, PostgreSQL views cannot have primary keys, or am I missing something? Another thing: with your recommendations I think I can stick with traditional views, is this what you are suggesting (I really hope so as materialized views are making my small SSD bleeding...)? – umbe1987 Sep 11 at 9:45
  • 1
    That was why I put quotes around primary key - you can tell GeoServer to treat id as a primary key if it is unique otherwise you can modify your view to make it unique – Ian Turton Sep 11 at 9:47
  • 1
    ok this is great news. The ids are not unique as they are basically the unique identifiers for my features but they do repeat in time so in my view I have lots of repetition. Will it be possible to specify a combination of (e.g.) id, date, start as "primary key" for GeoServer? – umbe1987 Sep 11 at 9:52
  • Anyway, table partition is definitely the way to go! – umbe1987 Sep 11 at 9:54
  • 1
    that's right - the regex can be used to limit what GWC caches but in this case you want everything – Ian Turton Sep 15 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.