Tag Info

New answers tagged

1

To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard It shards and replicates your PostgreSQL tables for horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application. ...


0

Yes, this is possible. Here is how Create a copy of the table (you can use the partition management tool for that: https://sqlpartitionmgmt.codeplex.com/) Switch the partition you want to "compress" into the copy Rebuild the indexes on the copy with FF = 100. Switch the partition back into the original table It should be pretty trivial to automate this ...


2

To get partition elimination, your queries need to be explicit about the bit data type, for example: ...WHERE Archivable = CONVERT(bit, 0) -- NOT ...WHERE Archivable = 0 Without this, the risk of truncation in the implicit conversion means partition elimination is not applied. The implicit conversion in the CREATE PARTITION FUNCTION statement is not a ...


0

Unfortunately you insert nav_date greater than '2014-01-01', so the partition wont accept the date greater than this. Because in this condition you have partitioned only values less than 2013. So alter the table by adding another partition.I think it works better.


1

I suspect this is more to do with the data type of AcctYear (DECIMAL(4,0)). When you simply specify WHERE AcctYear = 2014 SQL Server (in my test rig) believes this to be a SMALLINT. You can see this with a few trace flags: Effective partition elimination relies on data types lining up (amongst other things). Workarounds include changing the datatype of ...


0

Unfortunately, you are correct the suspect that the partitioning you created can get you in trouble. First of all, partition elimination only happens if the partitioning key is in the query you execute. In other words, any query that does NOT contain AcctYear will do (at least) 31 seeks. SQL Server has no way to know which partition to search if you don't ...


1

Postgres-XL is attempting to solve this as of 2014. They're aiming directly at big data on PostgreSQL, and they have developers from Stado onboard.


0

I would move the old records (closed incidents) to another table, not to another partition in the same table. Why ? Because if there is a LOGICAL reason, in my mind it makes more sense to put them as much seperate as possible, on a physical level. Consider this : you can store incidents, but your data schema can also store "open incidents" and "closed ...


2

You should be fine; the rowid would be a show-stopper if you were using it Temporarily, there would be "holes" in the data files, but Oracle will fill them in without any intervention. Ensure you have automatic tablespace management enabled. You can invoke that command but no need to; let Oracle take care of it The person that stated that probably meant ...



Top 50 recent answers are included