[GENERAL] Many, many materialised views - Performance?

Hi, I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of small tables. I'm wondering if materialised views will fare better, or if they too create a lot of fluff in pg_catalog and many files on-disk? -Toby -- Sent via pgsql-g

[GENERAL] Can checkpoint creation be parallel?

Hello: Sorry for disturbing: I have one question about checkponint . That is : can checkpoint be parallel? It is said that checkpoint will be activated according to either conditions: 1)After last checkpoint, checkpoint_timeout seconds passed. 2)When shared_buffers memory above checkpoint_segm

Re: [GENERAL] Many, many materialised views - Performance?

On Oct 8, 2013, at 9:36, Toby Corkindale wrote: > Hi, > I've discovered previously that Postgres doesn't perform so well in some > areas once you have hundreds of thousands of small tables. > > I'm wondering if materialised views will fare better, or if they too create a > lot of fluff in pg_

Re: [GENERAL] Many, many materialised views - Performance?

On 10/8/2013 12:36 AM, Toby Corkindale wrote: I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of small tables. i'm having a hard time envisioning a database that would need so many different record types. -- john r pier

[GENERAL] wal writer process vs writer process vs checkpoint process

Hello My customer asked me about the relationship about PostgreSQL's following process: wal writer process writer process checkpoint process Currently My understanding is: If I execute some DML, then,Firstly , the related operation or data will be written to wal buffer. Secondly, the related dat

Re: [GENERAL] Can checkpoint creation be parallel?

高健 wrote: > I have one question about checkponint . That is : can checkpoint be parallel? > > It is said that checkpoint will be activated according to either conditions: > > 1)After last checkpoint, checkpoint_timeout seconds passed. > > 2)When shared_buffers memory above checkpoint_segments si

[GENERAL] Urgent Help Required

I had got this message while running vacuum full from backend . Now My database is not starting , Help pls. backend> vacuum full debug; WARNING: database "debug" must be vacuumed within 99 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in "debug". ERROR: rel

Re: [GENERAL] postgreSQL query via JDBC in different OS taking different running time?

On Tue, Oct 8, 2013 at 3:48 AM, Aftab Ahmed Chandio wrote: > What do u suggest me, where I need to make performance tuning? w hich > configuration setting must need to modify in the linux? Well, others have already pointed out that you should first measure your query on the server. I would point

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Monday 07 October 2013 21:33:14 Jeff Janes wrote: > On Mon, Oct 7, 2013 at 11:44 AM, Michal TOMA wrote: > > I gave it in my first post. It is a software raid 1 of average 7200 rpm > > disks > > (Hitachi HDS723020BLE640) for the main tablespace and a software raid 1 > > of SSDs for onother table

Re: [GENERAL] pg_xlog size growing untill it fills the partition

On Monday 07 October 2013 15:23:33 Marcin Mańk wrote: > On Thu, Oct 3, 2013 at 11:56 PM, Michal TOMA wrote: > > Now I have: > > checkpoint_completion_target = 0.9 > > wal_buffers = 8MB > > checkpoint_segments = 16 > > checkpoint_timeout = 20min > > shared_bu

Re: [GENERAL] Why there are no max_wal_receivers

On 10/07/2013 11:05 PM, 高健 wrote: Hello: Thank you all for kindly replying my question. I read the documentation about pg_basebackup. The description says: The server must also be configured with max_wal_senders set high enough to leave at least one session available for the backup. Dose

Re: [GENERAL] Urgent Help Required

On 10/08/2013 03:55 AM, shailesh singh wrote: I had got this message while running vacuum full from backend . Now My database is not starting , Help pls. backend> vacuum full debug; WARNING: database "debug" must be vacuumed within 99 transactions HINT: To avoid a database shutdown, execut

Re: [GENERAL] Urgent Help Required

Adrian Klaver-3 wrote > On 10/08/2013 03:55 AM, shailesh singh wrote: >> I had got this message while running vacuum full from backend . Now My >> database is not starting , Help pls. >> >> backend> vacuum full debug; >> WARNING: database "debug" must be vacuumed within 99 transactions >> HINT

[GENERAL] ALTER TABLE VALIDATE CONSTRAINT w/o lock

Hi, I want to add a new column named sell_time to a table which already has a boolean field named is_sold. Then I want to create a new check constraint: ALTER TABLE ... CHECK(is_sold AND sell_time IS NOT NULL OR NOT is_sold AND sell_time IS NULL) NOT VALID; The constraint is added as NOT V

Re: [GENERAL] Urgent Help Required

