Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j6Jav-0002NB-KZ for pgsql-performance@arkaria.postgresql.org; Mon, 24 Feb 2020 19:40:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6Jat-0005Tx-Or for pgsql-performance@arkaria.postgresql.org; Mon, 24 Feb 2020 19:40:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j6Jas-0005Tp-Rl for pgsql-performance@lists.postgresql.org; Mon, 24 Feb 2020 19:40:43 +0000 Received: from sonic311-14.consmr.mail.bf2.yahoo.com ([74.6.131.124]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j6Jak-0003FB-Rj for pgsql-performance@lists.postgresql.org; Mon, 24 Feb 2020 19:40:41 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1582573233; bh=Euxpthqe+y0/nXfaeOwKIO6NfZhKsWGaAxhMQSNJ9x8=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=YqaPzPsf5QKMEt1TyRAJs+RPJtftd30PHW/mayV6LjlV4ux17CcXFI9j8RTi4l9J4qcGBhlOK4jvgPZOuWg3R+g590e0oPZdzdAfMJoyLXOK24E8Otk1C3ZZuKp8qnUoiEGO0JWN2FAfmcRI6GCrjfEAhsbIG8VzmYCU6b5H4Fh0qnJcOBDdNRyEEfp4b0Aqs/S7sbzK0+EZMZ+jNr8+ODOf+0Ysg6HS/dniYqUNiBE4rlBhXl9f1GhonP2C+htQTvwjyLbx4DvlYLuiUEFyzE7tI2/sttdf4Ev5gUGTNlXBTsPO4oYzylhmcu2Z7vxAw3hM3fAcdrvBJDQo6K4wJQ== X-YMail-OSG: hZVs.0kVM1mNIOp0VkteoCM2gJAMPia0Jjhsw6d5lSiXUAkJLhJPOoqZqhrWFln X08H2RMRhdumx_v6s0F7EHKU7fh3XUFUL3.EIHKFkxjwIEfarjR7hkyxWHD.5kQSZVXo.gsCfOrr SVpx8UZNlAGjd.g548N8sIkQ1NrMuQfoVaKD9YAglalZ2a4q28ImzrZ5tRA4o0jAgnLhWTE8pym6 HA7fTEvc8CTVGFFfUAkPp0tZ02WMEwc5lr8xPymB9I2N03h_cCl39aNfUimUwcumLFsbvzw7943S pS0aQbIhJLAG5w0Q3I6MMI8A8E2DjuXeW9KWWGsCgArGv3QQ1Jq7cx7EKv.XCpDlKG3tbnM7J2hp SPea_iu4l2Xt3flWZqzHxq9tfLHdlQ93LnB6BhkHMEgMMwUOWrgu7pC1weIssndQiTsZtIBaE6lc CDUv9Os5V_6294J7d9zY.6a14tB9zpvWPkWAxZhQhyuJ.b8.AxZ15mQLlUZDWBHfL9Q7hOzaf7Sm u.pqC.biSobYpvcfGr31QhYo7aiCOO_KRMRJxJGt1IBKxfuFgW6BC_YqxSYZrBbSzhF1Ab_HWCZq Rx8vCt7V74hyddUzldBcfk5QuoQVlliucFjlIPlrVoeCWCDdF.3GneIvmVpOkKMkJsa3DwTtmXd4 qiOSx5FFQlRu6m8OmclhaErNL9SLU.NScDJ6jpw8IBKtrkErMKlk_VqQGA9l__0KCIQEPryBIf4o Tm6XolleSAKrwvmXo8sztTvVw0WDB6hA5ZXGhJFMNHtorHfbDb2X8HVMjaklDo7HGR3W1mXzygY4 Fsvqr_vgvdE5NkZadcQLw0gZHyOasaQ6mezqrQbPXoITjol5_0DQy5R32jLfxD1QRpquM.CgVzqO v6TC_lohmahzCI69kswWAFS.aprZ2YT4WNyC3wGJmTL33yxAryK6JxOK0DgKOZhJPoDP5_r72mMu flNbFFDKoC2yscvJepaJsGhzNIwYHrK02fnsmtTfIR3_n5ke1p_WXDF7N0RdE18x3kkXbxNY8FYD 2EoBuwUJkJ3._Lmnep4jQNsdvpQNpWYMKxAQOL2EwHEPVlPrRr_dviGHSrkZRq9sJhR3q_CLWovV Ju4nBWf91yPkspBSBeQ6mqbcErhN9ih4XDWobk7Q1b94hcvoMis.U.19TCq2e39foo718vdrUrhy wjutaPa6o7HavcP9VI5TJXG4.Hv5tNZunYxd7.jBGfT5p6jWMNfhaSVtAKnvQ1wfg_soVS6s94oj PGP_zw6TcTH8hzW7pGdKrh30PjNj7jVMVhB_gTHpNWYlxMZ9D6s6MjrGe1GU5eHZ.UbviM3tJptZ .ToJHVENRu7.EsxkWfSBfUgmd5va5alYki1jORw-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Mon, 24 Feb 2020 19:40:33 +0000 Date: Mon, 24 Feb 2020 19:40:16 +0000 (UTC) From: Ravi Garg Reply-To: Ravi Garg To: Justin Pryzby Cc: pgsql-performance@lists.postgresql.org Message-ID: <843623673.5437661.1582573216373@mail.yahoo.com> In-Reply-To: <20200223151038.GW31889@telsasoft.com> References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com> <1789489940.5008704.1582451790367@mail.yahoo.com> <20200223101209.GU31889@telsasoft.com> <1169437250.5002073.1582455449880@mail.yahoo.com> <20200223151038.GW31889@telsasoft.com> Subject: Re: PostgreSQL 11 higher Planning time on Partitioned table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5437660_1955124496.1582573216370" X-Mailer: WebService/1.1.15199 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.97 Safari/537.36 Content-Length: 29336 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5437660_1955124496.1582573216370 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Justin, >I didn't hear how large the tables and indexes are.+----------------------= ---------------------+------------------+----------------------------------= ----------+|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 table_name=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| pg_rela= tion_size |=C2=A0 pg_total_relation_size - pg_relation_size |+-------------= ------------------------------+------------------+-------------------------= -------------------+| TransactionLog_20200213=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 95646646272 = | 4175699968=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|| TransactionLog_20= 200212=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= |=C2=A0 =C2=A0 =C2=A0 95573344256 | 4133617664=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0|| TransactionLog_20200211=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 91477336064 | 3= 956457472=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|| TransactionLog_20200= 210=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A08192000000 |=C2=A0 354344960=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0|| TransactionLog_20200214=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A068= 26672128 |=C2=A0 295288832=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|| Tra= nsactionLog_20200220=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A01081393152 |=C2=A0 =C2=A089497600= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|| pg_catalogpg_attribute=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 3088384 |=C2=A0 =C2=A0 2220032=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|| TransactionLog_20190925=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 1368064 |=C2=A0 =C2=A0 =C2=A0 90112=C2=A0 (174 such partitions)= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |+--------------------------------------= -----+------------------+--------------------------------------------+=C2= =A0>=C2=A0Do you mean that a given query is only going to hit 2 partitions = ?=C2=A0 Or do you> mean that all but the most recent 2 partitions are "arch= ival" and won't be > needed by future queries ? Yes all queries will hit only 2 partitions (e.g. if we do daily partition, = queries will hit only today's and yesterday's partition). > You should determine what an acceptable planning speed is, or the best ba= lance> of planning/execution time.=C2=A0 Try to detach half your current pa= rtitions and, if> that gives acceptable performance, then partition by day/= 2 or more.=C2=A0 You could> make a graph of (planning and total) time vs np= artitions, since I think it's> likely to be nonlinear.> I believe others ha= ve reported improved performance under v11 with larger> numbers of partitio= ns, by using "partitions of partitions".=C2=A0 So you could try> making par= titions by month themselves partitioned by day. FYI, these are the observations I am getting with various number of partiti= on and a multilevel partition with respect to Un-Partitioned.+-------------= --+----------------------+----------------+---------------+--------------+-= --------------+--------------+---------------+--------------+| Testcase=C2= =A0 =C2=A0 =C2=A0 | Partition Count=C2=A0 =C2=A0 =C2=A0 | Records in=C2=A0 = =C2=A0 =C2=A0| Select=C2=A0 =C2=A0 =C2=A0 =C2=A0 | Select=C2=A0 =C2=A0 =C2= =A0 =C2=A0| Update=C2=A0 =C2=A0 =C2=A0 =C2=A0 | Update=C2=A0 =C2=A0 =C2=A0 = =C2=A0| insert=C2=A0 =C2=A0 =C2=A0 =C2=A0 | insert=C2=A0 =C2=A0 =C2=A0 =C2= =A0||=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | each Parti= tion | planning (ms) | execute (ms) | planning (ms) | execute (ms) | planni= ng (ms) | execute (ms) |+---------------+----------------------+-----------= -----+---------------+--------------+---------------+--------------+-------= --------+--------------+| Single Level=C2=A0 |=C2=A0 =C2=A06=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 1000=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0|=C2=A0 1.162=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.045=C2=A0= =C2=A0 =C2=A0 =C2=A0 |=C2=A0 2.112=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.115=C2= =A0 =C2=A0 =C2=A0 =C2=A0 | 1.261=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0.178= =C2=A0 =C2=A0 =C2=A0 =C2=A0 || Partition=C2=A0 =C2=A0 =C2=A0|=C2=A0 30=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 1000=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 2.879=C2=A0 =C2=A0 =C2=A0 =C2=A0 = | 0.049=C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 5.146=C2=A0 =C2=A0 =C2=A0 =C2=A0= | 0.13=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 1.243=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0| 0.211=C2=A0 =C2=A0 =C2=A0 =C2=A0 ||=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| 200=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | 1000=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 18.479= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.087=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 31.385=C2= =A0 =C2=A0 =C2=A0 =C2=A0 | 0.18=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 1.253=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0.468=C2=A0 =C2=A0 =C2=A0 =C2=A0 |+-------= --------+----------------------+----------------+---------------+----------= ----+---------------+--------------+---------------+--------------+| Multi = Level=C2=A0 =C2=A0| 6 Partition having=C2=A0 =C2=A0| 1000=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| 3.6032=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.0695=C2=A0 = =C2=A0 =C2=A0 =C2=A0| x=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| x= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | x=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| x=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 || Partiti= on=C2=A0 =C2=A0 =C2=A0| 30 subpartition each |=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |+---------------+--------= --------------+----------------+---------------+--------------+------------= ---+--------------+---------------+--------------+| UnPartitioned | NA=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 430 Mil= lion=C2=A0 =C2=A0 | 0.0875=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0.0655=C2=A0 =C2=A0= =C2=A0 =C2=A0| x=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| x=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | x=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| x=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |+---------------= +----------------------+----------------+---------------+--------------+---= ------------+--------------+---------------+--------------+ > If you care about INSERT performance, you probably need to make at least = a> single partition's index fit within shared_buffers (or set shared_buffer= s such> that it fits).=C2=A0 Use transactions around your inserts.=C2=A0 If= your speed is not> limited by I/O, you could further use multiple VALUES()= ,() inserts, or maybe> prepared statements.=C2=A0 Maybe synchronous_commit= =3Doff.>=C2=A0> If you care about (consistent) SELECT performance, you shou= ld consider> VACUUMing the tables after bulk inserts, to set hint bits (and= since> non-updated tuples won't be hit by autovacuum).=C2=A0 Or maybe VACU= UM FREEZE to> freeze tuples (since it sounds like a typical page is unlikel= y to ever be> updated). Sure, I'll evaluate these settings, thanks. Thanks and Regards, Ravi Garg=20 On Sunday, 23 February, 2020, 08:40:58 pm IST, Justin Pryzby wrote: =20 =20 On Sun, Feb 23, 2020 at 10:57:29AM +0000, Ravi Garg wrote: >=C2=A0 =C2=A0 - Currently we are thinking to have Daily partitions and as = we need to keep 6 months of data thus 180 Partitions.However we have libert= y to reduce the number of partitions to weekly/fortnightly/monthly, If we g= et comparable performance.=C2=A0=20 I didn't hear how large the tables and indexes are. >=C2=A0 =C2=A0 - We need to look current partition and previous partition f= or all of our use-cases/queries. Do you mean that a given query is only going to hit 2 partitions ?=C2=A0 Or= do you mean that all but the most recent 2 partitions are "archival" and won't be needed by future queries ? > Can you please suggest what sort of combinations/partition strategy we ca= n 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). You should determine what an acceptable planning speed is, or the best bala= nce of planning/execution time.=C2=A0 Try to detach half your current partition= s and, if that gives acceptable performance, then partition by day/2 or more.=C2=A0 Y= ou could make a graph of (planning and total) time vs npartitions, since I think it'= s likely to be nonlinear. I believe others have reported improved performance under v11 with larger numbers of partitions, by using "partitions of partitions".=C2=A0 So you co= uld try making partitions by month themselves partitioned by day. >=C2=A0 =C2=A0 - Our use case is limited to simple selects (we don't join w= ith the other >=C2=A0 =C2=A0 tables) however,=C2=A0we are expecting ~70 million records i= nserted per day >=C2=A0 =C2=A0 and=C2=A0there would be couple of updates on each records wh= ere average record >=C2=A0 =C2=A0 size would be ~ 1.5 KB. >=C2=A0 shared_buffers=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 1048576 If you care about INSERT performance, you probably need to make at least a single partition's index fit within shared_buffers (or set shared_buffers s= uch that it fits).=C2=A0 Use transactions around your inserts.=C2=A0 If your sp= eed is not limited by I/O, you could further use multiple VALUES(),() inserts, or mayb= e prepared statements.=C2=A0 Maybe synchronous_commit=3Doff. If you care about (consistent) SELECT performance, you should consider VACUUMing the tables after bulk inserts, to set hint bits (and since non-updated tuples won't be hit by autovacuum).=C2=A0 Or maybe VACUUM FREEZ= E to freeze tuples (since it sounds like a typical page is unlikely to ever be updated). --=20 Justin =20 ------=_Part_5437660_1955124496.1582573216370 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi = Justin,

>I didn't hear how large the tables and indexes are.
+-------------------------------------------+------------= ------+--------------------------------------------+
|&= nbsp;             table_name    &nb= sp;              | pg_relation_size |&nb= sp; pg_total_relation_size - pg_relation_size |
+------= -------------------------------------+------------------+------------------= --------------------------+
| TransactionLog_20200213&n= bsp;                  |  =     95646646272 | 4175699968          &n= bsp;                     =  |
| TransactionLog_20200212      &= nbsp;            |      955733= 44256 | 4133617664                &= nbsp;                |
=
| TransactionLog_20200211            =        |      91477336064 | 3956457472&n= bsp;                     =            |
| Transactio= nLog_20200210                 =  |       8192000000 |  354344960   = ;                     &nb= sp;        |
| TransactionLog_20200= 214                   |&n= bsp;      6826672128 |  295288832      &= nbsp;                    =      |
| TransactionLog_20200220  =                  |   = ;    1081393152 |   89497600       =                     &nbs= p;    |
| pg_catalogpg_attribute   =                 |    &nb= sp;     3088384 |    2220032       =                     &nbs= p;    |
| TransactionLog_20190925   = ;                |    &nb= sp;     1368064 |      90112  (174 such parti= tions)          |
+-----------= --------------------------------+------------------+-----------------------= ---------------------+
 
> Do you mean that a given query is only going to hit 2 pa= rtitions ?  Or do you
> mean tha= t all but the most recent 2 partitions are "archival" and won't be> needed by f= uture queries ?

Yes all queries will hit only 2 partitio= ns (e.g. if we do daily partition, queries will hit only today's and yester= day's partition).
> Yo= u should determine what an acceptable planning speed is, or the best balanc= e
> of planning/execution time.  Try to detach half your = current partitions and, if
> that gives acceptable performance= , then partition by day/2 or more.  You could
> make a gr= aph of (planning and total) time vs npartitions, since I think it's
> likely to be nonlinear.
> I believe others have report= ed improved performance under v11 with larger
> numbers of par= titions, by using "partitions of partitions".  So you could try
<= div>> making partitions by month themselves partitioned by day.

FYI, these a= re the observations I am getting with various number of partition and a mul= tilevel partition with respect to Un-Partitioned.
+---------------+----------------------+----------------+--------------= -+--------------+---------------+--------------+---------------+-----------= ---+
| Testcase      | Partit= ion Count      | Records in     | Select = ;       | Select       | Update  &n= bsp;     | Update       | insert   =     | insert       |
|               |  &n= bsp;                   | each = Partition | planning (ms) | execute (ms) | planning (ms) | execute (ms) | p= lanning (ms) | execute (ms) |
+-------------= --+----------------------+----------------+---------------+--------------+-= --------------+--------------+---------------+--------------+
<= div style=3D"">| Single Level  |   6      =             | 1000       =    |  1.162        | 0.045   =     |  2.112        | 0.115   = ;     | 1.261         | 0.178  &nbs= p;     |
| Partition    =  |  30                &nb= sp; | 1000           |  2.879  &nbs= p;     | 0.049        |  5.146  &nb= sp;     | 0.13         | 1.243  &nb= sp;      | 0.211        |
|               | = 200                  | 1000&nb= sp;          | 18.479        |= 0.087        | 31.385        | 0.1= 8         | 1.253         = ;| 0.468        |
+-----= ----------+----------------------+----------------+---------------+--------= ------+---------------+--------------+---------------+--------------+
| Multi Level   | 6 Partition having&nb= sp;  | 1000           | 3.6032  &nb= sp;     | 0.0695       | x     = ;        | x            |= x             | x      &= nbsp;     |
| Partition  &nbs= p;  | 30 subpartition each |            =     |               |&nbs= p;             |        &= nbsp;      |             = |               |    &nb= sp;         |
+---------= ------+----------------------+----------------+---------------+------------= --+---------------+--------------+---------------+--------------+
| UnPartitioned | NA         = ;          | 430 Million    | 0.0875&nbs= p;       | 0.0655       | x   =          | x          &n= bsp; | x             | x    &n= bsp;       |
+----= -----------+----------------------+----------------+---------------+-------= -------+---------------+--------------+---------------+--------------+

<= div>
> If you care about INSERT performance, you probably need to ma= ke at least a
> single partition's index fit within shared_buf= fers (or set shared_buffers such
> that it fits).  Use tr= ansactions around your inserts.  If your speed is not
> l= imited by I/O, you could further use multiple VALUES(),() inserts, or maybe=
> prepared statements.  Maybe synchronous_commit=3Doff.<= /div>
> 
> If you care about (consistent) SELECT p= erformance, you should consider
> VACUUMing the tables after b= ulk inserts, to set hint bits (and since
> non-updated tuples = won't be hit by autovacuum).  Or maybe VACUUM FREEZE to
>= freeze tuples (since it sounds like a typical page is unlikely to ever be<= /div>
> updated).

Sure, I'll evaluate these settings, thanks.

Thanks and Regards,
Ravi G= arg


=20
=20
On Sunday, 23 February, 2020, 08:40:58 pm IST, Justin P= ryzby <pryzby@telsasoft.com> wrote:


On Sun, Feb 23, 2020 at 10:57:29AM +0000, Ravi Garg wr= ote:
>    - Currently we are thinking to hav= e Daily partitions and as we need to keep 6 months of data thus 180 Partiti= ons.However we have liberty to reduce the number of partitions to weekly/fo= rtnightly/monthly, If we get comparable performance. 

I didn't hear how large the tables and indexes are.<= br clear=3D"none">
>    - We need to look cu= rrent partition and previous partition for all of our use-cases/queries.
Do you mean that a given query is only g= oing to hit 2 partitions ?  Or do you
mean that all = but the most recent 2 partitions are "archival" and won't be
needed by future queries ?

> Ca= n you please suggest what sort of combinations/partition strategy we can te= st considering data-volume/vacuum etc. Also let me know if some of the pg_s= ettings can help us tuning this (I have attached my pg_settings).

You should determine what an acceptable planni= ng speed is, or the best balance
of planning/execution ti= me.  Try to detach half your current partitions and, if
that gives acceptable performance, then partition by day/2 or more.&nbs= p; You could
make a graph of (planning and total) time vs= npartitions, since I think it's
likely to be nonlinear.<= br clear=3D"none">
I believe others have reported improve= d performance under v11 with larger
numbers of partitions= , by using "partitions of partitions".  So you could try
making partitions by month themselves partitioned by day.

>    - Our use case is limited to sim= ple selects (we don't join with the other
>  &nbs= p; tables) however, we are expecting ~70 million records inserted per = day
>    and there would be couple of u= pdates on each records where average record
>  &n= bsp; size would be ~ 1.5 KB.

> = ; shared_buffers                &nb= sp;       | 1048576

If= you care about INSERT performance, you probably need to make at least asingle partition's index fit within shared_buffers (or set = shared_buffers such
that it fits).  Use transactions= around your inserts.  If your speed is not
limited = by I/O, you could further use multiple VALUES(),() inserts, or maybe
prepared statements.  Maybe synchronous_commit=3Doff.

If you care about (consistent) SELECT perf= ormance, you should consider
VACUUMing the tables after b= ulk inserts, to set hint bits (and since
non-updated tupl= es won't be hit by autovacuum).  Or maybe VACUUM FREEZE to
freeze tuples (since it sounds like a typical page is unlikely to ev= er be
updated).


--
Justin


=
------=_Part_5437660_1955124496.1582573216370--