Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-28 Thread Jim Nasby
On 4/28/15 5:41 AM, José Luis Tallón wrote: On 04/27/2015 08:49 AM, Jim Nasby wrote: On 4/25/15 1:19 PM, Bruce Momjian wrote: Note if you are storing a table with rows that exceed 2KB in size (aggregate size of each row) then the Maximum number of rows in a table may be limited to

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-28 Thread José Luis Tallón
On 04/27/2015 08:49 AM, Jim Nasby wrote: On 4/25/15 1:19 PM, Bruce Momjian wrote: Note if you are storing a table with rows that exceed 2KB in size (aggregate size of each row) then the Maximum number of rows in a table may be limited to 4 Billion, see TOAST. That's not accurate

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-27 Thread Jim Nasby
On 4/25/15 1:19 PM, Bruce Momjian wrote: Note if you are storing a table with rows that exceed 2KB in size (aggregate size of each row) then the Maximum number of rows in a table may be limited to 4 Billion, see TOAST. That's not accurate though; you could be limited to

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-27 Thread Roger Pack
On 4/27/15, Jim Nasby jim.na...@bluetreble.com wrote: On 4/25/15 1:19 PM, Bruce Momjian wrote: Note if you are storing a table with rows that exceed 2KB in size (aggregate size of each row) then the Maximum number of rows in a table may be limited to 4 Billion, see TOAST.

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-26 Thread Álvaro Hernández Tortosa
On 25/04/15 06:39, Jim Nasby wrote: On 4/24/15 7:11 PM, Álvaro Hernández Tortosa wrote: On 24/04/15 05:24, Tom Lane wrote: ... TBH, I've got very little enthusiasm for fixing this given the number of reports of trouble from the field, which so far as I recall is zero. Álvaro's case came up

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-26 Thread Stephen Frost
* Álvaro Hernández Tortosa (a...@8kdata.com) wrote: It's worth document but also, as I said, maybe also fixing them, so that if three years from now they really show up, solution is already in production (rather than in patching state). With the proliferation of JSON usage in PG thanks to

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-25 Thread Bruce Momjian
On Fri, Apr 24, 2015 at 11:39:04PM -0500, Jim Nasby wrote: On 4/24/15 7:11 PM, Álvaro Hernández Tortosa wrote: On 24/04/15 05:24, Tom Lane wrote: ... TBH, I've got very little enthusiasm for fixing this given the number of reports of trouble from the field, which so far as I recall is zero.

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-24 Thread Robert Haas
On Thu, Apr 23, 2015 at 11:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Having said all that, if we did try to fix it today, I'd imagine changing TOAST value identifiers to int64 and inventing a new TOAST pointer format for use when 32 bits isn't wide enough for the ID. But I think we're best

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-24 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Thu, Apr 23, 2015 at 11:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Having said all that, if we did try to fix it today, I'd imagine changing TOAST value identifiers to int64 and inventing a new TOAST pointer format for use when 32 bits isn't

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-24 Thread Álvaro Hernández Tortosa
On 24/04/15 05:24, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@momjian.us) wrote: On Sun, Feb 1, 2015 at 03:54:03PM +0100, Álvaro Hernández Tortosa wrote: The problem here is that performance degrades exponentially, or worse. Speaking here from

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-24 Thread Jim Nasby
On 4/24/15 7:11 PM, Álvaro Hernández Tortosa wrote: On 24/04/15 05:24, Tom Lane wrote: ... TBH, I've got very little enthusiasm for fixing this given the number of reports of trouble from the field, which so far as I recall is zero. Álvaro's case came up through intentionally trying to create

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-23 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote: On Sun, Feb 1, 2015 at 03:54:03PM +0100, Álvaro Hernández Tortosa wrote: The problem here is that performance degrades exponentially, or worse. Speaking here from experience, we already tested this for a very similar case (table creation, where

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-04-23 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@momjian.us) wrote: On Sun, Feb 1, 2015 at 03:54:03PM +0100, Álvaro Hernández Tortosa wrote: The problem here is that performance degrades exponentially, or worse. Speaking here from experience, we already tested this for a very

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-03 Thread José Luis Tallón
On 02/03/2015 03:44 AM, Jim Nasby wrote: [snip] The alternative could be some long LOB (HugeOBject?) using the equivalent to serial8 whereas regular LOBs would use serial4. Well, it depends on how we did this. We could (for example) add a field to pg_class that determines what type to use

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-03 Thread Jim Nasby
On 2/3/15 9:50 AM, David Steele wrote: On 2/3/15 10:01 AM, José Luis Tallón wrote: Hmmm alter column set storage external / set storage extended ? From http://www.postgresql.org/docs/9.4/static/sql-altertable.html : ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL |

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-03 Thread David Steele
On 2/3/15 10:01 AM, José Luis Tallón wrote: Hmmm alter column set storage external / set storage extended ? From http://www.postgresql.org/docs/9.4/static/sql-altertable.html : ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } This would do what

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-03 Thread David Steele
On 2/3/15 5:27 PM, Jim Nasby wrote: On 2/3/15 9:50 AM, David Steele wrote: EXTENDED is the default for most TOAST-able types and is still subject to TOAST_TUPLE_THRESHOLD which is normally 2K. EXTERNAL is the same but with no compression. See:

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Roger Pack
On 2/2/15, José Luis Tallón jltal...@adv-solutions.net wrote: On 01/31/2015 12:25 AM, Jim Nasby wrote: [snip] It's a bit more complex than that. First, toast isn't limited to bytea; it holds for ALL varlena fields in a table that are allowed to store externally. Second, the limit is actually

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread José Luis Tallón
On 02/02/2015 09:36 PM, Roger Pack wrote: On 2/2/15, José Luis Tallón jltal...@adv-solutions.net wrote: On 01/31/2015 12:25 AM, Jim Nasby wrote: [snip] It's a bit more complex than that. First, toast isn't limited to bytea; it holds for ALL varlena fields in a table that are allowed to store

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Tom Lane
Roger Pack rogerdpa...@gmail.com writes: On 2/2/15, José Luis Tallón jltal...@adv-solutions.net wrote: So yes, using one sequence per TOAST table should help. Combined with the new SequenceAMs / sequence implementation being proposed (specifically: one file for all sequences in a certain

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Jim Nasby
On 2/2/15 3:50 PM, José Luis Tallón wrote: Hmm 2^32 times aprox. 2kB (as per usual heuristics, ~4 rows per heap page) is 8796093022208 (~9e13) bytes ... which results in 8192 1GB segments :O Looks like partitioning might be needed much sooner than that (if only for index efficiency

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Roger Pack
On 1/30/15, Jim Nasby jim.na...@bluetreble.com wrote: On 1/30/15 11:54 AM, Roger Pack wrote: On 1/29/15, Roger Pack rogerdpa...@gmail.com wrote: Hello. I see on this page a mention of basically a 4B row limit for tables that have BLOB's Oops I meant for BYTEA or TEXT columns, but it's

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread José Luis Tallón
On 01/31/2015 12:25 AM, Jim Nasby wrote: [snip] It's a bit more complex than that. First, toast isn't limited to bytea; it holds for ALL varlena fields in a table that are allowed to store externally. Second, the limit is actually per-table: every table gets it's own toast table, and each

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-01 Thread Álvaro Hernández Tortosa
On 31/01/15 14:55, Roger Pack wrote: [...] Now, the reality is that GetNewOidWithIndex() is going to keep incrementing the global OID counter until it finds an OID that isn't in the toast table. That means that if you actually get anywhere close to using 4B OIDs you're going to become

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-01-31 Thread Roger Pack
Oops forgot to forward to the list (suggestion/feature request to the list admin for the various pg lists: make the default reply to go to the list, not the sender, if at all possible). Response below: On 1/30/15, Jim Nasby jim.na...@bluetreble.com wrote: On 1/30/15 11:54 AM, Roger Pack wrote:

[HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-01-30 Thread Roger Pack
On 1/29/15, Roger Pack rogerdpa...@gmail.com wrote: Hello. I see on this page a mention of basically a 4B row limit for tables that have BLOB's Oops I meant for BYTEA or TEXT columns, but it's possible the reasoning is the same... It only applies to large objects, not bytea or text. OK I

Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-01-30 Thread Jim Nasby
On 1/30/15 11:54 AM, Roger Pack wrote: On 1/29/15, Roger Pack rogerdpa...@gmail.com wrote: Hello. I see on this page a mention of basically a 4B row limit for tables that have BLOB's Oops I meant for BYTEA or TEXT columns, but it's possible the reasoning is the same... It only applies to