Dear all, First of all i wish to share actual error meassge, Below are the queries i had executed on the terminal on my server -bash-3.2$ touch fix.sql -bash-3.2$ echo "VACUUM FULL;" > fix.sql -bash-3.2$ postgres -D /var/lib/pgsql/data patnadbold < fix.sql WARNING: database "patnadbold" must be

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: > HINT: To avoid a database shutdown, execute a full-database VACUUM in > "patnadbold". > ERROR: could not access status of transaction 33011 > DETAIL: could not open file "pg_clog/": No such file or directory > exit > > > After this i am a

Re: [GENERAL] Urgent Help Required

On 10/08/2013 08:03 AM, shailesh singh wrote: Dear all, First of all i wish to share actual error meassge, Below are the queries i had executed on the terminal on my server -bash-3.2$ touch fix.sql -bash-3.2$ echo "VACUUM FULL;" > fix.sql -bash-3.2$ postgres -D /var/lib/pgsql/data patnadbold

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 08, 2013 at 08:06:50AM -0700, bricklen wrote: > On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: > > > HINT: To avoid a database shutdown, execute a full-database VACUUM in > > "patnadbold". > > ERROR: could not access status of transaction 33011 > > DETAIL: could not open file "p

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh wrote: > > On Tue, Oct 8, 2013 at 8:36 PM, bricklen wrote: > >> >> On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh wrote: >> >>> HINT: To avoid a database shutdown, execute a full-database VACUUM in >>> "patnadbold". >>> ERROR: could not access status

[GENERAL] Many thousands of partitions

Hello, I have question regarding one of caveats from docs: http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html "Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions." What's the alternative? Neste

Re: [HACKERS] [GENERAL] Urgent Help Required

yes i am executing psql to connect to this database. su - postgres psql patnadbold On Tue, Oct 8, 2013 at 8:48 PM, bricklen wrote: > > > > On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh wrote: > >> >> On Tue, Oct 8, 2013 at 8:36 PM, bricklen wrote: >> >>> >>> On Tue, Oct 8, 2013 at 8

Re: [GENERAL] Many thousands of partitions

On Tue, Oct 8, 2013 at 8:23 AM, Grzegorz Tańczyk wrote: > Hello, > > I have question regarding one of caveats from docs: > http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html > > If you are using Postgresql 8.3 then you should consider upgrading to 9.3 instead. > "Partitioning using

Re: [HACKERS] [GENERAL] Urgent Help Required

On Tue, Oct 8, 2013 at 8:25 AM, shailesh singh wrote: > yes i am executing psql to connect to this database. > > su - postgres > psql patnadbold > ..and then what? Does it immediately throw an error stating that you must issue a VACUUM?

Re: [HACKERS] [GENERAL] Urgent Help Required

First, while vacuum is usually preferred to vacuum full, in this case, I usually find that vacuum full clears up enough cruft to be worth it (not always, but especially if you are also having performance issues). Secondly I would recommend using the vacuumdb command from the shell instead of psql

Re: [GENERAL] Urgent Help Required

> postgres -D /var/lib/pgsql/data patnadbold < fix.sql What the heck is the point of feeding "VACUUM FULL;" into the standard input of the postgres command? "postgres" simply starts the server, it does not execute arbitrary SQL. Once the database is started you want to use "psql" - either inter

Re: [GENERAL] [HACKERS] Urgent Help Required

*Don't* VACUUM FULL. Just VACUUM. It's not the same thing. ...Robert -- 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] Can checkpoint creation be parallel?

On Tue, Oct 8, 2013 at 1:54 AM, 高健 wrote: > Hello: > > Sorry for disturbing: > > I have one question about checkponint . That is : can checkpoint be > parallel? > PostgreSQL does not currently implement it that way. And it is hard to see what the benefit would be of doing so. But it should be

[GENERAL] Looking for some advise on training materials

