Re: [GENERAL] Table Design for Many Updates

, Craig From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Tuesday, January 10, 2017 1:48 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Table Design for Many Updates On 1/10/2017 1:42 PM, David G. Johnston wrote

Re: [GENERAL] Table Design for Many Updates

: Re: [GENERAL] Table Design for Many Updates On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher mailto:cr...@wesvic.com> > wrote: I have a multi-tenant database that I'm migrating from SQL Server to PostgreSQL 9.6.1. I read the recent articles about the potential write amplificatio

Re: [GENERAL] Table Design for Many Updates

On 1/10/2017 1:42 PM, David G. Johnston wrote: On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher >wrote: I have a multi-tenant database that I'm migrating from SQL Server to PostgreSQL 9.6.1. I read the recent articles about the potential write amplification is

Re: [GENERAL] Table Design for Many Updates

On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher wrote: > I have a multi-tenant database that I'm migrating from SQL Server to > PostgreSQL 9.6.1. I read the recent articles about the potential write > amplification issue in Postgres. I have one particular table that has 14 > columns, a primary k

[GENERAL] Table Design for Many Updates

I have a multi-tenant database that I'm migrating from SQL Server to PostgreSQL 9.6.1. I read the recent articles about the potential write amplification issue in Postgres. I have one particular table that has 14 columns, a primary key, five foreign keys, and eight indexes. We have a little over

Re: [GENERAL] table design and data type choice

On Wed, Jan 8, 2014 at 3:11 PM, Jayadevan M wrote: > We have a table to record the voteup/votedown by users of questions and > answers (like on stackoverflow). So there will be a large number of inserts > (voteup/down), some updates(user changes mind)and may be a few deletes. The > queries will be

[GENERAL] table design and data type choice

Hi, We have a table to record the voteup/votedown by users of questions and answers (like on stackoverflow). So there will be a large number of inserts (voteup/down), some updates(user changes mind)and may be a few deletes. The queries will be mostly aggregates (count(*) where question_id= and vo

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On Mon, Nov 21, 2011 at 6:14 PM, Tomas Vondra wrote: . >> >> An index on (a, b) can be used for queries involving only a but not for >> those involving only b. > > That is not true since 8.2 - a multi-column index may be used even for > queries without conditions on leading columns. It won't b

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

> On Nov 20, 2011, at 21:33, Phoenix Kiula wrote: > > My big table now has about 70 million rows, with the following columns: > > alias | character varying(35) > url | text > modify_date | timestamp without time zone > ip | bigint > > > For each IP addres

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On 21 Listopad 2011, 4:17, David Johnston wrote: > On Nov 20, 2011, at 20:50, Phoenix Kiula wrote: > >> On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower >> wrote: >> >>> How about having 2 indexes: one on each of ip & url_md5? Pg will >>> combine the >>> indexes as required, or will just use one if

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On 21/11/11 14:50, Phoenix Kiula wrote: On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower wrote: How about having 2 indexes: one on each of ip& url_md5? Pg will combine the indexes as required, or will just use one if that is best. Thanks Gavin. Question: what if I have a joined index? If fro

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On Nov 20, 2011, at 20:50, Phoenix Kiula wrote: > On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower > wrote: > >> How about having 2 indexes: one on each of ip & url_md5? Pg will combine the >> indexes as required, or will just use one if that is best. > > > > Thanks Gavin. Question: what if I h

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

Hi, On 21 November 2011 00:33, Phoenix Kiula wrote: > Hi. > > Want to start another thread, loosely related to the performance > problems thread I have going. > > Need some DB design guidance from the gurus here. > > My big table now has about 70 million rows, with the following columns: You can

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower wrote: > How about having 2 indexes: one on each of ip & url_md5? Pg will combine the > indexes as required, or will just use one if that is best. Thanks Gavin. Question: what if I have a joined index? If from a joined index I only use the first co

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On 21/11/11 02:33, Phoenix Kiula wrote: Hi. Want to start another thread, loosely related to the performance problems thread I have going. Need some DB design guidance from the gurus here. My big table now has about 70 million rows, with the following columns: alias | character v

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On Sunday, November 20, 2011 7:12:59 am Phoenix Kiula wrote: > On Sun, Nov 20, 2011 at 9:33 PM, Phoenix Kiula wrote: > > I thought of adding a bigserial (serial8) column instead of > varchar(32) for the md5. But postgresql tells me that: > > -- > ERROR: type "bigserial" does not exist > -- >

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

On Nov 20, 2011, at 8:33, Phoenix Kiula wrote: > Hi. > > Want to start another thread, loosely related to the performance > problems thread I have going. > > Need some DB design guidance from the gurus here. > > My big table now has about 70 million rows, with the following columns: > > > al

Re: [GENERAL] Table Design question for gurus (without going to "NoSQL")...

partition your table if it is too big. -- 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] Table Design question for gurus (without going to "NoSQL")...

On Sun, Nov 20, 2011 at 9:33 PM, Phoenix Kiula wrote: > Hi. > > Want to start another thread, loosely related to the performance > problems thread I have going. > > Need some DB design guidance from the gurus here. > > My big table now has about 70 million rows, with the following columns: > > >  

[GENERAL] Table Design question for gurus (without going to "NoSQL")...

