8 ms·

Building a Database in the 2020s (2022)

103 points by lawrencechen 4y ago

KrugerDunnings 4y ago
I've been building a Postgresql extension in the last months for some functionality that was needed and have learned a ton about the internal workings of this database. All very scary and complicated sounding stuff but I feel privileged to be able to do this because the things you learn are just pure gold. My attitude before this was that of the ideal customer of a cloud database, someone who was scared of sql and preferred to hide behind the complexity of a ORM. Not anymore, now I write thousands of lines of sql and laugh to myself like a maniac.
- nonethewiser 4y ago
  That’s a big jump - hiding behind ORMs to learnings some inner workings of Postgres. Do you have an example of something that you previously saw as a black box but now understand? Perhaps something simpler than you anticipated, or just something you never even knew existed before digging into the details?
  - KrugerDunnings 4y ago
    There is just so much, I've thought of writing some blog post about it but there is already a lot of content out there because pg is a big community with a lot of people doing interesting things and I don't know if what I know is unique and not just parroting others peoples stuff. But ok sure I can give you some pointers to cool stuff. Sometimes it are just architectural patterns that are easy to do like upsert statements in sql using `on conflict`, or writing a task queue on the cheap using `for update skip locked limit 1`. Replace 90% of your crud api controllers with `postgrest` and use row level security for everything, AI is not going to steal your job category theory is. From a operations and scaling perspective it pays dividend to learn about `explain analyse` but did you know they also have `stats` that can help the analyser optimise queries based on statistical relations of different columns. I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. There are also just a ton of extension for timeseries or vector embeddings, or write your own in Rust using `pgx`. Like I said way too much to write here, I don't use all of this in production but I just keep finding these gems while working on my own project. There is a strong OSS ecosystem with lots of teams giving there own spin on pg and that is welcomed if you have a opinion of your own and still want to learn from others. You need motivation to go this deep but in contrast to other esoteric knowledge I have mastered companies are also willing to pay for it.
    - jrumbut 4y ago
      Yeah a number of pieces of software people use after a 5 minute tutorial are like this, a ton of depth that we frequently recreate crappy versions of within our apps. The Apache webserver (and most other webservers) is another example like Postgres where it has a ton of useful features no one uses. Some of these less popular features don't work at massive scale but in my experience they work great for smaller teams where adding a new tech to the stack is more painful.
    - bjornsing 4y ago
      > I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. In the cloud you can just snapshot/clone the underlying EBS volume and you’ll get a “branch” of any database on any file system, right?
      - muskmusk 4y ago
        Assuming no-one is using the database yes. Also not trivial to set up and use plus vm startup is somewhat slow. If your want a full featured product (in the postgres space) that does it then look at neon.tech (no affiliation).
    - kiwicopple 4y ago
      for what it's worth, what you describe is the architecture philosophy of supabase (disclosure: i'm the ceo) supabase is essentially a Postgres database with PostgREST on top, and we recommend pushing down a lot of the logic and security into the database. We took this philosophy with our pg_graphql extension (which uses pgx) and it is faster than other graphql implementations, simply it's co-located with your data, solving the n+1 problem. pl_rust just reached 1.0, and it is now a "trusted language" so you can expect to see it arriving on a few cloud providers soon. We are releasing something this week with the RDS team which will make it easier to write key parts of your application code in trusted languages. There are certainly trade-offs, and I don't know if _everything_ should be in the database. But in data-intensive cases it makes a lot of sense.
- juancn 4y ago
  Databases are worth the time to understand deeply. Postgres in particular is a magnificent beast.
