Changes Coming For PostgreSQL 9.5

[phoronix]

Phoronix: Changes Coming For PostgreSQL 9.5

The PostgreSQL 9.5 release change-log was recently updated in Git to reflect all of the latest changes for this next version of this database server due out in 2016...

http://www.phoronix.com/scan.php?page=news_item&px=PostgreSQL-9.5-Changes-So-Far

[uid313]

Microsoft SQL Server can do data masking.

You can set it on certain columns. Such as when you do a SELECT query on a table, you can get certain data in certain columns masked.
Example you can prevent developers (or some group) to have full access to customer data.

Example make all email addresses show up as "*@example.com" or "bob@*.com" with asterisk to mask the data.
This can be set on PII fields such as phone number, email address, social security number, password hashes, etc.

[Archprogrammer]

Microsoft SQL Server can do data masking.

You can set it on certain columns. Such as when you do a SELECT query on a table, you can get certain data in certain columns masked.
Example you can prevent developers (or some group) to have full access to customer data.

I'm not sure why that should be relevant, but you could do essentially the same thing with a view. Just create a view without the masked column and
grant the appropriate permissions to the view and the original table.

[bkor]

Lately I'm using SQL server. I'm just want some simple things out of a database server. The SQL server GUI (called SSMS) is great. Almost any new feature is also added to this GUI.

That said, there's various things that postgres can do that I find lacking in SQL server. E.g. I'd like to have a copy of one table as temp table. In postgres you can have one table rely/extend another table. So you don't have to update multiple tables in case you refresh one. That's a lot cleaner IMO. I haven't found the same yet in SQL server.

SQL server also seems slow to change a table containing loads of data. Seems related to the (IIRC) recovery options. It's much faster in postgres to do this.

[Almindor]

I've worked for a company with billions of rows sized tables that used MS SQL Server when I came in but was just trying to transition to a Linux server which became my sole responsibility.

My predecessor has designed the whole thing with MongoDB in mind which proved to be a horrible idea so we backpedalled into using Linux with PostgreSQL instead.

It proved to be a great success and while it took almost a month to get performance done right with indexes etc. it turned out PostgreSQL outperforms SQL Server in most areas when configured right. The only thing I missed were covered indexes (e.g. you got 5 columns you need to "get" with a specialized query from a huge table, but you only search on 2 of those, so you build an index on 2 columns but it contains the other 3 as "covered" so you don't have to access row data making into an index only search).

One of the biggest boons of postgreSQL compared to at least the older version of SQL Server was the autovacuuming. Indexes also didn't deteriorate so much as with SQL Server.

[adler187]

I'm not sure why that should be relevant, but you could do essentially the same thing with a view. Just create a view without the masked column and
grant the appropriate permissions to the view and the original table.

DB2 has this capability as well. The difference is that with a mask, the query will still return the column, but the data will be masked.

Let's say you have an employee table that contains the employee's SSN. The only people who need to have access to the full SSN is people in payroll, but managers might need to know the last 4 digits. You can create a mask like so:

CREATE MASK SSN_MASK ON EMPLOYEE
   FOR COLUMN SSN RETURN
      CASE
         WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'PAYROLL') = 1)
            THEN SSN
         WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR') = 1)
            THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
         ELSE NULL
      END
   ENABLE;

If you query select SSN from EMPLOYEE you'll get either

123-45-6789 if you're part of the PAYROLL group or
XXX-XX-6789 if you're part of the MGR group or
NULL if you're not in either group

You could do this with views, but it would be more work:
- create a view without the SSN column for users not in PAYROLL or MGR groups
- create a scalar function to mask the data and then create a view passing the SSN column to the scalar function
- update all your application code to determine which group the user is in and query the correct view/table appropriately


DB2 also has PERMISSIONs which are row based access control. This prevents rows from being returned based on whatever criteria you decide, similar to the masks above. You can find out more here: https://www-01.ibm.com/support/knowl...fymaskperm.htm

[stalkerg]

Yes! But my patches will only in 9.6

[fuzz]

The only thing I missed were covered indexes (e.g. you got 5 columns you need to "get" with a specialized query from a huge table, but you only search on 2 of those, so you build an index on 2 columns but it contains the other 3 as "covered" so you don't have to access row data making into an index only search).

Didn't postgres get covering indexes in 9.2? http://www.postgresql.org/about/news/1415/

[Almindor]

Didn't postgres get covering indexes in 9.2? http://www.postgresql.org/about/news/1415/

Sorry about the late reply, I just noticed this post..

I think it was false advertizing. There's nothing in http://www.postgresql.org/docs/curre...eateindex.html for covered indexes and I can't find anything when googling either. I don't think postgres has them.