We are trying to optimize out the performance on a database with these numbers and query requisites:
- 200-400k network segments identified by a unique ID
- 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
- the segment state will change every 3 minutes, H24, 7/7
- 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.
- 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:
- It is partitioned on field "day" with partition_manager (
pg_partman). Each partition is one month
- 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:
- 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:
- A limited number of rows for each month partition table
- Good performance when joining the static data of the segment (i.e. the geometry)
- The little redundancy of the current release is very good to respond at real-time requests
- Space-safer: only ~12 GB of stored data for each partition (1 month) -
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
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?