I have a very serious problem with PostgreSQL 14. In practice, every 5 days or so my tables, or rather some, are corrupted. When I launch a simple query, it does not execute and it remains stuck.`enter code here`
This DB receives many write-to's in a day. I think the table is corrupted. I would like to know why this happens if anyone has any ideas. Thank you all in advance. I paste here an analyze that I did where I try to get the sum of some quantities in a month,
"QUERY PLAN" "Aggregate (cost=109583.25..109583.26 rows=1 width=8) (actual time=138615.457..138628.145 rows=1 loops=1)" " -> Gather (cost=69650.26..109583.25 rows=2 width=0) (actual time=138569.160..138626.750 rows=17950 loops=1)" " Workers Planned: 2" " Workers Launched: 2" " -> Nested Loop (cost=68650.26..108583.05 rows=1 width=0) (actual time=138556.409..138599.546 rows=5983 loops=3)" " Join Filter: (m.causale_id = caus.id)" " Rows Removed by Join Filter: 154159" " -> Parallel Hash Join (cost=68650.26..108581.15 rows=1 width=8) (actual time=138556.373..138560.611 rows=5983 loops=3)" " Hash Cond: (rmdt.rigamovimentodettaglio_id = rmd.id)" " -> Parallel Seq Scan on rigamovimentodettagliotaglia rmdt (cost=0.00..36254.00 rows=980500 width=8) (actual time=0.006..73.938 rows=784833 loops=3)" " -> Parallel Hash (cost=68650.24..68650.24 rows=1 width=16) (actual time=138344.297..138344.300 rows=3414 loops=3)" " Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 760kB" " -> Hash Join (cost=35906.91..68650.24 rows=1 width=16) (actual time=137726.906..137728.106 rows=3414 loops=3)" " Hash Cond: (rmd.rigamovimento_id = r.id)" " -> Parallel Seq Scan on rigamovimentodettaglio rmd (cost=0.00..29574.42 rows=845042 width=16) (actual time=0.008..42.578 rows=676262 loops=3)" " -> Hash (cost=35906.88..35906.88 rows=2 width=16) (actual time=137605.446..137605.448 rows=9221 loops=3)" " Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 561kB" " -> Nested Loop (cost=0.85..35906.88 rows=2 width=16) (actual time=39.893..137593.589 rows=9221 loops=3)" " -> Index Scan using ukm9njdtmreayrcdjfln8chor0r on movimento m (cost=0.42..12620.62 rows=1 width=16) (actual time=0.085..32.738 rows=3290 loops=3)" " Index Cond: ((datamovimento >= '2023-01-01'::date) AND (datamovimento <= '2023-02-01'::date))" " -> Index Scan using uk_6iar6gjj0t1e0tq5g60a93wou on rigamovimento r (cost=0.43..23286.12 rows=15 width=16) (actual time=18.317..41.798 rows=3 loops=9870)" " Index Cond: (movimento_id = m.id)" " -> Seq Scan on causale caus (cost=0.00..1.40 rows=40 width=8) (actual time=0.001..0.003 rows=27 loops=17950)" "Planning Time: 0.531 ms" "JIT:" " Functions: 86" " Options: Inlining false, Optimization false, Expressions true, Deforming true" " Timing: Generation 7.836 ms, Inlining 0.000 ms, Optimization 3.694 ms, Emission 54.204 ms, Total 65.733 ms" "Execution Time: 138630.701 ms"
this is my configuration in db:
"allow_in_place_tablespaces" "off"
"allow_system_table_mods" "off"
"application_name" "pgAdmin 4 - CONN:8842897"
"archive_cleanup_command"
"archive_command" "(disabled)"
"archive_mode" "off"
"archive_timeout" "0"
"array_nulls" "on"
"authentication_timeout" "1min"
"autovacuum" "on"
"autovacuum_analyze_scale_factor" "0.1"
"autovacuum_analyze_threshold" "50"
"autovacuum_freeze_max_age" "200000000"
"autovacuum_max_workers" "3"
"autovacuum_multixact_freeze_max_age" "400000000"
"autovacuum_naptime" "1min"
"autovacuum_vacuum_cost_delay" "2ms"
"autovacuum_vacuum_cost_limit" "-1"
"autovacuum_vacuum_insert_scale_factor" "0.2"
"autovacuum_vacuum_insert_threshold" "1000"
"autovacuum_vacuum_scale_factor" "0.2"
"autovacuum_vacuum_threshold" "50"
"autovacuum_work_mem" "-1"
"backend_flush_after" "0"
"backslash_quote" "safe_encoding"
"backtrace_functions"
"bgwriter_delay" "200ms"
"bgwriter_flush_after" "512kB"
"bgwriter_lru_maxpages" "100"
"bgwriter_lru_multiplier" "2"
"block_size" "8192"
"bonjour" "off"
"bonjour_name"
"bytea_output" "hex"
"check_function_bodies" "on"
"checkpoint_completion_target" "0.9"
"checkpoint_flush_after" "256kB"
"checkpoint_timeout" "5min"
"checkpoint_warning" "30s"
"client_connection_check_interval" "0"
"client_encoding" "UNICODE"
"client_min_messages" "notice"
"cluster_name" "14/main"
"commit_delay" "0"
"commit_siblings" "5"
"compute_query_id" "auto"
"config_file" "/etc/postgresql/14/main/postgresql.conf"
"constraint_exclusion" "partition"
"cpu_index_tuple_cost" "0.005"
"cpu_operator_cost" "0.0025"
"cpu_tuple_cost" "0.01"
"cursor_tuple_fraction" "0.1"
"data_checksums" "off"
"data_directory" "/var/lib/postgresql/14/main"
"data_directory_mode" "0700"
"data_sync_retry" "off"
"DateStyle" "ISO, MDY"
"db_user_namespace" "off"
"deadlock_timeout" "1s"
"debug_assertions" "off"
"debug_discard_caches" "0"
"debug_pretty_print" "on"
"debug_print_parse" "off"
"debug_print_plan" "off"
"debug_print_rewritten" "off"
"default_statistics_target" "100"
"default_table_access_method" "heap"
"default_tablespace"
"default_text_search_config" "pg_catalog.english"
"default_toast_compression" "pglz"
"default_transaction_deferrable" "off"
"default_transaction_isolation" "read committed"
"default_transaction_read_only" "off"
"dynamic_library_path" "$libdir"
"dynamic_shared_memory_type" "posix"
"effective_cache_size" "3GB"
"effective_io_concurrency" "200"
"enable_async_append" "on"
"enable_bitmapscan" "on"
"enable_gathermerge" "on"
"enable_hashagg" "on"
"enable_hashjoin" "on"
"enable_incremental_sort" "on"
"enable_indexonlyscan" "on"
"enable_indexscan" "on"
"enable_material" "on"
"enable_memoize" "on"
"enable_mergejoin" "on"
"enable_nestloop" "on"
"enable_parallel_append" "on"
"enable_parallel_hash" "on"
"enable_partition_pruning" "on"
"enable_partitionwise_aggregate" "off"
"enable_partitionwise_join" "off"
"enable_seqscan" "on"
"enable_sort" "on"
"enable_tidscan" "on"
"escape_string_warning" "on"
"event_source" "PostgreSQL"
"exit_on_error" "off"
"extension_destdir"
"external_pid_file" "/var/run/postgresql/14-main.pid"
"extra_float_digits" "1"
"force_parallel_mode" "off"
"from_collapse_limit" "8"
"fsync" "on"
"full_page_writes" "on"
"geqo" "on"
"geqo_effort" "5"
"geqo_generations" "0"
"geqo_pool_size" "0"
"geqo_seed" "0"
"geqo_selection_bias" "2"
"geqo_threshold" "12"
"gin_fuzzy_search_limit" "0"
"gin_pending_list_limit" "4MB"
"hash_mem_multiplier" "1"
"hba_file" "/etc/postgresql/14/main/pg_hba.conf"
"hot_standby" "on"
"hot_standby_feedback" "off"
"huge_page_size" "0"
"huge_pages" "try"
"ident_file" "/etc/postgresql/14/main/pg_ident.conf"
"idle_in_transaction_session_timeout" "0"
"idle_session_timeout" "0"
"ignore_checksum_failure" "off"
"ignore_invalid_pages" "off"
"ignore_system_indexes" "off"
"in_hot_standby" "off"
"integer_datetimes" "on"
"IntervalStyle" "postgres"
"jit" "on"
"jit_above_cost" "100000"
"jit_debugging_support" "off"
"jit_dump_bitcode" "off"
"jit_expressions" "on"
"jit_inline_above_cost" "500000"
"jit_optimize_above_cost" "500000"
"jit_profiling_support" "off"
"jit_provider" "llvmjit"
"jit_tuple_deforming" "on"
"join_collapse_limit" "8"
"krb_caseins_users" "off"
"krb_server_keyfile" "FILE:/etc/postgresql-common/krb5.keytab"
"lc_collate" "C.UTF-8"
"lc_ctype" "C.UTF-8"
"lc_messages" "C.UTF-8"
"lc_monetary" "C.UTF-8"
"lc_numeric" "C.UTF-8"
"lc_time" "C.UTF-8"
"listen_addresses" "*"
"lo_compat_privileges" "off"
"local_preload_libraries"
"lock_timeout" "0"
"log_autovacuum_min_duration" "-1"
"log_checkpoints" "off"
"log_connections" "off"
"log_destination" "stderr"
"log_directory" "log"
"log_disconnections" "off"
"log_duration" "off"
"log_error_verbosity" "default"
"log_executor_stats" "off"
"log_file_mode" "0600"
"log_filename" "postgresql-%Y-%m-%d_%H%M%S.log"
"log_hostname" "off"
"log_line_prefix" "%m [%p] %q%u@%d "
"log_lock_waits" "off"
"log_min_duration_sample" "-1"
"log_min_duration_statement" "-1"
"log_min_error_statement" "error"
"log_min_messages" "warning"
"log_parameter_max_length" "-1"
"log_parameter_max_length_on_error" "0"
"log_parser_stats" "off"
"log_planner_stats" "off"
"log_recovery_conflict_waits" "off"
"log_replication_commands" "off"
"log_rotation_age" "1d"
"log_rotation_size" "10MB"
"log_statement" "none"
"log_statement_sample_rate" "1"
"log_statement_stats" "off"
"log_temp_files" "-1"
"log_timezone" "Europe/Rome"
"log_transaction_sample_rate" "0"
"log_truncate_on_rotation" "off"
"logging_collector" "off"
"logical_decoding_work_mem" "64MB"
"maintenance_io_concurrency" "10"
"maintenance_work_mem" "512MB"
"max_connections" "200"
"max_files_per_process" "1000"
"max_function_args" "100"
"max_identifier_length" "63"
"max_index_keys" "32"
"max_locks_per_transaction" "64"
"max_logical_replication_workers" "4"
"max_parallel_maintenance_workers" "2"
"max_parallel_workers" "4"
"max_parallel_workers_per_gather" "2"
"max_pred_locks_per_page" "2"
"max_pred_locks_per_relation" "-2"
"max_pred_locks_per_transaction" "64"
"max_prepared_transactions" "0"
"max_replication_slots" "10"
"max_slot_wal_keep_size" "-1"
"max_stack_depth" "2MB"
"max_standby_archive_delay" "30s"
"max_standby_streaming_delay" "30s"
"max_sync_workers_per_subscription" "2"
"max_wal_senders" "10"
"max_wal_size" "4GB"
"max_worker_processes" "4"
"min_dynamic_shared_memory" "0"
"min_parallel_index_scan_size" "512kB"
"min_parallel_table_scan_size" "8MB"
"min_wal_size" "1GB"
"old_snapshot_threshold" "-1"
"parallel_leader_participation" "on"
"parallel_setup_cost" "1000"
"parallel_tuple_cost" "0.1"
"password_encryption" "scram-sha-256"
"plan_cache_mode" "auto"
"port" "5432"
"post_auth_delay" "0"
"pre_auth_delay" "0"
"primary_conninfo"
"primary_slot_name"
"promote_trigger_file"
"quote_all_identifiers" "off"
"random_page_cost" "1.1"
"recovery_end_command"
"recovery_init_sync_method" "fsync"
"recovery_min_apply_delay" "0"
"recovery_target"
"recovery_target_action" "pause"
"recovery_target_inclusive" "on"
"recovery_target_lsn"
"recovery_target_name"
"recovery_target_time"
"recovery_target_timeline" "latest"
"recovery_target_xid"
"remove_temp_files_after_crash" "on"
"restart_after_crash" "on"
"restore_command"
"row_security" "on"
"search_path" """$user"", public"
"segment_size" "1GB"
"seq_page_cost" "1"
"server_encoding" "UTF8"
"server_version" "14.6 (Ubuntu 14.6-1.pgdg18.04+1)"
"server_version_num" "140006"
"session_preload_libraries"
"session_replication_role" "origin"
"shared_buffers" "1GB"
"shared_memory_type" "mmap"
"shared_preload_libraries"
"ssl" "on"
"ssl_ca_file"
"ssl_cert_file" "/etc/ssl/certs/ssl-cert-snakeoil.pem"
"ssl_ciphers" "HIGH:MEDIUM:+3DES:!aNULL"
"ssl_crl_dir"
"ssl_crl_file"
"ssl_dh_params_file"
"ssl_ecdh_curve" "prime256v1"
"ssl_key_file" "/etc/ssl/private/ssl-cert-snakeoil.key"
"ssl_library" "OpenSSL"
"ssl_max_protocol_version"
"ssl_min_protocol_version" "TLSv1.2"
"ssl_passphrase_command"
"ssl_passphrase_command_supports_reload" "off"
"ssl_prefer_server_ciphers" "on"
"standard_conforming_strings" "on"
"statement_timeout" "0"
"stats_temp_directory" "/var/run/postgresql/14-main.pg_stat_tmp"
"superuser_reserved_connections" "3"
"synchronize_seqscans" "on"
"synchronous_commit" "on"
"synchronous_standby_names"
"syslog_facility" "local0"
"syslog_ident" "postgres"
"syslog_sequence_numbers" "on"
"syslog_split_messages" "on"
"tcp_keepalives_count" "9"
"tcp_keepalives_idle" "7200"
"tcp_keepalives_interval" "75"
"tcp_user_timeout" "0"
"temp_buffers" "8MB"
"temp_file_limit" "-1"
"temp_tablespaces"
"TimeZone" "Europe/Rome"
"timezone_abbreviations" "Default"
"trace_notify" "off"
"trace_recovery_messages" "log"
"trace_sort" "off"
"track_activities" "on"
"track_activity_query_size" "1kB"
"track_commit_timestamp" "off"
"track_counts" "on"
"track_functions" "none"
"track_io_timing" "off"
"track_wal_io_timing" "off"
"transaction_deferrable" "off"
"transaction_isolation" "read committed"
"transaction_read_only" "off"
"transform_null_equals" "off"
"unix_socket_directories" "/var/run/postgresql"
"unix_socket_group"
"unix_socket_permissions" "0777"
"update_process_title" "on"
"vacuum_cost_delay" "0"
"vacuum_cost_limit" "200"
"vacuum_cost_page_dirty" "20"
"vacuum_cost_page_hit" "1"
"vacuum_cost_page_miss" "2"
"vacuum_defer_cleanup_age" "0"
"vacuum_failsafe_age" "1600000000"
"vacuum_freeze_min_age" "50000000"
"vacuum_freeze_table_age" "150000000"
"vacuum_multixact_failsafe_age" "1600000000"
"vacuum_multixact_freeze_min_age" "5000000"
"vacuum_multixact_freeze_table_age" "150000000"
"wal_block_size" "8192"
"wal_buffers" "16MB"
"wal_compression" "off"
"wal_consistency_checking"
"wal_init_zero" "on"
"wal_keep_size" "0"
"wal_level" "replica"
"wal_log_hints" "off"
"wal_receiver_create_temp_slot" "off"
"wal_receiver_status_interval" "10s"
"wal_receiver_timeout" "1min"
"wal_recycle" "on"
"wal_retrieve_retry_interval" "5s"
"wal_segment_size" "16MB"
"wal_sender_timeout" "1min"
"wal_skip_threshold" "2MB"
"wal_sync_method" "fdatasync"
"wal_writer_delay" "200ms"
"wal_writer_flush_after" "1MB"
"work_mem" "2621kB"
"xmlbinary" "base64"
"xmloption" "content"
"zero_damaged_pages" "off"
