Forest-cli database introspection very slow on pg14

Help me!
1

Feature(s) impacted

forest projects:create
forest schema:update

Observed behavior

On pg11, above commands take around 10 seconds
On pg14, above commands take around 3 minutes

This was measured on the same code+data, just by upgrading/rollbacking pg version.

Expected behavior

On pg11, above commands take around 10 seconds
On pg14, above commands take around 10 seconds

Context

  • Project name: see support email

  • Team name: see support email

  • Environment name: development

  • Agent type & version:

    “liana”: “forest-express-sequelize”,
    “liana_version”: “8.5.12”,
    “stack”: {
    “database_type”: “postgres”,
    “engine”: “nodejs”,
    “engine_version”: “16.15.0”,
    “orm_version”: “5.15.2”
    }

  • Recent changes made on your end if any: postgres upgrade

3

Hey @louisl, and welcome to our community :wave:

Just did a quick test on my end, and I’m sadly not able to reproduce your Observed Behavior.

Could you provide an estimated number or tables/records you have in total so I can put myself in a similar state to yours ?

Thanks in advance :pray:

4

Hello @anon39848301, thanks for testing this.

My postgres database “lives” in a Docker container FYI.

SELECT SUM(reltuples) AS estimate FROM pg_class where reltuples != -1 returns 290809.

Table count 123
select count(*) from information_schema.tables where table_schema='public' and table_name not ilike 'pg_%';

5

My postgres database “lives” in a Docker container FYI.

Same on my end (Tested with both 14 & 14.1)

I’ll do a quick sample database with multiple thousand of records to check if that’s related :+1:
I’ll update this response if I’m able to find something.

6

Oh, I’m on postgres 14.2, maybe this?

7

Still, unable to reproduce the issue :frowning:

Could you share with me your current version of pg?

8

14.2 (Debian 14.2-1.pgdg110+1)

docker-compose.yml

version: '3.5'

services:
  database:
    container_name: project-database
    image: postgres:14.2
    shm_size: 512m
    networks:
      - dbnet
    ports:
      - 5432:5432
    environment:
      - POSTGRES_HOST=database
      - POSTGRES_DB=db
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password

networks:
  dbnet:
    name: database-network

I will test on a database outside of Docker to see if the issue lies here

9

Ok so I used Postgres.app for easy reproduction

psql (14.4, server 11.17) => OK
psql (14.4, server 12.12) => KO
psql (14.4, server 13.8) => KO
psql (14.4, server 14.5) => KO
psql (14.4, server 15.0) => KO

So apparently even an upgrade to pg12 makes this very slow.
This is not linked to Docker too.
And I deleted all data from tables. Still KO with pg12+.
Deleted the schema, created a new schema with a dummy table, and here it is OK (:hot_face:).

So the database introspection with pg12+ doesn’t like something in our schema.

Would you like to have a dump of our schema? I can send it to your professional FA e-mail (there is no data inside)

10

Ok, so after importing your dump in 12.12, I’m getting ~3mn to generate all the models.

However, importing the same dump in 11.17 gives me … 4mn to generate all the models.

It seems like the SQL query needed to retrieve your database constraints (PK, unique indexes, etc) is very slow, but I’m still not sure why.

Running this SQL query on any table of any version of pg of your dump gives me ~700ms/table.
Running this SQL query on another database on pg@12 I have on my end gives me ~10ms/table.

I’m still digging the differences, but I guess this could also be interesting to share.

11

Hey @louisl :wave:

I finally manage to reproduce the issue, and the fix is on it’s way. It should be released today.

Be sure we’ll ping you once it is available :+1:

12

Hello @anon39848301, good news thanks a lot!

13

Hey @louisl,

The fix is released and included in the new forest-cli@3.0.2.

npm install -g forest-cli@latest should do the trick.

Let me know if your issue is fixed with this new version :pray:

14

It works great, thanks! (nice one-line fix by the way :ok_hand:t2:)