reducing NUMERIC size for 9.1
in [PgSql]
|
Prev: SHOW TABLES
Next: [HACKERS] Listen/Notify in 9.0
From: "David E. Wheeler" on 16 Jul 2010 12:03 On Jul 16, 2010, at 6:17 AM, Thom Brown wrote: > Joy! :) Nice patch Robert. Indeed. What are the implications for pg_upgrade? Will a database with values created before the patch continue to work after the patch has been applied (as happened with the new hstore in 9.0), or will pg_upgrade need to be taught how to upgrade the old storage format? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Bruce Momjian on 16 Jul 2010 12:04 David E. Wheeler wrote: > On Jul 16, 2010, at 6:17 AM, Thom Brown wrote: > > > Joy! :) Nice patch Robert. > > Indeed. > > What are the implications for pg_upgrade? Will a database with values > created before the patch continue to work after the patch has been > applied (as happened with the new hstore in 9.0), or will pg_upgrade > need to be taught how to upgrade the old storage format? Robert told me the old format continues to work in the upgraded databases. -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "David E. Wheeler" on 16 Jul 2010 12:06 On Jul 16, 2010, at 9:04 AM, Bruce Momjian wrote: >> What are the implications for pg_upgrade? Will a database with values >> created before the patch continue to work after the patch has been >> applied (as happened with the new hstore in 9.0), or will pg_upgrade >> need to be taught how to upgrade the old storage format? > > Robert told me the old format continues to work in the upgraded > databases. Awesome. rhaas++ Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Brendan Jurd on 16 Jul 2010 09:14 On 16 July 2010 22:51, Richard Huxton <dev(a)archonet.com> wrote: > On 16/07/10 13:44, Brendan Jurd wrote:> >> At this scale we should be seeing around 2 million bytes saved, but >> instead the tables are identical. �Is there some kind of disconnect in >> how the new short numeric is making it to the disk, or perhaps another >> effect interfering with my test? > > You've probably got rows being aligned to a 4-byte boundary. You're probably > not going to see any change unless you have a couple of 1-byte columns that > get placed after the numeric. If you went from 10 bytes down to 8, that > should be visible. Ah, thanks for the hint Richard. I didn't see any change with two 1-byte columns after the numeric, but with four such columns I did finally see a difference. Test script: BEGIN; CREATE TEMP TABLE foo (a numeric, b bool, c bool, d bool, e bool); INSERT INTO foo (a, b, c, d, e) SELECT 0::numeric, false, true, i % 2 = 0, i % 2 = 1 FROM generate_series(1, 1000000) i; SELECT pg_total_relation_size('foo'::regclass); ROLLBACK; Results: 8.4: 44326912 HEAD with patch: 36290560 That settles my concern and I'm happy to pass this along to a commiter. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Hitoshi Harada on 16 Jul 2010 13:03
2010/7/16 Brendan Jurd <direvus(a)gmail.com>: > On 16 July 2010 03:47, Robert Haas <robertmhaas(a)gmail.com> wrote: >> You might also look at testing with pg_column_size(). >> > > pg_column_size() did return the results I was expecting. > pg_column_size(0::numeric) is 8 bytes on 8.4 and it's 6 bytes on HEAD > with your patch. > > However, even with 1 million rows of 0::numeric in my test table, > there was no difference at all in the on-disk relation size (36290560 > with 36249600 in the table and 32768 in the fsm). > > At this scale we should be seeing around 2 million bytes saved, but > instead the tables are identical. �Is there some kind of disconnect in > how the new short numeric is making it to the disk, or perhaps another > effect interfering with my test? What about large ARRAY of numeric type? Once upon a time I develop tinyint for myself, the array size could get reduced. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |