JSON
Backfilling Historic Events After a Schema Change Safely
Three ways to rewrite a renamed JSON property across 1.5 million events in PostgreSQL: one UPDATE, batches and a table swap, with WAL, bloat and leftovers.
7 min read
Tag
schema-change
14 posts
JSON
Designing JSON Event Properties for Analysis From Day One
Four designs for the same 500,000 purchase events compared on size and query time: verbose nesting, short flat keys, hybrid columns and key/value arrays.
6 min read
Excel
Access Versus SQLite Versus PostgreSQL for a Small App
Compare Microsoft Access, SQLite and PostgreSQL for a small team replacing a spreadsheet, by users, platform, interface, limits, safety and upgrade path.
7 min read
Excel
Audit Trails and History That Excel Cannot Give You
Excel cannot say who changed a cell and why. See what version history does, and build an audit trail in PostgreSQL with triggers or temporal tables.
8 min read
Excel
Shared Excel Files and the Lost Update Problem Explained
How two people editing the same Excel file lose each other's changes, what co-authoring and locks do about it, and how a database prevents lost updates.
7 min read
Excel
Keeping Excel as the Front End to a Database Safely
Keep Excel for reading and analysing database data through queries and refresh, and decide how writes should reach the database without losing rules.
7 min read
Excel
Turning Repeating Columns Into a Proper Child Table
Replace columns like Phone 1, Phone 2, Phone 3 from an Excel sheet with a child table: unpivot in SQL or pandas, drop blanks, add keys and check the counts.
7 min read
Excel
Designing Database Tables From a Messy Excel Sheet
Turn a wide Excel sheet into database tables: find the entities, choose keys, remove repeated groups and derived columns, and check the design with queries.
8 min read
Excel
Moving From Excel to PostgreSQL Step by Step Without Loss
Move an Excel workbook into PostgreSQL: staging table, clean, normalized tables, constraints, roles, a verified load, and Excel as the read connection.
7 min read
Excel
Moving From Excel to SQLite for a Small Team Step by Step
Move an Excel list into SQLite: clean it, design tables, load with Python, add constraints, query from Excel and know the limits for a small team.
7 min read
Excel
Ten Signs Your Excel File Needs to Become a Database
Ten concrete signs that an Excel file has become a database, from several editors to lost updates and broken lookups, with a quick test for each.
7 min read
ExcelGuide
When a Spreadsheet Has Become a Database and What to Do
How to tell that an Excel file now behaves as a database, choose SQLite, Access or PostgreSQL, design tables, migrate safely and keep Excel as a reader.
17 min read