Hi Justin,
Thanks for response.
Unfortunately we will not be able to migrate to PG12 any time soon.
- There is only one index (unique index btree) on 'txnID' (i.e. transaction ID) character varying(36). Which we are creating on each partition.
- Our use case is limited to simple selects (we don't join with the other tables) however, we are expecting ~70 million records inserted per day and there would be couple of updates on each records where average record size would be ~ 1.5 KB.
- Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance.
- We need to look current partition and previous partition for all of our use-cases/queries.
Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).
Thanks and Regards,
Ravi Garg,
Mob : +91-98930-66610
On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby <pryzby@xxxxxxxxxxxxx> wrote:
On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> Hi,
> I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
That's probably to be expected under pg11:
https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher as more partitions are added
> There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
How large are the partitions and how many indexes each, and how large are they?
Each partition will be stat()ed and each index will be open()ed and read() for
every query. This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/
--
Justin
> Hi,
> I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
That's probably to be expected under pg11:
https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher as more partitions are added
> There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
How large are the partitions and how many indexes each, and how large are they?
Each partition will be stat()ed and each index will be open()ed and read() for
every query. This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/
--
Justin
postgres=# select name, setting from pg_settings order by name desc;
name | setting
----------------------------------------+-----------------------------------------
zero_damaged_pages | off
xmloption | content
xmlbinary | base64
work_mem | 4096
wal_writer_flush_after | 128
wal_writer_delay | 200
wal_sync_method | fdatasync
wal_sender_timeout | 60000
wal_segment_size | 1073741824
wal_retrieve_retry_interval | 5000
wal_receiver_timeout | 60000
wal_receiver_status_interval | 10
wal_log_hints | on
wal_level | replica
wal_keep_segments | 50
wal_consistency_checking |
wal_compression | off
wal_buffers | 8192
wal_block_size | 8192
vacuum_multixact_freeze_table_age | 150000000
vacuum_multixact_freeze_min_age | 5000000
vacuum_freeze_table_age | 150000000
vacuum_freeze_min_age | 50000000
vacuum_defer_cleanup_age | 0
vacuum_cost_page_miss | 10
vacuum_cost_page_hit | 1
vacuum_cost_page_dirty | 20
vacuum_cost_limit | 800
vacuum_cost_delay | 0
vacuum_cleanup_index_scale_factor | 0.1
update_process_title | on
unix_socket_permissions | 0777
unix_socket_group |
unix_socket_directories | /tmp
transform_null_equals | off
transaction_read_only | off
transaction_isolation | read committed
transaction_deferrable | off
track_io_timing | on
track_functions | all
track_counts | on
track_commit_timestamp | off
track_activity_query_size | 1024
track_activities | on
trace_sort | off
trace_recovery_messages | log
trace_notify | off
timezone_abbreviations | Default
TimeZone | UTC
temp_tablespaces |
temp_file_limit | -1
temp_buffers | 1024
tcp_keepalives_interval | 75
tcp_keepalives_idle | 7200
tcp_keepalives_count | 9
syslog_split_messages | on
syslog_sequence_numbers | on
syslog_ident | postgres
syslog_facility | local0
synchronous_standby_names |
synchronous_commit | on
synchronize_seqscans | on
superuser_reserved_connections | 3
stats_temp_directory | pg_stat_tmp
statement_timeout | 0
standard_conforming_strings | on
ssl_prefer_server_ciphers | on
ssl_passphrase_command_supports_reload | off
ssl_passphrase_command |
ssl_key_file | server.key
ssl_ecdh_curve | prime256v1
ssl_dh_params_file |
ssl_crl_file |
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL
ssl_cert_file | server.crt
ssl_ca_file |
ssl | off
shared_preload_libraries |
shared_buffers | 1048576
session_replication_role | origin
session_preload_libraries |
server_version_num | 110007
server_version | 11.7
server_encoding | UTF8
seq_page_cost | 1
segment_size | 131072
search_path | "$user", public
row_security | on
restart_after_crash | on
random_page_cost | 2
quote_all_identifiers | off
pre_auth_delay | 0
post_auth_delay | 0
port | 5432
password_encryption | md5
parallel_tuple_cost | 0.1
parallel_setup_cost | 1000
parallel_leader_participation | on
operator_precedence_warning | off
old_snapshot_threshold | -1
min_wal_size | 20480
min_parallel_table_scan_size | 1024
min_parallel_index_scan_size | 64
max_worker_processes | 8
max_wal_size | 61440
max_wal_senders | 10
max_sync_workers_per_subscription | 2
max_standby_streaming_delay | 30000
max_standby_archive_delay | 30000
max_stack_depth | 2048
max_replication_slots | 10
max_prepared_transactions | 0
max_pred_locks_per_transaction | 64
max_pred_locks_per_relation | -2
max_pred_locks_per_page | 2
max_parallel_workers_per_gather | 2
max_parallel_workers | 8
max_parallel_maintenance_workers | 2
max_logical_replication_workers | 6
max_locks_per_transaction | 64
max_index_keys | 32
max_identifier_length | 63
max_function_args | 100
max_files_per_process | 1000
max_connections | 3000
maintenance_work_mem | 1048576
log_truncate_on_rotation | on
log_timezone | UTC
log_temp_files | 0
log_statement_stats | off
log_statement | mod
log_rotation_size | 0
log_rotation_age | 1440
log_replication_commands | off
log_planner_stats | off
log_parser_stats | off
log_min_messages | warning
log_min_error_statement | error
log_min_duration_statement | 0
log_lock_waits | on
log_line_prefix | [%p-%s-%c-%l-%h-%u-%d-%a-%m]
log_hostname | off
logging_collector | on
log_filename | postgresql-%a.log
log_file_mode | 0600
log_executor_stats | off
log_error_verbosity | default
log_duration | on
log_disconnections | on
log_directory | /u02/pglogs
log_destination | stderr
log_connections | on
log_checkpoints | on
log_autovacuum_min_duration | 0
lo_compat_privileges | off
lock_timeout | 0
local_preload_libraries |
listen_addresses | 0.0.0.0
lc_time | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_messages | C
lc_ctype | en_US.UTF-8
lc_collate | en_US.UTF-8
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab
krb_caseins_users | off
join_collapse_limit | 8
jit_tuple_deforming | on
jit_provider | llvmjit
jit_profiling_support | off
jit_optimize_above_cost | 500000
jit_inline_above_cost | 500000
jit_expressions | on
jit_dump_bitcode | off
jit_debugging_support | off
jit_above_cost | 100000
jit | off
IntervalStyle | postgres
integer_datetimes | on
ignore_system_indexes | off
ignore_checksum_failure | off
idle_in_transaction_session_timeout | 0
ident_file | /opt/PostgreSQL/11/data/pg_ident.conf
huge_pages | try
hot_standby_feedback | off
hot_standby | on
hba_file | /opt/PostgreSQL/11/data/pg_hba.conf
gin_pending_list_limit | 4096
gin_fuzzy_search_limit | 0
geqo_threshold | 12
geqo_selection_bias | 2
geqo_seed | 0
geqo_pool_size | 0
geqo_generations | 0
geqo_effort | 5
geqo | on
full_page_writes | on
fsync | on
from_collapse_limit | 8
force_parallel_mode | off
extra_float_digits | 0
external_pid_file |
exit_on_error | off
event_source | PostgreSQL
escape_string_warning | on
enable_tidscan | on
enable_sort | on
enable_seqscan | on
enable_partitionwise_join | off
enable_partitionwise_aggregate | off
enable_partition_pruning | on
enable_parallel_hash | on
enable_parallel_append | on
enable_nestloop | on
enable_mergejoin | on
enable_material | on
enable_indexscan | on
enable_indexonlyscan | on
enable_hashjoin | on
enable_hashagg | on
enable_gathermerge | on
enable_bitmapscan | on
effective_io_concurrency | 1
effective_cache_size | 524288
dynamic_shared_memory_type | posix
dynamic_library_path | $libdir
default_with_oids | off
default_transaction_read_only | off
default_transaction_isolation | read committed
default_transaction_deferrable | off
default_text_search_config | pg_catalog.english
default_tablespace |
default_statistics_target | 5000
debug_print_rewritten | off
debug_print_plan | off
debug_print_parse | off
debug_pretty_print | on
debug_assertions | off
deadlock_timeout | 1000
db_user_namespace | off
DateStyle | ISO, MDY
data_sync_retry | off
data_directory_mode | 0700
data_directory | /opt/PostgreSQL/11/data
data_checksums | off
cursor_tuple_fraction | 0.1
cpu_tuple_cost | 0.03
cpu_operator_cost | 0.0025
cpu_index_tuple_cost | 0.005
constraint_exclusion | partition
config_file | /opt/PostgreSQL/11/data/postgresql.conf
commit_siblings | 5
commit_delay | 0
cluster_name | mracluster
client_min_messages | notice
client_encoding | UTF8
checkpoint_warning | 60
checkpoint_timeout | 300
checkpoint_flush_after | 32
checkpoint_completion_target | 0.9
check_function_bodies | on
bytea_output | hex
bonjour_name |
bonjour | off
block_size | 8192
bgwriter_lru_multiplier | 4
bgwriter_lru_maxpages | 1000
bgwriter_flush_after | 64
bgwriter_delay | 100
backslash_quote | safe_encoding
backend_flush_after | 0
autovacuum_work_mem | -1
autovacuum_vacuum_threshold | 50
autovacuum_vacuum_scale_factor | 0.05
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_cost_delay | 10
autovacuum_naptime | 5
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_max_workers | 3
autovacuum_freeze_max_age | 200000000
autovacuum_analyze_threshold | 50
autovacuum_analyze_scale_factor | 0.05
autovacuum | on
authentication_timeout | 60
array_nulls | on
archive_timeout | 0
archive_mode | off
archive_command | (disabled)
application_name | psql
allow_system_table_mods | off
(290 rows)