Hi. Want to start another thread, loosely related to the performance problems thread I have going. Need some DB design guidance from the gurus here. My big table now has about 70 million rows, with the following columns: alias | character varying(35) url | text modify_

Re: [GENERAL] Table design - postgresql solution

> Hi, > > I have a bit of a DB design question, associated with postgresql in > particular, hopefully thinking it could solve my dilemma. > > This is my setup of 3 tables: > > Table_1 > id_t1 > name > date_of_discovery > history > > Table_2 > id_t2 > name > type > size > > Table_3 > id_t3 > name >

Re: [GENERAL] Table design - postgresql solution

Sat, 4 Dec 2010 13:24:27 + Miguel Vaz > Your solution seems great and does the trick, but if most (and having > some items that relate to all 3 tables) of my relations are between > two tables, close to 1/3 of the id_tx fields would be null, correct? > I may be wrong in thinking like this, bu

Re: [GENERAL] Table design - postgresql solution

Miguel, The idea of a table "relations" is good. It is a common solution and it is called "bridge", "cross-reference", "many-to-many resolver" or a "join table". Querying such a table would be easier if you had as many columns, as related tables. So for Table_1, Table_2 and Table_3 have a join

[GENERAL] Table design - postgresql solution

Hi, I have a bit of a DB design question, associated with postgresql in particular, hopefully thinking it could solve my dilemma. This is my setup of 3 tables: Table_1 id_t1 name date_of_discovery history Table_2 id_t2 name type size Table_3 id_t3 name location color I want a solution (table

Re: [GENERAL] Table design - unknown number of column

On 11/9/06, Richard Ollier <[EMAIL PROTECTED]> wrote: Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase f

Re: [GENERAL] Table design - unknown number of column

On Thursday 9. November 2006 09:34, Richard Ollier wrote: >Hello, > >For a project I have a table containing products and flags. >The columns of this table are of 2 kinds : >- Not null data (id, column1, column2) >- Flags (100 different flags set to 1 or 0) > >Over the time the number of flag will

Re: [GENERAL] Table design - unknown number of column

I think the typical way of attacking a problem would be a second and third table. The second table would look like: flat_type table flag_type_id flag_type (like the column name in your original table) flag_type_description (BONUS: you can describe each flag) product_flag table product_

Re: [GENERAL] Table design - unknown number of column

Alban Hertroys wrote: Richard Ollier wrote: Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 t

Re: [GENERAL] Table design - unknown number of column

On fim, 2006-11-09 at 10:56 +0100, Alban Hertroys wrote: > Richard Ollier wrote: > > For a project I have a table containing products and flags. > > The columns of this table are of 2 kinds : > > - Not null data (id, column1, column2) > > - Flags (100 different flags set to 1 or 0) > > > > Over th

Re: [GENERAL] Table design - unknown number of column

Richard Ollier wrote: Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 to 200 or more. Having

[GENERAL] Table design - unknown number of column

Hello, For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 to 200 or more. So I have 2 choices : - Use a

Re: [GENERAL] Table design

On 12/2/05 10:21 AM, "Guy Rouillier" <[EMAIL PROTECTED]> wrote: > Sean Davis wrote: >> This might be a bit off-topic, but I'm curious what folks would do >> with this >> situation: >> >> I have about 6 different tab-delimited file types, all of which store >> similar information (microarray gene

Re: [GENERAL] Table design

Sean Davis wrote: > This might be a bit off-topic, but I'm curious what folks would do > with this > situation: > > I have about 6 different tab-delimited file types, all of which store > similar information (microarray gene expression). However, the files > come from different manufacturers, so

Re: [GENERAL] Table design

Hi Sean, We use something similar to approach 1) to store our microarray data. We have a data table that has a few specific columns (signal median, bkg median etc) as these exist in all the file formats... Plus also some generic columns for the rest of the data fields. Then we have a definition

Re: [GENERAL] Table design

Perhaps this is not a postgresql solution, but you could; 1. first design a common data structure in postgresql. 2. then convert each type of the tab-delimited file to a basic xml structures. 3. map the structures to the common data structure using xslt. I hope this helps, > This might be a bit

[GENERAL] Table design

This might be a bit off-topic, but I'm curious what folks would do with this situation: I have about 6 different tab-delimited file types, all of which store similar information (microarray gene expression). However, the files come from different manufacturers, so each has slightly different fiel

Re: [GENERAL] table design question

George, I am brand new to PostgreSQL and am just now getting my info together for a possible deployment on AIX. I have however worked with several large(in my frame of thinking) 6 million record databases on MSSQL. I did a ton of reading, here is what I've read, what I use and am pleased with:

Re: [GENERAL] Table Design: Timestamp vs time/date

Dale Walker <[EMAIL PROTECTED]> writes: > Having a 'timestamp' field 'CCYY-MM-DD HH:MM:SS.SS' or two separate > fields one for time 'HH:MM:SS.SS' and one for Date 'CCYY-MM-DD'. Go for the timestamp. Otherwise you'll be cursing yourself the first time someone wants to know about "all logins betwe

[GENERAL] Table Design: Timestamp vs time/date

I'm currently setting up a table to contain user login/logout information. Just wondered what would be more scalable??better to index,etc... Having a 'timestamp' field 'CCYY-MM-DD HH:MM:SS.SS' or two separate fields one for time 'HH:MM:SS.SS' and one for Date 'CCYY-MM-DD'. most queries will be o