8 ms·

Ask HN: How PostgreSQL source code is 3x shorter than MySQL's one?

119 points by mcsoft 6y ago

It looks like PostgreSQL is more feature-rich and reliable, though it's written in only 1491985 LOC vs 4466967 LOC for MySQL. How is that?

owowow 6y ago
Likely different strategies for software development and less need to support legacy clients contributed to the line count being much lower.
rmrfstar 6y ago
You could probably turn this into an interesting blog post. + What subsystems does each project have? How do LOC stack up on a per-subsystem basis? + Do they have different dependencies? + Does LOC have the same meaning in the two projects, or are there stylistic differences that meaningfully alter line counts? + Are statically linked binaries similarly sized? So, it looks like you have the right question. You just need to do the digging.
- eloff 6y ago
  Also if there are large generated files (the SQL parser is generated from a grammar) or if one project is vendoring dependencies it can artificially skew the line count. It's not a meaningful measure in itself without digging deeper.
Scarbutt 6y ago
MySQL has a storage abstraction and includes multiple storages.
- 67868018 6y ago
  Postgres has storage abstraction since early 2019
- castorp 6y ago
  I think you will always find something that the other product doesn't have (or doesn't have in the same way) Postgres e.g. has the infrastructure for extendable data types and index types and the infrastructure for extensions - none of that can be found in MySQL and that infrastructure needs source code as well. I don't think the nearly 3-fold difference can't be attributed to "features", but to other things. Maybe the projects are structured differently or include different additional tools in their code (e.g. the input files to generate the SQL parser, rather than the generated parser's C code).
userbinator 6y ago
Within a single language there's already considerable stylistic variance. I used to work in CS education and marked assignments, and I would see variances in length of students' (correct) solutions, for relatively trivial problems, that spanned a range of several multiples; e.g. what someone would take 50 lines to solve, someone else might need over 300. Part of it is comments and whitespace, but unnecessary abstractions also contribute to making code more "bloaty" than it could be.
- wwweston 6y ago
  It isn't just students. The classic Knuth vs McElroy comparison shows that the right abstractions can matter quite a bit: http://www.leancrew.com/all-this/2011/12/more-shell-less-egg/ http://www.leancrew.com/all-this/2011/12/more-shell-less-egg...
johannes1234321 6y ago
One can argue about the statement that PostgreSQL is more feature rich. MySQL has more replication features, different storage engines, etc. also in MySQL GIS functionality is included and not an external plugin (like PostGIS) The source tree you looked at probably also has ndb cluster included; if you cloned from GitHub, you also get the MySQL Router and other side components. MySQL also bundles most external dependencies (excluding for example boost) Any serious discussion would require deeper analysis. P.S. I work on the MySQL team, never looked at pgsql source, so can't really judge.
- redis_mlc 6y ago
  If you're a developer, a database is just a database. If you're a corporate DBA, they are each worlds apart. MySQL is a perfect match for most SaaS applications that deal with compliance. Postgres is simply not. The main issues with managing Postgres today are grant inheritance, and also the lack of COPY FROM/TO grants. I shouldn't have to update grants for non-admin or readonly users after adding new objects, and there needs to be the equivalent of MySQL's FILE grant for Postgres' COPY FROM/TO. OTOH, it is easy to reason about MySQL grants, and almost pain-free to update them in production. If anybody can send me a link on how to make Postgres work in a corporate and/or compliance environment, let me know. Source: DBA.
  - thinkx 6y ago
    > MySQL is a perfect match for most SaaS applications that deal with compliance. Postgres is simply not. Please elaborate on this “simply not”.. you are making a categorical statement.. does there exist a DISA-certified STIG for MySQL, for DoD and military applications?
    - johannes1234321 6y ago
      I don't know what DISA or STIG are, but yes there are such projects. https://www.mysql.com/customers/industry/?id=65 https://www.mysql.com/customers/industry/?id=65 has a few relevant references.
thinkx 6y ago
Bruce Momjian wrote a few blogs about code quality.. the phrase “brutal replacement of old code” comes to mind
- oftenwrong 6y ago
  Could you share a link to some of the blog posts?
capableweb 6y ago
When groups of people develop software, you get software that represents the group and it's dynamics (Conway's law). I think it's mostly because of the people who wrote the software, and what priorities they had when working on it.
twotwotwo 6y ago
So I cloned the mysql-server and postgres repos and ran sloccount. It's not the deepest dive or anything but was interesting. I saw MySQL had...600k lines of JavaScript? It turned out that the storage/ndb directory had a Web-based management interface for NDB, which vendors in the Dojo JavaScript framework. It also had ~50k lines of Java for the "ClusterJ" framework, which interfaces with NDB skipping the SQL layer. Overall, sloccount reports about 1.4M lines of code in storage/ndb/. NDB is a specialized cluster database where all secondary indexes have to fit in the cluster's RAM(!). Work on it started at Ericsson, then it was spun out into a startup which MySQL AB eventually bought. I imagine MySQL management at the time hoped the future of DB clusters might end up looking more like NDB and less like...gestures at today's database landscape. There are also ~500K lines of Unicode tables in the strings/ directory. I recall Postgres calls out to libc for locale/collation related stuff so probably doesn't need those tables in-tree. Even accounting for those chunks you still end up with ~1M vs ~2M SLOC as measured by sloccount. (I don't want to pretend the numbers are super precise.) There are probably other differences in what's in scope for the repo or other surprises. [Edit: see johannes1234321's comment which lists some of them.] Besides those, though, might be truth to others' comments about MySQL spending lots of code supporting drastically different old and new "worlds" in a single binary (non-transactional and transactional storage, originally very-nonstandard vs. currently more-standard SQL, statement-based and row-based replication...). And at a totally non-technical level, as a product MySQL seems to have had more money thrown at it and that tends to mean more code. This was fun but was an incredibly quick and dirty dive into it, and I'd love to hear more from folks who can look more or just know more.
- mywacaday 6y ago
  I was watching Sherlock Holmes tonight and for some reason I read that in Benedict Cumberbatches voice, most entertaining, thank you!
- minusf 6y ago
  postgresql can use ICU and on some platforms it's the only way to get utf8 collations.
- twoodfin 6y ago
aristofun 6y ago
Why did you assume there is strong correlation between LOC and features/reliability in the first place?
mobilemidget 6y ago
That the source is much larger I don't mind _so much_. But this recent thing mysql is doing worries me more. ls -lah /usr/sbin/mysqld -rwxr-xr-x 1 root root 1.1G Mar 26 2020 /usr/sbin/mysqld 1.1G binary.
- bmn__ 6y ago
  How did that happen? This system's `mysqld` (from mariadb-10.4.14) is 22 MB. Even the biggest binary is only 100 MB. stat -c '%s %n' /usr/{bin,sbin}/*|sort -nr|head -20 100034576 /usr/bin/blender 63352576 /usr/bin/godot 61299448 /usr/bin/scummvm 61004336 /usr/bin/godot-headless 55123448 /usr/bin/podman 42794056 /usr/bin/podman-remote 30582984 /usr/bin/makemkv 29998400 /usr/bin/godot-runner 29340272 /usr/bin/cabal 28086824 /usr/bin/node14 27641920 /usr/bin/godot-server 25842880 /usr/bin/restic 25345200 /usr/bin/krita 24520736 /usr/bin/lto-dump-10 22376736 /usr/bin/mariabackup 22041432 /usr/sbin/mysqld 17203016 /usr/bin/clementine 15435056 /usr/bin/z3 15360752 /usr/bin/doxygen 14694288 /usr/bin/php
  - mobilemidget 6y ago
    regular rpm install mysql 8 community edition mysql-community-server-8.0.20-1.el7.x86_64

119 points by mcsoft 6y ago

It looks like PostgreSQL is more feature-rich and reliable, though it's written in only 1491985 LOC vs 4466967 LOC for MySQL. How is that?

owowow 6y ago

Likely different strategies for software development and less need to support legacy clients contributed to the line count being much lower.

rmrfstar 6y ago

You could probably turn this into an interesting blog post. + What subsystems does each project have? How do LOC stack up on a per-subsystem basis? + Do they have different dependencies? + Does LOC have the same meaning in the two projects, or are there stylistic differences that meaningfully alter line counts? + Are statically linked binaries similarly sized? So, it looks like you have the right question. You just need to do the digging.

eloff 6y ago
Also if there are large generated files (the SQL parser is generated from a grammar) or if one project is vendoring dependencies it can artificially skew the line count. It's not a meaningful measure in itself without digging deeper.

Scarbutt 6y ago

MySQL has a storage abstraction and includes multiple storages.

67868018 6y ago
Postgres has storage abstraction since early 2019
castorp 6y ago
I think you will always find something that the other product doesn't have (or doesn't have in the same way) Postgres e.g. has the infrastructure for extendable data types and index types and the infrastructure for extensions - none of that can be found in MySQL and that infrastructure needs source code as well. I don't think the nearly 3-fold difference can't be attributed to "features", but to other things. Maybe the projects are structured differently or include different additional tools in their code (e.g. the input files to generate the SQL parser, rather than the generated parser's C code).

userbinator 6y ago

Within a single language there's already considerable stylistic variance. I used to work in CS education and marked assignments, and I would see variances in length of students' (correct) solutions, for relatively trivial problems, that spanned a range of several multiples; e.g. what someone would take 50 lines to solve, someone else might need over 300. Part of it is comments and whitespace, but unnecessary abstractions also contribute to making code more "bloaty" than it could be.

wwweston 6y ago
It isn't just students. The classic Knuth vs McElroy comparison shows that the right abstractions can matter quite a bit: http://www.leancrew.com/all-this/2011/12/more-shell-less-egg/ http://www.leancrew.com/all-this/2011/12/more-shell-less-egg...

johannes1234321 6y ago

One can argue about the statement that PostgreSQL is more feature rich. MySQL has more replication features, different storage engines, etc. also in MySQL GIS functionality is included and not an external plugin (like PostGIS) The source tree you looked at probably also has ndb cluster included; if you cloned from GitHub, you also get the MySQL Router and other side components. MySQL also bundles most external dependencies (excluding for example boost) Any serious discussion would require deeper analysis. P.S. I work on the MySQL team, never looked at pgsql source, so can't really judge.

redis_mlc 6y ago
If you're a developer, a database is just a database. If you're a corporate DBA, they are each worlds apart. MySQL is a perfect match for most SaaS applications that deal with compliance. Postgres is simply not. The main issues with managing Postgres today are grant inheritance, and also the lack of COPY FROM/TO grants. I shouldn't have to update grants for non-admin or readonly users after adding new objects, and there needs to be the equivalent of MySQL's FILE grant for Postgres' COPY FROM/TO. OTOH, it is easy to reason about MySQL grants, and almost pain-free to update them in production. If anybody can send me a link on how to make Postgres work in a corporate and/or compliance environment, let me know. Source: DBA.
- thinkx 6y ago
  > MySQL is a perfect match for most SaaS applications that deal with compliance. Postgres is simply not. Please elaborate on this “simply not”.. you are making a categorical statement.. does there exist a DISA-certified STIG for MySQL, for DoD and military applications?
  - johannes1234321 6y ago
    I don't know what DISA or STIG are, but yes there are such projects. https://www.mysql.com/customers/industry/?id=65 https://www.mysql.com/customers/industry/?id=65 has a few relevant references.

thinkx 6y ago

Bruce Momjian wrote a few blogs about code quality.. the phrase “brutal replacement of old code” comes to mind

oftenwrong 6y ago
Could you share a link to some of the blog posts?

capableweb 6y ago

When groups of people develop software, you get software that represents the group and it's dynamics (Conway's law). I think it's mostly because of the people who wrote the software, and what priorities they had when working on it.

twotwotwo 6y ago

So I cloned the mysql-server and postgres repos and ran sloccount. It's not the deepest dive or anything but was interesting. I saw MySQL had...600k lines of JavaScript? It turned out that the storage/ndb directory had a Web-based management interface for NDB, which vendors in the Dojo JavaScript framework. It also had ~50k lines of Java for the "ClusterJ" framework, which interfaces with NDB skipping the SQL layer. Overall, sloccount reports about 1.4M lines of code in storage/ndb/. NDB is a specialized cluster database where all secondary indexes have to fit in the cluster's RAM(!). Work on it started at Ericsson, then it was spun out into a startup which MySQL AB eventually bought. I imagine MySQL management at the time hoped the future of DB clusters might end up looking more like NDB and less like...gestures at today's database landscape. There are also ~500K lines of Unicode tables in the strings/ directory. I recall Postgres calls out to libc for locale/collation related stuff so probably doesn't need those tables in-tree. Even accounting for those chunks you still end up with ~1M vs ~2M SLOC as measured by sloccount. (I don't want to pretend the numbers are super precise.) There are probably other differences in what's in scope for the repo or other surprises. [Edit: see johannes1234321's comment which lists some of them.] Besides those, though, might be truth to others' comments about MySQL spending lots of code supporting drastically different old and new "worlds" in a single binary (non-transactional and transactional storage, originally very-nonstandard vs. currently more-standard SQL, statement-based and row-based replication...). And at a totally non-technical level, as a product MySQL seems to have had more money thrown at it and that tends to mean more code. This was fun but was an incredibly quick and dirty dive into it, and I'd love to hear more from folks who can look more or just know more.

mywacaday 6y ago
I was watching Sherlock Holmes tonight and for some reason I read that in Benedict Cumberbatches voice, most entertaining, thank you!
minusf 6y ago
postgresql can use ICU and on some platforms it's the only way to get utf8 collations.
twoodfin 6y ago

aristofun 6y ago

Why did you assume there is strong correlation between LOC and features/reliability in the first place?

mobilemidget 6y ago

That the source is much larger I don't mind _so much_. But this recent thing mysql is doing worries me more. ls -lah /usr/sbin/mysqld -rwxr-xr-x 1 root root 1.1G Mar 26 2020 /usr/sbin/mysqld 1.1G binary.

bmn__ 6y ago
How did that happen? This system's `mysqld` (from mariadb-10.4.14) is 22 MB. Even the biggest binary is only 100 MB. stat -c '%s %n' /usr/{bin,sbin}/*|sort -nr|head -20 100034576 /usr/bin/blender 63352576 /usr/bin/godot 61299448 /usr/bin/scummvm 61004336 /usr/bin/godot-headless 55123448 /usr/bin/podman 42794056 /usr/bin/podman-remote 30582984 /usr/bin/makemkv 29998400 /usr/bin/godot-runner 29340272 /usr/bin/cabal 28086824 /usr/bin/node14 27641920 /usr/bin/godot-server 25842880 /usr/bin/restic 25345200 /usr/bin/krita 24520736 /usr/bin/lto-dump-10 22376736 /usr/bin/mariabackup 22041432 /usr/sbin/mysqld 17203016 /usr/bin/clementine 15435056 /usr/bin/z3 15360752 /usr/bin/doxygen 14694288 /usr/bin/php
- mobilemidget 6y ago
  regular rpm install mysql 8 community edition mysql-community-server-8.0.20-1.el7.x86_64