Skip to main content

Questions tagged [storage]

For questions about the persistent storage of database data.

Filter by
Sorted by
Tagged with
0 votes
0 answers
54 views

What kind of DB is best for frequent writes / rare reads?

It needs to: Handle around 80,000 writes / second Store billions of rows Store them for many years Consistency is a must Reads are very rare Data is currently stored in an RDBMS (I need to collect ...
Puffin's user avatar
  • 1
0 votes
0 answers
32 views

How to Determine the Low High Water Mark (LHWM) in Oracle DB?

How could one determine the position of the Low High Water Mark (LHWM) for a non-partitioned heap table segment in Oracle? I am concerned, that if we only do direct path inserts and never update, ...
Alex Bartsmon's user avatar
0 votes
1 answer
40 views

Why does PostgreSQL not add padding for column alignment at the end of tuple?

In the example here: https://www.percona.com/blog/postgresql-column-alignment-and-padding-how-to-improve-performance-with-smarter-table-design/ Why doesn't PostgreSQL add 2 bytes padding after the ...
Amr Elmohamady's user avatar
0 votes
0 answers
38 views

I increased my NDF files from 3 files to 7 files. How to re-distribute the data across MDF and NDF files?

I have a big database that uses 1 MDF files and 4 NDF files. The MDF file is placed in a 2TB disk and NDF files are placed in 500GB disks separately. I set the MDF file to be unlimited, while the NDF ...
Affan Widyan's user avatar
0 votes
1 answer
84 views

How does the arithmetic for how many bytes are saved by row-compressing a datetime add up?

Everything that follows is quoted from the datetime row here. I have broken the quote to give commentary on my understanding. Uses the integer data representation by using two 4-byte integers. The ...
J. Mini's user avatar
  • 545
0 votes
1 answer
47 views

Will NVARCHAR(max) columns benefit from row compression?

I have a table that I believe owes most of its size to a huge NVARCHAR(max) column. I do not know how to test where most of its size comes from. Regardless, would such a table benefit from row ...
J. Mini's user avatar
  • 545
0 votes
2 answers
166 views

Review freed space after DELETE in postgres

I'm looking for some practical help to resolve Disk space issue on DBs. I've visited these pages already: Postgres: Reclaiming Space Current size of a PostgreSQL table (vs disk space used) https://...
Fran's user avatar
  • 103
-1 votes
1 answer
65 views

Postgres files and pages

I got confused between files and pages in postgres, I have table for 700 MG and i find this file in base/5, so if i have the whole file with the actual size where's is pages then ?! I try to ...
user avatar
0 votes
1 answer
97 views

PostgreSQL: What's the most accurate way to find the network bandwidth of a Query result set

I have been trying to figure out network bandwidth of a Query output, since PostgreSQL only holds I/O bandwidth of Query I ended up in Octect_length() or pg_column_size(). I found below query from ...
goodfella's user avatar
  • 168
0 votes
1 answer
98 views

ERROR: duplicate key value violates unique constraint "sometable_pkey"

I have a giant table (500,000)+ rows with many columns that are varchar yes/no, and I want to reduce the size the db occupies, by changing the data types of varchar (yes/no domain), to int. Then ...
RandomNumberFun's user avatar
0 votes
2 answers
190 views

Does rebuilding/reorganize an index in SQL Server free up space in the DB file due to compacting data pages?

If I have an index with a default fill factor of 100%, and modifications result in a large amount of internal fragmentation (page splits, free space, etc.) so that many pages end up around 60% full ...
Marcus's user avatar
  • 69
7 votes
3 answers
2k views

ReFS Vs NTFS for SQL Server 2019, server 2019

Recently we've been running into errors when snapshotting DBs (operation error 665) One of the solutions of this error is to change the underlying disk type to ReFS. However I can't find much out ...
Cheerful DBA's user avatar
1 vote
1 answer
86 views

Optimizing type alignment for table with many of variable-length columns in Postgres

I have the following DDL for a Postgres table that was generated by JPA: postgres=# \d+ mytable; Table "public.mytable" Column ...
user991710's user avatar
0 votes
0 answers
15 views

How to expand storage in Single node Elasticsearch

We need to increase storage capacity for Elasticsearch working on a Single CentOS node. Even if Elasticsearch support multiple path I don't think it's useful and they also suggest not to use it. Other ...
goodfella's user avatar
  • 168
1 vote
1 answer
31 views

Seeking Guidance on Storage Models for Frequently Executed SQL Statement

An application frequently executes the following prepared statement in SQL: SELECT AVG(StockUnits) FROM PRODUCT; I'm currently exploring the most suitable storage model for the database architecture. ...
cricket900's user avatar
0 votes
0 answers
13 views

Polyglot Persistence and Data Redundancy

I am building a project that will have a recommendation model I built with TensorFlow. I am using MongoDB as my main database but I read about graph databases like neo4j and some people say that it ...
allennghayoui's user avatar
0 votes
1 answer
274 views

Best solution for storing metadata and data from documents?

First of all do I want to mention that I'm fairly new to databases and big data. Background: I'm working for a large company who wished to unify all their documentation. At it's core of my project am ...
nklsla's user avatar
  • 101
3 votes
1 answer
271 views

Row greater than max size (8060) but I can't see why

I have a table that has 483 columns. A date not null, a char(6) not null, and then 481 x float sparse null. By my maths, even if every sparse float is non-null, the row should still only be: date: 3 ...
tim654321's user avatar
  • 133
0 votes
2 answers
170 views

PostgreSQL table storage and retrieval with index on non-primary key column

I understand basic primary key table storage in PostgreSQL, where the data is stored in pages, and a B-tree index structure is used for efficient retrieval. However, I would like to understand the ...
sujeet's user avatar
  • 247
1 vote
1 answer
73 views

The size of my table is not growing

I couldn't see any growth in table size down to a single byte when I added 1 million rows in the table during the last days. I'm using this query: select table_name, pg_relation_size(quote_ident(...
Morgan's user avatar
  • 11
0 votes
1 answer
486 views

Local vs Shared Storage for MS SQL Server's Always On Availability Groups: What's Required?

Is it a requirement for MS SQL Server's Always On Availability Groups to always utilize shared Storage SAN or a virtual shared storage like Storage Spaces Direct? Can I set up Always On Availability ...
user avatar
0 votes
1 answer
413 views

Can this slow sysbench mysql result be "normal" for consumer hardware?

I have 4 different MySQL 8.0.30 servers on Rocky Linux 9, in one I replaced NVME SSD hoping it will perform better. I made an image with "Macrium Reflect" software and copied exact same ...
adrianTNT's user avatar
  • 194
2 votes
0 answers
271 views

How can I limit storage space taken up by `wpr_bucket_table`?

TL;DR: What is the wpr_bucket_table object in a SQL Server database, and how can we reduce the space it takes up? We have an Azure SQL Database that takes up significantly more storage than expected. ...
gps's user avatar
  • 31
0 votes
1 answer
45 views

What is a common type of storage hardware for dedicated MySQL servers? [closed]

I am curious what kind of storage hardware is common to see in servers that are dedicated for MySQL ? For example online services like Amazon AWS, Azure, Google, etc that offer(ed) dedicated mysql ...
adrianTNT's user avatar
  • 194
0 votes
1 answer
206 views

Same data stored in more rows with more repetition results in smaller SQLITE file?

I'm comparing two very barebones SQLite databases with the same data stored differently. Database A has tables like this: string (TEXT UNIQUE) count_1 (INTEGER) count_2 (INTEGER) the 23 10 it 56 44 ...
Nico's user avatar
  • 13
0 votes
1 answer
344 views

Using GPT partition type with SQL Server in production?

We are currently using MBR partition type with our SQL Server in production, but as our data volume increases, we are facing issues with our data drives getting full. We can't extend them because they ...
Fajela Tajkiya's user avatar
1 vote
0 answers
32 views

Structuring an N-Gram-counts-over-time database

I’m working on an application similar to the Google Ngram viewer, tracking the occurrence of terms in articles from different news publications over time. In the past I’ve worked primarily on front-...
Nico's user avatar
  • 13
0 votes
1 answer
92 views

How to tell if a MySQL query speed is bottlenecked by storage or CPU speed?

Using MySQL 8.0.30 on Rocky Linux 9 For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram ...
adrianTNT's user avatar
  • 194
0 votes
1 answer
336 views

How to remove unallocated space after partitioning

I've recently done partitioning on a table (postcode column) and created new filegroups for each postcode. Now that it is no longer using the PRIMARY filegroup, the file size is still as it is. How do ...
aYasser97's user avatar
0 votes
1 answer
61 views

Most generic filter class model

I'm designing an application that retrieves data from a (currently simple) .csv file and stores it's raw content. The application is intended to offer the user the possibility to define filters on the ...
Axel Samyn's user avatar
2 votes
2 answers
1k views

Is space reserved for a field when data doesn't use the full length?

A column is marked as nvarchar(50), but the value stored is only two characters. Do the remaining 48 characters take up space?
rome's user avatar
  • 29
1 vote
0 answers
90 views

How To Transfer Postgres Data That Doesn't Have a .s.PGSQL.5432 Socket or /var/lib/pg_data Mount?

The End Goal To somehow combat the issues listed so I can transfer stored data from the old database node (that has an improper postgres mount) to the new one that is currently bringing in new data (...
KotoKuraken's user avatar
5 votes
2 answers
2k views

Disable TOAST compression for all columns

I am running PostgreSQL on compressed ZFS file system. One tip mentioned is to disable PostgreSQL's inline TOAST compression because ZFS can compress data better. This can be done by setting column ...
Mikko Ohtamaa's user avatar
3 votes
2 answers
2k views

Storage space used for NULL values

According to below links, NULL values take some storage space: https://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server https://www.sqlservercentral.com/forums/topic/...
Aleksey Vitsko's user avatar
1 vote
2 answers
166 views

Could MySQL perform better on a 10GB network storage with raid vs local HDD?

Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
adrianTNT's user avatar
  • 194
0 votes
1 answer
610 views

Table with smaller data types seems to take more space on disk?

I have these two identical tables: Table "public.region" Column | Type | Collation | Nullable | Default -------------+----------+-----------+----------+--------- ...
tail's user avatar
  • 235
1 vote
1 answer
2k views

More columns vs. more rows to optimize storage and performance?

I have a table with this structure: ticker VARCHAR NOT NULL, interval VARCHAR NOT NULL, ts TIMESTAMP WITHOUT TIME ZONE NOT NULL, price ...
Thomas's user avatar
  • 303
0 votes
1 answer
94 views

Primary File Organization in DBMS - Files of ordered records (sorted files)

"Fundamental of Database Systems", 3rd ed. by Elmasri and Navathe, page 136 says: "We can physically order the records of a file on disk based on the values of one of their fields [...]&...
keezar's user avatar
  • 33
4 votes
1 answer
2k views

Microsoft SQL server storage best practice on Windows server vm with hybrid storage

I have a windows server VM, with a combination of HDD and SSD storage, the SSD storage capacity is limited to 50 GB, but HDD storage capacity is high. What is the best way to share these storages to ...
Reza M.A's user avatar
  • 251
0 votes
0 answers
502 views

Why JSON indexing take less storage than multiple columns in MySQL 8?

I am doing a quick performance comparison for 10 000 rows on MySQL 8.0.27 between a table multiple_cols with 2 columns num INT and str CHAR(200) having each an index, a table json_indexing with 1 ...
Guillaume S.'s user avatar
1 vote
2 answers
628 views

Determine Ideal Collation Set for correct data storage

Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following: My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. ...
DevelJoe's user avatar
  • 163
1 vote
2 answers
2k views

Size of data in DB is much larger than size of data dumped to disk

I have a table that contains 99.9% of the data in the database. When I query the size of the table and database, I get: mydb=# SELECT pg_size_pretty( pg_database_size('mydb') ); pg_size_pretty ------...
s5s's user avatar
  • 151
1 vote
1 answer
221 views

Performance of temp db files on SAN

It is generally recommended to put the tempdb database on disks that differ from those that are used by user databases. I have 4 databases on my sql server, ranging from 5GB to 300GB in size. D: drive ...
variable's user avatar
  • 3,476
6 votes
1 answer
859 views

Are TOAST rows written for UPDATEs not changing the TOASTable column?

Imagine I have a table in Postgres 13 like this: CREATE TABLE public.people ( id integer PRIMARY KEY, full_name character varying(255), bio text ); I then insert a row with enough ...
James Healy's user avatar
0 votes
0 answers
1k views

Oracle Storage Full, How to release space?

i have and AWS RDS Oracle Database wich its storage is full, looks like the cause is SYSAUX tablespace with 10.2GB size The largest object inside SYSAUX is: WRI$_ADV_OBJECTS (TABLE) checking the table ...
RobinNavegante's user avatar
-1 votes
1 answer
729 views

How to reduce big size database files (Data File ) SQL Server without SHRINK?

I found a couple of 'question' regarding shrinking big datafiles but I think that is not very accurate to my situation. So here is my question I have a big database with almost 8TB of Data File Size ...
SakZepelin's user avatar
-1 votes
1 answer
579 views

I want to drop existing table space and add the freed space to the new table space in oracle

I want to drop existing table space and add the freed space to the new table space in oracle. I am trying drop an existing tablespace on oracle db, and then use the freed space for a new table space. ...
kbj's user avatar
  • 1
0 votes
0 answers
2k views

What are the differences between Heap File Organization and Sequential File Organization (Pile Method)

I was learning about File Organization in DBMS, but i found that the Heap File Organization and Sequential File Organization (Pile Method) have the same definition (or maybe i couldn't find any ...
Malek Alhourani's user avatar
0 votes
2 answers
453 views

How to store a table with 1000+ columns, which are mostly null

Today I have to restart a projet and due to a specificity of the business (real estate), I will have to store a table for house/apartment/hangar/wahetever. But a single row can contain more than 1000 ...
user avatar
0 votes
1 answer
661 views

Postgres Database flagged as STORAGE FULL whereas there's 45 Gb available

I will start this post by stating that I am not very experienced in database administration, please accept my apologies for any unclear explanation you may find below. We have a replica postgresql ...
Vincent Rolea's user avatar

1
2 3 4 5
7