4 ms·

Largely for driver coverage. Postgres drivers are available in tons of languages so by being wire compatible you can skip all that work.

traceroute66 5y ago
> Postgres drivers are available in tons of languages So are SQLite drivers (and MySQL and all the other top-10/top-20 databases). Why add un-necessary abstraction ? Why add an un-necessary layer with bugs and edge-cases that you will subsequently waste hours of your life debugging ? Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. > What SQLite3 driver out there allows you to talk over a network connection to a remote database? (Replied here because I can't reply to your point below) Maybe that's because, as per the SQLite website[1], SQLite was never intended to be used as a client/server database !!! I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. There are many excellent client/server databases out there (including the postgres referred to here). If you need that functionality you should be using the right tool for the job, there's no need to shoehorn SQLite into it. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- ansible 5y ago
  > Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. What SQLite3 driver out there allows you to talk over a network connection to a remote database? Not any one that I've ever used.
- ripley12 5y ago
  > I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. It depends a lot on what you're using the remote connectivity for. I think I have a pretty good understanding of SQLite's strengths+capabilities, enough to understand when it can replace a traditional RDBMS for a back-end service. I wouldn't build a solution where multiple clients are connecting to the DB remotely - but sometimes I want the convenience of connecting to the DB remotely for observability/troubleshooting. This is something that's trivial with traditional RDBMSs, and a bit painful with SQLite. Exposing the SQLite DB over the network for occasional interactive use seems quite useful to me.
  - nerdponx 5y ago
    It's also useful if you initially built an application SQLite and are transitioning to Postgres.
ansible 5y ago
Yes. I don't see the harm in the PostgreSQL wire protocol becoming a defacto standard for sending SQL statements over a network connection and getting results back. SQL itself is only a vague standard these days, as almost every single database has its own dialect. The only ones that are the same are MariaDB and MySQL... and that doesn't count. [1] And I don't see this situation changing in the medium term, or ever, really. [1] ... because the first is the fork of the second.
- unilynx 5y ago
  This. I’ll take the pg protocol over OCI and especially ODBC any day.
- clhodapp 5y ago
  There's a small amount of harm because Postgres doesn't currently support request multiplexing and so its wire protocol doesn't have any facility for it. This pushes other databases to inherit one of Postgres's few true weak points.
  - ansible 5y ago
    I take it that opening multiple database connections to the same server is not usually viable? I realize there are usually limits to open sockets / file descriptors and such, and that multiple connections will consume more memory on each end. Are there problems beyond that?
    - clhodapp 5y ago
      It's usually (but not always) viable and it's ultimately what one ends up doing. You're right about the drawbacks. One additional drawback is the setup time for opening new connections (network round-trip, re-authenticating, etc.).
  - amitlan 5y ago
    If I’m not wrong, I think this situation has changed with the latest Postgres release: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....

traceroute66 5y ago

> Postgres drivers are available in tons of languages So are SQLite drivers (and MySQL and all the other top-10/top-20 databases). Why add un-necessary abstraction ? Why add an un-necessary layer with bugs and edge-cases that you will subsequently waste hours of your life debugging ? Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. > What SQLite3 driver out there allows you to talk over a network connection to a remote database? (Replied here because I can't reply to your point below) Maybe that's because, as per the SQLite website[1], SQLite was never intended to be used as a client/server database !!! I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. There are many excellent client/server databases out there (including the postgres referred to here). If you need that functionality you should be using the right tool for the job, there's no need to shoehorn SQLite into it. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

ansible 5y ago
> Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. What SQLite3 driver out there allows you to talk over a network connection to a remote database? Not any one that I've ever used.
ripley12 5y ago
> I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. It depends a lot on what you're using the remote connectivity for. I think I have a pretty good understanding of SQLite's strengths+capabilities, enough to understand when it can replace a traditional RDBMS for a back-end service. I wouldn't build a solution where multiple clients are connecting to the DB remotely - but sometimes I want the convenience of connecting to the DB remotely for observability/troubleshooting. This is something that's trivial with traditional RDBMSs, and a bit painful with SQLite. Exposing the SQLite DB over the network for occasional interactive use seems quite useful to me.
- nerdponx 5y ago
  It's also useful if you initially built an application SQLite and are transitioning to Postgres.

ansible 5y ago

Yes. I don't see the harm in the PostgreSQL wire protocol becoming a defacto standard for sending SQL statements over a network connection and getting results back. SQL itself is only a vague standard these days, as almost every single database has its own dialect. The only ones that are the same are MariaDB and MySQL... and that doesn't count. [1] And I don't see this situation changing in the medium term, or ever, really. [1] ... because the first is the fork of the second.

unilynx 5y ago
This. I’ll take the pg protocol over OCI and especially ODBC any day.
clhodapp 5y ago
There's a small amount of harm because Postgres doesn't currently support request multiplexing and so its wire protocol doesn't have any facility for it. This pushes other databases to inherit one of Postgres's few true weak points.
- ansible 5y ago
  I take it that opening multiple database connections to the same server is not usually viable? I realize there are usually limits to open sockets / file descriptors and such, and that multiple connections will consume more memory on each end. Are there problems beyond that?
  - clhodapp 5y ago
    It's usually (but not always) viable and it's ultimately what one ends up doing. You're right about the drawbacks. One additional drawback is the setup time for opening new connections (network round-trip, re-authenticating, etc.).
- amitlan 5y ago
  If I’m not wrong, I think this situation has changed with the latest Postgres release: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....