In this blog we are talking about indexes. Speeding up index creation in PostgreSQL is one of the main topic.
Postgresql
Versioning data in Postgres? Testing a git like approach – Specfy
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:
- In-Table versioning, the WordPress way of doing thing. Add a a column
version(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. - 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
idto be inserted multiple times. And you obviously need one table per versionned table.
Upsert in SQL
Inserting and updating data with a single query.
Source: Upsert in SQL
SQLedge
SQLedge: Replicate Postgres to SQLite on the Edge – https://github.com/zknill/sqledge
An introduction to the pg_auto_failover project
“Postgres is awesome, makes developping my application so much easier, it solves so many problems for me!” and the next step “so, how do I run Postgres in Production?”. If you’re not sure how to bridge that gap yourself, how to deploy your first production system with automated failover, then pg_auto_failover is for you.
Source: An introduction to the pg_auto_failover project
I may need it someday