We are trying to optimize out the performance on a database with these numbers and query requisites:

  1. 200-400k network segments identified by a unique ID
  2. each network segment have a state with a limited number of dynamic attributes (i.e. an average speed). A single state of dynamic attributes could be stored in 8 bytes
  3. the segment state will change every 3 minutes, H24, 7/7
  4. Is it possible to query for the state of a group of segments (sometimes all segments) in a particular date range or just the actual situation.
  5. It could be requested a spatial query to find all segments "around me" in a particular date (normally "at now")

With these requisites, we arrived at this solution (with a strong drawback, explained below).

[A] TABLE main_segments_history( 
      id_segment integer NOT NULL,
      day date NOT NULL,    
      day_slices bigint[],
      CONSTRAINT main_segments_history_pk PRIMARY KEY (id_segment,day)

[B] TABLE current_segment_release_state(
      id_segment integer NOT NULL,
      release_date timestamptz,
      ... all other attributes ...
      CONSTRAINT currsegm_release_state_pk PRIMARY KEY (id_segment,release_date)

Explaining the [A] table:

  1. It is partitioned on field "day" with partition_manager (pg_partman). Each partition is one month
  2. The day_slices array is a one-dimensional array of 480 elements, representing the granularity of each 3-minute-slice of the full day

Explaining the [B] table:

  1. It's just the current release state for each segment

There is a back-end process that elaborate the network.
It inserts or updates the states of each segment, every 3 minutes.
In other words this process will insert new rows on day start and will update the inner array every 3 minutes.

The advantages of this solution:

  1. A limited number of rows for each month partition table
  2. Good performance when joining the static data of the segment (i.e. the geometry)
  3. The little redundancy of the current release is very good to respond at real-time requests
  4. Space-safer: only ~12 GB of stored data for each partition (1 month) -

The drawbacks:

  1. constraint_exclusion. When querying on date range there is a need of using that feature/parameter of PostgreSQL. That is using constant values in a precompiled query that spans over multiple partitions. Example:

    constraint exclusion OK (will only search on February and March 2017):

    SELECT * FROM main_segments_history 
             WHERE day BETWEEN '2017-01-01' AND '2017-02-03'

    constraint exclusion KO (will search on all partition tables):

    SELECT * FROM main_segments_history m 
             JOIN sometable s ON s.id=m.id_segment 
             WHERE day BETWEEN s.day_from AND s.day_to
  2. The UPDATE is evil.

We turned OFF the autovacuum for performance issues, doing it with a batch every night.
Consider that in this way we have almost between 90M and 190M UPDATES each day, this is also the number of rows completely rewritten by postgreSQL (as you certainly know an UPDATE will flag the row deleted and then a NEW row is inserted) every day.

More, the UPDATE is a great time-consuming operation creating often a delay on the writes.

We first investigate at the possibility of using a LINK-TO-DATA design, using for example a table as a container of segments id with a data_id to a BIG TABLE of segment states, but we discarded when we just count the number of rows each month to handle with: ~2.880.000.000 with an amount of space of ~3GB a day. Not so good.

What do you think about? Do you have any solution to optimize this system?

  • You haven't shown the UPDATE, which is the thing you say needs improving. Is it one command every 3 minutes, or 200k-400k commands every 3 minutes? Also, turning off autovac will probably makes things worse, not better. What problems was it causing?
    – jjanes
    Commented May 17, 2017 at 18:27
  • we are making 200k-400k UPDATEs every 3 minutes, something like: UPDATE main_segments_history SET day_slices[$current_index]= $value WHERE [...]. The issue we were facing with the AUTOVACUUM enabled was the degradation of performance in completing the real-time tasks we need.
    – Flavio
    Commented May 18, 2017 at 7:37

1 Answer 1


The only way to avoid updates to your tables is just not to do them. Given the fact that you're just logging data, I would suggest a daily table, where you store the data for one day. Instead of having 190 M updates/day, you may have 190 M (smaller) inserts/day; and as many updates as there are segments.

 CREATE TABLE main_segments_history
       id_segment integer NOT NULL,
       day date NOT NULL,    
       day_slices bigint[],
       CONSTRAINT main_segments_history_pk PRIMARY KEY (id_segment, day)
 ) ;

 CREATE TABLE dayly_segments
      id_segment integer NOT NULL,
      day date NOT NULL,
      id_slice integer NOT NULL,
      slice bigint,
      PRIMARY KEY (id_segment, day, id_slice)
 ) ;

This would simulate the data for one day (and 200 segments; instead of the millions you might have):

     (id_segment, day, id_slice, slice)
     id_segment, '2017-01-01', id_slice, (random()*1e7)::bigint
     generate_series (1, 200) AS s1(id_segment)
     CROSS JOIN generate_series (1, 20*24) AS s2(id_slice) ;

I assume you have some time with low activity, when you run the vacuuming processes. At that time, you can move data from the dayly_segments table to the main_segments_history, and put all the data as an array. This way, you're never updating main_segments_history.

This is, basically, what you would do:

 -- Move segments from dayly_segments to main_segment_history
     (id_segment, day, day_slices)
     id_segment, day, 
     (SELECT array_agg(slice) 
      FROM (SELECT slice 
              FROM dayly_segments s1 
             WHERE s1.id_segment = s0.id_segment AND s1.day = s0.day 
          ORDER BY id_slice) AS s2)
         id_segment, day 
         dayly_segments s0
         day = '2017-01-01'
     ) AS s0 ;

 -- Delete them from original
     day = '2017-01-01' ;

 -- At this point, you should also...
 VACUUM dayly_segments ;

dbfiddle here


  1. You never "miss" any $current_value. That is, there are no holes in your arrays.
  2. id_slice can be assigned the result of any increasing sequence. You could aso use, instead of an integer value, just a time value.
  • thanks for your reply! Your solution is good and with no fragmentation until the end of the day. The size overhead is at most 15 GB for the "daily" table. However, I think we could also avoid the DELETE/VACUUM in the "daily" table just using a pg_partman on daily table. In this way the "history" process can COPY all data from "yesterday" while the new release of the day is created in the next partitioned table.
    – Flavio
    Commented May 23, 2017 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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