One big table or split data? Writing data. From disk point of view. With a good storage (GBs/s, writing speed)

From: "Sam R(dot)" <samruohola(at)yahoo(dot)com>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: One big table or split data? Writing data. From disk point of view. With a good storage (GBs/s, writing speed)
Date: 2018-10-12 16:27:45
Message-ID: 1288355243.8943320.1539361665724@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi!

Could someone discuss about following? It would be great to hear comments!
There is a good storage. According to "fio", write speed could be e.g. 3 GB/s. (It is First time using the command for me, so I am not certain of the real speed with "fio". E.g. with --bs=100m, direct=1, in fio. The measurement result may be faulty also. So, checking still.)
Currently there is one table without partitioning. The table contains json data. In PostgreSQL, in Linux.
Write speed can be e.g. 300 - 600 MB/s, through PostgreSQL. Measured with dstat while inserting. Shared buffers is large is PostgreSQL.
With a storage/disk which "scales", is there some way to write faster to the disk in the system through PostgreSQL?
Inside same server.
Does splitting data help? Partitioned table / splitting to smaller tables? Should I test it?
Change settings somewhere? Block sizes? 8 KB / 16 KB, ... "Dangerous" to change?
2nd question, sharding:
If the storage / "disk" scales, could better *disk writing speed* be achieved (in total) with sharding kind of splitting of data? (Same NAS storage, which scales, in use in all shards.)Sharding or use only one server? From pure disk writing speed point of view.

BR Sam

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dinesh Chandra 12108 2018-10-15 05:42:28 Import csv in PostgreSQL
Previous Message Mariel Cherkassky 2018-10-11 07:58:06 Re: does work_mem is used on temp tables?