{"id":10940,"date":"2023-11-03T12:27:38","date_gmt":"2023-11-03T11:27:38","guid":{"rendered":"https:\/\/monodes.com\/predaelli\/?p=10940"},"modified":"2023-11-03T12:27:42","modified_gmt":"2023-11-03T11:27:42","slug":"versioning-data-in-postgres-testing-a-git-like-approach-specfy","status":"publish","type":"post","link":"https:\/\/monodes.com\/predaelli\/2023\/11\/03\/versioning-data-in-postgres-testing-a-git-like-approach-specfy\/","title":{"rendered":"Versioning data in Postgres? Testing a git like approach – Specfy"},"content":{"rendered":"\n

Versioning data in Postgres? Testing a git like approach – Specfy<\/a><\/em> is fashinating but I think that most of the time these two proposed alternatives fit most of the needs:<\/p>\n\n\n\n

    \n
  1. In-Table versioning, the WordPress way of doing thing. Add a a column version<\/code> (or modify date) and SELECT the maximum version. It’s simple and doesn’t require maintaining multiple schema or resources. However it has massive drawback in term of performance, and query simplicity. The table will inevitably grow, and SELECT needs to have an order by which can make joining\/grouping\/aggregating harder or slower.<\/li>\n\n\n\n
  2. Copy table versioning: the most simple and efficient alternative. Create a quasi equivalent copy of the table your are versioning, migration is almost 1:1. However you still need to add metadata fields, disable or rename primary key to allow the same id<\/code> to be inserted multiple times. And you obviously need one table per versionned table.<\/li>\n<\/ol>\n\n\n\n\n\n\n\n\n\n\n\n
    \n

    Versioning data in Postgres? Testing a git like approach<\/h1>\n\n\n\n

    Samuel Bodin<\/a><\/p>\n\n\n\n

    Oct 20, 2023<\/p>\n\n\n\n

    Postgres is probably the software I love the most. Battle tested, resilient and scalable, it has been perfectly crafted in the last 30 years into one the most critical piece of technology out there. However one could complain it’s not the fastest software to introduce new feature. And when something is missing you might end up being stuck, adding plugins is not easy and often impossible in the cloud, new version migration can be tricky or very slow. Moreover, a much-anticipated feature might take years to see the light of day, like table partitioning. And a notable absence in PostgreSQL<\/strong> is: data versioning.<\/p>\n\n\n\n

    Over the years, I had to add versioning in Postgres more than 10 times, and every time in a slightly different way. When I started prototyping Specfy I knew it would my 11th. Keeping history of different tables is critical<\/strong> to this project, so I needed a simple way to do it at scale with different tables, evolving schema and with as little maintenance as possible.<\/p>\n\n\n\n

    I listed alternatives and ready to use solution, but all of them had drawbacks (listed at the end) so I continued my search. I got curious about storing everything in Git.<\/strong> It has out of the box versioning, branching, conflict resolution, scale, etc. After a few days of digging, I realised it was a bit overkill and not the best for structured content like JSON, but it motivated me to try something new, a generic git-like versioning but in Postgres<\/strong>.<\/p>\n\n\n\n

    \u00a7<\/a>How Git internal works?<\/h2>\n\n\n\n

    I think it’s important to understand how git works internally before jumping on the solution. We won’t replicate all git’s pattern in Postgres but try to achieve the same way to store and version objects. Please note that the following section is a simplification to make it easier to understand.<\/p>\n\n\n\n

    \u00a7<\/a>Creating a file<\/h3>\n\n\n\n

    A git commit flow usually looks like this:<\/p>\n\n\n\n

    git add myfile.txt\ngit commit -m "initial commit"\ngit push\n<\/code><\/pre>\n\n\n\n
    Those are high level commands that hides the complexity of git. It’s actually possible to write a commit using more low-level commands, like this:<\/p>\n\n\n\n
    # Get the hash of the file's content\n$hash=${git hash-object -w myfile.txt}\n\n# Add this hash with the file to object storage\ngit update-index --add --cacheinfo 100644 \\\n  $hash myfile.txt\n\n# Write this file to a tree\n# A tree is representation of a folder and its files\n#  each folder has a tree hash\n# For the sake of simplicity we are still using some plumbing\n$treeHash=${git write-tree}\n\n# Commit the tree\ngit commit-tree $treeHash -m "initial commit"\n\n# Update the ref to point the HEAD to the new tree\ngit update-ref refs\/heads\/main $treeHash\ngit push\n<\/code><\/pre>\n\n\n\n
    \"\"\/<\/figure>\n\n\n\n
    Basically at each step you get an hash: a file has an hash, a tree has an hash, a commit has an hash, a branch is a reference to hash, a tag is a reference to hash, a stash has an hash, etc…<\/p>\n\n\n\n
    git log --pretty=raw\n\ncommit 7db0d9cefc38d263af8be1fa4b2f84531c151a60\ntree 93d8ccbc3f275695ad86ca6126f0154b3973be42\nparent 2f4eef4ad79ffda62f9b940d5a95fab7d17c0bd2\nauthor Samuel Bodin <1637651+bodinsamuel@users.noreply.github.com> 1697460399 +0200\n<\/code><\/pre>\n\n\n\n
    \u00a7<\/a>Updating a file<\/h3>\n\n\n\n
    Now that we understood the internals we can use the plumbing to update our file.<\/p>\n\n\n\n
    echo "foobar" > myfile.txt\ngit add myfile.txt\ngit commit -m "fix: something"\ngit push\n<\/code><\/pre>\n\n\n\n
    After our “initial commit” we now have a .git<\/code> with one object and one commit. When we update the file, add it and commit it, we create a new object, a new tree and a new commit. Then we push it to the current branch. The new commit will become the new HEAD.<\/p>\n\n\n\n
    \"\"\/<\/figure>\n\n\n\n
    As you can see a new commit is a full copy of the file tree, and all previous objects are still there. So when you switch to a branch, checkout a commit or a file, you are getting all the information inside a single commit hash.<\/p>\n\n\n\n
    \u00a7<\/a>Git is not storing patches<\/h3>\n\n\n\n
    After 10 years of using Git, I realised something, it is not storing patches. Naively when you read a git diff or a pull request, you might think like me, that git is storing a diff between A and B and just displays it when needed. But in reality it stores the full file in the object storage. So each modification is just a new file<\/strong> (or object), described by an hash, added to a file tree and linked to a commit hash.<\/p>\n\n\n\n
    That also means at any given time, git has a copy of all your files since the beginning of the project. If you have rewrote a file 999 times, there is 999 times copy of this file in the object storage. That’s why it can be slow to clone a git repository.<\/p>\n\n\n\n
    All of this combined, it has many advantages:<\/p>\n\n\n\n
      \n
    1. You can follow history by just following an object hash and its parent hash, like a linked list<\/li>\n\n\n\n
    2. You can navigate history instantly without having to recompute any diff<\/li>\n\n\n\n
    3. You can switch to a branch by changing the HEAD hash<\/li>\n\n\n\n
    4. You can rebase by changing a single parent hash<\/li>\n\n\n\n
    5. You can diff two versions years appart because you have access to the full files<\/li>\n\n\n\n
    6. You can revert to any version by changing a single hash<\/li>\n\n\n\n
    7. You can rewrite history by changing a single parent hash<\/li>\n<\/ol>\n\n\n\n
      Using git clone --depth 1<\/code> allows you to fasten your clone by not downloading all the history<\/p>\n\n\n\n
      “Storing everything at each update” sounds counter-intuitive and naive, but it is actually the most efficient solution in this scenario. Especially considering storage is cheap and computation is costly and slow.
      <\/p>\n\n\n\n
      \n\n\n\n
      \u00a7<\/a>Implementation<\/h2>\n\n\n\n
      Following all of this discovery, I came up with this naive and simple implementation in Postgres. I didn’t use git directly because it’s hard to maintain a git layer, especially in a short-lived cloud based environment, and I didn’t need all the features. Calling it a git-like is definitely far fetched, but the main idea is:<\/p>\n\n\n\n
        \n
      1. everything is stored in a global object storage<\/strong><\/li>\n\n\n\n
      2. identified by a unique hash<\/strong><\/li>\n\n\n\n
      3. each update contains the whole object<\/strong><\/li>\n\n\n\n
      4. immutable<\/strong><\/li>\n<\/ol>\n\n\n\n
        \u00a7<\/a>Blob storage<\/h3>\n\n\n\n
        A “blob” (or binary large object) is basically a any data object in binary (e.g: text, image, video, etc.). In this blog post, I will use this name to refer to versionned objects. I didn’t use the name “objects” to avoid any confusion, because it’s a very generic term used in many languages and especially in Javascript.<\/p>\n\n\n\n
        \"\"\/<\/figure>\n\n\n\n
        To store all our blobs, we have one table identified by an unique random id blob_id<\/code> (my equivalent of a git hash<\/code>). I didn’t used a content hash for the id because I didn’t really care for integrity at the time but it could definitely be a fingerprint instead of a random string.<\/p>\n\n\n\n
        The blob_id<\/code> is used has a Foreign Key in every other tables to determine their HEAD, so each row has it’s own HEAD.<\/p>\n\n\n\n
        The content<\/code> column will store any type of content, we don’t care what is inside it’s only relevant to the associated table. Remember we are aiming to be very generic. It is a json<\/code> type; we could have stored the content in a text<\/code> field but it was handy to have access to the data. At some scale it might definitly be interesting to use a blob<\/code> field.<\/p>\n\n\n\n
        The author_id<\/code> and created_at<\/code> capture by who and when the change was made.<\/p>\n\n\n\n
        \u00a7<\/a>How it works?<\/h2>\n\n\n\n
        Let’s explain all of this with a more down to earth example. Let’s say we are creating a blog. We have a blobs<\/code> table that will contain our blobs, and a posts<\/code> table will contain our blog posts.<\/p>\n\n\n\n
        \u00a7<\/a>Creating a row<\/h3>\n\n\n\n
        When we create a new row, we create a blob, that gives us a blob_id<\/code>. We can now create a new row in the appropriate table and reference the blob_id<\/code> to log where the HEAD is.<\/p>\n\n\n\n
          \n
        1. <\/li>\n<\/ol>\n\n\n\n
          INSERT INTO blobs (blob_id, parent_id, content)\n  VALUES ("blob_a", null, "{title: 'Foobar'}");\n\nINSERT INTO posts (id, blob_id, title)\n  VALUES ("post_a", "blob_a", "Foobar");\n<\/code><\/pre>\n\n\n\n
          \"\"\/<\/figure>\n\n\n\n
          \u00a7<\/a>Updating a row<\/h3>\n\n\n\n
          We now have a row post_a<\/code> versionned by blob_a<\/code>. We want to update the title, and thus create a new version. To do that, we create a new blob referencing its parent with the new values, and update post_a<\/code>.<\/p>\n\n\n\n
            \n
          1. <\/li>\n<\/ol>\n\n\n\n
            INSERT INTO blobs (blob_id, parent_id, content)\n  VALUES ("blob_b", "blob_a", "{title: 'Hello World'}");\n\nUPDATE posts SET blob_id  = 'blob_b', title = 'Hello world' WHERE id = 'post_a';\n<\/code><\/pre>\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            \u00a7<\/a>Reverting a row<\/h3>\n\n\n\n
            Good thing with this system, it’s rather simple to go back in time and revert to a previous blob. Depending on the level of transparency you want to achieve, we can revert by simply using the hash (#1) or by copying the previous blob (#2).<\/p>\n\n\n\n
            \"\"\/<\/figure>\n\n\n\n
            \u00a7<\/a>Deleting a row<\/h3>\n\n\n\n
            Deleting a versionned object can be done in many ways depending on what you are looking for.<\/p>\n\n\n\n
            \u00a7<\/a>Option #1 – Soft delete<\/h4>\n\n\n\n
            The classic way. Add a deleted_at<\/code> field in the posts<\/code> table and update this field independently. It’s quick and dirty but you don’t keep history.<\/p>\n\n\n\n
            UPDATE posts SET deleted_at = '2023-10-20' WHERE id = 'post_a';\n<\/code><\/pre>\n\n\n\n
            \u00a7<\/a>Option #2 – Soft versionned delete<\/h4>\n\n\n\n
            Add a deleted_at<\/code> field in the posts<\/code> table and treat delete as a regular update, so you create new blob with the field.<\/p>\n\n\n\n
            INSERT INTO blobs (blob_id, parent_id, content)\n  VALUES ("blob_c", "blob_b", "{title: 'Hello World', deleted_at: '2023-10-20'}");\n\nUPDATE posts SET blob_id  = 'blob_c', deleted_at = '2023-10-20' WHERE id = 'post_a';\n<\/code><\/pre>\n\n\n\n
            \u00a7<\/a>Option #3 – Hard versionned delete<\/h4>\n\n\n\n
            Add a deleted<\/code> boolean field in the blobs<\/code> table and delete the original row. This keep the posts<\/code> table clean while still keeping history for transparency or revert.<\/p>\n\n\n\n
              \n
            1. <\/li>\n<\/ol>\n\n\n\n
              INSERT INTO blobs (blob_id, parent_id, content, deleted)\n  VALUES ("blob_c", "blob_b", "{title: 'Hello World'}", true);\n\nDELETE posts WHERE id  = 'post_a';\n<\/code><\/pre>\n\n\n\n
              \"\"\/<\/figure>\n\n\n\n
              \u00a7<\/a>Option #4 – Full hard delete<\/h4>\n\n\n\n
              The whole point of this system is to keep history so it’s a bit counter intuitive to hard delete everything, except to be compliant with GDPR (and local alternatives). For this case I would recommend adding another column to identify the organisation_id or user_id so that global deletion doesn’t require listing all object ids.<\/p>\n\n\n\n
              DELETE blobs WHERE content->>'id' = 'post_a' ;\nDELETE posts WHERE id = 'post_a';\n<\/code><\/pre>\n\n\n\n
              \u00a7<\/a>Listing versions<\/h3>\n\n\n\n
              If you want to build a way for your user to list versions, revert or diff in the past it’s rather straigth forward. You might want to optimise the query by creating an index with fields your regularly filter with. On my side, I choosed to duplicate the columns with the classic type + type_id<\/code>.<\/p>\n\n\n\n
              SELECT *\nFROM blobs\nWHERE content->>'id' = 'post_a'\nORDER BY created_at DESC\n<\/code><\/pre>\n\n\n\n
              \u00a7<\/a>Batching<\/h3>\n\n\n\n
              A concept of branch or pull request<\/strong> can be easily replicated with an additional table. This table “branch” has a blob_ids<\/code> array column that will hold many blob_id<\/code>. Those blobs are not created in the source table until we decide to do so.<\/p>\n\n\n\n
              \"\"\/<\/figure>\n\n\n\n
              This is very handy to batch multiple updates to many different objects, or to allow the creation of multiple new objects in a single transaction. Specfy use this to allow users to modify everything, create a batch of change that is reviewable and diffable, like a pull request but for content.<\/p>\n\n\n\n
              \"\"\/<\/figure>\n\n\n\n
              \n\n\n\n
              \u00a7<\/a>Table migration<\/h2>\n\n\n\n
              Now you might be wondering, we have N copies of our data, but only the original table has a versionned schema. If we add or drop a column, what happen to my now outdated blobs?<\/p>\n\n\n\n
              You have multiples choices here:<\/p>\n\n\n\n
              \u00a7<\/a>Versionned blobs<\/h3>\n\n\n\n
              Store the table version in the blob (or the date of last migration for example) to be able to determine if the schema is still compatible or not. This can help to adapt the API or UI that is built on top of this data, but requires keeping a strategy for all schema version. And not all schema version are incompatible, for example adding a new nullable column doesn’t change anything to the previous blobs.<\/p>\n\n\n\n
              I do believe it’s the hard way, and something you wouldn’t do anyway with other alternatives.<\/p>\n\n\n\n
              \u00a7<\/a>Schema migration + Data migration<\/h3>\n\n\n\n
              Another possible way of dealing with migration: when you migrate your table, you also migrate your data, the data being basically a copy of the schema it would be the same migration translated to JSON.<\/p>\n\n\n\n
              -- #1 Adding a nullable column\n-- Doesnt require additional fix for the data\nALTER TABLE "posts" ADD COLUMN "description" VARCHAR(250);\n<\/code><\/pre>\n\n\n\n
              -- #2 Dropping a column\n-- Requires us to also delete the field in the blobs\nALTER TABLE "posts" DROP COLUMN "description";\n\nUPDATE "blobs" SET content = sub.next\nFROM (SELECT id, content::jsonb - 'description' AS next FROM "Blobs") AS sub\nWHERE "blobs".id = sub.id;\n<\/code><\/pre>\n\n\n\n
              You might understand now that this the biggest drawback<\/strong> to this solution. The problem of schema drift exists with all versioning solution<\/strong> but this might be one of the slowest. And updating millions\/billions of rows can range from slow to impossible, so you might want to do it asynchronously or find another way to deal with it.<\/p>\n\n\n\n
              For example, in Specfy, I have an array of ignored fields<\/strong>, so that when I merge an old blob into a row I can just omit some fields at insert time.<\/p>\n\n\n\n
              \/\/ Pseudo code\n\n\/\/ List of previously existing fields that are now dropped\nconst ignored = ['description'];\n\n\/\/ select the row\nconst row = SELECT * FROM "blobs" WHERE blob_id = "blob_a";\n\n\/\/ filter deleted columns\nconst filtered = omit(row.content, ignored);\n\n\/\/ Update the original table\nUPDATE `posts` ${...filtered} WHERE id = row.content.id;\n<\/code><\/pre>\n\n\n\n
              \n\n\n\n
              \u00a7<\/a>Conclusion<\/h2>\n\n\n\n
              I feel like it’s a nice alternative to what we often do. It’s easy to use and agnostic<\/strong>. Want to version a new table? Nothing else to do, the system is already there. All the versioning can be done within a single system, in a single transaction<\/strong>. One table, no other API, no other system to maintain. It scales with your Postgres and can eventually be partionned or sharded.<\/p>\n\n\n\n
              It has a major drawback, the migration of data<\/strong>. This is a common issue with all versioning system, but it’s especially complicated when deleting or updating a column type. If you plan on modifying the schema very often, it might not be the best choice.<\/p>\n\n\n\n
              This is currently used by Specfy to version components, projects, documents, etc<\/strong>. With that, we created a notification system<\/strong> that always point to the appropriate version in time without duplication. Our revisions feature<\/strong> (pull request equivalent) was also built on top of this, it allows users to create temporary workspace, where they can branch from the HEAD of any data objects, update it, diff it, and merge it when they are are ready.
              <\/p>\n\n\n\n
              \n\n\n\n
              \u00a7<\/a>Alternatives in Postgres<\/h2>\n\n\n\n
              \u00a7<\/a>In-Table versioning<\/h3>\n\n\n\n
              This is the WordPress way of doing thing. You have a posts<\/code> table with a column version<\/code> and SELECT the maximum version. It’s simple and doesn’t require maintaining multiple schema or ressources. However it has massive drawback in term of performance, and query simplicity. The table will inevitably grow, and SELECT needs to have an order by which can make joining\/grouping\/aggregating harder or slower.<\/p>\n\n\n\n
              \u00a7<\/a>Outside versioning<\/h3>\n\n\n\n
              You can use a NoSql database or even filesystem to version your objects, which can double as backup. However you now need to maintain another API and storage which can be down or require independent maintenance. And you are even at bigger risk of schema drift.<\/p>\n\n\n\n
              \u00a7<\/a>Copy table versioning<\/h3>\n\n\n\n
              This is the most simple and efficient alternative. Create a quasi equivalent copy of the table your are versioning, migration is almost 1:1. However you still need to add metadata fields, disable or rename primary key to allow the same id<\/code> to be inserted multiple times. And you obviously need one table per versionned table.
              <\/p>\n\n\n\n
              \n\n\n\n
              \u00a7<\/a>Additional questions<\/h2>\n\n\n\n
              \u00a7<\/a>Why not using only the “blobs” table directly?<\/h3>\n\n\n\n
              For many reasons:<\/p>\n\n\n\n
                \n
              1. this table is not optimised for querying except by id<\/code>.<\/li>\n\n\n\n
              2. table will grow subsequently so any other mean of query will be slower and slower over time<\/li>\n\n\n\n
              3. indexing other fields requires jsonb<\/code> data type, that is slower and heavier in memory<\/li>\n\n\n\n
              4. foreign key checks in json<\/code> are lost<\/li>\n\n\n\n
              5. json<\/code> syntax is not that well supported in all the tooling<\/li>\n<\/ol>\n\n\n\n
                \u00a7<\/a>Why do you spread the fields in the end table and not just copy the json?<\/h3>\n\n\n\n
                Basically for the same reason as the previous question. Jsonb in Postgres is nice and performant but will always be slower than regular access. You also loose foreign key checks and it’s simply easier in most tooling.<\/p>\n\n\n\n
                Having regular fields allow us to control the schema with regular tools.<\/p>\n\n\n\n
                \u00a7<\/a>Is there any database alternatives or extension?<\/h3>\n\n\n\n
                For me not using Postgres was not a question, but you might be in a different situation. Please mind I have not tried all those alternatives:<\/p>\n\n\n\n
                  \n
                1. Neon neon.tech<\/a> (Open Source)<\/li>\n\n\n\n
                2. Dolt www.dolthub.com<\/a> (Open Source)<\/li>\n\n\n\n
                3. temporal_tables (Postgres extension) pgxn.org<\/a><\/li>\n\n\n\n
                4. postgresql-tableversion (Postgres extension) github.com\/linz<\/a><\/li>\n<\/ol>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
                  Versioning data in Postgres? Testing a git like approach – Specfy is fashinating but I think that most of the time these two proposed alternatives fit most of the needs:<\/p>\n
                  Read more →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[278],"tags":[303],"class_list":["post-10940","post","type-post","status-publish","format-standard","hentry","category-tricks","tag-postgresql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6daft-2Qs","jetpack-related-posts":[{"id":9759,"url":"https:\/\/monodes.com\/predaelli\/2022\/10\/24\/6-popular-postgresql-cli-libraries\/","url_meta":{"origin":10940,"position":0},"title":"6 Popular PostgreSQL CLI Libraries","author":"Paolo Redaelli","date":"2022-10-24","format":false,"excerpt":"6 Popular PostgreSQL CLI Libraries pgcli - Postgres CLI with autocompletion and syntax highlighting pgsh - Branch your PostgreSQL Database like Git psql - The built-in PostgreSQL CLI client psql2csv - Run a query in psql and output the result as CSV nancy - The Nancy CLI is a unified\u2026","rel":"","context":"In "Documentations"","block_context":{"text":"Documentations","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":9598,"url":"https:\/\/monodes.com\/predaelli\/2022\/09\/14\/choosing-a-postgres-primary-key\/","url_meta":{"origin":10940,"position":1},"title":"Choosing a Postgres Primary Key","author":"Paolo Redaelli","date":"2022-09-14","format":"link","excerpt":"\u00a0Choosing a Postgres Primary Key Choosing a Postgres Primary Key 2022-09-08 \u2022 24 minute read VictorGuest Author Primary keys are important. They uniquely identify rows of data in tables, and make it easy to fetch data. The job of a database is to archive and recall data and you're going\u2026","rel":"","context":"In "Documentations"","block_context":{"text":"Documentations","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3743,"url":"https:\/\/monodes.com\/predaelli\/2018\/02\/03\/postgres-hidden-gems-craig-kerstiens\/","url_meta":{"origin":10940,"position":2},"title":"Postgres hidden gems – Craig Kerstiens","author":"Paolo Redaelli","date":"2018-02-03","format":false,"excerpt":"citext I've always been fond of PostgreSQL, now this Postgres hidden gems - Craig Kerstiens shows its smart features even more! There are many interesting features of Postgresql that I didn't knew, as I haven't actually used it for a while Postgres has a rich set of features, even when\u2026","rel":"","context":"In "Documentations"","block_context":{"text":"Documentations","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":11886,"url":"https:\/\/monodes.com\/predaelli\/2024\/09\/01\/git-copy-a-file-or-directory-from-another-repository-preserving-the-history\/","url_meta":{"origin":10940,"position":3},"title":"Git: Copy a file or directory from another repository preserving the history","author":"Paolo Redaelli","date":"2024-09-01","format":"quote","excerpt":"How to copy a file or directory from another GIT repository while preserving its history? Internet is full of magic formulas each one more complex. Here I\u2019m proposing a much simpler and faster one that is to make a git format-patch for the entire history of the file or subdirectory\u2026","rel":"","context":"In "Tricks"","block_context":{"text":"Tricks","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/tricks\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":14199,"url":"https:\/\/monodes.com\/predaelli\/2025\/10\/26\/against-sql\/","url_meta":{"origin":10940,"position":4},"title":"Against SQL","author":"Paolo Redaelli","date":"2025-10-26","format":false,"excerpt":"Against SQL Against SQL Published 2021-07-09 TLDR The relational model is great: A shared universal data model allows cooperation between programs written in many different languages, running on different machines and with different lifespans. Normalization allows updating data without worrying about forgetting to update derived data. Physical data independence allows\u2026","rel":"","context":"In "Documentations"","block_context":{"text":"Documentations","link":"https:\/\/monodes.com\/predaelli\/category\/documentations\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":8922,"url":"https:\/\/monodes.com\/predaelli\/2021\/11\/18\/an-introduction-to-the-pg_auto_failover-project\/","url_meta":{"origin":10940,"position":5},"title":"An introduction to the pg_auto_failover project","author":"Paolo Redaelli","date":"2021-11-18","format":"link","excerpt":"\u201cPostgres is awesome, makes developping my application so much easier, it solves so many problems for me!\u201d and the next step \u201cso, how do I run Postgres in Production?\u201d. If you\u2019re not sure how to bridge that gap yourself, how to deploy your first production system with automated failover, then\u2026","rel":"","context":"In "Senza categoria"","block_context":{"text":"Senza categoria","link":"https:\/\/monodes.com\/predaelli\/category\/senza-categoria\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/posts\/10940","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/comments?post=10940"}],"version-history":[{"count":0,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/posts\/10940\/revisions"}],"wp:attachment":[{"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/media?parent=10940"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/categories?post=10940"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/monodes.com\/predaelli\/wp-json\/wp\/v2\/tags?post=10940"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}