pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVarchar vs foreign key vs enumerator - table and index size
10+ messages / 6 participants
[nested] [flat]
* Varchar vs foreign key vs enumerator - table and index size
@ 2013-08-31 13:35 Łukasz Walkowski <lukasz.walkowski@homplex.pl>
0 siblings, 3 replies; 10+ messages in thread
From: Łukasz Walkowski @ 2013-08-31 13:35 UTC (permalink / raw)
To: pgsql-performance
Hi,
This is my first post on this group so welcome everyone! Currently I'm working on optimizing a quite simple database used to store events from one website. Every event is a set of data describing user behaviour. The main table that stores all events is built using schema:
Column | Type | Modifiers
-----------------+-----------------------------+-----------
id | bigint | not null
browser | character varying(255) |
created | timestamp without time zone |
eventsource | character varying(255) |
eventtype | character varying(255) |
ipaddress | character varying(255) |
objectid | bigint |
sessionid | character varying(255) |
shopids | integer[] |
source | character varying(255) |
sourceid | bigint |
supplierid | bigint |
cookieuuid | uuid |
serializeddata | bytea |
devicetype | character varying(255) |
operatingsystem | character varying(255) |
It was a quick project to play with EclipseLink, Hibernate and some Jersey Rest services, so isn't perfect. However the database became quite usefull and we decided to optimize this table as it grew quite large (128GB right now without indexes, about 630M records). There is only primary key index on this table. Here is the list of changes that I'd like to make to the table (some of them should be done from the scratch):
1. Changing ipaddress from varchar to inet - this should save some space and lower the size of potential index.
2. Changing id for some composite id with created contained in it.
3. And this part is most interesting for me. Columns browser, eventsource, eventtype, devicetype, operatingsystem contain a small pool of strings - for example for devicetype this is set to Computer, Mobile, Tablet or Unknown. Browser is set to normalized browser name. In every case I can store those data using one of 3 different methods:
- store as varchar as it is now - nice and easy, but index on those columns is quite big and I think storing many of similar strings is waste of space.
- store only id's and join external tables as needed, for example for browsers I only need smallint key, as there is a limited number of browsers. The column browser becomes smallint and we have additional table with two columns (id, browser varchar). This should save some space on event table, but if I want name of the browser in some report I need to join tables. Second thing - on every insert there is constraint that is checked for this field and this can affect performance. I was thinking about the same strategy for the remaining fields - this would give me 5 additional tables and 5 additional constraints on event table. Browser table will have about ~100 records, eventtype and eventsource will have about 8-12 records each, devicetype - 4 records, operatingsystem - didn't really check this one, but I think something around 100 like browser.
- introduce enumerator type for each of the column and store those values as enumerator. This one should be the most space efficient, but it will be problematic in case of changing column values like browser or operatingsystem as altering enumerator isn't that simple.
For browser average text length is 19 characters, for eventsource and eventtype eventsource average text lenght is 24 characters. Database encoding is set to UTF8.
My question is - what is estimated difference in table size between those 3 variants of storing columns? In theory third one should give me the smallest database and index size but is the most problematic from all of the above.
Lukasz Walkowski
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-08-31 15:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2013-08-31 15:20 UTC (permalink / raw)
To: Łukasz Walkowski <lukasz.walkowski@homplex.pl>; +Cc: pgsql-performance
=?utf-8?Q?=C5=81ukasz_Walkowski?= <lukasz.walkowski@homplex.pl> writes:
> 3. And this part is most interesting for me. Columns browser, eventsource, eventtype, devicetype, operatingsystem contain a small pool of strings - for example for devicetype this is set to Computer, Mobile, Tablet or Unknown. Browser is set to normalized browser name. In every case I can store those data using one of 3 different methods:
> - store as varchar as it is now - nice and easy, but index on those columns is quite big and I think storing many of similar strings is waste of space.
If you're starting to be concerned about space, it's definitely time to
get away from this choice. Depending on what locale you're using,
comparing varchar values can be quite an expensive operation, too.
> - store only id's and join external tables as needed, for example for browsers I only need smallint key, as there is a limited number of browsers.
I think the main "pro" of this approach is that it doesn't use any
nonstandard SQL features, so you preserve your options to move to some
other database in the future. The main "con" is that you'd be buying into
fairly significant rewriting of your application code, since just about
every query involving these columns would have to become a join.
FWIW, I'd be inclined to just use integer not smallint. The space savings
from smallint is frequently illusory because of alignment considerations
--- for instance, an index on a single smallint column will *not* be any
smaller than one on a single int column. And smallint has some minor
usage annoyances because it's a second-class citizen in the type promotion
hierarchy --- you may find yourself needing explicit casts to smallint
here and there.
> - introduce enumerator type for each of the column and store those values as enumerator. This one should be the most space efficient, but it will be problematic in case of changing column values like browser or operatingsystem as altering enumerator isn't that simple.
Space-wise this is going to be equivalent to the integer-foreign-key
solution. It's much nicer from a notational standpoint, though, because
you don't need joins --- it's likely that you'd need few if any
application code changes to go this route. (But I'd advise doing some
testing to verify that before you take it as a given.)
You're right though that enums are not a good option if you expect
frequent changes in the pool of allowed values. I guess the question
is how often does that happen, in your application? Adding a new value
from time to time isn't much of a problem unless you want to get picky
about how it sorts relative to existing values. But you can't ever delete
an individual enum value, and we don't support renaming them either.
(Though if you're desperate, I believe a manual UPDATE on the pg_enum
catalog would work for that.)
Another thing to think about is whether you have auxiliary data about each
value that might usefully be stored as additional columns in the small
tables. The enum approach doesn't directly handle that, though I suppose
you could still create small separate tables that use an enum column as
primary key.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-08-31 17:06 Łukasz Walkowski <lukasz.walkowski@homplex.pl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Łukasz Walkowski @ 2013-08-31 17:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom,
> If you're starting to be concerned about space, it's definitely time to
> get away from this choice. Depending on what locale you're using,
> comparing varchar values can be quite an expensive operation, too.
I don't like wasting space and processing power even if more work is required to achieve this. We use pl_PL.UTF-8 as our locale.
> I think the main "pro" of this approach is that it doesn't use any
> nonstandard SQL features, so you preserve your options to move to some
> other database in the future. The main "con" is that you'd be buying into
> fairly significant rewriting of your application code, since just about
> every query involving these columns would have to become a join.
Well, I don't really think I will move from Postgresql anytime soon. It's just the best database for me. Rewriting code is one of the things I'm doing right now but before I touch database, I want to be sure that the choices I made are good.
> FWIW, I'd be inclined to just use integer not smallint. The space savings
> from smallint is frequently illusory because of alignment considerations
> --- for instance, an index on a single smallint column will *not* be any
> smaller than one on a single int column. And smallint has some minor
> usage annoyances because it's a second-class citizen in the type promotion
> hierarchy --- you may find yourself needing explicit casts to smallint
> here and there.
Ok, thats important information. Thank you.
>
> Space-wise this is going to be equivalent to the integer-foreign-key
> solution. It's much nicer from a notational standpoint, though, because
> you don't need joins --- it's likely that you'd need few if any
> application code changes to go this route. (But I'd advise doing some
> testing to verify that before you take it as a given.)
>
> You're right though that enums are not a good option if you expect
> frequent changes in the pool of allowed values. I guess the question
> is how often does that happen, in your application? Adding a new value
> from time to time isn't much of a problem unless you want to get picky
> about how it sorts relative to existing values. But you can't ever delete
> an individual enum value, and we don't support renaming them either.
> (Though if you're desperate, I believe a manual UPDATE on the pg_enum
> catalog would work for that.)
>
> Another thing to think about is whether you have auxiliary data about each
> value that might usefully be stored as additional columns in the small
> tables. The enum approach doesn't directly handle that, though I suppose
> you could still create small separate tables that use an enum column as
> primary key.
>
> regards, tom lane
So, I'll go for enumerators for device type, eventtype and eventsource as those columns are quite stable. For browser and operating system I'll do external tables.
Thank you - any additional tips are welcome.
Reagards,
Lukasz Walkowski
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-01 01:31 Craig James <cjames@emolecules.com>
parent: Łukasz Walkowski <lukasz.walkowski@homplex.pl>
0 siblings, 1 reply; 10+ messages in thread
From: Craig James @ 2013-09-01 01:31 UTC (permalink / raw)
To: Łukasz Walkowski <lukasz.walkowski@homplex.pl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Sat, Aug 31, 2013 at 10:06 AM, Łukasz Walkowski <
lukasz.walkowski@homplex.pl> wrote:
> > I think the main "pro" of this approach is that it doesn't use any
> > nonstandard SQL features, so you preserve your options to move to some
> > other database in the future. The main "con" is that you'd be buying
> into
> > fairly significant rewriting of your application code, since just about
> > every query involving these columns would have to become a join.
>
> Well, I don't really think I will move from Postgresql anytime soon. It's
> just the best database for me. Rewriting code is one of the things I'm
> doing right now but before I touch database, I want to be sure that the
> choices I made are good.
>
If your applications are read-heavy and only have a small-ish amount of
code that inserts/updates the table, it may not be that much of a rewrite.
You can create a integer/varchar table of key/values, use its key to
replace the current varchar column, rename the original table, and create a
view with the original table's name. Code that only reads the data won't
know the difference. And it's a portable solution.
I did this and it worked out well. If the key/value pairs table is
relatively small, the planner does an excellent job of generating efficient
queries against the big table.
Craig
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-01 03:10 Vitalii Tymchyshyn <tivv00@gmail.com>
parent: Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2 siblings, 1 reply; 10+ messages in thread
From: Vitalii Tymchyshyn @ 2013-09-01 03:10 UTC (permalink / raw)
To: Łukasz Walkowski <lukasz.walkowski@homplex.pl>; +Cc: pgsql-performance
2013/8/31 Łukasz Walkowski <lukasz.walkowski@homplex.pl>
>
> 3. And this part is most interesting for me. Columns browser, eventsource,
> eventtype, devicetype, operatingsystem contain a small pool of strings -
> for example for devicetype this is set to Computer, Mobile, Tablet or
> Unknown. Browser is set to normalized browser name. In every case I can
> store those data using one of 3 different methods:
>
>
Well, there are some more options:
a) Store int keys and do mapping in the application (e.g. with java enums).
This can save you a join, that is especially useful if you are going to do
paged output with limit/offset scenario. Optimizer sometimes produce
suboptimal plans for join in offset/limit queries.
b) Store small varchar values as keys (up to "char" type if you really want
to save space) and do user display mapping in application. It's different
from (a) since it's harder to mess with the mapping and values are still
more or less readable with simple select. But it can be less efficient than
(a).
c) Do mixed approach with mapping table, loaded on start into application
memory. This would be an optimization in case you get into optimizer
troubles.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-02 09:47 Łukasz Walkowski <lukasz.walkowski@homplex.pl>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 10+ messages in thread
From: Łukasz Walkowski @ 2013-09-02 09:47 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On 1 wrz 2013, at 03:31, Craig James <cjames@emolecules.com> wrote:
> If your applications are read-heavy and only have a small-ish amount of code that inserts/updates the table, it may not be that much of a rewrite. You can create a integer/varchar table of key/values, use its key to replace the current varchar column, rename the original table, and create a view with the original table's name. Code that only reads the data won't know the difference. And it's a portable solution.
>
> I did this and it worked out well. If the key/value pairs table is relatively small, the planner does an excellent job of generating efficient queries against the big table.
>
> Craig
Actually this (event) table is write heavy. But the concept is really cool and worth trying. Thanks.
Lukasz
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-02 09:53 Łukasz Walkowski <lukasz.walkowski@homplex.pl>
parent: Vitalii Tymchyshyn <tivv00@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Łukasz Walkowski @ 2013-09-02 09:53 UTC (permalink / raw)
To: Vitalii Tymchyshyn <tivv00@gmail.com>; +Cc: pgsql-performance
On 1 wrz 2013, at 05:10, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
>
>
> Well, there are some more options:
> a) Store int keys and do mapping in the application (e.g. with java enums). This can save you a join, that is especially useful if you are going to do paged output with limit/offset scenario. Optimizer sometimes produce suboptimal plans for join in offset/limit queries.
> b) Store small varchar values as keys (up to "char" type if you really want to save space) and do user display mapping in application. It's different from (a) since it's harder to mess with the mapping and values are still more or less readable with simple select. But it can be less efficient than (a).
> c) Do mixed approach with mapping table, loaded on start into application memory. This would be an optimization in case you get into optimizer troubles.
>
> Best regards, Vitalii Tymchyshyn
I'd like to leave database in readable form because before I add some new queries and rest endpoints to the application, I test them as ad-hoc queries using command line. So variant a) isn't good for me. Variant b) is worth trying and c) is easy to code, but I still prefer having all this data in database independent of application logic.
Thanks for suggestion,
Lukasz
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-02 17:02 Andrew Dunstan <andrew@dunslane.net>
parent: Łukasz Walkowski <lukasz.walkowski@homplex.pl>
0 siblings, 1 reply; 10+ messages in thread
From: Andrew Dunstan @ 2013-09-02 17:02 UTC (permalink / raw)
To: Łukasz Walkowski <lukasz.walkowski@homplex.pl>; +Cc: Vitalii Tymchyshyn <tivv00@gmail.com>; pgsql-performance
On 09/02/2013 05:53 AM, Łukasz Walkowski wrote:
> On 1 wrz 2013, at 05:10, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
>>
>> Well, there are some more options:
>> a) Store int keys and do mapping in the application (e.g. with java enums). This can save you a join, that is especially useful if you are going to do paged output with limit/offset scenario. Optimizer sometimes produce suboptimal plans for join in offset/limit queries.
>> b) Store small varchar values as keys (up to "char" type if you really want to save space) and do user display mapping in application. It's different from (a) since it's harder to mess with the mapping and values are still more or less readable with simple select. But it can be less efficient than (a).
>> c) Do mixed approach with mapping table, loaded on start into application memory. This would be an optimization in case you get into optimizer troubles.
>>
>> Best regards, Vitalii Tymchyshyn
> I'd like to leave database in readable form because before I add some new queries and rest endpoints to the application, I test them as ad-hoc queries using command line. So variant a) isn't good for me. Variant b) is worth trying and c) is easy to code, but I still prefer having all this data in database independent of application logic.
>
I think the possible use of Postgres enums has been too easily written
off in this thread. Looking at the original problem description they
look like quite a good fit, despite the OP's skepticism. What exactly is
wanted that can't be done with database enums? You can add new values to
the type very simply. You can change the values of existing labels in
the type slightly less simply, but still without any great difficulty.
Things that are hard to do include removing labels in the set and
changing the sort order, because those things would require processing
tables where the type is used, unlike the simple things. But neither of
these is required for typical use cases. For most uses of this kind they
are very efficient both in storage and processing.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-03 19:00 Vitalii Tymchyshyn <tivv00@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 10+ messages in thread
From: Vitalii Tymchyshyn @ 2013-09-03 19:00 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Łukasz Walkowski <lukasz.walkowski@homplex.pl>; pgsql-performance
Well, in older version of Hibernate it was a little tricky to handle
Postgresql Enums. Dunno if it's out of the box now.
Also adding new value is an explicit operation (much like with lookup
table). I've had quite a complex code with second connection opening to
support lookup table filling without flooding original transaction with
additional locks that could lead to deadlocks.
BTW: Does adding new value to enum adds some locks? Can a check if value
exists and adding new value be done in atomic fashion without grabbing some
global lock?
P.S. As I see, it can be a topic for good article for, say, dzone. The
problem can be quite tricky in MVCC database and choice must be done wisely.
Best regards, Vitalii Tymchyshyn
2013/9/2 Andrew Dunstan <andrew@dunslane.net>
>
> On 09/02/2013 05:53 AM, Łukasz Walkowski wrote:
>
>> On 1 wrz 2013, at 05:10, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
>>
>>>
>>> Well, there are some more options:
>>> a) Store int keys and do mapping in the application (e.g. with java
>>> enums). This can save you a join, that is especially useful if you are
>>> going to do paged output with limit/offset scenario. Optimizer sometimes
>>> produce suboptimal plans for join in offset/limit queries.
>>> b) Store small varchar values as keys (up to "char" type if you really
>>> want to save space) and do user display mapping in application. It's
>>> different from (a) since it's harder to mess with the mapping and values
>>> are still more or less readable with simple select. But it can be less
>>> efficient than (a).
>>> c) Do mixed approach with mapping table, loaded on start into
>>> application memory. This would be an optimization in case you get into
>>> optimizer troubles.
>>>
>>> Best regards, Vitalii Tymchyshyn
>>>
>> I'd like to leave database in readable form because before I add some new
>> queries and rest endpoints to the application, I test them as ad-hoc
>> queries using command line. So variant a) isn't good for me. Variant b) is
>> worth trying and c) is easy to code, but I still prefer having all this
>> data in database independent of application logic.
>>
>>
>
> I think the possible use of Postgres enums has been too easily written off
> in this thread. Looking at the original problem description they look like
> quite a good fit, despite the OP's skepticism. What exactly is wanted that
> can't be done with database enums? You can add new values to the type very
> simply. You can change the values of existing labels in the type slightly
> less simply, but still without any great difficulty. Things that are hard
> to do include removing labels in the set and changing the sort order,
> because those things would require processing tables where the type is
> used, unlike the simple things. But neither of these is required for
> typical use cases. For most uses of this kind they are very efficient both
> in storage and processing.
>
> cheers
>
> andrew
>
--
Best regards,
Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Varchar vs foreign key vs enumerator - table and index size
@ 2013-09-12 15:07 Jim Nasby <jim@nasby.net>
parent: Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2 siblings, 0 replies; 10+ messages in thread
From: Jim Nasby @ 2013-09-12 15:07 UTC (permalink / raw)
To: Łukasz Walkowski <lukasz.walkowski@homplex.pl>; +Cc: pgsql-performance
On 8/31/13 8:35 AM, Łukasz Walkowski wrote:
> 3. And this part is most interesting for me. Columns browser, eventsource, eventtype, devicetype, operatingsystem contain a small pool of strings - for example for devicetype this is set to Computer, Mobile, Tablet or Unknown. Browser is set to normalized browser name. In every case I can store those data using one of 3 different methods:
Sorry for the late reply... the Enova Tools project in pgFoundry has code that lets you create a "dynamic lookup table" that allows for easily normalizing slow-changing data. You could then put a writable view on top of that so that the app wouldn't know the difference. It also has a backfill framework that would help you move data from the old table to the new table.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2013-09-12 15:07 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-31 13:35 Varchar vs foreign key vs enumerator - table and index size Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2013-08-31 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-31 17:06 ` Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2013-09-01 01:31 ` Craig James <cjames@emolecules.com>
2013-09-02 09:47 ` Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2013-09-01 03:10 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2013-09-02 09:53 ` Łukasz Walkowski <lukasz.walkowski@homplex.pl>
2013-09-02 17:02 ` Andrew Dunstan <andrew@dunslane.net>
2013-09-03 19:00 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2013-09-12 15:07 ` Jim Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox