rebuild index and update statistics for a postgresql 9.3.6 DB with command.

and what is your question?

If you asking about why it is about half the size, did you recently delete a LOT of data in the database?  Rebuilding of indexes will remove the dead rows from the indexes.

What you describe is expected behavior.

hi,

I think this is the result of VACUUM(FULL, ANALYZE, VERBOSE);
What I want to know is, if the command I post is too much on index rebuild and update statistics ?

what should the correct one if it it not !

for postgreSQL 9.3., the command above will freeze the whole DB and I can't run anything else? that's why I am wondering why is it?

or any other higher version number of PostgreSQL, when running the command above, is not going to freeze the whole DB?

also it seems I can enable the autovacuum feature of postgreSQL so it free up space for us automatically?

what I found out is , this command:

REINDEX DATABASE  <Application database>;  
  REINDEX SYSTEM <Application database> ;
  VACUUM(FULL, ANALYZE, VERBOSE);

only work in PostgresQL 9.3.6 but not on 11.14, only this one ok:
VACUUM(FULL, ANALYZE, VERBOSE);

good ! then I am not sure why !!

so these is the command is the command I need to rebuild index and update statistics for all system and user database?

I run these command today again and the result :

Hi,
All three commands,
REINDEX DATABASE  <Application database>;  
  REINDEX SYSTEM <Application database> ;
  VACUUM(FULL, ANALYZE, VERBOSE);
are SQL commands and require that you to connect to the database that you intend to run it on. In other words, they only work on currently connected database and the tables/indexes that it holds. The error you get and the manual clearly imply this.

Regards,
     Tomas Helgi

are SQL commands and require that you to connect to the database that you intend to run it on.

tks but I am connected to it, I can run select version(), but not that.

it is connected.

and I still found only this one:

 VACUUM(FULL, ANALYZE, VERBOSE);

can be run

Hi,
Try

REINDEX system "alfresco";

Select allOpen in new window

If that works then your database was created with double quotes around the name.
Note that databases,tables,views etc created with double quotes around the name are case sensitive and therefore you need to address the objects with "name" but addressing the objects only with name will simply not work.
For example

SELECT * FROM "table"

Select allOpen in new window

will work but not

SELECT * FROM table

Select allOpen in new window

Regards,
     Tomas Helgi

REINDEX system "alfresco";

still the same!

but I tried the postgres DB however it works.


so why?

I already drag and drop the name alfresco to the command windows and run it accordingly

Hi,

The screenshot you posted tells me that you are connected to the postgres database (the main cluster database - name in bold)  and not alfresco. Create another connection and set alfresco as the database. The connection config should point to alfresco. Then run the reindex alfresco commands using that connection.

Regards,
   Tomas Helgi

Tomas,

please help on this:

https://www.experts-exchange.com/questions/29230479/historical-performance-record-of-PostgreSQL-9-3-6.html

Tomas,

"work on all versions as intended and designed. I'm running these commands on both version 9.x, 11.x and 13.x without problems."

I found out why it doesn't works on my side now, is the management tools , if idle for a while, the command SEEMS can't find the SAME connection, and I need to open ONE MORE connection console and run that command:

 REINDEX  table alf_node_properties; --314GB tables.
REINDEX  table alf_node  ;    --80GB tables
REINDEX  table alf_child_assoc;   --17GB tables.
 REINDEX SYSTEM  alfresco;

  VACUUM(FULL, ANALYZE, VERBOSE);
  

Select allOpen in new window

BTW, in MS SQL to open a dedicated user database we use : use <database> go;

how about in postgreSQL?  

is there any command/script for postgreSQL to say only to rebuild the index of a table if the fragmentation is >30% and pass that process if not ?

Hi,

how about in postgreSQL?  

You open a dedicated connection to a database in PostgreSQL. The USE SQL keyword is only used and works in SQLServer, MySQL and MariaDB. See the relevent SQL keywords list in the DBMS manuals.

is there any command/script for postgreSQL to say only to rebuild the index of a table if the fragmentation is >30% and pass that process if not ? 

By querying the stat catalog tables you can easily build a script that executes VACUUM / REINDEX commands if a certain fragmentation threshold is reached.

Take a look at these links
https://wiki.postgresql.org/wiki/Show_database_bloat
https://fatdba.com/2018/09/04/fragmentation-or-bloating-in-postgresql-how-to-identify-and-fix-it-using-db-vacuuming/

Regards,
     Tomas Helgi

You open a dedicated connection to a database in PostgreSQL. The USE SQL keyword is only used and works in SQLServer, MySQL and MariaDB. See the relevent SQL keywords list in the DBMS manuals. 

