6 ms·

> Developing against a relational database requires devs to watch out for "N+1" query patterns, where a query leads to a loop that leads to more queries. N+1 qu

> Developing against a relational database requires devs to watch out for "N+1" query patterns, where a query leads to a loop that leads to more queries. N+1 queries against Postgres and MySQL can be lethal to performance. Not so much for SQLite. This is misleading AFAICT. The article(s) is actually comparing remote RDBMS to local RDBMS, not Postgres to SQLite. Postgres can also be served over a UNIX socket, removing the individual query overhead due to TCP roundtrip. SQLite is a great technology, but keep in mind that you can also deploy Postgres right next to your app as well. If your app is something like a company backend that could evolve a lot and benefit from Postgres's advanced features, this may be the right choice.

tptacek 4y ago
Anyone want to chime in with the largest app they've deployed where prod Postgres was reachable over a Unix domain socket?
- pphysch 4y ago
  I would wager that the median IT shop/dept is running off a single node RDBMS, the majority of which also have the spare resources to support running the primary application (which has 1-100 DAU). No practical way to test this, of course.
- whalesalad 4y ago
  is it cheating when pgbouncer is being accessed via socket? lol
  - tptacek 4y ago
    Yes, that is cheating.
    - kjeetgill 4y ago
      I don't have any context around this: why is that cheating?
      - tptacek 4y ago
        Because the point of the Unix domain socket (in this argument) is eliminating network round trips.
        kjeetgill 4y ago
        Oh gotcha, I assumed they meant all 3 we're on one host. I'm not too familiar with the pg ecosystem.
- nucleardog 4y ago
  Used to work developing standalone kiosks and things (think along the lines of stuff you'd find at a museum as the basic type of thing I'd work on), so absolutely not the use case you're thinking of. In a number of cases, we were working with data that would really benefit from actual GIS tooling so PostGIS was kind of the natural thing to reach for. Many of these kiosks had slow, intermittent, or just straight up no internet connection available. So we just deployed PostGIS directly on the kiosk hardware. Usually little embedded industrial machines with passive cooling working with something like a generation or two out-of-date Celeron, 8GB RAM, and a small SSD. We'd load up shapefiles covering a bunch of features across over a quarter million square miles while simultaneously running Chromium in kiosk mode. I know for a fact some of those had around 1k DAU. I mean, never more than one simultaneously but we did have them! I'm sure it would have handled more just fine if it weren't for the damn laws of physics limiting the number of people that can be physically accessing the hardware at the same time. That said, we had the benefit of knowing that our user counts would never really increase and due to real-world limitations we'd never be working with a remote database because there was literally no internet available. In general I'd still say colocating your database is not an excuse to allow N+1 query patterns to slip in. They'll be fine for now and just come back to bite you in the ass when your app _does_ outgrow colocating the app/database.
