51–60 of 81 posts
Earlier quoted context omitted.
Another benefit of partial indexes is to limit a unique constraint: create index users_email on users(email) where status != 'delete'
Be very careful, then, as the optimizer will (usually?) not use the index if the condition is not part if the query.
> Clear bloat in tables Ohh, we've had issues with this. We have this table that's mostly ephemeral data, so rows are constantly inserted and then deleted after a certain amount of time. Due to a bug the deletion didn't work for a while and the db grew very large. Fixed the deletion, but no amount of vacuuming actually allows us to fully reclaim that space so we don't have to pay for it. At the same time the extra co…
Another solution is tombstoning data so you never actually do a DELETE, and partial indexes go a long way to making that scale. It removes the logn cost of all of the dead data on every subsequent insert.
Earlier quoted context omitted.
Actually it's the opposite of database normalization. Normalizing removes data redundancy. This adds data redundancy. When I design a database structure, it's common to start with the most normalized representation possible. And then to denormalize the minimum necessary for performance reasons -- duplicating rows and/or columns just like here so certain data can be retrieved more quickly, whenever indexes aren't powe…
I think what lucian1900 may be thinking is that instead of create table purchase_order ( id int primary key, ordered_on timestamptz not null, customer_id int not null references customer, canceled_on timestamptz ); you could have create table purchase_order ( id int primary key, ordered_on timestamptz not null, customer_id int not null references customer ); create table order_cancelation ( order_id int primary key r…
This has nothing to do with the content, but the design of this page really stuck out to me. It's very easy to read and doesn't have fluff. But it still feels modern (in the good way). It's perfectly balanced.
Earlier quoted context omitted.
Actually it's the opposite of database normalization. Normalizing removes data redundancy. This adds data redundancy. When I design a database structure, it's common to start with the most normalized representation possible. And then to denormalize the minimum necessary for performance reasons -- duplicating rows and/or columns just like here so certain data can be retrieved more quickly, whenever indexes aren't powe…
I think what lucian1900 may be thinking is that instead of create table purchase_order ( id int primary key, ordered_on timestamptz not null, customer_id int not null references customer, canceled_on timestamptz ); you could have create table purchase_order ( id int primary key, ordered_on timestamptz not null, customer_id int not null references customer ); create table order_cancelation ( order_id int primary key r…
Earlier quoted context omitted.
> The database is obviously not able to tell that there will never be any values above 7 You say "obviously", but with updated statistics this is the exactly the kind of thing you might expect the planner to know and aid index decisions. I'm a huge fan of Postgres, coming to it around 5 years ago from at least 10 previous years with SQL Server, but I have hit a few things like this in that time. IME the planner is mu…
No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics. You could argue that it should be able to use it if the table has a check constraint preventing severity_id above 7 being ever inserted. That is something that could be done, I don't know if PostgreSQL does it (I doubt it…
The only system I've heard of that relies on up-to-date statistics for correctness is snowflake (long but interesting talk here [2]), where having accurate max/mins for each micro partition is really helpful for cutting down the amount of data in the large range scan queries common in BI. I'd guess that being a BI system, snowflake can get away with higher row update latency too.
[1] https://www.sqlshack.com/sql-server-statistics-and-how-to-pe...
> Clear bloat in tables Ohh, we've had issues with this. We have this table that's mostly ephemeral data, so rows are constantly inserted and then deleted after a certain amount of time. Due to a bug the deletion didn't work for a while and the db grew very large. Fixed the deletion, but no amount of vacuuming actually allows us to fully reclaim that space so we don't have to pay for it. At the same time the extra co…
The problem we always ran into with deletes is them triggering full table scans because our indexes weren't set up correctly to test foreign key constraints properly. Constant game of whack-a-mole that everyone quickly grew tired of. Also more indexes increases the slope of the line for insert operations as data size grows. Another solution is tombstoning data so you never actually do a DELETE, and partial indexes go…
This is a classic case where partitioning shines. Lets say those are logs. You partition it monthly and want to retain 3 months of data.
- M1 - M2 - M3
When M4 arrives you drop partition M1. This is a very fast operation and the space is returned to the OS. You also don't need to vacuum after dropping it. When you arrive at M5 you repeat the process by dropping M2.
> Another solution is tombstoning data so you never actually do a DELETE, and partial indexes go a long way to making that scale. It removes the logn cost of all of the dead data on every subsequent insert.
If you are referring to PostgreSQL then this would actually be worse than outright doing a DELETE. PostgreSQL is copy on write so an UPDATE to a is_deleted column will create a new copy of the record and a new entry in all its indexes. The old one would still need to be vacuumed. You will accumulate bloat faster and vacuums will have more work to do. Additionally, since is_deleted would be part of partial indexes like you said, a deleted record would also incur a copy in all indexes present on the table.
Compare that to just doing the DELETE which would just store the transaction ID of the query that deleted the row in cmax and a subsequent vacuum would be able to mark it as reusable by further inserts.
Earlier quoted context omitted.
> The database is obviously not able to tell that there will never be any values above 7 You say "obviously", but with updated statistics this is the exactly the kind of thing you might expect the planner to know and aid index decisions. I'm a huge fan of Postgres, coming to it around 5 years ago from at least 10 previous years with SQL Server, but I have hit a few things like this in that time. IME the planner is mu…
No, I don't think statistics can let you get away with this. Databases are concurrent, you can't guarantee that a different session will not insert a record that invalidates your current statistics. You could argue that it should be able to use it if the table has a check constraint preventing severity_id above 7 being ever inserted. That is something that could be done, I don't know if PostgreSQL does it (I doubt it…
Of course you can't make a guarantee like that, but why would you need to? Statistics are there to guide planner choices, not make cast iron predictions.