I am moving from SQL Server to Postgres, but I faced a very primitive problem, which I solved after googling for a while, but I don't know if what I did is the official way!

I have a simple table of id integer column, tags array column and a jsonb column.

I have Gin index on the tags array field, and performance is great selecting records. I only have around 130,000 rows.

I faced a very slow update, just a very simple update append numeric value to the array column updating the entire table 130,000 row, I am adding this part , as I think it was not clear to all

it took around 2 minutes to complete!

When I read more about PostgreSQL, I understood that it almost rebuild my table as I update every row.

So I made 2 things I

ALTER TABLE mytable SET (FILLFACTOR = 50);
VACUUM FULL mytable;

and on every query, I drop the gin index and do the update and rebuild the index.

This result in a total of 2~3 seconds instead of 2 minutes for the update.

So I think I solved my problem as dropping the index allowed HOT update, but my question is (( is it the official way to do things like this ))?

CodePudding user response：

PostGreSQL is known to be very slow on some queries. Especially COUNT and UPDATE facing Microsoft SQL Server (and some other RDBMS).

For COUNT queries read the paper I wrote : http://mssqlserver.fr/postgresql-vs-microsoft-sql-server-comparison-part-2-count-performances/

For UPDATEs PostGreSQL is unnable to do a "in place" UPDATE and act as DELETE INSERT.... https://www.cybertec-postgresql.com/en/is-update-the-same-as-delete-insert-in-postgresql/

Of course it is not a good pratice at all to drop an index to minimize the execution time because concurrent users wont benefits from this index and the reCREATE index will have an increasing time when you will have some more rows in your table.