Re: Performance degradation after upgrading from 9.5 to 14
I'm not sure, does it helps you but read this: https://www.cybertec-postgresql.com/en/b-tree-index-improvements-in-postgresql-v12/ "Since upgrading with pg_upgrade does not change the data files, indexes will still be in version 3 after an upgrade" I reindexed all my database, when did upgrade pg<12 -> pg>=12 if pg_upgrade was a tool. exp-imp for upgrade does not need reindex. br Kaido On Wed, 17 Apr 2024 at 20:39, Marcin Giedz wrote: > how about this: > > jit = off ? > > Marcin > > > On Wed, 17 Apr 2024 at 19:33, Johnathan Tiamoh > wrote: > >> 1) How did you upgrade? pg_dump or pg_upgrade? >> >> I use pg_ugrade with kink option. >> >> 2) Did you run ANALYZE to collect statistics after the upgrade? >> >> >> Yes. I ran vacuumdb-analyze in stages after the upgrade >> >> 3) Did you transfer the configuration, or did you just create a new >> cluster with the default values? >> >> I transfer the configuration >> >> 4) What exactly is slower? Queries? Inserts? >> >> queries >> >> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >> >> it's more than 5 times slower than before. Very high load averages >> >> On Wed, Apr 17, 2024 at 1:25 PM Tomas Vondra < >> tomas.von...@enterprisedb.com> wrote: >> >>> On 4/17/24 19:13, Johnathan Tiamoh wrote: >>> > Hello, >>> > >>> > >>> > I performed an upgrade from postgresql-9.5 to postgresql-14 and the >>> > performance has degraded drastically. >>> > >>> > Please, is they any advice on getting performance back ? >>> > >>> >>> There's very little practical advice we can provide based on this >>> report, because it's missing any useful details. There's a number of >>> things that might have caused this, but we'd have to speculate. >>> >>> For example: >>> >>> 1) How did you upgrade? pg_dump or pg_upgrade? >>> >>> 2) Did you run ANALYZE to collect statistics after the upgrade? >>> >>> 3) Did you transfer the configuration, or did you just create a new >>> cluster with the default values? >>> >>> 4) What exactly is slower? Queries? Inserts? >>> >>> 5) Can you quantify the impact? Is it 2x slower? 100x slower? >>> >>> >>> regards >>> >>> >>> -- >>> Tomas Vondra >>> EnterpriseDB: http://www.enterprisedb.com >>> The Enterprise PostgreSQL Company >>> >>
Logging statement having any threat?
Hello All, Its postgres version 15.4 and its RDS, in which our dev team gets the infrastructure code from another third party team which provides us base infrastructure code to build a postgres database, in which we will be able to do change DB parameter values etc whatever is mentioned in the file with possible values. But surprisingly we don't see log_statement there. Below was our requirement, For debugging and evaluating performance we were having pg_stat_statements but it contains aggregated information about all the query execution. But in case just want to debug any point in time issues where the selected few queries were performing bad (may be because of plan change), we were planning to have the auto_explain extension added and set the log_min_duration to ~5 seconds, So that, all the queries going above that time period(5 seconds) will be logged and provide detailed information on the exact point of bottleneck. But we see the log_statement parameter has been removed from the base infrastructure script/terraform script given by the database team here, so that means we will get it as default which is "NONE", which means no statement(SELECT/DML/DDL etc) can be logged. Now when we reach out to the infrastructure team , they are saying these variables(pg_cluster_log_statement,pg_instance_log_statement) were removed due to potential security threat. So I want to understand from experts here , how this is really a security threat and if any option to get this logging enabled (which will help us debug performance issues) at same time addressing the threat too? Regards Lok
Re: Logging statement having any threat?
On 4/20/24 07:02, Lok P wrote: Hello All, Its postgres version 15.4 and its RDS, in which our dev team gets the infrastructure code from another third party team which provides us base infrastructure code to build a postgres database, in which we will be able to do change DB parameter values etc whatever is mentioned in the file with possible values. But surprisingly we don't see log_statement there. Below was our requirement, For debugging and evaluating performance we were having pg_stat_statements but it contains aggregated information about all the query execution. But in case just want to debug any point in time issues where the selected few queries were performing bad (may be because of plan change), we were planning to have the auto_explain extension added and set the log_min_duration to ~5 seconds, So that, all the queries going above that time period(5 seconds) will be logged and provide detailed information on the exact point of bottleneck. But we see the log_statement parameter has been removed from the base infrastructure script/terraform script given by the database team here, so that means we will get it as default which is "NONE", which means no statement(SELECT/DML/DDL etc) can be logged. Have you tried?: https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT " log_statement (enum) <...> The default is none. Only superusers and users with the appropriate SET privilege can change this setting. " Or https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET set_config ( setting_name text, new_value text, is_local boolean ) → text Now when we reach out to the infrastructure team , they are saying these variables(pg_cluster_log_statement,pg_instance_log_statement) were Where are those variables coming from? I can not find them in RDS or Terraform docs. removed due to potential security threat. So I want to understand from experts here , how this is really a security threat and if any option to get this logging enabled (which will help us debug performance issues) at same time addressing the threat too? Regards Lok -- Adrian Klaver adrian.kla...@aklaver.com
Re: Logging statement having any threat?
On 4/20/24 07:02, Lok P wrote: Now when we reach out to the infrastructure team , they are saying these variables(pg_cluster_log_statement,pg_instance_log_statement) were removed due to potential security threat. So I want to understand from experts here , how this is really a security threat and if any option to get this logging enabled (which will help us debug performance issues) at same time addressing the threat too? I should have added to previous post, if you have access to the database the security wall has already been breached. Regards Lok -- Adrian Klaver adrian.kla...@aklaver.com
I wrote a summary of lesser-known Postgres type system information
At PostgresConf 2024, I gave a presentation about PostgreSQL types (thanks to folks on this list for the assist with that). I wrote up a summary of it here: https://lydb.xyz/postgres-types/
