Tag Info

New answers tagged

-1

You are hitting the inherent scalability limits of Postgres (or any other RDBMS). Remember that an RDBMS index is a B-Tree. A B-Tree is O(log n) for both average and worst case. This makes it a nice, safe, predictable choice for reasonable values of N. It breaks down when N gets too big. NoSQL databases are (for the most part) hash tables. A hash ...


0

You could use views to expose your tables including the WHERE clause that filters the data according to your partition function. For instance, if you have a table dbo.Orders, you could move it to a different schema (eg data) and have data.Orders as the table name. Then you could create a view named dbo.Orders to filter just the active data: CREATE VIEW ...


1

As far as I know, even you have 2 database files to take advantage from partitioning you must create partitions (this feature is available only in Eneterprise Edition of SQL Server). Each partition will be in different file. No Archived will be in PRIMARY file. Others will be in DATABASE_ARCHIVED. The process is automatic, so there is no need to change any ...


2

No, if you are talking about partitioned tables, sql uses the where clause to filter rows. if your query doesn't have the where clause it will return all rows. if you can't test your changes you probably shouldn't be making them.


1

All things considered, I would go with option 2. Dates will be evenly selected on, but I'm going to guess that for a given query only one or two date partitions will be involved. It's a shame you can't cluster on geolocation and partition on date, which would be ideal. Elevation tends to correlate with geolocation anyway, if the bounding boxes are ...


0

FOREIGN KEYS are not supported in PARTITIONed tables. (With luck this might change in 5.8.) If you don't have over a million rows, don't bother to PARTITION. If you are not depending on FOREIGN KEY CONSTRAINTS, don't use them. INDEXes will suffice. If you would like to discuss these further, please provide SHOW CREATE TABLE (even if not finalized) and ...


0

I assume it is the .ibd file that is 20GB, not the .frm? If you want two physically separate tables you could copy tab1-1 into tab1-2 (so you have two copies) then Delete half the data from each. You'd still need to Optimize both tables afterwards to shrink them. Using the MySQL partition option you would have one table tab1, which you would partition by ...



Top 50 recent answers are included