Re: [GENERAL] Book or other resource on Postgres-local code?

2016-12-30 8:04 GMT+01:00 Guyren Howe : > > > On Dec 29, 2016, at 23:01 , Regina Obe wrote: > > > > > >> As an aside from my last question about my LYDB effort: > > > >> https://medium.com/@gisborne/love-your-database-lydb- > 23c69f480a1d#.4jngp2rcb > > > >> I

Re: [GENERAL] Book or other resource on Postgres-local code?

> On Dec 29, 2016, at 23:01 , Regina Obe wrote: > > >> As an aside from my last question about my LYDB effort: > >> https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb > >> I would like to find a book or other resource about SQL server-side >>

Re: [GENERAL] Book or other resource on Postgres-local code?

> As an aside from my last question about my LYDB effort: > https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb > I would like to find a book or other resource about SQL server-side > programming (stored procedures etc) best practices in general and for > Postgres in

Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

Reducing worker mem shaved about 12 minutes off the query time.. Thanks for the suggestion. I lowered it to 10MB instead of 100MB > On Dec 29, 2016, at 8:07 PM, Charles Clavadetscher > wrote: > > Forwarding to list. > > -Original Message- > From:

FW: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

Forwarding to list. -Original Message- From: ajmcello [mailto:ajmcell...@gmail.com] Sent: Freitag, 30. Dezember 2016 07:05 To: Charles Clavadetscher Subject: Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb] There are no connections except

Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

Hello > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ajmcello > Sent: Freitag, 30. Dezember 2016 05:54 > To: POSTGRES > Subject: [GENERAL] performance tuning postgresql 9.5.5.10

[GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

I am trying to optimize and tune my server for fastest simple queries with highest connection to server possible. Basically, a SELECT item from table takes 30 minutes on a machine with SSD drives. The table has 900K entries and 12 columns. Using that SELECT query, I then have the ability to make

Re: [GENERAL] Book or other resource on Postgres-local code?

Hi From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe Sent: Donnerstag, 29. Dezember 2016 23:32 To: pgsql-general Subject: [GENERAL] Book or other resource on Postgres-local code? As an aside from my

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I have datagrip and it's OK but it doesn't really do everything I want. I don't understand why it doesn't fetch all objects from the database and then put them into the disk in a directory so I can put it all under git and then let me work on them syncing the files back as they change. For

[GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

Further to my attempts to enlighten application developers about what they might better do in the database: https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb it occurs to me to wonder

Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:51 PM, Tomas Vondra wrote: > On 12/30/2016 12:46 AM, David G. Johnston wrote: > >> On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra >> > >>wrote: >> >> On 12/30/2016 12:33

Re: [GENERAL] Performance PLV8 vs PLPGSQL

If you want an IDE, Jetbrains, the makers of great IDEs like IntelliJ, PyCharm. and AppCode, among others, have recently come out with what is arguably the BEST IDE for DBAs, DataGrip. It runs on most major platforms, and is so good that I have bitten the bullet and paid the yearly subscription

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice. On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent wrote: > I would hope Postgres core folk take no more than a nanosecond to reject > the idea that they work on an IDE.

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while. > On Dec 29, 2016, at 5:32 PM, Tim Uckun wrote: > > Honestly I don't even like JS. Having said

Re: [GENERAL] Special index for "like"-based query

On 12/30/2016 12:46 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra >wrote: On 12/30/2016 12:33 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:21 PM, Job

Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra wrote: > On 12/30/2016 12:33 AM, David G. Johnston wrote: > >> On Thu, Dec 29, 2016 at 4:21 PM, Job > >wrote: >> >> Hello, >> >> in Postgresql 9.6 we

Re: [GENERAL] Special index for "like"-based query

On 12/30/2016 12:33 AM, David G. Johnston wrote: On Thu, Dec 29, 2016 at 4:21 PM, Job >wrote: Hello, in Postgresql 9.6 we have a query running on a very large table based, in some cases, on a like statement: ...

Re: [GENERAL] Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:21 PM, Job wrote: > Hello, > > in Postgresql 9.6 we have a query running on a very large table based, in > some cases, on a like statement: > > ... WHERE FIELD='CODE' OR FIELD LIKE 'CODE_%' > > Which type of index can i create to speed to the

Re: [GENERAL] Performance PLV8 vs PLPGSQL

Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX, As I said before, I think posgres gives a unique and underutilized language

[GENERAL] Special index for "like"-based query

Hello, in Postgresql 9.6 we have a query running on a very large table based, in some cases, on a like statement: ... WHERE FIELD='CODE' OR FIELD LIKE 'CODE_%' Which type of index can i create to speed to the search when the "like" case happens? Thank you! /F -- Sent via pgsql-general

Re: [GENERAL] Performance PLV8 vs PLPGSQL

On 29/12/16 09:12, Francisco Olarte wrote: On Wed, Dec 28, 2016 at 5:53 PM, Jan de Visser wrote: .but the term "impedance mismatch" is at least 25 year old; Much older, I was told it in class at least 32 years ago. as far as I know it was coined _Borrowed_ from

Re: [GENERAL] Book or other resource on Postgres-local code?

On Thu, 29 Dec 2016, Guyren Howe wrote: I would like to find a book or other resource about SQL server-side programming (stored procedures etc) best practices in general and for Postgres in particular. Start here: Rich --

[GENERAL] Book or other resource on Postgres-local code?

As an aside from my last question about my LYDB effort: https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.4jngp2rcb I would like to find a book or other resource about SQL server-side programming

[GENERAL] Write-optimized data structures

Hi, Does Postgresql have any write-optimized data structure like LSM-tree? if not is there any plan to implement that? I'm building a write-heavy OLTP application. I'm looking at write-optimized databases like MyRocks, TokuDB, and Cassandra but I'd prefer to stay within Postgresql. Would PG

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

Thanks Francis. Meanwhile just gone through usage of perl in postgres function. In turn, this Postgres function can be called in pg_bulkload. I think, this task can be done. Let me give a try. -- View this message in context:

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

Yes. I can able to apply those mentioned transformation in pgloader & pg_bulkload-SQL filter. Yet to take performance stats. Meanwhile, I'm trying to figure out the other best possible option. We are counting more on performance, error & audit handling. Thanks -- View this message in

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

rajmhn wrote: > But, how this can be accomplished when it have 100's of columns from source. > Need to apply transformations only for few columns as mentioned above. Did you try pgloader? See http://pgloader.io/ -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

On Thu, Dec 29, 2016 at 8:41 PM, rajmhn wrote: > Thanks Francis.That seems to be a good solution. Yep, but not for your problem as ... > > Thought to use pg_bulkload, a third party library instead of copy, where > reject handling can be done in efficient way. Mine was

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

Thanks Francis.That seems to be a good solution. Thought to use pg_bulkload, a third party library instead of copy, where reject handling can be done in efficient way. Transformation(FILTER) functions can be implemented with any languages in pg_bulkload before it was loaded to table. SQL, C, PLs

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

Hi: On Thu, Dec 29, 2016 at 3:01 PM, Jan de Visser wrote: > On Thursday, December 29, 2016 5:10:08 AM EST rajmhn wrote: ... >> I'm new to C. Gone through this documentation. Not clear, how to start. ... > It seems to me it would be much easier to load the data into a

Re: [GENERAL] vacuumdb --analyze-only scans all pages?

On 29.12.2016 16:10, Tom Lane wrote: Adrian Klaver writes: On 12/28/2016 11:54 PM, Gerhard Wiesinger wrote: vacuumdb --analyze-only --all --verbose INFO: analyzing "public.log" INFO: "log": scanned 3 of 30851 pages, containing 3599899 live rows and 0 dead

Re: [GENERAL] LYDB: What advice about stored procedures and other server side code?

On 12/29/2016 02:12 AM, Christoph Moench-Tegeder wrote: ## Karsten Hilbert (karsten.hilb...@gmx.net): Many applications are not designed to have a "stable" database API. It seems OP is arguing they should. Well, if the environment allows for that, fine. If not, well, duh. Been following

Re: [GENERAL] vacuumdb --analyze-only scans all pages?

Adrian Klaver writes: > On 12/28/2016 11:54 PM, Gerhard Wiesinger wrote: >> vacuumdb --analyze-only --all --verbose >> INFO: analyzing "public.log" >> INFO: "log": scanned 3 of 30851 pages, containing 3599899 live rows >> and 0 dead rows; 3 rows in sample,

Re: [GENERAL] vacuumdb --analyze-only scans all pages?

On 12/28/2016 11:54 PM, Gerhard Wiesinger wrote: Hello, PostgreSQl 9.6.1: after a pg_dump/restore procedure it scans all pages (at least for some of the tables, analyze-only switch is specified). I would expect that only the sample rows are scanned. "log_details": scanned 2133350 of 2133350

Re: [GENERAL] Er Data Modeller for PostgreSQL

On Thu, Dec 29, 2016 at 8:59 AM, Rich Shepard wrote: > On Thu, 29 Dec 2016, Nicolas Paris wrote: > > Hi I'd like to tell about Sql Power Architect >> > > Nicholas, > > SPA was going to be my next re-examination after dbeaver. Since the > latter > easily accomplished

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

Thanks Jan I'm converting the code from Oracle to Postgres. Both version of code will be available for different users. In Oracle, doing these kind of transformation in SQL loader. Need to follow the same kind of approach in Postgres. SQL filter approach was very easy in terms of coding. From

Re: [GENERAL] postgres pg_bulkload c filter function in c programming

On Thursday, December 29, 2016 5:10:08 AM EST rajmhn wrote: > Gurus, > > Reading the data from file and loading it using pg_bulkload- C filter. As > per documentation, C filter is much faster than SQL filter. > > I'm new to C. Gone through this documentation. Not clear, how to start. >

Re: [GENERAL] Er Data Modeller for PostgreSQL

On Thu, 29 Dec 2016, Nicolas Paris wrote: Hi I'd like to tell about Sql Power Architect Nicholas, SPA was going to be my next re-examination after dbeaver. Since the latter easily accomplished what I needed I stopped there. Thanks for the reminder, Rich -- Sent via pgsql-general

[GENERAL] postgres pg_bulkload c filter function in c programming

Gurus, Reading the data from file and loading it using pg_bulkload- C filter. As per documentation, C filter is much faster than SQL filter. I'm new to C. Gone through this documentation. Not clear, how to start. https://www.postgresql.org/docs/current/static/xfunc-c.html. Can someone kindly

Re: [GENERAL] Performance PLV8 vs PLPGSQL

On 12/29/2016 10:35 AM, Pavel Stehule wrote: 2016-12-29 10:03 GMT+01:00 Tim Uckun >: I think it's awesome that postgres allows you to code in different languages like this. It really is a unique development environment and one that is

Re: [GENERAL] Er Data Modeller for PostgreSQL

2016-12-29 1:03 GMT+01:00 Rich Shepard : > On Wed, 28 Dec 2016, Adrian Klaver wrote: > > An example from my machine that works: >> aklaver@tito:~/bin> java -jar schemaSpy_5.0.0.jar -t pgsql -s public -u >> postgres -db production -host localhost -dp >>

Re: [GENERAL] LYDB: What advice about stored procedures and other server side code?

## Karsten Hilbert (karsten.hilb...@gmx.net): > > Many applications are not designed to have a "stable" database API. > It seems OP is arguing they should. Well, if the environment allows for that, fine. If not, well, duh. Regards, Christoph -- Spare Space -- Sent via pgsql-general mailing

Re: [GENERAL] Performance PLV8 vs PLPGSQL

2016-12-29 10:03 GMT+01:00 Tim Uckun : > I think it's awesome that postgres allows you to code in different > languages like this. It really is a unique development environment and one > that is overlooked as a development platform. It would be nice if more > languages were

Re: [GENERAL] Performance PLV8 vs PLPGSQL

2016-12-29 10:04 GMT+01:00 Tim Uckun : > Mostly generating SQL statements to execute. Like for example deciding > which partition to insert into. > Then you don't find any possible performance difference - the query is about 10-100x slower than expression - so the plpgsql

Re: [GENERAL] Performance PLV8 vs PLPGSQL

Mostly generating SQL statements to execute. Like for example deciding which partition to insert into. On Thu, Dec 29, 2016 at 10:00 PM, Pavel Stehule wrote: > > > 2016-12-29 9:23 GMT+01:00 Tim Uckun : > >> I am not doubting the efficacy of stored

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I think it's awesome that postgres allows you to code in different languages like this. It really is a unique development environment and one that is overlooked as a development platform. It would be nice if more languages were delivered in the default package especially lua, V8 and mruby. On

Re: [GENERAL] Performance PLV8 vs PLPGSQL

2016-12-29 9:23 GMT+01:00 Tim Uckun : > I am not doubting the efficacy of stored procs, just wondering which > language is better. From the sound of it string manupilation is slow in > PL-PGSQL but looking at my procs there does seem to be a lot of string > manipulation going

Re: [GENERAL] Performance PLV8 vs PLPGSQL

My recommendation. See them as tools in a toolkit, not a question of what is best. For places where you have SQL statements as primary do SQL or PLPGSQL functions. For places where you are manipulating values (parsing strings for example) use something else (I usually use pl/perl for string

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I am not doubting the efficacy of stored procs, just wondering which language is better. From the sound of it string manupilation is slow in PL-PGSQL but looking at my procs there does seem to be a lot of string manipulation going on so maybe I better do some tests. On Thu, Dec 29, 2016 at 3:02