6 ms·

>There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys. Then you start adding t

>There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys. Then you start adding them, one by one, two by two, until you've got quite a few indexes for most any query you can run. Something is slow? Throw an index at it. What you end up with is some contention on overall throughput of your database, and well a lot of indexes that became a tangled ball of yarn over time. I spent years doing index maintenance on a large corporate database. Creating and rebuilding indexes was a hassle. So I set about building a new kind of database engine where every column in a table organizes its data for fast access (e.g. a columnar store with no separate indexing structure). I expected my system to be substantially faster than a Postgres table without indexes for a broad range of queries. What I didn't expect was for it to also be much faster for tables that were highly indexed in Postgres. I ran tons of queries on both systems and almost all of them were like this video: https://www.youtube.com/watch?v=OVICKCkWMZE https://www.youtube.com/watch?v=OVICKCkWMZE

MuffinFlavored 4y ago
What's the gotcha other than "it's a homegrown database engine"? Did you start from scratch? It can do JOINs and stuff?
- Bogdanp 4y ago
  One potential hint is the Postgres queries are being executed as sequential scans, as you can see from the explain output. The first query only returns 200k rows (out of 7M), so even without any config tweaks it should be using an index if one is present. So, my guess is those queries are being made against unindexed columns. Also, I'm guessing this is vanilla Postgres, so it's possible OP hasn't tweaked its config for the machine they're using, and the default config pg ships with is designed for fairly resource-constrained machines by today's standards. If the data is indexed, it's possible the rows are all over the place, and clustering could help.
  - skunkworker 4y ago
    In addition, I wonder if random_page_cost is not set to be 1. I've noticed on machines with SSD only pools that makes the query planner much more deterministic and utilizes indexes over sequential scans almost unused.
  - didgetmaster 4y ago
    I would love it if even one of the database experts who are so sure that the comparisons are somehow rigged, would actually run their own benchmark to prove it wrong instead of just speculating. Get any moderately sized table (millions of rows and a dozen or more columns) on your 'highly-tuned' postgres database with all the proper indexes in place and load that same data into Didgets (takes a whole 10 minutes to download the software and start using it) and run whatever queries suits your fancy.
    - Bogdanp 4y ago
      My post doesn't assume bad intent. As the new entrant in a space, the onus is on you to show your work. So, if you want people to run their own benchmark then share the data and configs you used. I actually tried to find the dataset you used yesterday to run my own benchmark, but the only official "Chicago crime database" I could find had only 300k rows and 19 columns, so I assumed it wasn't the same data you mention in your video.
      - didgetmaster 4y ago
        Here is the link where you can download the latest crime data for the Chicago police department (just export to CSV format): https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-Present/ijzp-q8t2 https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-... Even though I replied to your post, it was more of a general observation than to you specifically. My video has been viewed over a thousand times. I get comments all the time that say it can't be real or that I must be handicapping the other databases (I also have a video comparing to SQLite) by not configuring them correctly. I have yet to have a single person who claims to be a database expert try it out on their own favorite data set and tell me that they found Didgets to be slower than their preferred database engine.
- didgetmaster 4y ago
  The engine was created from scratch. It can do JOINs but hasn't yet implemented all the different kinds of joins. It is a completely different architecture (originally designed to be a file system replacement where multiple tags could be attached to each and every file) and the database functionality was almost discovered by accident. The tags I invented to make finding files based on them extremely fast, looked a lot like a columnar store. So I tried building regular relation tables using them. It surprised me when queries against my tables gave other databases (with decades of development behind them) a real run for their money.
SoftTalker 4y ago
> First you start off with almost none, maybe a few on primary keys. Primary keys are always indexed. Does the author really know what he’s talking about?
- Petersipoi 4y ago
  The author clearly knows this based on the sentence you quoted. I’m very confused as to what your complaint is. > I have almost no fruit, just a few apples. Indicates that I do know that apples are a fruit. Not that I don’t.
  - Petersipoi 4y ago
    GP edited their comment after I responded and didn’t mention it. Completely changed the comment. Was very antagonistic to the author of the article before.
