Ideas for improving AATAMS' performance about aatams HOT 5 CLOSED

An outage which just happened was possibly (likely) caused by materialised view refresh.

from aatams.

I'm not sure if I've made it clear enough in the past w.r.t. to the seriousness of this issue.

There are two main problems occurring as a result of the issues list above:

1. data consistency - by having "join" tables and materialised views, the chances of having inconsistent data goes up immensely, and so this is related to the work @xhoenner is doing currently with the reconciliation;
2. performance, reliability - the materialised view refresh is putting an enormous load on the system every night, and is in fact making the app unavailable while it is happening. With continued growth of the DB, this outage period is only going to increase, possibly to the point of where it starts overlapping with business hours.

from aatams.

I just prototyped (1) from above (loading detections using COPY).

I was able to load ~3.5M detections in around 11s (~300k records/s) - this is around 1500x as fast as the web app currently loads detections (including all the validation and what not).

FWIW, we could re-load every uploaded detection (~90M) in around 5 minutes this way :-)

from aatams.

To be fairer, I added a compound index and a primary key constraint. It takes now ~40s to load 3.5M records - still fast enough.

Table definition:

-- Table: raw_det

-- DROP TABLE raw_det;

CREATE TABLE raw_det
(
  "timestamp" timestamp with time zone,
  receiver_name text,
  transmitter_id text,
  id bigint NOT NULL,
  CONSTRAINT raw_det_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE raw_det
  OWNER TO aatams;

-- Index: pagination_index

-- DROP INDEX pagination_index;

CREATE INDEX pagination_index
  ON raw_det
  USING btree
  ("timestamp", receiver_name COLLATE pg_catalog."default", transmitter_id COLLATE pg_catalog."default");

COPY query (including some awk magic to add an id column, we would need to do a similar thing to add a receiver_download_file_id column):

COPY raw_det
FROM PROGRAM 'awk -F, ''{$(NF+1)=++i;}1'' OFS=, /tmp/big_dets.csv'
CSV HEADER DELIMITER ',';

from aatams.

Note that in 1), the file would have to exist on the same server as the DB. Possibly, rather than storing the file on disk, we could store it in the DB (as a BLOB or some such), and COPY from that.

This would also simplify deployment and backups somewhat (because only the DB has state, not DB + filesystem).

from aatams.