Re: [PERFORM] Thousands of tables versus on table?
[EMAIL PROTECTED] wrote: On Wed, 6 Jun 2007, Steinar H. Gunderson wrote: On Tue, Jun 05, 2007 at 05:59:25PM -0400, Tom Lane wrote: I think the main argument for partitioning is when you are interested in being able to drop whole partitions cheaply. Wasn't there also talk about adding the ability to mark individual partitions as read-only, thus bypassing MVCC and allowing queries to be satisfied using indexes only? Not that I think I've seen it on the TODO... :-) now that's a very interesting idea, especially when combined with time-based data where the old times will never change. That's been discussed, but it's controversial. IMHO a better way to achieve that is to design the dead-space-map so that it can be used to check which parts of a table are visible to everyone, and skip visibility checks. That doesn't require any user action, and allows updates. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
[EMAIL PROTECTED] wrote: On Tue, 5 Jun 2007, Tom Lane wrote: [EMAIL PROTECTED] writes: however I really don't understand why it is more efficiant to have a 5B line table that you do a report/query against 0.1% of then it is to have 1000 different tables of 5M lines each and do a report/query against 100% of. Essentially what you are doing when you do that is taking the top few levels of the index out of the database and putting it into the filesystem; plus creating duplicative indexing information in the database's system catalogs. The degree to which this is a win is *highly* debatable, and certainly depends on a whole lot of assumptions about filesystem performance. You also need to assume that constraint-exclusion in the planner is pretty doggone cheap relative to the table searches, which means it almost certainly will lose badly if you carry the subdivision out to the extent that the individual tables become small. (This last could ^^ what is considered 'small'? a few thousand records, a few million records? I would say small is when the individual tables are in the 10 to 20 Megabyte range. How many records that is depends on record width, of course. Basically, once the tables get small enough that you don't really need indexes much, since you tend to grab 25% or more of each one that you're going to hit in a query. what multiplication factor would there need to be on the partitioning to make it worth while? 100 tables, 1000 tables, 1 tables? Really depends on the size of the master table I think. If the master table is about 500 Megs in size, and you partition it down to about 1 meg per child table, you're probably ok. Walking through 500 entries for constraint exclusion seems pretty speedy from the tests I've run on a 12M row table that was about 250 Megs, split into 200 to 400 or so equisized child tables. The time to retrieve 85,000 rows that were all neighbors went from 2 to 6 seconds, to about 0.2 seconds, and we got rid of indexes entirely since they weren't really needed anymore. the company that I'm at started out with a seperate database per customer (not useing postgres), there are basicly zero cross-customer queries, with a large volume of updates and lookups. overall things have now grown to millions of updates/day (some multiple of this in lookups), and ~2000 customers, with tens of millions of rows between them. having each one as a seperate database has really helped us over the years as it's made it easy to scale (run 500 databases on each server instead of 1000, performance just doubled) I think that for what you're doing, partitioning at the database level is probably a pretty good compromise solution. Like you say, it's easy to put busy databases on a new server to balance out the load. Hardware is cheap. various people (not database experts) are pushing to install Oracle cluster so that they can move all of these to one table with a customerID column. Have these people identified a particular problem they're trying to solve, or is this a religious issue for them? From your description it sounds like a matter of dogma, not problem solving. the database folks won't comment much on this either way, but they don't seem enthusiastic to combine all the data togeather. I think they can see the fecal matter heading towards the rotational cooling device on this one. I can't imagine this being a win from the perspective of saving the company money. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Thousands of tables versus on table?
Tom Lane wrote: The degree to which this is a win is *highly* debatable, and certainly depends on a whole lot of assumptions about filesystem performance. You also need to assume that constraint-exclusion in the planner is pretty doggone cheap relative to the table searches, which means it almost certainly will lose badly if you carry the subdivision out to the extent that the individual tables become small. (This last could be improved in some cases if we had a more explicit representation of partitioning, but it'll never be as cheap as one more level of index search.) I did some testing a while back on some of this, and with 400 or so partitions, the select time was still very fast. We were testing grabbing 50-80k rows from 12M at a time, all adjacent to each other. With the one big table and one big two way index method, we were getting linearly increasing select times as the dataset grew larger and larger. The indexes were much larger than available memory and shared buffers. The retrieval time for 50-80k rows was on the order of 2 to 6 seconds, while the retrieval time for the same number of rows with 400 partitions was about 0.2 to 0.5 seconds. I haven't tested with more partitions than that, but might if I get a chance. What was really slow was the inserts since I was using rules at the time. I'd like to try re-writing it to use triggers, since I would then have one trigger on the parent table instead of 400 rules. Or I could imbed the rules into the app that was creating / inserting the data. The insert performance dropped off VERY fast as I went over 100 rules, and that was what primarily stopped me from testing larger numbers of partitions. The select performance stayed very fast with more partitions, so I'm guessing that the constraint exclusion is pretty well optimized. I'll play with it some more when I get a chance. For certain operations like the one we were testing, partitioning seems to pay off big time. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Thousands of tables versus on table?
[EMAIL PROTECTED] wrote: various people (not database experts) are pushing to install Oracle cluster so that they can move all of these to one table with a customerID column. They're blowing smoke if they think Oracle can do this. One of my applications had this exact same problem -- table-per-customer versus big-table-for-everyone. Oracle fell over dead, even with the best indexing possible, tuned by the experts, and using partitions keyed to the customerID. We ended up breaking it up into table-per-customer because Oracle fell over dead when we had to do a big update on a customer's entire dataset. All other operations were slowed by the additional index on the customer-ID, especially complex joins. With a table-for-everyone, you're forced to create tricky partitioning or clustering, clever indexes, and even with that, big updates are problematic. And once you do this, then you become heavily tied to one RDBMS and your applications are no longer portable, because clustering, indexing, partitioning and other DB tuning tricks are very specific to each RDBMS. When we moved to Postgres, we never revisited this issue, because both Oracle and Postgres are able to handle thousands of tables well. As I wrote in a previous message on a different topic, often the design of your application is more important than the performance. In our case, the table-per-customer makes the applications simpler, and security is MUCH easier. Oracle is simply not better than Postgres in this regard. As far as I know, there is only one specific situation (discussed frequently here) where Oracle is faster: the count(), min() and max() functions, and I know significant progress has been made since I started using Postgres. I have not found any other query where Oracle is significantly better, and I've found several where Postgres is the clear winner. It's telling that Oracle's license contract prohibits you from publishing comparisons and benchmarks. You have to wonder why. Craig ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
Craig James wrote: Oracle is simply not better than Postgres in this regard. As far as I know, there is only one specific situation (discussed frequently here) where Oracle is faster: the count(), min() and max() functions, and I know significant progress has been made since I started using Postgres. I have not found any other query where Oracle is significantly better, and I've found several where Postgres is the clear winner. In my testing between a commercial database that cannot be named and postgresql, I found max() / min() to be basically the same, even with where clauses and joins happening. count(*), OTOH, is a still a clear winner for the big commercial database. With smaller sets (1 Million or so) both dbs are in the same ballpark. With 30+million rows, count(*) took 2 minutes on pgsql and 4 seconds on the big database. OTOH, there are some things, like importing data, which are MUCH faster in pgsql than in the big database. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Thousands of tables versus on table?
Scott Marlowe wrote: OTOH, there are some things, like importing data, which are MUCH faster in pgsql than in the big database. An excellent point, I forgot about this. The COPY command is the best thing since the invention of a shirt pocket. We have a database-per-customer design, and one of the mosterous advantages of Postgres is that we can easily do backups. A pg_dump, then scp to a backup server, and in just a minute or two we have a full backup. For recovery, pg_restore is equally fast and amazing. Last time I checked, Oracle didn't have anything close to this. Craig ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Thousands of tables versus on table?
On 6/6/07, Craig James [EMAIL PROTECTED] wrote: They're blowing smoke if they think Oracle can do this. Oracle could handle this fine. Oracle fell over dead, even with the best indexing possible, tuned by the experts, and using partitions keyed to the customerID. I don't think so, whoever tuned this likely didn't know what they were doing. It's telling that Oracle's license contract prohibits you from publishing comparisons and benchmarks. You have to wonder why. They did this for the same reason as everyone else. They don't want non-experts tuning the database incorrectly, writing a benchmark paper about it, and making the software look bad. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Thousands of tables versus on table?
On 6/6/07, Craig James [EMAIL PROTECTED] wrote: Last time I checked, Oracle didn't have anything close to this. When did you check, 15 years ago? Oracle has direct-path import/export and data pump; both of which make generic COPY look like a turtle. The new PostgreSQL bulk-loader takes similar concepts from Oracle and is fairly faster than COPY. Don't get me wrong, I'm pro-PostgreSQL... but spouting personal observations on other databases as facts just boasts an PostgreSQL-centric egotistical view of the world. If you don't tune Oracle, it will suck. If you don't understand Oracle architecture when you tune an application, it will suck; just like PostgreSQL. People who don't have extensive experience in the other databases just hear what you say and regurgitate it as fact; which it is not. Look at how many people in these lists still go on and on about MySQL flaws based on their experience with MySQL 3.23. Times change and it doesn't do anyone any good to be ignorant of other databases. If you're going to speak about another database in a comparison, please stay current or specify the database you're comparing against. This is nothing against you, but it always starts an avalanche of, look how perfect we are compared to everyone else. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Thousands of tables versus on table?
On Wed, Jun 06, 2007 at 12:06:09AM +0200, Steinar H. Gunderson wrote: Wasn't there also talk about adding the ability to mark individual partitions as read-only, thus bypassing MVCC and allowing queries to be satisfied using indexes only? I have a (different) problem that read-only data segments (maybe partitions, maybe something else) would help, so I know for sure that someone is working on a problem like this, but I don't think it's the sort of thing that's going to come any time soon. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Thousands of tables versus on table?
On Tue, Jun 05, 2007 at 03:31:55PM -0700, [EMAIL PROTECTED] wrote: various people (not database experts) are pushing to install Oracle cluster so that they can move all of these to one table with a customerID column. Well, you will always have to deal with the sort of people who will base their technical prescriptions on the shiny ads they read in SuperGlobalNetworkedExecutiveGoFast, or whatever rag they're reading these days. I usually encourage such people actually to perform the analysis of the license, salary, contingency, and migrations costs (and do a similar analysis myself, actually, so when they have overlooked the 30 things that individually cost $1million a piece, I can point them out). More than one jaw has had to be picked up off the floor when presented with the bill for RAC. Frequently, people discover that it is a good way to turn your tidy money-making enterprise into a giant money hole that produces a sucking sound on the other end of which is Oracle Corporation. All of that aside, I have pretty severe doubts that RAC would be a win for you. A big honkin' single database in Postgres ought to be able to do this too, if you throw enough hardware money at it. But it seems a waste to re-implement something that's already apparently working for you in favour of something more expensive that you don't seem to need. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
On 6/6/07, Andrew Sullivan [EMAIL PROTECTED] wrote: Well, you will always have to deal with the sort of people who will base their technical prescriptions on the shiny ads they read in SuperGlobalNetworkedExecutiveGoFast, or whatever rag they're reading these days. Always. I usually encourage such people actually to perform the analysis of the license, salary, contingency, and migrations costs Yes, this is the best way. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Thousands of tables versus on table?
Jonah H. Harris wrote: On 6/6/07, Craig James [EMAIL PROTECTED] wrote: They're blowing smoke if they think Oracle can do this. Oracle could handle this fine. Oracle fell over dead, even with the best indexing possible, tuned by the experts, and using partitions keyed to the customerID. I don't think so, whoever tuned this likely didn't know what they were doing. Wrong on both counts. You didn't read my message. I said that *BOTH* Oracle and Postgres performed well with table-per-customer. I wasn't Oracle bashing. In fact, I was doing the opposite: Someone's coworker claimed ORACLE was the miracle cure for all problems, and I was simply pointing out that there are no miracle cures. (I prefer Postgres for many reasons, but Oracle is a fine RDBMS that I have used extensively.) The technical question is simple: Table-per-customer or big-table-for-everyone. The answer is, it depends. It depends on your application, your read-versus-write ratio, the table size, the design of your application software, and a dozen other factors. There is no simple answer, but there are important technical insights which, I'm happy to report, various people contributed to this discussion. Perhaps you have some technical insight too, because it really is an important question. The reason I assert (and stand by this) that They're blowing smoke when they claim Oracle has the magic cure, is because Oracle and Postgres are both relational databases, they write their data to disks, and they both have indexes with O(log(N)) retrieval/update times. Oracle doesn't have a magical workaround to these facts, nor does Postgres. Craig ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Thousands of tables versus on table?
On 6/6/07, Craig James [EMAIL PROTECTED] wrote: You didn't read my message. I said that *BOTH* Oracle and Postgres performed well with table-per-customer. Yes, I did. My belief is that Oracle can handle all customers in a single table. The technical question is simple: Table-per-customer or big-table-for-everyone. The answer is, it depends. I agree, it does depend on the data, workload, etc. No one-size-fits-all answer there. The reason I assert (and stand by this) that They're blowing smoke when they claim Oracle has the magic cure, is because Oracle and Postgres are both relational databases, they write their data to disks, and they both have indexes with O(log(N)) retrieval/update times. Oracle doesn't have a magical workaround to these facts, nor does Postgres. Agreed that they are similar on the basics, but they do use significantly different algorithms and optimizations. Likewise, there is more tuning that can be done with Oracle given the amount of time and money one has to spend on it. Again, cost/benefit analysis on this type of an issue... but you're right, there is no magic cure. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Thousands of tables versus on table?
[EMAIL PROTECTED] wrote: On Mon, 4 Jun 2007, Scott Marlowe wrote: Gregory Stark wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. That's not entirely true. PostgreSQL can be markedly faster using partitioning as long as you always access it by referencing the partitioning key in the where clause. So, if you partition the table by date, and always reference it with a date in the where clause, it will usually be noticeably faster. OTOH, if you access it without using a where clause that lets it pick partitions, then it will be slower than one big table. So, while this poster might originally think to have one table for each user, resulting in thousands of tables, maybe a compromise where you partition on userid ranges would work out well, and keep each partition table down to some 50-100 thousand rows, with smaller indexes to match. what if he doesn't use the postgres internal partitioning, but instead makes his code access the tables named responsesN where N is the id of the customer? this is what it sounded like he was asking initially. Sorry, I think I initially read your response as Postgres doesn't really get any faster by breaking the tables up without the like that part. I've found that as long as the number of tables is 10,000 or so, having a lot of tables doesn't seem to really slow pgsql down a lot. I'm sure that the tipping point is dependent on your db machine. I would bet that if he's referring to individual tables directly, and each one has hundreds instead of millions of rows, the performance would be better. But the only way to be sure is to test it. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Thousands of tables versus on table?
So, partitioning in PSQL 8 is workable, but breaking up the table up into actual separate tables is not? Another solution we have proposed is having 'active' and 'completed' tables. So, rather than thousands, we'd have four tables: responders_active responders_completed responses_active responses_completed That way, the number of responses_active records would not be as huge. The problem, as we see it, is that the responders are entering their responses and it is taking too long. But if we separate out active and completed surveys, then the inserts will likely cost less. We might even be able to reduce the indices on the _active tables because survey administrators would not want to run as many complex reports on the active responses. There would be an extra cost, when the survey is completed, of copying the records from the '_active' table to the '_completed' table and then deleting them, but that operation is something a survey administrator would be willing to accept as taking a while (as well as something we could put off to an off hour, although we have lots of international customers so it's not clear when our off hours are.) =thomas On 6/5/07 12:48 PM, Scott Marlowe [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: On Mon, 4 Jun 2007, Scott Marlowe wrote: Gregory Stark wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. That's not entirely true. PostgreSQL can be markedly faster using partitioning as long as you always access it by referencing the partitioning key in the where clause. So, if you partition the table by date, and always reference it with a date in the where clause, it will usually be noticeably faster. OTOH, if you access it without using a where clause that lets it pick partitions, then it will be slower than one big table. So, while this poster might originally think to have one table for each user, resulting in thousands of tables, maybe a compromise where you partition on userid ranges would work out well, and keep each partition table down to some 50-100 thousand rows, with smaller indexes to match. what if he doesn't use the postgres internal partitioning, but instead makes his code access the tables named responsesN where N is the id of the customer? this is what it sounded like he was asking initially. Sorry, I think I initially read your response as Postgres doesn't really get any faster by breaking the tables up without the like that part. I've found that as long as the number of tables is 10,000 or so, having a lot of tables doesn't seem to really slow pgsql down a lot. I'm sure that the tipping point is dependent on your db machine. I would bet that if he's referring to individual tables directly, and each one has hundreds instead of millions of rows, the performance would be better. But the only way to be sure is to test it. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
Thomas Andrews wrote: On 6/5/07 12:48 PM, Scott Marlowe [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: On Mon, 4 Jun 2007, Scott Marlowe wrote: Gregory Stark wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. That's not entirely true. PostgreSQL can be markedly faster using partitioning as long as you always access it by referencing the partitioning key in the where clause. So, if you partition the table by date, and always reference it with a date in the where clause, it will usually be noticeably faster. OTOH, if you access it without using a where clause that lets it pick partitions, then it will be slower than one big table. So, while this poster might originally think to have one table for each user, resulting in thousands of tables, maybe a compromise where you partition on userid ranges would work out well, and keep each partition table down to some 50-100 thousand rows, with smaller indexes to match. what if he doesn't use the postgres internal partitioning, but instead makes his code access the tables named responsesN where N is the id of the customer? this is what it sounded like he was asking initially. Sorry, I think I initially read your response as Postgres doesn't really get any faster by breaking the tables up without the like that part. I've found that as long as the number of tables is 10,000 or so, That should have been as long as the number of tables is 10,000 or so... having a lot of tables doesn't seem to really slow pgsql down a lot. I'm sure that the tipping point is dependent on your db machine. I would bet that if he's referring to individual tables directly, and each one has hundreds instead of millions of rows, the performance would be better. But the only way to be sure is to test it. Please stop top posting. This is my last reply until you stop top posting. So, partitioning in PSQL 8 is workable, but breaking up the table up into actual separate tables is not? Ummm, that's not what I said. They're similar in execution. However, partitioning might let you put 100 customers into a given table, if, say, you partitioned on customer ID or something that would allow you to group a few together. Another solution we have proposed is having 'active' and 'completed' tables. So, rather than thousands, we'd have four tables: responders_active responders_completed responses_active responses_completed That's not a bad idea. Just keep up on your vacuuming. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
Scott Marlowe [EMAIL PROTECTED] writes: Sorry, I think I initially read your response as Postgres doesn't really get any faster by breaking the tables up without the like that part. Well breaking up the tables like that or partitioning, either way should be about equivalent really. Breaking up the tables and doing it in the application should perform even better but it does make the schema less flexible and harder to do non-partition based queries and so on. I guess I should explain what I originally meant: A lot of people come from a flat-file world and assume that things get slower when you deal with large tables. In fact due to the magic of log(n) accessing records from a large index is faster than first looking up the table and index info in a small index and then doing a second lookup in up in an index for a table half the size. Where the win in partitioning comes in is in being able to disappear some of the data entirely. By making part of the index key implicit in the choice of partition you get away with a key that's half as large. And in some cases you can get away with using a different key entirely which wouldn't otherwise have been feasible to index. In some cases you can even do sequential scans whereas in an unpartitioned table you would have to use an index (or scan the entire table). But the real reason people partition data is really for the management ease. Being able to drop, and load entire partitions in O(1) is makes it feasible to manage data on a scale that would simply be impossible without partitioned tables. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Thousands of tables versus on table?
Gregory Stark wrote: Scott Marlowe [EMAIL PROTECTED] writes: Sorry, I think I initially read your response as Postgres doesn't really get any faster by breaking the tables up without the like that part. Well breaking up the tables like that or partitioning, either way should be about equivalent really. Breaking up the tables and doing it in the application should perform even better but it does make the schema less flexible and harder to do non-partition based queries and so on. True, but we can break it up by something other than the company name on the survey, in this instance, and might find it far easier to manage by, say, date range, company ID range, etc... Plus with a few hand rolled bash or perl scripts we can maintain our database and keep all the logic of partitioning out of our app. Which would allow developers not wholly conversant in our partitioning scheme to participate in development without the fear of them putting data in the wrong place. Where the win in partitioning comes in is in being able to disappear some of the data entirely. By making part of the index key implicit in the choice of partition you get away with a key that's half as large. And in some cases you can get away with using a different key entirely which wouldn't otherwise have been feasible to index. In some cases you can even do sequential scans whereas in an unpartitioned table you would have to use an index (or scan the entire table). Yeah, I found that out recently while I benchmarking a 12,000,000 row geometric data set. Breaking it into 400 or so partitions resulted in no need for indexes and response times of 0.2 or so seconds, where before that I'd been in the 1.5 to 3 second range. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Thousands of tables versus on table?
On Tue, 5 Jun 2007, Gregory Stark wrote: Scott Marlowe [EMAIL PROTECTED] writes: Sorry, I think I initially read your response as Postgres doesn't really get any faster by breaking the tables up without the like that part. Well breaking up the tables like that or partitioning, either way should be about equivalent really. Breaking up the tables and doing it in the application should perform even better but it does make the schema less flexible and harder to do non-partition based queries and so on. but he said in the initial message that they don't do cross-customer reports anyway, so there really isn't any non-partition based querying going on anyway. I guess I should explain what I originally meant: A lot of people come from a flat-file world and assume that things get slower when you deal with large tables. In fact due to the magic of log(n) accessing records from a large index is faster than first looking up the table and index info in a small index and then doing a second lookup in up in an index for a table half the size. however, if your query plan every does a sequential scan of a table then you are nog doing a log(n) lookup are you? Where the win in partitioning comes in is in being able to disappear some of the data entirely. By making part of the index key implicit in the choice of partition you get away with a key that's half as large. And in some cases you can get away with using a different key entirely which wouldn't otherwise have been feasible to index. In some cases you can even do sequential scans whereas in an unpartitioned table you would have to use an index (or scan the entire table). But the real reason people partition data is really for the management ease. Being able to drop, and load entire partitions in O(1) is makes it feasible to manage data on a scale that would simply be impossible without partitioned tables. remember that the origional question wasn't about partitioned tables, it was about the performance problem he was having with one large table (slow insert speed) and asking if postgres would collapse if he changed his schema to use a seperate table per customer. I see many cases where people advocate collapsing databases/tables togeather by adding a column that indicates which customer the line is for. however I really don't understand why it is more efficiant to have a 5B line table that you do a report/query against 0.1% of then it is to have 1000 different tables of 5M lines each and do a report/query against 100% of. it would seem that the fact that you don't have to skip over 99.9% of the data to find things that _may_ be relavent would have a noticable cost in and of itself. David Lang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Thousands of tables versus on table?
On Tue, Jun 05, 2007 at 05:59:25PM -0400, Tom Lane wrote: I think the main argument for partitioning is when you are interested in being able to drop whole partitions cheaply. Wasn't there also talk about adding the ability to mark individual partitions as read-only, thus bypassing MVCC and allowing queries to be satisfied using indexes only? Not that I think I've seen it on the TODO... :-) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Thousands of tables versus on table?
[EMAIL PROTECTED] writes: however I really don't understand why it is more efficiant to have a 5B line table that you do a report/query against 0.1% of then it is to have 1000 different tables of 5M lines each and do a report/query against 100% of. Essentially what you are doing when you do that is taking the top few levels of the index out of the database and putting it into the filesystem; plus creating duplicative indexing information in the database's system catalogs. The degree to which this is a win is *highly* debatable, and certainly depends on a whole lot of assumptions about filesystem performance. You also need to assume that constraint-exclusion in the planner is pretty doggone cheap relative to the table searches, which means it almost certainly will lose badly if you carry the subdivision out to the extent that the individual tables become small. (This last could be improved in some cases if we had a more explicit representation of partitioning, but it'll never be as cheap as one more level of index search.) I think the main argument for partitioning is when you are interested in being able to drop whole partitions cheaply. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
On Tue, 5 Jun 2007, Tom Lane wrote: [EMAIL PROTECTED] writes: however I really don't understand why it is more efficiant to have a 5B line table that you do a report/query against 0.1% of then it is to have 1000 different tables of 5M lines each and do a report/query against 100% of. Essentially what you are doing when you do that is taking the top few levels of the index out of the database and putting it into the filesystem; plus creating duplicative indexing information in the database's system catalogs. The degree to which this is a win is *highly* debatable, and certainly depends on a whole lot of assumptions about filesystem performance. You also need to assume that constraint-exclusion in the planner is pretty doggone cheap relative to the table searches, which means it almost certainly will lose badly if you carry the subdivision out to the extent that the individual tables become small. (This last could ^^ what is considered 'small'? a few thousand records, a few million records? what multiplication factor would there need to be on the partitioning to make it worth while? 100 tables, 1000 tables, 1 tables? the company that I'm at started out with a seperate database per customer (not useing postgres), there are basicly zero cross-customer queries, with a large volume of updates and lookups. overall things have now grown to millions of updates/day (some multiple of this in lookups), and ~2000 customers, with tens of millions of rows between them. having each one as a seperate database has really helped us over the years as it's made it easy to scale (run 500 databases on each server instead of 1000, performance just doubled) various people (not database experts) are pushing to install Oracle cluster so that they can move all of these to one table with a customerID column. the database folks won't comment much on this either way, but they don't seem enthusiastic to combine all the data togeather. I've been on the side of things that said that seperate databases is better becouse it improves data locality to only have to look at the data for one customer at a time rather then having to pick out that customer's data out from the mass of other, unrelated data. be improved in some cases if we had a more explicit representation of partitioning, but it'll never be as cheap as one more level of index search.) say you have a billing table of customerID, date, description, amount, tax, extended, paid and you need to do things like report on invoices that haven't been paied summarize the amount billed each month summarize the tax for each month but you need to do this seperately for each customerID (not as a batch job that reports on all customerID's at once, think a website where the customer can request such reports at any time with a large variation in criteria) would you be able to just have one index on customerID and then another on date? or would the second one need to be on customerID||date? and would this process of going throught he index and seeking to the data it points to really be faster then a sequential scan of just the data related to that customerID? I think the main argument for partitioning is when you are interested in being able to drop whole partitions cheaply. I fully understand this if you are doing queries across all the partitions, but if your query is confined to a single partition, especially in the case where you know ahead of time in the application which 'partition' you care about it would seem that searching through significantly less data should be a win. David Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Thousands of tables versus on table?
On Wed, 6 Jun 2007, Steinar H. Gunderson wrote: On Tue, Jun 05, 2007 at 05:59:25PM -0400, Tom Lane wrote: I think the main argument for partitioning is when you are interested in being able to drop whole partitions cheaply. Wasn't there also talk about adding the ability to mark individual partitions as read-only, thus bypassing MVCC and allowing queries to be satisfied using indexes only? Not that I think I've seen it on the TODO... :-) now that's a very interesting idea, especially when combined with time-based data where the old times will never change. David Lang ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Thousands of tables versus on table?
I have several thousand clients. Our clients do surveys, and each survey has two tables for the client data, responders responses Frequent inserts into both table. Right now, we are seeing significant time during inserts to these two tables. Some of the indices in tableA and tableB do not index on the client ID first. So, we are considering two possible solutions. (1) Create separate responders and responses tables for each client. (2) Make sure all indices on responders and responses start with the client id (excepting, possibly, the primary keys for these fields) and have all normal operation queries always include an id_client. Right now, for example, given a responder and a survey question, we do a query in responses by the id_responder and id_survey. This gives us a unique record, but I'm wondering if maintaining the index on (id_responder,id_survey) is more costly on inserts than maintaining the index (id_client,id_responder,id_survey) given that we also have other indices on (id_client,...). Option (1) makes me very nervous. I don't like the idea of the same sorts of data being stored in lots of different tables, in part for long-term maintenance reasons. We don't really need cross-client reporting, however. =thomas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Thousands of tables versus on table?
On Mon, 2007-06-04 at 13:40 -0400, Thomas Andrews wrote: I have several thousand clients. Our clients do surveys, and each survey has two tables for the client data, responders responses Frequent inserts into both table. Right now, we are seeing significant time during inserts to these two tables. Can you provide some concrete numbers here? Perhaps an EXPLAIN ANALYZE for the insert, sizes of tables, stuff like that? Some of the indices in tableA and tableB do not index on the client ID first. What reason do you have to think that this matters? So, we are considering two possible solutions. (1) Create separate responders and responses tables for each client. (2) Make sure all indices on responders and responses start with the client id (excepting, possibly, the primary keys for these fields) and have all normal operation queries always include an id_client. Right now, for example, given a responder and a survey question, we do a query in responses by the id_responder and id_survey. This gives us a unique record, but I'm wondering if maintaining the index on (id_responder,id_survey) is more costly on inserts than maintaining the index (id_client,id_responder,id_survey) given that we also have other indices on (id_client,...). Option (1) makes me very nervous. I don't like the idea of the same sorts of data being stored in lots of different tables, in part for long-term maintenance reasons. We don't really need cross-client reporting, however. What version of PG is this? What is your vacuuming strategy? Have you tried a REINDEX to see if that helps? -- Mark Lewis ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Thousands of tables versus on table?
Oh, and we vacuum every day. Not sure about REINDEX, but I doubt we have done that. =thomas Mark Lewis wrote: On Mon, 2007-06-04 at 13:40 -0400, Thomas Andrews wrote: I have several thousand clients. Our clients do surveys, and each survey has two tables for the client data, responders responses Frequent inserts into both table. Right now, we are seeing significant time during inserts to these two tables. Can you provide some concrete numbers here? Perhaps an EXPLAIN ANALYZE for the insert, sizes of tables, stuff like that? Some of the indices in tableA and tableB do not index on the client ID first. What reason do you have to think that this matters? So, we are considering two possible solutions. (1) Create separate responders and responses tables for each client. (2) Make sure all indices on responders and responses start with the client id (excepting, possibly, the primary keys for these fields) and have all normal operation queries always include an id_client. Right now, for example, given a responder and a survey question, we do a query in responses by the id_responder and id_survey. This gives us a unique record, but I'm wondering if maintaining the index on (id_responder,id_survey) is more costly on inserts than maintaining the index (id_client,id_responder,id_survey) given that we also have other indices on (id_client,...). Option (1) makes me very nervous. I don't like the idea of the same sorts of data being stored in lots of different tables, in part for long-term maintenance reasons. We don't really need cross-client reporting, however. What version of PG is this? What is your vacuuming strategy? Have you tried a REINDEX to see if that helps? -- Mark Lewis begin:vcard fn:Thomas Andrews n:Andrews;Thomas org:Soliant Consulting adr;dom:;; 14 N. Peoria Street 2H;Chicago;IL;60607 email;internet:[EMAIL PROTECTED] title:Application Engineer tel;work:617-868-5580 tel;home:617-868-5580 tel;cell:617-599-8728 x-mozilla-html:FALSE url:http://www.soliantconsulting.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Thousands of tables versus on table?
We're running 7.4 but will be upgrading to 8.2. The responses table has 20,000,000 records. Sometimes (but not all the time) an insert into the responses table can take 5-6 seconds. I guess my real question is, does it ever make sense to create thousands of tables like this? =thomas Mark Lewis wrote: On Mon, 2007-06-04 at 13:40 -0400, Thomas Andrews wrote: I have several thousand clients. Our clients do surveys, and each survey has two tables for the client data, responders responses Frequent inserts into both table. Right now, we are seeing significant time during inserts to these two tables. Can you provide some concrete numbers here? Perhaps an EXPLAIN ANALYZE for the insert, sizes of tables, stuff like that? Some of the indices in tableA and tableB do not index on the client ID first. What reason do you have to think that this matters? So, we are considering two possible solutions. (1) Create separate responders and responses tables for each client. (2) Make sure all indices on responders and responses start with the client id (excepting, possibly, the primary keys for these fields) and have all normal operation queries always include an id_client. Right now, for example, given a responder and a survey question, we do a query in responses by the id_responder and id_survey. This gives us a unique record, but I'm wondering if maintaining the index on (id_responder,id_survey) is more costly on inserts than maintaining the index (id_client,id_responder,id_survey) given that we also have other indices on (id_client,...). Option (1) makes me very nervous. I don't like the idea of the same sorts of data being stored in lots of different tables, in part for long-term maintenance reasons. We don't really need cross-client reporting, however. What version of PG is this? What is your vacuuming strategy? Have you tried a REINDEX to see if that helps? -- Mark Lewis begin:vcard fn:Thomas Andrews n:Andrews;Thomas org:Soliant Consulting adr;dom:;; 14 N. Peoria Street 2H;Chicago;IL;60607 email;internet:[EMAIL PROTECTED] title:Application Engineer tel;work:617-868-5580 tel;home:617-868-5580 tel;cell:617-599-8728 x-mozilla-html:FALSE url:http://www.soliantconsulting.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Thousands of tables versus on table?
Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. How often do you update or delete records and how many do you update or delete? Once per day is a very low frequency for vacuuming a busy table, you may be suffering from table bloat. But if you never delete or update records then that's irrelevant. Does reindexing or clustering the table make a marked difference? I would suggest you post your schema and the results of vacuum verbose. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Thousands of tables versus on table?
On 6/4/07 3:43 PM, Gregory Stark [EMAIL PROTECTED] wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. How often do you update or delete records and how many do you update or delete? Once per day is a very low frequency for vacuuming a busy table, you may be suffering from table bloat. But if you never delete or update records then that's irrelevant. It looks like the most inserts that have occurred in a day is about 2000. The responders table has 1.3 million records, the responses table has 50 million records. Most of the inserts are in the responses table. Does reindexing or clustering the table make a marked difference? Clustering sounds like it might be a really good solution. How long does a cluster command usually take on a table with 50,000,000 records? Is it something that can be run daily/weekly? I'd rather not post the schema because it's not mine - I'm a consultant. I can tell you our vacuum every night is taking 2 hours and that disk IO is the real killer - the CPU rarely gets higher than 20% or so. =thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Thousands of tables versus on table?
On 6/4/07, Thomas Andrews [EMAIL PROTECTED] wrote: On 6/4/07 3:43 PM, Gregory Stark [EMAIL PROTECTED] wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. How often do you update or delete records and how many do you update or delete? Once per day is a very low frequency for vacuuming a busy table, you may be suffering from table bloat. But if you never delete or update records then that's irrelevant. It looks like the most inserts that have occurred in a day is about 2000. The responders table has 1.3 million records, the responses table has 50 million records. Most of the inserts are in the responses table. Does reindexing or clustering the table make a marked difference? Clustering sounds like it might be a really good solution. How long does a cluster command usually take on a table with 50,000,000 records? Is it something that can be run daily/weekly? I'd rather not post the schema because it's not mine - I'm a consultant. I can tell you our vacuum every night is taking 2 hours and that disk IO is the real killer - the CPU rarely gets higher than 20% or so. =thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings What OS are you running on? -- Yudhvir Singh Sidhu 408 375 3134 cell
Re: [PERFORM] Thousands of tables versus on table?
Linux 2.4.9, if I¹m reading this right. =thomas On 6/4/07 4:08 PM, Y Sidhu [EMAIL PROTECTED] wrote: On 6/4/07, Thomas Andrews [EMAIL PROTECTED] wrote: On 6/4/07 3:43 PM, Gregory Stark [EMAIL PROTECTED] wrote: Thomas Andrews [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. How often do you update or delete records and how many do you update or delete? Once per day is a very low frequency for vacuuming a busy table, you may be suffering from table bloat. But if you never delete or update records then that's irrelevant. It looks like the most inserts that have occurred in a day is about 2000. The responders table has 1.3 million records, the responses table has 50 million records. Most of the inserts are in the responses table. Does reindexing or clustering the table make a marked difference? Clustering sounds like it might be a really good solution. How long does a cluster command usually take on a table with 50,000,000 records? Is it something that can be run daily/weekly? I'd rather not post the schema because it's not mine - I'm a consultant. I can tell you our vacuum every night is taking 2 hours and that disk IO is the real killer - the CPU rarely gets higher than 20% or so. =thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings What OS are you running on?
Re: [PERFORM] Thousands of tables versus on table?
Thomas Andrews [EMAIL PROTECTED] writes: Clustering sounds like it might be a really good solution. How long does a cluster command usually take on a table with 50,000,000 records? Is it something that can be run daily/weekly? ouch, ok, with 50M records cluster isn't going to be quick either, especially if you have a lot of indexes. With those kinds of numbers and with the kind of workload you're describing where you have different areas that are really complete separate you might consider partitioning the table. That's essentially what you're proposing anyways. Honestly table partitioning in Postgres is pretty young and primitive and if you have the flexibility in your application to refer to different tables without embedding them throughout your application then you might consider that. But there are also advantages to being able to select from all the tables together using the partitioned table. I'd rather not post the schema because it's not mine - I'm a consultant. I can tell you our vacuum every night is taking 2 hours and that disk IO is the real killer - the CPU rarely gets higher than 20% or so. Do you ever update or delete these records? If you never update or delete records then the vacuum is mostly a waste of effort anyways. (You still have to vacuum occasionally to prevent xid wraparound but that's much much less often). If you do delete records in large batches or have lots of updates then vacuuming daily with default fsm settings probably isn't enough. How many indexes do you have? And if they don't all have client_id in their prefix then I wonder about the plans you're getting. It's unfortunate you can't post your schema and query plans. It's possible you have some plans that are processing many more records than they need to to do their work because they're using indexes or combinations of indexes that aren't ideal. specific enough -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Thousands of tables versus on table?
can tell you our vacuum every night is taking 2 hours and that disk IO is the real killer - the CPU rarely gets higher than 20% or so. How many gigabytes of stuff do you have in this database ? ( du -sh on the *right* directory will suffice, don't include the logs etc, aim for data/base/oid) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Thousands of tables versus on table?
Gregory Stark wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. That's not entirely true. PostgreSQL can be markedly faster using partitioning as long as you always access it by referencing the partitioning key in the where clause. So, if you partition the table by date, and always reference it with a date in the where clause, it will usually be noticeably faster. OTOH, if you access it without using a where clause that lets it pick partitions, then it will be slower than one big table. So, while this poster might originally think to have one table for each user, resulting in thousands of tables, maybe a compromise where you partition on userid ranges would work out well, and keep each partition table down to some 50-100 thousand rows, with smaller indexes to match. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Thousands of tables versus on table?
On Mon, 4 Jun 2007, Scott Marlowe wrote: Gregory Stark wrote: Thomas Andrews [EMAIL PROTECTED] writes: I guess my real question is, does it ever make sense to create thousands of tables like this? Sometimes. But usually it's not a good idea. What you're proposing is basically partitioning, though you may not actually need to put all the partitions together for your purposes. Partitioning's main benefit is in the management of the data. You can drop and load partitions in chunks rather than have to perform large operations on millions of records. Postgres doesn't really get any faster by breaking the tables up like that. In fact it probably gets slower as it has to look up which of the thousands of tables you want to work with. That's not entirely true. PostgreSQL can be markedly faster using partitioning as long as you always access it by referencing the partitioning key in the where clause. So, if you partition the table by date, and always reference it with a date in the where clause, it will usually be noticeably faster. OTOH, if you access it without using a where clause that lets it pick partitions, then it will be slower than one big table. So, while this poster might originally think to have one table for each user, resulting in thousands of tables, maybe a compromise where you partition on userid ranges would work out well, and keep each partition table down to some 50-100 thousand rows, with smaller indexes to match. what if he doesn't use the postgres internal partitioning, but instead makes his code access the tables named responsesN where N is the id of the customer? this is what it sounded like he was asking initially. David Lang ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
