Tjyylli

I’m looking for suggestions to create several different postgresql.conf versions: one optimized for Writing, the second optimized for Reading.

Context

I ETL dozens of very large (multi-GB) text files into PostgreSQL 11. The process is repeated twice a month for some datasets, and monthly for other datasets. Then I replicate the local database tables to a cloud-based server for subscribers to access.

Step 01: Local Processing Server

This is an iMac 27 with 64GB in RAM and a 01 TB SSD disk.
I have an instance of PostgreSQL 11 with the data directory stored in the SSD. This is the "Local SSD Instance".

At this stage I take raw text files and insert the records into PostgreSQL (using either the fantastic pgLoader or my own custom Python code). Then there’s significant post-processing: indexing of fields; many UPDATES; cross-referencing data; and geocoding records.

This process is totally hardware-bound. And I wrote (what I think is) a writing-optimized postgresql.conf file, below. My goal is to optimize PostgreSQL for fast writing (no WAL, huge checkpoint intervals, etc). At this stage I don’t care about crash protection.

In addition, during the ETL process, I use SQL table-creation commands like:

• low FILLFACTOR (“in heavily updated tables smaller fillfactors are appropriate.”)


• CREATE UNLOGGED TABLE

Is there a way to load the entire PostgreSQL application into RAM and perform all operations (raw data loading, indexing, field enrichment) in RAM? Once finished, save data files back to SSD.

Step 02: Transfer to Local Repository

Once a large table is loaded, indexed, and post-processed in Step 01 above I replicate the table to a second local instance of PostgreSQL 11 where the data directory is stored in an external 16 TB Thunderbolt 3 disk, the "Local Thunderbolt 3 Instance". My iMac does not have enough SSD storage to hold all the tables in the SSD.

This is the command I use to transfer a table from "Local SSD Instance" to "Local Thunderbolt 3 Instance":

pg_dump postgresql://<Username>:<Password>@<Source_IP>:<Source_Port>/<Source_Database> -t '<Table_Name>_*' | psql postgresql://<Username>:<Password>@<Target_IP>:<Target_Port>/<Target_Database>

One problem here is that each index is recreated after the actual records are transferred. The re-indexing takes a lot of time. Is there another way to transfer the tables from "Local SSD Instance" to "Local Thunderbolt 3 Instance" without re-indexing every table?

No, I don’t want to transfer tables directly from "Local SSD Instance" to the remote server. I need to have a local repository of all tables as there will be multiple cloud-based instances with different variations of the individual loaded tables.

I read somewhere that transfering a table to a different server somehow "compacts" the data so that I don’t need to run VACUUM on the target table. Is this correct? Or do I still need to run VACUUM after re-indexing the table?

Step 03: Replicate data to cloud-based server

I have set up a "Droplet" in Digital Ocean with 48 GB Memory, 960 GB Disk + 1.66 TB attached storage, running Ubuntu 18.04.2 x64. Here I’m running PostgreSQL 11 as well.

The postgresql.conf file needs to be optimized for writing as I need to transfer over 100 tables with over 1.5 TB of data as quickly as possible. In this step I take individual tables from the "Local Thunderbolt 3 Instance" and transfer the tables to Digital Ocean’s droplet.

The table transfer command I use is:

pg_dump postgresql://<Username>:<Password>@<Source_IP>:<Source_Port>/<Source_Database> -t '<Table_Name>_*' | psql postgresql://<Username>:<Password>@<Target_IP>:<Target_Port>/<Target_Database>

But a problem here is that each index is recreated after the actual records are transferred. The re-indexing takes a lot of time. How can I accelerate the process of transferring tables to the remote server?

Step 04: Optimize cloud-based server for Reading.

Once all the tables have been transferred, I need to change the postgresql.conf file and use a read-optimized configuration.

The read-optimized configuration needs to provide the fastest possible response rates to PostgreSQL queries. Any suggestions?

My Questions:

• How to create a write-optimized postgresql.conf file?


• How to create a read-optimized postgresql.conf file?


• How to transfer tables to different server without re-indexing at the target server?

Thanks.

# Writing-optimized postgresql.conf file:

#

# WAL:

wal_level = minimal

max_wal_size = 10GB

wal_buffers = 16MB

archive_mode = off

max_wal_senders = 0

#

# Memory:

shared_buffers = 1280MB

temp_buffers = 800MB

work_mem = 400MB

maintenance_work_mem = 640MB

dynamic_shared_memory_type = posix

#

autovacuum = off

bonjour = off

checkpoint_completion_target = 0.9

default_statistics_target = 1000

effective_cache_size = 4GB

#

synchronous_commit = off

#

random_page_cost = 1

seq_page_cost = 1