Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On Sat, Feb 21, 2015 at 5:33 AM, Arjen Nienhuis a.g.nienh...@gmail.com wrote: Hi, On 19 Feb 2015 17:12, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. Postgres can use almost anything as a key so it probably depends on the library you use to access the database. If it supports composite primary keys you can use the 6 ints as a key: PRIMARY KEY (n1, n2, n3, n4, n5, n6) The numeric type can hold 192-bit numbers. I think Lazarus supports this as well. You could also use a surrogate key and define a UNIQUE constraint on the 6 ints or the 192-bit number. You could also use 3 64 bit bigints if that's easier. The other way to do it is bytea. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On 25/02/15 15:42, Merlin Moncure wrote: On Sat, Feb 21, 2015 at 5:33 AM, Arjen Nienhuis a.g.nienh...@gmail.com wrote: Hi, On 19 Feb 2015 17:12, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. Postgres can use almost anything as a key so it probably depends on the library you use to access the database. If it supports composite primary keys you can use the 6 ints as a key: PRIMARY KEY (n1, n2, n3, n4, n5, n6) The numeric type can hold 192-bit numbers. I think Lazarus supports this as well. You could also use a surrogate key and define a UNIQUE constraint on the 6 ints or the 192-bit number. You could also use 3 64 bit bigints if that's easier. The other way to do it is bytea. The other way to do it is to have semantically-meaningful columns rather than glomming them into this 192-bit integer, and a composite key on the lot - if the set truly is unique. -- Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
Hi, On 19 Feb 2015 17:12, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. Postgres can use almost anything as a key so it probably depends on the library you use to access the database. If it supports composite primary keys you can use the 6 ints as a key: PRIMARY KEY (n1, n2, n3, n4, n5, n6) The numeric type can hold 192-bit numbers. I think Lazarus supports this as well. You could also use a surrogate key and define a UNIQUE constraint on the 6 ints or the 192-bit number.
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On 02/19/2015 10:19 AM, brian wrote: On Thu, 19 Feb 2015 09:30:57 -0700, you wrote: On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. If your application understands/parses/makes use of the data in those 192 bites, I would reload with an additional unique id field. For the intended number of rows of data a sequence would be fine, though I'm partial to UUIDs. Alternatively map the 192 bytes to two fields and make a unique key of both of them. Third alternative would be to use a binary BitString a suggested by Brian. Thanks. The purpose of the field is purely as a check against the user feeding the same data in twice. Once I've constructed it, I never pull the field apart again. It had to be done this way, as otherwise the boolean statement to check for uniqueness was horrendous. Brian. Then B. Dunavant's suggestion is probably best. Certainly easiest. How (else) does your app or reporting query this data? That could also effect your choice.
[GENERAL] Some indexing advice for a Postgres newbie, please?
Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
You should consider a BitString. http://www.postgresql.org/docs/9.4/static/datatype-bit.html On Thu, Feb 19, 2015 at 11:10 AM, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. If your application understands/parses/makes use of the data in those 192 bites, I would reload with an additional unique id field. For the intended number of rows of data a sequence would be fine, though I'm partial to UUIDs. Alternatively map the 192 bytes to two fields and make a unique key of both of them. Third alternative would be to use a binary BitString a suggested by Brian.
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On Thu, Feb 19, 2015 at 2:14 PM, Brian Dunavant br...@omniti.com wrote: You should consider a BitString. http://www.postgresql.org/docs/9.4/static/datatype-bit.html On Thu, Feb 19, 2015 at 11:10 AM, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I suggest the bytea type.
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On Thu, 19 Feb 2015 09:30:57 -0700, you wrote: On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. If your application understands/parses/makes use of the data in those 192 bites, I would reload with an additional unique id field. For the intended number of rows of data a sequence would be fine, though I'm partial to UUIDs. Alternatively map the 192 bytes to two fields and make a unique key of both of them. Third alternative would be to use a binary BitString a suggested by Brian. Thanks. The purpose of the field is purely as a check against the user feeding the same data in twice. Once I've constructed it, I never pull the field apart again. It had to be done this way, as otherwise the boolean statement to check for uniqueness was horrendous. Brian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
