Postgres Pro
Downloads
Home   >   mailing lists

Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically. - Mailing list pgsql-bugs

From Tyler
Subject Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.
Date
Msg-id 263af164-52f5-4b9c-bac3-5309795fe802@gmx.com
Whole thread Raw
Responses Re: Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.
List pgsql-bugs
Tree view
In our project lemmy, we recently had a production breaking bug causing extremely slow queries to one of our tables.

Finally after a lot of testing, we narrowed it down to a migration that increased the size of a varchar column meant to store URL data.

This increases the url column from 512 -> 2000 characters.

up.sql:

```sql
ALTER TABLE post
    ALTER COLUMN url TYPE varchar(2000);
```

This table currently has 1,186,895 rows, and joins are occasionally done to that column.

We finally realized that running this simple query manually, fixed the issue:

`ANALYZE post (url);`

I'm sure we're not the only ones to experience this potentially production-breaking bug, and postgres should probably automatically re-run analyze on columns for tables that have a large number of rows, that are changed.

For more context, see: https://github.com/LemmyNet/lemmy/pull/5148

Thanks everyone,

-- dessalines

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: Detection of hadware feature => please do not use signal
Next
From: Tom Lane
Date:
Subject: Re: Detection of hadware feature => please do not use signal