Re: [GENERAL] Would an index benefit select ... order by?
You mean Postgres wouldn't *always* use created_at's index with such access patterns on a big table (even if one exists): select * from foo order by created_at desc; No, it wouldn't necessarily, and that's a good thing. A full-table indexscan can often be slower than a sort because of inefficient disk access patterns. The planner will estimate the cost of each possibility and pick the one that looks cheaper. What if it's really a limited select: select * from foo order by created_at desc limit ; because this is what I meant initially (sorry), would Postgres always use index to get at sorted created_at values, so I don't *have* to create the index? I think maintaining the index has its own penalty so in my upcoming project I'm evaluating the option of skipping defining one entirely unless absolutely necessary. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] what could be blocking an insertion?
Hi, A few days ago an insertion operation issued by a JDBC client that was blocked by postgreSQL. I could verify that by issuing the operation using psql. My question is, what could be causing this? There were other JDBC clients but they never lock any tables. They all used transactions with the serializable isolation level. A day or so later the problem was gone. Any idea? I need to prevent it from happening again. Thanks! - -- Kent Tong Wicket tutorials freely available at http://www.agileskills2.org/EWDW -- View this message in context: http://www.nabble.com/what-could-be-blocking-an-insertion--tf4749607.html#a13581279 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] What makes a Postgres DBA?
Merlin Moncure wrote: On 11/4/07, Kevin Hunter <[EMAIL PROTECTED]> wrote: Following up on a recent thread (http://archives.postgresql.org/pgsql-general/2007-11/msg00064.php) ... Next question and one that I'm not sure how to phrase: how does one become a Postgres-savvy* DBA? Just by working with it as a developer and then moving "up the ranks"? (i.e working with larger, more active setups/datasets?) There's the training options listed on http://www.postgresql.org/about/eventarchive, but I'm personally skeptical at just how much one can retain from a 1-to-5 day course. I would suggest that in order for someone to get that most coveted of titles 'PostgreSQL DBA' on should: Administration: * Understand tradeoffs of autovacuum and scheduled vacuum (increasingly, this question will morph into autovacuum configuration and monitoring) * Understand backups: when to use pitr, pg_dump * Basic configuration: shared_buffers, etc etc * Be able to do task specific tuning: understand difference between oltp and olap environments * Be versatile with psql shell (most of the best DBAs use psql almost exclusively) * No how to troubleshoot and deal with locking problems and runaway queries * Should be able to setup postgresql manually without using packages * Be able to implement a replication technology (Slony) * Understand some of the specific challenges involved in dealing with large databases, which I define as over 10 times the memory in the server. * Understand the challenges involved with 24/7 environments * Understand the differences in low and high security environments and how to set up for both Development * Should be very comfortable with pl/sql, pl/plpgsql (pl/perl or alternative is bonus) * Understand triggers, views, functions, rules, constraints, domains and type and be able to give examples of when they should be used (or not) * Understand good normalization strategies * Be able to discuss pros and cons of surrogate keys * Understand MVCC * C language experience * Strong familiarity with bash or perl (preferably both) Personal * A DBA often anchors a development team. 'Works well with others' is a cliché, but still applies. * At least superficially familiar with the technologies that interface with the database * Be cool and calm under pressure * Be established on the appropriate mailing lists and use good etiquette * Understand that developers are out to get your database merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq An excellent summary ! I would add that the core of being a DBA does have some platform independent aspects. I took some introductory Informix classes years ago on data normalization and the like, which combined with some long-ago computer science classes (data structures, mostly) at UCB to help broaden my understanding. And a lot of OJT and other classes since then, of course. Some of the issues that are general (but which require understanding of each platform's unique aspects). All of these tend to be more meaningful the more you understand about the domain as a whole. Merlin noted them but they are pillars on which a lot of useful databases can be built. * data normalization -- what data goes where * indexing -- what it does, what it helps, what it doesn't help * referential integrity and why it matters (and when it doesn't) * authentication and access issues * locking and related issues * backups and safety nets I particularly like your last points, Merlin, on developers. I've been on both sides of the fence and databases are a specialized animal that usually needs specialized care. Being able to work well with designers and developers is crucial in delivering a useful end result. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) ---(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: [GENERAL] COPY ... FROM and index usage
Dimitri, thank you for your quoting. I'm a pg_bulkload author. pg_bulkload is optimized especially for appending data to table with indexes. If you use it, you don't need to drop index before loading data. But you have to consider conditions carefully as Dimitri said below. See also pg_bulkload README: http://pgfoundry.org/docman/view.php/1000261/473/README.pg_bulkload-2.2.0.txt Best regards, Dimitri Fontaine wrote: Hi, Le Sunday 04 November 2007 11:22:19 Reg Me Please, vous avez écrit : That is, should I drop all indexes during a "COPY ... FROM" in order to gain the maximum speed to load data? When looking for a way to speed up data loading, you may want to consider pgbulkload, a project which optimizes index creation while loading data, and bypass constraints: it's useful when you know you trust input. As I've never used it myself, I can only provides following links: http://pgfoundry.org/projects/pgbulkload http://pgfoundry.org/docman/view.php/1000261/456/20060709_pg_bulkload.pdf Regards, -- Toru SHIMOGAKI<[EMAIL PROTECTED]> NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] What makes a Postgres DBA?
On 11/4/07, Kevin Hunter <[EMAIL PROTECTED]> wrote: > Following up on a recent thread > (http://archives.postgresql.org/pgsql-general/2007-11/msg00064.php) ... > > Next question and one that I'm not sure how to phrase: how does one > become a Postgres-savvy* DBA? Just by working with it as a developer > and then moving "up the ranks"? (i.e working with larger, more active > setups/datasets?) There's the training options listed on > http://www.postgresql.org/about/eventarchive, but I'm personally > skeptical at just how much one can retain from a 1-to-5 day course. I would suggest that in order for someone to get that most coveted of titles 'PostgreSQL DBA' on should: Administration: * Understand tradeoffs of autovacuum and scheduled vacuum (increasingly, this question will morph into autovacuum configuration and monitoring) * Understand backups: when to use pitr, pg_dump * Basic configuration: shared_buffers, etc etc * Be able to do task specific tuning: understand difference between oltp and olap environments * Be versatile with psql shell (most of the best DBAs use psql almost exclusively) * No how to troubleshoot and deal with locking problems and runaway queries * Should be able to setup postgresql manually without using packages * Be able to implement a replication technology (Slony) * Understand some of the specific challenges involved in dealing with large databases, which I define as over 10 times the memory in the server. * Understand the challenges involved with 24/7 environments * Understand the differences in low and high security environments and how to set up for both Development * Should be very comfortable with pl/sql, pl/plpgsql (pl/perl or alternative is bonus) * Understand triggers, views, functions, rules, constraints, domains and type and be able to give examples of when they should be used (or not) * Understand good normalization strategies * Be able to discuss pros and cons of surrogate keys * Understand MVCC * C language experience * Strong familiarity with bash or perl (preferably both) Personal * A DBA often anchors a development team. 'Works well with others' is a cliché, but still applies. * At least superficially familiar with the technologies that interface with the database * Be cool and calm under pressure * Be established on the appropriate mailing lists and use good etiquette * Understand that developers are out to get your database merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Restore a database
On 04/11/2007 20:52, Jesus Arocho wrote: Ok, I committed the worst mistake in db admin. I upgraded to 8.1 on my debian server but forgot to backup one of the databases. The 7.1 directory is still there. I would like a list of options. I am not sure that temporarily reinstalling 7.1 will allow access immediately or must I have to rebuild something. You ought to be able to reinstall 7.1 and have it listen on a different port - then you can do a pg_dump ... | psql ... to transfer the database. I did that once on Windows (using the Cygwin build), and it worked fine. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Restore a database
Ok, I committed the worst mistake in db admin. I upgraded to 8.1 on my debian server but forgot to backup one of the databases. The 7.1 directory is still there. I would like a list of options. I am not sure that temporarily reinstalling 7.1 will allow access immediately or must I have to rebuild something. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Alvaro Herrera <[EMAIL PROTECTED]> writes: No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation >> > Jeff Amiel wrote: >> Can somebody explain this one to me? because of our auditing technique, we >> have many LONG lived temp tables.(one per pooled connection)...so as >> long as the pool isn't disturbed, these temp tables can exist for a long >> time (weeksmonths?) > Hmm. The problem is that the system can't advance the frozen Xid for a > database when there are temp tables that live for long periods of time. > Autovacuum can't vacuum those tables; if the app vacuums them itself > then there's no problem, but you can only vacuum them in the same > session that creates it. I'm not convinced there's a huge problem here. Surely Jeff's app is going to either vacuum or truncate those temp tables occasionally; otherwise they'll bloat to the point of uselessness. Either action will fix the problem. The real issue is that the app has to remember to do that. Perhaps a better TODO item would be * Find a way to autovacuum temp tables though I admit I have no clue how to do that without giving up most of the performance advantages of temp tables. regards, tom lane ---(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: [GENERAL] COPY ... FROM and index usage
Hi, Le Sunday 04 November 2007 11:22:19 Reg Me Please, vous avez écrit : > That is, should I drop all indexes during a "COPY ... FROM" in order to > gain the maximum speed to load data? When looking for a way to speed up data loading, you may want to consider pgbulkload, a project which optimizes index creation while loading data, and bypass constraints: it's useful when you know you trust input. As I've never used it myself, I can only provides following links: http://pgfoundry.org/projects/pgbulkload http://pgfoundry.org/docman/view.php/1000261/456/20060709_pg_bulkload.pdf Regards, -- dim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] COPY ... FROM and index usage
"Josh Tolley" <[EMAIL PROTECTED]> writes: > Although questions of "which is faster" often depend very heavily on > the data involved, the database schema, the hardware, etc., typically > people find it best to drop all indexes during a large import and > recreate them afterward. See also the extensive discussion of this topic at http://www.postgresql.org/docs/8.2/static/populate.html regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Would an index benefit select ... order by?
rihad <[EMAIL PROTECTED]> writes: > You mean Postgres wouldn't *always* use created_at's index with such > access patterns on a big table (even if one exists): > select * from foo order by created_at desc; No, it wouldn't necessarily, and that's a good thing. A full-table indexscan can often be slower than a sort because of inefficient disk access patterns. The planner will estimate the cost of each possibility and pick the one that looks cheaper. regards, tom lane ---(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: [GENERAL] COPY ... FROM and index usage
Il Sunday 04 November 2007 16:21:41 Erik Jones ha scritto: > On Nov 4, 2007, at 9:15 AM, Reg Me Please wrote: > > Il Sunday 04 November 2007 14:59:10 Josh Tolley ha scritto: > >> On 11/4/07, Reg Me Please <[EMAIL PROTECTED]> wrote: > >>> Hi all. > >>> > >>> I'd like to know whether the indexes on a table are updated or > >>> not during > >>> a "COPY ... FROM" request. > >>> > >>> That is, should I drop all indexes during a "COPY ... FROM" in > >>> order to > >>> gain the maximum speed to load data? > >>> > >>> Thanks. > >> > >> Although questions of "which is faster" often depend very heavily on > >> the data involved, the database schema, the hardware, etc., typically > >> people find it best to drop all indexes during a large import and > >> recreate them afterward. > >> > >> - Josh/eggyknap > > > > This sounds very reasonable to me. > > But the first question remains unanswered: > > > > Are the indexes updated during the COPY ... FROM ? > > Of course. Why would think that data could be inserted into a table > by any means without it updating the table's indexes? That would > make the index worthless. > > Erik Jones > > Software Developer | Emma® > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com I do understand your remarks and would agree. But I was thinking about the COPY...FROM request not as a normal INSERT INTO. If this were the case I could have been running a REINDEX TABLE. -- Reg me Please ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COPY ... FROM and index usage
On Nov 4, 2007, at 9:15 AM, Reg Me Please wrote: Il Sunday 04 November 2007 14:59:10 Josh Tolley ha scritto: On 11/4/07, Reg Me Please <[EMAIL PROTECTED]> wrote: Hi all. I'd like to know whether the indexes on a table are updated or not during a "COPY ... FROM" request. That is, should I drop all indexes during a "COPY ... FROM" in order to gain the maximum speed to load data? Thanks. Although questions of "which is faster" often depend very heavily on the data involved, the database schema, the hardware, etc., typically people find it best to drop all indexes during a large import and recreate them afterward. - Josh/eggyknap This sounds very reasonable to me. But the first question remains unanswered: Are the indexes updated during the COPY ... FROM ? Of course. Why would think that data could be inserted into a table by any means without it updating the table's indexes? That would make the index worthless. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] COPY ... FROM and index usage
Il Sunday 04 November 2007 14:59:10 Josh Tolley ha scritto: > On 11/4/07, Reg Me Please <[EMAIL PROTECTED]> wrote: > > Hi all. > > > > I'd like to know whether the indexes on a table are updated or not during > > a "COPY ... FROM" request. > > > > That is, should I drop all indexes during a "COPY ... FROM" in order to > > gain the maximum speed to load data? > > > > Thanks. > > Although questions of "which is faster" often depend very heavily on > the data involved, the database schema, the hardware, etc., typically > people find it best to drop all indexes during a large import and > recreate them afterward. > > - Josh/eggyknap This sounds very reasonable to me. But the first question remains unanswered: Are the indexes updated during the COPY ... FROM ? Thanks again. -- Reg me Please ---(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: [GENERAL] COPY ... FROM and index usage
On 11/4/07, Reg Me Please <[EMAIL PROTECTED]> wrote: > Hi all. > > I'd like to know whether the indexes on a table are updated or not during > a "COPY ... FROM" request. > > That is, should I drop all indexes during a "COPY ... FROM" in order to gain > the maximum speed to load data? > > Thanks. Although questions of "which is faster" often depend very heavily on the data involved, the database schema, the hardware, etc., typically people find it best to drop all indexes during a large import and recreate them afterward. - Josh/eggyknap ---(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: [GENERAL] What makes a Postgres DBA?
The bigger danger is hiring an *Oracle* Financials or *Oracle* Manufacturing person to become a DBA because they have 'Oracle' on their resume This is the most comprehensive analysis of DBA requirements I have seen thus far For myself I'm not tied to any specific Database having worked in mySQL and Postgres this year but I do lean towards Oracle as I know it has the raw horsepower to accomplish distributed transactions in their entire suite of DB Product offerings (also I was an Oracle DBA in years past) The questions on a DBA should add 1)what is a cluster 2)what is a borken chain? 3)when are Btree indexes used 4)Tell me how to performance optimise a multiple condition predicate using the principles of boolean logic Thanks Greg Martin-- - Original Message - Wrom: DDJBLVLMHAALPTCXLYRWTQTIPWIGYOKSTTZ To: "Kevin Hunter" <[EMAIL PROTECTED]> Cc: "Postgres General List" Sent: Sunday, November 04, 2007 4:18 AM Subject: Re: [GENERAL] What makes a Postgres DBA? > On Sun, 4 Nov 2007, Kevin Hunter wrote: > > > Am I assuming too much already by not defining what a DBA is in general? > > Probably. I'd startby looking at the list of DBA duties at > http://en.wikipedia.org/wiki/Database_administrator and considering which > of those are database-specific for a second. > > Two examples of the extremes here. "Installation" is a very product based > thing. I assure that even if you can know everything in the world about > how to install every other type of database system, you might still fail > miserably to bring a new Oracle system up. On the opposite side, most of > the work for "data modeling" is very similiar for any SQL-based database. > > I like to think of this as a grid. Across the top I put the various tasks > DBAs work on. Vertically I go from generic to specific knowledge at > various levels. Consider the task of deleting data from a table. I'd > classify understanding of that subject like this: > > -generic new DBA: can use DELETE properly > -experienced DBA: understands how dead rows get left behind by deletes > -generic expert DBA: can comment on whether the expected balance of > insert vs. delete operations will impact the optimal B-tree fill factor > > -new PostgreSQL DBA: knows to run VACUUM to clean up dead rows > -experienced PG DBA: tunes autovacuum and monitors/adjusts the FSM > parameters to keep dead rows under control > -expert PG DBA: runs reports against pg_stattuple to instrument vacuum > > > "what do I need to able to do to be able to honestly say that 'I am a > > Postgres DBA' on my resume" > > Organizing things as above, this turns into a somewhat fuzzy question > about how much of the grid one has to cover before achieving that goal. > Consider this; who will be more effective as a PostgreSQL DBA: > > -A person with many years of large-scale DBA experience with another > database, but who just starting using PostgreSQL a few months ago > > -Someone who has been using PostgreSQL for a few years but only on small > projects > > There's understanding the breadth of this field, and there's knowing some > depth about each of the topic, and the exact mix of the two varies from > person to person. There's so many aspects to this type of work that > drawing a line and saying "if you know X, Y, and Z you can consider > yourself a Postgres DBA" doesn't make a lot of sense. You mentioned > training and certification. Part of the value of going through either of > those is that you end up with some baseline idea of what someone who has > gone through the class/test has been exposed to. > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > > ---(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 > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Copy the database..
El dom, 04-11-2007 a las 02:16 +0100, Rainer Bauer escribió: > Abandoned wrote: > > >I tryed pg_dump but it is very slowly. Are there any faster way to > >copy database? > > Actually, I was looking for something along the same line. > > I often want to test some functionality in my program based on the same > dataset. However, dump/restore takes too long to be of any use. > > Wouldn't it be possible to copy the database folder and somehow instruct the > postmaster to include the copied data after a restart? > > Rainer > > ---(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 Hi for all, Try with de PITR (http://www.postgresql.org/docs/8.1/static/backup-online.html), should be help you... -- Regards, Julio Cesar Sánchez González. -- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran. www.sistemasyconectividad.com.mxhttp://darkavngr.blogspot.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] COPY ... FROM and index usage
Hi all. I'd like to know whether the indexes on a table are updated or not during a "COPY ... FROM" request. That is, should I drop all indexes during a "COPY ... FROM" in order to gain the maximum speed to load data? Thanks. -- Reg me Please ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] What makes a Postgres DBA?
On Sun, 4 Nov 2007, Kevin Hunter wrote: Am I assuming too much already by not defining what a DBA is in general? Probably. I'd startby looking at the list of DBA duties at http://en.wikipedia.org/wiki/Database_administrator and considering which of those are database-specific for a second. Two examples of the extremes here. "Installation" is a very product based thing. I assure that even if you can know everything in the world about how to install every other type of database system, you might still fail miserably to bring a new Oracle system up. On the opposite side, most of the work for "data modeling" is very similiar for any SQL-based database. I like to think of this as a grid. Across the top I put the various tasks DBAs work on. Vertically I go from generic to specific knowledge at various levels. Consider the task of deleting data from a table. I'd classify understanding of that subject like this: -generic new DBA: can use DELETE properly -experienced DBA: understands how dead rows get left behind by deletes -generic expert DBA: can comment on whether the expected balance of insert vs. delete operations will impact the optimal B-tree fill factor -new PostgreSQL DBA: knows to run VACUUM to clean up dead rows -experienced PG DBA: tunes autovacuum and monitors/adjusts the FSM parameters to keep dead rows under control -expert PG DBA: runs reports against pg_stattuple to instrument vacuum "what do I need to able to do to be able to honestly say that 'I am a Postgres DBA' on my resume" Organizing things as above, this turns into a somewhat fuzzy question about how much of the grid one has to cover before achieving that goal. Consider this; who will be more effective as a PostgreSQL DBA: -A person with many years of large-scale DBA experience with another database, but who just starting using PostgreSQL a few months ago -Someone who has been using PostgreSQL for a few years but only on small projects There's understanding the breadth of this field, and there's knowing some depth about each of the topic, and the exact mix of the two varies from person to person. There's so many aspects to this type of work that drawing a line and saying "if you know X, Y, and Z you can consider yourself a Postgres DBA" doesn't make a lot of sense. You mentioned training and certification. Part of the value of going through either of those is that you end up with some baseline idea of what someone who has gone through the class/test has been exposed to. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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: [GENERAL] Would an index benefit select ... order by?
Should an index be used on a created_at timestamp column if you know you will be using "ORDER BY created_at ASC|DESC" from time to time? Yes. Thanks. This is stated explicitly in 8.3 docs (as opposed to 8.2) http://www.postgresql.org/docs/8.3/static/indexes-ordering.html And you should use EXPLAIN. You mean Postgres wouldn't *always* use created_at's index with such access patterns on a big table (even if one exists): select * from foo order by created_at desc; ? Mind you the distribution of created_at values are going to be as different as the time is (i.e. almost as many different values as there are tables in the row). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] young guy wanting (Postgres DBA) ammo
On Sat, 3 Nov 2007, Ted Byers wrote: As one of these programmers, where is the best place to find the information I need to get it right...I ask you where I can learn what you believe a good DBA needs to know. What a DBA should know in general is a little different from the question I think you want an answer to, which is "what should a programmer know so that they can effectively work like/without a DBA?" There's an academic answer to that question. I could tell you to learn something about data normalization, indexing, what happens on the server when you join two tables, and how cursors work in your language of choice. But without some practice, I don't know how much of that would stick. The most valuable exercise I think someone with a good programming background, but is relatively new to databases, can go through is to work on a dramatically larger data set than you would normally encounter. The main thing I've seen developers do wrong is writing code or designing tables that don't scale well. Since coding works better when you can get quick feedback after changes, it's very easy to settle into working with only small test cases, and that can turn into a serious problem when such code runs into the real world. The only way to really understand how to think more like a DBA is to try and write something that works well against a big pile of data. To throw out some simple guidelines, you want to be working with a database that's at least 10X as big as the amount of RAM on your system, and if you do something that scans the full table like "select * from x" that should take at least a couple of minutes to complete. Now, try to build a program that operates quickly on subsets of this data. Working on this scale will let you discover very quickly if you've made any of the blatant mistakes that programmers unfamiliar with working on full-size data sets tend to make. Most importantly, you don't ever want to write queries that assume it's OK to send all the data to the client to sort through, rather than pushing as much as possible toward the database server. Second, you'll end up needing to construct indexes properly to optimize the server side. And if there's more than one big table involved, you'll be forced to come to grips with how joins work and probably learn something about normalization. You'll know you're learning something valuable whenver you run something that you expect to return almost instantly, but instead it churns away for minutes before finishing. Resist the urge to stop it too quickly, and instead spend that time thinking about what's gone wrong, or monitoring the client and/or server for clues. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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
