I'm trying to run crosstab() function on the amount of data, that doesn't fit RAM. I expected that Postgres will use temp files on disk, as with sorting after exhausting work_mem limit. But it doesn't seem to use them.
My test case:
Postgresql 15 running in docker container, that started with this command:
docker run -d \
--name postgres_olap \
-p 5432:5432 \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-e POSTGRES_PASSWORD=qq123 \
-v /home/pavelt/projects_data/postgres_config_comparison/pgdata_olap:/var/lib/postgresql/data/pgdata \
-v /home/pavelt/code/postgres_config_comparison/olap/postgresql.conf:/etc/postgresql/postgresql.conf \
--shm-size=4gb \
--memory=16g \
--memory-swap=16g \
--cpuset-cpus=0,1,2,3,4,5,6,7 \
postgres:15.2 \
-c 'config_file=/etc/postgresql/postgresql.conf'
Manually changed Postgres parameters:
max_connections = 20
shared_buffers = 4GB
effective_cache_size = 8GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26107kB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
shared_preload_libraries='auto_explain'
auto_explain.log_min_duration = 3s
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_wal = true
auto_explain.log_timing = true
Tables (I created materialized view to avoid sorting during crosstab() running):
create table raw_data as
select a.time, b.id_sensor, random() value
from generate_series('2023-01-01 00:00:00'::timestamp, '2023-01-15 00:00:00'::timestamp, '1 second'::interval) as a(time)
cross join generate_series(1, 300) as b(id_sensor);
create materialized view raw_data_sorted as
select time, id_sensor, value
from raw_data
order by 1;
Table raw_data_sorted size is 18GB.
Query, that I'm trying to run:
select *
from crosstab(
$$
select time, id_sensor, value
from raw_data_sorted
$$::text,
$$
select * from generate_series(1, 300) as b(id_sensor) order by id_sensor
$$::text
) as ct(time timestamp,
sensor_id_1 double precision,
sensor_id_2 double precision,
sensor_id_3 double precision,
sensor_id_4 double precision,
sensor_id_5 double precision,
sensor_id_6 double precision,
sensor_id_7 double precision,
sensor_id_8 double precision,
sensor_id_9 double precision,
sensor_id_10 double precision,
sensor_id_11 double precision,
sensor_id_12 double precision,
sensor_id_13 double precision,
sensor_id_14 double precision,
sensor_id_15 double precision,
sensor_id_16 double precision,
sensor_id_17 double precision,
sensor_id_18 double precision,
sensor_id_19 double precision,
sensor_id_20 double precision,
sensor_id_21 double precision,
sensor_id_22 double precision,
sensor_id_23 double precision,
sensor_id_24 double precision,
sensor_id_25 double precision,
sensor_id_26 double precision,
sensor_id_27 double precision,
sensor_id_28 double precision,
sensor_id_29 double precision,
sensor_id_30 double precision,
sensor_id_31 double precision,
sensor_id_32 double precision,
sensor_id_33 double precision,
sensor_id_34 double precision,
sensor_id_35 double precision,
sensor_id_36 double precision,
sensor_id_37 double precision,
sensor_id_38 double precision,
sensor_id_39 double precision,
sensor_id_40 double precision,
sensor_id_41 double precision,
sensor_id_42 double precision,
sensor_id_43 double precision,
sensor_id_44 double precision,
sensor_id_45 double precision,
sensor_id_46 double precision,
sensor_id_47 double precision,
sensor_id_48 double precision,
sensor_id_49 double precision,
sensor_id_50 double precision,
sensor_id_51 double precision,
sensor_id_52 double precision,
sensor_id_53 double precision,
sensor_id_54 double precision,
sensor_id_55 double precision,
sensor_id_56 double precision,
sensor_id_57 double precision,
sensor_id_58 double precision,
sensor_id_59 double precision,
sensor_id_60 double precision,
sensor_id_61 double precision,
sensor_id_62 double precision,
sensor_id_63 double precision,
sensor_id_64 double precision,
sensor_id_65 double precision,
sensor_id_66 double precision,
sensor_id_67 double precision,
sensor_id_68 double precision,
sensor_id_69 double precision,
sensor_id_70 double precision,
sensor_id_71 double precision,
sensor_id_72 double precision,
sensor_id_73 double precision,
sensor_id_74 double precision,
sensor_id_75 double precision,
sensor_id_76 double precision,
sensor_id_77 double precision,
sensor_id_78 double precision,
sensor_id_79 double precision,
sensor_id_80 double precision,
sensor_id_81 double precision,
sensor_id_82 double precision,
sensor_id_83 double precision,
sensor_id_84 double precision,
sensor_id_85 double precision,
sensor_id_86 double precision,
sensor_id_87 double precision,
sensor_id_88 double precision,
sensor_id_89 double precision,
sensor_id_90 double precision,
sensor_id_91 double precision,
sensor_id_92 double precision,
sensor_id_93 double precision,
sensor_id_94 double precision,
sensor_id_95 double precision,
sensor_id_96 double precision,
sensor_id_97 double precision,
sensor_id_98 double precision,
sensor_id_99 double precision,
sensor_id_100 double precision,
sensor_id_101 double precision,
sensor_id_102 double precision,
sensor_id_103 double precision,
sensor_id_104 double precision,
sensor_id_105 double precision,
sensor_id_106 double precision,
sensor_id_107 double precision,
sensor_id_108 double precision,
sensor_id_109 double precision,
sensor_id_110 double precision,
sensor_id_111 double precision,
sensor_id_112 double precision,
sensor_id_113 double precision,
sensor_id_114 double precision,
sensor_id_115 double precision,
sensor_id_116 double precision,
sensor_id_117 double precision,
sensor_id_118 double precision,
sensor_id_119 double precision,
sensor_id_120 double precision,
sensor_id_121 double precision,
sensor_id_122 double precision,
sensor_id_123 double precision,
sensor_id_124 double precision,
sensor_id_125 double precision,
sensor_id_126 double precision,
sensor_id_127 double precision,
sensor_id_128 double precision,
sensor_id_129 double precision,
sensor_id_130 double precision,
sensor_id_131 double precision,
sensor_id_132 double precision,
sensor_id_133 double precision,
sensor_id_134 double precision,
sensor_id_135 double precision,
sensor_id_136 double precision,
sensor_id_137 double precision,
sensor_id_138 double precision,
sensor_id_139 double precision,
sensor_id_140 double precision,
sensor_id_141 double precision,
sensor_id_142 double precision,
sensor_id_143 double precision,
sensor_id_144 double precision,
sensor_id_145 double precision,
sensor_id_146 double precision,
sensor_id_147 double precision,
sensor_id_148 double precision,
sensor_id_149 double precision,
sensor_id_150 double precision,
sensor_id_151 double precision,
sensor_id_152 double precision,
sensor_id_153 double precision,
sensor_id_154 double precision,
sensor_id_155 double precision,
sensor_id_156 double precision,
sensor_id_157 double precision,
sensor_id_158 double precision,
sensor_id_159 double precision,
sensor_id_160 double precision,
sensor_id_161 double precision,
sensor_id_162 double precision,
sensor_id_163 double precision,
sensor_id_164 double precision,
sensor_id_165 double precision,
sensor_id_166 double precision,
sensor_id_167 double precision,
sensor_id_168 double precision,
sensor_id_169 double precision,
sensor_id_170 double precision,
sensor_id_171 double precision,
sensor_id_172 double precision,
sensor_id_173 double precision,
sensor_id_174 double precision,
sensor_id_175 double precision,
sensor_id_176 double precision,
sensor_id_177 double precision,
sensor_id_178 double precision,
sensor_id_179 double precision,
sensor_id_180 double precision,
sensor_id_181 double precision,
sensor_id_182 double precision,
sensor_id_183 double precision,
sensor_id_184 double precision,
sensor_id_185 double precision,
sensor_id_186 double precision,
sensor_id_187 double precision,
sensor_id_188 double precision,
sensor_id_189 double precision,
sensor_id_190 double precision,
sensor_id_191 double precision,
sensor_id_192 double precision,
sensor_id_193 double precision,
sensor_id_194 double precision,
sensor_id_195 double precision,
sensor_id_196 double precision,
sensor_id_197 double precision,
sensor_id_198 double precision,
sensor_id_199 double precision,
sensor_id_200 double precision,
sensor_id_201 double precision,
sensor_id_202 double precision,
sensor_id_203 double precision,
sensor_id_204 double precision,
sensor_id_205 double precision,
sensor_id_206 double precision,
sensor_id_207 double precision,
sensor_id_208 double precision,
sensor_id_209 double precision,
sensor_id_210 double precision,
sensor_id_211 double precision,
sensor_id_212 double precision,
sensor_id_213 double precision,
sensor_id_214 double precision,
sensor_id_215 double precision,
sensor_id_216 double precision,
sensor_id_217 double precision,
sensor_id_218 double precision,
sensor_id_219 double precision,
sensor_id_220 double precision,
sensor_id_221 double precision,
sensor_id_222 double precision,
sensor_id_223 double precision,
sensor_id_224 double precision,
sensor_id_225 double precision,
sensor_id_226 double precision,
sensor_id_227 double precision,
sensor_id_228 double precision,
sensor_id_229 double precision,
sensor_id_230 double precision,
sensor_id_231 double precision,
sensor_id_232 double precision,
sensor_id_233 double precision,
sensor_id_234 double precision,
sensor_id_235 double precision,
sensor_id_236 double precision,
sensor_id_237 double precision,
sensor_id_238 double precision,
sensor_id_239 double precision,
sensor_id_240 double precision,
sensor_id_241 double precision,
sensor_id_242 double precision,
sensor_id_243 double precision,
sensor_id_244 double precision,
sensor_id_245 double precision,
sensor_id_246 double precision,
sensor_id_247 double precision,
sensor_id_248 double precision,
sensor_id_249 double precision,
sensor_id_250 double precision,
sensor_id_251 double precision,
sensor_id_252 double precision,
sensor_id_253 double precision,
sensor_id_254 double precision,
sensor_id_255 double precision,
sensor_id_256 double precision,
sensor_id_257 double precision,
sensor_id_258 double precision,
sensor_id_259 double precision,
sensor_id_260 double precision,
sensor_id_261 double precision,
sensor_id_262 double precision,
sensor_id_263 double precision,
sensor_id_264 double precision,
sensor_id_265 double precision,
sensor_id_266 double precision,
sensor_id_267 double precision,
sensor_id_268 double precision,
sensor_id_269 double precision,
sensor_id_270 double precision,
sensor_id_271 double precision,
sensor_id_272 double precision,
sensor_id_273 double precision,
sensor_id_274 double precision,
sensor_id_275 double precision,
sensor_id_276 double precision,
sensor_id_277 double precision,
sensor_id_278 double precision,
sensor_id_279 double precision,
sensor_id_280 double precision,
sensor_id_281 double precision,
sensor_id_282 double precision,
sensor_id_283 double precision,
sensor_id_284 double precision,
sensor_id_285 double precision,
sensor_id_286 double precision,
sensor_id_287 double precision,
sensor_id_288 double precision,
sensor_id_289 double precision,
sensor_id_290 double precision,
sensor_id_291 double precision,
sensor_id_292 double precision,
sensor_id_293 double precision,
sensor_id_294 double precision,
sensor_id_295 double precision,
sensor_id_296 double precision,
sensor_id_297 double precision,
sensor_id_298 double precision,
sensor_id_299 double precision,
sensor_id_300 double precision
);
Using the command docker stats I saw that the container consumed all memory up to the limit. Directory for Postgres temp files /var/lib/postgresql/data/pgdata/base/pgsql_tmp remains empty all time during query running.
Based on the results of these measurements, I can assume that the function crosstab() doesn't use temp files. But I couldn't find any explicit comment about that in the documentation or after googling.
Can anyone confirm my conclusions, or even better indicate some document where this is clearly stated? Or will the next step be to analyze the source code of the extension?
P.S. I understand after this research that this function is probably not intended for such volumes of data and it is better to do pivot in the application. But in the end, I just want to confirm my conclusions.