- dewey 4y ago
  What about tables without primary keys?
  - rzzzt 4y ago
    DBMS-s still have to identify a particular row somehow. That identifier is CTID in Postgres and ROWID in Oracle. Both will change if the data is moved around.
  - deleted 4y ago
    [deleted]
  - lgas 4y ago
    Generally not a good idea to have these at all.
  - wielebny 4y ago
    These are usually stored in CSV files.
- eropple 4y ago
chasil 4y ago
In Oracle, Tom Kyte's advice is to never rebuild them, unless there are reasons for structural change or physically relocating the storage. I have occasionally seen performance gains by rebuilding a composite index with key compression, for example. Indexes do tend to return to their natural size in heavy use, so repeatedly shrinking them will just tax performance for a temporary gain in storage. https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:2913600659112 https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTI...
CharlesW 4y ago
You self-promote here often¹ (not judging, just an observation), and here are some thoughts that have occurred to me while reading your posts and comments. • Your vision for Didget is blurry. Sometimes it's a database (or "general purpose data management system"), sometimes it's a file system. Products that are dessert toppings and floor waxes rarely find purchase. My advice: Decide which problem domain you're playing in, then which problem(s) within that domain this solves. • The Postgres comparison seems naive. As the Postgres wiki notes, "PostgreSQL ships with a basic configuration tuned for wide compatibility rather than performance. Odds are good the default parameters are very undersized for your system." A comparison with a system-appropriate Postgres configuration would be more interesting. • Promoting a new closed-source database seems anachronistic when there's a plethora of fine open-source options. If you're wondering why you're not getting many leads from HN, I'd imagine this is the main reason. ¹ https://hn.algolia.com/?dateRange=all&page=0&prefix=false&query=didgets.com&sort=byPopularity&type=all https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...
- didgetmaster 4y ago
  Whenever I mention my project's website in an HN comment, I am 'self-promoting' so you are correct. In my experience that is not something that is frowned upon on HN, since I see this kind of thing all the time (the article I commented on was promoting another product). Didgets, as it exists today, is a technology rather than a 'product'. It consists of a set of highly optimized data objects that can be used to build complex systems like hierarchical file systems, relational database tables, logging frameworks, configuration managers, or content indexers. I have implemented enough functionality into the browser application to prove that it can do those things well, but none of them are fully implemented yet. I am trying to find the right product-market fit. I have not yet open-sourced any of the code, but I probably will once I decide which open source licensing is best for it. I am simply trying to introduce this technology to others to see if there is any interest in it. Note: While I didn't explore every configuration option in Postgres so I don't have confidence that it ran as fast as it possibly could, I did not just use the default configuration. I tried all the usual tricks to speed it up.
  - 4y ago

MuffinFlavored 4y ago

What's the gotcha other than "it's a homegrown database engine"? Did you start from scratch? It can do JOINs and stuff?

Bogdanp 4y ago
One potential hint is the Postgres queries are being executed as sequential scans, as you can see from the explain output. The first query only returns 200k rows (out of 7M), so even without any config tweaks it should be using an index if one is present. So, my guess is those queries are being made against unindexed columns. Also, I'm guessing this is vanilla Postgres, so it's possible OP hasn't tweaked its config for the machine they're using, and the default config pg ships with is designed for fairly resource-constrained machines by today's standards. If the data is indexed, it's possible the rows are all over the place, and clustering could help.
- skunkworker 4y ago
  In addition, I wonder if random_page_cost is not set to be 1. I've noticed on machines with SSD only pools that makes the query planner much more deterministic and utilizes indexes over sequential scans almost unused.
- didgetmaster 4y ago
  I would love it if even one of the database experts who are so sure that the comparisons are somehow rigged, would actually run their own benchmark to prove it wrong instead of just speculating. Get any moderately sized table (millions of rows and a dozen or more columns) on your 'highly-tuned' postgres database with all the proper indexes in place and load that same data into Didgets (takes a whole 10 minutes to download the software and start using it) and run whatever queries suits your fancy.
  - Bogdanp 4y ago
    My post doesn't assume bad intent. As the new entrant in a space, the onus is on you to show your work. So, if you want people to run their own benchmark then share the data and configs you used. I actually tried to find the dataset you used yesterday to run my own benchmark, but the only official "Chicago crime database" I could find had only 300k rows and 19 columns, so I assumed it wasn't the same data you mention in your video.
    - didgetmaster 4y ago
      Here is the link where you can download the latest crime data for the Chicago police department (just export to CSV format): https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-Present/ijzp-q8t2 https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-... Even though I replied to your post, it was more of a general observation than to you specifically. My video has been viewed over a thousand times. I get comments all the time that say it can't be real or that I must be handicapping the other databases (I also have a video comparing to SQLite) by not configuring them correctly. I have yet to have a single person who claims to be a database expert try it out on their own favorite data set and tell me that they found Didgets to be slower than their preferred database engine.
