Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bszNp-000636-Ey for pgsql-performance@arkaria.postgresql.org; Sat, 08 Oct 2016 21:42:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bszNo-0004Kw-IL for pgsql-performance@arkaria.postgresql.org; Sat, 08 Oct 2016 21:42:16 +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 1bszNn-0004KV-Kv for pgsql-performance@postgresql.org; Sat, 08 Oct 2016 21:42:15 +0000 Received: from mail-bl2nam02on0064.outbound.protection.outlook.com ([104.47.38.64] helo=NAM02-BL2-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 1bszNi-0008RL-Sq for pgsql-performance@postgresql.org; Sat, 08 Oct 2016 21:42:14 +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=6NNPG5Pe0/F+aXuERCiRGdxF+47bQ5MI06eQjXJw98o=; b=ZNAvzYpW5p7GxTTmj5Ufpbm2QnTCJfUYAAqTK7grFjcRE+S1UENX6gVFlcw5xrbXb9zf81nVt/H1E6Z2++6tKG90PI+CHEMlD7qdY5t0TalctIhTY0fgFggfR44wOQrRl4Ua5fcaSS+F1SJGlanKYktBpWJDtmmsfAD5jbuUElo= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:9c8e:3846:2f8f:4cf3) by CY4PR11MB1637.namprd11.prod.outlook.com (10.172.71.13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.649.16; Sat, 8 Oct 2016 21:42:06 +0000 Subject: Re: Millions of tables To: Greg Spiegelberg References: <001401d21a42$2d21a8b0$8764fa10$@postgrespro.ru> CC: "Alex Ignatov (postgrespro)" , Terry Schmitt , pgsql-performa. From: Jim Nasby Message-ID: Date: Sat, 8 Oct 2016 16:42:01 -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: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:9c8e:3846:2f8f:4cf3] X-ClientProxiedBy: DM5PR08CA0014.namprd08.prod.outlook.com (10.175.218.24) To CY4PR11MB1637.namprd11.prod.outlook.com (10.172.71.13) X-MS-Office365-Filtering-Correlation-Id: 5025a4db-d3a5-41a6-0691-08d3efc3f387 X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;2:frLaBVImeg6Cqhs19LwvWnBEzMj8FZbAPeMKt4DUu7GnksJ1s7E8uKBIQI0vwh3xzGqM/SRqgmTNL+pOZRqnkdNJKVzeFMW0wX8C/idP8H+kO+tzTfqYSk/JMks0NaOvSQqqVYRVx+V0AmJ7BYIhlgJMXfKliE96M0mhbTyEMRuplgeCc63xDolYXjbOqjVPLBJhGodo7Af++LTb1zPh+w==;3:IJsLx/X0vkACFP9utGHUKxqwRTwxbUjL2+4LphqZ5GSGmEFNSY7jFU3ozld4IxKj3ifp6ElxKGQl+W+hx5S6KDMGFfmte5XgckXs9fpq8O8syPBg1t8F00pnjnN1ci9KO/Oo7pvKtP78WYMOJhwLOw==;25:U67fJGJFYJK0UayyT93F7dKBxxhYrdepKjDsQ6LNhjxp/GsJNJR5bKQPcZtD4sKxd/A0cqjUNbDQ1fK1tKFVejyXAXRE+opS+72bKoIWqCZrFVm3uJBnRVvOzqWtgMWeMU/CI+wwIJN7LP9mYLFNMOEVAFCZT+HghRhS/gGs/UHB7UpQC9wKkox6dOJHWB1bm6Pohd8yBcT1vLgXsIaK0Gu3FND/UGG0oSPGvZJvTZ+jR/JJH1ws2GECuE40gPDeTKJ/+Zq/b9JUf27j8iRnXcFBW+PP+oWyPF6ElMWFOZqVpIf+U/yyBk+ESGembT28SuCrOuhnML/BRAcH6VqE4sfq0ZEESbKkcqkkIVyCJ0LNmddeeRDOiOplT6vfZ5ConfBQe3SrG7f8JdnqrR7DfLAs9eG0Dmrm226zLFrqtogkRJa/zRGpHCEMnD+5DrFA X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1637; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;31:5X8CM7RAsynRxmWN2QuVQD2SR+IOetvOs9dv6kUW1wqYdQBvZIg1rn2jVNYAFy6OflNpLjvqsxB5sWg7xwDCbWqGX8z8z2wEr3+Pkof8BGmTG4upbNy4kiKuzliaswQS1Lhblwd2LVTa1hcKg5xL9lEZS8UpyriQ+2gUYuWgCYUtYc/Gwsib7yKUPI5dkvaz+uLDhw5rIBpA/bGHr0V/oPssDGybaF2K1/74SlsDKhRAB82UdxeveCCth+6Aylbz;4:ra3/NBZN99Phcxoqgz4dEcav2aeXvt5VpiOj427Q2O9nX/LwFIzdT8cINOE7le5YS2wXy5GqJDVCL88n6egmdI36QUv7MLB/O05fZVwJ65YZ+jree84mwjyHapM4cazI9+keSoz7cF53TU/HkWzJlt5x8A81SQwlFj9h35RBYDkLWwAzEhearaV3lwb3loXq3WmZTn0IbpLv+YtSMztrwf0xo0bN8EhvS1Whc6EOh2iffXlOfhis33720NqFe35a3Z0SrleTHASiQinW7CtD1O0GkIPYwe3HDjDwdQ9T7q3RDsb6pRAnLl4YZTrU60b528TLddcJat7sPV5HDiwvEpI70AR1/OY5rs+p2fl3K2m/XOq74ajHAxjmtenloJYFSIJHv9+YohN1nKOzRmNHUtDWv8EVy6kFum1S0NkDxlLyM9Ls90BeWrbvLB/99DoA 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)(6043046)(6042046);SRVR:CY4PR11MB1637;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1637; X-Forefront-PRVS: 008960E8EC X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(979002)(6009001)(7916002)(24454002)(199003)(189002)(377454003)(105586002)(101416001)(189998001)(68736007)(6666003)(5660300001)(4001350100001)(97736004)(76176999)(54356999)(50986999)(77096005)(305945005)(15975445007)(23676002)(16601075003)(230700001)(7736002)(65826007)(7846002)(69596002)(6916009)(2950100002)(64126003)(81156014)(50466002)(42186005)(1411001)(110136003)(19580395003)(36756003)(81166006)(92566002)(65956001)(8676002)(31696002)(6116002)(106356001)(586003)(33646002)(93886004)(53416004)(31686004)(83506001)(86362001)(65806001)(2906002)(4326007)(47776003)(3826002)(85282002)(969003)(989001)(999001)(1009001)(1019001);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1637;H:decina.attlocal.net;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtDWTRQUjExTUIxNjM3OzIzOlZQQlJJOFFhOHltdVJUNzlzM0NsOU5scmVy?= =?utf-8?B?MkZTTWVNclp6L0FmN2FpYkdHb2dYdUI2UDBvVE8vU0xkNG0rVFRLU3ZBazNF?= =?utf-8?B?alBzSjFBTExkTm5hTTNwbGxOYjMwMDBKR0thRXh2ZE5QZnBYOWtHL3N5M0h0?= =?utf-8?B?b1ZvN2EvMER0R2JKb0xqdE1uN1R5RGRYamZWaDRrNkVuQVIwUXFGcFA5bnJB?= =?utf-8?B?dGpHS0liSm03RW43NmxKS1g1VWJFRSt3SWVTM3REalhtMzRGaWQ1NWNrdTJp?= =?utf-8?B?YVQrVTFaWVg4RU4vcjZNN3BJY20ydjQwUVNUcjlORDJqVEQwdENNY3kzMHFz?= =?utf-8?B?UmtXbWZUYURzYjVkVWpIdlBvRU1mV1Jua1VFRlpDbk55SWxGRWpZTHUrSDIw?= =?utf-8?B?Mlp2WmhqeGFqbm0yWWM4dDNjK0ZwUjJtQzQ4U2plZHl1VGpIQUlUL2w3aDd3?= =?utf-8?B?WDFGaGZqODlESHE5b0p5K3JVQnlVUW1wMGRDLzY5cGhpOTZxUWkyc2hXc3R5?= =?utf-8?B?R0lmQlRMTjM2ZENSK3g5bjVTWlFTbXZBdUk3YjQzeXk5SmRWem5MZU9HV0gz?= =?utf-8?B?U2ZqS0dhQk9YOG8vZGhiN0Vkd2l3QmVmdFovS3BKNDlTNXlrQnVjZWliSE5s?= =?utf-8?B?VGtkYVlaMUNZN1NiTG5rR3gzSWozQXdNWUNML0ZWZWpFenFISzgrUXNZZERH?= =?utf-8?B?d3o3RXdVQVZZNVI5M3ZVUEdQK28vK2xycFRJTnpPVG81WVNTSVo4aTJ5MTNL?= =?utf-8?B?L1hiY0UrZ3VMbWNWQXR6T2w0TDR4MGp4QVd5Ykk0VWh5NEc1MHpQNzJzSEtv?= =?utf-8?B?RVFISytFRlRuTmV0SWc3a3RVNEY2aGxUeE1UZEpzUktmUzQyY2tLS3o3SWhZ?= =?utf-8?B?MklycXVZOXdrc2Q4QWwrMkN4aUVMWWE5b1ppYTE0b2lPUE93OERuNEVwNlR0?= =?utf-8?B?eDV6RVJWY0FtaklZOW9LWDNiRkxud2NuSWw0cnIzNU9JbTFkNWNIOE1LeWdD?= =?utf-8?B?WjVJd2o2cXVIcDZiM2FSWUpvOGVTcnZjTkp4b0RJcHVHSUJncGJLbEYvekEw?= =?utf-8?B?Y2hqRGhWYzRkYytjcnZPYisvSTRKclY5clZMWXJDWm1GWmxGbEMxQWxuUm9C?= =?utf-8?B?QkZiK0tNNm1KRjUvN0Z0eHR6VDFRVlBLaVh6cUxva0JvR1dtZUoxcDN3RFlO?= =?utf-8?B?bW10VUxHMFN3UWtDUVF0S202L1REQzVUbFhzVS9qQkw0blRtai9GL2ZEWHll?= =?utf-8?B?a1VpVXBva1VOVjhRZEJkbWM5T0ZWWmhDZUJJNjAvblovZkhYRUJZRzB1ZGxH?= =?utf-8?B?U1YxcUNBcXVlajJRTjgwelhUQ21hdnJxcnp5NkRpYWhxb3lQSS9IMzh5TDZS?= =?utf-8?B?V3pKQ3hhYTFTdWViTUw3R0xIeVlVNlJoT3FYcVZuNWlOSGljNzdpVE9iZUJW?= =?utf-8?B?a2FaMVRlUmhSK0FieEpXU1k3TzdJdVVBMzh1cTIwOHVGZWhKaHJ4WDg2MlQr?= =?utf-8?B?aHVTc0syUDMwTmVvblk5M0Nici90TVlNUkx1YkgrSDV1bkZYRXYzZUdRMDF4?= =?utf-8?B?Zk9SM1ZHTC9MTnplcVg2VllhN0xiTjVTa0V5RmRWSzFuSEM2K1ErZjBxUTBn?= =?utf-8?B?TG1hQnRCM3lMem1BdHRGZlVYVlhmc0NkMnZlb3V6WU1hU0FYR3g2Y0RnM3VF?= =?utf-8?B?bjNMMUpJaEptVWhVd1A3dFpkUkhEVGFBZ1FpMkVGb0p2TjFvV0t0dVNrV1NX?= =?utf-8?B?QUtZVWZ0SHR0VXhSdGdEcld4S1VOODdtcmU2cVk2TzRWaXprcVRSeGVPNlEw?= =?utf-8?B?R3hyTmw0bFZLM2ZGNGNETVh5Rys0S0FKV1dVajlFcTlPcTRLWWN6aW03cVhY?= =?utf-8?B?UFFWZEFTUFZDWXM1ZUFyQlhyZVhaWG9RSlJ1bkpSc0dTa3IzNlJFZWpQRHdh?= =?utf-8?B?bEZINUFxMTZVYjU1SGgrVmsrTWhzMjBZRmpwNGMzYUYxbUhOWkNYTXFidloz?= =?utf-8?B?VDNrMmRlK2N5S016SVh3V2VGdjdnNk1Ob0NhbHFHWnNDNElvdHg4Q1gxemtE?= =?utf-8?Q?m7K0=3D?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;6:vjlnssdRYV9MsnJx++ZmZute8i7Bd/TFTlLRlWtqn+ef8hCkroIHXpFX7ZRxPnIm5Hd090lIafmvuRuyLfX/YR8/20A5CbrjTmyBdUoQuU1RS1bkW0BERTKm53FrPO777tNFSzNao1b6R2ibuge3xp/NZjtpKGm4r3mMYSZ/BQy5g0PAGQUGriqn/zZC2BLVKeZO0W40N1Y/knPP+7/vJ/avsj2S/v33/oAZaC22SgChpFOf7ef1bXzvyXY/CfNNNcCnO9ICmBUVj0bSWERuD37xxwSl0/NwytrwK72+niHstA4GKjSgMtEMhLdSY5hBaolhvixKObK7XMP8xF9WSA==;5:2gHPxNwTcAgCLQWzbSMxK3Eo5+RLebU5BhUaz/nf0M1rleDc3qQLogmgXWMG6UeIh1J8cSn0yWWXSmIk89tDwYbYxG1AyW/n8GpAnomo6KL3vDs9qDJJ+3zMHZccU0b1bYym9cYp8SQzA7e49m1s0+szBTonfvedRqpPvSxK8O0=;24:0N+2MRY0adJCQqtx2WMLOXY62gy0dgxXJQe+q0vs+FOAip1/8zfWXJ68/edhJBT/TWbAr+3WeIkP0eUZww/QDieoRIkfGXU+puWsaS++F8c= SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1637;7:Wh+mPk7KYvsYfFIpC3D76heZ6O4GF1ClJEv5idgKKIaLwv9bZpNv0GXyVGhWDefpgMdMJ9RDwil5hni/UzU674Al+VBtPRjegCBFEChOBS+ZvxPwDtqsCGBWPm4nQlScpMSpsTrs4nGyszxKOF6xLyj6ncSmGPQ8odjDP2X4t2Ji8ywzJ+U6BDZ8/ZojSNrPNAxkZIugiydcsqRKdhl8Lx9vrxRtBzKMi7IkJeW6LY+G4gRTLx6kg6zWPZjOznj+8LBKBVQkfDdHKyR9dQM20sjYKDQIA1BZKW1fktKxcTBf5t5NAynuYGoxP+SaO23YlJ8KepcgEF3uAuTHxri9jA== X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 08 Oct 2016 21:42:06.5969 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1637 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 10/5/16 7:34 AM, Greg Spiegelberg wrote: > When you say "must do a vacuum of the entire database", are you saying > the entire database must be vacuum'd as a whole per 2B transactions or > all tables must be vacuum'd eventually at least once? All tables at least once. Prior to 9.6, that had to happen ever 2B transactions. With 9.6 there's a freeze map, so if a page never gets dirtied between vacuum freeze runs then it doesn't need to be frozen. -- 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