Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bnVjD-0000Lo-SS for pgsql-performance@arkaria.postgresql.org; Fri, 23 Sep 2016 19:01:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bnVjD-0000oL-8x for pgsql-performance@arkaria.postgresql.org; Fri, 23 Sep 2016 19:01:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bnVhX-0007Fk-AG for pgsql-performance@postgresql.org; Fri, 23 Sep 2016 18:59:59 +0000 Received: from mail-cys01nam02on0082.outbound.protection.outlook.com ([104.47.37.82] helo=NAM02-CY1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bnVhT-0005Gi-84 for pgsql-performance@postgresql.org; Fri, 23 Sep 2016 18:59:58 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=UnN2B6NrtFlL3JqnecU40UfQgq/M/v7E1DRR8gecTXI=; b=R7hFKfxpqG0jUa3SKrHPI9lmXzxrpQDi5FkHFng9cyU7RcSOPYN8wqAx77UTLxk8pS4I4kcn8/8IFZdTI0UngVZc9eg3XZ23QaA610EXE394lXiwTnAl0Gz6So+u0FcjHZZceMQnOcQwjMAzz3273hBECnyN+sjAJMrBk8Dyn5E= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.16.71) by BN6PR11MB1634.namprd11.prod.outlook.com (10.172.24.7) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.629.8; Fri, 23 Sep 2016 18:59:51 +0000 Subject: Re: Storing large documents - one table or partition by doc? To: Mike Sofen , References: <042201d21594$0061acb0$01250610$@runbox.com> From: Jim Nasby Message-ID: <060e03cb-bd2b-beb9-b2a1-dc1bfd30b0ca@BlueTreble.com> Date: Fri, 23 Sep 2016 13:59:42 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.3.0 MIME-Version: 1.0 In-Reply-To: <042201d21594$0061acb0$01250610$@runbox.com> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: YQXPR01CA0083.CANPRD01.PROD.OUTLOOK.COM (10.165.103.51) To BN6PR11MB1634.namprd11.prod.outlook.com (10.172.24.7) X-MS-Office365-Filtering-Correlation-Id: caf6fdeb-2684-41cd-43c5-08d3e3e3ccb4 X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1634;2:maIq/5hvo0biXfUMXieXE1WEDrMbAgtshn8wak1OLTJtmHN44gpdv6O4O9d/Cwfz25r7yGVYETtwkSyQaqhaWNtxfHLzAWCzZLKOli3R6dpHAZF8KV7vbhKqZ3MHKrZJHc638Os941I7XUMFXjP9S8SlJklrqEXkstgspex7JotpeF7HV8NG5EM2UNHOZ4QT;3:0VcuCrSzMlTSF6FJ3ydGEngjmQMWBdH8gmLCQ/yb0Bfu70PiFUcMRS29DDezxrk3r/IfbwTZfXxXwYj0Rhf76LlaR1dT2nCnmT+FB9KqjOQyS9e9/NpV6KSoUTb4Jml2;25:vKcVhXxiC8xkaJCdR160so/DmLEOLwqqw8j37UVByC/Ey/u+X0Npb4WeEukIxgTj45qqDBbzXNFBeW4fbf2SV1iOZdGVsftCW6rtEeKJSZAz7p4AZQG81sRSMuWBd44Y7XQxu8iwL4d1zPa/Av5ZC8uwxvfMPoeRK4qYJjErLHWXayHJyXv6pJyk88qsNpO7i2DX34Mk+qUjLHQbKxNrj/VI5hQ5WrJJ7o9bPJUFUANHIM4N3siR2VunhE+GdVMsB1TdrqTepLDu0bpOkpcHD1VWDAdMOJ9mzYxxucSZ25bsq22wTbw8+Y407E8S3XVqzGkugc4r6gOy+cZxR/MrcyCWXo+UHUjHrjkEqe513Mx6tkgKRe4Y9VfDR7VFmebFIV+5MEsWFekWg2FdcnZmpcSOSpkba8Ma9ydo/QRnmG4= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1634; X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1634;31:ujZzHUZ2B2oagLyHfLlD8yGdBJcyYsl0JFo6a6Sdt+FeYCcjGaL64bMBoBdPv/JjFg5hi2capAwnl1/AAgkHTu2mQBiNBKYthPWBEfY79QfslwMwtNu+PG7fyhd+XmZmsj7lLC03fQ5lzfqyU/GLPVoXzBS3qyAkgHMZOCCNCOieP1s0pZZGECTFVG6J2PFQ4Gn+d1RmqI+FzouQozvm0p7ba8LpflkouVr6z7E6Z4k=;4:6rAYusHG7iZQl6HWlKy8edkuMJVobOreQxpTXYyxuZuU3OW48I2/vsFZMDkPHk9YhqyDfzEuH3H0dC2VP16WBl2Ewb+qQ2wPtKHC7+2caZKMwW4LBG3kCxfsE3nr0qDAhuLKv2n7mbLCh2O++JvqGlL4UWUZeVhfiusEPgLeAhcFyO8aqQ5Ln8Cf1DYi/Lpk6SOZCscmBeDYDsALy4R/LyJCUMNAgT7+auGfDwZAN2MO2RJtUy9LgPezWzXl9QGpdK7J5g5gN+12h9utZ5reKnTjXYqYdwalQwXAY88d7S5t4pUtXVdrzz+inmNnb8jEbSDNnzfo9fpNfugR8ScCRtKQzvqfTCL27KCfrQggaQJmceqz28Z4lvZv9EeYgcgDHkw1dX7pfmgtO8MBaSez4CJ3RATDlGgqkQdTpn9IH64= X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(6042046)(6043046);SRVR:BN6PR11MB1634;BCL:0;PCL:0;RULEID:;SRVR:BN6PR11MB1634; X-Forefront-PRVS: 0074BBE012 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(24454002)(189002)(377454003)(199003)(23676002)(33646002)(7846002)(105586002)(42186005)(189998001)(81166006)(81156014)(8676002)(107886002)(106356001)(19580395003)(4001350100001)(305945005)(97736004)(50986999)(5001770100001)(7736002)(16601075003)(64126003)(15975445007)(54356999)(92566002)(36756003)(77096005)(66066001)(31686004)(68736007)(76176999)(101416001)(50466002)(586003)(2870700001)(86362001)(47776003)(65956001)(5660300001)(31696002)(65806001)(83506001)(3846002)(2906002)(6116002)(65826007)(2950100002)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:BN6PR11MB1634;H:decina.local;FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1;LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtCTjZQUjExTUIxNjM0OzIzOk9Wa1Q1cE8yVE00bDhwK1lybnJDV1ZNalRU?= =?utf-8?B?RU9Xd2R3ZXhYUTNyUWdpUU04ODIwaWlTV2xJb3ZkRW5PU1MyNS9mL3FZTmt4?= =?utf-8?B?Y0dRbTdZUVYxSXFZUGVHSUJFRUZpdkk3Qkl1QWNDT01ObndKM052Y1pUUmRR?= =?utf-8?B?TFhnWWttTnVZaXR6bGJRc2tYeHlHcXlhdUJ1VUNGVkh3a0ZWQ1VTNTcrVmcx?= =?utf-8?B?UVdHK2xrc0FSektsdlZWeVhkeVZVdHRIemZrQUhoVVFDbDZ6ZHRhdExZVnRT?= =?utf-8?B?Z0F3cERGcDhFNVNXVFJIZEg4LzdnMmdnWTJpdkdlMzBlK3dxQS9ZcnpyYk1v?= =?utf-8?B?VG9aM3hFbDF1cnhIeWpNNEpRM1U1RVNvZWxYQzM4Q0RsNk9pdE5SUkFRdHk1?= =?utf-8?B?bU5qSmREc0k2ODRWeVM1TjdkdmorMWN4Y1lBT04yc3JxeTFicDdvU084SG1H?= =?utf-8?B?TGZDY2g3Mi9DMWd2R0xHK0V3ZVlucURBQWNieW5sSVYrdDRTczJtWmNxZGJN?= =?utf-8?B?RldyTEFKbEhIbGE3S1I5a09TOGlTMkJ6blZtQ2p1RmVycUY5MGV1QzU2alBn?= =?utf-8?B?S21HdDlKMm1IZTZ3bUpPQnF0VHRjQXNUMCt6Yys4cVdQY2xaU2VYSEt2QVhy?= =?utf-8?B?eHBHSDhNZm1sa2VpTGJGZmdYT2dUOU05aHZWOEJxUlpRUXY5UGhnU0xqSlVy?= =?utf-8?B?Slh5aE8wamQ4Y01zdC8zTXJDRG1CNTRBbXM1dWU3V0t5VHBDSUZRK2Vrd3N4?= =?utf-8?B?NHR1Q2VQSDJycGlVdVBPaHBYUElYWHcyVnVhWDFhdUU0YlIyNzBjMldlU0FE?= =?utf-8?B?U29NYVpJRFZJZjRtKzV1UlN2aWRKTTFhZlphMS9mK25mNnVpVStiSGxva1g3?= =?utf-8?B?VG1FSlUxenJaenVXSkRBd0lRTExWdGg1UnlWNVo3S25aRWVINUhoallKYlQw?= =?utf-8?B?T2lIemJQYVYxTWFmaFk3ZWJnSmoyaFFOaVVlMU05MWk0TXQvWnlIYmh5Y3ZV?= =?utf-8?B?bjhUYjRYUjVHRFA0Uk9Kem5DT2pmT3VkYitndVVHNUZSRDF2MnA4SXZuSjBB?= =?utf-8?B?dWNCeVZzeEtKZmpIU3JrRVNlRG9ISzN2Wk5meFQvOVB2NVgyTzBocXJXak9l?= =?utf-8?B?OXllREJEMTdFM2F1cjY1YzZjYWRaVTZVSDRVd0s0NWlpK0JRWm5XL1VVZWFt?= =?utf-8?B?aE9SUTZQNkJqamxlaFZQRnVNaGxscS8rbG5sNVQ1UUZtRklqYXA4UzRyaTE5?= =?utf-8?B?SEgyUjhIQ1p3VHZMQ3VJdGpTNHBNV0V1bEwzc2tzZXh5SWphSnR0QURzbXEr?= =?utf-8?B?NWtNVHEzS3FZeHZ4RGFjT2R5RFZYblIrcnBNdUpEQjRaWC9GTFk3OEkvU3A0?= =?utf-8?B?VHVKMmFqVVQ2emwxencyc2xjVEwvUFNsR1o2TG0ySnlPL09ZR1k0TEpwdTVy?= =?utf-8?B?c2V5Z0E0QWdJSlBnRFI5UXhYTnBkdnFaZEI3RXJ4Qit3LzRyUFQyOGVjL0pH?= =?utf-8?B?V21xd01yaUJiaGE2cmFFMXl1Z29EbER3VWJwcHlzejErM1ZUOEFvbWx1eEFv?= =?utf-8?B?L2NSVU4vNHdYRjhUZHpJMG5PWmx5QkxSeW44OGNCbUh1RGZrMS9PRkQyN2dM?= =?utf-8?B?Y1ZTc0NnLzhGV3h3dXgvK25qZHFQVndJRWtyTXJwcm03dmZxZ2ZTeEh5UGFF?= =?utf-8?B?dVpENVpEU2tweUJpWlpYK0U5bmtBSHlYSStWRnRONnVqWVh1OU8wZnA1RXh5?= =?utf-8?B?QXJ6eVY4bXI5dTA1UjRDalp2Ykp0a2lpeXZEZ3JwMnA2R3BDUWd3cTh6emZs?= =?utf-8?Q?MOYkVS5aGhIU+?= X-Microsoft-Exchange-Diagnostics: 1;BN6PR11MB1634;6:w+11k7eiJ2x48JMjiBbxB8MJlsjppcuI/gbe4NvIbG5StJnRG2u3+4QtlMzXOHHJF89DM6mjmoCGnB8VyJLoRwf+53/R7xZaKaGJ6N5Q34k2kTd53i+HMdH+nr7WZQL5b6LZzmX/hx2APCGv6V+htVOr0hgx+vEoFxByqc9ki/HawD8n4SshUuP6yurwB6lHDuuSy/6/xB3cfU0VFZvpaIuqPLW+WtKBnITAmm4tzDOASJmSPnhJhViUvTiq7aNRaNZGfGWfsZxpR75V7nNTuhC4LPV24Z+VjoFz8y32VoRRFyEJtsOVmzUnh10Lw09F;5:nlQwg/czsmB0XMGaxJYT7gZnUJRDqqcJE0H/R4VYzmedkAmt5iddgOHHCiFr7DTDh8zbxHsuVmyKkQhG33ISnEYII75kh/CnMjs7Moq6Yo5E3ef4r4z0A88wxRjgKBCv4+HGFqT8lviKV7dd+nVtOw==;24:Fs3GlValo0RXYOwQVeg9Xg5VPKtmqHXy76B17088LQmvl6vOqP20pz5FxNgzNl5XqSYXSGlKteX+E35Nd2NMujaFpmrpaq5lzwJPkInptyc=;7:HUaSocMGAt5WmjpV3+U+LSSJBM+ErRLbs1InqUgL6ZWZ85DgWMmcmeHOOw1BAl9Y6dQmUOwepxoGfWIlXbBl5buAvVk5cFGQjzyI3YNLPCz6SUorlaPm/a4+MddjLn6yC+F993v5sB57UGu89pFjWdG1A7EgH/3harDy7CkZ2KZe7RLfl0J0wsqFvs2qy7pkWkRlNpE0S8Ou55fxHyfQCJqrfyKkNBy+tlkX96Tl1ZRnjEF639bVm3aWI9B/C4IWI1LGN4BXMjirwtG2rq8mMwnlpO43cgsKFMnPbe1KDkwZdrtJUHcUfSe1j8MGf/Xy SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Sep 2016 18:59:51.5944 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1634 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 9/23/16 7:14 AM, Mike Sofen wrote: > So with proper indexing, I can’t see where there will be a performance > issue. Table bloat could become problematic. If there is a pattern where you can predict which documents are likely to be active (say, documents that have been modified in the last 10 days), then you can keep all of those in a set of tables that is fairly small, and keep the remaining documents in a set of "archive" tables. That will help reduce bloat in the large archive tables. Before putting in that extra work though, I'd just try the simple solution and see how well it works. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance