Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYB6Z-00Ejwn-Tm for pgsql-general@arkaria.postgresql.org; Thu, 08 Feb 2024 20:34:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rYB6Z-002tAr-57 for pgsql-general@arkaria.postgresql.org; Thu, 08 Feb 2024 20:34:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYB6Y-002t83-Q8 for pgsql-general@lists.postgresql.org; Thu, 08 Feb 2024 20:34:42 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rYB6W-006BWl-1K for pgsql-general@lists.postgresql.org; Thu, 08 Feb 2024 20:34:42 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-59a99ef8c7fso97603eaf.0 for ; Thu, 08 Feb 2024 12:34:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1707424478; x=1708029278; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=25QUPBs/8cMDCu9ZAQrUyaGmn1SCJ4CIUYTtAkFuEUM=; b=U7S05QJWmohlxnhKtAcaa5Gw12oJzLENb3Zw9myUC2dAjXCVf+v29V4WQY5w8xMWHd v5Nph3Uzwr6kgP+BFmncul8Li/HsdSeQ/q4cNVE9uy3wZbZkvZdlbEq4k0i1WYvMl2Jw aUiBcHZJiGV1yUkgXJGnoGoejslM34Arb1AWhyN9CsESpV+lDQ0mlrMfznXBcuTqmRUQ /gFC73EAJyY3actjFvVekZRc9su24E2AQORK3X9BueyH0FaFjMSmvvhUnk8awmKvs4ly gQEE3ttdnVBRRgHnLBIkH3wHcHGSuAcVdGjyRRzf/2aXZzlyCIudRZz6Gd7P/34pw5yO pYNg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707424478; x=1708029278; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=25QUPBs/8cMDCu9ZAQrUyaGmn1SCJ4CIUYTtAkFuEUM=; b=ffFMbdjfY46O1E4957BaAT6icBBEn0dFeEn5ZI1Pot+ZowV2wx9H5GC6/tWs2l9b6C Kj1Hmj/aWhtBxEfafAK7aoktqFdAQq9akgkeT1QuR8sPWwIwukLRWjuGNN9Dw/s6tyy/ uSidXdtxEhlJqMLF6Zp8nFovOo0aP7DwTLhT5qZywXn+L/3YiOEutNPgb04KYMm4aErY rNjD/nbX1FlcgpjYpRgSDwRYGJNyj3+JU7BuszLgmXaUpAH7Rwkh1VQDX33TPYheRqv8 yZdswpvTbbZJklrD6ol5dhb4ltebIijOR03iH23yfEWkklqQJr7MVXohc/J5K55NQEg+ bGdA== X-Gm-Message-State: AOJu0Yw8xRWkPw61jmirmiZrf8OkcTESzGj+zb1BIfF1fNkABNVPP3t/ 2LN8R2L5bfMVb3Do7nLl8JqdaRebHidJGqr0s5RsNkoGeRQtE+/pLPcoSW6e1w== X-Google-Smtp-Source: AGHT+IHPuiycnFU7xQRyLpos84ElwAvMg3oWZmDpZLw+tSNj1wYD4bYKwek5VTRim34Ky+I0dIh/BQ== X-Received: by 2002:a4a:9c46:0:b0:59c:e5c8:bdc5 with SMTP id c6-20020a4a9c46000000b0059ce5c8bdc5mr772428ook.3.1707424478022; Thu, 08 Feb 2024 12:34:38 -0800 (PST) X-Forwarded-Encrypted: i=1; AJvYcCVRh6iqCE+bS5n1SDoP5gWa7KUAEbm0NO5Vq8aEBmLW+z4CPfeiSpPpUiZ520Gn8pvjIuv90eOgujJxD4aN5Zbflkg0fz9Sua8GNmKyzmjgmUr0lERbew8fEgt2+w3nm6qOWinUWRA= Received: from [192.168.2.3] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id u10-20020a4a6c4a000000b00599f5c2c052sm32768oof.8.2024.02.08.12.34.36 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 08 Feb 2024 12:34:37 -0800 (PST) Message-ID: <4ef16a66-a54f-47b2-956d-803561c06e47@gmail.com> Date: Thu, 8 Feb 2024 14:34:34 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Partitioning options Content-Language: en-US To: veem v , Greg Sabino Mullane Cc: sud , pgsql-general@lists.postgresql.org References: From: Jim Nasby In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/8/24 1:43 PM, veem v wrote: > > On Thu, 8 Feb 2024 at 20:08, Greg Sabino Mullane > wrote: > > Should we go for simple daily range partitioning on the > transaction_date column? > > > This one gets my vote. That and some good indexes. > > > Hello Greg, > > Out of curiosity, As OP mentioned that there will be Joins and also > filters on column Customer_id column , so why don't you think that > subpartition by customer_id will be a good option? I understand List > subpartition may not be an option considering the new customer_ids gets > added slowly in the future(and default list may not be allowed) and also > OP mentioned, there is skewed distribution of data for customer_id > column. However what is the problem if OP will opt for HASH subpartition > on customer_id in this situation? > > Is it because the number of partitions will be higher i.e. > > If you go with simple range partitioning, for 5 months you will have > ~150 daily range partitions and with each index the count of partition > will gets double, for e.g if you will have 10 indexes, the total > partitions will be = ~150 table partition+ (10*150)index partition= 1650 > total number of partitions. > > If OP goes for , range-hash, and hash will mostly have to be 2^N, so say > 8, hash sub-partitions , then the total number of partitions will be = > (8*150) table partitions+ (8*150*10) index partitions= ~13200 partitions. > > Though there are no theoretical limits to the number of partitions in > postgres, there are some serious issues noted in the past with higher > number of table partitions. One such is below. Is this the reason? > > https://www.kylehailey.com/post/postgres-partition-pains-lockmanager-waits The issue with partitioning by customer_id is that it won't do much (if anything) to improve data locality. When partitioning by date, you can at least benefit from partition elimination *IF* your most frequent queries limit the number of days that the query will look at. Per the OP, all queries will include transaction date. Note that does NOT actually mean the number of days/partitions will be limited (ie, WHERE date > today - 150 will hit all the partitions), but if we assume that the majority of queries will limit themselves to the past few days then partitioning by date should greatly increase data locality. Also, when it comes to customer partitioning... really what you probably want there isn't partitioning but sharding. -- Jim Nasby, Data Architect, Austin TX