didgetmaster 4y ago
The engine was created from scratch. It can do JOINs but hasn't yet implemented all the different kinds of joins. It is a completely different architecture (originally designed to be a file system replacement where multiple tags could be attached to each and every file) and the database functionality was almost discovered by accident. The tags I invented to make finding files based on them extremely fast, looked a lot like a columnar store. So I tried building regular relation tables using them. It surprised me when queries against my tables gave other databases (with decades of development behind them) a real run for their money.

SoftTalker 4y ago

> First you start off with almost none, maybe a few on primary keys. Primary keys are always indexed. Does the author really know what he’s talking about?

Petersipoi 4y ago
The author clearly knows this based on the sentence you quoted. I’m very confused as to what your complaint is. > I have almost no fruit, just a few apples. Indicates that I do know that apples are a fruit. Not that I don’t.
- Petersipoi 4y ago
  GP edited their comment after I responded and didn’t mention it. Completely changed the comment. Was very antagonistic to the author of the article before.
dewey 4y ago
What about tables without primary keys?
- rzzzt 4y ago
  DBMS-s still have to identify a particular row somehow. That identifier is CTID in Postgres and ROWID in Oracle. Both will change if the data is moved around.
- deleted 4y ago
  [deleted]
- lgas 4y ago
  Generally not a good idea to have these at all.
- wielebny 4y ago
  These are usually stored in CSV files.
eropple 4y ago

chasil 4y ago

In Oracle, Tom Kyte's advice is to never rebuild them, unless there are reasons for structural change or physically relocating the storage. I have occasionally seen performance gains by rebuilding a composite index with key compression, for example. Indexes do tend to return to their natural size in heavy use, so repeatedly shrinking them will just tax performance for a temporary gain in storage. https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:2913600659112 https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTI...

CharlesW 4y ago

You self-promote here often¹ (not judging, just an observation), and here are some thoughts that have occurred to me while reading your posts and comments. • Your vision for Didget is blurry. Sometimes it's a database (or "general purpose data management system"), sometimes it's a file system. Products that are dessert toppings and floor waxes rarely find purchase. My advice: Decide which problem domain you're playing in, then which problem(s) within that domain this solves. • The Postgres comparison seems naive. As the Postgres wiki notes, "PostgreSQL ships with a basic configuration tuned for wide compatibility rather than performance. Odds are good the default parameters are very undersized for your system." A comparison with a system-appropriate Postgres configuration would be more interesting. • Promoting a new closed-source database seems anachronistic when there's a plethora of fine open-source options. If you're wondering why you're not getting many leads from HN, I'd imagine this is the main reason. ¹ https://hn.algolia.com/?dateRange=all&page=0&prefix=false&query=didgets.com&sort=byPopularity&type=all https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...

didgetmaster 4y ago
Whenever I mention my project's website in an HN comment, I am 'self-promoting' so you are correct. In my experience that is not something that is frowned upon on HN, since I see this kind of thing all the time (the article I commented on was promoting another product). Didgets, as it exists today, is a technology rather than a 'product'. It consists of a set of highly optimized data objects that can be used to build complex systems like hierarchical file systems, relational database tables, logging frameworks, configuration managers, or content indexers. I have implemented enough functionality into the browser application to prove that it can do those things well, but none of them are fully implemented yet. I am trying to find the right product-market fit. I have not yet open-sourced any of the code, but I probably will once I decide which open source licensing is best for it. I am simply trying to introduce this technology to others to see if there is any interest in it. Note: While I didn't explore every configuration option in Postgres so I don't have confidence that it ran as fast as it possibly could, I did not just use the default configuration. I tried all the usual tricks to speed it up.
- 4y ago