- tshaddox 4y ago
  Are there significant limits to the size of an app that could be deployed alongside Postgres versus the size of an app that could be deployed alongside SQLite?
  - zie 4y ago
    No, the big difference is write speed. SQLite is limited in the amount of writes it can do at the same time(typically 1). This is generally called "concurrent writes". Last I checked SQLite can't really get past 1 write at the same time, but it can emulate concurrent writes in WAL mode such that it isn't normally a problem, for most applications. Postgres has no such limit(though there can be limits to updating a particular row/column of data concurrently, obviously). Otherwise both are generally limited to the physical resources of the machine(memory, disk, etc). Generally speaking you can scale boxes much much farther than your data size for most applications.
    - nicoburns 4y ago
      > it can emulate concurrent writes in WAL mode Seems a bit unfair to call WAL mode emulation of "true" concurrent writes as I'm pretty sure a write-ahead-log (WAL) is exactly how other databases implement multiple concurrent writes. It's just always-on rather than being opt-in.
      - zie 4y ago
        Well, but that's not really what it's doing. WAL1 mode in SQLite is different than in your typical RDBMS. WAL mode in SQLite is just getting the write path outside of the read path, but there is still only 1 write allowed at any given time. The "fake" concurrency is done by letting write transactions queue up and wait for the lock. See [here](https://sqlite.org/wal.html https://sqlite.org/wal.html) under "2.2. Concurrency" where it says: > "However, since there is only one WAL file, there can only be one writer at a time." SQLite is awesome, I'm a huge fan, but if you need to do lots and lots of writes, then SQLite is not your friend. Luckily that's a very rare application. There is a reason you never see SQLite being the end point for logs and other write-heavy applications.
- shrubble 4y ago
  It's a great way to deploy if you have the RAM and disk. Bump up the shared buffers, have a multithreaded or pooled driver for your app server so connections are not being opened and closed all the time. It really 'flies' under such a scenario. Largest app was a big analytics and drug tracking application for a small pharmaceutical company. Not sure of the size but it was very reporting heavy.
- fdr 4y ago
  Not me personally, but my understanding some Cisco products have Postgres "embedded." If they're not using unix domain sockets, they're at least using loopback. ...come to think of it, they may not be the only network device maker that does this.
- jzelinskie 4y ago
  I'm surprised no one is chiming in from the LAMP era. Maybe I was just in a bubble, but I feel like before modern cloud providers, folks deploying on VPSs tried to keep operational complexity lower by running their database on the same box until they couldn't anymore. In my experience, I just cached the living hell out of the apps to avoid the I/O from the database. Providers in those days had huge issues with noisy neighbors, so I/O performance was quite poor. [0]: https://en.wikipedia.org/wiki/LAMP_(software_bundle) https://en.wikipedia.org/wiki/LAMP_(software_bundle)
- lbhdc 4y ago
  We use managed postgres on GCP, and it is served over unix socket.
  - cbsmith 4y ago
    Kind of by definition, GCP's managed postgres is served up over the network. I'm guessing you mean "unix socket" in the sense of the socket API, not unix domain sockets.
- BeefWellington 4y ago
  Back in my consulting days one of my clients had an application with nearly all of the business logic being done in-database. The app was essentially a frontend for stored procedures galore and it sat on an absolute monster of a system for its time (~256GB of RAM, 24 or 32 Cores I think). It handled something like 100k DAU with no issue. That was for just the transactional system. They replicated data to other (similar style) systems for less mission-critical things like reporting, billing, etc. I want to be clear though, I would never recommend anyone do this. For its time it was impressive but I suspect by now it's been re-engineered away from that model, if for no other reason than its a lot of eggs in one basket.
- metafex 4y ago
  Had an instance with almost 2TB on disk in 2015, it's probably a lot more by now, if it's still running as-is. Was for analytics/data-warehousing. Though the big table was partitioned so queries were always nice and snappy. The machine had 128gb ram back then, but probably never needed that much. The applications working with the data ran on that same server, so processing latency was usually <10ms even for larger queries.
woooooo 4y ago
Isn't "n + 1" typically a degenerate case of ORM rather than a relational thing? The whole point of rdbms is you can do a join instead of iterated lookups.
- pphysch 4y ago
  Yeah most N+1 issues can be mitigated by optimizing the queries. You definitely don't need an ORM to get to N+1 hell, a for-loop in vanilla PHP will do.
  - mgkimsal 4y ago
    A for-loop in vanilla anything will do. Nothing special about PHP. Ruby, Python, Java, etc...
    - pphysch 4y ago
      Of course. PHP is the one you will mainly encounter in the wilderness, though.
      - 0x457 4y ago
        Most common place I saw N+1 is ActiveRecord (Ruby on Rails). Because by default, no relationships are loaded and the way AR "loads" stuff makes is extremely easy to create N+M queries on 5 lines of ruby. Then, those people will tell you that you run out of database before you run out of ruby...
  - woooooo 4y ago
    Yeah, but you have to go out of your way to do that. If you're in touch with the SQL you're executing at all, the join is the obvious easy way to do it, it's not some arcane optimization.
    - RussianCow 4y ago
      That’s a really big “if”. In my experience, most application engineers aren’t very good at SQL because they don’t have to be; most apps just don't ever reach a scale where it matters, and something like Rails or Django with N+1 queries all over the place performs just fine.
benbjohnson 4y ago
You could run Postgres over UNIX sockets although you will still get higher latency than SQLite's in-process model. Also, running a Postgres on every app instance on the edge probably isn't practical. Postgres has some great advanced features if you need them but it's also much more heavy weight. With LiteFS, we're aiming to easily run on low resource cloud hardware such as nodes with 256MB or less of RAM. I haven't tried it on a Raspberry Pi yet but I suspect it would run fine there as well.
- pphysch 4y ago
  SQLite almost certainly is the better edge RDBMS than Postgres, if only because it has less features taking up space. However, "local SQLite vs. remote Postgres/MySQL" remains a false dichotomy when talking about network latency.
  - yencabulator 4y ago
    There's still plenty of overhead in serializing data over a unix domain socket to a different process, waiting for that process to be scheduled, waiting for it to serialize a response, waiting for the client process to be scheduled. SQLite avoids all of that.
  - clord 4y ago
    It's an application design choice. It's perfectly reasonable to consider those two options when designing a system. The constraints of the system determine which is better for the application. I'd pick a centralized network-reachable database with a strong relational schema for write-heavy applications, and a lighter in-process system for something that is mostly reads and where latency matters. It's not a false dichotomy — but more like a continuum that certainly includes both extremes on it.
- cbsmith 4y ago
  Isn't this all talking to S3 anyway, not to mention the network trips intrinsic to the system before it gets to S3? I mean, I'm sure there's some performance win here, but I'm surprised it's so significant. It's not like the address-space separation is without benefits... heck, if it weren't, you could simply have embedded the whole application inside Postgres and achieved the same effect.
alberth 4y ago
A better comparison might be Postgres to BedrockDB. BeckrockDB is a MySQL compatible interface wrapped around SQLite and as such, is intended for remote RDBMS use. Expensify has some interesting scaling performance data for their use of it. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
simonw 4y ago
I get the impression that there's still quite a bit of overhead involved in talking to PostgreSQL that you don't get with SQLite. A SQLite query is pretty much a C function call that works directly with data structures and local disk.
pweissbrod 4y ago
Select N+1 is a fundamental anti-pattern of relational database usage. Reducing the latency per round trip doesn't change this fact.
- simonw 4y ago
  I disagree. I think it makes a big difference. https://sqlite.org/np1queryprob.html#the_need_for_over_200_sql_statements_per_webpage https://sqlite.org/np1queryprob.html#the_need_for_over_200_s... talks about this in the context of Fossil, which uses hundreds of queries per page and loads extremely fast. It turns out the N+1 thing really is only an anti-pattern if you're dealing with significant overhead per query. If you don't need to worry about that you can write code that's much easier to write and maintain just by putting a few SQL queries in a loop! Related: the N+1 problem is notorious in GraphQL world as one of the reasons building a high performance GraphQL API is really difficult. With SQLite you don't have to worry about that! I built https://datasette.io/plugins/datasette-graphql https://datasette.io/plugins/datasette-graphql on SQLite and was delighted at how well it can handle deeply nested queries.
  - pweissbrod 4y ago
    Perhaps the overhead of chatty queries is diminished in this special use case. But it doesn't change the fact that standalone database server processes are designed to support specific queries at lower frequencies. This is one of the main points of The SQL language is to load precisely the data that is needed in a single statement. Relying on this is a design pattern would only scale in specific use cases and would hit hard walls in changing scenarios
hinkley 4y ago
Yep. The poison is still in the cup, it's just a smaller dose. Keep drinking if you think that fact is enough to save you... I see bad judgement calls coming from small numbers, often due to failing to do the cost x frequency math properly in your head. If you're looking at an operation that takes 3ms or 3μs that is called a million times per minute, or twenty thousand times per request, you don't have enough significant figures there and people make mistakes. 3ms x 12345 = ~40s, not 37035ms. Better if you use a higher resolution clock and find out it's actually 3.259 ms, leading to a total of ~40.23s Point is, when we are doing billions of operations per second, lots of small problems that hide in clock jitter can become really big problems, and a 5-10% error repeated for half a dozen concerns can lead to serious miscalculations in capacity planning and revenue.

tptacek 4y ago

Anyone want to chime in with the largest app they've deployed where prod Postgres was reachable over a Unix domain socket?

pphysch 4y ago
I would wager that the median IT shop/dept is running off a single node RDBMS, the majority of which also have the spare resources to support running the primary application (which has 1-100 DAU). No practical way to test this, of course.
whalesalad 4y ago
is it cheating when pgbouncer is being accessed via socket? lol
- tptacek 4y ago
  Yes, that is cheating.
  - kjeetgill 4y ago
    I don't have any context around this: why is that cheating?
    - tptacek 4y ago
      Because the point of the Unix domain socket (in this argument) is eliminating network round trips.
      - kjeetgill 4y ago
        Oh gotcha, I assumed they meant all 3 we're on one host. I'm not too familiar with the pg ecosystem.
nucleardog 4y ago
Used to work developing standalone kiosks and things (think along the lines of stuff you'd find at a museum as the basic type of thing I'd work on), so absolutely not the use case you're thinking of. In a number of cases, we were working with data that would really benefit from actual GIS tooling so PostGIS was kind of the natural thing to reach for. Many of these kiosks had slow, intermittent, or just straight up no internet connection available. So we just deployed PostGIS directly on the kiosk hardware. Usually little embedded industrial machines with passive cooling working with something like a generation or two out-of-date Celeron, 8GB RAM, and a small SSD. We'd load up shapefiles covering a bunch of features across over a quarter million square miles while simultaneously running Chromium in kiosk mode. I know for a fact some of those had around 1k DAU. I mean, never more than one simultaneously but we did have them! I'm sure it would have handled more just fine if it weren't for the damn laws of physics limiting the number of people that can be physically accessing the hardware at the same time. That said, we had the benefit of knowing that our user counts would never really increase and due to real-world limitations we'd never be working with a remote database because there was literally no internet available. In general I'd still say colocating your database is not an excuse to allow N+1 query patterns to slip in. They'll be fine for now and just come back to bite you in the ass when your app _does_ outgrow colocating the app/database.
tshaddox 4y ago
Are there significant limits to the size of an app that could be deployed alongside Postgres versus the size of an app that could be deployed alongside SQLite?
- zie 4y ago
  No, the big difference is write speed. SQLite is limited in the amount of writes it can do at the same time(typically 1). This is generally called "concurrent writes". Last I checked SQLite can't really get past 1 write at the same time, but it can emulate concurrent writes in WAL mode such that it isn't normally a problem, for most applications. Postgres has no such limit(though there can be limits to updating a particular row/column of data concurrently, obviously). Otherwise both are generally limited to the physical resources of the machine(memory, disk, etc). Generally speaking you can scale boxes much much farther than your data size for most applications.
  - nicoburns 4y ago
    > it can emulate concurrent writes in WAL mode Seems a bit unfair to call WAL mode emulation of "true" concurrent writes as I'm pretty sure a write-ahead-log (WAL) is exactly how other databases implement multiple concurrent writes. It's just always-on rather than being opt-in.
    - zie 4y ago
      Well, but that's not really what it's doing. WAL1 mode in SQLite is different than in your typical RDBMS. WAL mode in SQLite is just getting the write path outside of the read path, but there is still only 1 write allowed at any given time. The "fake" concurrency is done by letting write transactions queue up and wait for the lock. See [here](https://sqlite.org/wal.html https://sqlite.org/wal.html) under "2.2. Concurrency" where it says: > "However, since there is only one WAL file, there can only be one writer at a time." SQLite is awesome, I'm a huge fan, but if you need to do lots and lots of writes, then SQLite is not your friend. Luckily that's a very rare application. There is a reason you never see SQLite being the end point for logs and other write-heavy applications.
shrubble 4y ago
It's a great way to deploy if you have the RAM and disk. Bump up the shared buffers, have a multithreaded or pooled driver for your app server so connections are not being opened and closed all the time. It really 'flies' under such a scenario. Largest app was a big analytics and drug tracking application for a small pharmaceutical company. Not sure of the size but it was very reporting heavy.
fdr 4y ago
Not me personally, but my understanding some Cisco products have Postgres "embedded." If they're not using unix domain sockets, they're at least using loopback. ...come to think of it, they may not be the only network device maker that does this.
jzelinskie 4y ago
I'm surprised no one is chiming in from the LAMP era. Maybe I was just in a bubble, but I feel like before modern cloud providers, folks deploying on VPSs tried to keep operational complexity lower by running their database on the same box until they couldn't anymore. In my experience, I just cached the living hell out of the apps to avoid the I/O from the database. Providers in those days had huge issues with noisy neighbors, so I/O performance was quite poor. [0]: https://en.wikipedia.org/wiki/LAMP_(software_bundle) https://en.wikipedia.org/wiki/LAMP_(software_bundle)
lbhdc 4y ago
We use managed postgres on GCP, and it is served over unix socket.
- cbsmith 4y ago
  Kind of by definition, GCP's managed postgres is served up over the network. I'm guessing you mean "unix socket" in the sense of the socket API, not unix domain sockets.
BeefWellington 4y ago
Back in my consulting days one of my clients had an application with nearly all of the business logic being done in-database. The app was essentially a frontend for stored procedures galore and it sat on an absolute monster of a system for its time (~256GB of RAM, 24 or 32 Cores I think). It handled something like 100k DAU with no issue. That was for just the transactional system. They replicated data to other (similar style) systems for less mission-critical things like reporting, billing, etc. I want to be clear though, I would never recommend anyone do this. For its time it was impressive but I suspect by now it's been re-engineered away from that model, if for no other reason than its a lot of eggs in one basket.
metafex 4y ago
Had an instance with almost 2TB on disk in 2015, it's probably a lot more by now, if it's still running as-is. Was for analytics/data-warehousing. Though the big table was partitioned so queries were always nice and snappy. The machine had 128gb ram back then, but probably never needed that much. The applications working with the data ran on that same server, so processing latency was usually <10ms even for larger queries.

woooooo 4y ago

Isn't "n + 1" typically a degenerate case of ORM rather than a relational thing? The whole point of rdbms is you can do a join instead of iterated lookups.

pphysch 4y ago
Yeah most N+1 issues can be mitigated by optimizing the queries. You definitely don't need an ORM to get to N+1 hell, a for-loop in vanilla PHP will do.
- mgkimsal 4y ago
  A for-loop in vanilla anything will do. Nothing special about PHP. Ruby, Python, Java, etc...
  - pphysch 4y ago
    Of course. PHP is the one you will mainly encounter in the wilderness, though.
    - 0x457 4y ago
      Most common place I saw N+1 is ActiveRecord (Ruby on Rails). Because by default, no relationships are loaded and the way AR "loads" stuff makes is extremely easy to create N+M queries on 5 lines of ruby. Then, those people will tell you that you run out of database before you run out of ruby...
- woooooo 4y ago
  Yeah, but you have to go out of your way to do that. If you're in touch with the SQL you're executing at all, the join is the obvious easy way to do it, it's not some arcane optimization.
  - RussianCow 4y ago
    That’s a really big “if”. In my experience, most application engineers aren’t very good at SQL because they don’t have to be; most apps just don't ever reach a scale where it matters, and something like Rails or Django with N+1 queries all over the place performs just fine.

benbjohnson 4y ago

You could run Postgres over UNIX sockets although you will still get higher latency than SQLite's in-process model. Also, running a Postgres on every app instance on the edge probably isn't practical. Postgres has some great advanced features if you need them but it's also much more heavy weight. With LiteFS, we're aiming to easily run on low resource cloud hardware such as nodes with 256MB or less of RAM. I haven't tried it on a Raspberry Pi yet but I suspect it would run fine there as well.

pphysch 4y ago
SQLite almost certainly is the better edge RDBMS than Postgres, if only because it has less features taking up space. However, "local SQLite vs. remote Postgres/MySQL" remains a false dichotomy when talking about network latency.
- yencabulator 4y ago
  There's still plenty of overhead in serializing data over a unix domain socket to a different process, waiting for that process to be scheduled, waiting for it to serialize a response, waiting for the client process to be scheduled. SQLite avoids all of that.
- clord 4y ago
  It's an application design choice. It's perfectly reasonable to consider those two options when designing a system. The constraints of the system determine which is better for the application. I'd pick a centralized network-reachable database with a strong relational schema for write-heavy applications, and a lighter in-process system for something that is mostly reads and where latency matters. It's not a false dichotomy — but more like a continuum that certainly includes both extremes on it.
cbsmith 4y ago
Isn't this all talking to S3 anyway, not to mention the network trips intrinsic to the system before it gets to S3? I mean, I'm sure there's some performance win here, but I'm surprised it's so significant. It's not like the address-space separation is without benefits... heck, if it weren't, you could simply have embedded the whole application inside Postgres and achieved the same effect.

alberth 4y ago

A better comparison might be Postgres to BedrockDB. BeckrockDB is a MySQL compatible interface wrapped around SQLite and as such, is intended for remote RDBMS use. Expensify has some interesting scaling performance data for their use of it. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

simonw 4y ago

I get the impression that there's still quite a bit of overhead involved in talking to PostgreSQL that you don't get with SQLite. A SQLite query is pretty much a C function call that works directly with data structures and local disk.

pweissbrod 4y ago

Select N+1 is a fundamental anti-pattern of relational database usage. Reducing the latency per round trip doesn't change this fact.

simonw 4y ago
I disagree. I think it makes a big difference. https://sqlite.org/np1queryprob.html#the_need_for_over_200_sql_statements_per_webpage https://sqlite.org/np1queryprob.html#the_need_for_over_200_s... talks about this in the context of Fossil, which uses hundreds of queries per page and loads extremely fast. It turns out the N+1 thing really is only an anti-pattern if you're dealing with significant overhead per query. If you don't need to worry about that you can write code that's much easier to write and maintain just by putting a few SQL queries in a loop! Related: the N+1 problem is notorious in GraphQL world as one of the reasons building a high performance GraphQL API is really difficult. With SQLite you don't have to worry about that! I built https://datasette.io/plugins/datasette-graphql https://datasette.io/plugins/datasette-graphql on SQLite and was delighted at how well it can handle deeply nested queries.
- pweissbrod 4y ago
  Perhaps the overhead of chatty queries is diminished in this special use case. But it doesn't change the fact that standalone database server processes are designed to support specific queries at lower frequencies. This is one of the main points of The SQL language is to load precisely the data that is needed in a single statement. Relying on this is a design pattern would only scale in specific use cases and would hit hard walls in changing scenarios

hinkley 4y ago

Yep. The poison is still in the cup, it's just a smaller dose. Keep drinking if you think that fact is enough to save you... I see bad judgement calls coming from small numbers, often due to failing to do the cost x frequency math properly in your head. If you're looking at an operation that takes 3ms or 3μs that is called a million times per minute, or twenty thousand times per request, you don't have enough significant figures there and people make mistakes. 3ms x 12345 = ~40s, not 37035ms. Better if you use a higher resolution clock and find out it's actually 3.259 ms, leading to a total of ~40.23s Point is, when we are doing billions of operations per second, lots of small problems that hide in clock jitter can become really big problems, and a 5-10% error repeated for half a dozen concerns can lead to serious miscalculations in capacity planning and revenue.