paulddraper 4y ago
I'm surprised there isn't a "serverless" PostgreSQL. That seems like it would get more bank for buck then writing a cloud native DB from scratch. (Or maybe there is one but I don't know about it.) AWS made a serverless MySQL.
- jansommer 4y ago
  neon.tech
- esperent 4y ago
  A quick Google search suggests there are many serverless PostGres implementations, including an AWS version.
  - paulddraper 4y ago
    Ah I'm out of date then
  - antifa 4y ago
    I wish GCP would implement one.
- dragonwriter 4y ago
  > I'm surprised there isn't a "serverless" PostgreSQL There are many. > AWS made a serverless MySQL. AWS Aurora Serverless has both MySQL and Postgres flavors.
- adam_gyroscope 4y ago
  bit.io
- samieljabali 3y ago
  planetscale.com
vkakade 4y ago
I would also add that the databases in 2020s will be written in Rust, rather than C/C++. The safety guarantees Rust provides makes the development process faster, as well as results is clean code that is easier to understand and extend.
- gxt 4y ago
  Working on it. Just wish LLVM was natively written in rust too, it's easy to segfault when doing something unexpected.
  - mamcx 4y ago
    Same. Even if some aspect of Rust safety can't cover the complications of the low-level stuff, is easier to do that on Rust than with C/c++.
- breck 4y ago
  I have seen some data that makes me think you may be right. I haven't looked at database projects yet but looking at the code bases of other programming languages and big systems (such as Linux), I see the Rust file count going up. That being said, I did recently look at SQLite and that is still all C. It's on my todo list to look this up for all the major open source DBs.
  - xiphias2 4y ago
    I was thinking of giving AutoGPT a try to convert SQLite (or Redis) to Rust, because it has a lot of tests anyways, so it would be fun. I'm still waiting for GPT-4 API access though :(
- jandrewrogers 4y ago
  Modern database architectures have memory safety models that the Rust compiler currently can’t reason about. Hence why new database kernels are still written in C++. It isn’t because C++ is great (it is not) but because there are proven high-performance safety models in databases that Rust has difficulty expressing without a lot of “unsafe”. Generally speaking, modern database architectures have minimal dynamic memory allocation, no multi-threaded code to speak of, or even buffer overflow issues (everything internally is built for paged memory). Where are these advantages from Rust supposed to come from? It has a lot of disadvantages for databases today, and I say that as someone who has used Rust in this space. People who build this kind of software are pragmatic and Rust doesn’t deliver for database developers currently.
carom 4y ago
Memory safety is something that needs to be mentioned. I was integrating DuckDB into a project and ended up ripping it out after running into memory corruption issue in practice. Upon investigation they had a massive issue of fuzzer found bugs on their GitHub. While I am glad they are fuzzing and finding issues, I cannot ship that onto customer systems. We have a few very good memory safe programming languages at this point. Please do not start a project in C/C++ unless you are truly exceptional and understand memory management and exploitation inside and out. I switched to SQLite on the project since it is one of the more fuzzed applications out there that fit the need. The next embeddable database I use (bonus if it works on cloud) will need to be in a memory safe language.
- tyingq 4y ago
  If you're writing an embeddable database, exposing it as a C library maximizes the potential user base. Since most languages have some way to do ffi with C, most platforms have C compilers, etc.
  - josephg 4y ago
    C FFI goes both ways. It’s also pretty easy to compile rust or zig code into a static or dynamic C library. And then that library can be used directly from C or from anything that has a C FFI (Python, Ruby, javascript, Java, etc etc). I’ve been working on an iOS app lately with some of the core code in rust (shared with other platforms) and the UI in native swift. Changing the API at the boundary is a bit annoying, but the resulting app works great.
    - plagiarist 4y ago
      What are the ergonomics of compiling that binary library? I think this sounds really neat, if you have a build system that produces something like an .xcframework and corresponding package file it wouldn't even be a pain point.
      - josephg 3y ago
breck 4y ago
I'm working on a new Git backed file based Database for knowledge bases. Not designed for domains where you can confidently predict your schemas ahead of time but instead designed for use cases where you have large complex schemas that are frequently changing. It simply wasn't possible a few years ago (SSDs weren't fast enough and Git was too slow for projects with huge number of files). I'm having fun with it. Current version is just written in Javascript but if there demand hits a higher level would likely write a version in Rust or Go. If anyone has any pointers to similar projects I'm all ears.
- FridgeSeal 4y ago
  Have you looked at Dolt? Or LakeFS? Both of these are taking the “hot for data” approach too.
  - breck 4y ago
    I had not seen LakeFS. Thanks of the pointer! I have been following Dolt. Been a while since I took a look. Seems like they are making progress!
refset 4y ago
Interesting read! I would like to add: * databases need to get better yet at schema management and workload isolation to enable multiple applications to properly integrate through the database (as traditionally envisioned) * HTAP seems inevitable but needs built-in support for row-level history/versioning to get maximum benefit * databases should abstract over raw compute infrastructure efficiently enough that you don't need k8s to run your application logic and APIs elsewhere. The database should be a decent all-in-one place to build & ship stuff
quanticle 4y ago
Is it just me, or does Ed Huang skip over the most important part of database design: actually making sure the database has stored the data? I read to the end of the article, and while having a database as a serverless collection of microservices deployed to a cloud provider might be useful, it ultimately will be useless if this swarm approach doesn't give me any guarantees about how or if my data actually makes it onto persistent storage at some point. I was expecting a discussion of the challenges and pitfalls involved in ensuring that a cloud of microservices can concurrently access a common data store (whether that's a physical disk on a server or a S3 bucket), without stomping on each other, but that seemed to be entirely missing from the post. Performance and scalability are fine, but when it comes to databases, they're of secondary importance to ensuring that the developer has a good understanding of when and if their data has been safely stored.
- audioheavy 3y ago
  Excellent point. Many discussions here do not emphasize transactional guarantees enough, and most developers writing front-ends should not have to worry about programming to address high write contention and concurrency to avoid data anomalies. As an industry, we've progressed quite a bit from accepting data isolation level compromises like "eventual consistency" in NoSQL, cloud, and serverless databases. The database I work with (Fauna) implements a distributed transaction engine inspired by the Calvin consensus protocol that guarantees strictly serializable writes over disparate globally deployed replicas. Both Calvin and Spanner implement such guarantees (in significantly different ways) but Fauna is more of a turn-key, low Ops service. Again, to disclaim, I work for Fauna, but we've proven that you can accomplish this without having to worry about managing clusters, replication, partitioning strategies, etc. In today's serverless world, spending time managing database clusters manually involves a lot of undifferentiated, costly heavy lifting. YMMV.
- rockwotj 4y ago
  I agree that actually persisting data reliability is tablestakes for a database, which I would assume Ed takes for granted this needs to work. Obviously lots of non trivial stuff there but this post seems to be more about database product direction than the nitty gritty technical details talking about fsync, filesystems, etc
vp8989 4y ago
I dont agree with the premise that running transactional and analytical workloads on the same database is architecturally “simpler”. In my experience this is only true at very low scale and those contexts are already sufficiently well served by existing database tech.
- munchor 4y ago
  Can you elaborate? I am curious :)
  - vp8989 4y ago
    At a certain amount of scale its net “simpler” if the analytics people are free to do their jobs without worrying they will bring down “the app” and vice versa. OLAP has certainly become overcomplicated but collapsing everything into big monolith dbs again is an over correction IMO.
    - munchor 4y ago
      Yeah, that’s interesting. I think both SingleStoreDB and Rockset have come up with solutions to this problem (by separating the compute). But I understand that most database providers do not have a mechanism for the analytics people to not bring down the app accidentally. [1]: https://www.singlestore.com/blog/distributed-sql-workspaces-power-modern-applications/ https://www.singlestore.com/blog/distributed-sql-workspaces-... [2]: https://www.rockset.com/blog/introducing-compute-compute-separation/ https://www.rockset.com/blog/introducing-compute-compute-sep...
    - FridgeSeal 4y ago
      To be fair, the OP is part of PingCap, who build TiKV/TiDB, which is built from the ground up to support both workloads, they call their database “Hybrid Transactional Analytical” database, which is explicitly designed to support both workloads without either side “treading on each other”, and without needing ETL pipelines gluing stuff together.
