Обсуждение: Re: Slow response to my query

On Thu, 28 Nov 2019 05:35:44 +0100
Goke Aruna <goksie@gmail.com> wrote:

Hi,

> I have a HP DL380 server  g8 with 32GB RAM and 48 *2.5Ghz CPU. Running
> on CentOS Linux 7.4.
>
> I have a postgresql 12 database. One of my tables has 42 columns and
> has about 500million records.
> However, any attempt to select even 1 column from the table take hours
> at least 1.5hours to respond.

Did you test your installation with pgbench?

> Kindly help on documentation on how to optimize it to return the
> response in milliseconds.

Do you have any indexes?

What is your test query?

Jean-Yves

On Thu, 28 Nov 2019 10:38:22 +0100
Goke Aruna <goksie@gmail.com> wrote:

> however, do you have ant specific test to run with the pgbench?

Nope, I don't have the man in mind - read it and adapt your test to your
problem.

BTW, you did not say it, but I suppose you're using rust not SSDz?

> I have about 6 of my columns indexed.

Are they involved in your query?

> I will share the test query once i got the queries from the UI man.

? Without the table and it's indexes structures plus the query, it's like
a car without an engine (or a chauffeur)…

Also, read this:
https://hakibenita.com/be-careful-with-cte-in-postgre-sql
and that:
https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

Jean-Yves

On Fri, 29 Nov 2019 10:01:16 -0500
Keith <keith@keithf4.com> wrote:

> Please do not advise the use of inheritance/trigger based partitioning
> anymore, especially on PG12. Partitioning is now part of core and is
> much more easily managed that way
>
> https://www.postgresql.org/docs/12/ddl-partitioning.html

Thanks Keith, I missed this one :/

JY

Thanks Steve, Jean-Yves, Keith,

This is the explanation of one of my queries:

what is your advise on this.

EXPLAIN SELECT count(*) AS aggregate FROM allcalls;

