FAQ
Dear Postgres users,
from last few months I am reading and searching for can postgresql used as
application server? As postgresql supports many languages like pl/perl,
pl/python etc, supports dblink like functions to connect to other postgresql
servers and now features are in development to use external data. Postgresql
works well on many operating systems and has a stable and good quality code.
As many users are using plpython or plperl to work on many types of data and
to implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web
application in other servers.
4. data and logic/processing can be separated. One postgresql can be used as
application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

Search Discussions

  • David Johnston at Aug 13, 2011 at 5:12 pm

    On Aug 13, 2011, at 11:57, c k wrote:

    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used as application server? As postgresql supports many languages like pl/perl, pl/python etc, supports dblink like functions to connect to other postgresql servers and now features are in development to use external data. Postgresql works well on many operating systems and has a stable and good quality code. As many users are using plpython or plperl to work on many types of data and to implement logic that can be useful in web application management.
    So i am thinking if I can use postgresql as web application server.
    Few points that supports this opinion:
    1. multiple languages support for stored procs and triggers.
    2. can connect to other postgresql servers or databases
    3. it is easy to manage stored procs or triggers than managing web application in other servers.
    4. data and logic/processing can be separated. One postgresql can be used as application server and another as database.
    5. stable, mature and open codebase.

    I request to users and developers give your suggestions and opinions.
    Waiting for your replies.

    Thanks and regards,

    Chaitanya Kulkarni
    Code yourself a nice "hello world" application that can accessed by a web browser and outputs in HTML using only PostgreSQL. IF you can actually do that simple task you will then be in a better position to decide if such an architecture is worth expanding upon.

    The better question to ask is why wouldn't you want to use something like Tomcat or Apache+Programming Language?

    David J.
  • Gavin Flower at Aug 13, 2011 at 10:06 pm

    On 14/08/11 05:12, David Johnston wrote:
    On Aug 13, 2011, at 11:57, c kwrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used as application server? As postgresql supports many languages like pl/perl, pl/python etc, supports dblink like functions to connect to other postgresql servers and now features are in development to use external data. Postgresql works well on many operating systems and has a stable and good quality code. As many users are using plpython or plperl to work on many types of data and to implement logic that can be useful in web application management.
    So i am thinking if I can use postgresql as web application server.
    Few points that supports this opinion:
    1. multiple languages support for stored procs and triggers.
    2. can connect to other postgresql servers or databases
    3. it is easy to manage stored procs or triggers than managing web application in other servers.
    4. data and logic/processing can be separated. One postgresql can be used as application server and another as database.
    5. stable, mature and open codebase.

    I request to users and developers give your suggestions and opinions.
    Waiting for your replies.

    Thanks and regards,

    Chaitanya Kulkarni
    Code yourself a nice "hello world" application that can accessed by a web browser and outputs in HTML using only PostgreSQL. IF you can actually do that simple task you will then be in a better position to decide if such an architecture is worth expanding upon.

    The better question to ask is why wouldn't you want to use something like Tomcat or Apache+Programming Language?

    David J.

    Hi,

    I see from other posts that you are using Python (A lovely language, but
    one that does not scale well for my own purposes -- unfortunately) so
    this is most likely not relevant to your current situation.

    However, for those people who would consider Java, then consider using
    JBoss 7, it is very fast and will support very sophisticated web front
    ends while allowing very powerful logic written in Java, and can easily
    be configured to use PostgreSQL.

    The combination of JBoss http://www.jboss.org/overview.html and JEE
    (Java Enterprise Edition), is well suited to large projects that can
    benefit from multi-core processors and massive amounts of RAM. My own
    humble development machine has a mere 4 cores and 8GB, but production
    machines can easily use a terrabyte of memory and many more cores.
    Eclipse is an IDE that is well suited for developing applications in
    Java, and can be configured to develop applications for JBoss.


    Cheers,
    Gavin

    I have written a script to install JBoss 7 (a lot easier than for JBoss
    4 and earlier!) and convert it to use PostgreSQL 9.1 beta3. This I am
    using to revise my JEE knowledge and get to grips with version 6 of JEE.
  • Sad at Aug 13, 2011 at 5:24 pm

    c k wrote:

    from last few months I am reading and searching for can postgresql
    used as application server?
    So i am thinking if I can use postgresql as web application server.

    I request to users and developers give your suggestions and opinions.
    Waiting for your replies.
    Yes! Yes And Yes, my friend!
    You should!

    i am using postgresql exactly as application server.
    (stored procedures in PLpgsql)
    at http://platzcart.com i managed to minimize server side scripting:
    Stored procedures run entire "business logic" + PHP envelop results into
    HTML code.

    and in the next project (yet under construction)
    i managed to ELIMINATE server side scripting at all
    (except captcha server, which is standalone (written in perl))
    PLpgsql "business logic" again + client side JS is building presentation.

    most important point to support you is
    *This is the originally intended usage of a DBMS*

    All the troubles, all the NoSQL whining caused by unnecessary middleware.

    P.S.
    i have held a report to NLUUG Spring Conference 2011
    about platzcart.com implementation
    it is located at http://platzcart.com/lib/platzcart.pdf
    it clearly describes advantages of using postgresql as an application
    server.
    and i am sure it explains the good practice (maybe the best one)

    P.P.S.
    now i am using nginx_http_postgres_module
  • C k at Aug 13, 2011 at 6:37 pm
    Yes, I know that I can not create a simple web application using only
    postgresql because we need a web server to server the html content. I not
    going to add web server functionalities to postgresql, but just want to use
    existing features. I would like to generate content dynamically. I want
    minimum developers to be required, simple and powerful security and
    administration, and most importantly ability to respond to changes.
    As sad@bestmx.ru replied above, I want to go through this way at least to
    test the application. What I am going to do is
    use python for a simple test web server.
    Use xml to define static content for each page.
    Use posgtresql to generate dynamic content using stored procs.
    static content of the page and dynamic content both will make final web
    page.
    Use javascript for client UI.

    For my application the most important part is generating dynamic content.
    Scaling, concurrency etc are not the issues at this time. We have to adopt
    govt. rules and change the business logic accordingly and it must be fast
    enough to save penalties.

    Regards,

    Chaitanya Kulkarni
    On Sat, Aug 13, 2011 at 10:54 PM, sad@bestmx.ru wrote:

    c k wrote:

    from last few months I am reading and searching for can postgresql
    used as application server?
    So i am thinking if I can use postgresql as web application server.

    I request to users and developers give your suggestions and opinions.
    Waiting for your replies.
    Yes! Yes And Yes, my friend!
    You should!

    i am using postgresql exactly as application server.
    (stored procedures in PLpgsql)
    at http://platzcart.com i managed to minimize server side scripting:
    Stored procedures run entire "business logic" + PHP envelop results into
    HTML code.

    and in the next project (yet under construction)
    i managed to ELIMINATE server side scripting at all
    (except captcha server, which is standalone (written in perl))
    PLpgsql "business logic" again + client side JS is building presentation.

    most important point to support you is
    *This is the originally intended usage of a DBMS*

    All the troubles, all the NoSQL whining caused by unnecessary middleware.

    P.S.
    i have held a report to NLUUG Spring Conference 2011
    about platzcart.com implementation
    it is located at http://platzcart.com/lib/**platzcart.pdf<http://platzcart.com/lib/platzcart.pdf>
    it clearly describes advantages of using postgresql as an application
    server.
    and i am sure it explains the good practice (maybe the best one)

    P.P.S.
    now i am using nginx_http_postgres_module




    --
    Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin>
  • Sad at Aug 13, 2011 at 6:44 pm

    c k wrote:
    Yes, I know that I can not create a simple web application using only
    postgresql because we need a web server to server the html content.
    u r wrong.
    u CAN!

    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.
  • C k at Aug 13, 2011 at 6:45 pm
    can u please give me it's link.
    I found ngx_postgres module.

    Chaitanya Kulkarni
    On Sun, Aug 14, 2011 at 12:14 AM, sad@bestmx.ru wrote:

    c k wrote:
    Yes, I know that I can not create a simple web application using only
    postgresql because we need a web server to server the html content.
    u r wrong.
    u CAN!

    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.
  • Sad at Aug 13, 2011 at 6:49 pm

    can u please give me it's link.
    I found ngx_postgres module.
    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.
    http://wiki.nginx.org/3rdPartyModules
    http://labs.frickle.com/nginx_ngx_postgres/
  • Uwe Schroeder at Aug 14, 2011 at 8:09 am

    can u please give me it's link.
    I found ngx_postgres module.
    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.
    http://wiki.nginx.org/3rdPartyModules
    http://labs.frickle.com/nginx_ngx_postgres/
    Which is still a webserver on top of postgresql. Sure, you're coding more in
    postgresql, but by the end of the day, a database is a database and a
    webserver is a webserver. Sure, you can use a kitchen knife to chop firewood,
    but wouldn't a tool made for the job be better at it?
    IMHO you trade flexibility and security with "simplification" Personally I'd
    never ever hook my database directly to the internet - for me that's like
    writing your PIN on your banking card and leave it next to the ATM :-)
  • Sad at Aug 14, 2011 at 12:34 pm
    On Aug 14 2011, Uwe Schroeder wrote:
    can u please give me it's link.
    I found ngx_postgres module.
    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.
    http://wiki.nginx.org/3rdPartyModules
    http://labs.frickle.com/nginx_ngx_postgres/
    Personally I'd never
    ever hook my database directly to the internet - for me that's like
    writing your PIN on your banking card and leave it next to the ATM :-)

    DSTABASE IS THE BEST TOOL TO AGGANGE AN ACCESS CONTROL SCHEME
  • C k at Aug 14, 2011 at 5:59 pm
    Thanks for everyone.
    I will give reply after some experiments.
    Regards,

    Chaitanya Kulkarni
    On Sun, Aug 14, 2011 at 6:04 PM, wrote:

    On Aug 14 2011, Uwe Schroeder wrote:

    can u please give me it's link.
    I found ngx_postgres module.
    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.
    http://wiki.nginx.org/**3rdPartyModules<http://wiki.nginx.org/3rdPartyModules>
    http://labs.frickle.com/nginx_**ngx_postgres/<http://labs.frickle.com/nginx_ngx_postgres/>
    Personally I'd never ever hook my database directly to the internet - for
    me that's like writing your PIN on your banking card and leave it next to
    the ATM :-)

    DSTABASE IS THE BEST TOOL TO AGGANGE AN ACCESS CONTROL SCHEME


    --
    Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin>
  • John DeSoi at Aug 16, 2011 at 1:24 pm

    On Aug 13, 2011, at 2:44 PM, sad@bestmx.ru wrote:

    c k wrote:
    Yes, I know that I can not create a simple web application using only postgresql because we need a web server to server the html content.
    u r wrong.
    u CAN!

    there is nginx_htttp_postgresql_module
    exactly to connect webserver directly to postgresql
    and to OUTPUT query result to a browser.

    You could also use something like node.js which allows you to connect Javascript directly to Postgres using the front end/back end protocol.

    With something like this, you could talk directly to Postgres from the browser:

    http://ajaxian.com/archives/tcpsocket-sockets-in-the-browser

    http://www.postgresql.org/docs/9.0/interactive/protocol.html

    I'm not saying this is the right approach, but if that is what you are really after...



    John DeSoi, Ph.D.
  • Chris Travers at Aug 13, 2011 at 9:49 pm
    Hi CK:

    First, it depends on what you mean by an application server. There
    are people who in fact do very similar things with PostgreSQL,
    essentially having it take on roles traditionally served by
    middleware.
    On Sat, Aug 13, 2011 at 8:57 AM, c k wrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used as
    application server? Kinda.
    As postgresql supports many languages like pl/perl,
    pl/python etc, supports dblink like functions to connect to other postgresql
    servers and now features are in development to use external data. Postgresql
    works well on many operating systems and has a stable and good quality code.
    As many users are using plpython or plperl to work on many types of data and
    to implement logic that can be useful in web application management.
    So i am thinking if I can use postgresql as web application server.
    Few points that supports this opinion:
    1. multiple languages support for stored procs and triggers.
    2. can connect to other postgresql servers or databases
    Best used sparingly.
    3. it is easy to manage stored procs or triggers than managing web
    application in other servers.
    I agree, but....
    4. data and logic/processing can be separated. One postgresql can be used as
    application server and another as database.
    Bad idea. You don't really gain anything except complexity and
    headache by trying to separate like this.
    5. stable, mature and open codebase.
    Probably better than give some general feedback I will share how we do
    this with LedgerSMB. Some of our deployments using this approach are
    decent-sized.

    1: Thus far all our stored procs are all in SQL and PLPGSQL. We do
    not currently use PL/Perl or PL/Python, or any other stored procedure
    language.

    2: We pay close attention to semantics in order to ensure, to the
    extent possible, that catalog data allows for discovery of stored
    procedure interfaces. We then do a lot of query building in the
    "client" app (a web app) to discover these interfaces and call them
    properly. We do not include SQL code in most perl modules. Instead
    virtually all calls go through a generic discovery interface.

    3: All logic required to store, retrieve, or present (to the
    application) the data goes through these stored procedures.

    4: The web app is a fairly thin Perl glue that binds HTML templates
    written in Template Toolkit to these stored procedures. It's rare to
    find Perl functions more than about 5-10 lines long and when that
    happens most of the logic is usually taking data and putting it into a
    tabular structure for a report template.

    This could be done with a desktop app as well. The primary business
    logic and security is thus reusable between applications, making
    PostgreSQL essentially a middleware server.

    Observations from my experience: Design is critical and difficult.
    There aren't a lot of people doing this sort of thing and so there is
    a LOT of trial and error. Also, it is quite possible to do a heck of
    a lot in this area in SQL and PLPGSQL. Focus on writing unified,
    maintainable queries and semantically meaningful interfaces (argument
    names, function names, etc).

    Best Wishes,
    Chris Travers
  • Scott Marlowe at Aug 15, 2011 at 3:54 pm

    On Sat, Aug 13, 2011 at 9:57 AM, c k wrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used as
    application server? As postgresql supports many languages like pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
  • Benjamin Krajmalnik at Aug 15, 2011 at 4:58 pm
    Further to Scott's comment, we are running our application platform on
    nginx/php (using php-fpm).
    It scales very well and it is extremely fast.
    When running under Apache, we had to constantly restart the apache
    service because it could not handle the load (at 150 concurrent users
    hitting the app it would pretty much grind to a standstill). We moved
    to Nginx, have quadrupled the number of devices which are accessing our
    platform, and the number of php processes which are actually running is
    < 10. I had allocated a dynamic pool whereby we have 35 processes ready
    to accept requests at all times and which can dynamically scale up, but
    it does not come close to needing to use all of the existing processes.
    Everything is running under FreeBSD 8.1 amd64.

    As Scott mentioned, wrapping it into a real language is much better -
    the extent of what you will be able to do within the module will be
    severely limited vs a real language.

    -----Original Message-----
    From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
    owner@postgresql.org] On Behalf Of Scott Marlowe
    Sent: Monday, August 15, 2011 9:54 AM
    To: c k
    Cc: pgsql-general@postgresql.org; pgsql-admin
    Subject: Re: [ADMIN] Using Postgresql as application server
    On Sat, Aug 13, 2011 at 9:57 AM, c k wrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used as
    application server? As postgresql supports many languages like
    pl/perl,

    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.

    --
    Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-admin
  • Sad at Aug 15, 2011 at 5:33 pm

    Scott Marlowe пишет:
    On Sat, Aug 13, 2011 at 9:57 AM, c kwrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used as
    application server? As postgresql supports many languages like pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
    BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
  • Scott Marlowe at Aug 15, 2011 at 10:46 pm

    On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru wrote:
    Scott Marlowe пишет:
    On Sat, Aug 13, 2011 at 9:57 AM, c kwrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used
    as
    application server? As postgresql supports many languages like pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
    BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
    But I can throw 1,000 cores at a large load with php. Much harder to
    do with plpgsql.
  • Sad at Aug 16, 2011 at 7:46 am

    Scott Marlowe пишет:
    On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ruwrote:
    Scott Marlowe пишет:
    On Sat, Aug 13, 2011 at 9:57 AM, c kwrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used
    as
    application server? As postgresql supports many languages like pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
    BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
    But I can throw 1,000 cores at a large load with php. Much harder to
    do with plpgsql.
    and?
    all of them would inevitably connect the same postgresql

    the solution is outside the scope.
    ...a web-server page cache is one element of a solution
  • Scott Marlowe at Aug 16, 2011 at 3:44 pm

    2011/8/16 sad@bestmx.ru <sad@bestmx.ru>:
    Scott Marlowe пишет:
    On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ruwrote:
    Scott Marlowe пишет:
    On Sat, Aug 13, 2011 at 9:57 AM, c k<shreeseva.learning@gmail.com>
    wrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used
    as
    application server? As postgresql supports many languages like pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
    BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
    But I can throw 1,000 cores at a large load with php.  Much harder to
    do with plpgsql.
    and?
    all of them would inevitably connect the same postgresql
    And they'd each need postgresql to do a concat? I'd hope nobody was
    dumb enough to program the app layer to do something like that. PG
    might make a decent app server, but there's no way you could scale it
    to millions of users like you could a farm of app servers.
  • Evan Rempel at Aug 16, 2011 at 4:49 pm
    Technically it can be done, but just because we can do something does not
    mean we should do something. Having said that...

    We have been using a middleware product that shall remain nameless,
    that goes against a large commercial database that shall also remain nameless.
    The middleware has been migrating to a more and more database based code
    set, and as an administrator of such a system I can state that this is
    awful.

    Getting appropriate logging out of the application logic for both auditing purposes
    and trouble shooting is near impossible. Performance is nearly impossible to tune as
    everything runs inside the database. One giant process chewing up cores of CPU power.


    Security is near impossible to manage as well. Again, almost everything needs to run as
    the same user. The database is now making calls to generate pdf objects and make
    printing calls.

    None of the traditional tools can be used to integrate the application into the enterprise.
    The load balancer needs to add x-forwarded headers to http requests, but the
    custom http code can't handle that, so all web access appears to come from the load
    balancer. This violates regulatory requirements. Log file formats are not standard
    since none of the code is standard, this means that none of the event correlation
    tools can be used for intrusion detection etc.

    It is just a nightmare. The previous version that had real middleware and real database
    servers was much better. The workloads were different so each server could be tuned for
    what it was doing. We were able to purchase hardware appropriate to the task. Big RAM
    for database, big CPU for middleware. Overall it was cheaper.

    Just my $0.02

    Evan

    Scott Marlowe wrote:
    2011/8/16 sad@bestmx.ru <sad@bestmx.ru>:
    Scott Marlowe ÐÉÛÅÔ:
    On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru<sad@bestmx.ru> šwrote:
    Scott Marlowe ÐÉÛÅÔ:
    On Sat, Aug 13, 2011 at 9:57 AM, c k<shreeseva.learning@gmail.com>
    šwrote:
    Dear Postgres users,
    from last few months I am reading and searching for can postgresql used
    as
    application server? As postgresql supports many languages like pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
    BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
    But I can throw 1,000 cores at a large load with php. šMuch harder to
    do with plpgsql.
    and?
    all of them would inevitably connect the same postgresql
    And they'd each need postgresql to do a concat? I'd hope nobody was
    dumb enough to program the app layer to do something like that. PG
    might make a decent app server, but there's no way you could scale it
    to millions of users like you could a farm of app servers.
  • Sad at Aug 16, 2011 at 4:55 pm

    Evan Rempel пишет:
    Security is near impossible to manage as well. Again, almost
    everything needs to run as
    the same user.
    throw your nameless DMS away, or fire the architect.
    Scott Marlowe ÐÉÛÅÔ:
    On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru<sad@bestmx.ru> šwrote:
    Scott Marlowe ÐÉÛÅÔ:
    On Sat, Aug 13, 2011 at 9:57 AM, c k<shreeseva.learning@gmail.com>
    šwrote:
    Dear Postgres users,
    from last few months I am reading and searching for can
    postgresql used
    as
    application server? As postgresql supports many languages like
    pl/perl,
    Besides the previously mentioned nginx module there's apache's mod
    libpq http://asmith.id.au/mod_libpq.html

    But I'd stick to a language to wrap stuff in like php etc.
    BTW, string concatenation in postgresql (plpgsql) is FASTER than
    in PHP
    But I can throw 1,000 cores at a large load with php. šMuch harder to
    do with plpgsql.
    and?
    all of them would inevitably connect the same postgresql
    And they'd each need postgresql to do a concat? I'd hope nobody was
    dumb enough to program the app layer to do something like that. PG
    might make a decent app server, but there's no way you could scale it
    to millions of users like you could a farm of app servers.
    2011/8/16 sad@bestmx.ru <sad@bestmx.ru>:
  • Chris Travers at Aug 16, 2011 at 5:24 pm
    I can't let this slide :-D
    On Tue, Aug 16, 2011 at 9:27 AM, Evan Rempel wrote:
    Technically it can be done, but just because we can do something does not
    mean we should do something. Having said that...

    We have been using a middleware product that shall remain nameless,
    that goes against a large commercial database that shall also remain
    nameless.
    The middleware has been migrating to a more and more database based code
    set, and as an administrator of such a system I can state that this is
    awful.
    From your description below, it truly sounds awful. However, this
    strikes me as being an issue of *which* logic is moved into the
    database instead of *that* logic is being moved into the database.
    Getting appropriate logging out of the application logic for both auditing
    purposes
    and trouble shooting is near impossible. Performance is nearly impossible to
    tune as
    everything runs inside the database. One giant process chewing up cores of
    CPU power.
    LedgerSMB has been moving in the direction of more logic in the
    database because we have found the opposite. Troubleshooting is
    easier, code maintenance is easier, performance is easier to
    troubleshoot and manage, and security is far more manageable. Now,
    granted we are retrofitting security onto a codebase which had none
    when we forked, so that is a difference..... We have eliminated a
    much larger number of bottlenecks by going this way than we have run
    into. Again the issue is *which* logic goes into the db, and that's
    an absolutely key question when running middle tiers in the dbms.

    Security is near impossible to manage as well. Again, almost everything
    needs to run as
    the same user. The database is now making calls to generate pdf objects and
    make
    printing calls.
    Ouch.... I guess I could kinda see the PDF generation calls (I'd still
    prefer a queue and transform approach), but not the printing calls.
    And if you can't move security into the database, then you have a kind
    of major problem: you aren't *really* generating a consistent and
    consistently enforced API in this way, and so you can't get to the
    roles a middleware solution gives you.

    As long as you still need the middleware, then the question really
    becomes, what logic needs to be centralized in the db and what logic
    is specific to each middleware application? What do you get from
    putting each thing in the database? My recommendation is to review
    that. If it is a single app db, then use stored procs as essentially
    named queries. If it is possible, move the printing calls into a
    separate process and have it signalled by the db app on database
    commit.

    But it really sounds like an unmaintainable mess. IME, however, that
    is avoidable while still placing the RDBMS in the center of the
    logic-complete application server environment.
    None of the traditional tools can be used to integrate the application into
    the enterprise.
    The load balancer needs to add x-forwarded headers to http requests, but the
    custom http code can't handle that, so all web access appears to come from
    the load
    balancer. This violates regulatory requirements. Log file formats are not
    standard
    since none of the code is standard, this means that none of the event
    correlation
    tools can be used for intrusion detection etc.
    Ouch..... I second the suggestion that the architecture here lacks
    the separation of concerns approach necessary to make this work, and
    that either the software you are using is the problem or the architect
    is. However, it doesn't tell me that the approach of using the RDBMS
    as the entry point into an application server environment is
    necessarily a bad thing.

    Best Wishes,
    Chris Travers
  • Craig James at Aug 16, 2011 at 7:25 pm

    On 8/16/11 10:24 AM, Chris Travers wrote:
    I can't let this slide :-D

    On Tue, Aug 16, 2011 at 9:27 AM, Evan Rempelwrote:
    Technically it can be done, but just because we can do something does not
    mean we should do something. Having said that...

    We have been using a middleware product that shall remain nameless,
    that goes against a large commercial database that shall also remain
    nameless.
    The middleware has been migrating to a more and more database based code
    set, and as an administrator of such a system I can state that this is
    awful.
    From your description below, it truly sounds awful. However, this
    strikes me as being an issue of *which* logic is moved into the
    database instead of *that* logic is being moved into the database.
    In other words, it's just a typical engineering problem: pick the right tools for the job. Just because you're a good mason doesn't mean you should build a boat out of cement. You can do it, but aluminum, steel, fiberglass and wood are better choices. Just because you are really good at database programming doesn't mean you should turn a database server into a web server. You can do it, but it will be an expensive, suboptimal, and unmaintainable system.

    There are many opinions about the exact right balance of where functionality should be implemented, but extreme solutions are almost never optimal.

    Craig
    Getting appropriate logging out of the application logic for both auditing
    purposes
    and trouble shooting is near impossible. Performance is nearly impossible to
    tune as
    everything runs inside the database. One giant process chewing up cores of
    CPU power.
    LedgerSMB has been moving in the direction of more logic in the
    database because we have found the opposite. Troubleshooting is
    easier, code maintenance is easier, performance is easier to
    troubleshoot and manage, and security is far more manageable. Now,
    granted we are retrofitting security onto a codebase which had none
    when we forked, so that is a difference..... We have eliminated a
    much larger number of bottlenecks by going this way than we have run
    into. Again the issue is *which* logic goes into the db, and that's
    an absolutely key question when running middle tiers in the dbms.
    Security is near impossible to manage as well. Again, almost everything
    needs to run as
    the same user. The database is now making calls to generate pdf objects and
    make
    printing calls.
    Ouch.... I guess I could kinda see the PDF generation calls (I'd still
    prefer a queue and transform approach), but not the printing calls.
    And if you can't move security into the database, then you have a kind
    of major problem: you aren't *really* generating a consistent and
    consistently enforced API in this way, and so you can't get to the
    roles a middleware solution gives you.

    As long as you still need the middleware, then the question really
    becomes, what logic needs to be centralized in the db and what logic
    is specific to each middleware application? What do you get from
    putting each thing in the database? My recommendation is to review
    that. If it is a single app db, then use stored procs as essentially
    named queries. If it is possible, move the printing calls into a
    separate process and have it signalled by the db app on database
    commit.

    But it really sounds like an unmaintainable mess. IME, however, that
    is avoidable while still placing the RDBMS in the center of the
    logic-complete application server environment.
    None of the traditional tools can be used to integrate the application into
    the enterprise.
    The load balancer needs to add x-forwarded headers to http requests, but the
    custom http code can't handle that, so all web access appears to come from
    the load
    balancer. This violates regulatory requirements. Log file formats are not
    standard
    since none of the code is standard, this means that none of the event
    correlation
    tools can be used for intrusion detection etc.
    Ouch..... I second the suggestion that the architecture here lacks
    the separation of concerns approach necessary to make this work, and
    that either the software you are using is the problem or the architect
    is. However, it doesn't tell me that the approach of using the RDBMS
    as the entry point into an application server environment is
    necessarily a bad thing.

    Best Wishes,
    Chris Travers
  • Merlin Moncure at Aug 16, 2011 at 6:25 pm

    On Tue, Aug 16, 2011 at 11:27 AM, Evan Rempel wrote:
    Technically it can be done, but just because we can do something does not
    mean we should do something. Having said that...

    We have been using a middleware product that shall remain nameless,
    that goes against a large commercial database that shall also remain
    nameless.
    The middleware has been migrating to a more and more database based code
    set, and as an administrator of such a system I can state that this is
    awful.

    Getting appropriate logging out of the application logic for both auditing
    purposes
    and trouble shooting is near impossible. Performance is nearly impossible to
    tune as
    everything runs inside the database. One giant process chewing up cores of
    CPU power.


    Security is near impossible to manage as well. Again, almost everything
    needs to run as
    the same user. The database is now making calls to generate pdf objects and
    make
    printing calls.

    None of the traditional tools can be used to integrate the application into
    the enterprise.
    The load balancer needs to add x-forwarded headers to http requests, but the
    custom http code can't handle that, so all web access appears to come from
    the load
    balancer. This violates regulatory requirements. Log file formats are not
    standard
    since none of the code is standard, this means that none of the event
    correlation
    tools can be used for intrusion detection etc.

    It is just a nightmare. The previous version that had real middleware and
    real database
    servers was much better. The workloads were different so each server could
    be tuned for
    what it was doing. We were able to purchase hardware appropriate to the
    task. Big RAM
    for database, big CPU for middleware. Overall it was cheaper.
    very few of those complaints would apply to postgres -- the database
    does not run in a single process (thank goodness for that!), logging
    via the various pls is trivially done and effective, etc. also if
    you're serving http it does make sense to wrap postgres with a thin
    http server (lighttpd, node.js, nginx are all good choices). most of
    the rest of your gripes seem to apply more to the specific middleware
    implementation vs a general appraisal of the technique. it's trivial
    to implement database side security systems and many people do so.

    one point that is getting lost in all this that if you are using a
    database for an application server, this does not mean it's the same
    database as your main database or even on the same machine -- you can
    still keep traditional separation of roles and use something like
    dblink to transfer data. regarding the use of postgres functions for
    things like reports and pdf generation, I see absolutely nothing wrong
    with doing this...although plpgsql is not a very good
    choice...pl/python or pl/java would be a better way to go.

    merlin
  • Scott Marlowe at Aug 16, 2011 at 6:33 pm

    On Tue, Aug 16, 2011 at 12:25 PM, Merlin Moncure wrote:
    one point that is getting lost in all this that if you are using a
    database for an application server, this does not mean it's the same
    database as your main database or even on the same machine -- you can
    still keep traditional separation of roles and use something like
    dblink to transfer data.  regarding the use of postgres functions for
    I'm thining pl/proxy would be pretty amazing here. I still prefer
    using another layer in another language for an app server, but now
    that you've got me thinking a bit more out of the box, pl/proxy would
    increase your ability to scale quite a lot.
  • C k at Aug 16, 2011 at 6:38 pm
    I want to use the postgresql for exactly the same use. I want to keep my
    database separate and use another postgresql machine just as application
    server. Even for load balancing and scaling we can use many techniques mixed
    to get the work done.

    Chaitanya Kulkarni
    On Tue, Aug 16, 2011 at 11:55 PM, Merlin Moncure wrote:
    On Tue, Aug 16, 2011 at 11:27 AM, Evan Rempel wrote:
    Technically it can be done, but just because we can do something does not
    mean we should do something. Having said that...

    We have been using a middleware product that shall remain nameless,
    that goes against a large commercial database that shall also remain
    nameless.
    The middleware has been migrating to a more and more database based code
    set, and as an administrator of such a system I can state that this is
    awful.

    Getting appropriate logging out of the application logic for both auditing
    purposes
    and trouble shooting is near impossible. Performance is nearly impossible to
    tune as
    everything runs inside the database. One giant process chewing up cores of
    CPU power.


    Security is near impossible to manage as well. Again, almost everything
    needs to run as
    the same user. The database is now making calls to generate pdf objects and
    make
    printing calls.

    None of the traditional tools can be used to integrate the application into
    the enterprise.
    The load balancer needs to add x-forwarded headers to http requests, but the
    custom http code can't handle that, so all web access appears to come from
    the load
    balancer. This violates regulatory requirements. Log file formats are not
    standard
    since none of the code is standard, this means that none of the event
    correlation
    tools can be used for intrusion detection etc.

    It is just a nightmare. The previous version that had real middleware and
    real database
    servers was much better. The workloads were different so each server could
    be tuned for
    what it was doing. We were able to purchase hardware appropriate to the
    task. Big RAM
    for database, big CPU for middleware. Overall it was cheaper.
    very few of those complaints would apply to postgres -- the database
    does not run in a single process (thank goodness for that!), logging
    via the various pls is trivially done and effective, etc. also if
    you're serving http it does make sense to wrap postgres with a thin
    http server (lighttpd, node.js, nginx are all good choices). most of
    the rest of your gripes seem to apply more to the specific middleware
    implementation vs a general appraisal of the technique. it's trivial
    to implement database side security systems and many people do so.

    one point that is getting lost in all this that if you are using a
    database for an application server, this does not mean it's the same
    database as your main database or even on the same machine -- you can
    still keep traditional separation of roles and use something like
    dblink to transfer data. regarding the use of postgres functions for
    things like reports and pdf generation, I see absolutely nothing wrong
    with doing this...although plpgsql is not a very good
    choice...pl/python or pl/java would be a better way to go.

    merlin

    --
    Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-admin

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
categoriespostgresql
postedAug 13, '11 at 3:57p
activeAug 16, '11 at 7:25p
posts26
users12
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase