Hacker News Reader

Comment by chasil | Hacker News Reader

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 removed. I don't know if "AS OF" is still supported. In Oracle, it has been quite helpful.

Edit: Flashback came to Oracle in 9i, when "rollback segments" were replaced by the "undo tablespace" that could present any table as it appeared in the past, limited by the amount of "undo" available. The "FLASHBACK ANY TABLE" privilege is required to see this history on tables that you do not own, and that also conveys the privilege to fully revert tables to their previous contents. You must be a DBA to exercise flashback on tables owned by SYS.

View on HN

Expanding on the the above,

> The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-th...]

Postgres deprecated support for time-travel in ~1997 and the more general notion of "system time" wasn't standardised until SQL:2011. This blog post is a good overview on the SQL:2011 spec + adoption in databases of (bi-)temporal versioning: https://illuminatedcomputing.com/posts/2019/08/sql2011-surve...

Temporal versioning is less sophisticated than git-like versioning (no branching etc.) but is usually more aligned with common end-user requirements. Kent Beck suggests this framing of "eventual business consistency": https://tidyfirst.substack.com/p/eventual-business-consisten...

> (bi-)temporal versioning

I once spent some time trying to find a way to do bi-temporal versioning in Postgres.

The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at.

I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.

A shame indeed, they were only a few decades too early! The support for "Time Varying Data" gets discussed briefly in this 1995 paper by Stonebraker "The Design of Postgres"

> POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable.

> [...] Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version.

https://dsf.berkeley.edu/papers/ERL-M85-95.pdf

Those are cool features, so why were they dis-continued?

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 tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time."

https://learn.microsoft.com/en-us/sql/relational-databases/t...

It was the audit features that got introduced in 2008 C2 audit (or something like this) and change tracking. From what I remember, temporal table was a 2016 feature and more or less confirmed by your link.

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.

It's either free (for XE), $17,500 per cpu (for SE2), or $47,500 per cpu (for Enterprise). x86-64 gets a 2-for-1 core discount.

You can also license it by user.

https://www.oracle.com/assets/technology-price-list-070617.p...

Using hints is typically a sign of failure. The DB optimiser should typically not need them.

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?

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

Total db noob here: is Oracle's query planner better? What's the difference between the two?

This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.

Oracle has query hints; pg does not. Query planner hinters are useful. That's my point.

No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.

You're right, I meant a failure of the DB/optimiser not the person. Thanks.

No they're not perfect but I need them rarely with MSSQL

> An optimizer will quickly run into NP-hard problems like join ordering

As a start, dynamic planning based on cardinality estimation based on stats. And then some. It explodes horribly, yes.

Totally untrue...but adding hints makes your queries dependent on current performance.

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).

It's not automatically creating them AFAIK, but alloydb has an index suggestion tool https://cloud.google.com/alloydb/docs/use-index-advisor

Generally this seems like a nice balance to me - watch real queries, look for expensive plans and sniff out likely missing indexes based on the data distribution, but give the DBA final say on whether it's a good idea

> adding hints makes your queries dependent on current performance

I don't understand this, can you explain.

> Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system

That's inaccurate. As a DBA I can understand the system better at higher level, but the database has statistics which gives it typically better understanding of the data distribution at a lower level. Indeed I could get that information and feed it into the query plan via hints, but that's going to be an enormous amount of my time, and I would have to do it every time the query is run whereas the database can keep an eye on the statistics as it varies and rebuild the query over time.

Equally, if an index is added you can expect the database to start using it immediately without adding hints. Etc

I am a fairly(?) skilled DBA who has a reasonable idea of what goes on underneath the hood, I do have some idea what I'm talking about.

> (To answer my own question, apparently Azure SQL does)

Which is fun when you add a future schema migration that drops a column, test in locally and in some test cases, and then see it bomb in production because the column has an Index that you weren't aware of. (That's an easy thing to solve though, but definitely one of those cases where "The database is messing with its schema on its own" does do funny things)

In a perfect world, I wouldn't disagree; but we don't live in a perfect world where everything always goes your way.

Sometimes the planner needs a little help, and that's OK.

I did say 'typically'! Yes, agreed, but it should rarely need help. Also hints are oh so often jammed in by newbies. I strongly get the impression use of hints is inversely correlated with experience/knowledge.

I'm a greybeard you cheeky scamp ;)

I very rarely used hints in my more database'y days - but every so often, they were needed, and often to tell the database something that seemed screamingly obvious to me.

I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience.

We are in strong agreement, and the phrasing and measure of your post made it pretty obvious you are knowledgeable (another inverse correlation: absolute opinions launched with violence also seemed to be the realm of newbies; and yours was eminently not).

> I very rarely used hints in my more database'y days

evidence thereof!

> I'm not sure where you got the notion that hints were something noobs would be working with? At least, that certainly wasn't my experience.

It's been very, very much my experience. I worked in one place where nolock hints were applied everywhere, without them realising that READ UNCOMMITTED trans iso even existed, so had to show them that, and show them it could be specified at the application layer (not in the SQL). In another recent case I came across their use and I'm damn sure the person who used it didn't actually know what it did. Yeah, I've seen far too much hinting in my career.

Around the same time, I wrote an extremely convoluted MySQL query that changed its plan based on the column order in the SELECT clause.

I wouldn't post snooty and dismissive comments about this - assume that you are in a company of people who have been to more rodeos than you have.

I've >25 years of DB rodeoing. Perhaps I know something about this by now. See my other answers on this thread.

> that changed its plan based on the column order in the SELECT clause.

Hmm? Could you elaborate; you mean the order of the cols or using ORDER BY? I assume you mean the latter but it sounded like the former.

The postgress team is generally opposed to hints. And their reasoning sort of makes sense.

When do you need a hint? why do you need a hint? If you can answer those questions wouldn't it be better to codify those answers into the query planner than a one off hint?

Which nicely sidesteps how hard it actually would be to first, understand the query planner to the degree needed to change it and second, the amount of effort it would take transform a one off hint into a general purpose optimization engine.

Reply on news.ycombinator.com