Skip to main content
Share Your Experience: Take the 2024 Developer Survey
127 votes
Accepted

Best database and table design for billions of rows of data

This is exactly what I do every day, except instead of using the hourly data, I use the 5 minute data. I download about 200 million records everyday, so the amount you talk about here is not a problem....
Mr.Brownstone's user avatar
82 votes

Best database and table design for billions of rows of data

PostgreSQL and BRIN indexes Test it for yourself. This isn't a problem on a 5 year old laptop with an ssd. EXPLAIN ANALYZE CREATE TABLE electrothingy AS SELECT x::int AS id, (x::int % ...
Evan Carroll's user avatar
  • 63.6k
14 votes

Best database and table design for billions of rows of data

It amazes me me that nobody here has mentioned benchmarking - that is until @EvanCarroll came along with his excellent contribution! If I were you, I would spend some time (and yes, I know it's a ...
Vérace's user avatar
  • 30.2k
14 votes
Accepted

How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

A very common misconception about Databases can be dispelled with this: Database != File When you update a Row in a database, the underlying data file on disk isn't touched at all - at least ...
Phill  W.'s user avatar
  • 8,815
10 votes

How to build a table for a private messaging system that supports replies?

Hierarchic Self-Referential Data Syntax shown using the free and open source database, PostgreSQL You need a self-referential table, which creates hierarchy in the database. This is how it looks. ...
Evan Carroll's user avatar
  • 63.6k
10 votes

Choosing relational DB for a small virtual server with 1Gb RAM

You could try SQLite. It's the most lightweight, relational database system, with enough support, documentation, and user base to be considered mainstream, in my opinion. It's easy to learn and mostly ...
J.D.'s user avatar
  • 37.9k
8 votes

Database for efficient range aggregate queries?

Using SQL Server ColumnStore indexes Well, okay, just one -- a clustered CS index. If you want to read about the hardware I did this on, head over here. Full disclosure, I wrote that blog post on ...
Erik Darling's user avatar
  • 41.2k
8 votes
Accepted

MySQL audit and general log

Audit Plugin MySQL Enterprise Audit is a new feature for security and compliance. It's not included in the Open Source / Community edition. As of MySQL 5.6.10, MySQL Enterprise Edition includes ...
Sybil's user avatar
  • 2,508
8 votes
Accepted

Single user mode while restoring database

You would only need to put the SQL Server instances in single user mode if you were restoring the master database. For user databases, you have to make sure there are no active connections to the ...
Scott Hodgin - Retired's user avatar
7 votes

Best database and table design for billions of rows of data

If you have not already, take a look at a time series DBMS, since it is optimized for storing and querying data where the primary focus is the date/time type. Typically time series databases are used ...
FloorDivision's user avatar
6 votes

PostGIS vs. SQL Server for GIS data

Having worked with the GIS capabilities of both systems, I'd suggest the following: If you are primarily looking to store and query geometries, their capabilities are pretty comparable. If you want ...
N8allan's user avatar
  • 197
6 votes

Best database and table design for billions of rows of data

Clearly this is not a NoSQL problem, but I would suggest that while an RDBMS solution would work, I think an OLAP approach will fit much better and given the very limited data ranges involved, I would ...
Paul Smith's user avatar
5 votes

Which major database platforms support TTL?

If you need true TTL functionality, the only real choice I know of is using an object DB. Mongo does TTL quite well; Redis fully supports TTL; LevelDB has an add-on which supports TTL; MS Azure ...
mgmonteleone's user avatar
5 votes

How large should be mysql innodb_buffer_pool_size?

@RickJames answer in sql: SELECT 'Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests' AS Expression, 'Read requests that had to hit disk' AS Meaning, ROUND((SELECT ...
M-A-X's user avatar
  • 151
5 votes

Are relational databases even viable for large eCommerce platforms?

There are really three different questions in here. Q: Is a table with hundreds of thousands of rows huge? Generally, no. For example, you can download the Stack Overflow public data export, and it ...
Brent Ozar's user avatar
4 votes
Accepted

Database design for students and families/parents

How about creating a separate Table called Person ? You will need separate tables for storing Guardian or Student related information and they both can refer to person Id. Instead of family you can ...
mouliin's user avatar
  • 522
4 votes

Recommendation for storage of series of time series

I can suggest Akumuli. It's a time-series database that supports compression and high-throughput data ingestion. With 25KHz measurement frequency and 20 engines, you will need to write 500K data ...
Evgeny Lazin's user avatar
4 votes
Accepted

What is the difference between views and materialized views

A VIEW is a stored query. When you select from it, you essentially run the query. CREATE VIEW foo AS SELECT col1,col2 FROM bar; Is essentially SELECT foo.* FROM ( SELECT col1,col2 FROM ...
Evan Carroll's user avatar
  • 63.6k
4 votes

Single user mode while restoring database

You cannot restore database if you are connected to this database, you'll get the error: RESTORE cannot process database 'my_db_name' because it is in use by this session This means that you ...
sepupic's user avatar
  • 11k
4 votes

How to store real-time data to database

Generally speaking with relational databases, 1000 single-row inserts will be slower than a single 1000-row insert. Batching up your data in memory and loading to the database may be faster. As you ...
AMtwo's user avatar
  • 16.2k
4 votes
Accepted

Database software solution

SQL Server express is a good choice. You can also create a very low cost (a few bucks a month) database only on Azure. In that scenario there would be no maintenance. I would argue SQL is what you ...
Doug B's user avatar
  • 104
3 votes
Accepted

Optimal database for large number of columns or rows

PostgreSQL @Dobob everything can be normalized :) If it's not very secret, what sort of data it is? (Asking as a geneticist-turned-DBA.) This is generally true. And you should start at looking how ...
Evan Carroll's user avatar
  • 63.6k
3 votes
Accepted

Elevation data in a database (tens of billions of information)

PostgreSQL with the PostGIS extension would appear to have everything you need to manage this data. PostGIS has a fairly rich set of functions that deal with rasters like the files that you have ...
MickyT's user avatar
  • 1,111
3 votes

Database for efficient range aggregate queries?

PostgreSQL with a BRIN index Even if seq is indexed, a typical database implementation will loop through each row to compute the sum in best case O(n), where n is the size of the range. That's not ...
Evan Carroll's user avatar
  • 63.6k
3 votes
Accepted

Good databases for unanchored LIKE condition

One technique is to split the "unanchored" query into two "anchored" parts. As you've shown a B-Tree search with a trailing wildcard is fast. The problem is you also need a leading wildcard. If you ...
Michael Green's user avatar
3 votes

whats the use of an autoincrement primary column?

There are a few advantages to using an auto-increment column as a primary key. Unlikely to need to change: If your primary key is business data that's meaningful to human beings, you may be asked to ...
RDFozz's user avatar
  • 11.6k
3 votes

best local database (for PC)

Consider SQLite. It is lightweight, reliable, and well proven in production. In fact, the creators claim it is the most widely deployed database in the world. It works well as a data interchange ...
Zerodf's user avatar
  • 333
3 votes

Are there alternatives to Microsoft Access?

Check out Linked Tables to SQL Server if you're running up against complexity requirements for your data. Basically, as you are expecting, Access would still work as your front-end and SQL Server ...
John Eisbrener's user avatar
3 votes
Accepted

MySql : Does it worth to convert a column with type DATETIME to DATE?

I mean, does a DATETIME field takes more size compare to DATE field? That's true. DATE needs 3 bytes, DATETIME needs 5-8 bytes. Is it more faster for the DB to search in a DATE field? In most ...
Akina's user avatar
  • 20k

Only top scored, non community-wiki answers of a minimum length are eligible