I want to get some folks some professional development work to do on the topic of databases (PostgreSQL-related, but I'm looking for some more general material on the subject of relational design, etc as well) Because the training budget is used up for this year, and I don't want to wait until Ja

[GENERAL] need elegant way to store and query tables with variable headers

Hi: Longshot, but here goes Someone is asking me for a way to architect a model which will store basic table data (columns with names and rows), but the number and name of the columns are both variables. I'll call these "data-tables" here. Example, Store this "data-table" 'col1'

[GENERAL] Re: need elegant way to store and query tables with variable headers

> Example, Store this "data-table" > > 'col1' 'col2' 'col3' 'val' > 0 0 0 'a' > 0 0 1 'b' > 0 1 0 'c' Random thoughts: If you know of a non-elegant way to accomplish this

Re: [GENERAL] Urgent Help Required

On 10/08/2013 09:03 AM, David Johnston wrote: postgres -D /var/lib/pgsql/data patnadbold < fix.sql What the heck is the point of feeding "VACUUM FULL;" into the standard input of the postgres command? "postgres" simply starts the server, it does not execute arbitrary SQL. Once the database

Re: [GENERAL] need elegant way to store and query tables with variable headers

Gauthier, Dave wrote on 08.10.2013 20:27: Someone is asking me for a way to architect a model which will store basic table data (columns with names and rows), but the number and name of the columns are both variables. I'll call these "data-tables" here. sounds like the hstore extension could

[GENERAL] ERROR: invalid value "????" for "YYYY"

I'm posting this question to pgsql-general. Hopefully someone can share some insights with me. I have a bunch of tables in the database and in a separate schema. The tables' names are in this format: ???_???_???_MMDD where the last 8 characters

Re: [GENERAL] ERROR: invalid value "????" for "YYYY"

I think that clearly it is getting "pg_statistic" from the pg_catalog schema and feeding it to the query. So "tatistic" gets extracted, being the last 8 characters. And then "tati" is fed to the part of the to_date function. But I already specify the table_schema to be the one that I want.

Re: [GENERAL] ERROR: invalid value "????" for "YYYY"

On 08/10/2013 21:50, Brian Wong wrote: select table_name, to_date(right(table_name, 8), 'MMDD') blah from information_schema.tables where table_schema = '' and table_catalog = '' *and to_date(right(table_name, 8), 'MMDD') is not null;* *ERROR: invalid value "tati" for ""* DE

Re: [GENERAL] ERROR: invalid value "????" for "YYYY"

On 08/10/2013 23:03, Rowan Collins wrote: With tables_with_dates As ( select table_name, to_date(right(table_name, 8), 'MMDD') blah from information_schema.tables where table_schema = '' and table_catalog = '' ) Select table_name, blah Where blah > '2013-01-01'::date

Re: [GENERAL] Many thousands of partitions

On 10/08/2013 11:23 AM, Grzegorz Tańczyk wrote: Hello, I have question regarding one of caveats from docs: http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html "Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousan

Re: [GENERAL] ERROR: invalid value "????" for "YYYY"

On 10/08/2013 01:50 PM, Brian Wong wrote: I'm posting this question to pgsql-general . Hopefully someone can share some insights with me. I have a bunch of tables in the database and in a separate schema. The tables' names are in this format:

Re: [GENERAL] Looking for some advise on training materials

> Because the training budget is used up for this year, and I don't want > to wait until January, I'm trying to find things that are inexpensive > enough to get started on right away. I'm looking forward to others' suggestions which will surely be better, but I originally learned the basics of dat

Re: [GENERAL] Many, many materialised views - Performance?

On 08/10/13 19:58, Alban Hertroys wrote: On Oct 8, 2013, at 9:36, Toby Corkindale wrote: Hi, I've discovered previously that Postgres doesn't perform so well in some areas once you have hundreds of thousands of small tables. I'm wondering if materialised views will fare better, or if they too

Re: [GENERAL] Many, many materialised views - Performance?

Toby Corkindale wrote: > In this instance, we have a lot of queries that build certain aggregate > results, which are very slow. The queries were initially all implemented > as views, but then we started doing a type of materialising of our own, > turning them into tables with CREATE TABLE AS SEL

Re: [GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?

http://kettle.pentaho.com/ works pretty good to. On Mon, Oct 7, 2013 at 11:39 AM, Michal TOMA wrote: > Talend? > http://talend.com/ > But usually all major ETL tools do work with any database including > PostgreSQL > > On Monday 07 October 2013 17:54:36 Vick Khera wrote: > > http://lmgtfy.com/

[GENERAL] streaming replication timeout error

Hello: My customer encountered some connection timeout, while using one primary-one standby streaming replication. The original log is japanese, because there are no error-code like oracle's ora-xxx, I tried to translate the japanese information into English, But that might be not correct English

Re: [GENERAL] Can checkpoint creation be parallel?

Thanks a lot for all your kindly replying! >The background writer and ordinary backends might write data (for their own rea>sons) that the checkpointer would have otherwise needed to write anyway. And does the ordinary backends refer to the ones created when a client make a connection to PG? For

[GENERAL]

set pgsql-general digest