5 ms·

>Really the only downside I ever found — at the scale I was operating at — was that it bloats the database backups. Easily solved by having two separate databa

by bmurphy1976 7y ago

>Really the only downside I ever found — at the scale I was operating at — was that it bloats the database backups. Easily solved by having two separate databases. One for dynamic content, one for static files, which is probably good practice regardless.

Spivak 7y ago
Doesn't that introduce the desync problem again? Is there something that can enforce consistency across the two databases?
- zzzcpan 7y ago
  Backups are fundamentally limited to eventual consistent, there is no need for databases to be synchronously replicated for backups. I mean splitting database has no effect on backup consistency, although a more decent way of dealing with it is not splitting database, but simply running an async replica to do backups from.
  - ahachete 7y ago
    Backups on databases like PostgreSQL are strongly consistent, regardless of where you take it from (master or replica). Postgres replication is strictly sequential wrt transaction commit order. As such, splitting the database may incur in significant consistency issues that a backup doesn't incur into. I believe this splitting technique is not a good one except for potentially narrow use cases.
    - zzzcpan 7y ago
      Strong consistency is not what you think then. You can only do stale reads from backups.
      - ptx 7y ago
        I thought ahachete meant that a database backup is atomic, i e. it will only contain fully completed transactions. The problem with data split across databases then is that transactions don't span multiple databases, so you can't get an atomic snapshot of data spanning multiple databases. Are you saying that even a single-database backup is not atomic?
        zzzcpan 7y ago
        No, it's atomic. It's just not important if separate databases are not atomic to each other when you recover from backups, you will still have data loss and an inconsistent state. I.e. things that should be in the database missing, and that shouldn't be overwriting missing IDs, etc. Backups cannot be strongly consistent, so you have to take the exact same approaches to deal with this whether you store everything in a single database or in two separate ones.
  - saltcured 7y ago
    Are you are talking past each other...? I suspect the grandparent post is concerned about the loss of ACID properties for updating data and metadata if they are split across two databases. This is a concern for regular application access too. Meanwhile, you assume that this consistency problem is already solved in the application, and so of course the backup and restoration problem can also then be handled. To the grandparent: you typically solve this application consistency issue by using something like immutable object store semantics with versioned references. The object store is capable of answering requests for multiple versions of the asset, and the application metadata store tracks individual versions. You can sequence the order in which you commit to these stores, so the asset is always available before it is published in the metadata store. Alternatively, you can make consumers aware of temporary unavailability, so they can wait for the asset to become available even if they stumble on the new version reference before the content is committed. You can also find hybrids where the metadata store is used to track the asset lifecycle, exposing previous and next asset version references in a sort of two-phase commit protocol at the application level. This can allow consuming applications to choose whether they wait for the latest or move forward with older data. It also makes it easier to handle failure/recovery procedures such as canceling an update that is taking too long and reclaiming resources.
- bmurphy1976 7y ago
  I'm assuming the static and dynamic content don't need to be transactionally integrated. If they do, you could still solve it with two databases, but you'd use one database for the "current" data and another database to store "historical" data. Also, remember the GP said "small to mid-size."

Spivak 7y ago

Doesn't that introduce the desync problem again? Is there something that can enforce consistency across the two databases?

zzzcpan 7y ago
Backups are fundamentally limited to eventual consistent, there is no need for databases to be synchronously replicated for backups. I mean splitting database has no effect on backup consistency, although a more decent way of dealing with it is not splitting database, but simply running an async replica to do backups from.
- ahachete 7y ago
  Backups on databases like PostgreSQL are strongly consistent, regardless of where you take it from (master or replica). Postgres replication is strictly sequential wrt transaction commit order. As such, splitting the database may incur in significant consistency issues that a backup doesn't incur into. I believe this splitting technique is not a good one except for potentially narrow use cases.
  - zzzcpan 7y ago
    Strong consistency is not what you think then. You can only do stale reads from backups.
    - ptx 7y ago
      I thought ahachete meant that a database backup is atomic, i e. it will only contain fully completed transactions. The problem with data split across databases then is that transactions don't span multiple databases, so you can't get an atomic snapshot of data spanning multiple databases. Are you saying that even a single-database backup is not atomic?
      - zzzcpan 7y ago
        No, it's atomic. It's just not important if separate databases are not atomic to each other when you recover from backups, you will still have data loss and an inconsistent state. I.e. things that should be in the database missing, and that shouldn't be overwriting missing IDs, etc. Backups cannot be strongly consistent, so you have to take the exact same approaches to deal with this whether you store everything in a single database or in two separate ones.
- saltcured 7y ago
  Are you are talking past each other...? I suspect the grandparent post is concerned about the loss of ACID properties for updating data and metadata if they are split across two databases. This is a concern for regular application access too. Meanwhile, you assume that this consistency problem is already solved in the application, and so of course the backup and restoration problem can also then be handled. To the grandparent: you typically solve this application consistency issue by using something like immutable object store semantics with versioned references. The object store is capable of answering requests for multiple versions of the asset, and the application metadata store tracks individual versions. You can sequence the order in which you commit to these stores, so the asset is always available before it is published in the metadata store. Alternatively, you can make consumers aware of temporary unavailability, so they can wait for the asset to become available even if they stumble on the new version reference before the content is committed. You can also find hybrids where the metadata store is used to track the asset lifecycle, exposing previous and next asset version references in a sort of two-phase commit protocol at the application level. This can allow consuming applications to choose whether they wait for the latest or move forward with older data. It also makes it easier to handle failure/recovery procedures such as canceling an update that is taking too long and reclaiming resources.
bmurphy1976 7y ago
I'm assuming the static and dynamic content don't need to be transactionally integrated. If they do, you could still solve it with two databases, but you'd use one database for the "current" data and another database to store "historical" data. Also, remember the GP said "small to mid-size."