timescaledb: Bulk insert exhausts all memory

Question

Summary

I am attempting to insert data into a timescaledb hypertable in bulk. Regardless of what I try, the memory usage grows gradually until the server process is killed due to a lack of memory. I have observed this with datasets as small as 1.6 million rows on a server where 128 GB of RAM is available to postgres/timescaledb, so I must be doing something wrong. Doing exactly the same inserts into a table that is not a hypertable works just fine, so the problem must be related to timescaledb.

What I am trying to do

The table I wish to insert into is defined as follows:

CREATE TABLE test (
    gid BIGINT NOT NULL,
    location_ GEOGRAPHY(POINT),
    -- about 15 other VARCHAR and BIGINT columns omitted for brevity
    far_end_gid BIGINT NOT NULL,
    day_partition_key DATE NOT NULL,
    PRIMARY KEY (gid, far_end_gid, day_partition_key)
);
SELECT create_hypertable(
    'test', 'day_partition_key', chunk_time_interval => INTERVAL '1 day');

The data to be inserted is in another table in the database (a temp table created filled by a \COPY operation); this other table has the same fields as the target hypertable except that some fields require some parsing (converting strings to date etc.). The INSERT query that fails is

INSERT INTO topology_test
    SELECT
        tobigintornull(gid) AS gid_as_int,
        -- about 15 other VARCHAR and BIGINT columns omitted
        CAST(CAST(REPLACE(far_end_gid, ',', '.') AS DOUBLE PRECISION) AS BIGINT),
        CAST(tobigintornull(far_end_cell_id) AS BIGINT),
        TO_DATE(day_partition_key, 'YYYYMMDD')
    FROM test_temp_table
    WHERE
        tobigintornull(gid) is not null
        and tobigintornull(REPLACE(far_end_gid, ',', '.')) is not null
        and day_partition_key is not null
    ON CONFLICT DO NOTHING;

where the ON CONFLICT part is intended to drop duplicate primary keys silently and the function tobigintornull does what its name suggests: it converts the input into a bigint if possible, and if not, returns null (this helps drop rows that cannot be parsed). Its definition is

CREATE OR REPLACE FUNCTION tobigintornull(text) RETURNS BIGINT AS $$
DECLARE x BIGINT;
BEGIN
    x = $1::BIGINT;
    RETURN x;
EXCEPTION WHEN others THEN
    RETURN NULL;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;

The definition for DOUBLE PRECISION inputs is similar. Note that only a small fraction of rows (definitely less than 1%) are dropped by the WHERE clause.

The full data set is 593 million rows, and it is not ordered in any way. Suspecting that the lack of ordering of the input was part of the problem, I have created a subset of the data containing 1.6 million rows where all rows have the same value for day_partition_key (that subset should be perfectly ordered from timescaledb's point of view).

The problem

The problem manifests itself as postgres's memory usage increasing gradually until the full 128 GB available to the database is used (that takes about five minutes). After 100% memory usage for a minute or so, the insert query crashes. The logs show the following:

timescaledb_1  | 2021-08-23 11:56:50.727 UTC [1] LOG:  server process (PID 1231) was terminated by signal 9: Killed
timescaledb_1  | 2021-08-23 11:56:50.727 UTC [1] DETAIL:  Failed process was running: INSERT INTO test
timescaledb_1  |            SELECT

(...)

timescaledb_1  | 2021-08-23 11:56:50.727 UTC [1] LOG:  terminating any other active server processes
timescaledb_1  | 2021-08-23 11:56:50.741 UTC [1215] WARNING:  terminating connection because of crash of another server process
timescaledb_1  | 2021-08-23 11:56:50.741 UTC [1215] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
timescaledb_1  | 2021-08-23 11:56:50.741 UTC [1215] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
timescaledb_1  | 2021-08-23 11:56:50.744 UTC [1221] WARNING:  terminating connection because of crash of another server process
timescaledb_1  | 2021-08-23 11:56:50.744 UTC [1221] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
timescaledb_1  | 2021-08-23 11:56:50.744 UTC [1221] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
timescaledb_1  | 2021-08-23 11:56:50.771 UTC [1] LOG:  all server processes terminated; reinitializing
timescaledb_1  | 2021-08-23 11:56:51.371 UTC [1259] LOG:  database system was interrupted; last known up at 2021-08-23 11:50:40 UTC
timescaledb_1  | 2021-08-23 11:56:51.711 UTC [1259] LOG:  database system was not properly shut down; automatic recovery in progress
timescaledb_1  | 2021-08-23 11:56:51.720 UTC [1259] LOG:  redo starts at 25A/16567EF8
timescaledb_1  | 2021-08-23 11:56:51.788 UTC [1259] LOG:  invalid record length at 25A/165C1A30: wanted 24, got 0
timescaledb_1  | 2021-08-23 11:56:51.788 UTC [1259] LOG:  redo done at 25A/165C19D0
timescaledb_1  | 2021-08-23 11:56:51.893 UTC [1] LOG:  database system is ready to accept connections
timescaledb_1  | 2021-08-23 11:56:52.039 UTC [1265] LOG:  TimescaleDB background worker launcher connected to shared catalogs

No rows are present in the target table after the db recovers, obviously.

What I have tried to fix it

• I have reduced the size of the data set from 593 M rows to 1.6 M rows, ensuring that the subset has only a single value for the date column used for chunking (day_partition_key). The result is exactly the same.
• Following the discussion at https://github.com/timescale/timescaledb/issues/643, I have changed the timescaledb config with SET timescaledb.max_open_chunks_per_insert=1;. The problem is still exactly the same.
• I have tried creating another target table without making it a hypertable. Inserting the 1.6 M row subset then works just fine. I expect the full set would work to, but I haven't taken the time to do that.

Versions, hardware and configuration

The docker image timescale/timescaledb-postgis:latest-pg13 (bf76e5594c98) was used to run timescaledb. It contains:

• PostgreSQL 13.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
• Timescaledb 2.3.0
• POSTGIS="2.5.5" [EXTENSION] PGSQL="130" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 7.1.1, September 1st, 2020" GDAL="GDAL 3.1.4, released 2020/10/20" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" RASTER

The docker container is limited to 16 cores and 128 GB of memory. For the tests referred to above, I used configuration parameters suggested by https://pgtune.leopard.in.ua/#/ for a data warehouse with my parameters, except that I lowered the available memory I provided to pgtune to 64 GB, hoping that would result in a config where the db would use less memory (I have also tried settings that are recommended for 128 GB of memory, the result is the same). The settings are:

max_connections = 30
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 34952kB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

Answer 1

Great work and very well explained!

I think you'll need to profile your Postgres to better understand what is going on.

Just ideas:

1. Remove the plpgsql function and validate your string or use coalesce to manage the null values.

I'd also consider using common table expressions to solve your issue in a single place and avoid calling the function multiple times.

2. If only the normalized data is important and the denormalized results are not important, consider adding a pre-step to clean up before insert and avoid the where clauses and so on.