you mean we can ONLY do it in connection string level ? 

Hi,

you mean we can ONLY do it in connection string level ?                                   

Yes, as stated in the PostgreSQL manual.

Regards,
    Tomas Helgi

tks.

this means when we try to AUTOMATE that, I can use windows task scheduler to with .bat file with that connection string and run that index rebuild.

the index rebuild so far so good ! so now just connection string does matter.

tks and I might come back later.

I have a new thing from here, I rebuild 2 x largest table of the third party application and it has error and might be this is ONE of the reason the application is slow sometimes and it has a frequent running query JOIN them:

 REINDEX  table alf_node_properties;  --
 -- SQL Error [23505]: ERROR: could not create unique index "alf_node_properties_pkey"
  Detail: Key (node_id, qname_id, list_index, locale_id)=(51060016, 28, -1, 1) is duplicated.
 

REINDEX  table alf_node  ;  --
SQL Error [23505]: ERROR: could not create unique index "alf_node_pkey"
  Detail: Key (id)=(844) is duplicated.

anyway to let index rebuild keep going and ignore that?

and this one:

REINDEX  table alf_child_assoc;  

result :
SQL Error [53100]: ERROR: could not write block 2853940 of temporary file: No space left on device
  Hint: Perhaps out of disk space?

we will have a lot of space left, why ?

Hi,
On what disk do you store the PGDATA directory ?
Run this query to see the total-size of indexes on that table.

SELECT    pg_size_pretty (pg_indexes_size('alf_child_ass'));

Select allOpen in new window

You will need at least that size of free space on your disk and more for other workload.

Regards,
    Tomas Helgi

SELECT    pg_size_pretty (pg_indexes_size('alf_child_ass')); 

7905 MB

and we have:

What about the table itself?

SELECT    pg_size_pretty (pg_relation_size('alf_child_ass
'));

Select allOpen in new window

Can you successfully run REINDEX on a single index of that table instead of all indexes for that table ?

REINDEX INDEX the_indexname;

Select allOpen in new window

Regards,
   Tomas Helgi

after I run :

VACUUM(FULL, ANALYZE, VERBOSE);

for the DB I want to maintenance, the total sizes cut by 1/2, this is a good signal! and I also delete some backup file which is for test only and it make the driver has more free space,  from 103GB -> 688GB left

then I do that again:

REINDEX  table alf_child_assoc; --this is a 2.1 GB table.

and it can finish... ! so FREE space is that important (and 103GB is not enought..... ?!!) for the reindex rebuild do works.

and this:

 REINDEX  table alf_node_properties;  -- -- SQL Error [23505]: ERROR: could not create unique index "alf_node_properties_pkey"
  Detail: Key (node_id, qname_id, list_index, locale_id)=(51060016, 28, -1, 1) is duplicated.
 

REINDEX  table alf_node  ;  --SQL Error [23505]: ERROR: could not create unique index "alf_node_pkey"
  Detail: Key (id)=(844) is duplicated.

any idea on how to solve? 

Hi,

REINDEX  table alf_node_properties;  -- -- SQL Error [23505]: ERROR: could not create unique index "alf_node_properties_pkey"  Detail: Key (node_id, qname_id, list_index, locale_id)=(51060016, 28, -1, 1) is duplicated.
 

REINDEX  table alf_node  ;  --SQL Error [23505]: ERROR: could not create unique index "alf_node_pkey"  Detail: Key (id)=(844) is duplicated.

This means that you have duplicate entries in your table on these keys mentioned in the errors and you need to delete one of the duplicate record in order to be able to create these indexes.
Doing reindex on a busy table can lead to duplicate entries in a table between the time when the reindex process drops a unique index and then tries to recreate it.

Regards,
    Tomas Helgi

Doing reindex on a busy table can lead to duplicate entries in a table between the time when the reindex process drops a unique index and then tries to recreate it.

PostgreSQL is not very good on that? I don't see oracle and MS SQL has this kind of issue. or they just handle it for us behind the scene ? the postgreSQL should lock the table when rebuilding the index and this process already slow down the whole DB for a long time..too long for the locking ..

" you need to delete one of the duplicate record in order to be able to create these indexes. "

so before the index process I have to do it.? and any query can help to find this out ? I want to do all that before we backup from 9.3.6 and restore it to 11.14 and 13.4/13.5

it should have primary key to make sure no duplicate entries are here... 

I am sorry sir, will VACUUM(FULL, ANALYZE, VERBOSE);, once cancel during the process if we found it is slow, corrupt any data?

Any experience, on the failure of this process corrupt data and make the DB in bad state?