deleted 4y ago
[deleted]

103 points by lawrencechen 4y ago

I've been building a Postgresql extension in the last months for some functionality that was needed and have learned a ton about the internal workings of this database. All very scary and complicated sounding stuff but I feel privileged to be able to do this because the things you learn are just pure gold. My attitude before this was that of the ideal customer of a cloud database, someone who was scared of sql and preferred to hide behind the complexity of a ORM. Not anymore, now I write thousands of lines of sql and laugh to myself like a maniac.

nonethewiser 4y ago
That’s a big jump - hiding behind ORMs to learnings some inner workings of Postgres. Do you have an example of something that you previously saw as a black box but now understand? Perhaps something simpler than you anticipated, or just something you never even knew existed before digging into the details?
- KrugerDunnings 4y ago
  There is just so much, I've thought of writing some blog post about it but there is already a lot of content out there because pg is a big community with a lot of people doing interesting things and I don't know if what I know is unique and not just parroting others peoples stuff. But ok sure I can give you some pointers to cool stuff. Sometimes it are just architectural patterns that are easy to do like upsert statements in sql using `on conflict`, or writing a task queue on the cheap using `for update skip locked limit 1`. Replace 90% of your crud api controllers with `postgrest` and use row level security for everything, AI is not going to steal your job category theory is. From a operations and scaling perspective it pays dividend to learn about `explain analyse` but did you know they also have `stats` that can help the analyser optimise queries based on statistical relations of different columns. I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. There are also just a ton of extension for timeseries or vector embeddings, or write your own in Rust using `pgx`. Like I said way too much to write here, I don't use all of this in production but I just keep finding these gems while working on my own project. There is a strong OSS ecosystem with lots of teams giving there own spin on pg and that is welcomed if you have a opinion of your own and still want to learn from others. You need motivation to go this deep but in contrast to other esoteric knowledge I have mastered companies are also willing to pay for it.
  - jrumbut 4y ago
    Yeah a number of pieces of software people use after a 5 minute tutorial are like this, a ton of depth that we frequently recreate crappy versions of within our apps. The Apache webserver (and most other webservers) is another example like Postgres where it has a ton of useful features no one uses. Some of these less popular features don't work at massive scale but in my experience they work great for smaller teams where adding a new tech to the stack is more painful.
  - bjornsing 4y ago
    > I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. In the cloud you can just snapshot/clone the underlying EBS volume and you’ll get a “branch” of any database on any file system, right?
    - muskmusk 4y ago
      Assuming no-one is using the database yes. Also not trivial to set up and use plus vm startup is somewhat slow. If your want a full featured product (in the postgres space) that does it then look at neon.tech (no affiliation).
  - kiwicopple 4y ago
    for what it's worth, what you describe is the architecture philosophy of supabase (disclosure: i'm the ceo) supabase is essentially a Postgres database with PostgREST on top, and we recommend pushing down a lot of the logic and security into the database. We took this philosophy with our pg_graphql extension (which uses pgx) and it is faster than other graphql implementations, simply it's co-located with your data, solving the n+1 problem. pl_rust just reached 1.0, and it is now a "trusted language" so you can expect to see it arriving on a few cloud providers soon. We are releasing something this week with the RDS team which will make it easier to write key parts of your application code in trusted languages. There are certainly trade-offs, and I don't know if _everything_ should be in the database. But in data-intensive cases it makes a lot of sense.
