Postgres Pro
Downloads
Home   >   mailing lists

Thread: Read-only tables to avoid row visibility check

Read-only tables to avoid row visibility check

From
Seamus Abshere
Date:
hi,

https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
of `ALTER TABLE table SET READ ONLY`.

Would this mean that row visibility checks could be skipped and thus
index-only scans much more common?

Thanks,
Seamus

--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere

Re: Read-only tables to avoid row visibility check

From
Melvin Davidson
Date:
First of all, it would be really nice if you mentioned the version of PostgreSQL and O/S when posing questions.

That being said, that wiki is a _discussion_, and as such, a suggestion on how it "might" be implemented.

However, at this time, there is no such option as SET READ ONLY in any version of PostgreSQL.

The best you can do is revoke INSERT, UPDATE & DELETE from public and all specific granted users.

On Mon, Feb 22, 2016 at 3:35 PM, Seamus Abshere <seamus@abshere.net> wrote:
hi,

https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
of `ALTER TABLE table SET READ ONLY`.

Would this mean that row visibility checks could be skipped and thus
index-only scans much more common?

Thanks,
Seamus

--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Read-only tables to avoid row visibility check

From
Seamus Abshere
Date:
On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote:
> However, at this time, there is no such option as SET READ ONLY in any version of PostgreSQL.

I know.

I am wondering if hypothetical read-only tables would make index-only
scans more possible by avoiding the need for row visibility checks.


--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere

Re: Read-only tables to avoid row visibility check

From
"David G. Johnston"
Date:
On Mon, Feb 22, 2016 at 2:38 PM, Seamus Abshere <seamus@abshere.net> wrote:
On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote:
> However, at this time, there is no such option as SET READ ONLY in any version of PostgreSQL.

I know.

I am wondering if hypothetical read-only tables would make index-only
scans more possible by avoiding the need for row visibility checks.

​If the system is working properly then a READ ONLY table in fact should be able to use Index Only Scans without the hack of a DBA telling it that said table is READ ONLY.​  The presence or absence of such an attribute should not impact that particular optimization.  Corner cases such as, "it was READ ONLY so I made it writable, wrote to it, then changed it back to READ ONLY", have not been considered here.

​Since the answer to your actual question would be "it depends on the implementation" it would probably be more constructive to actually communicate the thoughts that provoked the question.

David J.​

Re: Read-only tables to avoid row visibility check

From
Seamus Abshere
Date:
On Mon, Feb 22, 2016, at 06:48 PM, David G. Johnston wrote:
> it would probably be more constructive to actually communicate the thoughts that provoked the question.

My company has a largish table - 250+ columns, 1 row for every household
in the US. It's read-only. We've gotten advice to convert to a column
store (cstore_fdw, etc.) but we would love to just stay with
tried-and-true postgres tables. Plus, many of our queries are against
dozens of columns at once.

Being able to tell postgres that our table is "Read Only" has imaginary
mystical properties for me, first and foremost being able to count
against indexes without ever hitting the disk.

> ​If the system is working properly then a READ ONLY table in fact should be able to use Index Only Scans without the
hackof a DBA telling it that said table is READ ONLY.​ 

So this should happen already?

Re: Read-only tables to avoid row visibility check

From
Merlin Moncure
Date:
On Mon, Feb 22, 2016 at 2:35 PM, Seamus Abshere <seamus@abshere.net> wrote:
> hi,
>
> https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
> of `ALTER TABLE table SET READ ONLY`.
>
> Would this mean that row visibility checks could be skipped and thus
> index-only scans much more common?

Personally I don't see how that buys you very much.  Right now you can
VACUUM the table which will update the visibility map, allowing index
only scans to be chosen.  Visibility checks are also already optimized
away by the database over time via hint bits.

I think the optimization you are looking for is to have the database
exploit the fact that when the table is created and/or loaded in a
single transaction, it marks everything visible and valid by default
and then wipes it all away should the insert fail.

merlin

Re: Read-only tables to avoid row visibility check

From
Tom Lane
Date:
Seamus Abshere <seamus@abshere.net> writes:
> Being able to tell postgres that our table is "Read Only" has imaginary
> mystical properties for me, first and foremost being able to count
> against indexes without ever hitting the disk.

>> ​If the system is working properly then a READ ONLY table in fact should be able to use Index Only Scans without the
hackof a DBA telling it that said table is READ ONLY.​ 

> So this should happen already?

Yeah.  Index-only scans will work if all (or at least most) of the table
hasn't been modified since the last VACUUM.  If we had a READ ONLY
property, I do not think it would affect that logic at all; it would just
prevent future mods going forward.  Which, as noted, you could already do
by revoking suitable privileges.

            regards, tom lane