Thread
-
The memory usage is positively correlated with the number of partition tables when pg_get_expr is called.
Zhu Yang <fairyfar@msn.com> — 2025-08-20T06:16:53Z
Unreasonable memory usage. # SUMMARY INFO OS: Red Hat Enterprise Linux 7.6 PostgreSQL version: 19devel [yz@bogon postgresql]$ psql postgres psql (19devel) Type "help" for help. postgres=# select version(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1 20200408 (Red Hat 9.3.1-2), 64-bit (1 row) # TEST CASE 1. Modify postgresql.conf, and set GUCs: ``` max_locks_per_transaction = 2048 shared_buffers = '10GB' ``` 2. Restart PostgreSQL Make the parameter modification take effect. 3. Create database ``` CREATE DATABASE mem; ``` 4. Create 500 partition tables (it is recommended to create them in segments), with each partitioned table having 200 sub-partitions. ``` [yz@bogon postgresql]$ psql mem psql (19devel) Type "help" for help. mem=# DO $$ DECLARE part_start bigint := 1; part_end bigint := 500; part_curr bigint := part_start; sub_start bigint := 1; sub_end bigint := 200; sub_curr bigint := sub_start; BEGIN WHILE part_curr < part_end LOOP EXECUTE format(' CREATE TABLE sales%s( id int, sale_price bigint, product_name text )partition by range (sale_price)', part_curr ); sub_curr := sub_start; WHILE sub_curr < sub_end LOOP EXECUTE format(' CREATE TABLE sales%s_%s PARTITION OF sales%s FOR VALUES FROM (%s) TO (%s)', part_curr, sub_curr, part_curr, sub_curr, sub_curr + 1 ); sub_curr := sub_curr + 1; END LOOP; part_curr := part_curr + 1; END LOOP; END $$; ``` After successful creation, exit the psql connection. 5. Call pg_get_expr ``` [yz@bogon postgresql]$ psql mem psql (19devel) Type "help" for help. mem=# SELECT pg_get_expr(relpartbound, oid) FROM pg_class WHERE relname like 'sales%'; ``` Wait for the query to complete. 6. Check the query memory usage in Step 5 Find the PID corresponding to the query: ``` [yz@bogon ~]$ ps ux USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND yz 1739 0.0 1.8 5619732 144672 ? Ss 07:22 0:01 /home/yz/postgresql/pg_bin/bin/postgres -D pg_data yz 1740 0.0 0.0 5619868 3704 ? Ss 07:22 0:00 postgres: io worker 1 yz 1741 0.0 0.0 5619868 6580 ? Ss 07:22 0:00 postgres: io worker 0 yz 1742 0.0 0.0 5619868 2924 ? Ss 07:22 0:00 postgres: io worker 2 yz 1743 0.8 12.1 5661680 971236 ? Ds 07:22 3:42 postgres: checkpointer yz 1744 0.0 1.0 5620820 87232 ? Ss 07:22 0:11 postgres: background writer yz 1746 0.1 0.2 5619868 18212 ? Ss 07:22 0:29 postgres: walwriter yz 1747 0.0 0.0 5658196 3408 ? Ss 07:22 0:01 postgres: autovacuum launcher yz 1748 0.0 0.0 5621196 3436 ? Ss 07:22 0:00 postgres: logical replication launcher yz 30192 1.4 0.0 124644 3636 pts/0 S+ 14:31 0:00 psql mem yz 30193 21.3 10.5 5942276 841712 ? Ss 14:31 0:10 postgres: yz mem [local] idle ``` Print the memory count of CacheMemoryContext using gdb: ``` [yz@bogon ~]$ gdb -p 30193 ... (gdb) p *CacheMemoryContext $1 = {type = T_AllocSetContext, isReset = false, allowInCritSection = false, mem_allocated = 286261312, methods = 0xecbff0 <mcxt_methods+240>, parent = 0x2b2af50, firstchild = 0x2b5a3d0, prevchild = 0x2b8f2c0, nextchild = 0x2b8b2a0, name = 0xea853f "CacheMemoryContext", ident = 0x0, reset_cbs = 0x0} ``` It can be seen that the value of mem_allocated in CacheMemoryContext is approximately 280MB. When the number of partition tables or sub-partitions increases, the memory usage also increases. Yours Sincerely, Yang Zhu -
Re: The memory usage is positively correlated with the number of partition tables when pg_get_expr is called.
Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-20T12:09:10Z
Zhu Yang <fairyfar@msn.com> writes: > Unreasonable memory usage. Not at all. You have a session that has accessed 100000 tables. If you don't want to eat a lot of relcache space, rethink your schema. (IMV, a table with hundreds or thousands of partitions is an anti-pattern.) regards, tom lane