Repository navigation
postgres version 16 onwards is consuming entire disk space within container (possible disk spill is happenning) #1317
Description
I have created a stored procedure:
CREATE OR REPLACE PROCEDURE TEL1PrepUploadTables(IN Dataset text)
LANGUAGE PLPGSQL
AS \$\$
BEGIN
EXECUTE format('CREATE TABLE "add_cell_raw_%s" PARTITION OF add_cell_raw FOR VALUES IN (''%s'');',Dataset,Dataset);
EXECUTE format('CREATE TABLE "del_cell_id_raw_%s" PARTITION OF del_cell_id_raw FOR VALUES IN (''%s'');',Dataset,Dataset);
EXECUTE format('CREATE OR REPLACE VIEW add_cell_raw_latest AS
SELECT * FROM "add_cell_raw_%s";',Dataset);
EXECUTE format('CREATE OR REPLACE VIEW del_cell_id_raw_latest AS SELECT * FROM "del_cell_id_raw_%s";',
Dataset);
END;
\$\$;in this procedure there is a step to create or replace view add_cell_raw_latest...
which takes data from a raw table add_cell_raw_%s (actual value add_cell_raw_c169cwea01_export_LTECell_v4_20250208073005)...this raw table contains roughly 750 MB
TEL1_WEA_4G=# SELECT pg_size_pretty(pg_table_size('"add_cell_raw_c169cwea01_export_LTECell_v4_20250208073005"'));
pg_size_pretty
----------------
737 MB
(1 row)
Now when this CREATE VIEW runs internally postgres runs the following step to create the VIEW
TEL1_WEA_4G=# SELECT pid, query, state, wait_event, wait_event_type FROM pg_stat_activity WHERE state <> 'idle';
pid | query | state | wait_event | wait_event_type
-----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+-----------------
173 | COPY "add_cell_raw_c169cwea01_export_LTECell_v4_20250208073005" ("Geometry", "dataset", "DateTime", "UploadTime", "mcc", "mnc", "cid", "cellType", "cellName", "antennaLat", "antennaLon", "antennaOrientation", "antennaOpening", "antennaHorizontalRange", "geoType") FROM STDIN; | active | |
Now the ISSUE is i have created a kubernetes container using
postgres images which are version 15 or lower and postgres images which are version 16 or higher (with volume allocated to container 50 GB)..
Observation
- when I am running container based on postgres image versions 15 or lower the space is being occupied in a reasonable way
wmps-postgres-deployment-b6465cb7b-rj9lw:/# df -h
Filesystem Size Used Available Use% Mounted on
overlay 154.9G 102.3G 52.5G 66% /
tmpfs 64.0M 0 64.0M 0% /dev
/dev/vda1 154.9G 102.3G 52.5G 66% /docker-entrypoint-initdb.d
/dev/vda1 154.9G 102.3G 52.5G 66% /etc/hosts
/dev/vda1 154.9G 102.3G 52.5G 66% /dev/termination-log
/dev/vda1 154.9G 102.3G 52.5G 66% /etc/hostname
/dev/vda1 154.9G 102.3G 52.5G 66% /etc/resolv.conf
shm 64.0M 1.0M 63.0M 2% /dev/shm
**kadalu:kadalu-storage-pool
50.0G 3.8G 46.2G 8% /var/lib/postgresql/data**
- HOWEVER same STEP running on postgres image versions 16 or higher leads to immediate disk usage consuming entire 50 GB diskspace ... corrupting the container making it unusable
wmps-postgres-deployment-b6465cb7b-rj9lw:/# df -h
Filesystem Size Used Available Use% Mounted on
overlay 154.9G 102.3G 52.5G 66% /
tmpfs 64.0M 0 64.0M 0% /dev
/dev/vda1 154.9G 102.3G 52.5G 66% /docker-entrypoint-initdb.d
/dev/vda1 154.9G 102.3G 52.5G 66% /etc/hosts
/dev/vda1 154.9G 102.3G 52.5G 66% /dev/termination-log
/dev/vda1 154.9G 102.3G 52.5G 66% /etc/hostname
/dev/vda1 154.9G 102.3G 52.5G 66% /etc/resolv.conf
shm 64.0M 1.0M 63.0M 2% /dev/shm
**kadalu:kadalu-storage-pool
50.0G 50G 0G 100% /var/lib/postgresql/data**
As I said this issue coming from postgres images 16 and onwards... I am reading some articles where to increase the query performance could lead to disk spill of the data in postgres... please look into it and fix it
Activity
LaurentGoderre commentedon Feb 17, 2025
Can you run
\l+in psql to see the size of the db?saurabh021184 commentedon Feb 17, 2025
This is the screen shot of version 17 postgres image... but the database is still in healthy state... i can't give you the same data when it gets corrupted as when the container goes bad and i cannot run this command.
LaurentGoderre commentedon Feb 17, 2025
How many time has the corrupted db pod restarted?
LaurentGoderre commentedon Feb 17, 2025
Can you give this a try? https://stackoverflow.com/a/72740536
saurabh021184 commentedon Feb 17, 2025
I waited
I waited for couple of hours for it to restore... But it was in same state