0

I have local database in postgres. In which single table contains data of "74980435".

When I have tried to execute SELECT query it is throwing this error:

"could not write block 657567 of temporary file: No space left on device".

I am trying to execute select query in Laravel.

Can anyone help me?

Dalton Cézane
  • 3,672
  • 2
  • 35
  • 60
Jimesh Gajera
  • 612
  • 1
  • 11
  • 32
  • possibly related: https://stackoverflow.com/questions/52606584/postgresql-temporary-file-no-space-left-on-device-location-of-temporary-files – jmunsch Jun 28 '19 at 22:00

1 Answers1

1

Your query (which you didn't show) is probably missing a join condition or two, or it tries to sort an enormous amountt of rows or cache an enormous function result or materialize node.

When data don't fit in work_mem, PostgreSQL starts putting them into temporary disk files. Your query created enough of those to fill the file system temporarily.

You can set the temp_file_limit parameter as a defense, but you should figure out the bug in your query.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • There is no bug into the query because now table contains "133072194" records and I am trying to execute "SELECT COUNT(test) FROM mytable WHERE test = false" but it is taking Execution time: 128320.712 ms – Jimesh Gajera Jul 01 '19 at 19:16
  • I already have indexing on test column. Could you please let me know, what I can optimize or change, so my query became faster. – Jimesh Gajera Jul 01 '19 at 19:17
  • Certainly not without knowing the query and the `EXPLAIN (ANALYZE, BUFFERS)` output. But you should open another question for that. – Laurenz Albe Jul 02 '19 at 02:35