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

eneral@postgresql.org Subject: Re: [GENERAL] Table Design for Many Updates On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher <cr...@wesvic.com <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 rec

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

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 >

[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

Re: [GENERAL] table design and data type choice

On Wed, Jan 8, 2014 at 3:11 PM, Jayadevan M maymala.jayade...@gmail.comwrote: 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

[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=x and

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

On Mon, Nov 21, 2011 at 6:14 PM, Tomas Vondra t...@fuzzy.cz 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

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 phoenix.ki...@gmail.com wrote: On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: How about having 2 indexes: one on each of ip url_md5? Pg will combine the indexes as

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

On Nov 20, 2011, at 21:33, Phoenix Kiula phoenix.ki...@gmail.com 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

[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

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

On Sun, Nov 20, 2011 at 9:33 PM, Phoenix Kiula phoenix.ki...@gmail.com 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

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 Nov 20, 2011, at 8:33, Phoenix Kiula phoenix.ki...@gmail.com 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

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 phoenix.ki...@gmail.com 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

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

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

On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower gavinflo...@archidevsys.co.nz 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

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

Hi, On 21 November 2011 00:33, Phoenix Kiula phoenix.ki...@gmail.com 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

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

On Nov 20, 2011, at 20:50, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower gavinflo...@archidevsys.co.nz 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.

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 gavinflo...@archidevsys.co.nz 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

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 location color I

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 table:

Re: [GENERAL] Table design - postgresql solution

Sat, 4 Dec 2010 13:24:27 + Miguel Vaz pagong...@gmail.com 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

[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

[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 - 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.

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 the time the

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

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

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

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

[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

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

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

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 each

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 expression).

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 between

[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