Finalize Aggregate  (cost=2707819.51..2707819.52 rows=1 width=8)
  ->  Gather  (cost=2707819.30..2707819.51 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=2706819.30..2706819.31 rows=1 width=8)
              ->  Parallel Append  (cost=0.00..2635105.63 rows=28685466 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190603  (cost=0.00..703632.78 rows=8035778 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190611  (cost=0.00..639557.82 rows=7182082 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190601  (cost=0.00..592956.85 rows=6943685 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190602  (cost=0.00..553063.66 rows=6514966 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190530  (cost=0.00..647.68 rows=9768 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190531  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190604  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190605  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190606  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190607  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190608  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190609  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190610  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190612  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190613  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190615  (cost=0.00..10.76 rows=76 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190614  (cost=0.00..10.71 rows=71 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190616  (cost=0.00..10.71 rows=71 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190617  (cost=0.00..10.29 rows=29 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190501  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190502  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190503  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190504  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190505  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190506  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190507  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190508  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190509  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190510  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190511  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190512  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190513  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190514  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190515  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190516  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190517  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190518  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190519  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190520  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190521  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190522  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190523  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190524  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190525  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190526  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190527  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190528  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190529  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190618  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190619  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190620  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190621  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190622  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190623  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190624  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190625  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190626  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190627  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190628  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190629  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190630  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190701  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190702  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190703  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190704  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190705  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190706  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190707  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190708  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190709  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190710  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190711  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190712  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190713  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190714  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190715  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190716  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190717  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190718  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190719  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190720  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190721  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190722  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190723  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190724  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190725  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190726  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190727  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190728  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190729  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190730  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190731  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190801  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190802  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190803  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190804  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190805  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190806  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190807  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190808  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190809  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190810  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190811  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190812  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190813  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190814  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190815  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190816  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190817  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190818  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190819  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190820  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190821  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190822  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190823  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190824  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190825  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190826  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190827  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190828  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190829  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190830  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190831  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190901  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190902  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190903  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190904  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190905  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190906  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190907  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190908  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190909  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190910  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190911  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190912  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190913  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190914  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190915  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190916  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190917  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190918  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190919  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190920  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190921  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190922  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190923  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190924  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190925  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190926  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190927  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190928  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190929  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20190930  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191001  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191002  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191003  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191004  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191005  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191006  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191007  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191008  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191009  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191010  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191011  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191012  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191013  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191014  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191015  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191016  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191017  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191018  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191019  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191020  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191021  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191022  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191023  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191024  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191025  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191026  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191027  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191028  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191029  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191030  (cost=0.00..10.12 rows=12 width=0)
                    ->  Parallel Seq Scan on allcalls_p20191031  (cost=0.00..10.12 rows=12 width=0)

On Fri, Nov 29, 2019 at 4:23 PM Bzzzz <lazyvirus@gmx.com> wrote:

On Fri, 29 Nov 2019 10:01:16 -0500
Keith <keith@keithf4.com> wrote:

> Please do not advise the use of inheritance/trigger based partitioning
> anymore, especially on PG12. Partitioning is now part of core and is
> much more easily managed that way
>
> https://www.postgresql.org/docs/12/ddl-partitioning.html

Thanks Keith, I missed this one :/

JY

On Fri, 29 Nov 2019 17:35:03 +0100
Goke Aruna <goksie@gmail.com> wrote:

> are you debit or IOPS bounded,
>      IOPS

Then prefer 1|10, others are bounded to the R/W speed of only the
slowest disk while 1|10 is distributed among 1/2 disk Nb, so faster for
reads.
NB: This is less true with ZFS as other factors are at work.

Note that if you plan to use ZFS, extending an array of disks will mean
most of new writings will happen only onto the new disks until they reach
the same filling level than others - often crucial whatever the RAIDZ
level is.

Also do not forget about backupS!

> how fast is your DB growing,
>      Like 1.5GB per hour

Hmm, this lead to a bit more than 1TB/month30d, you'll either need more
SSDz or larger.

This is here that you can see carriers' names in the plain is
eating too place for nothing: one regular INTEGER (int4) = 4 bytes
(used as a foreign key) or even less if you do not have a lot of
carriers and the y fit into 32,768 (small int), when each character is
_at least_ one byte and often more in UTF8.

Also, the link I supplied you with (the one w/ rock and sand) is about
aligning row according to Pg internal constraints (in terms of memory) -
as it is said in this article, you could quite easily reach -20% on table
place taken on disk - this is almost a nonsense for any small app, but
with your number of rows, this becomes almost mandatory to scan faster at
a lower cost with less than an hour of tests/calculations to reorder
columns in your main table.

> how many simultaneous accesses you have/need,
>      Application - 5 and probably additional 5 for operator.

Ok almost nothing, connections speaking, but with a big debit each.

> what is the distribution between reads & writes,
>         70 read while 30 writ
> what is the alignement & size of one row,
>       Most used table has 42 columns while next after it has 18 columns

No, see above about what kind of alignment it is.

> etc.
>
> After that,
> which stripe size, 32

I do not use RAID anymore (and used it only in software version), so I do
not know if it is a good size taking in account the Pg page size (8kB),
the SSD sector (normally 2kB) and the clustering (min size of a
logical sector.)

JY

On Sat, 30 Nov 2019 08:07:27 +0100
Goke Aruna <goksie@gmail.com> wrote:

> This is great, thank you!

You're welcome.

> Very educative. I am still reading on the zfs.
> From your explanation, you prefer software raid to hardware raid,
> though greater preference for ZFS.

You can't really compare RAID to RAIDZ, they're different paradigms.
RAID is redundancy only, while RAIDZ is redundancy + data integrity.

Of course, nothing comes without a cost (be it $$$ and/or speed) AND as
usual, YMMV from your own needs (an objective decision way it to make a
Ben (from Benjy Franklin): for each system, you take a sheet of paper,
separate it in 2 with a line, put positive points to the left and
negative to the right, balance them, add the points to see on which
side it is leaning AND respect this result (!).)

> Do you have a pointer to what I can read on how this is achieved be it
> LVM or ZFS?

Not really, I read a lot about that (hundreds of articles) before even
testing, so dig the web and read a lot - avoid old papers, as ZoL is
evolving quite fast.

> By the way, based on your experience, what Linux variants will you
> recommend for postgresql and what version.

Debian _stable_ is world's best (I'm a little biased on this one as I
use it for 20 years now;)
But not if you do not have very good administration skills and to also
be avoid if you want to boot your system on ZFS (which is not such a
good idea anyway.)

> I have read from severalnines.com site that CentOS 7 should be avoided
> as much as possible unfortunately I am currently running it.

Yeah, it is veeery baaad… compared to Debian ;-p)
No, it is indeed a good distro, as almost any other one that is a root
one ; it is also often used as a base for some applications on CD or live
CDz.

If you don't know which one to pick, test them thoroughly one by one
taking your time (time only fully acknowledge things for which you took…
your time) - also take time to read articles and know if it's community's
large and alive (watch their mailing-lists archives, do they have a
large number of posts per month or not) and avoid derivatives (zzzzzz,
based upon aaaaaa.)
Take all your time, because your choice is something that'll almost
surely follow you for a looong time.

Jean-Yves