Re: [ADMIN] Large table or many small tables?
Joel Mc Graw <[EMAIL PROTECTED]> writes: > Assuming that the data is exactly the same, I'm wondering if it is > better to store it in a single large table or many smaller tables. > Right now I have upwards of 2000 tables, some containing upwards of > 10,000 records, and each record contains a large amount of text stored > in a varchar field. 10K records is in the range of "too small to notice". On the other hand, 2000 tables is probably more files than you want to be holding open at once, on most Unixen. You would be much better off with one table having 20M records, I should think. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Linux Distributions
On Thursday 07 March 2002 01:02, Peter Darley wrote: > Ian, > By better I guess I mean faster. I'm looking to get the most speed out of > my database without having to invest in new hardware. > Thanks, > Peter Darley Then I don't think the PostgreSQL binaries provided in any given distribution will have any significant bearing on the matter; what will play a role is 1) how well does the distribution support your hardware; 2) whether your hardware is tuned optimally (BIOS, HD parameters etc.); 3) how PostgreSQL is built and configured 1 and 2 are beyond the scope of this list. How the PostgreSQL binaries were built (self-built or distribution-supplied) will probably have less of an effect on speed than the configuration (although as mentioned distributions often lag behind a version or two; the newer the version, the better the support), which very much depends on you and your hardware. HTH Ian Barwick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Linux Distributions
Ian, By better I guess I mean faster. I'm looking to get the most speed out of my database without having to invest in new hardware. Thanks, Peter Darley -Original Message- From: Ian Barwick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 3:17 PM To: Peter Darley; Pgsql-Admin Subject: Re: [ADMIN] Linux Distributions On Wednesday 06 March 2002 18:49, Peter Darley wrote: > Friends, > I'm wondering if there is a specific Linux distribution for the x86 that > will provide a better PostgreSQL server than the other ones? > Thanks, > Peter Darley Do you have a more precise definition of "better"? If you mean "better" for a production environment then I would recommend the Linux distribution or [insert other OS name here] that best suits your setup and which you feel most comfortable with. Then build and configure PostgreSQL (and any other major applications you will be relying heavily on, e.g. Apache in a webs server environment) yourself. (Distribution-built applications such as these are usually very nicely done and are suitable if you want to get acquainted with them, but often lag a version or two behind the current release version; and of course the distributors don't know very much about your particular production environment). Ian Barwick ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Linux Distributions
On Wednesday 06 March 2002 18:49, Peter Darley wrote: > Friends, > I'm wondering if there is a specific Linux distribution for the x86 that > will provide a better PostgreSQL server than the other ones? > Thanks, > Peter Darley Do you have a more precise definition of "better"? If you mean "better" for a production environment then I would recommend the Linux distribution or [insert other OS name here] that best suits your setup and which you feel most comfortable with. Then build and configure PostgreSQL (and any other major applications you will be relying heavily on, e.g. Apache in a webs server environment) yourself. (Distribution-built applications such as these are usually very nicely done and are suitable if you want to get acquainted with them, but often lag a version or two behind the current release version; and of course the distributors don't know very much about your particular production environment). Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] fk constraint can't be dropped
On Wed, 6 Mar 2002, Zhang, Anna wrote:
> Hi,
> I created a foreign key constraint on table referral like this:
>
> alter table referral add constraint fk_referral foreign key (handle)
> references domain (handle);
> create
>
> alter table referral drop constraint fk_referral restrict;
> ERROR: ALTER TABLE / DROP CONSTRAINT: fk_referral does not exist.
>
> I am using postgres 7.2. If fk can't be dropped, what the synax: ALTER TABLE
> [ ONLY ] table DROP CONSTRAINT constraint { RESTRICT | CASCADE } for in
> docs? If we have to drop fk, does this mean we have to drop table and
The man page for alter table seems fairly explicit that drop constraint
only drops check constraints currently.
> recreate without fk? Stupid!
That's the easiest way. You could also drop the three triggers that
actually are the implementation of the constraint (which you can find
by looking into pg_trigger - look for triggers that have names like
RI_ConstraintTrigger_ that have appropriate the appropriate
name in tgconstrname and use drop trigger
"RI_ConstraintTrigger_"; for each of them)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] production state?????/
How do you get the server into production state? What does production state mean? What parms need to be set for production state in the postgresql.conf file or other file? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] fk constraint can't be dropped
Hi,
I created a foreign key constraint on table referral like this:
alter table referral add constraint fk_referral foreign key (handle)
references domain (handle);
create
alter table referral drop constraint fk_referral restrict;
ERROR: ALTER TABLE / DROP CONSTRAINT: fk_referral does not exist.
I am using postgres 7.2. If fk can't be dropped, what the synax: ALTER TABLE
[ ONLY ] table DROP CONSTRAINT constraint { RESTRICT | CASCADE } for in
docs? If we have to drop fk, does this mean we have to drop table and
recreate without fk? Stupid!
Anybody has an experience that droped fk succefully? Thanks!
Anna Zhang
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] Linux Distributions
> "Andy" == Andy Ruhl <[EMAIL PROTECTED]> writes: Andy> On Wed, 6 Mar 2002, Peter Darley wrote: >> Andy, I've got nothing against FreeBSD, except that I'm familiar >> with Linux and not with BSD. What is it about FreeBSD that is >> going to make it faster than Linux? Any idea where I can find some >> comparative benchmarks? Thanks, Peter Darley Andy> I don't mean to start a flame war here... Andy> Actually, I've seen benchmarks (but don't remember where they Andy> are) that show that Linux and FreeBSD are amazingly equal at Andy> most activities on equivalent hardware. Andy> My point is, machines are faster when they are actually running. Well... from experience, there are points where FreeBSD has strong advantages. Memory management is an obvious one. NFS is is another. Softupdates (the native file system) also has a small edge. We run a linux binary on a FreeBSD cluster (100-or-so nodes) because the FreeBSD cluster will do about 5% more work than the exact same hardware booted with linux (we actually use a diskless DHCP boot --- so we can quickly swap back and forth to test). 5% isn't a huge difference, but it is a real world application. Dave. -- |David Gilbert, Velocet Communications. | Two things can only be | |Mail: [EMAIL PROTECTED] | equal if and only if they | |http://daveg.ca | are precisely opposite. | =GLO ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Linux Distributions
iirc redhat has been busy optimizing postgresql, but if you take the time and sit down, and configure postgres, you can prolly make it just as speedy on any distro. just my .02 Matrix - Original Message - From: "Andy Ruhl" <[EMAIL PROTECTED]> To: "Peter Darley" <[EMAIL PROTECTED]> Cc: "Pgsql-Admin" <[EMAIL PROTECTED]> Sent: Wednesday, March 06, 2002 4:01 PM Subject: Re: Linux Distributions > On Wed, 6 Mar 2002, Peter Darley wrote: > > > Andy, > > I've got nothing against FreeBSD, except that I'm familiar with Linux and > > not with BSD. What is it about FreeBSD that is going to make it faster than > > Linux? Any idea where I can find some comparative benchmarks? > > Thanks, > > Peter Darley > > I don't mean to start a flame war here... > > Actually, I've seen benchmarks (but don't remember where they are) that > show that Linux and FreeBSD are amazingly equal at most activities on > equivalent hardware. > > My point is, machines are faster when they are actually running. > > Andy > > -- > [EMAIL PROTECTED] > SDF Public Access UNIX System - http://sdf.lonestar.org > > > ---(end of broadcast)--- > TIP 3: 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 > ---(end of broadcast)--- TIP 3: 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
[ADMIN] Large table or many small tables?
Assuming that the data is exactly the same, I'm wondering if it is better to store it in a single large table or many smaller tables. Right now I have upwards of 2000 tables, some containing upwards of 10,000 records, and each record contains a large amount of text stored in a varchar field. Problem Domain (for anyone that's interested): I receive data transfers from people; a table is created for each data transfer. Each data transfer has multiple text files. The files are parsed into pages, and each page is stored in the table just created. The number of transfers (and thereby the number of tables) is increasing daily. Thanks for any input. -- Joel Mc Graw DataBill, LLC 602-415-1234 ext. 13 -BEGIN GEEK CODE BLOCK- Version: 3.1 GCS d-@ s: a C UB P-- L- E? W++ N w--- O? M+ V PS+++ PE++ Y+ PGP++ t+ 5++ X tv+ b+ DI++ G e++ h r+++ y -END GEEK CODE BLOCK- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Linux Distributions
On Wed, 6 Mar 2002, Peter Darley wrote: > Andy, > I've got nothing against FreeBSD, except that I'm familiar with Linux and > not with BSD. What is it about FreeBSD that is going to make it faster than > Linux? Any idea where I can find some comparative benchmarks? > Thanks, > Peter Darley I don't mean to start a flame war here... Actually, I've seen benchmarks (but don't remember where they are) that show that Linux and FreeBSD are amazingly equal at most activities on equivalent hardware. My point is, machines are faster when they are actually running. Andy -- [EMAIL PROTECTED] SDF Public Access UNIX System - http://sdf.lonestar.org ---(end of broadcast)--- TIP 3: 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: [ADMIN] Linux Distributions
Andy, I've got nothing against FreeBSD, except that I'm familiar with Linux and not with BSD. What is it about FreeBSD that is going to make it faster than Linux? Any idea where I can find some comparative benchmarks? Thanks, Peter Darley -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Andy Ruhl Sent: Wednesday, March 06, 2002 12:23 PM To: Peter Darley Cc: Pgsql-Admin Subject: Re: [ADMIN] Linux Distributions Yeah, it's called FREEBSD. www.freebsd.org. If you don't like that, go to www.netbsd.org. Sorry, had to do it. Andy On Wed, 6 Mar 2002, Peter Darley wrote: > Date: Wed, 6 Mar 2002 09:49:43 -0800 > From: Peter Darley <[EMAIL PROTECTED]> > To: Pgsql-Admin <[EMAIL PROTECTED]> > Subject: [ADMIN] Linux Distributions > > Friends, > I'm wondering if there is a specific Linux distribution for the x86 that > will provide a better PostgreSQL server than the other ones? > Thanks, > Peter Darley > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- [EMAIL PROTECTED] SDF Public Access UNIX System - http://sdf.lonestar.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Linux Distributions
Yeah, it's called FREEBSD. www.freebsd.org. If you don't like that, go to www.netbsd.org. Sorry, had to do it. Andy On Wed, 6 Mar 2002, Peter Darley wrote: > Date: Wed, 6 Mar 2002 09:49:43 -0800 > From: Peter Darley <[EMAIL PROTECTED]> > To: Pgsql-Admin <[EMAIL PROTECTED]> > Subject: [ADMIN] Linux Distributions > > Friends, > I'm wondering if there is a specific Linux distribution for the x86 that > will provide a better PostgreSQL server than the other ones? > Thanks, > Peter Darley > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- [EMAIL PROTECTED] SDF Public Access UNIX System - http://sdf.lonestar.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] VACUUM in 7.2
On Wed, Mar 06, 2002 at 09:57:48AM +0100, [EMAIL PROTECTED] wrote: > > Hi, > > What function does VACUUM play in 7.2 ? Since a normal VACUUM does not > reclaim space, is it run only to update statistics ? > Is VACUUM still very slow on very large tables (over 50GB) like in 7.1 ? > > Regards > Robert In the 7.2 docs it explains what the new and improved vscuum does. It will go through a table and mark deleted tuples as free so the db can reuse allocked disk space. there is also a new option that will give you the old behavior. It is in the 7.2 admin guide chapter 8. marc > > > ---(end of broadcast)--- > TIP 3: 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Authentication problem
> The "sameuser" part of this works now, since sameuser is a record > matching constraint, not an authentication test. > > There has been some talk of adding a more flexible username-matching > field to pg_hba (whereupon the file name would be inappropriate ;-)) > but no one's really done any work on it. I hope to add the username for 7.3. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: 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
[ADMIN] Linux Distributions
Friends, I'm wondering if there is a specific Linux distribution for the x86 that will provide a better PostgreSQL server than the other ones? Thanks, Peter Darley ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] postgres 7.2 on HPUX 10.20, regression test errors.
Morten Sickel <[EMAIL PROTECTED]> writes:
> I am planning to upgrade a 7.1.3 installation to 7.2 on an old HP-box. When
> running the regression test, some strange errors occurs,
I use HPUX 10.20 every day, and I can assure you 7.2 passes its
regression tests for me. Perhaps something strange about your
installation? What compiler are you using, what configure options,
etc?
> insert into WSlot values ('WS.001.1a', '001', '', '');
> + ERROR: record new has no field backlink
This failure mode seems vaguely familiar to me, but I cannot recall why.
Anyone else seen plpgsql regress test fail this way?
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] --fast switch
Can anyone offer me some insight into a --fast switch that can be used to improve speed on Poastgres inserts. I have been vacuuming database often but still our inserts seem to take quite a bit of time. Someone mentioned this switch to me but I cannot seem to find any documentation on it. thanks Jodi ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] Authentication problem
Dmitry Morozovsky <[EMAIL PROTECTED]> writes: > There is no "fall-through" or "backup": if one record is chosen > and the authentication fails, the following records are not > considered. > Are there any plans to loose this restriction? No. I don't believe we could count on clients to respond to multiple authentication challenges of different types. > It would be very useful to use e.g. > local all ident admin > local sameuserident sameuser > local all passwordpasswd.user The "sameuser" part of this works now, since sameuser is a record matching constraint, not an authentication test. There has been some talk of adding a more flexible username-matching field to pg_hba (whereupon the file name would be inappropriate ;-)) but no one's really done any work on it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Checking Existence
Is there a shell script (or a call to a function) available to check the existence of an index before it is dropped? I'm reloading a set of tables, and I want to drop the index before the COPY, then recreate it. _ Herb Blacker Database Administrator ReCare, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Authentication problem
Hi there colleagues, >From the Docs (Admin 4.1): There is no "fall-through" or "backup": if one record is chosen and the authentication fails, the following records are not considered. Are there any plans to loose this restriction? It would be very useful to use e.g. local all ident admin local sameuserident sameuser local all passwordpasswd.user and have backup pseudo-users in admin ident-map, allow connecting users to personal databases and list exceptions in password file. Or, is there another way to achieve this? Also, of course, it would be _very_ useful to tell full connects and read-only connects (not allowed to create tables/indexes/views/etc...) Sincerely, D.Marck [DM5020, DM268-RIPE, DM3-RIPN] *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- [EMAIL PROTECTED] *** ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] postgres 7.2 on HPUX 10.20, regression test errors.
Hi,
I am planning to upgrade a 7.1.3 installation to 7.2 on an old HP-box. When
running the regression test, some strange errors occurs, I get everyting
fine until:
*** 1007,1053
--- 1007,1095
-- Second we install the wall connectors
--
insert into WSlot values ('WS.001.1a', '001', '', '');
+ ERROR: record new has no field backlink
insert into WSlot values ('WS.001.1b', '001', '', '');
+ ERROR: record new has no field backlink
and so on for lots of the following inserts, seems that the schema for the
database does not agree with what the queries belives, which seems a bit too
strange to me. I recall having seen something about an upgraded
regressiontest for 7.2, but I could not find anything on the web site. Does
anybody have any ideas on what is going on here?
Morten
--
Morten Sickel
Norwegian Radiation Protection Authority
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[ADMIN] VACUUM in 7.2
Hi, What function does VACUUM play in 7.2 ? Since a normal VACUUM does not reclaim space, is it run only to update statistics ? Is VACUUM still very slow on very large tables (over 50GB) like in 7.1 ? Regards Robert ---(end of broadcast)--- TIP 3: 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
