Ytukyg

I'm a freshman with Postgresql, so any hints or tips are appreciated.

My program is very small. It receives data from socket, and then insert them into a table that has no index/foreign key/constrain/Text Field. In the fact, it has a few of numeric fields only.

My data from network is coming continuously at a rate about 100 to 1000 records per seconds. My Postgresql is not optimized at all, and every thing of it is running at the default values.

The trouble is
The time consumption of every db-insert varys in a very wide range. The maximum time of a single insert might be 10s, and the minimum one might be 100us, while the average one is about 900us.

Even though I can increase the length of buffer, to avoid the buffer be exhausted when we wait for a too long insert to finish, I think this is NOT the correct solution, because regardless how long the buffer length is, we might encounter a more slower insert.
I guesss it is abnormal in the world of Postgresql(10s for writing a tiny plain table), but I have no idea to solve it, so I ask it here to hope someone point out my mistake or some optimizing methods.

env: Centos 7.5 64bit, Postgresql 10, gcc 8.2.0 and latest pqxx lib.

This is my pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD



# "local" is for Unix domain socket connections only

local   all             all                                     trust



# IPv4 local connections:

host    all             all             127.0.0.1/32            trust



# IPv4 incoming connections:

host    all             all             all                     password



# IPv6 local connections:

host    all             all             ::1/128                 ident



# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            ident

host    replication     all             ::1/128                 ident

and the postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;

max_connections = 100           # (change requires restart)

shared_buffers = 128MB          # min 128kB

dynamic_shared_memory_type = posix  # the default is the first option

log_destination = 'stderr'      # Valid values are combinations of

logging_collector = on          # Enable capturing of stderr and csvlog

log_directory = 'log'           # directory where log files are written,

log_filename = 'postgresql-%a.log'  # log file name pattern,

log_truncate_on_rotation = on       # If on, an existing log file with the

log_rotation_age = 1d           # Automatic rotation of logfiles will

log_rotation_size = 0           # Automatic rotation of logfiles will

log_line_prefix = '%m [%p] '        # special values:

log_timezone = 'PRC'

datestyle = 'iso, ymd'

timezone = 'PRC'

lc_messages = 'zh_CN.UTF-8'         # locale for system error message

lc_monetary = 'zh_CN.UTF-8'         # locale for monetary formatting

lc_numeric = 'zh_CN.UTF-8'          # locale for number formatting

lc_time = 'zh_CN.UTF-8'             # locale for time formatting

default_text_search_config = 'pg_catalog.simple'

Sorry for my English, I'm a Chinese. I appreciate any patience to read my poor English.
Thanks!