Is the index building process locking the table?

Question

PostgreSQL

I can't find it in the documentation.
The problem is this. There is a table on 100 million records. I needed to add one field and then I decided to fill it in by doing

UPDATE (SET a = 'foo' where field и LIKE '%pattern%')

by him.
Before UPDATE, I made an index on it, and as I understand it, it was a big mistake.

As a result, a day has passed, I have one core loaded and all other UPDATE requests (not related to this field are processed for 10 minutes).

Hence the question:
1. Do I understand correctly that hash indexes cannot be used by all
1.1 kernels at once, but btree can?
2. How to be in the current situation. I understand that the index had to be done after UPDATE, but how to understand how long the process will end? Might be worth the wait?
3. Why is UPDATE not associated with this field so slow?

Answer 1

UPDATE (SET a = 'foo' where field and LIKE '%pattern%')

It was necessary to add " EXPLAIN ANALYSE " before the command, then it was possible to find out the query plan without executing it.

3. Why is UPDATE not associated with this field so slow?

See above.
Especially when

There is a table on 100 million records.

Also, what index was added to the field on which the LIKE is performed? If B-tree, Hash, then it has zero value to speed up the query. This field may need a GIN index (pg_trgm). Or even it is necessary to redesign the tables, taking this field out, breaking it into many records and linking N:M. We don't know what the data is in that field.
For experiments, it is better to create a limited dataset close to production and test on it, instead of 100M records.

Answer 2

Is the index building process locking the table?

and right there

Before UPDATE I made an index on it

Well, what do you think?

I needed to add one field

adding a non-DEFAULT field that allows a nullable value will be fast. If it needs to be filled in (there is DEFAULT, or how you do it by hand), this is already an update for 100 million lines.

1. Do I understand correctly that hash indexes cannot be used by all
1.1 kernels at once, but btree can?

In general, neither hash nor btree indexes have anything to do with it.
Postgres only has parallel queries - parallel queries that can be executed by multiple threads. There are no parallel updates.

3. Why is UPDATE not associated with this field so slow?

The main update loads the machine + if other updates try to change the same rows in the table, they most likely do not slow down for you, but simply fall off by timeout. For lines are blocked by the main infinite transaction.
Which field is changed (same or not) does not matter.

2. How to be in the current situation. I understand that the index had to be done after UPDATE, but how to understand how long the process will end? Might be worth the wait?

Beat this update to get started. Nothing bad will happen. No one here will tell you how much longer it will hang. Maybe he is waiting for some kind of blocking.
Then VACUUM ANALYZE on the table.
As for filling the field, the conditions are not clear from the question. Do you want to do this in the entire table (or most of it)? Or this one: where field и LIKE '%pattern%'- fetching a small number of rows (approximately how many?)?
* actually, it would be worth pulling SELECT with the same condition before UPDATE and see how long it takes *
A simple index will not work with queries like ilike '%pattern%'. Install the pg_trgm extension and make a GIN index . Again, if you need to fill half a table, the index will not save you, you will have to break the UPDATE into pieces.

Answer 3

try disabling ssl support by correcting the line in the configuration.php configuration file:
public $force_ssl = '0';

Answer 4

Your admin panel is trying to load via https (and the site does not work on https itself).
In the configuration.php file, change
Also check that the $live_site variable in the same file does not contain https. Well, in general, look for the occurrence of https and ssl in the config.