On 2/1/07, Steve Holdoway <[EMAIL PROTECTED]> wrote:
> Why would it be a "bottleneck"? If you are updating or deleting 300K a > week, definitely make sure you take a look at autovacuum and turn it ON. It is a potential bottleneck as I said. I am replacing between 5 and 10 percent of the data in the table every week. There are, in addition, a daily total of about a million hits ( read/update/delete ), as I said. This is the busiest table in the database. Therefore, it's the most likely candidate to cause performance problems. That's what I call a bottleneck.
The reason I am asking why you think it is a bottleneck is because many MySQL users see 'reading' a table as a bottleneck, and this is usually because they are using MyISAM tables. Under MyISAM tables readers block readers, writers block readers, readers block writers. This is NOT the case with PostgreSQL, readers never block other readers, writers never block readers, and readers never block writers. PostgreSQL does this using multi-versioning, similar to how Oracle works.
Depends on how you are querying the table. This is really a database > modeling question, and leads into many many more questions. I would say if > your frequently range scanning the table (selecting several rows) and in > those cases you rarely need the "blob", then I would fork it off into a > child table. If the "blob" is rarely accessed, and only accessed directly, > then definitely a child table in my book. > > The reason is if your frequently fetching rows from this table and rarely > using the blob all you are doing is consuming memory that could be better > used for other things, and spinning I/O when it is not necessary. Sorry, you're completley wrong. If you consider that the only way of getting info is select *, then this is true. Personally, I think that anyone who does this in code is plain lazy and should find a job more suited to them (:
Okay, I would love to see your test case. My statement is based on the suggestion that you were embedding your data in the same row as a bytea column. Here is a test case that I ran: NOTE: Fill factor is set to 10% to exaggerate a wide row, this also proves the point that EMPTY space in a table affects performance. drop table foo; create table foo (x integer not null primary key, y text not null) with (fillfactor = 10); insert into foo (x, y) select generate_series(1,100000), repeat('1234567890', 10240); checkpoint; analyze foo; select relpages,reltuples,pg_size_pretty(pg_relation_size(relname)) from pg_class where relname = 'foo'; relpages | reltuples | pg_size_pretty ----------+-----------+---------------- 100000 | 100000 | 781 MB (1 row) -- fetch just the integer column explain analyze select x from foo; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..101000.00 rows=100000 width=4) (actual time= 10.389..19288.848 rows=100000 loops=1) Total runtime: 19700.804 ms (2 rows) -- fetch just the text column explain analyze select y from foo; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..101000.00 rows=100000 width=32) (actual time= 9.234..19863.485 rows=100000 loops=1) Total runtime: 20290.618 ms (2 rows) -- fetch both the integer and text column explain analyze select * from foo; ------------------------------------------------------------------------------------------------------------------ Seq Scan on foo (cost=0.00..101000.00 rows=100000 width=36) (actual time= 40.478..20470.648 rows=100000 loops=1) Total runtime: 20904.611 ms (2 rows) -- Runtime is the same for all three cases against the "wide" table. Disproves the theory that selecting or not selecting a "column" has something to do with performance. I would think logically it does when you are talking about sorting and merging data sets, not when we are talking about physical reads. -- Now let's seperate the "large" text column from the rest of the data. drop table bar; create table bar (x integer not null primary key); insert into bar (x) select generate_series(1,100000); checkpoint; analyze bar; select relpages,reltuples,pg_size_pretty(pg_relation_size(relname)) from pg_class where relname = 'bar'; relpages | reltuples | pg_size_pretty ----------+-----------+---------------- 441 | 100000 | 3528 kB (1 row) -- fetch from the "skinny" table explain analyze select * from bar; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Seq Scan on bar (cost=0.00..1441.00 rows=100000 width=4) (actual time= 19.552..416.575 rows=100000 loops=1) Total runtime: 790.901 ms (2 rows) -- Notice how must faster it is, seems to suggest that a table with a narrow column with is faster. I think this case proves the point that a "wide" blob if not accessed frequently shouldn't be included in the core table. Naturally this makes no sense if every single time you are going to fetch the blob and use it with every single fetch against the table. I am asking for input from those who have been in this situation before, and
have experience in the tradeoff of running a separate table for the big stuff as against the extra ( 8 byte? ) column that would be added to the master table. Why am I asking this? Because Postgres has an unique way of handling this kind of data, unique from even the last time I used postgres in anger - 7.4. It's different from every other rdbms ( and MySQL ), and I have no practical experience of it in the wild.
I think you are referring to large objects, which are completely different then your original statement. Your original statement seemed to ask about including binary data in the table versus not in the table. My understanding of large objects is they create a file on the filesystem (probably an oid like a table does) and you store the oid of the "file" in a table. Basically you have bytea (byte arrays) which are stored inline with the rest of the row and large objects where store a reference to another object.
It really has to do with how you design your schema. No. It really has to do with the gathering of relevant information so that I can design my schema properly. Academically, 3NF may be the solution to every design, but in the real world, there are many other things to take in to account. I've monitored the data flows, sized the hardware to handle the IO, and can either spend a week or two benchmarking different solutions from cold, or I can take the advice of those with relevant experience ( who I expect to find on this list ) to point me in the right direction first.
That is my point here, the schema should be designed properly. A poor schema design and yield awful I/O performance, which is essentially my point in my previous message. Anyways, if you are typically this sarcastic then I would suggest you learn how to either better phrase your responses or not respond at all. Frankly, no one has to help you, and making statements as you have above are rude to everyone on this list. I have plenty of experience building large distributed OLTP systems and multi terabyte data warehouses, so you can either take the advice or leave it.