juancn 4y ago
Databases are worth the time to understand deeply. Postgres in particular is a magnificent beast.

paulddraper 4y ago

I'm surprised there isn't a "serverless" PostgreSQL. That seems like it would get more bank for buck then writing a cloud native DB from scratch. (Or maybe there is one but I don't know about it.) AWS made a serverless MySQL.

jansommer 4y ago
neon.tech
esperent 4y ago
A quick Google search suggests there are many serverless PostGres implementations, including an AWS version.
- paulddraper 4y ago
  Ah I'm out of date then
- antifa 4y ago
  I wish GCP would implement one.
dragonwriter 4y ago
> I'm surprised there isn't a "serverless" PostgreSQL There are many. > AWS made a serverless MySQL. AWS Aurora Serverless has both MySQL and Postgres flavors.
adam_gyroscope 4y ago
bit.io
samieljabali 3y ago
planetscale.com

vkakade 4y ago

I would also add that the databases in 2020s will be written in Rust, rather than C/C++. The safety guarantees Rust provides makes the development process faster, as well as results is clean code that is easier to understand and extend.

gxt 4y ago
Working on it. Just wish LLVM was natively written in rust too, it's easy to segfault when doing something unexpected.
- mamcx 4y ago
  Same. Even if some aspect of Rust safety can't cover the complications of the low-level stuff, is easier to do that on Rust than with C/c++.
breck 4y ago
I have seen some data that makes me think you may be right. I haven't looked at database projects yet but looking at the code bases of other programming languages and big systems (such as Linux), I see the Rust file count going up. That being said, I did recently look at SQLite and that is still all C. It's on my todo list to look this up for all the major open source DBs.
- xiphias2 4y ago
  I was thinking of giving AutoGPT a try to convert SQLite (or Redis) to Rust, because it has a lot of tests anyways, so it would be fun. I'm still waiting for GPT-4 API access though :(
jandrewrogers 4y ago
Modern database architectures have memory safety models that the Rust compiler currently can’t reason about. Hence why new database kernels are still written in C++. It isn’t because C++ is great (it is not) but because there are proven high-performance safety models in databases that Rust has difficulty expressing without a lot of “unsafe”. Generally speaking, modern database architectures have minimal dynamic memory allocation, no multi-threaded code to speak of, or even buffer overflow issues (everything internally is built for paged memory). Where are these advantages from Rust supposed to come from? It has a lot of disadvantages for databases today, and I say that as someone who has used Rust in this space. People who build this kind of software are pragmatic and Rust doesn’t deliver for database developers currently.

carom 4y ago

Memory safety is something that needs to be mentioned. I was integrating DuckDB into a project and ended up ripping it out after running into memory corruption issue in practice. Upon investigation they had a massive issue of fuzzer found bugs on their GitHub. While I am glad they are fuzzing and finding issues, I cannot ship that onto customer systems. We have a few very good memory safe programming languages at this point. Please do not start a project in C/C++ unless you are truly exceptional and understand memory management and exploitation inside and out. I switched to SQLite on the project since it is one of the more fuzzed applications out there that fit the need. The next embeddable database I use (bonus if it works on cloud) will need to be in a memory safe language.

tyingq 4y ago
If you're writing an embeddable database, exposing it as a C library maximizes the potential user base. Since most languages have some way to do ffi with C, most platforms have C compilers, etc.
- josephg 4y ago
  C FFI goes both ways. It’s also pretty easy to compile rust or zig code into a static or dynamic C library. And then that library can be used directly from C or from anything that has a C FFI (Python, Ruby, javascript, Java, etc etc). I’ve been working on an iOS app lately with some of the core code in rust (shared with other platforms) and the UI in native swift. Changing the API at the boundary is a bit annoying, but the resulting app works great.
  - plagiarist 4y ago
    What are the ergonomics of compiling that binary library? I think this sounds really neat, if you have a build system that produces something like an .xcframework and corresponding package file it wouldn't even be a pain point.
    - josephg 3y ago

breck 4y ago

I'm working on a new Git backed file based Database for knowledge bases. Not designed for domains where you can confidently predict your schemas ahead of time but instead designed for use cases where you have large complex schemas that are frequently changing. It simply wasn't possible a few years ago (SSDs weren't fast enough and Git was too slow for projects with huge number of files). I'm having fun with it. Current version is just written in Javascript but if there demand hits a higher level would likely write a version in Rust or Go. If anyone has any pointers to similar projects I'm all ears.

FridgeSeal 4y ago
Have you looked at Dolt? Or LakeFS? Both of these are taking the “hot for data” approach too.
- breck 4y ago
  I had not seen LakeFS. Thanks of the pointer! I have been following Dolt. Been a while since I took a look. Seems like they are making progress!

refset 4y ago

Interesting read! I would like to add: * databases need to get better yet at schema management and workload isolation to enable multiple applications to properly integrate through the database (as traditionally envisioned) * HTAP seems inevitable but needs built-in support for row-level history/versioning to get maximum benefit * databases should abstract over raw compute infrastructure efficiently enough that you don't need k8s to run your application logic and APIs elsewhere. The database should be a decent all-in-one place to build & ship stuff

quanticle 4y ago

Is it just me, or does Ed Huang skip over the most important part of database design: actually making sure the database has stored the data? I read to the end of the article, and while having a database as a serverless collection of microservices deployed to a cloud provider might be useful, it ultimately will be useless if this swarm approach doesn't give me any guarantees about how or if my data actually makes it onto persistent storage at some point. I was expecting a discussion of the challenges and pitfalls involved in ensuring that a cloud of microservices can concurrently access a common data store (whether that's a physical disk on a server or a S3 bucket), without stomping on each other, but that seemed to be entirely missing from the post. Performance and scalability are fine, but when it comes to databases, they're of secondary importance to ensuring that the developer has a good understanding of when and if their data has been safely stored.

audioheavy 3y ago
Excellent point. Many discussions here do not emphasize transactional guarantees enough, and most developers writing front-ends should not have to worry about programming to address high write contention and concurrency to avoid data anomalies. As an industry, we've progressed quite a bit from accepting data isolation level compromises like "eventual consistency" in NoSQL, cloud, and serverless databases. The database I work with (Fauna) implements a distributed transaction engine inspired by the Calvin consensus protocol that guarantees strictly serializable writes over disparate globally deployed replicas. Both Calvin and Spanner implement such guarantees (in significantly different ways) but Fauna is more of a turn-key, low Ops service. Again, to disclaim, I work for Fauna, but we've proven that you can accomplish this without having to worry about managing clusters, replication, partitioning strategies, etc. In today's serverless world, spending time managing database clusters manually involves a lot of undifferentiated, costly heavy lifting. YMMV.
rockwotj 4y ago
I agree that actually persisting data reliability is tablestakes for a database, which I would assume Ed takes for granted this needs to work. Obviously lots of non trivial stuff there but this post seems to be more about database product direction than the nitty gritty technical details talking about fsync, filesystems, etc

vp8989 4y ago

I dont agree with the premise that running transactional and analytical workloads on the same database is architecturally “simpler”. In my experience this is only true at very low scale and those contexts are already sufficiently well served by existing database tech.

munchor 4y ago
Can you elaborate? I am curious :)
- vp8989 4y ago
  At a certain amount of scale its net “simpler” if the analytics people are free to do their jobs without worrying they will bring down “the app” and vice versa. OLAP has certainly become overcomplicated but collapsing everything into big monolith dbs again is an over correction IMO.
  - munchor 4y ago
    Yeah, that’s interesting. I think both SingleStoreDB and Rockset have come up with solutions to this problem (by separating the compute). But I understand that most database providers do not have a mechanism for the analytics people to not bring down the app accidentally. [1]: https://www.singlestore.com/blog/distributed-sql-workspaces-power-modern-applications/ https://www.singlestore.com/blog/distributed-sql-workspaces-... [2]: https://www.rockset.com/blog/introducing-compute-compute-separation/ https://www.rockset.com/blog/introducing-compute-compute-sep...
  - FridgeSeal 4y ago
    To be fair, the OP is part of PingCap, who build TiKV/TiDB, which is built from the ground up to support both workloads, they call their database “Hybrid Transactional Analytical” database, which is explicitly designed to support both workloads without either side “treading on each other”, and without needing ETL pipelines gluing stuff together.

deleted 4y ago

[deleted]