JavaScript heap out of memory for simple date query

[swyngaard]

I'm trying to fetch a single column (with type timestamp with time zone) with 11 million records:

(async () => {
  const { Client } = require('pg');
  const client = new Client();
  await client.connect();
  await client.query('SELECT "date_first_withdraw" FROM "AccountActivities"');
  console.log('done!');
  await client.end();
})();

However, it fails with the following error message:

<--- Last few GCs --->

[4991:0x333d8f0]    33970 ms: Scavenge 1390.7 (1421.8) -> 1390.0 (1422.3) MB, 3.2 / 0.0 ms  (average mu = 0.146, current mu = 0.075) allocation failure 
[4991:0x333d8f0]    33976 ms: Scavenge 1390.8 (1422.3) -> 1390.1 (1422.8) MB, 3.3 / 0.0 ms  (average mu = 0.146, current mu = 0.075) allocation failure 
[4991:0x333d8f0]    33983 ms: Scavenge 1390.9 (1422.8) -> 1390.2 (1423.8) MB, 3.6 / 0.0 ms  (average mu = 0.146, current mu = 0.075) allocation failure 


<--- JS stacktrace --->

==== JS stack trace =========================================

    0: ExitFrame [pc: 0x16789b75be1d]
Security context: 0x266e5261e6e9 <JSObject>
    1: exec [0x266e52609239](this=0x1847b3d064a1 <JSRegExp <String[69]: (\d{1,})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})(\.\d{1,})?.*?( BC)?$>>,0x19d1c721f951 <String[22]: 2018-01-15 20:18:45+00>)
    2: parseDate [0x1d8c9f6215e1] [/home/swyngaard/test-api/node_modules/postgres-date/index.js:~8] [pc=0x16789b764efc](this=0x1847b3d06549 <JSArray[1]>,isoD...

FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory
 1: 0x8fa0c0 node::Abort() [node]
 2: 0x8fa10c  [node]
 3: 0xb0026e v8::Utils::ReportOOMFailure(v8::internal::Isolate*, char const*, bool) [node]
 4: 0xb004a4 v8::internal::V8::FatalProcessOutOfMemory(v8::internal::Isolate*, char const*, bool) [node]
 5: 0xef49b2  [node]
 6: 0xef4ab8 v8::internal::Heap::CheckIneffectiveMarkCompact(unsigned long, double) [node]
 7: 0xf00b92 v8::internal::Heap::PerformGarbageCollection(v8::internal::GarbageCollector, v8::GCCallbackFlags) [node]
 8: 0xf014c4 v8::internal::Heap::CollectGarbage(v8::internal::AllocationSpace, v8::internal::GarbageCollectionReason, v8::GCCallbackFlags) [node]
 9: 0xf04131 v8::internal::Heap::AllocateRawWithRetryOrFail(int, v8::internal::AllocationSpace, v8::internal::AllocationAlignment) [node]
10: 0xecd5b4 v8::internal::Factory::NewFillerObject(int, bool, v8::internal::AllocationSpace) [node]
11: 0x116d73e v8::internal::Runtime_AllocateInNewSpace(int, v8::internal::Object**, v8::internal::Isolate*) [node]
12: 0x16789b75be1d 
Aborted

The above query succeeds when using LIMIT 8600000. It also succeeds without the LIMIT if I use the official psql client. This is the table schema:

                                            Table "public.AccountActivities"
       Column        |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
---------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id                  | uuid                     |           | not null |         | plain    |              | 
 acct_address        | character varying(255)   |           |          |         | extended |              | 
 date_first_deposit  | timestamp with time zone |           |          |         | plain    |              | 
 date_first_withdraw | timestamp with time zone |           |          |         | plain    |              | 
 tx_in               | integer                  |           |          |         | plain    |              | 
 tx_out              | integer                  |           |          |         | plain    |              | 
 val_in              | double precision         |           |          |         | plain    |              | 
 val_out             | double precision         |           |          |         | plain    |              | 
 avg_tx_in_month     | double precision         |           |          |         | plain    |              | 
 std_tx_in_month     | double precision         |           |          |         | plain    |              | 
 avg_tx_out_month    | double precision         |           |          |         | plain    |              | 
 std_tx_out_month    | double precision         |           |          |         | plain    |              | 
 avg_val_in_month    | double precision         |           |          |         | plain    |              | 
 std_val_in_month    | double precision         |           |          |         | plain    |              | 
 avg_val_out_month   | double precision         |           |          |         | plain    |              | 
 std_val_out_month   | double precision         |           |          |         | plain    |              | 
 is_contract         | boolean                  |           |          |         | plain    |              | 
 last_upd            | timestamp with time zone |           |          |         | plain    |              | 
 age_seconds         | integer                  |           |          |         | plain    |              | 
 createdAt           | timestamp with time zone |           | not null |         | plain    |              | 
 updatedAt           | timestamp with time zone |           | not null |         | plain    |              | 
 selected_month      | date                     |           |          |         | plain    |              | 
Indexes:
    "AccountActivities_pkey" PRIMARY KEY, btree (id)

Additionally, the query also fails with the same error if I try to fetch only the id column (which is type uuid).

Node version: v10.16.3
pg version: 7.12.1

The size of the data should comfortably fit in available memory but it is does not. How can I load all the dates into memory?

[sehrope]

The command line psql is a C program that uses libpq. It mallocs memory so as long as you have enough memory or swap, it can eventually allocate it to handle huge results.

This driver (pg) runs in the garbage collected Node.js VM. It has a max allocation size which you could increase with some options. Check out the --max_old_space_size option. That's if you really need to have all the results in memory. The amount of memory required isn't exactly what's sent on the wire as there's overhead for the internal buffers, over allocating for efficiency, and even more memory for data type conversion.

A better alternative would be to change whatever you're doing to use either the cursor or COPY interface. Those let you process infinite sized results with limited memory:

https://node-postgres.com/api/cursor/

https://github.com/brianc/node-pg-copy-streams#readme

[gajus]

To add to @sehrope, Slonik implements stream for this reason.

https://github.com/gajus/slonik#slonik-query-methods-stream

await connection.stream(sql`SELECT "date_first_withdraw" FROM "AccountActivities"`, (stream) => {
  stream.on('data', (datum) => {
    // Row data.
    datum;
  });
});

[brianc]

Yah streaming is the way to go here - check out pg-query-stream or pg-cursor