41–50 of 92 posts
This is true in a sense, but these are commonly stored compressed and of course you can expect to have a pretty good compression ratio when changed files are mostly similar. And if they're not, you really have to store a copy anyway.
I just taught one of my developers how to use a basic Oracle Flashback query to pull a historical version of a table. This is my cheat sheet: ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; select count(*) from dual AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00'); I was aware that Postgres had "time travel," but I don't know if it used this syntax, but I am aware that the feature has been remove…
Been a feature of SQL Server since I believe SQL Server 2008 - temporal tables. They are used (incorrectly) at my place of work quite a bit. If auditing data (who changed what and when) is important - there are two choices: 1) system versioning or 2) add a "version" column or something similar which increments with each change. There are tradeoffs for both. "Temporal tables (also known as system-versioned temporal ta…
In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few years now. I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository. https://github.com/simonw/simonwillisonblog-backup This gives…
In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few years now. I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository. https://github.com/simonw/simonwillisonblog-backup This gives…
Earlier quoted context omitted.
You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
Poster said oracle not PG
Earlier quoted context omitted.
Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracl...
> I was writing Oracle queries that could make your head spin Absolutely, and some of the things Tom Kyte (Oracle's resident DB performance guru) could do with Oracle were spectacular. As you say, its a shame about the Oracle baggage, and in particular the steep price tag. Otherwise I'm certain it would be far more widely deployed as database.
You can also license it by user.
https://www.oracle.com/assets/technology-price-list-070617.p...
Earlier quoted context omitted.
Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracl...
Using hints is typically a sign of failure. The DB optimiser should typically not need them .
Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system. Even databases are general-purpose machines. That's the whole point of adding indexes, etc.
I mean, has there ever been a database that auto-adds indexes based on profiler/optimizer feedback? (To answer my own question, apparently Azure SQL does).
But I realized pg_largeobject (1) chunks data to around 2kB per chunk, and (2) stores each chunk on its own row, and (3) each row uses "oid" type as identifier, which is just 32 bit long. It's probably large enough for anything I would ever need, but for some reason I don't feel comfortable with only 32 bits as primary key.
https://neon.tech/docs/introduction/branching is exactly what the author is looking for and is the best way to do highly efficient point-in-time versioning of Postgres data I have seen so far. I deployed NeonDB in a large enterprise client in Q2 of this year and, yes, the initial data migration is the hard part. We added 2x100gbe network cards directly on the VMware hosts where the data was so the migration to the Ku…
Earlier quoted context omitted.
That's interesting. Seems to be made for CI, testing, not inside a single production though.
Neon is designed to run in production. It runs on top of out storage that is designed for webscale and production usage.