Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XKwGt-0006uP-Vw for pgsql-performance@arkaria.postgresql.org; Fri, 22 Aug 2014 21:21:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XKwGt-0002LH-Fc for pgsql-performance@arkaria.postgresql.org; Fri, 22 Aug 2014 21:21:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XKwGs-0002LB-HJ for pgsql-performance@postgresql.org; Fri, 22 Aug 2014 21:21:18 +0000 Received: from tamriel.snowman.net ([2001:4830:167b::11]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XKwGp-00047z-P6 for pgsql-performance@postgresql.org; Fri, 22 Aug 2014 21:21:16 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 5BEE35F7FB; Fri, 22 Aug 2014 17:21:15 -0400 (EDT) Date: Fri, 22 Aug 2014 17:21:15 -0400 From: Stephen Frost To: Emi Lu Cc: pgsql-performance@postgresql.org, david.g.johnston@gmail.com Subject: Re: autocommit (true/false) for more than 1 million records Message-ID: <20140822212115.GB16422@tamriel.snowman.net> References: <53F7ACF4.1010604@encs.concordia.ca> <20140822210018.GZ16422@tamriel.snowman.net> <53F7B1E5.6030507@encs.concordia.ca> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="2JVjLP4z+A3AR3Vp" Content-Disposition: inline In-Reply-To: <53F7B1E5.6030507@encs.concordia.ca> X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/3.11.0-19-generic (x86_64) X-Uptime: 17:20:37 up 136 days, 7:39, 11 users, load average: 0.12, 0.10, 0.07 User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -2.6 (--) 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 --2JVjLP4z+A3AR3Vp Content-Type: text/plain; charset=us-ascii Content-Disposition: inline * Emi Lu (emilu@encs.concordia.ca) wrote: > >* > >>Trying to insert into one table with 1 million records through java > >>JDBC into psql8.3. May I know (1) or (2) is better please? > >> > >>(1) set autocommit(true) > >>(2) set autocommit(false) > >> commit every n records (e.g., 100, 500, 1000, etc) > >It depends on what you need. > > > >Data will be available to concurrent processes earlier with (1), while > >(2) will go faster. > No need to worry about the lock/loosing records because after data > loading will do a check. For now, I'd like the fastest way. Would > you suggest commit every 1000 or 3000 records? The improvement drops off pretty quickly in my experience, but it depends on the size of the records and other things. Try it and see..? It's almost certainly going to depend on your specific environment. Thanks, Stephen --2JVjLP4z+A3AR3Vp Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJT97RLAAoJEO1sijiDR2RV0jQP+QHv1gthhsZovYCsRmkfBOoF 10AE42H+MaJ3yzM57oqut42mKgpZdGla+W1P+1LBZ92Ub/6SFhgPEPT9f4I9MR8N lVJnxyojDUCb30wkxjYJxwtyWc8nRKmS64b88LoTRP8zlWK69E2oYBk2NGKrM91C 0psKMdXVeohyP6rCPFLHulXdSSHw0Cb/Qlcgsdnb8I4D6iO88UqIYqw52lJCQmam MJYwDd6wEqk2pFVw8clALe8MB50xYjJI2jfB91DXbr6nRfyixn5/vyKWpOmNRnrG 5Mx6Wh+QO7IPHPJY35PpqhMGrSqVs8tKIlSQunD5gh/NyGVOrR8wR2TebeDHfXPh k2DZGbbsPPWwgOUB1zyqq4HRsLNqPIApDWzhz9jIfw73uRUJrYNZ8OoRZj6dDwyT FvcW6yDxr+AHp+9bhUEeZN8RSTGnfVa9POpkElF/GhjJTc1E433GN93xOi/ohEWt zppJ+NvQssKofagYfxyDPPSKGdjAkHDw7ZgZsRNNWYObQvj6ltQ4+yWHiYicFR/f ihxmJtE8M0FeowWKA4AteWrrzPSvvPDiHAQkySgjURGUjIdeOy7uYMzeHQMMQJrz mThuI5e3Mophhemxh+cKZOomOes0gzuFzQ+oL4NxqYlx4cifUhhwtozl24by86zm DuuDXMgGqO1jQqDLG6wp =yjJM -----END PGP SIGNATURE----- --2JVjLP4z+A3AR3Vp--