Re: [GENERAL] Need schema design advice
Jeff, this is *exactly* the kind of feedback I was hoping to get. Thanks so much for the link and the explanation. Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PQexecParams question
I don't see that working using arrays here. Can you elaborate please ?
Re: [GENERAL] Need schema design advice
knee deep in a schema design myself ..curious as to which advice did jeff offer for schema design? thanks Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > To: pgsql-general@postgresql.org > From: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Need schema design advice > Date: Sun, 12 Oct 2008 13:55:42 + > > Jeff, this is *exactly* the kind of feedback I was hoping to get. > Thanks so much for the link and the explanation. > > Matt > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
Re: [GENERAL] PQexecParams question
Hello 2008/10/12 Grzegorz Jaśkiewicz <[EMAIL PROTECTED]>: > I don't see that working using arrays here. Can you elaborate please ? > select * from foo where a = any ($1) postgres=# select 1 = any(array[1,2,3]); ?column? -- t (1 row) regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Chart of Accounts
Dear All, I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently. I am trying to implement an "intelligent" Chart of Accounts for an accounting program. The following is long-winded but please bear with me: I have a table coa (chart of accounts) with the following schema CREATE TABLE coa( coa_id serial not null, parent_id int not null default 0, account_name text not null, amt money default 0, primary key(coa_id) ); After populating the database with basic accounts it resembles this (the hierarchy is mine): coa_id, parent_id, account_name, amt 0,-1, 'Chart of Accounts',0.00 1, 0, 'Assets',0.00 5, 1, 'Fixed Assets',0.00 6, 5, 'Motor Van', 0.00 --truncated --- 2, 0, 'Liabilities', 0.00 3, 0, 'Income', 0.00 4, 0, 'Expenses',0.00 So far, so good. I would like it so that if the amt of a a child account changes, the parent account is updated, if a child account is deleted, the amount is reduced off of the parent account etc. I have managed to achieve this using the following trigger functions: CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS $body$ begin update coa set amt = amt - old.amt where coa_id = old.parent_id; return old; end; $body$ LANGUAGE 'plpgsql' -- CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS $body$ begin UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id; return new; end; $body$ LANGUAGE 'plpgsql' CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS $body$ begin IF new.parent_id = old.parent_id THEN UPDATE coa SET amt = amt + (new.amt - old.amt) WHERE coa_id = new.parent_id; ELSE UPDATE coa SET amt = amt - old.amt WHERE parent_id = old.parent_id; UPDATE coa SET amt = amt + new.amt WHERE parent_id = new.parent_id; END IF; RETURN new; end; $body$ LANGUAGE 'plpgsql' These have been bound to the respective ROW before triggers. And they work as expected upto a certain extent. eg assigning a value to 'Motor Van' updates the relevant parent accounts: UPDATE coa SET amt = 4000 WHERE coa_id = 6; The problem comes about when one wants to change the parent account for a sub account eg, assuming in the example above that 'Motor Van' was a liability, attempting to change its parent_id from 1 to 2 is erronous and somewhat interesting because the amt for all related accounts are reset to unpredictible values, AND the parent_id does not change anyway. The problem lies squarely in the function coa_upd_amt(). Any ideas. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Run postgresql engine in readonly mode?
On Oct 11, 2008, at 6:41 PM, Joshua Tolley wrote: On Thu, Oct 9, 2008 at 2:37 AM, Galland Gregoire <[EMAIL PROTECTED]> wrote: Hi all! I would like to run all my databases in a readonly mode just for a few hours (migration plan). Is it a way to tell the postgresql engine to run in readonly? Sincerly G.Galland This comes up periodically on lists. There's not really a way. You can set transactions as read only (see docs for SET TRANSACTION) but that's probably not feasible, as you'd have to change your application. You might create triggers preventing INSERTs and UPDATEs, but a good bet is just to back up before hand and warn your users. set default_transaction_read_only to true? Not entirely proof against a determined user, but good enough for most things, I'd guess. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reg: Permission error in Windows psql while trying to read sql commands from file
On Sun, 2008-10-12 at 09:25 +0530, Raj K wrote: > Since it is in windows - I could not find any specific file permission > mechanisms similar to linux. (This is my first foray in windows - so I > am a newbie there too ) > The computer is not in a network. So, through googling, I found that > to share it, we have to move it to > C:\Documents and Settings\All Users\Documents\ - which I did. > > But even that did not help - as mentioned in the first mail. Try using forward slashes in your path: testdb=# \i c:/testdb.txt And if you have spaces, enclose the path in quotation marks: # \i 'C:/Documents and Settings/All Users/Documents/DB/testdb.txt' > If you could help me on this I would be much obliged.. > > Regards > Raj - Josh Williams -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need schema design advice
On Sat, Oct 11, 2008 at 05:10:26PM +, Matthew Wilson wrote:
> I need to track employees and their preferred locations, shifts, and
> stations.
As always there's a trade off between "general" EAV style designs and
more specific ones (as noted by Jeff). One, more EAV style, design that
sprung to mind is:
CREATE TABLE preftypes (
preftype TEXT PRIMARY KEY
);
CREATE TABLE prefopts (
prefopt TEXT PRIMARY KEY,
preftype TEXT REFERENCES preftype
);
CREATE TABLE emps (
empid TEXT PRIMARY KEY
);
CREATE TABLE empprefs (
empid TEXT REFERENCES emps,
prefopt TEXT REFERENCES prefopts,
PRIMARY KEY (empid, prefopt)
);
INSERT INTO preftypes (prefname) VALUES
('location'), ('shift'), ('station');
INSERT INTO emps (empid) VALUES
('alice'), ('bob');
INSERT INTO prefopts (preftype, prefopt) VALUES
('location', 'west-side'),
('location', 'north-side'),
('shift','morning'),
('shift','night'),
('station', 'cash-register'),
('station', 'dishwasher');
INSERT INTO empprefs (empid, prefopt) VALUES
('alice', 'west-side'),
('alice', 'morning'),
('alice', 'cash-register'),
('bob', 'west-side'),
('bob', 'north-side'),
('bob', 'night'),
('bob', 'dishwasher');
you may want to move the "preftype" into the primary key of the
"prefopts" table; that would force you to reference it in the "empprefs"
table making queries asking for employee's preferences to specific
preftypes easier.
> create table preferences (
>
> employee_id int references employee (id),
> other_table_name text, /
> other_table_id int));
"other_table_name" sounds like bad style; no real way to enforce
integrity constraints (rules/triggers maybe, but it would be a bit of a
fiddle and prone to subtle bugs).
The scheme I gave should allow you to add new preference types, but it
makes it difficult to add details about the types' options. As always,
it's a trade off between what you're optimizing for. If you're adding
more preference types then go for a EAV style design, if you're going to
want to add more details about the preferences (this does seem to be the
common case, which is why most people here stay away from EAV designs).
This is easier to get started with, so if it's just going to be a quick
tech demo then this may be good. Be warned though that code from demos
tends to live much longer than you'd ever possibly expect so doing the
more long winded thing first may be easier--even a couple of months down
the line.
Sam
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need schema design advice
On Sun, Oct 12, 2008 at 08:12:40PM +0100, I wrote: > As always, it's a trade off between what you're optimizing for. If > you're adding more preference types then go for a EAV style design, if > you're going to want to add more details about the preferences (this > does seem to be the common case, which is why most people here stay > away from EAV designs)[...] oops, got distracted and forgot to finish this sentence! ...then you're better off with a non-EAV style design. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reg: Permission error in Windows psql while trying to read sql commands from file
On 12/10/2008 04:55, Raj K wrote: > Since it is in windows - I could not find any specific file > permission mechanisms similar to linux. To see the file permissions, right-click on the file and look at the "Security" tab - it lists users and their permissions on the file. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] New shapshot RPMs (Oct 11 2008) are ready for testing
Hi, Released new sets: http://people.planetpostgresql.org/devrim/index.php?/archives/124-New-shapshot-RPMs-Oct-11-2008-are-ready-for-testing.html Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] PQexecParams question
that would be a type mismatch, heh.
Re: [GENERAL] Starting PostgreSQL
admin wrote: > Sorry folks, a perennial one I'm sure ... > > I have read the manual and Googled for a couple of hours but still can't > connect to PostgreSQL 8.3.4 (the PGDG RPMs running on an up to date > CentOS 5.2). > > I continually get this message: > > psql: could not connect to server: No such file or firectory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PDSQL.0"? > > Yes, the server is running as 'ps -aux' and 'netstat -l' and 'service > postgresql status' all confirm. Do you mean you have something like this in your netstan -l? unix 2 [ ACC ] STREAM LISTENING 12587 /tmp/.s.PGSQL.5432 note, this is on a linux box with postgresql in standard configuration. Just look at the port number embedded in the socket name. I don't really think you can run a process on port 0. I think your psql is looking for the wrong socket. Try: $ psql -p 5432 ... If you don't see any unix socket for PG (I don't even think that's possible), then you need to use IP sockets: $ psql -p 5432 -h localhost ... > > service postgresql start/stop/restart works without errors > pg_ctl start/stop/restart works without errors > > There is no socket file in /tmp. Opps sorry I missed this. Well double check with netstat, but it's possible your PG is not configured for Unix sockets... even if I wouldn't know how to do that. I just checked a CentOS5.2 running PG and there it is: $ ls -l /tmp/.s.PGSQL.5432 srwxrwxrwx 1 postgres postgres 0 Oct 13 01:22 /tmp/.s.PGSQL.5432 .TM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need schema design advice
could you provide a brief explanation of EAV ? thx Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Sun, 12 Oct 2008 20:22:14 +0100 > From: [EMAIL PROTECTED] > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Need schema design advice > > On Sun, Oct 12, 2008 at 08:12:40PM +0100, I wrote: > > As always, it's a trade off between what you're optimizing for. If > > you're adding more preference types then go for a EAV style design, if > > you're going to want to add more details about the preferences (this > > does seem to be the common case, which is why most people here stay > > away from EAV designs)[...] > > oops, got distracted and forgot to finish this sentence! > > ...then you're better off with a non-EAV style design. > > > Sam > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Stay up to date on your PC, the Web, and your mobile phone with Windows Live. http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/
Re: [GENERAL] Chart of Accounts
You are making this far to complicated. I just redid the accounting side of an application we have access to source code, so been here and done this. If i was not for the rest of the application i would have completely redone the accounting table layout something like this 3 Accounting Tables One has you chart of Accounts Create table coa ( coa_id serial not null, parent_id int not null default 0, doIhaveChildren boolean default false account_name text null ) primary key(coa_id) Create Table general_ledger_transactions( transaction_id serial not null coad_id integer, accounting_period integer, debit numeric(20,10) , credit numeric(20,10), transaction_date datestamp) primary key (transaction_id) special note do not use only 2 decimal points in the accounting tables. If your application uses 10 decimal places somewhere then every table in the database that has decimals needs to have the same precision. Nothing is more annoying where a transaction says 1.01 and the other side says 1.02 due to rounding. Also you want to split out the debit and credits instead of using one column. Example one column accounting table to track values entered how do you handle Crediting a Credit Account Type. is it a negative or positive entry??? Create table accounting_periods ( accounting_period serial not null, start_date date, end_date date, accounting_period_Open boolean) I would used views and the application to create the tree list view i think your after. As you also need to know the Open Balances, Debit, Credits and Closing Balances by accounting period.. One idea is is create a functions that scans through the general_ledger_transactions table to get your values So create a View something like this Example would by Select Sum(debits) + Case when coa.doIhaveChildren then GetChildAccountDebits(coa.coa_id, period_id) else 0.0 end; from general_ledger_transactions, coa, where general_ledger_transactions.coad_id = coa.coa_id and coa.coa_id = SomPassedAccountID group by general_ledger_transactions.period_id What happen is the GetChildAccountDebits() function takes two parameters. One is the coa_id and the other is accounting period to search The function would look something like this return Select Sum(debits) + Case when coa.doIhaveChildren then GetChildAccountDebits(coa.coa_id, period_id) else 0.0 end; from general_ledger_transactions, coa, where general_ledger_transactions.coa_id= coa_id and coa.parent_id = ThePassedAccountID and general_ledger_transactions.period_id =PassedPeriodID This creates a loop back which can be dangers if Parent_account is also a Child_account of itself which creates an endless loop then creates a stack error. Outside of that is works great. i do something very similar Bill of Material and in our Accounting James Hitz wrote: Dear All, I have just started experimenting with PGSQL, with a view to migrate from the SQL server I use currently. I am trying to implement an "intelligent" Chart of Accounts for an accounting program. The following is long-winded but please bear with me: I have a table coa (chart of accounts) with the following schema CREATE TABLE coa( coa_id serial not null, parent_id int not null default 0, account_name text not null, amt money default 0, primary key(coa_id) ); After populating the database with basic accounts it resembles this (the hierarchy is mine): coa_id, parent_id, account_name, amt 0,-1, 'Chart of Accounts',0.00 1, 0, 'Assets',0.00 5, 1, 'Fixed Assets',0.00 6, 5, 'Motor Van', 0.00 --truncated --- 2, 0, 'Liabilities', 0.00 3, 0, 'Income', 0.00 4, 0, 'Expenses',0.00 So far, so good. I would like it so that if the amt of a a child account changes, the parent account is updated, if a child account is deleted, the amount is reduced off of the parent account etc. I have managed to achieve this using the following trigger functions: CREATE OR REPLACE FUNCTION public.coa_del_amt() RETURNS trigger AS $body$ begin update coa set amt = amt - old.amt where coa_id = old.parent_id; return old; end; $body$ LANGUAGE 'plpgsql' -- CREATE OR REPLACE FUNCTION public.coa_ins_amt() RETURNS trigger AS $body$ begin UPDATE coa SET amt = amt + new.amt WHERE coa_id = new.parent_id; return new; end; $body$ LANGUAGE 'plpgsql' CREATE OR REPLACE FUNCTION public.coa_upd_amt() RETURNS trigger AS $body$ begin IF new.parent_id = old.parent_id THEN UPDATE coa SET amt = amt + (new.amt -
Re: [GENERAL] Need schema design advice
On Oct 12, 2008, at 5:51 PM, Martin Gainty wrote:
could you provide a brief explanation of EAV ?
Instead of:
create table vehicles
(
kind text primary key,
wheels int
);
insert into vehicles (kind, wheels) values ('car',4);
insert into vehicles (kind, wheels) values ('bike',2);
create table boats
(
kind text primary key,
displacement int
);
insert into boats (kind,displacement) values ('small boat',1000);
insert into boats (kind,displacement) values ('big boat',30);
... in an EAV model you would do something like:
create table eav
(
kind text primary key,
attr text,
value text
);
insert into eav (kind, attr, value) values ('car','wheels','4');
insert into eav (kind, attr, value) values ('bike','wheels','2');
insert into eav (kind, attr, value) values ('small
boat','displacement','1000');
insert into eav (kind, attr, value) values ('big
boat','displacement','30');
Re: [GENERAL] Need schema design advice
On Sun, Oct 12, 2008 at 8:10 PM, Ben Chobot <[EMAIL PROTECTED]> wrote:
> On Oct 12, 2008, at 5:51 PM, Martin Gainty wrote:
> > could you provide a brief explanation of EAV ?
>
> (...) in an EAV model you would do something like:
> create table eav
> (
> kind text primary key,
> attr text,
> value text
> );
> insert into eav (kind, attr, value) values ('car','wheels','4');
> insert into eav (kind, attr, value) values ('bike','wheels','2');
> insert into eav (kind, attr, value) values ('small
> boat','displacement','1000');
> insert into eav (kind, attr, value) values ('big
> boat','displacement','30');
Truly Hideous (TM).
Martin, I recommend you read the following:
http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Drupal and PostgreSQL - performance issues?
Hi there, I've been toying with using PostgreSQL for some of my Drupal sites for some time, and after his session at OpenSourceDays in Copenhagen last weekend, Magnus Hagander told me that there a quite a few in the PostgreSQL community using Drupal. I have been testing it a bit performance-wise, and the numbers are worrying. In my test, MySQL (using InnoDB) had a 40% lead in performance, but I'm unsure whether this is indicative for PostgreSQL performance in general or perhaps a misconfiguration on my part. In any case, if anyone has any tips, input, etc. on how best to configure PostgreSQL for Drupal, or can find a way to poke holes in my analysis, I would love to hear your insights :) The performance test results can be found on my blog: http://mikkel.hoegh.org/blog/2008/drupal_database_performance_mysql_and_postgresql_compared -- Kind regards, Mikkel Høgh <[EMAIL PROTECTED]> smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Oct 12, 2008, at 11:57 PM, Mikkel Høgh wrote: In any case, if anyone has any tips, input, etc. on how best to configure PostgreSQL for Drupal, or can find a way to poke holes in my analysis, I would love to hear your insights :) I just came across this article about moving Drupal from MySQL to PostgreSQL because of MyISAM data corruption and InnoDB was too slow. http://groups.drupal.org/node/15793 John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Drupal and PostgreSQL - performance issues?
On Sun, Oct 12, 2008 at 9:57 PM, Mikkel Høgh <[EMAIL PROTECTED]> wrote: > Hi there, > > I've been toying with using PostgreSQL for some of my Drupal sites for some > time, and after his session at OpenSourceDays in Copenhagen last weekend, > Magnus Hagander told me that there a quite a few in the PostgreSQL community > using Drupal. > > I have been testing it a bit performance-wise, and the numbers are worrying. > In my test, MySQL (using InnoDB) had a 40% lead in performance, but I'm > unsure whether this is indicative for PostgreSQL performance in general or > perhaps a misconfiguration on my part. The test you're running is far too simple to tell you which database will actually be faster in real world usage. No updates, no inserts, no interesting or complex work goes into just delivering the front page over and over. I suggest you invest some time learning how to drive a real load testing tool like jmeter and build realistic test cases (with insert / update / delete as well as selects) and then see how the databases perform with 1, 2, 5, 10, 50, 100 consecutive threads running at once. Without a realistic test scenario and with no connection pooling and with no performance tuning, I don't think you should make any decisions right now about which is faster. It may well be that in a more realistic testing that mysql keeps up through 5 or 10 client connections then collapses at 40 or 50, while pgsql keeps climbing in performance. This is the performance curve I'm used to seeing from both dbs under heavy load. In simple terms, you're kicking the tires and making a decision based on that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Drupal and PostgreSQL - performance issues?
* Mikkel Høgh ([EMAIL PROTECTED]) wrote: > I have been testing it a bit performance-wise, and the numbers are > worrying. In my test, MySQL (using InnoDB) had a 40% lead in > performance, but I'm unsure whether this is indicative for PostgreSQL > performance in general or perhaps a misconfiguration on my part. The comments left on your blog would probably be a good first step, if you're not doing them already.. Connection pooling could definitely help if you're not already doing it. Drupal's MySQL-isms don't help things either, of course. Also, you don't post anything about the PostgreSQL config, nor the hardware it's running on. The default PostgreSQL config usually isn't appropriate for decent hardware and that could be a contributing factor here. It would also be useful to make sure you've analyze'd your tables and didn't just do a fresh load w/o any statistics having been gathered. We run Drupal on PostgreSQL for an internal site and it works reasonably well. We havn't had any performance problems but it's not a terribly large site either. The issues we've had tend to come from PostgreSQL's somewhat less-than-supported status with Drupal. I've been meaning to look into Drupal's PG support to see about improving it. Perhaps this winter I'll get a chance to. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
> I have been testing it a bit performance-wise, and the numbers are > worrying. In my test, MySQL (using InnoDB) had a 40% lead in > performance, but I'm unsure whether this is indicative for PostgreSQL > performance in general or perhaps a misconfiguration on my part. In my experience the "numbers are always worrying" in a read-only environment. I've used MySQL, but found it rather disturbing when it comes to integrity. MySQL has just some things I can't live with (i.e. silently ignoring overflowing charater types etc). That aside, MySQL IS fast when it comes to read operations. That's probably because it omits a lot of integrity checks postgres and other standard compliant databases do. I'm running a turbogears website with a couple million pages on postgresql and I don't have any problems, so I guess postgres can be configured to service Drupal just as well. Check your indexes and your work memory (postgresql.conf). You want to have the indexes correct and in my experiene the work memory setting is rather important. You want to have enough work memory for sorted queries to fit the resultset into memory - as always disk access is expensive, so I avoid that by having 2GB memory exclusively for postgres - which allows me to do quite expensive sorts in memory, thus cutting execution time down to a couple milliseconds. Oh, and never forget: explain analyze your queries. That will show you whether your indexes are correct and useful, as well as how things are handled. Once you learn how to read the output of that, you'll be surprised what little change to a query suddenly gives you a performance boost of 500% or more. I had queries take 30 seconds cut down to 80 milliseconds just by setting indexes straight. Keep in mind: postgres will take good care of your data (the most important asset in todays economy). I run all my customers on postgres and did so ever since postgres became postgresql (the times way back then when postgres had it's own query language instead of SQL). With a little care I've never seen postgresql dump or corrupt my data - not a "pull the plug" scenario and not a dumb user SQL injection scenario. I was always able to recover 100% of data (but I always used decent hardware, which IMHO makes a big difference). I've toyed with MySQL (not as deep as postgresql I must admit) and it dumped/corruped my data on more than one occasion. Sure, it can be my proficiency level with MySQL, but personally I doubt that. Postgresql is just rock solid no matter what. Uwe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Drupal and PostgreSQL - performance issues?
Alright, my benchmarks might have been a bit naïve. When it comes to hardware, my webserver is a SunFire X2100 with an Opteron 1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux Server 8.04 LTS. When it comes to the resource usage section of my postgresql.conf, the only thing that are not commented out are: shared_buffers = 24MB max_fsm_pages = 153600 I freely admit that the reason I haven't messed with these values is that I have next to no clue what the different things do and how they affect performance, so perhaps an apology is in order. As Scott wrote, "Without a realistic test scenario and with no connection pooling and with no performance tuning, I don't think you should make any decisions right now about which is faster". My apologies. -- Kind regards, Mikkel Høgh <[EMAIL PROTECTED]> On 13/10/2008, at 06.54, Stephen Frost wrote: * Mikkel Høgh ([EMAIL PROTECTED]) wrote: I have been testing it a bit performance-wise, and the numbers are worrying. In my test, MySQL (using InnoDB) had a 40% lead in performance, but I'm unsure whether this is indicative for PostgreSQL performance in general or perhaps a misconfiguration on my part. The comments left on your blog would probably be a good first step, if you're not doing them already.. Connection pooling could definitely help if you're not already doing it. Drupal's MySQL-isms don't help things either, of course. Also, you don't post anything about the PostgreSQL config, nor the hardware it's running on. The default PostgreSQL config usually isn't appropriate for decent hardware and that could be a contributing factor here. It would also be useful to make sure you've analyze'd your tables and didn't just do a fresh load w/o any statistics having been gathered. We run Drupal on PostgreSQL for an internal site and it works reasonably well. We havn't had any performance problems but it's not a terribly large site either. The issues we've had tend to come from PostgreSQL's somewhat less-than-supported status with Drupal. I've been meaning to look into Drupal's PG support to see about improving it. Perhaps this winter I'll get a chance to. Thanks, Stephen smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Out of memory in create index
After dropping an index to do some full-table updating, I'm running into an out of memory issue recreating one of my indices. This is on 8.3 running on linux. The table in question has about 300m rows. The index is on a single integer column. There are approximately 4000 unique values among the rows. create index val_datestamp_idx on vals(datestamp) tablespace space2; About 30 seconds into the query, I get: ERROR: out of memory DETAIL: Failed on request of size 536870912. Increasing maintenance_work_mem from 1GB to 2GB changed nothing at all- exact same error at exact same time. Watching memory on the machine shows the out of memory error happens when the machine is only at about 35% user. create index concurrently shows an identical error. Two other indexes (multicolumn) on the same table have already been successfully recreated, so this puzzles me. Actually, while I was writing this, I added an additional column to the index and it now appears to be completing (memory has reached about the point it had been failing at and is now holding steady, and the query has been going for significantly longer than the 30 seconds or so it took to error out previously). I sort by both columns at times, so the extra column may in fact turn out to be useful, but the failure of the single column create index in the face of the other successful creates has me confused. Can anyone shed some light on the situation? -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
